Finance (excel functions)
h4919
2020S_FIN325_ClassExercise_Ch9.FCFProjectValuation_A.xlsxBlooper Industry
| Case. Blooper Industry 5year's project | ||||||||
| 1. Inputs | 6. Project Valuation & Investment Criteria | |||||||
| Initial Investment ($) | 10,000 | Disc. Payback Period | 4.26 | |||||
| useful life of the invested assets (years) | 5 | Net Present Value | 5,098.65 | |||||
| Salvage value of the fixed asset ($) | 2,000 | IRR | 24.07% | |||||
| Sales of the fixed assets, t=5 ($) | 3,000 | PI | 1.44 | |||||
| Selling price / unit (initial year) | 15 | |||||||
| Number of units sold a year | 1,000 | |||||||
| Variable Cost (% of sales) | 40% | |||||||
| Fixed Cost ($) | 4,000 | |||||||
| Growth rate (Inflation included) | 5% | |||||||
| Inflation on Fixed cost | 3% | |||||||
| Discount rate (WACC) | 12% | |||||||
| Accounts Receivable, as fraction of sales at current year | 17% | |||||||
| Inventory as fraction of following years' expenses | 15% | *Assuming Inventory (Direct Material, Direct Labor, Work In Process) sold next year | ||||||
| Tax rate | 35% | |||||||
| 2. Captial investment | 0 | 1 | 2 | 3 | 4 | 5 | ||
| - | Investment in fixed assets | 10,000 | - 0 | - 0 | - 0 | - 0 | - 0 | |
| + | Sales of fixed assets | - 0 | - 0 | - 0 | - 0 | - 0 | 3,000 | |
| - | Tax of Gain on sales of fixed assets | 350 | =(3000-2000)*35% | |||||
| = | Cash Flow in fixed assets | (10,000) | - 0 | - 0 | - 0 | - 0 | 2,650 | |
| 3. Operating Cash Flows | 0 | 1 | 2 | 3 | 4 | 5 | ||
| + | Revenues | - 0 | 15,000 | 15,750 | 16,538 | 17,364 | 18,233 | |
| - | Variable Cost | - 0 | 6,000 | 6,300 | 6,615 | 6,946 | 7,293 | |
| - | Fixed Cost | - 0 | 4,000 | 4,120 | 4,244 | 4,371 | 4,502 | =4000*(1+3% inflation) |
| - | Depreciation (Straight Line) | - 0 | 1,600 | 1,600 | 1,600 | 1,600 | 1,600 | =(10000-2000)/5 |
| = | Profit before tax (EBIT) | - 0 | 3,400 | 3,730 | 4,079 | 4,448 | 4,838 | |
| - | Tax | - 0 | 1,190 | 1,306 | 1,428 | 1,557 | 1,693 | |
| + | Add back Depreciation | - 0 | 1,600 | 1,600 | 1,600 | 1,600 | 1,600 | |
| = | Operating Cash Flows | - 0 | 3,810 | 4,025 | 4,251 | 4,491 | 4,744 | |
| =17%*15000+15%*(6300+4120) | ||||||||
| 4. Change in working capital | 0 | 1 | 2 | 3 | 4 | 5 | ||
| - | Working capital | 1,500 | 4,113 | 4,306 | 4,509 | 4,721 | - 0 | Must be zero |
| = | Cash Flows of Changes in net Working capital | (1,500) | (2,613) | (193) | (203) | (212) | 4,721 | - 0 |
| 5. Project Cash flows | 0 | 1 | 2 | 3 | 4 | 5 | ||
| Project cash flows | (11,500) | 1,197 | 3,831 | 4,049 | 4,279 | 12,116 | ||
| Present Value of projected cash flows | (11,500) | 1,069 | 3,054 | 2,882 | 2,719 | 6,875 | ||
| Cumulative PV of projected cash flows | (11,500) | (10,431) | (7,377) | (4,495) | (1,776) | 5,099 | ||
United Pigpen
| United Pigpen Case. 8 year project | |||||||||||
| United Pigpen is considering a proposal to manufacture high-protein hog feed. The project would make use of an existing warehouse, which is currently rented out to a neighboring firm. The next year’s rental charge on the warehouse is $100,000, and thereafter, the rent is expected to grow in line with inflation at 4% a year. In addition to using the warehouse, the proposal envisages an investment in plant and equipment of $1.2 million. This could be depreciated for tax purposes straight-line over 10 years. However, Pigpen expects to terminate the project at the end of 8 years and to resell the plant and equipment in year 8 for $400,000. Finally, the project requires an immediate investment in working capital of $350,000. Thereafter, working capital is forecasted to be 10% of sales in each of years 1 through 7. Working capital will be run down to zero in year 8 when the project shuts down. Year 1 sales of hog feed are expected to be $4.2 million, and thereafter, sales are forecasted to grow by 5% a year, slightly faster than the inflation rate. Manufacturing costs are expected to be 90% of sales, and profits are subject to tax at 35%. The cost of capital is 12%. What is the NPV, IRR, DPP, PI of Pigpen’s project? | |||||||||||
| 1. Input variables ($,thousand) | 6. Project Valuation & Investment Criteria | ||||||||||
| Investment | 1,200 | Disc. Payback Period | 7.83 | ||||||||
| Revenue | 4,200 | Net Present Value | 85.80 | ||||||||
| Rev growth (%) | 5% | IRR | 13.19% | ||||||||
| Manufacture costs (% of sales) | 90% | PI | 1.06 | ||||||||
| Rent (opp. Cost) | 100 | ||||||||||
| Rent Growth (%) | 4% | ||||||||||
| Dep'n Life (years) | 10 | ||||||||||
| Tax Rate (%) | 35% | ||||||||||
| Plant Sale, t=8 | 400 | ||||||||||
| WC investment, t=0 | 350 | ||||||||||
| WC ongoing, t1-7, % of sales | 10% | ||||||||||
| Cost of Capital (%) | 12% | ||||||||||
| 2. Captial investment | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ||
| - | Investment in fixed assets | 1,200 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | |
| + | Sales of fixed assets | 400 | |||||||||
| - | Tax of Gain on Sale of fixed assets | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | 56 | =(400-(1200-120*8))*35% |
| = | Cash Flow in fixed assets | (1,200) | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | 344 | |
| 3. Operating Cash Flows | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ||
| + | Revenues | - 0 | 4,200 | 4,410 | 4,631 | 4,862 | 5,105 | 5,360 | 5,628 | 5,910 | |
| - | Manufacturing cost | - 0 | 3,780 | 3,969 | 4,167 | 4,376 | 4,595 | 4,824 | 5,066 | 5,319 | |
| - | Rent Opportunity cost | - 0 | 100 | 104 | 108 | 112 | 117 | 122 | 127 | 132 | |
| - | Depreciation | - 0 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | |
| = | Profit before tax (EBIT) | - 0 | 200 | 217 | 235 | 254 | 274 | 294 | 316 | 339 | |
| - | Tax | - 0 | 70 | 76 | 82 | 89 | 96 | 103 | 111 | 119 | |
| + | Add back Depreciation | - 0 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | |
| = | Operating Cash Flows | - 0 | 250 | 261 | 273 | 285 | 298 | 311 | 326 | 341 | |
| 4. Change in working capital | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ||
| - | Working capital | 350 | 420 | 441 | 463 | 486 | 511 | 536 | 563 | - 0 | |
| = | Cash Flows of Changes in net Working capital | (350) | (70) | (21) | (22) | (23) | (24) | (26) | (27) | 563 | |
| 5. Project Cash flows | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ||
| Project cash flows | (1,550) | 180 | 240 | 251 | 262 | 273 | 286 | 299 | 1,247 | ||
| Present Value of projected cash flows | (1,550) | 161 | 191 | 178 | 166 | 155 | 145 | 135 | 504 | ||
| Cumulative PV of projected cash flows | (1,550) | (1,389) | (1,198) | (1,020) | (853) | (698) | (553) | (418) | 86 |
Gain on Sale after tax
| Cash Flows. Tax from Gain on Sales of Invested Asset | |||
| Quick Computing installed its previous generation of computer chip manufacturing equipment 3 years ago. Some of that older equipment will become unnecessary when the company goes into production of its new product. The obsolete equipment, which originally cost $40 million, has been depreciated straight-line over an assumed tax life of 5 years, but it can be sold now for $18 million. The firm’s tax rate is 35%. What is the after-tax cash flow from the sale of the equipment? | |||
| Input variables: | ($ thousand, %) | ||
| Age of equipment | 3 | ||
| Initial cost | 40,000 | ||
| Useful life | 5 | ||
| Current market value | 18,000 | ||
| Tax rate | 35% | ||
| Solution and Explanation: | ($ thousand) | ||
| Initial Cost (Original Cost) | 40,000 | ||
| Accumulated Depreciation (3years) | 24,000 | =40000/5*3 | |
| Book Value | 16,000 | =40000-40000/5*3 | |
| Sales Price | 18,000 | ||
| Gain on Sale | 2,000 | =18000-16000 | |
| Tax on Gain | 700 | =2000*35% | |
| Cash flow. Gain on sale after tax | 17,300 | =18000-700 | |
