3. Your firm is considering purchasing a piece of equipment that will save your firm $200K per year for the next 5 years. After that, the savings will taper off, declining 15% per year for 10 years, and then the savings will cease. The cost of this equipment is $1.1M. Your firm's cost of capital is 11%. Time 0 1 Cash Flow ($1,100,000) $200,000 1 $200,000 $170,000 $144,500 5 6 7 15 $39,375 You are going to approach this problem by modeling the cash flows. Please do the following: a. We typically think of all rates of change as growth rates and use a negative value for declining rates. Using cell B12, define the rate of growth in this way. b. Build a responsive table of cash flows in the Analysis section. All values should be linked to the inputs. There are three phases of cash flows: the first where the cash flow is $200K, the second where it's declining over 10 years, and the third is after 10 years where you should have a blank (two sets of quotes). If your version of Excel is new enough, you will be able to use the IFS function. The third logical test should just be "TRUE". If you don't have the IFS function, you can use a series of nested IF statements. For further clarification, the example below shows the case where the number of fixed cash flows is 3 and the number of declining cash flows is 7. I recommend using your formula for the cash flows for all of the cells from t=1 to t=50 for significant flexibility in inputs. However, it is good to note that this finite table can hold only so many combinations of inputs. Time 0 1 2 3 4 5 6 7 8 9 10 11 12 13 Cash Flow ($1,100,000) $200,000 $200,000 $200,000 $170,000 $144,500 $122,825 $104,401 $88,741 $75,430 $64,115 c. Calculate the NPV of this investment in the outputs section using the NPV function. I recommend using all 50 cash flows in the NPV function to cover any range of inputs that would populate 50 periods. Don't forget that the initial cash flow doesn't belong in the NPV function and needs to be added to the function's results. d. Using Goal-seek, find the annual savings for the first phase that cause the project to be a zero-NPV project. This is called the Break-Even point because it's as low as the savings can be before the project become unattractive. Record this value (hard- coded) in the Outputs and be sure to change your annual savings input back to $200K. A B 1 Model Inputs 2 Initial Cost $1,100,000 E F H Output NPV $27,202.91 -NPV(B7,816:365)+B15 Break-Even First YT Must re-run goal seek it inputs change Savings $200,000 Annual Savings 3 4 Yrs of Constant Savings 5 LT Decline Rate 6 # Years Decline 7 Cost of Capital 8 5 15% 10 11.0% Sensitivity Analysis Data Table 15% 10% 20% 3 4 5 -IFS(A16$B$4,815*0.85) 9 10 Analysis 11 12 Second Phase Gr Rate 13 14 Time 15 0 16 1 17 2 18 3 19 4 20 5 21 22 7 23 8 24 9 25 10 26 11 27 12 28 13 29 14 Cash Flow ($1,100,000) $200,000 $200,000 $200,000 $200,000 $200,000 $170,000 $144,500 $122,825 $104,401 $88,741 $75,430 $64,115 $54,498 $46,323 20 10 An