CostBenefitAnalysis成本效益分析

CostBenefitAnalysisCost-BenefitAnalysis-1CostBenefitAnalysis,Slide2Copyright©2004,JimSchwab,UniversityofTexasatAustin Incost-benefitanalysis,wecomparethecostsandbenefitsofoneormoreprojectstodeterminewhichareworthwhile,andwhichshouldbeprioritizedwhentherearemultipleprojects.Thecomputationsaresimilartothoseincosteffectivenessanalysis;wesimplyareapplyingeconomicevaluationtechniquestotwoentities:costsandbenefits. Theminimumrequirementforaprojecttobejudgedworthwhileisthatitsbenefit-costratiobeatleast1.0.Thismeansthatthebenefitsequalorexceedthecostsoftheproject. Whencomparingmultipleworthwhileprojects,prioritywouldbegiventotheprojectwiththehighestbenefit-costratio. Cost-benefitanalysiscanbemuchmorecomplexthatwewillpresenthere.Realworkproblemsfrequentlyhavebenefitstomultiplegroups,i.e.therecipientsoftheserviceandsocietyatlarge.Forexample,apersoncuredofsubstanceabusecouldshowhisorherwagesasapersonalbenefit.Societywouldalsogainbecausethisindividualnowpaystaxes,doesnotstealtopayforthedrughabit,etc.Cost-BenefitAnalysis-2CostBenefitAnalysis,Slide3Copyright©2004,JimSchwab,UniversityofTexasatAustin Inmanycases,benefitstoonegroupmaybecoststoanothergroup.Forexample,welfarereformmaysavethegovernmentmoney,butreducetheincomeofmerchantswhoownthestoreswherewelfarerecipientsshop. Anothercomplexitywhichwewillnotpursueistheprobabilityorlikelihoodoftheoccurrenceofdifferenteventsoroutcomes.Futureeventsandcostsarebasedontheassumptionoftheirlikelihoodofoccurrence.Wecancalculatescenarioswithdifferentprobabilitiesforfutureeventstoseewhatimpactthatwouldmakeforchoosingamongtheavailablealternatives.Example1:EastStocktonUrbanRenewalProject-1CostBenefitAnalysis,Slide4Copyright©2004,JimSchwab,UniversityofTexasatAustin ThisproblemissimilartotheprobleminQuantitativeMethodsforPublicDecisionMakingbyChristopherK.McKenna,159. Theobjectiveandsocialbenefitsofurbanrenewalare(1)superiorpatternofresourceallocation,(2)socialbenefitsoftheremovalofblight,and(3)improvedlocalfinancialposition.Althoughtheremaybeanumberofalternativeusesforlandbeingredeveloped,wearehereconsideringthemoreaggregatealternatives,eitherurbanrenewalornourbanrenewalinaparticularsectionofthecity.Thisisthelevelofevaluationappropriateforcost-benefitanalysis.Thealternativeswouldthenbetheparticularurbanrenewalprojectsthatshouldorshouldnotbeundertaken. Amongtheconstraintsactiveonurbanrenewalisthelegalrequirementthataredevelopmentagencymustprovideformerresidentsofanurbanrenewalareawithdecent,safe,andsanitaryhousingthatisconvenientlylocatedandwithinthemeansoftheresidents.Notethatitisnotimplicitlyassumedthatrelocationresultsinhousingfacilityimprovementfortheresidents,Example1:EastStocktonUrbanRenewalProject-2CostBenefitAnalysis,Slide5Copyright©2004,JimSchwab,UniversityofTexasatAustin Thecostsincludethoseforrelocation,surveyandplanning,administration,publicimprovements,demolition,andthevalueofimprovementsdemolished.Benefitsincludethosespecificallyassociatedwiththestatedobjectivesaswellasnon-economicnegativeeffectsofrelocationandpossiblelandvaluewrite-down.Inurbanrenewalthereare,ofcourse,tangibleandintangiblebenefits;inthisexercise,ourgoalistodeterminewhatlevelofintangiblebenefitswoulddecisionmakershavetosubstantiateinordertojustifytheprojectfromacost-benefitperspective. TheEastStockton,California,UrbanRenewalProjectwasofficiallyapprovedbythefederalgovernmentinJuly1959.Theworkbook,“UrbanRenewal.xls”,whichcanbedownloadedfromthecoursedownloadwebpage,displaysthevariouscostsassociatedwiththerenewalprojectandthetimeatwhichtheyoccurred.Mostofthecostswereactuallyincurredoveranintervaloftime;insuchcasesthecenteroftheintervalisusedasthedateofthecost.

