Chapter 6 Evaluating the Steps To Success: Level 2 developed in Europe to mold skis more precisely and less expensively than the The Equipment division at The Zone is looking into a new piece of equipment to be S1,500,000. The projected cost savings are expected to be S10.00 Technology being used at TheZone. The cost of the machine plus installations skis, You have been asked to estimate a projected cash flow savings if anytha perp generated by this proposed project over the next four years. Complete the following 1. Open the workbook named Ski.xlsx in the Chapter 6 folder, and then save the as Ski Molder Cash Flow Estimate.xlsx. The structure for the projected care estimate is provided on Sheetl, as shown in Figure 6.26. Figure 6.26: Worksheet for ski molder projected cash flow estimate E 2 A 31 2 Some Car Savis Expense Detection 9 Cossavings 10 11 Aastaar wed Savines ATES 14 Add Back Depreciation 15 d Principal Payments 16 17 Proedd Cash Flow Estate 2. Rename the Sheetl worksheet as cashflow. Insert the following title at the top of the worksheet, merged and centered: Ski Molding Project - Projected 4-Year Cash Flow Estimate 3. Enter the sales volume for each year, assuming sales in year 1 of 140,000 pains. The sales volume for each successive year is assumed to be 5% more than the previous yeu 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 sub sequent years, 5. Calculate the cost savings as the number of pairs of skis sold multiplied by the cor- savings per ski. 6. On a separate worksheet named loan (similar to the Loan worksheet shows Figure 6.15), create an amortization table listing the principal and interest paymer and remaining principal in each monthly period, assuming The one will borrow the moncy under the following terms: 400 Chapter 6 Evaluating the Financial Impact of loans and Investments Funding will be arranged for the entire cost of this investment (the cost of the machine ples installation) at 5.25% interest compounded monthly paid out in full in equal monthly installments over four years. 7. On the cashflow worksheet, calculate the cumulative interest expense for year 1 loan will start at the beginning of year 1 January), and all payments will be made at (the interest portion of the loan payments for the corresponding year). Assume the the end of each period. Write your formula so that it can be copied across the row to automatically calculate these values for years 2 through 4. 8. Calculate the depreciation for this equipment using the straight line depreciation method. The cquipment is assumed to have an 8-year life with a salvage value of $50,000 at the end of that period. For cost, use the cost of the machine plus instal- 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 TheZone is taxed at a 35% rate. Use a global named range to store this value. lation. Set up a separate worksheet named depreciation to store these values (similar 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 lute and relative cell referencing so that your formulas will work for each of the cash a final projected cash flow estimate for each of the four years. Use the correct abso- 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. B E D F 1 Ski Molding Project - Projected 4-Year Cash Flow Estimate 1 2 Year 2 3 140,000 3 Sales Volume 147,000 154,350 162,068 $ 4 Cost Savings Per Pair 10.00 $ 10.00 $ 10.00 10.00 $ 5 5 6 Cost Savings $1,400,000.00 $ 1,470,000.00 $ 1,543,500.00 $ 1,620,680.00 7 7 8 Interest Expense (70,501.51) (51,889.36) (32,276.21) (11,608,23) 8 9 Depreciation (181 250.00 (181,250.00 (181,250.00) (181,250.00) -9 10 Net Cost Savings 1.148, 248.49 1,236,860.64 1,329,973.79 1,427 821.77 30 11 31 12 Additional Tax Owed (401,886.97 (432.901.22 (465,490.83) 499.737.62) 32 13 Savings After Taxes 746,361.52 803,959.41 864,482.96 928,084.15 33 14 34 15 Add Back Depreciation 181,250.00 181,250.00 181,250.00 181,250.00 35 16 Subtract Principal Payments (346,06731) (364,679,47) 17 (384,292.62) (404,960.60) 36 37 18 Projected Cash Flow Estimate $ 581.544.20 $ 620,529.95 $ 661,440.34 $ 704,373.55 19 38 cashflow loan depreciation M N Amortization Table Loan Clown 1TD.OU) AT Interest Rate 1 Londration 4 Nunt of prod. por ME 12 Enden Valletta Mont Payment : Raming Buod Arincipal Interest Phant Principal Payment $ 1,500,000 X210) 528 151.52 2 11t. ! 6.4303 1282747 3 144357370 [CL1ER 28,3984 4 1.41515 28 19 191.39 ) = 1 ULLAH (d B) () 0 1.368.005 12 15,9412 W720) 21,309250.33 5,8153 ) 8 1,300353.65 [CE CE (29025 11) 91,271,308.54 15581,97 (29,15209 10 1,24211844 (54344 (29,273.6 11 1,212870.81 15.308 34 (PAATH 121183460 08 5177.33) (23538.39 13 1,153932.09 [5] 129,005.01) 141 1,124 2017.07 4.918 67 (29,79540 151 1,09447167 4.788.31) (29,92578 16 1,084,5 45.92 1485729 ) 17 1,034419 23 1452589 3018818) 18.1.004301.08 14.39382 130 320 25 973,980 80 14201.17 0 , 943,527.90 14.127.93 130503131 21 912941.77 13.994.12 (30 7 1996 882221 82 38587 (20 854 35 22 851367.47 (37244 30 989 34 820 37813 (3.589 15 (31.12491 78925372 325) 13126109 20 5/2013 2.316.22 3.1 397 85 72859428 12.178.85 131535.22 295059.08 22.040.88 (31.ETag 883 38587 (2 90231 (31 8117 631574.12 12.782 14 31950.96 =93823 13 12.802.35 122 090.722 58753247 12.48295 12223111) 535301.35 12.341.96 132 3724132 502929 28 12.200122 12251375 470415.47 12052 33 2 00: 43771947 11.915.20 132.798 87 404900,00 41.771.709 132.94232 372018.22 1. BET 133,086.43 338,931 74 [12B 33.231 24 30.5.700.00 11.327.44 133, 37e.es 27232 87 (1.191.42 ( F) 238 801 22 11.044 702 133.ees 31 20.5. 131.91 1897 45 123.8 16.02 1711315.29 749,50) 23 24 ) 137340.72 1600.911 134 113.10 103237.50 1451.00 134202 403 47 69975.10 130172 13441230) 48 (15121) (34,502.30 EC2 F H A 1 Depreciation Values 2 Range Name Value 3 Capital $1.500.000 4 Salvage $ 50.000 5 Life 8 Chapter 6 Evaluating the Steps To Success: Level 2 developed in Europe to mold skis more precisely and less expensively than the The Equipment division at The Zone is looking into a new piece of equipment to be S1,500,000. The projected cost savings are expected to be S10.00 Technology being used at TheZone. The cost of the machine plus installations skis, You have been asked to estimate a projected cash flow savings if anytha perp generated by this proposed project over the next four years. Complete the following 1. Open the workbook named Ski.xlsx in the Chapter 6 folder, and then save the as Ski Molder Cash Flow Estimate.xlsx. The structure for the projected care estimate is provided on Sheetl, as shown in Figure 6.26. Figure 6.26: Worksheet for ski molder projected cash flow estimate E 2 A 31 2 Some Car Savis Expense Detection 9 Cossavings 10 11 Aastaar wed Savines ATES 14 Add Back Depreciation 15 d Principal Payments 16 17 Proedd Cash Flow Estate 2. Rename the Sheetl worksheet as cashflow. Insert the following title at the top of the worksheet, merged and centered: Ski Molding Project - Projected 4-Year Cash Flow Estimate 3. Enter the sales volume for each year, assuming sales in year 1 of 140,000 pains. The sales volume for each successive year is assumed to be 5% more than the previous yeu 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 sub sequent years, 5. Calculate the cost savings as the number of pairs of skis sold multiplied by the cor- savings per ski. 6. On a separate worksheet named loan (similar to the Loan worksheet shows Figure 6.15), create an amortization table listing the principal and interest paymer and remaining principal in each monthly period, assuming The one will borrow the moncy under the following terms: 400 Chapter 6 Evaluating the Financial Impact of loans and Investments Funding will be arranged for the entire cost of this investment (the cost of the machine ples installation) at 5.25% interest compounded monthly paid out in full in equal monthly installments over four years. 7. On the cashflow worksheet, calculate the cumulative interest expense for year 1 loan will start at the beginning of year 1 January), and all payments will be made at (the interest portion of the loan payments for the corresponding year). Assume the the end of each period. Write your formula so that it can be copied across the row to automatically calculate these values for years 2 through 4. 8. Calculate the depreciation for this equipment using the straight line depreciation method. The cquipment is assumed to have an 8-year life with a salvage value of $50,000 at the end of that period. For cost, use the cost of the machine plus instal- 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 TheZone is taxed at a 35% rate. Use a global named range to store this value. lation. Set up a separate worksheet named depreciation to store these values (similar 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 lute and relative cell referencing so that your formulas will work for each of the cash a final projected cash flow estimate for each of the four years. Use the correct abso- 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. B E D F 1 Ski Molding Project - Projected 4-Year Cash Flow Estimate 1 2 Year 2 3 140,000 3 Sales Volume 147,000 154,350 162,068 $ 4 Cost Savings Per Pair 10.00 $ 10.00 $ 10.00 10.00 $ 5 5 6 Cost Savings $1,400,000.00 $ 1,470,000.00 $ 1,543,500.00 $ 1,620,680.00 7 7 8 Interest Expense (70,501.51) (51,889.36) (32,276.21) (11,608,23) 8 9 Depreciation (181 250.00 (181,250.00 (181,250.00) (181,250.00) -9 10 Net Cost Savings 1.148, 248.49 1,236,860.64 1,329,973.79 1,427 821.77 30 11 31 12 Additional Tax Owed (401,886.97 (432.901.22 (465,490.83) 499.737.62) 32 13 Savings After Taxes 746,361.52 803,959.41 864,482.96 928,084.15 33 14 34 15 Add Back Depreciation 181,250.00 181,250.00 181,250.00 181,250.00 35 16 Subtract Principal Payments (346,06731) (364,679,47) 17 (384,292.62) (404,960.60) 36 37 18 Projected Cash Flow Estimate $ 581.544.20 $ 620,529.95 $ 661,440.34 $ 704,373.55 19 38 cashflow loan depreciation M N Amortization Table Loan Clown 1TD.OU) AT Interest Rate 1 Londration 4 Nunt of prod. por ME 12 Enden Valletta Mont Payment : Raming Buod Arincipal Interest Phant Principal Payment $ 1,500,000 X210) 528 151.52 2 11t. ! 6.4303 1282747 3 144357370 [CL1ER 28,3984 4 1.41515 28 19 191.39 ) = 1 ULLAH (d B) () 0 1.368.005 12 15,9412 W720) 21,309250.33 5,8153 ) 8 1,300353.65 [CE CE (29025 11) 91,271,308.54 15581,97 (29,15209 10 1,24211844 (54344 (29,273.6 11 1,212870.81 15.308 34 (PAATH 121183460 08 5177.33) (23538.39 13 1,153932.09 [5] 129,005.01) 141 1,124 2017.07 4.918 67 (29,79540 151 1,09447167 4.788.31) (29,92578 16 1,084,5 45.92 1485729 ) 17 1,034419 23 1452589 3018818) 18.1.004301.08 14.39382 130 320 25 973,980 80 14201.17 0 , 943,527.90 14.127.93 130503131 21 912941.77 13.994.12 (30 7 1996 882221 82 38587 (20 854 35 22 851367.47 (37244 30 989 34 820 37813 (3.589 15 (31.12491 78925372 325) 13126109 20 5/2013 2.316.22 3.1 397 85 72859428 12.178.85 131535.22 295059.08 22.040.88 (31.ETag 883 38587 (2 90231 (31 8117 631574.12 12.782 14 31950.96 =93823 13 12.802.35 122 090.722 58753247 12.48295 12223111) 535301.35 12.341.96 132 3724132 502929 28 12.200122 12251375 470415.47 12052 33 2 00: 43771947 11.915.20 132.798 87 404900,00 41.771.709 132.94232 372018.22 1. BET 133,086.43 338,931 74 [12B 33.231 24 30.5.700.00 11.327.44 133, 37e.es 27232 87 (1.191.42 ( F) 238 801 22 11.044 702 133.ees 31 20.5. 131.91 1897 45 123.8 16.02 1711315.29 749,50) 23 24 ) 137340.72 1600.911 134 113.10 103237.50 1451.00 134202 403 47 69975.10 130172 13441230) 48 (15121) (34,502.30 EC2 F H A 1 Depreciation Values 2 Range Name Value 3 Capital $1.500.000 4 Salvage $ 50.000 5 Life 8