Hello,Please help me with this spreadsheet.Thank you

 ATTACHMENT PREVIEW Download attachmentApplying Sustainability Input to Project AnalysisAnswer the questions at the bottom of the page.Source: ‘Chapter 11.Tool Kit for Cash Flow Estimation and Risk AnalysisShould I add another piece of machinery?Is it viable to do so?Part 1.Inputs and Key ResultsInputsBase-CaseKey ResultsEquipment cost$4,080NPV$446Change any number in blue in the base caseandSalvage value, equipment, Year 4$300IRR11.61%it will change the values of the criteria for viability.Opportunity cost$0MIRR10.34%Externalities (cannibalization)$0PI1.09Units sold, Year 1605Payback3.33Annual change in units sold, after Year 14.00%Discounted payback3.7660500.00%Sales price per unit, Year 1$11.60Annual change in sales price, after Year 12.00%Variable cost per unit (VC), Year 1$6.00Annual change in VC, after Year 12.00%Nonvariable cost (Non-VC), Year 1$2,000Annual change in Non-VC, after Year 12.00%Project WACC8.00%Tax rate40.00%Working capital as % of next year’s sales12.65%Figure 11-2.Analysis of a New (Expansion) Project: Cash Flows and Performance Measures (Dollars in Thousands)Part 2.Cash Flows and Performance MeasuresModel Using Straight-Line Depreciation01234Variables Used in the Cash Flow Forecast01234Variables Used in the Cash Flow ForecastUnit sales605629654681Unit sales605629654681Sales price per unit$11.60$11.83$12.07$12.31Sales price per unit$11.60$11.83$12.07$12.31Variable cost per unit$6.00$6.12$6.24$6.37Variable cost per unit$6.000$6.120$6.242$6.367Nonvariable costs (excluding depreciation)$2,000$2,040$2,081$2,122Nonvariable costs (excluding depreciation)$2,000$2,040$2,081$2,122Cash Flows At End of YearCash Flows At End of YearInvestment Outlays at Time = 001234Investment Outlays at Time = 001234Equipment-$4,080Equipment-$4,080Initial investment in working capital-888Initial investment in working capital-888Opportunity cost, after taxes0Opportunity cost, after taxes0Net Cash Flows Over the Project’s LifeNet Cash Flows Over the Project’s LifeSales revenues = Units × Price/unit$7,018$7,445$7,897$8,377Sales revenues = Units × Price/unit$7,018$7,445$7,897$8,377Variable costs= Units × Cost/unit3,6303,8514,0854,333Variable costs= Units × Cost/unit3,6303,8514,0854,333Nonvariable costs (excluding depreciation)2,0002,0402,0812,122Nonvariable costs (excluding depreciation)2,0002,0402,0812,122Depreciation: Accelerated, from table below1,3461,836612286Depreciation: Accelerated, from table below1,0201,0201,0201,020Operating profit (EBIT)$42-$282$1,120$1,636Operating profit (EBIT)$368$534$712$902Taxes on operating profit17-113448655Taxes on operating profit147214285361Net operating profit after taxes$25-$169$672$982Net operating profit after taxes$221$320$427$541Add back depreciation1,3461,836612286Add back depreciation1,0201,0201,0201,020Opportunity cost, after taxes0000Opportunity cost, after taxes0000Cannibalization or complementary effects, after taxe0000Cannibalization or complementary effects, after taxes0000Salvage value (taxed as ordinary income)300Salvage value (taxed as ordinary income)300Tax on salvage value (SV is taxed at 40%)-120Tax on salvage value (SV is taxed at 40%)-120Change in WC: Outflow(–) or recovery (+)-54-57-611,060Change in WC: Outflow(–) or recovery (+)-54-57-611,060Overall Objective:To see how sustainabilityadjustments can impact the viability of projects.ANALYSIS OF AN EXPANSION PROJECT(Section 11.2)The model uses the “Base-Case” input values shown below to calculate the NPV and otherperformance measures.The main model assumes that the firm uses accelerateddepreciation.A modified version of the model, shown in Columns J through R, shows theresults if the firm elects to use straight-line depreciation.This analysis demonstrates thataccelerated depreciation improves project profitability.DO NOT CHANGE NPV, IRR, MIRR, PI, Payback, andDiscounted PaybackIf you change any of the blue values in the Input Section shown above, the model below willchange instantly, causing changes in NPV and other output variables.You can see the effectin the Key Results box shown above. If you change an input value but later want to return tothe 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.