Example1:EastStocktonUrbanRenewalProject-3CostBenefitAnalysis,Slide6Copyright©2004,JimSchwab,UniversityofTexasatAustin Thecostofthelandisnotincludedinthelistofcostssincelandpurchaseswerelaterresold.IntheEastStocktonrenewalproject,thelandwaspurchasedfor$669,129overaperiodroughlycenteredatJune30,1960.Afterclearingandrenewal,thelandwassubsequentlysoldfor$1,200,000overaperiodroughlycenteredatJune30,1965.Employingadiscountrateof6percent,thesellingpricewasdiscountedtoJune30,1960,yieldingapresentvalueof$896,760;hence,theredevelopmentagencyhada“profit”of$227,631ontheprojectarealand.Thisamountisincludedinthelistoftangiblebenefitsinthe“UrbanRenewal”workbook. Othertangiblebenefitswerenotquitesoeasilyestimated.Theincreaseinthepropertyvalueintheprojectareawastheresultofthreefactors:inflation,growthinrealincomeandpopulation,andurbanrenewal.Toisolatetheincreaseduetourbanrenewal,acomparisonwasmadebetweenincreasesintheprojectareaandincreasesneartheprojectarea.Thecomparisonledtoanestimateof$415,500astheincreaseinthevalueoftheneighborhoodproperties.Publicimprovementssuchasschoolsandpartswereestimatedatavalueequaltotheircost.Example1:EastStocktonUrbanRenewalProject-4CostBenefitAnalysis,Slide7Copyright©2004,JimSchwab,UniversityofTexasatAustin Urbanrenewalisgenerallyexpectedtoreducethecostofmunicipalservices.ThesavingsinthecostoffireprotectionwasestimatedbynotingthatpriortourbanrenewaltheperpersonexpenditureforEastStocktonaswas2½timeswhatitwasfortherestofthecity.AssumingthatafterrenewaltheresidentsofEastStocktonwouldrequireonlyaverageprotection,thereducedcostoffireprotectionwasestimatedtobe$42,000annually.Capitalizingtheannualamountof$42,000at6percentyields$700,000asthepresentvalueoffuturefireprotectioncostsavings.Thesavingsinhealthprotectionandpoliceprotectioncostswereestimatedsimilarly. Thequestionswewillanswerinthisexerciseare:whatlevelofintangiblebenefitsneedtobeidentifiedinorderforthisprojecttosatisfytheminimumcostbenefitratioof1.0?Doesahigherorlowerdiscountratesubstantiallychangeouranswer?

