Steps To Success: Level 2 The Fquipment division at TheZone is looking into a new piece of equipmeth thot developed in Furope to mold skis mote precisely and less expensively than the , the cost of the machine plus installation is one. Complete the following: 1. Open the workbook named Ski.xlsx in the Chapter 6 folder, and then tave the be as Ski Molder Cash Flow Bstimate, dsx. The structure for the projected cade, the Figure 6.26: Woricsheet for ski molder projected cash flow estimate 2. Rename the Sheet1 worksheet as cashflow. Insert the following title at the top of the worksheet, merged and centered: Ski Molding Project - Projected 4-Yes Cush Flow Estimate. 3. Enter the sales volume for each year, assuming sales in year 1 of 140,000 pain. The sales volume for each successive year is assumed to be 5% more than the previousyee Round your calculated sales volumes to the nearest whole number. 4. Enter the cost savings as $10.00 per pair, which will be the same amount in the wh. sequent years. 5. Calculate the cost savings as the number of pairs of skis sold multiplied by the coe savings per ski. 6. On a separate workheet named loan (similar to the Loan worksheet shown it Figure 6.15 ), create an amortization table listing the principal and interest paymens and remaining principal in each monthly period, assuming TheZone will borrow the money under the following terms: Evaluaking the Financial hapoct of lopins and himentwerch 1. Oa the wathtlow worksheet, calculate the cumblative infereit experie toe yrar I (the imerest portion of the loan payments for the cerreypondisg year). Aseurse the wo the depreciation worksticet shown in Figare 6.19 ), and ase theies valoes ( imitat firmila Q. Cajculate the net cost savingh- the cost uvings les the interet expense and deprecation. 10. Cululate the additional tax that would be owed (based on the net cose uving? asuming that Theilone is taxed at a 35% rate. Use a global named range to store (his value- 11. Calaulate the savings after taxes 12. Complete the worksheet, adding back in the depreciation that wat dectocted and ading in the cumulative principal payments for the cotreyonding year, to arrive at a final projected cash flow cstimate for each of the foar years. Use the correct absolute athd relative cell referencing so that yout formulas will work for cach of the calt faivyears. 13. Slapping several rows under the data, include a scntence ummarizing whether of not the cost of this machine will be recowered bosed on the Projected Cash Fow Estimate orer the fout years. Highlight your analysis in a light blise color. 14. Ndd titles on each workshect, and format them to make them casy to reak and anderstand. 15. Sue and close the Siki Molder Cash Flow Estimate xiss workbook- LEVEL 3 Evaluating the Financial Viability of Alternative Project Options Sotting Up a Worksheet to Analyze Profitability Byan has now estimated the cash flow for the manufacture of the T/Edge ahoc according to the terms of the option 1 loan, which requires 51,000,000 in capital fanded through a bay from CtrBank at 8% interest compounded quarterhy. This cash flow spreadshect will gie the company an excellent idea of what funds will be necded when, and at what time Steps To Success: Level 2 The Fquipment division at TheZone is looking into a new piece of equipmeth thot developed in Furope to mold skis mote precisely and less expensively than the , the cost of the machine plus installation is one. Complete the following: 1. Open the workbook named Ski.xlsx in the Chapter 6 folder, and then tave the be as Ski Molder Cash Flow Bstimate, dsx. The structure for the projected cade, the Figure 6.26: Woricsheet for ski molder projected cash flow estimate 2. Rename the Sheet1 worksheet as cashflow. Insert the following title at the top of the worksheet, merged and centered: Ski Molding Project - Projected 4-Yes Cush Flow Estimate. 3. Enter the sales volume for each year, assuming sales in year 1 of 140,000 pain. The sales volume for each successive year is assumed to be 5% more than the previousyee Round your calculated sales volumes to the nearest whole number. 4. Enter the cost savings as $10.00 per pair, which will be the same amount in the wh. sequent years. 5. Calculate the cost savings as the number of pairs of skis sold multiplied by the coe savings per ski. 6. On a separate workheet named loan (similar to the Loan worksheet shown it Figure 6.15 ), create an amortization table listing the principal and interest paymens and remaining principal in each monthly period, assuming TheZone will borrow the money under the following terms: Evaluaking the Financial hapoct of lopins and himentwerch 1. Oa the wathtlow worksheet, calculate the cumblative infereit experie toe yrar I (the imerest portion of the loan payments for the cerreypondisg year). Aseurse the wo the depreciation worksticet shown in Figare 6.19 ), and ase theies valoes ( imitat firmila Q. Cajculate the net cost savingh- the cost uvings les the interet expense and deprecation. 10. Cululate the additional tax that would be owed (based on the net cose uving? asuming that Theilone is taxed at a 35% rate. Use a global named range to store (his value- 11. Calaulate the savings after taxes 12. Complete the worksheet, adding back in the depreciation that wat dectocted and ading in the cumulative principal payments for the cotreyonding year, to arrive at a final projected cash flow cstimate for each of the foar years. Use the correct absolute athd relative cell referencing so that yout formulas will work for cach of the calt faivyears. 13. Slapping several rows under the data, include a scntence ummarizing whether of not the cost of this machine will be recowered bosed on the Projected Cash Fow Estimate orer the fout years. Highlight your analysis in a light blise color. 14. Ndd titles on each workshect, and format them to make them casy to reak and anderstand. 15. Sue and close the Siki Molder Cash Flow Estimate xiss workbook- LEVEL 3 Evaluating the Financial Viability of Alternative Project Options Sotting Up a Worksheet to Analyze Profitability Byan has now estimated the cash flow for the manufacture of the T/Edge ahoc according to the terms of the option 1 loan, which requires 51,000,000 in capital fanded through a bay from CtrBank at 8% interest compounded quarterhy. This cash flow spreadshect will gie the company an excellent idea of what funds will be necded when, and at what time
