Hello,

Please help me with this spreadsheet.

Thank you

Applying Sustainability Input to Project Analysis Overall Objective: To see how sustainability adjustments can impact the viability of projects. Answer the questions at the bottom of the page. Source: 'Chapter 11. Tool Kit for Cash Flow Estimation and Risk Analysis ANALYSIS OF AN EXPANSION PROJECT (Section 11.2) Should I add another piece of machinery? Is it viable to do so? The model uses the "Base-Case" input values shown below to calculate the NPV and other performance measures. The main model assumes that the firm uses accelerated depreciation. A modified version of the model, shown in Columns J through R, shows the results if the firm elects to use straight-line depreciation. This analysis demonstrates that accelerated depreciation improves project profitability. Part 1. Inputs and Key Results Inputs Base-Case Equipment cost Salvage value, equipment, Year 4 Opportunity cost Key Results $4,080 $300 $0 Externalities (cannibalization) $446 11.61% 10.34% NPV IRR MIRR $0 1.09 PI Payback Discounted payback Units sold, Year 1 605 Annual change in units sold, after Year 1 4.00% Sales price per unit, Year 1 $11.60 Annual change in sales price, after Year 1 2.00% Variable cost per unit (VC), Year 1 $6.00 Annual change in VC, after Year 1 2.00% Nonvariable cost (Non-VC), Year 1 $2,000 Annual change in Non-VC, after Year 1 2.00% Project WACC 8.00% Tax rate 40.00% Working capital as % of next year's sales 12.65% 3.33 3.76 Change any number in blue in the base case and it will change the values of the criteria for viability. DO NOT CHANGE NPV, IRR, MIRR, PI, Payback, and Discounted Payback 60500.00% If you change any of the blue values in the Input Section shown above, the model below will change instantly, causing changes in NPV and other output variables. You can see the effect in the Key Results box shown above. If you change an input value but later want to return to the base case, use Scenario Manager to select the Base-Case. In Excel 2003, select Tools, Scenarios. In Excel 2007, select Data, What-If-Analysis, Scenario Manager. Figure 11-2. Analysis of a New (Expansion) Project: Cash Flows and Performance Measures (Dollars in Thousands) Part 2. Cash Flows and Performance Measures Variables Used in the Cash Flow Forecast Unit sales Sales price per unit Variable cost per unit Nonvariable costs (excluding depreciation) Investment Outlays at Time = 0 Equipment Initial investment in working capital Opportunity cost, after taxes 0 0 -$4,080 -888 0 Model Using Straight-Line Depreciation 1 2 3 4 605 629 654 $11.60 $11.83 $12.07 $6.00 $6.12 $6.24 $2,000 $2,040 $2,081 Cash Flows At End of Year 1 2 3 681 $12.31 $6.37 $2,122 4 Sales revenues = Units Price/unit Variable costs = Units Cost/unit Nonvariable costs (excluding depreciation) Depreciation: Accelerated, from table below Operating profit (EBIT) Taxes on operating profit Net operating profit after taxes Add back depreciation Opportunity cost, after taxes Cannibalization or complementary effects, after taxe Salvage value (taxed as ordinary income) Tax on salvage value (SV is taxed at 40%) Change in WC: Outflow (-) or recovery (+) Unit sales Sales price per unit Variable cost per unit Nonvariable costs (excluding depreciation) Investment Outlays at Time = 0 Equipment Initial investment in working capital Opportunity cost, after taxes Net Cash Flows Over the Project's Life 0 1 2 3 4 Variables Used in the Cash Flow Forecast 0 -$4,080 -888 0 605 629 654 $11.60 $11.83 $12.07 $6.000 $6.120 $6.242 $2,000 $2,040 $2,081 Cash Flows At End of Year 1 2 3 681 $12.31 $6.367 $2,122 4 Net Cash Flows Over the Project's Life $7,018 3,630 2,000 1,346 $42 17 $25 1,346 0 0 $7,445 3,851 2,040 1,836 -$282 -113 -$169 1,836 0 0 $7,897 4,085 2,081 612 $1,120 448 $672 612 0 0 -54 -57 -61 $8,377 4,333 2,122 286 $1,636 655 $982 286 0 0 300 -120 1,060 Sales revenues = Units Price/unit Variable costs = Units Cost/unit Nonvariable costs (excluding depreciation) Depreciation: Accelerated, from table below Operating profit (EBIT) Taxes on operating profit Net operating profit after taxes Add back depreciation Opportunity cost, after taxes Cannibalization or complementary effects, after taxes Salvage value (taxed as ordinary income) Tax on salvage value (SV is taxed at 40%) Change in WC: Outflow (-) or recovery (+) $7,018 3,630 2,000 1,020 $368 147 $221 1,020 0 0 $7,445 3,851 2,040 1,020 $534 214 $320 1,020 0 0 $7,897 4,085 2,081 1,020 $712 285 $427 1,020 0 0 -54 -57 -61 $8,377 4,333 2,122 1,020 $902 361 $541 1,020 0 0 300 -120 1,060 Project net cash flows: Time Line -$4,968 $1,317 $1,610 Project Evaluation Accelerated Results Formulas $446 NPV =NPV(E59,F101:I101)+E101 11.61% IRR =IRR(E101:I101) 10.34% MIRR =MIRR(E101:I101,E59,E59) 1.09 Profitability index =NPV(E59,F101:I101)/(-E101) 3.33 Payback =PERCENTRANK(E112:I112,0,6)*I111 Discounted payback 3.76 =PERCENTRANK(E114:I114,0,6)*I111 Year: 0 1 2 Calculations for Payback Cumulative cash flows for payback -$4,968 -$3,650 -$2,041 Discounted cash flows for disc. paybac -$4,968 $1,220 $1,380 Cumulative discounted cash flows -$4,968 -$3,748 -$2,368 Accelerated Depreciation Depreciable basis: $4,080 Rate/year 33% 45% Dollars/year $1,346 $1,836 $1,223 $2,507 Project net cash flows: Time Line -$4,968 $1,187 Straight Line Results $390 11.01% 10.06% 1.08 3.40 Note: see Ch 10 Tool Kit.xls for a detailed explanation of how to use 3.81 the PERCENTRANK function to calculate payback. 3 4 Calculations for Payback Year: 0 1 -$818 $1,689 Cumulative cash flows for payback -$4,968 -$3,781 $971 $1,843 Discounted cash flows for discounted payback -$4,968 $1,099 -$1,397 $446 Cumulative discounted cash flows -$4,968 -$3,869 Straight-Line Depreciation 15% 7% Depreciable basis: $4,080 Rate/year 25% $612 $286 Dollars/year $1,020 $1,283 $1,386 $2,801 2 -$2,498 $1,100 -$2,769 3 -$1,112 $1,100 -$1,668 4 $1,689 $2,059 $390 25% $1,020 25% $1,020 25% $1,020 Taxation of Salvage We use two ways to deal with depreciation: MACRS and Straight-Lin Suppose GPC terminates operations before the equipment is fully depreciated. The after-tax salvage value depends upon the price at which GPC can sell the equipment and upon the book value of the equipment (i.e., the original basis less all previous depreciation charges). See below for calculations of yearly book values. Year: Beginning book valu Depreciation Ending book value 1 $4,080 $1,346 $2,734 2 $2,734 $1,836 $898 3 $898 $612 $286 4 $286 $286 $0 If GPC terminates at Year 2 and can sell the equipment for $898, what is the after-tax salvage cash flow? What if GPC can only sell the equipment for $98 at Year 2? Cash from sale Tax expense (credit) Net cash flow from salvage Case 2: Loss $98.00 $897.60 -$799.60 -$319.84 $898.00 $0.16 $897.84 Market value when salvaged at Year 2 Book value when salvaged at Year 2 Expected gain or loss Tax expense (credit) Case 1: Gain $898.00 $897.60 $0.40 $0.16 $98.00 -$319.84 $417.84 Questions: 1. Due to increased regulations for pollution control, equipment costs are increased by 20 percent. How does this impact the viablility of the project? Redo the analysis above and put the values of your project evaluation criteria below. Just cut and paste without the formulas. Project Evaluation Results NPV IRR MIRR Profitability index Payback Discounted payback Accelerated Formulas =NPV(E59,F101:I101)+E101 =IRR(E101:I101) =MIRR(E101:I101,E59,E59) =NPV(E59,F101:I101)/(-E101) =PERCENTRANK(E112:I112,0,6)*I111 =PERCENTRANK(E114:I114,0,6)*I111 Straight Line Results Answer: 2. Due to your sustainability improvement done in Question 1, your sales went up by 10 percent. What the the performance of your project now? Redo the analysis above and put the values of your project evaluation criteria below. Just cut and paste without the formulas. Project Evaluation Results NPV IRR MIRR Profitability index Payback Discounted payback Accelerated Formulas =NPV(E59,F101:I101)+E101 =IRR(E101:I101) =MIRR(E101:I101,E59,E59) =NPV(E59,F101:I101)/(-E101) =PERCENTRANK(E112:I112,0,6)*I111 =PERCENTRANK(E114:I114,0,6)*I111 Straight Line Results Answer: Question 3. Because of you sustainability friendly project, you were able to secure a lower rate of financing causing your WACC to decrease by 10 percent. What is the performance of the project now. Do keep the changes made in Questions 1 and 2. Project Evaluation Results NPV IRR MIRR Profitability index Payback Discounted payback Accelerated Formulas =NPV(E59,F101:I101)+E101 =IRR(E101:I101) =MIRR(E101:I101,E59,E59) =NPV(E59,F101:I101)/(-E101) =PERCENTRANK(E112:I112,0,6)*I111 =PERCENTRANK(E114:I114,0,6)*I111 Straight Line Results Answer: 4. Using the findings from Questions 1, 2, and 3, make some general statements about theimplications of sustainability and financial management for corporations. 1 2 3 4 5 etc. ine. Applying Sustainability Input to Project Analysis Overall Objective: To see how sustainability adjustments can impact the viability of projects. Answer the questions at the bottom of the page. Source: 'Chapter 11. Tool Kit for Cash Flow Estimation and Risk Analysis ANALYSIS OF AN EXPANSION PROJECT (Section 11.2) Should I add another piece of machinery? Is it viable to do so? The model uses the "Base-Case" input values shown below to calculate the NPV and other performance measures. The main model assumes that the firm uses accelerated depreciation. A modified version of the model, shown in Columns J through R, shows the results if the firm elects to use straight-line depreciation. This analysis demonstrates that accelerated depreciation improves project profitability. Part 1. Inputs and Key Results Inputs Base-Case Equipment cost Salvage value, equipment, Year 4 Opportunity cost NPV IRR MIRR $300 $0 Externalities (cannibalization) Units sold, Year 1 Annual change in units sold, after Year 1 Sales price per unit, Year 1 Annual change in sales price, after Year Variable cost per unit (VC), Year 1 Annual change in VC, after Year 1 Nonvariable cost (Non-VC), Year 1 Annual change in Non-VC, after Year 1 Project WACC Tax rate Working capital as % of next year's sales Key Results $4,080 $0 $446 11.61% 10.34% DO NOT CHANGE NPV, IRR, MIRR, PI, Payback, and Discounted Payback 1.09 PI Payback 605 4.00% $11.60 2.00% $6.00 2.00% $2,000 2.00% 8.00% 40.00% 12.65% Change any number in blue in the base case and it will change the values of the criteria for viability. Discounted payback 3.33 3.76 $ 605 If you change any of the blue values in the Input Section shown above, the model below will change instantly, causing changes in NPV and other output variables. You can see the effect in the Key Results box shown above. If you change an input value but later want to return to the base case, use Scenario Manager to select the Base-Case. In Excel 2003, select Tools, Scenarios. In Excel 2007, select Data, What-If-Analysis, Scenario Manager. Figure 11-2. Analysis of a New (Expansion) Project: Cash Flows and Performance Measures (Dollars in Thousands) Part 2. Cash Flows and Performance Measures Model Using Straight-Line Depreciation Variables Used in the Cash Flow Forecas Unit sales Sales price per unit Variable cost per unit Nonvariable costs (excluding depreciation) Variables Used in the Cash Flow Forecast Investment Outlays at Time = 0 Equipment Initial investment in working capital Opportunity cost, after taxes 0 0 -$4,080 -888 0 1 2 3 605 629 654 $11.60 $11.83 $12.07 $6.00 $6.12 $6.24 $2,000 $2,040 $2,081 Cash Flows At End of Year 1 2 3 4 681 $12.31 $6.37 $2,122 4 Sales revenues = Units Price/unit Variable costs = Units Cost/unit Nonvariable costs (excluding depreciation) Depreciation: Accelerated, from table below Operating profit (EBIT) Taxes on operating profit Net operating profit after taxes Add back depreciation Opportunity cost, after taxes Cannibalization or complementary effects, after tax Salvage value (taxed as ordinary income) Tax on salvage value (SV is taxed at 40%) Unit sales Sales price per unit Variable cost per unit Nonvariable costs (excluding depreciation) Investment Outlays at Time = 0 Equipment Initial investment in working capital Opportunity cost, after taxes Net Cash Flows Over the Project's Life 0 0 -$4,080 -888 0 1 2 3 605 629 654 $11.60 $11.83 $12.07 $6.000 $6.120 $6.242 $2,000 $2,040 $2,081 Cash Flows At End of Year 1 2 3 4 681 $12.31 $6.367 $2,122 4 Net Cash Flows Over the Project's Life $7,018 3,630 2,000 1,346 $42 17 $25 1,346 0 0 $7,445 3,851 2,040 1,836 -$282 -113 -$169 1,836 0 0 $7,897 4,085 2,081 612 $1,120 448 $672 612 0 0 $8,377 4,333 2,122 286 $1,636 655 $982 286 0 0 300 -120 Sales revenues = Units Price/unit Variable costs = Units Cost/unit Nonvariable costs (excluding depreciation) Depreciation: Straight Line, from table below Operating profit (EBIT) Taxes on operating profit Net operating profit after taxes Add back depreciation Opportunity cost, after taxes Cannibalization or complementary effects, after taxes Salvage value (taxed as ordinary income) Tax on salvage value (SV is taxed at 40%) $7,018 3,630 2,000 1,020 $368 147 $221 1,020 0 0 $7,445 3,851 2,040 1,020 $534 214 $320 1,020 0 0 $7,897 4,085 2,081 1,020 $712 285 $427 1,020 0 0 $8,377 4,333 2,122 1,020 $902 361 $541 1,020 0 0 300 -120 Change in WC: Outflow (-) or recovery (+) Project net cash flows: Time Line -54 -$4,968 Project Evaluation -57 -61 1,060 $1,317 $1,610 $1,223 $2,507 Accelerated Change in WC: Outflow (-) or recovery (+) Project net cash flows: Time Line -54 -$4,968 -57 -61 1,060 $1,187 $1,283 $1,386 $2,801 2 -$2,498 $1,100 -$2,769 3 -$1,112 $1,100 -$1,668 4 $1,689 $2,059 $390 25% $1,020 25% $1,020 Straight Line Formulas NPV =NPV(E59,F101:I101)+E101 $446 IRR =IRR(E101:I101) 11.61% MIRR =MIRR(E101:I101,E59,E59) 10.34% Profitability index =NPV(E59,F101:I101)/(-E101) 1.09 Payback =PERCENTRANK(E112:I112,0,6)*I111 3.33 =PERCENTRANK(E114:I114,0,6)*I111 Discounted payback 3.76 Year: 0 1 2 Calculations for Payback Cumulative cash flows for payback -$4,968 -$3,650 -$2,041 Discounted cash flows for disc. payba -$4,968 $1,220 $1,380 Cumulative discounted cash flows -$4,968 -$3,748 -$2,368 Accelerated Depreciation Depreciable basis: $4,080 Rate/year 33% 45% Dollars/year $1,346 $1,836 Results Results $390 11.01% 10.06% 1.08 3.40 3.81 3 -$818 $971 -$1,397 15% $612 Note: see Ch 10 Tool Kit.xls for a detailed explanation of how to use the PERCENTRANK function to calculate payback. 4 Calculations for Payback Year: 0 1 $1,689 Cumulative cash flows for payback -$4,968 -$3,781 $1,843 Discounted cash flows for discounted paybac -$4,968 $1,099 $446 Cumulative discounted cash flows -$4,968 -$3,869 Straight-Line Depreciation 7% Depreciable basis: $4,080 Rate/year 25% $286 Dollars/year $1,020 25% $1,020 Taxation of Salvage We use two ways to deal with depreciation: MACRS and Straight-L Suppose GPC terminates operations before the equipment is fully depreciated. The aftertax salvage value depends upon the price at which GPC can sell the equipment and upon the book value of the equipment (i.e., the original basis less all previous depreciation charges). See below for calculations of yearly book values. Year: Beginning book val Depreciation Ending book value 1 $4,080 $1,346 $2,734 2 $2,734 $1,836 $898 3 $898 $612 $286 4 $286 $286 $0 If GPC terminates at Year 2 and can sell the equipment for $898, what is the after-tax salvage cash flow? What if GPC can only sell the equipment for $98 at Year 2? Cash from sale Tax expense (credit) Net cash flow from salvage Case 2: Loss $98.00 $897.60 -$799.60 -$319.84 $898.00 $0.16 $897.84 Market value when salvaged at Year 2 Book value when salvaged at Year 2 Expected gain or loss Tax expense (credit) Case 1: Gain $898.00 $897.60 $0.40 $0.16 $98.00 -$319.84 $417.84 Questions: 1. Due to increased regulations for pollution control, equipment costs are increased by 20 percent. How does this impact the viablility of the project? Redo the analysis above and put the values of your project evaluation criteria below. Just cut and paste without the formulas. Project Evaluation Accelerated Results NPV IRR MIRR Profitability index Payback Discounted payback -$89 7.36% 7.58% 0.98 3.53 #N/A Formulas =NPV(E59,F101:I101)+E101 =IRR(E101:I101) =MIRR(E101:I101,E59,E59) =NPV(E59,F101:I101)/(-E101) =PERCENTRANK(E112:I112,0,6)*I111 =PERCENTRANK(E114:I114,0,6)*I111 Straight Line Results -$155 6.94% 7.27% 0.97 3.58 #N/A Answer: With Increase in Eqpt cost, Project becomes unviable. Using either Depreciation method gives a negative NPV. 2. Due to your sustainability improvement done in Question 1, your sales went up by 10 percent. What the the performance of your project now? Redo the analysis above and put the values of your project evaluation criteria below. Just cut and paste without the formulas. Project Evaluation Accelerated Results NPV IRR MIRR $618 12.28% 10.74% Formulas =NPV(E59,F101:I101)+E101 =IRR(E101:I101) =MIRR(E101:I101,E59,E59) Straight Line Results $552 11.64% 10.45% 42 Profitability index Payback Discounted payback 1.11 3.27 3.71 =NPV(E59,F101:I101)/(-E101) =PERCENTRANK(E112:I112,0,6)*I111 =PERCENTRANK(E114:I114,0,6)*I111 1.09 3.35 3.77 Answer: With Increase in Sales due to changes made in Q1, Project is now viable. NPV is Positive, IRR is more than WACC. Question 3. Because of you sustainability friendly project, you were able to secure a lower rate of financing causing your WACC to decrease by 10 percent. What is the performance of the project now. Do keep the changes made in Questions 1 and 2. Project Evaluation Accelerated Results NPV IRR MIRR Profitability index Payback $746 12.28% 10.45% 1.13 3.27 Discounted payback 3.66 Formulas =NPV(E59,F101:I101)+E101 =IRR(E101:I101) =MIRR(E101:I101,E59,E59) =NPV(E59,F101:I101)/(-E101) =PERCENTRANK(E112:I112,0,6)*I111 =PERCENTRANK(E114:I114,0,6)*I111 Straight Line Results $684 11.64% 10.20% 1.12 3.35 3.72 Answer: With decrease in WACC, Project NPV has further improved. Also Payback period & Discounted Payback period also reduced. 4. Using the findings from Questions 1, 2, and 3, make some general statements about the implications of sustainability and financial management for corporations. 1 2 3 4 5 etc. Sustainability initiatives result in higher sales Sustainability initiatives result in lower WACC due to saving of environment Sustainability initiatives result in higher NPV as compard to base case Sustainability initiatives result in lower payback period as compared to base case Sustainability initiatives result in lower discounted payback period Line. Applying Sustainability Input to Project Analysis Overall Objective: To see how sustainability adjustments can impact the viability of projects. Answer the questions at the bottom of the page. Source: 'Chapter 11. Tool Kit for Cash Flow Estimation and Risk Analysis ANALYSIS OF AN EXPANSION PROJECT (Section 11.2) Should I add another piece of machinery? Is it viable to do so? The model uses the "Base-Case" input values shown below to calculate the NPV and other performance measures. The main model assumes that the firm uses accelerated depreciation. A modified version of the model, shown in Columns J through R, shows the results if the firm elects to use straight-line depreciation. This analysis demonstrates that accelerated depreciation improves project profitability. Part 1. Inputs and Key Results Inputs Base-Case Equipment cost Salvage value, equipment, Year 4 Opportunity cost NPV IRR MIRR $300 $0 Externalities (cannibalization) Units sold, Year 1 Annual change in units sold, after Year 1 Sales price per unit, Year 1 Annual change in sales price, after Year Variable cost per unit (VC), Year 1 Annual change in VC, after Year 1 Nonvariable cost (Non-VC), Year 1 Annual change in Non-VC, after Year 1 Project WACC Tax rate Working capital as % of next year's sales Key Results $4,080 $0 $446 11.61% 10.34% DO NOT CHANGE NPV, IRR, MIRR, PI, Payback, and Discounted Payback 1.09 PI Payback 605 4.00% $11.60 2.00% $6.00 2.00% $2,000 2.00% 8.00% 40.00% 12.65% Change any number in blue in the base case and it will change the values of the criteria for viability. Discounted payback 3.33 3.76 $ 605 If you change any of the blue values in the Input Section shown above, the model below will change instantly, causing changes in NPV and other output variables. You can see the effect in the Key Results box shown above. If you change an input value but later want to return to the base case, use Scenario Manager to select the Base-Case. In Excel 2003, select Tools, Scenarios. In Excel 2007, select Data, What-If-Analysis, Scenario Manager. Figure 11-2. Analysis of a New (Expansion) Project: Cash Flows and Performance Measures (Dollars in Thousands) Part 2. Cash Flows and Performance Measures Model Using Straight-Line Depreciation Variables Used in the Cash Flow Forecas Unit sales Sales price per unit Variable cost per unit Nonvariable costs (excluding depreciation) Variables Used in the Cash Flow Forecast Investment Outlays at Time = 0 Equipment Initial investment in working capital Opportunity cost, after taxes 0 0 -$4,080 -888 0 1 2 3 605 629 654 $11.60 $11.83 $12.07 $6.00 $6.12 $6.24 $2,000 $2,040 $2,081 Cash Flows At End of Year 1 2 3 4 681 $12.31 $6.37 $2,122 4 Sales revenues = Units Price/unit Variable costs = Units Cost/unit Nonvariable costs (excluding depreciation) Depreciation: Accelerated, from table below Operating profit (EBIT) Taxes on operating profit Net operating profit after taxes Add back depreciation Opportunity cost, after taxes Cannibalization or complementary effects, after tax Salvage value (taxed as ordinary income) Tax on salvage value (SV is taxed at 40%) Unit sales Sales price per unit Variable cost per unit Nonvariable costs (excluding depreciation) Investment Outlays at Time = 0 Equipment Initial investment in working capital Opportunity cost, after taxes Net Cash Flows Over the Project's Life 0 0 -$4,080 -888 0 1 2 3 605 629 654 $11.60 $11.83 $12.07 $6.000 $6.120 $6.242 $2,000 $2,040 $2,081 Cash Flows At End of Year 1 2 3 4 681 $12.31 $6.367 $2,122 4 Net Cash Flows Over the Project's Life $7,018 3,630 2,000 1,346 $42 17 $25 1,346 0 0 $7,445 3,851 2,040 1,836 -$282 -113 -$169 1,836 0 0 $7,897 4,085 2,081 612 $1,120 448 $672 612 0 0 $8,377 4,333 2,122 286 $1,636 655 $982 286 0 0 300 -120 Sales revenues = Units Price/unit Variable costs = Units Cost/unit Nonvariable costs (excluding depreciation) Depreciation: Straight Line, from table below Operating profit (EBIT) Taxes on operating profit Net operating profit after taxes Add back depreciation Opportunity cost, after taxes Cannibalization or complementary effects, after taxes Salvage value (taxed as ordinary income) Tax on salvage value (SV is taxed at 40%) $7,018 3,630 2,000 1,020 $368 147 $221 1,020 0 0 $7,445 3,851 2,040 1,020 $534 214 $320 1,020 0 0 $7,897 4,085 2,081 1,020 $712 285 $427 1,020 0 0 $8,377 4,333 2,122 1,020 $902 361 $541 1,020 0 0 300 -120 Change in WC: Outflow (-) or recovery (+) Project net cash flows: Time Line -54 -$4,968 Project Evaluation -57 -61 1,060 $1,317 $1,610 $1,223 $2,507 Accelerated Change in WC: Outflow (-) or recovery (+) Project net cash flows: Time Line -54 -$4,968 -57 -61 1,060 $1,187 $1,283 $1,386 $2,801 2 -$2,498 $1,100 -$2,769 3 -$1,112 $1,100 -$1,668 4 $1,689 $2,059 $390 25% $1,020 25% $1,020 Straight Line Formulas NPV =NPV(E59,F101:I101)+E101 $446 IRR =IRR(E101:I101) 11.61% MIRR =MIRR(E101:I101,E59,E59) 10.34% Profitability index =NPV(E59,F101:I101)/(-E101) 1.09 Payback =PERCENTRANK(E112:I112,0,6)*I111 3.33 =PERCENTRANK(E114:I114,0,6)*I111 Discounted payback 3.76 Year: 0 1 2 Calculations for Payback Cumulative cash flows for payback -$4,968 -$3,650 -$2,041 Discounted cash flows for disc. payba -$4,968 $1,220 $1,380 Cumulative discounted cash flows -$4,968 -$3,748 -$2,368 Accelerated Depreciation Depreciable basis: $4,080 Rate/year 33% 45% Dollars/year $1,346 $1,836 Results Results $390 11.01% 10.06% 1.08 3.40 3.81 3 -$818 $971 -$1,397 15% $612 Note: see Ch 10 Tool Kit.xls for a detailed explanation of how to use the PERCENTRANK function to calculate payback. 4 Calculations for Payback Year: 0 1 $1,689 Cumulative cash flows for payback -$4,968 -$3,781 $1,843 Discounted cash flows for discounted paybac -$4,968 $1,099 $446 Cumulative discounted cash flows -$4,968 -$3,869 Straight-Line Depreciation 7% Depreciable basis: $4,080 Rate/year 25% $286 Dollars/year $1,020 25% $1,020 Taxation of Salvage We use two ways to deal with depreciation: MACRS and Straight-L Suppose GPC terminates operations before the equipment is fully depreciated. The aftertax salvage value depends upon the price at which GPC can sell the equipment and upon the book value of the equipment (i.e., the original basis less all previous depreciation charges). See below for calculations of yearly book values. Year: Beginning book val Depreciation Ending book value 1 $4,080 $1,346 $2,734 2 $2,734 $1,836 $898 3 $898 $612 $286 4 $286 $286 $0 If GPC terminates at Year 2 and can sell the equipment for $898, what is the after-tax salvage cash flow? What if GPC can only sell the equipment for $98 at Year 2? Cash from sale Tax expense (credit) Net cash flow from salvage Case 2: Loss $98.00 $897.60 -$799.60 -$319.84 $898.00 $0.16 $897.84 Market value when salvaged at Year 2 Book value when salvaged at Year 2 Expected gain or loss Tax expense (credit) Case 1: Gain $898.00 $897.60 $0.40 $0.16 $98.00 -$319.84 $417.84 Questions: 1. Due to increased regulations for pollution control, equipment costs are increased by 20 percent. How does this impact the viablility of the project? Redo the analysis above and put the values of your project evaluation criteria below. Just cut and paste without the formulas. Project Evaluation Accelerated Results NPV IRR MIRR Profitability index Payback Discounted payback -$89 7.36% 7.58% 0.98 3.53 #N/A Formulas =NPV(E59,F101:I101)+E101 =IRR(E101:I101) =MIRR(E101:I101,E59,E59) =NPV(E59,F101:I101)/(-E101) =PERCENTRANK(E112:I112,0,6)*I111 =PERCENTRANK(E114:I114,0,6)*I111 Straight Line Results -$155 6.94% 7.27% 0.97 3.58 #N/A Answer: With Increase in Eqpt cost, Project becomes unviable. Using either Depreciation method gives a negative NPV. 2. Due to your sustainability improvement done in Question 1, your sales went up by 10 percent. What the the performance of your project now? Redo the analysis above and put the values of your project evaluation criteria below. Just cut and paste without the formulas. Project Evaluation Accelerated Results NPV IRR MIRR $618 12.28% 10.74% Formulas =NPV(E59,F101:I101)+E101 =IRR(E101:I101) =MIRR(E101:I101,E59,E59) Straight Line Results $552 11.64% 10.45% 42 Profitability index Payback Discounted payback 1.11 3.27 3.71 =NPV(E59,F101:I101)/(-E101) =PERCENTRANK(E112:I112,0,6)*I111 =PERCENTRANK(E114:I114,0,6)*I111 1.09 3.35 3.77 Answer: With Increase in Sales due to changes made in Q1, Project is now viable. NPV is Positive, IRR is more than WACC. Question 3. Because of you sustainability friendly project, you were able to secure a lower rate of financing causing your WACC to decrease by 10 percent. What is the performance of the project now. Do keep the changes made in Questions 1 and 2. Project Evaluation Accelerated Results NPV IRR MIRR Profitability index Payback $746 12.28% 10.45% 1.13 3.27 Discounted payback 3.66 Formulas =NPV(E59,F101:I101)+E101 =IRR(E101:I101) =MIRR(E101:I101,E59,E59) =NPV(E59,F101:I101)/(-E101) =PERCENTRANK(E112:I112,0,6)*I111 =PERCENTRANK(E114:I114,0,6)*I111 Straight Line Results $684 11.64% 10.20% 1.12 3.35 3.72 Answer: With decrease in WACC, Project NPV has further improved. Also Payback period & Discounted Payback period also reduced. 4. Using the findings from Questions 1, 2, and 3, make some general statements about the implications of sustainability and financial management for corporations. 1 2 3 4 5 etc. Sustainability initiatives result in higher sales Sustainability initiatives result in lower WACC due to saving of environment Sustainability initiatives result in higher NPV as compard to base case Sustainability initiatives result in lower payback period as compared to base case Sustainability initiatives result in lower discounted payback period Line