please show how you got the answers, all of the green cells have to be input as formulas
Madison Manufacturing is looking to add a new assembly line to its manufacturing plant. The project is expected to have a five year lifespan, after which it will be shutdown. The equipment needed for the assembly line will cost $350,000 and will be depreciated using the 3-year MACRS depreciation schedule (depreciation rates are 33%, 45%, 15%, and 7% for years 1, 2, 3, and 4, respectively. An additional $35,000 in working capital will also be needed at the start of the project. The assembly line is expected to generate $250,000 in revenue per year with associated operating expenses of $110,000 per year. It is forecast that the equipment will have no salvage value by the end of the project. Madison's tax rate is 26% and the company's CFO has estimated that projects of this type require a return (wacc) of 10%. A. Under this Base Case scenario, setup your calculation cells below to compute NPV, IRR, and the Pl for this project. Once your spreadsheet is working, take the values you get in cells D59, D60, and D61and type them into cells E69, F69, and G69. B. Now you should change you input cells (leaving your calculation cells alone) and compute NPV, IRR, and PI for a Worse Case Scenario with the following changes to the Base Case Inputs: Revenue of $180,000, Operating Expenses of $150,000, and a tax rate of 35%. Take the values you get in cells D59-61, and type them in cells E68, F68, and G68. C. Now change your input cells (leaving the calculation cells alone) and compute NPV, IRR, and Pl for a Best Case Scenario with the following changes to the Base Case Inputs: Revenue of $350,000, Operating Expenses of $160,000, and a tax rate of 26%. Once again take the values you get in cells 59-61 and typethem in cells E70, F70, and G70. D. To finish your scenario analysis, use a formula in cell 873 to compute the expected NPV from your three scenarios. Note that the probability of each scenario is already included below. F. In cell E74 type in your investment recommendation as a simple Accept or Reject. B E F G H 38 39 40 41 42 43 44 45 46 INPUTS WACC Tax Rate Revenue Operating Expenses Salvage Value NOWC Equipment Cost Depreciation Rate 10% 26% 250,000 110,000 0 35,000 350,000 =input cells = calculation cells (you must use equations/formulas only) = output cells (you may type in your answer) 47 0.33 0.45 0.15 0.07 0 0 1 2 3 4 5 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 Year Revenue Operating Expense Depreciation EBIT Tax NOPAT Depreciation Change in LT Operating Assets Change in NOWC FCF Output NPV Sheet1 + B D E F G H NOPAT Depreciation Change in LT Operating Assets Change in NOWC FCF 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 Output NPV IRR Profitability Index (PI) NPV IRR PI Scenario Analysis: Worst Case Base Case Best Case Prob 0.30 0.40 0.30 expected NPV Investment Decision
