AutoSave OFF @yo. Home Insert Draw Page Layout Formulas Data Review View Tell me Arial 10 s v Paste BIU 29 Wrap Text Merge & Center v a. Ar B3 X fx F G G H 2. A B D E F 1 Ski Molding Project 4-Year Cash Flow Estimate 2 Year 3 4 3 Sales Volume 4 Cost Savings Per Pair 5 6 Cost Savings 7 8 Interest Expense 9 Depreciation 10 Net Cost Savings 11 12 Additional Tax Owed 13 Savings After Taxes 14 15 Add Back Depreciation 16 Subtract Principal Payments 17 18 Projected Cash Flow Estimate 19 20 21 B D 22 Ski Molding Project - Projected 4-Year Cash Flow Estimate Chapter Evaluating the Financial Impact of loans and Investments Funding will be arranged for the entire cost of this investment (the cost of the machine monthly installments over four years. plus installation) at 5.25% interest compounded monthly paid out in fill in equal 7. On the cashflow worksheet, calculate the cumulative interest expense for year 1 (the interest portion of the loan payments for the corresponding year). Assume the loan will start at the beginning of year 1 (January), and all payments will be made at automatically calculate these values for years 2 through 4. the end of each period. Write your formula so that it can be copied across the row to 8. Calculate the depreciation for this equipment using the straight line depreciation method. The equipment is assumed to have an Seycar life with a salvage value of $50,000 at the end of that period. For cost, use the cost of the machine plus instal- lation. Set up a separate worksheet named depreciation to store these values (similar to the depreciation worksheet shown in Figure 6.19), and use named ranges in your formula. 9. Calculate the net cost savings--the cost savings less the interest expense and depreciation. 10. Calculate the additional tax that would be owed (based on the net cost savings) assuming that The Zone is taxed at a 35% rate. Use a global named range to store this value. 11. Calculate the savings after taxes. 12. Complete the worksheet, adding back in the depreciation that was deducted and adding in the cumulative principal payments for the corresponding year, to arrive at a final projected cash flow estimate for each of the four years. Use the correct abso- lute and relative cell referencing so that your formulas will work for each of the cash flow years. 13. Skipping several rows under the data, include a sentence summarizing whether or not the cost of this machine will be recovered based on the Projected Cash Flow Estimate over the four years. Highlight your analysis in a light blue color. 14. Add titles on each worksheet, and format them to make them easy to read and understand. 15. Save and close the Ski Molder Cash Flow Estimate.xlsx workbook