| uperficial Casting Company is considering adding a new line to its product mix. The production line would be set up in unused space in Superficial main plant based in Hollywood, California. The machinery�s invoice price is $208,000, another $12,000 in shipping charges is required, and installation costs would amount to $27,000. The machinery has an economic life of 4 years, and Superficial management has obtained a special tax ruling that places the equipment in MACRS 3-year class. The machinery is expected to have a salvage value of $28,000 after 4 years of use. The new line would generate incremental sales of 1,325 units per year for all 4 years at an incremental cost of $112 per unit in the first year, excluding depreciation. Costs (excluding depreciation) are expected to increase by 2% per year due to inflation after the first year. Each unit can be sold for $219 in the first year. The sales price is expected to rise by 3.75% in each of the next two years, and then to fall by 8% in the fourth year, due to fast diminishing demand. To handle the new line, the company will require net operating working capital at 11.5% of the sales anticipated at the end of the next period. In other words, the company will need net operating working capital at time 0 of 11.5% of the sales expected at the end of year 1, etc. Net operating working capital is expected to be fully recoverable at the end of year 4, the last year of the product�s life cycle. The firm�s marginal tax rate is 38.2%, and its cost of capital is 10.75% per year. Deliverables (point values shown in parentheses); (1) (20) Set up a spreadsheet showing input and output sections next to each other to facilitate ease of sensitivity analysis. Input should include all parameters discussed in the text of the problem. Output should include: NPV, IRR, MIRR, PI, Payback Years and a Decision Box (Invest or Don�t Invest). If you want extra credit for including the Discounted Years to Payback, you should include that in your output section, as well. (2) (50) Show a calculation area separate from the Input and Output area where the parameters used in the calculations are LINKED to the input section, where necessary, and the output in the calculation area is LINKED to the output section. Your logic should be easy to follow. Ideally, MACRS factors, annual depreciation and book value should be shown together on three separate lines. Net Working Capital Requirements should be shown on a line with Changes in Net Working Capital Requirements shown on a line below the Requirements line. (3) (10) Derive a one way DATA TABLE for one of the variables in the input section versus the NPV of the output section. (5) Graph this table and label the graph following the label standards discussed in class. (4) (15) Derive a two way DATA TABLE using any two input variables that you like against NPV. Color code the NPV values less than 25% of your best estimate of the NPV. This table should be properly labeled, as well. (5) All dollar results should be shown in the nearest dollar. All percent values should be shown as xx.xx%. And all years to payback or index values should be shown to two decimal places. Do not round your results until the end of the calculations. A- (5) Derive a formula for the Discounted Years to Payback for this case and include the result in the Output Section. B- (10) Use a vertical or horizontal table lookup for revenue. Generate several different revenue tables, using different inflation rates. When creating your graph, do it by showing NPV or IRR vs the different table options. | ||||||
| Discounted Payback Period | 247000 | |||||
| 0.0975 | 1 | |||||
| Ln (1/1-o1xr/cf0)/ln(1+r) | 0.1275 | |||||
| 0.1575 | ## | |||||
| O1 = Initial Investment (Outflow) | ||||||
| r = Rate | 0 | |||||
| CF = Periodic cash flows. | ||||||
| ln(1/1-247000/241803.7)ln(1+0.1075) | ||||||
| #N/A | Year 1 | Year 2 | Year 3 | Year 4 | ||
| Input | ||||||
| Sales Per Unit | 1325 | 1325 | 1325 | 1325 | ||
| Salvage Value | 28000 | |||||
| Total Sales (219) | 290175 | 294164.9 | 294219.7675 | 294961 | ||
| Depreciation | 0 | 0 | 0 | 0 | ||
| Cost | 148400 | 151050 | 153700 | 21200 | ||
| Sales adjustments | 3989.906 | 4044.767 | 4045.521803 | -23214 | ||
| Cost of capital 09.75% | 24082.5 | 24082.5 | 24082.5 | 24082.5 | ||
| Tax | 44958.54 | 45470.38 | 44479.03617 | 95377.187 | ||
| Salvage Value | 28000 | |||||
| Total Exp | 172482.5 | 175132.5 | 177782.5 | 45282.5 | ||
| Net working capital | 117692.5 | 119032.4 | 116437.2675 | 249678.5 | ||
| 0.405591 | 0.404645 | 0.395749302 | 0.8464797 | |||
| Tax 38.2% | 44958.54 | 45470.38 | 44479.03617 | 95377.187 | ||
| Net working Capital 11.5% | 0.115 | 0.115 | 0.115 | 0.115 | ||
| Expected sales | 205852.6 | 208961.5 | 211617.7733 | 79203.015 | ||
| 33370.13 | 33828.96 | 33835.27326 | 33920.515 | |||
| 0.115 | 0.115 | 0.115 | 0.115 | |||
| Total Expenses | 217441 | 220602.9 | 222261.5362 | 140659.69 | ||
| Net Income | 72733.97 | 73562.03 | 71958.23129 | 182301.31 | ||
| Total Net Income | 400555.5 | |||||
| Cost of Capital increased due to inflation | ||||||
| Increase in Inflation rate | Interest | ∆ Net Income | ||||
| First Table | 0.0975 | 400555.54 | ||||
| Second Table | 0.1275 | 382238.02 | ||||
| Third Table | 0.1575 | 363920.50 | ||||
| Net Income | 72733.97 | 73562.03 | 71958.23129 | 182301.31 | ||
| Net Income | 68154.59 | 68982.65 | 67378.85129 | 177721.93 | ||
| Net Income | 63575.21 | 64403.27 | 62799.47129 | 173142.55 | ||
| Year 1 | Year 2 | Year 3 | Year 4 | |||
| Input | ||||||
| Sales Per Unit | 1325 | 1325 | 1325 | 1325 | ||
| Salvage Value | 28000 | |||||
| Total Sales (219) | 290175 | 294164.9 | 294219.8 | 294961 | ||
| Depreciation | 0 | 0 | 0 | 0 | ||
| Cost | 148400 | 151050 | 153700 | 21200 | ||
| Sales adjustments | 3989.906 | 4044.767 | 4045.522 | -23214 | ||
| Cost of capital 15.75% | 38902.5 | 38902.5 | 38902.5 | 38902.5 | ||
| Tax | 39297.3 | 39809.14 | 38817.8 | 89715.95 | ||
| Salvage Value | 28000 | |||||
| Total Exp | 187302.5 | 189952.5 | 192602.5 | 60102.5 | ||
| Net working capital | 102872.5 | 104212.4 | 101617.3 | 234858.5 | ||
| 0.354519 | 0.354265 | 0.345379 | 0.796236 | |||
| Tax 38.2% | 39297.3 | 39809.14 | 38817.8 | 89715.95 | ||
| Net working Capital 11.5% | 0.115 | 0.115 | 0.115 | 0.115 | ||
| Expected sales | 220672.6 | 223781.5 | 226437.8 | 94023.02 | ||
| 33370.13 | 33828.96 | 33835.27 | 33920.52 | |||
| 0.115 | 0.115 | 0.115 | 0.115 | |||
| Total Expenses | 226599.8 | 229761.6 | 231420.3 | 149818.4 | ||
| Net Income | 63575.21 | 64403.27 | 62799.47 | 173142.6 | ||
| Total Income | 363920.5 | |||||