View the AnswerProject net cash flows: Time Line-$4,968$1,317$1,610$1,223$2,507Project net cash flows: Time Line-$4,968$1,187$1,283$1,386$2,801Project EvaluationAcceleratedStraight LineResultsFormulasResultsNPV$446=NPV(E59,F101:I101)+E101$390IRR11.61%=IRR(E101:I101)11.01%MIRR10.34%=MIRR(E101:I101,E59,E59)10.06%Profitability index1.09=NPV(E59,F101:I101)/(-E101)1.08Payback3.33=PERCENTRANK(E112:I112,0,6)*I1113.40Discounted payback3.76=PERCENTRANK(E114:I114,0,6)*I1113.81Calculations for PaybackYear:01234Calculations for PaybackYear:01234Cumulative cash flows for payback-$4,968-$3,650-$2,041-$818$1,689Cumulative cash flows for payback-$4,968-$3,781-$2,498-$1,112$1,689Discounted cash flows for disc. paybac-$4,968$1,220$1,380$971$1,843Discounted cash flows for discounted payback-$4,968$1,099$1,100$1,100$2,059Cumulative discounted cash flows-$4,968-$3,748-$2,368-$1,397$446Cumulative discounted cash flows-$4,968-$3,869-$2,769-$1,668$390Accelerated DepreciationStraight-Line DepreciationDepreciable basis:$4,080Rate/year33%45%15%7%Depreciable basis:$4,080Rate/year25%25%25%25%Dollars/year$1,346$1,836$612$286Dollars/year$1,020$1,020$1,020$1,020Taxation of SalvageWe use two ways to deal with depreciation: MACRS and Straight-LiYear:1234Beginning book valu$4,080$2,734$898$286Depreciation$1,346$1,836$612$286Ending book value$2,734$898$286$0Market value when salvaged at Year 2$898.00$98.00Book value when salvaged at Year 2$897.60$897.60Expected gain or loss$0.40-$799.60Tax expense (credit)$0.16-$319.84Cash from sale$898.00$98.00Tax expense (credit)$0.16-$319.84Net cash flow from salvage$897.84$417.84Questons:1.Due to increased regulaTons for polluTon control, equipmentcosts are increased by 20 percent.How does this impactthe viablility of the project?Redo The analysis above and puT The values of your projecT evaluatonProject EvaluationAcceleratedStraight LineResultsFormulasResultsNPV=NPV(E59,F101:I101)+E101IRR=IRR(E101:I101)MIRR=MIRR(E101:I101,E59,E59)Profitability index=NPV(E59,F101:I101)/(-E101)Payback=PERCENTRANK(E112:I112,0,6)*I111Discounted payback=PERCENTRANK(E114:I114,0,6)*I111Answer:2.Due To your susTainabiliTy improvemenT done in Queston 1, your saleswenT up by 10 percenT.WhaT The The performance of your projecT now?Redo The analysis above and puT The values of your projecT evaluatonProject EvaluationAcceleratedStraight LineResultsFormulasResultsNPV=NPV(E59,F101:I101)+E101IRR=IRR(E101:I101)MIRR=MIRR(E101:I101,E59,E59)Profitability index=NPV(E59,F101:I101)/(-E101)Payback=PERCENTRANK(E112:I112,0,6)*I111Discounted payback=PERCENTRANK(E114:I114,0,6)*I111Note: seeCh 10 Tool Kit.xlsfor a detailed explanation of how to usethe PERCENTRANK function to calculate payback.Suppose GPC terminates operations before the equipment is fully depreciated. The after-taxsalvage value depends upon the price at which GPC can sell the equipment and upon thebook value of the equipment (i.e., the original basis less all previous depreciation charges).See below for calculations of yearly book values.If GPC terminates at Year 2 and can sell the equipment for $898, what is the after-tax salvagecash flow? What if GPC can only sell the equipment for $98 at Year 2?Case 1:GainCase 2:LosscriTeria below. JusT cuT and pasTe wiThouTThe formulas.criTeria below. JusT cuT and pasTe wiThouTThe formulas.

Show entire documentSign up to view the entire interaction

Step-by-step answer

ur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facili  ATTACHMENT PREVIEW Download attachment

Project for sustainability and finance-6.xlsx

itur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectu trices ac magna. Fusce dui lectus, congue vel lao ec facilisis. Pellentesque dapibus efficitur laore

Subscribe to view the full answer

Subscribe to unlock

Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"