FINC 423 Financial Models Final Exam Cory Materials R&D department developed a new caulking compound for use in the residential construction industry. The R&D department incurred $75,000 of expenses to develop the new caulking compound. While Cory Materials sells other building materials, the caulking compound is a new product for the company. Cory Materials intends to utilize an existing storage building that it already owns, but leases to another firm for $50,000 per year. Cory Materials’ marketing manager spent $25,000 on an industry study of the caulking market and expects to sell the product for a sixyear period, after which the R&D should have a different caulking product. At the end of year 6, Cory Materials plans to liquidate the assets from the caulking compound in anticipation of a different product. In addition to the facts and assumptions below, assume that working capital must be invested immediately (in year 0) and will be fully recovered at the end of year 6. Note that the difference between the selling price of the equipment at the end of year 6 and the equipment's book value at the time of the liquidation is a taxable gain. Identify the relevant cash flows, then calculate the investment’s net present value and internal rate of return, and MIRR. Facts and assumptions Initial cost of new equipment Installation and shipping of equipment Expected life of project (years) Depreciation method (5 Year Equipment Class) Selling (salvage) price of equipment at the end of year 6 Price per tube of caulking Number of tubes that can be sold in year 1 Unit growth rate in tubes sold (after year 1) Unit Cost per tube Incremental fixed annual selling and administrative expenses Tax rate Working capital required (percent of sales at time 1 invested at time 0) Minimum required rate of return (WACC) Base $450,000 $50,000 6 MACRS $120,000 $5.00 100,000 2% $2.00 $100,000 20% 15% 10% Compute NPV, IRR, and MIRR to decide if Corry Materials should add the new caulking compound to its product lineup of building materials. 1. Build your financial model to solve the above problem. Make sure you consider color scheme, design layout, and documentation. Your model is being evaluated on its user functionality as well as correct calculations. Your model structure should be similar to the Freshly Frozen Fish example from class with an input section, project capital requirements section, pro forma income statement section, cash flows section, and finally Fall21 a results section. This is to be your own work as this is an EXAM, not a group assignment. 2. Sensitivity Analysis Use a data table to compute the sensitivity of the NPV and MIRR to a change in unit price from $4.00 - $6.00. Use a data table to compute the sensitivity of the NPV and MIRR to a change in the total installed equipment cost from $400,000 - $600,000. 3. Scenario Analysis Run the Scenario Manager for the following cases: Facts and assumptions Initial cost of new equipment Installation and shipping of equipment Expected life of equipment (years) Depreciation method Selling price of new equipment at the end of year 6 Unit Price Units Unit growth rate (after year 1) Unit Cost Incremental fixed annual selling and administrative expenses To compute the NPV, IRR, MIRR for each case. Finally, you need to create an organized output Tab. 4. Dashboard Fall21 $ Base Best Worst $450,000 $350,000 $550,000 $50,000 6 MACRS $120,000 $150,000 $100,000 $5.00 $6.00 $4.00 100,000 150,000 75,000 2% 5% 0% $2.00 $1.50 $2.50 100,000 $ 80,000 $ 120,000 Create a dashboard to analyze the project results a. b. c. d. e. f. Include the scenario manager summary table (with range names not cell numbers) Include a column graph for NPV of each case scenario Include a column graph for IRR of each case scenario Include a scatter graph for the NPV as the unit price changes from $4 to $6. Include a scatter graph for the IRR as the unit price changes from $4 to $6. Include a scatter graph for the NPV as the total installed new equipment cost changes from $400,000 – 600,000. g. Include a scatter graph for the IRR as the new equipment cost changes from $400,000 – 600,000. 5. Due Uploaded to Blackboard by 11:59pm on 12/15/21 BONUS (7 Extra Points Possible) What do you do with all the information that you have computed for this project? What is your recommendation? There are three potential solutions that you could recommend. Choose your recommendation/request (you can only choose 1!) and support it with the data from the problem or results from your model. 1. You recommend that Cory Materials should add the new caulking compound to its product lineup of building materials. Why? Support your solution with the data from the problem or results from your model. If you could only choose 1, what graphic from the Dashboard would you choose to put on a PowerPoint slide to justify your recommendation? Why? 2. You recommend that Cory Materials should NOT add the new caulking compound to its product lineup of building materials. Why? Support your solution with the data from the problem or results from your model. If you could only choose 1, what graphic from the Dashboard would you choose to put on a PowerPoint slide to justify your recommendation? Why? 3. You respectively request that Cory Materials provide you additional information to further your analysis. Why? What additional information would you request and support your request with the data from the problem or results from your model. If you could only choose 1, what graphic from the Dashboard would you choose to put on a PowerPoint slide to justify your request for additional information? Why? Fall21 A 1 B C D E F G Year 4 Year 5 Frozen Catfish Fillet Project Inputs 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Cost of Land Cost of Buildings & Equipment MACRS Class Life of Project (Years) Terminal Value of Land Terminal Value of Buildings & Equipment First Year Catfish Sales (lbs) Price per Pound Unit Sales Growth Rate Variable Costs as % of Sales Fixed Costs Tax Rate WACC 250,000 400,000 20 5 350,000 200,000 200,000 2.50 8% 60% 80,000 25% 10% 16 17 18 19 20 21 22 23 24 25 Investments Schedule for Frozen Catfish Fillet Project Year 0 Year 1 Year 2 Year 3 Investment in Plant/Equipment MACRS Depreciation Book Value Salvage Value Tax on Salvage Net Salvage 26 Net Investment in Fixed Assets 27 28 Investment in Net Working Capital Chapter 12-13 Capital Budgeting Under Risk Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. What is Capital Budgeting? • Capital budgeting is the term used to describe the process of determining how a firm should allocate scarce capital resources to available long-term investment opportunities • Because the goal of the firm is to maximize shareholder’s wealth, managers are responsible for selecting only those investments that are expected to increase shareholder wealth • Ultimately, we will be concerned with the comparison of costs versus benefits • The benefits are the cash flows provided by the investment, and the cost is the amount spent to acquire it (all in present value terms) • Only projects where the benefit equals or exceeds the cost should be accepted Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Making the Decision • Once the relevant cash flows have been determined, it is time to calculate the profitability measures • There are six commonly used profitability measures: • Net Present Value • Internal Rate of Return • Modified Internal Rate of Return • Payback Period • Discounted Payback Period • Profitability Index Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Net Present Value (NPV) • The NPV is a direct comparison of the benefit of the project with its cost • It is calculated as: N CFt − IO = Benifit − Cost t t =1 (1 + WACC ) NPV =  • Excel has an NPV function, but beware that it actually calculates the present value of uneven cash flows, not the net present value (so, use it to calculate the first term above, and then subtract the IO) • The project should be accepted if NPV ≥ 0 • If NPV = 0 the project will earn exactly its WACC and the shareholders of the firm will be satisfied • If NPV > 0 then the project will earn more than its WACC • If NPV 1then the project will earn more than its WACC • If PI < 1 then the project will earn less than its WACC and shareholder wealth will decline, so the project would be rejected Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Optimal Capital Budget • Optimal Capital Budget Without Capital Rationing • The projects are listed in Table 12-6. • Sort & Filter • Enter the data from Table 12-6 into your RMM worksheet beginning with the labels in A22:C22. The cumulative cost can be calculated by entering: =SUM(A$23:A23) into B23 and then copying the formula to the other cells. The first step in determining the optimal capital budget is to sort all of the projects by their IRR. Select A22:C32 and click the Sort & Filter button on the Home tab. Because we want to sort based on the third column (instead of the first column, which is the default), we need to choose Custom Sort from the menu. Choose IRR from the “Sort by” list and Largest to Smallest in the Order list. This sorted list of IRRs is known as the Investment Opportunity Schedule (IOS). Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Optimal Capital Budget Without Capital Rationing Sorted Investment Opportunity Schedule Cost Cumulative Cost 407,769 407,769 396,209 803,978 439,207 1,243,185 271,477 1,514,662 445,529 1,960,191 189,921 2,150,112 74,950 2,225,062 146,661 2,371,723 201,843 2,573,566 138,298 2,711,864 Optimal Capital Budget is where MCC and IOS intersect IRR 16.51% 16.16% 15.87% 15.38% 15.02% 13.82% 13.00% 12.19% 11.69% 11.48% As was shown on page 361, we can make the IOS line into a step function. The process is exactly the same as before in Chapter 11: We must add additional data points for the cumulative cost and IRR, so that we have two points at each change in the IRR. Monczka/Handfield/Giunipero/Patterson, Purchasing & Supply Chain Management, 6th Edition. © 2016 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Optimal Capital Budget • Optimal Capital Budget Under Capital Rationing • Let’s look at an example • Because of declining demand for high-pressure frammis valves, the Frammis Valve Corporation of America (FVCA) is considering expanding into a number of other businesses. The managers of FVCA have determined that it has 13 potential new investments (see Exhibit 12-10). The total cost of these investments would be $7,611,990, but they are limited to a maximum total investment of only $3,000,000. Your job is to determine the combination of the projects the company should choose. Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Optimal Capital Budget Under Capital Rationing The Optimal Capital Budget Under Capital Rationing Project Cost NPV Include A 237,005 84,334 1 B 766,496 26,881 1 C 304,049 23,162 1 D 565,178 82,598 1 E 108,990 20,590 1 F 89,135 90,404 1 G 795,664 18,163 1 H 814,493 97,682 1 I 480,321 52,063 1 J 826,610 53,911 1 K 734,830 56,323 1 L 910,598 88,349 1 M 978,621 69,352 1 Total 7,611,990 763,812 13 Constraint 3,000,000 Because each project will either be selected or not, this is a perfect use for a binary variable. A binary variable can take on one of two values, most commonly 0 or 1. We will set up a column with 0s and 1s, where 1 indicates that a project is selected, and 0 indicates rejection. Because each project will either be selected or not, this is a perfect use for a binary variable. A binary variable can take on one of two values, most commonly 0 or 1. We will set up a column with 0s and 1s, where 1 indicates that a project is selected, and 0 indicates rejection. Due to nature of problem, we need to use sumproduct function since some projects cannot be accepted Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Optimal Capital Budget Under Capital Rationing • To restate the problem, we want to maximize the total NPV in C17 by changing the cells in D4:D16 subject to two constraints. The first constraint is that the total cost, in B17, must be less than or equal to 3,000,000. Next we must constrain the values in D4:D16 to be either 0 or 1, but they cannot take on any noninteger values (i.e., they must be binary values).Launch the Solver from the Analysis group on the Data tab • If you don’t see the Solver button, then you need to activate the Solver add-in. Click the File tab and then click Options. You can activate Solver from the Add-Ins section. Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Excel Solver • • • • • • Objective Cell MAX, MIN, or Value Cells to Change Constraints (ADD) Non-Negative Solving Method Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Optimal Solution 12-11 The Optimal Capital Budget Under Capital Rationing Project Cost NPV Include A 237,005 84,334 1 B 766,496 26,881 0 C 304,049 23,162 1 D 565,178 82,598 1 E 108,990 20,590 0 F 89,135 90,404 1 G 795,664 18,163 0 H 814,493 97,682 1 I 480,321 52,063 0 J 826,610 53,911 0 K 734,830 56,323 0 L 910,598 88,349 1 M 978,621 69,352 0 Total 2,920,458 466,529 6 Constraint 3,000,000 Selected A C D F H L Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Relevant and Irrelevant Cash Flows • In order to be considered relevant for capital budgeting, the cash flows must be: • Incremental – That is, over and above any cash flows that the firm already has-think new project cash flows minus existing cash flows • After-tax – Shareholders are not concerned with before-tax cash flows because they can’t be reinvested or paid out as dividends until the taxes have been paid • The cash flows must not be: • Sunk costs - These are cash flows that have occurred in the past and cannot be recovered regardless of the investment decision, so they cannot affect our decision • Additional financing costs – While financing costs are relevant to the decision, we don’t want to count them as cash flows because we will account for them when discounting the cash flows at the WACC (i.e., we don’t want to double count financing costs as that would unnecessarily penalize the project) Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Calculating the Relevant Cash Flows • The image at right shows the three types of cash flows that we are concerned with: • Initial Outlay (IO) – The net, upfront nonoperating cash flows required to acquire the project • Annual After-Tax Cash Flows (ATCF) – The annual operating cash flows that the project will provide after taxes • Terminal Cash Flow (TCF) – The nonoperating cash flows associated with the end of the life of the project • Note that the ATCF will occur each year during the life of the project (it will generally be different each year), while the IO and TCF each occur only once • With these Cash Flows and the WACC, we can compute the NPV and make the project decision Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Calculating the Relevant Cash Flows • Relevant Cash Flows are incremental FREE CASH FLOWS • Free Cash Flow Model • Free Cash Flow = Operating Cash Flow – Change in Operating Assets • • • • Recall define after-tax operating cash flow as: After-Tax Operating Cash Flow = NOPAT + Noncash Expenses NOPAT = EBIT(1-tax rate) The most significant Noncash Expense is Depreciation, so • After-Tax Operating Cash Flow = EBIT(1-tax rate) + Depreciation Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Free Cash Flow Model • The required investment in operating assets: • Change in Operating Assets = Change in NOWC + Change in Operating Fixed Assets • where NOWC is net operating working capital. • A simplification (for our purposes) is to use NWC ~ NOWC and change in operating assets ~ net capital expenditures. • Change in Operating Assets = Change in NWC + Net Capital Expenditures • Free Cash Flow = Operating Cash Flow – Change in Operating Assets • Free Cash Flow = EBIT(1-tax rate) + Depreciation – Change in NWC –Net Capital Expenditures Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Free Cash Flow Model • Free Cash Flow = EBIT(1-tax rate) + Depreciation – Change in NWC –Net Capital Expenditures • To determine the operating cash flow, we usually generate a pro forma income statement with incremental operating cash flows • To determine the change in operating fixed assets, we generate a portion of a pro forma balance sheet Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Calculating the Relevant Cash Flows IO • Let’s apply the Free Cash Flow Model to the project timeline • • Initial Outlay (I O) – The net, upfront nonoperating cash flows required to acquire the project • Note that at time 0 there is no operating cash flow; • Thus the free cash flow at time 0, the IO equals • IO = - (Change in NWC + Net Capital Expenditures) • The sign on IO is usually negative as this is an investment Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Calculating the Relevant Cash Flows IO Initial Outlay (IO) – The net, upfront nonoperating cash flows required to acquire the project • The following is a rough guideline as to how to calculate the IO cash flow IO = - [ΔNWC + Price of Project Equipment/Buildings + Shipping + Installation + Training − (Salvage Value − Taxes on Salvage) of existing equipment] • Note that Shipping, Installation, and Training are relevant cash flows • Note that every project is different, and the formula is a guide to possible components of the initial outlay • For “new” projects there are no “existing” cash flows so there is no net salvage value of existing equipment • For “replacement” projects we must consider net salvage value of existing equipment (we will discuss the details of net salvage value later) Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Calculating the Relevant Cash Flows ATCF • Let’s apply the Free Cash Flow Model to the project timeline • • Annual After-Tax Cash Flows (ATCF) – The annual operating cash flows that the project will provide after taxes • Usually there is no additional annual required investment in operating assets during the operating period of the project ; • Thus the free cash flow during the operating period of the project (assuming no additional annual required investment in operating assets) simplifies to • ATCF = EBIT(1-tax rate) + Depreciation Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Calculating the Relevant Cash Flows ATCF • Annual After-Tax Cash Flows (ATCF) – The annual operating cash flows that the project will provide after taxes • Incremental ATCF = incremental EBIT(1-tax rate) + incremental Depreciation • The sign on ATCF is usually positive as these are usually inflows. Some exceptions are projects when we have to select the “best” negative project. • We will always generate a pro forma income statement in this class, but the following is a rough guideline formula • ATCF = (ΔRevenues − ΔNet Expenses − ΔDepreciation) × (1 − tax rate) + ΔDepreciation • Again for “new” projects, there is no existing cash flow. For “replacement” projects we must consider the existing cash flow Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Calculating the Relevant Cash Flows TCF • Let’s apply the Free Cash Flow Model to the project timeline • •Terminal Cash Flow (TCF) – The nonoperating cash flows associated with the end of the life of the project • Since we are accounting for the nonoperating cash flows associated with the end of the life of the project the free cash flow at the end of the life of the project simplifies to TCF = (  NOWC ) − (Shut-down Expenses  (1 − tax rate)) + Salvage Value − Taxes on Salvage of New Machine • The sign on TCF is usually positive since we are recovering (sell) the investment in Net Working Capital and recovering (sell) some of the investment in capital by Salvage Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Calculating the Relevant Cash Flows TCF • Terminal Cash Flow (TCF) – The nonoperating cash flows associated with the end of the life of the project • Some typical assumptions regarding the terminal cash flow • Although we almost always assume zero salvage value for Depreciation Calculation purposes, generally we assume some positive salvage value for the equipment at the end of the project. • Since taxes are a relevant cash flow, we estimate the taxes on salvage for the equipment (we will discuss the details of taxes on salvage later) • The typical assumption is that the company will recover (liquidate and sell) ALL of the net working capital that was invested over the project life. Most analysts will simply estimate the initial investment in net working capital (a negative cash flow for IO) and then recover that investment at the end of the project (a positive cash flow for TCF) Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Freshly Frozen Fish Company Example The Freshly Frozen Fish Company currently markets frozen fish fillets and other related products. While seeking expansion ideas, the company decided to look into the possibility of a line of frozen cat-fish fillets. Entry into this business would require the purchase of an existing 80-acre catfish farm in western Alabama at a cost of $250,000 for the land and $400,000 for the buildings and equipment. The buildings and equipment will be depreciated using MACRS with a class life of 20 years. At the end of the five years, management anticipates that the farm will be sold for $550,000 ($350,000 for the land and $200,000 for the buildings and equipment).The marketing department estimates that the firm will be able to sell 200,000 pounds of fillets at an average wholesale price of $2.50 per pound during the first year. Unit demand is expected to grow at a rate of 8% annually thereafter. Variable operating expenses are expected to average 60% of gross sales, and fixed costs (not including depreciation) will be $80,000 per year. The company’s marginal tax rate is 25% and its WACC is 10%. Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Freshly Frozen Fish Company Example Frozen Catfish Fillet Project Inputs Cost of Land 250,000 Cost of Buildings & Equipment 400,000 MACRS Class 20 Life of Project (Years) 5 Terminal Value of Land 350,000 Terminal Value of Buildings & Equipment 200,000 First Year Catfish Sales (lbs) 200,000 x Price per Pound 2.50 x Unit Sales Growth Rate 8% Variable Costs as % of Sales 60% x Fixed Costs 80,000 Tax Rate 25% WACC 10% Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Freshly Frozen Fish Company Example IO • IO Cash Flow • IO = - [ΔNWC + Price of Project Equipment/Buildings + Shipping + Installation + Training − (Salvage Value − Taxes on Salvage) of existing equipment] • The project is a “new” so there is no existing equipment • For this simplified example there is no Shipping, Installation, Training, or investment in working capital • IO = - ( Cost of Project Equipment/Buildings + Land) Timothy R. Mayes, Financial Analysis with Microsoft Excel, 9th Edition. © 2021 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Freshly Frozen Fish Company Example ATCF • ATCF Cash Flow • ATCF = EBIT(1-tax rate) + Depreciation • The project is a “new” so there are no existing cash flows • For this project, we must compute the depreciation using MACRS • MACRS uses IRS tables for the allowable depreciation expense period (Class Life) based on the type of asset • MACRS depreciates to a terminal value of zero (and this assumption is usually used for Straight Line Depreciation as well) • Class Life
Purchase answer to see full attachment