Tangiblecosts/benefitsofurbanrenewalprojectCostBenefitAnalysis,Slide8Copyright©2004,JimSchwab,UniversityofTexasatAustinThetangiblecostsandbenefitsfortheprojecthavebeenenteredinaworkbookcalledUrbanRenewal.xlswhichcanbedownloadedfromacoursewebpage.AllofthisinformationisgiveninthechapterbyMcKenna.NotethattheworksheetincludestheDateoftheexpensebecausenotallexpensesoccurredatregularannualintervals.Excelhasanotherworksheetfunctiontosupportcalculationsofnetpresentvaluewhenthestreamofpaymentsoccurindifferenttimeperiods,theXNPVfunction.ThediscountrateCostBenefitAnalysis,Slide9Copyright©2004,JimSchwab,UniversityofTexasatAustinFirst,weenterthediscountratestatedintheproblem6%inthecellD2oftheCost-benefitanalysisworksheet.UseXNPVfunctiontocalculatevalueoftangiblecostsCostBenefitAnalysis,Slide10Copyright©2004,JimSchwab,UniversityofTexasatAustinFirst,selectthecellinwhichweExceltoreturnthepresentvalueofthetangiblecosts,cellB14ontheCost-benefitanalysisworksheet.Second,selecttheFunctioncommandfromtheInsertmenu.Unlikethe‘NPV’function,the‘XNPV’functiondoesnotmaketheassumptionthattheseriesofcostsoccursatregular,annualintervals.XPNVpermitsustoassociateddateswitheachcostitem.LocatetheXNPVfunctionCostBenefitAnalysis,Slide11Copyright©2004,JimSchwab,UniversityofTexasatAustinWewillsearchfortheXNPVfunction.First,typeXNPVintheSearchfortextbox,andclickontheGobutton.TheXNPVfunctionnamewillappearintheSelectafunctionlistbox.ClickontheOKbuttonaccessthedialogboxwherethefunctionargumentsareentered.Note:theXNPVfunctionispartoftheAnalysisToolpakthatweusedforDataAnalysis.IfExceldoesnotfindit,checktomakesuretheAnalysisToolpakAdd-inhasbeeninstalled.TheargumentstotheXNPVfunctionCostBenefitAnalysis,Slide12Copyright©2004,JimSchwab,UniversityofTexasatAustinThefirstargumenttotheXNPVfunctionisthediscountrate,whichweputincellD2.ThesecondargumenttotheXNPVfunctionisthecellscontainingthetangiblecosts,B2:B12.ThethirdargumenttotheXNPVfunctionisthecellscontainingthedatesthetangiblecostsoccurred,C2:C12.Withtheargumentsentered,clickontheOKbutton.NetpresentvalueoftangiblecostsCostBenefitAnalysis,Slide13Copyright©2004,JimSchwab,UniversityofTexasatAustinExcelcomputesthenetpresentvaluefortheseriesofcostsforthisproject.Thetableoftangiblebenefits-1CostBenefitAnalysis,Slide14Copyright©2004,JimSchwab,UniversityofTexasatAustinTheXPNVfunctiondiscountsthestreamofcostsorbenefitsbacktothefirstdateintheseries.Forcosts,theentryfor‘Surveyandplanning’wasdatedtooccuratthestartoftheproject.Sincethisitemwaslistedfirst,itcouldbeusedforthedate(12-31-58)towhichallothercostswerediscounted.Thetableoftangiblebenefits-2CostBenefitAnalysis,Slide15Copyright©2004,JimSchwab,UniversityofTexasatAustinThetableoftangiblebenefitswascopiedfromtheMcKennatext,exceptfortheentryonrow18whichwasaddedasarequirementoftheXNPVfunction.Inthecaseofbenefits,therewas,quitenaturally,nobenefittoberealizedatthestartoftheproject.TosatisfytheExcelXPNVfunction,Iaddedadummyentrytothetable,‘Immediatebenefits’withavalueof$0toberealizedatthestartoftheprojecton12-31-58.Sincethisentrywasforzerodollars,itwillnotaffectourbenefitcalculations.ThedateentryincellC18meetstherequirementoftheXNPVfunctionforaninitialdatetowhichallotherbenefitsarediscounted.UseXNPVtocalculatevalueoftangiblebenefitsCostBenefitAnalysis,Slide16Copyright©2004,JimSchwab,UniversityofTexasatAustinFirst,selectthecellinwhichweExceltoreturnthepresentvalueofthetangiblebenefitscellB26ontheCost-benefitanalysisworksheet.Second,selecttheFunctioncommandfromtheInsertmenu.LocatetheXNPVfunctionCostBenefitAnalysis,Slide17Copyright©2004,JimSchwab,UniversityofTexasatAustinWewillsearchfortheXNPVfunction.First,typeXNPVintheSearchfortextbox,andclickontheGobutton.TheXNPVfunctionnamewillappearintheSelectafunctionlistbox.ClickontheOKbuttonaccessthedialogboxwherethefunctionargumentsareentered.Note:theXNPVfunctionispartoftheAnalysisToolpakthatweusedforDataAnalysis.IfExceldoesnotfindit,checktomakesuretheAnalysisToolpakAdd-inhasbeeninstalled.TheargumentstotheXNPVfunctionCostBenefitAnalysis,Slide18Copyright©2004,JimSchwab,UniversityofTexasatAustinThefirstargumenttotheXNPVfunctionisthediscountrate,whichweputincellD2.ThesecondargumenttotheXNPVfunctionisthecellscontainingthetangiblebenefits,B18:B24.ThethirdargumenttotheXNPVfunctionisthecellscontainingthedatesthetangiblebenefitsoccurred,C18:C24.Withtheargumentsentered,clickontheOKbutton.NetpresentvalueoftangiblebenefitsCostBenefitAnalysis,Slide19Copyright©2004,JimSchwab,UniversityofTexasatAustinExcelcomputesthenetpresentvaluefortheseriesofbenefitsforthisproject.ComputetherequiredintangiblebenefitsCostBenefitAnalysis,Slide20Copyright©2004,JimSchwab,UniversityofTexasatAustinTheproblemstatementwantedustofindtheminimumlevelofintangiblebenefitsthatwouldbenecessarytomeettheminimumbenefit-costratioof1.0.The‘Requiredintangiblebenefits’areequaltothedifferencebetweentangiblecostsandtangiblebenefits.IncellB28,entertheformulaforcomputingthedifferencebetweentangiblecostsincellB14andtangiblebenefitsincellB26:=B14-B26.Inordertosatisfybenefit-costcriteria,theprojectplannerswouldhavetoidentifyanddocument$1,062,932inintangiblebenefits.WhatifthediscountrateweredifferentCostBenefitAnalysis,Slide21Copyright©2004,JimSchwab,UniversityofTexasatAustinTheproblemstatementalsowantedustodeterminewhetherornotahigherorlowerdiscountratesubstantiallychangesouranswer.Inordertoseetheresultsofthetestingdifferentdiscountrates,wesplitthescreenatrow24andarrangethepanesasshown.TestahigherdiscountrateCostBenefitAnalysis,Slide22Copyright©2004,JimSchwab,UniversityofTexasatAustinEnter7%incellD2totesttheeffectofahigherdiscountrate.Excelhasrecalculatedtherequiredintangiblebenefitsneededtobehigherbyabout$15,000($1,077,691-$1,062,932).Forthissizeoftheurbanrenewalproject,IwouldnotconsiderthisasubstantialdifferenceTestalowerdiscountrateCostBenefitAnalysis,Slide23Copyright©2004,JimSchwab,UniversityofTexasatAustinTotestalowerdiscountrate,enter5%incellD2totesttheeffectofahigherdiscountrate.Excelhasrecalculatedtherequiredintangiblebenefitsneededtobelowerbyabout$16,000($1,046,037-$1,062,932).Forthissizeoftheurbanrenewalproject,IwouldnotconsiderthisasubstantialdifferenceWehaveansweredallofthequestionsstatedintheproblem.Example2:AHighwayExpansionProjectCostBenefitAnalysis,Slide24Copyright©2004,JimSchwab,UniversityofTexasatAustin ThisexamplewasadaptedfromaproblempresentedinPublicPolicyAnalysis:AppliedResearchMethodsbyTheodoreH.Poister,pages397-400. Thiscasepertainstoahypotheticalhighwayprojectinwhichtwoalternativeexpansionlevels(expansiontoa4-lanehighwayandexpansiontoa6-lanehighway)areconsideredincomparisonwiththealternativeofretainingtheexistingroadway.Inthisapplication,thealternativesarecomparedincrementally,sothatthebenefitsandcostsofexpandingtoa4-lanehighwayarederivedbycomparingitwiththeexistingroadway,andthecostsandbenefitscorrespondingtothe6-laneexpansionarebasedontheincrementalcostsandbenefitsbeyondthe4-lanehighwayexpansion.Sequentially,then,theanalysisaddressestheissueofwhetheritisjustifiabletoexpandtoa4-lanehighway,asifso,whetheritisfurtherjustifiedtoexpandtothe6-lanehighway. Forthisproblem,wewillpresentindetailhowthebenefitsandcostsarederived.Thesheet,column,androwlabelshavebeenenteredintotheworkbook,HighwayProject.xls.ThebenefitoftraveltimesavedbytheproposedhighwaysCostBenefitAnalysis,Slide25Copyright©2004,JimSchwab,UniversityofTexasatAustinTheaveragetimespentpertripdeclinesdramaticallywiththeexpansiontothe4-lanehighway,andthenmodestlyaswemovetothe6-lanehighwayexpansion.Averagedrivingtimepertripontheexistinghighwayisestimatedtobe30minutes.Ifthehighwayisexpandedto4-lanes,theaveragetriptimedropsto18minutes.Ifthehighwayisexpandedto6-lanes,theaveragetriptimedropsanadditionaltwominutesto16minutes.ComputingthetimecostpertripCostBenefitAnalysis,Slide26Copyright©2004,JimSchwab,UniversityofTexasatAustinSettingthevalueofthedriver’stimeat$2.00perhour,thetimecostpertripiscomputedbydividingthenumberofminutesintheaveragetripby60andthenmultiplyingby$2.00.IncellB3,entertheformula=B2/60*2.IncellC3,entertheformula=C2/60*2.IncellD3,entertheformula=D2/60*2.TotalcostpertripCostBenefitAnalysis,Slide27Copyright©2004,JimSchwab,UniversityofTexasatAustinOthertripcostsincreaseslightlyatthefirstexpansionlevel(from$1.75to$1.90),becauseofhigherandlessefficientoperatingspeeds,andthendecreaseslightlyatthesecondexpansionlevel(from$1.90to$1.85)becauseofimprovedmaneuverabilityindispersedtraffic.Enter$1.75incellB4,$1.90incellC4,and$1.85incellD4.Totalvariablecostpertripiscomputedbyadding‘Timecostpertrip’and‘Othercostspertrip’.Enter=B3+B4incellB5,=C3+C4incellC5,and=D3+D4incellD5.CostsavingspertripCostBenefitAnalysis,Slide28Copyright©2004,JimSchwab,UniversityofTexasatAustinThecostsavingspertripwhenexpandingtothe4-lanehighwayisthedifferencebetweenthetotalvariablecostsfortheexistinghighway($2.75)andthe4-lanehighway($2.50)whichequals$.25.Entertheformula=B5-C5incellC6.Thecostsavingspertripwhenexpandingtothe6-lanehighwayisthedifferencebetweenthetotalvariablecostsforthe4-lanehighway($2.50)andthe6-lanehighway($2.38)whichequals$.12.Entertheformula=C5-D5incellD6.ComputingcostsavingsoncurrenttripsCostBenefitAnalysis,Slide29Copyright©2004,JimSchwab,UniversityofTexasatAustinFirst,weenterthesamenumberoftripsperyearforeachhighwaycondition,1milliontripsperyearincellsB8,C8,andD8.Itislikelythatthenumberoftripswouldincreasebecauseofimprovedtravel.Estimatingsavingsbasedontheexistingnumberoftripsis,therefore,aconservativeestimateoftheprobablesavings.Second,tocomputethecostsavingsforalltrips,wemultiplythecostsavingspertriponrow6bythenumberoftripsperyearonrow8.Enter=C6*C8incellC9and=D6*D8incellD9.ProjectedsavingsworksheetCostBenefitAnalysis,Slide30Copyright©2004,JimSchwab,UniversityofTexasatAustinFirst,copythecostsavingsforeachoftheexpansionprojectsfromcellsC9throughD9onthe‘Cost-benefitAnalysis’worksheetandPasteSpecialtheValuesintocellsB2throughC2onthe‘ProjectedSavings’worksheet.Second,filltheannualsavingsdownforatwenty-fiveyeartimeperiod.Forthisproblem,thepresentvalueofthebenefitsstreamiscomputedbyassumingthatthesameamountofbenefitwillaccrueforeachof25yearsintothefuture.HighlightcellsB2throughC26andselecttheFill>DowncommandfromtheEditmenu.ComputeNPVofsavingsfor4laneexpansionCostBenefitAnalysis,Slide31Copyright©2004,JimSchwab,UniversityofTexasatAustinFirst,selectthecellinwhichweExceltoreturnthepresentvalueofthesavings,cellC10ontheCost-benefitAnalysisworksheet.Second,selecttheFunctioncommandfromtheInsertmenu.LocatetheNPVfunctionCostBenefitAnalysis,Slide32Copyright©2004,JimSchwab,UniversityofTexasatAustinWewillsearchfortheNPVfunction.First,typeNPVintheSearchfortextbox,andclickontheGobutton.TheNPVfunctionnamewillappearintheSelectafunctionlistbox.ClickontheOKbuttonaccessthedialogboxwherethefunctionargumentsareentered.TheargumentstotheNPVfunctionCostBenefitAnalysis,Slide33Copyright©2004,JimSchwab,UniversityofTexasatAustinThefirstargumenttotheNPVfunctionisthediscountrate,whichwewillenterdirectlyas8%.ThesecondargumenttotheNPVfunctionisthecellscontainingtheprojectedsavingsfor4laneexpansion,'ProjectedSavings'!B2:B26.RemembertoenterthequotemarksaroundthenameoftheworksheetProjectedSavingsbecauseitcontainsaspace.Withtheargumentsentered,clickontheOKbutton.NPVforprojectedsavingsfor4laneexpansionCostBenefitAnalysis,Slide34Copyright©2004,JimSchwab,UniversityofTexasatAustinTheNPVfunctionreturnsthepresentvalueoftheprojectedsavingsfor4laneexpansion,$2,668,694.05.ComputeNPVofprojectedsavingsfor6laneexpansionCostBenefitAnalysis,Slide35Copyright©2004,JimSchwab,UniversityofTexasatAustinFirst,selectthecellinwhichweExceltoreturnthepresentvalueofthesavings,cellD10ontheCost-benefitAnalysisworksheet.Second,selecttheFunctioncommandfromtheInsertmenu.LocatetheNPVfunctionCostBenefitAnalysis,Slide36Copyright©2004,JimSchwab,UniversityofTexasatAustinWewillsearchfortheNPVfunction.First,typeNPVintheSearchfortextbox,andclickontheGobutton.TheNPVfunctionnamewillappearintheSelectafunctionlistbox.ClickontheOKbuttonaccessthedialogboxwherethefunctionargumentsareentered.TheargumentstotheNPVfunctionCostBenefitAnalysis,Slide37Copyright©2004,JimSchwab,UniversityofTexasatAustinThefirstargumenttotheNPVfunctionisthediscountrate,whichwewillenterdirectlyas8%.ThesecondargumenttotheNPVfunctionisthecellscontainingtheprojectedsavingsfor6laneexpansion,'ProjectedSavings'!C2:C26.RemembertoenterthequotemarksaroundthenameoftheworksheetProjectedSavingsbecauseitcontainsaspace.Withtheargumentsentered,clickontheOKbutton.NPVforprojectedsavingsfor6laneexpansionCostBenefitAnalysis,Slide38Copyright©2004,JimSchwab,UniversityofTexasatAustinTheNPVfunctionreturnsthepresentvalueoftheprojectedsavingsfor6laneexpansion,$1,245,394.11.Thecostsofthehighwayprojects-1CostBenefitAnalysis,Slide39Copyright©2004,JimSchwab,UniversityofTexasatAustinTheexpansiontoa4-lanehighwaywillcost$2,000,000inconstructioncosts.Enter$2,000,000incellC13.Similarly,theexpansionto6laneswillcostanadditional$2,000,000.Enter$2,000,000incellD13.Thecostsofthehighwayprojects-2CostBenefitAnalysis,Slide40Copyright©2004,JimSchwab,UniversityofTexasatAustinTheannualmaintenancecostsforboththeexistinghighwayandeachofthealternativesisenteredintheworksheet.Enter$20,000incellB14,$30,000incellC14,and$50,000incellD14.Thecostsofthehighwayprojects-3CostBenefitAnalysis,Slide41Copyright©2004,JimSchwab,UniversityofTexasatAustinTheincreaseinmaintenancecostsarecomputedbysubtractingtheexistingroadwaymaintenancecostsfromthe4-laneexpansionmaintenancecostsandsubtractingthe4-laneexpansionmaintenancecostsfromthe6-lanemaintenancecosts.First,entertheformula=C14-B14incellC15tocomputetheincreaseinmaintenancecostsassociatedwiththeexpansionto4lanes.Second,entertheformula=D14-C14incellD15tocomputetheincreaseinmaintenancecostsassociatedwithaddingtwoadditionallanestothe4lanehighway.AnnualMaintenanceworksheetCostBenefitAnalysis,Slide42Copyright©2004,JimSchwab,UniversityofTexasatAustinFirst,copytheincreaseinmaintenancecostsforeachoftheexpansionprojectsfromcellsC15throughD15onthe‘Cost-benefitAnalysis’worksheetandPasteSpecialtheValuesintocellsB2throughC2onthe‘AnnualMaintenance’worksheet.Second,filltheannualmaintenancecostincreasesdownforatwenty-fiveyeartimeperiod.Forthisproblem,thepresentvalueofthecoststreamiscomputedbyassumingthatthesameamountofmaintenancecostswillbeincurredforeachof25yearsintothefuture.HighlightcellsB2throughC26andselecttheFill>DowncommandfromtheEditmenu.ComputeNPVofincreasedmaintenancefor4laneexpansionCostBenefitAnalysis,Slide43Copyright©2004,JimSchwab,UniversityofTexasatAustinFirst,selectthecellinwhichweExceltoreturnthepresentvalueoftheincreasedmaintenance,cellC16ontheCost-benefitAnalysisworksheet.Second,selecttheFunctioncommandfromtheInsertmenu.LocatetheNPVfunctionCostBenefitAnalysis,Slide44Copyright©2004,JimSchwab,UniversityofTexasatAustinWewillsearchfortheNPVfunction.First,typeNPVintheSearchfortextbox,andclickontheGobutton.TheNPVfunctionnamewillappearintheSelectafunctionlistbox.ClickontheOKbuttonaccessthedialogboxwherethefunctionargumentsareentered.TheargumentstotheNPVfunctionCostBenefitAnalysis,Slide45Copyright©2004,JimSchwab,UniversityofTexasatAustinThefirstargumenttotheNPVfunctionisthediscountrate,whichwewillenterdirectlyas8%.ThesecondargumenttotheNPVfunctionisthecellscontainingtheincreasedmaintenancefor4laneexpansion,'ProjectedSavings'!B2:B26.RemembertoenterthequotemarksaroundthenameoftheworksheetAnnualMaintenancebecauseitcontainsaspace.Withtheargumentsentered,clickontheOKbutton.NPVforincreasedmaintenancefor4laneexpansionCostBenefitAnalysis,Slide46Copyright©2004,JimSchwab,UniversityofTexasatAustinTheNPVfunctionreturnsthepresentvalueoftheincreasedmaintenancefor4laneexpansion,$106,747.76.ComputeNPVofmaintenancefor6laneexpansionCostBenefitAnalysis,Slide47Copyright©2004,JimSchwab,UniversityofTexasatAustinFirst,selectthecellinwhichweExceltoreturnthepresentvalueoftheincreasedmaintenance,cellD16ontheCost-benefitAnalysisworksheet.Second,selecttheFunctioncommandfromtheInsertmenu.LocatetheNPVfunctionCostBenefitAnalysis,Slide48Copyright©2004,JimSchwab,UniversityofTexasatAustinWewillsearchfortheNPVfunction.First,typeNPVintheSearchfortextbox,andclickontheGobutton.TheNPVfunctionnamewillappearintheSelectafunctionlistbox.ClickontheOKbuttonaccessthedialogboxwherethefunctionargumentsareentered.TheargumentstotheNPVfunctionCostBenefitAnalysis,Slide49Copyright©2004,JimSchwab,UniversityofTexasatAustinThefirstargumenttotheNPVfunctionisthediscountrate,whichwewillenterdirectlyas8%.ThesecondargumenttotheNPVfunctionisthecellscontainingtheincreasedmaintenancefor6laneexpansion,'ProjectedSavings'!C2:C26.RemembertoenterthequotemarksaroundthenameoftheworksheetAnnualMaintenancebecauseitcontainsaspace.Withtheargumentsentered,clickontheOKbutton.NPVforincreasedmaintenancefor6laneexpansionCostBenefitAnalysis,Slide50Copyright©2004,JimSchwab,Universit