Pick at least two variables in the spreadsheet to "flex" by 5% up and down (for variables that cover more than one year please remember to change all years). Move these variables up 5% and recorded the new net present value. Take the difference between the new net present value and the original net present value and divide by the original net present value to get the percentage change in net present value. Record what you changed and the percentage change in net present value.

 

The part above is done,I'm attaching screenshots of it. I need help with the part below.

The 5% increase and decrease have been completed for both projects. I also calculated the percentage change in NPV. There are 4 highlighted rows on each of the four pages.

 

Use the paragraph to discuss the first variable that your team "flexed". Please describe the variable, the percentage change in the variable (if different than 5%), and the percentage change in the net present value as a result of the change. Remember to note if it is a decrease or increase in Net Present Value. Use an another paragraph to describe the projects sensitivity to the second variable. The next paragraphs should discuss two actions that your group believes management can do to mitigate the risk of this project.

      

Transcribed Image Text:

B75 2 Year 4 Acquisition Stage Assumptions 5 Building Modification 6 Cost of Land 7 Cost of Equipment 8 Promotional Advertising 9 Employee Training (8 emp, $20/hr, 20 hrs training) 10 Additional Investment in Working Capital 11 Working Capital Balance 12 Incremental Invest in working Capital A 14 Operating Stage Assumption 15 Additional sales Resulting from Expansion 16 Additional Fixed cost from expansion 17 Variable cost of additional product 18 Depreciation on Building 19 Depreciation on Equipment 20 Tax Rate 21 22 23 Acquisition Stage Cash Flow 25 Year 27 Cost of Building Modification (40 yr depreciation) 28 Cost of Land (not depreciated) 29 Cost of equipment (depreciated to 0 over 8 years) 30 Promotional Advertising 31 Employee Training 32 Additional Investment in Working Capital 33 Total Acquisition Stage Cash Flow 34 35 36 Operating Stage Cash Flow 38 Year 40 Additional sales Resulting from Expansion 41 Variable cost of additional product 42 Additional Fixed cost from expansion 43 Depreciation on Building 44 Deprec Equipment 45 Taxable Income 46 Taxes 47 After Tax Income 48 Depreciation Addback 49 Operating Stage Cash Flow 50 51 52 Disposition Stage Cash Flow 54 Year 56 Continuing Value 57 58 Total Cash Flows 59 60 NPV 61 Profitability Index 62 IRR fx 63 Payback Period 64 65 Present Value Payback Period 67 68 69 70 71 72 73 Ś Ś $ $ $ $ $ $ $ $ $ $ $ $ $ $ B 7.43 0 Wk6 Building Modification (700,000) (100,000) (50,000) (3,200) 7% 36,400 $ (36,400) $ 0 (700,000) (100,000) (50,000) (3,200) (36,400) $ (889,600) $ 0 0 356,222.16 14.33% $ $ 1.40 ($889,600.00) ($889,600.00) (889,600) $ $ $ $ $ $ $ $ $ $ $ с 1 7% 38,948 $ (2,726) $ 520,000 100,000 60% 2.5% 12.5% 0.28 1 1 $ 1 (2,726) $ (2,726) $ 520,000 $ (312,000) $ (100,000) $ (17,500) $ (12,500) $ 78,000 $ (21,840) $ 56,160 $ 30,000 $ 86,160 $ $ (889,600) $ (806,166) $ 7.02 83,434 $ D 2 7% 41,674 $ (2,917) $ 8% 100,000 $ 60% 2.5% 12.5% 0.28 2 2 (2,917) $ (2,917) $ 561,600 $ (336,960) $ (100,000) $ (17,500) $ (12,500 $ 94,640 $ (26,499) $ 68,141 $ 30,000 $ 98,141 $ 2 95,224 $ (710,943) $ E 3 7% 8% 3 60% 2.5% 12.5% 0.28 3 44,592 $ (3,121) $ 100,000 $ 3 (3,121) S (3,121) $ 606,528 $ (363,917) $ (100,000) $ (17,500) $ (12,500) $ 112,611 $ (31,531) $ 81,080 $ 30,000 $ 111,080 $ 107,959 $ (602,984) $ F Wk8 Bldg Modification (5% up) 4 7% 47,713 $ (3,340) $ 8% 100,000 $ 60% 2.5% 12.5% 0.28 4 4 (3,340) $ (3,340) $ 655,050 $ (393,030) $ (100,000) $ (17,500) $ (12,500) $ 132,020 $ (36,966) $ 95,054 $ 30,000 $ 125,054 $ 4 121,715 $ (481,270) $ $77,253.37 $81,638.88 $85,701.06 $89,463.84 ($812,346.63) ($730,707.75) ($645,006.68) ($55 .85) G 5 7% 51,053 $ (3,574) $ 8% 100,000 $ 60% 2.5% 12.5% 0.28 5 5 (3,574) $ (3,574) $ 707,454 $ (424,473) $ (100,000) $ (17,500) $ (12,500) $ 152,982 $ (42,835) $ 110,147 $ 30,000 $ 140,147 $ 5 136,573 $ (344,696) $ H 6 7% 54,627 $ (3,824) $ 8% 100,000 $ 60% 2.5% 12.5% 0.28 6 6 (3,824) $ (3,824) $ 764,051 $ (458,430) $ (100,000) $ (17,500) $ (12,500) $ 175,620 $ (49,174) $ 126,447 $ 30,000 $ 156,447 $ 6 152,623 $ (192,074) $ I 7 7% 58,450 $ (4,092) $ 8% 100,000 $ 60% 2.5% 12.5% 0.28 7 7 (4,092) $ (4,092) $ 825,175 $ (495,105) $ (100,000) $ (17,500) $ (12,500) 200,070 $ (56,020) $ 144,050 $ 30,000 $ 174,050 $ 7 169,959 $ (22,115) $ J Wk8 Bldg Modification (5% down) 8 7% 62,542 (4,378) 8% 100,000 60% 2.5% 12.5% 0.28 8 8 (4,378) (4,378) 891,189 (534,713) (100,000) (17,500) (12,500) 226,475 8 $ 965,311.61 (63,413) 163,062 30,000 193,062 1,153,996 1,131,881 $92,949.37 $96,178.20 $99,169.31 $623,468.13 93.47) 267,245.97) $356,222.16 + K L M N O P Q Re-doing the kitchen, seating area, additional seating, more counter space Kitchen equipment, new appliance, Local Advertising (Only at 1 store) 8 employees, $20/hr, 20 hours training R B75 2 Year 4 Acquisition Stage Assumptions 5 Building Modification 6 Cost of Land 7 Cost of Equipment 8 Promotional Advertising 9 Employee Training (8 emp, $20/hr, 20 hrs training) 10 Additional Investment in Working Capital 11 Working Capital Balance 12 Incremental Invest in working Capital A 14 Operating Stage Assumption 15 Additional sales Resulting from Expansion 16 Additional Fixed cost from expansion 17 Variable cost of additional product 18 Depreciation on Building 19 Depreciation on Equipment 20 Tax Rate 21 22 23 Acquisition Stage Cash Flow 25 Year 27 Cost of Building Modification (40 yr depreciation) 28 Cost of Land (not depreciated) 29 Cost of equipment (depreciated to 0 over 8 years) 30 Promotional Advertising 31 Employee Training 32 Additional Investment in Working Capital 33 Total Acquisition Stage Cash Flow 34 35 36 Operating Stage Cash Flow 38 Year 40 Additional sales Resulting from Expansion 41 Variable cost of additional product 42 Additional Fixed cost from expansion 43 Depreciation on Building 44 Deprec Equipment 45 Taxable Income 46 Taxes 47 After Tax Income 48 Depreciation Addback 49 Operating Stage Cash Flow 50 51 52 Disposition Stage Cash Flow 54 Year 56 Continuing Value 57 58 Total Cash Flows 59 60 NPV 61 Profitability Index 62 IRR fx 63 Payback Period 64 65 Present Value Payback Period 67 68 69 70 71 72 73 Ś Ś $ $ $ $ $ $ $ $ $ $ $ $ $ $ B 7.43 0 Wk6 Building Modification (700,000) (100,000) (50,000) (3,200) 7% 36,400 $ (36,400) $ 0 (700,000) (100,000) (50,000) (3,200) (36,400) $ (889,600) $ 0 0 356,222.16 14.33% $ $ 1.40 ($889,600.00) ($889,600.00) (889,600) $ $ $ $ $ $ $ $ $ $ $ с 1 7% 38,948 $ (2,726) $ 520,000 100,000 60% 2.5% 12.5% 0.28 1 1 $ 1 (2,726) $ (2,726) $ 520,000 $ (312,000) $ (100,000) $ (17,500) $ (12,500) $ 78,000 $ (21,840) $ 56,160 $ 30,000 $ 86,160 $ $ (889,600) $ (806,166) $ 7.02 83,434 $ D 2 7% 41,674 $ (2,917) $ 8% 100,000 $ 60% 2.5% 12.5% 0.28 2 2 (2,917) $ (2,917) $ 561,600 $ (336,960) $ (100,000) $ (17,500) $ (12,500 $ 94,640 $ (26,499) $ 68,141 $ 30,000 $ 98,141 $ 2 95,224 $ (710,943) $ E 3 7% 8% 3 60% 2.5% 12.5% 0.28 3 44,592 $ (3,121) $ 100,000 $ 3 (3,121) S (3,121) $ 606,528 $ (363,917) $ (100,000) $ (17,500) $ (12,500) $ 112,611 $ (31,531) $ 81,080 $ 30,000 $ 111,080 $ 107,959 $ (602,984) $ F Wk8 Bldg Modification (5% up) 4 7% 47,713 $ (3,340) $ 8% 100,000 $ 60% 2.5% 12.5% 0.28 4 4 (3,340) $ (3,340) $ 655,050 $ (393,030) $ (100,000) $ (17,500) $ (12,500) $ 132,020 $ (36,966) $ 95,054 $ 30,000 $ 125,054 $ 4 121,715 $ (481,270) $ $77,253.37 $81,638.88 $85,701.06 $89,463.84 ($812,346.63) ($730,707.75) ($645,006.68) ($55 .85) G 5 7% 51,053 $ (3,574) $ 8% 100,000 $ 60% 2.5% 12.5% 0.28 5 5 (3,574) $ (3,574) $ 707,454 $ (424,473) $ (100,000) $ (17,500) $ (12,500) $ 152,982 $ (42,835) $ 110,147 $ 30,000 $ 140,147 $ 5 136,573 $ (344,696) $ H 6 7% 54,627 $ (3,824) $ 8% 100,000 $ 60% 2.5% 12.5% 0.28 6 6 (3,824) $ (3,824) $ 764,051 $ (458,430) $ (100,000) $ (17,500) $ (12,500) $ 175,620 $ (49,174) $ 126,447 $ 30,000 $ 156,447 $ 6 152,623 $ (192,074) $ I 7 7% 58,450 $ (4,092) $ 8% 100,000 $ 60% 2.5% 12.5% 0.28 7 7 (4,092) $ (4,092) $ 825,175 $ (495,105) $ (100,000) $ (17,500) $ (12,500) 200,070 $ (56,020) $ 144,050 $ 30,000 $ 174,050 $ 7 169,959 $ (22,115) $ J Wk8 Bldg Modification (5% down) 8 7% 62,542 (4,378) 8% 100,000 60% 2.5% 12.5% 0.28 8 8 (4,378) (4,378) 891,189 (534,713) (100,000) (17,500) (12,500) 226,475 8 $ 965,311.61 (63,413) 163,062 30,000 193,062 1,153,996 1,131,881 $92,949.37 $96,178.20 $99,169.31 $623,468.13 93.47) 267,245.97) $356,222.16 + K L M N O P Q Re-doing the kitchen, seating area, additional seating, more counter space Kitchen equipment, new appliance, Local Advertising (Only at 1 store) 8 employees, $20/hr, 20 hours training R

Answer rating: 100% (QA)

For the first variable that our team flexed we increased the Cost of Equipment by 5 for both Project ... View the full answer