某咨询－战略方法之一

Author:CollinsQianReviewer:BrianBilello

bcBainMathMarch1998Copyright©1998Bain&Company,Inc.1CU7112997ECABainMathAgenda

BasicmathFinancialmathStatisticalmath2CU7112997ECABainMathAgenda

BasicmathratioproportionpercentinflationforeignexchangegraphingFinancialmathStatisticalmath3CU7112997ECABainMathRatio

Definition:Application:Note:TheratioofAtoBiswrittenorA:BABAratiocanbeusedtocalculatepriceperunit(),giventhetotalrevenueandtotalunitsPriceUnittotalrevenue=

Given:

==Answer:PriceUnit$9MM1.5MMThemathforratiosissimple.Identifyingarelevantunitcanbechallengingtotalunits=

price/unit=

$9.0MM1.5MM$?$6.04CU7112997ECABainMathProportion

Definition:IftheratioofAtoBisequaltotheratioofCtoD,thenAandBareproportionaltoCandD.Application:

=

ItfollowsthatAxD=BxCABCDRevenue=SG&A=Given:$135MM$83MM$270MM$?19961999Answer:$135MM

$270MM$83MM$?135MMx?=83MMx270MM83MMx270MM135MM=TheconceptofproportioncanbeusedtoprojectSG&Acostsin1999,givenrevenuein1996,SG&Acostsin1996,andrevenuein1999(assumingSG&Aandrevenuein1999areproportionaltoSG&Aandrevenuein1996)?==$166MM5CU7112997ECABainMathPercent

Definition:Apercentage(abbreviated“percent”)isaconvenientwaytoexpressaratio.Literally,percentagemeans“per100.”Application:Inpercentageterms,0.25=25per100or25%InherfirstyearatBain,anAClogged7,000frequentfliermilesbyflyingtoherclient.Inhersecondyear,shelogged25,000miles.Whatisthepercentageincreaseinmiles?Given:ApercentagecanbeusedtoexpressthechangeinanumberfromonetimeperiodtothenextAnswer:

-1=3.57-1=2.57=257%25,0007,000%change==-1

newvalue-originalvalueoriginalvaluenewvalueoriginalvalueTheratioof5to20isor0.255206CU7112997ECABainMathInflation-DefinitionsIfanitemcost$1.00in1997andcost$1.03in1998,inflationwas3%from1997to1998.Theitemisnotintrinsicallymorevaluablein1998-thedollarislessvaluableWhencalculatingthe“real”growthofadollarfigureovertime(e.g.,revenuegrowth,unitcostgrowth),itisnecessarytosubtractouttheeffectsofinflation.Inflationarygrowthisnot“real”growthbecauseinflationdoesnotcreateintrinsicvalue.Definition:Apricedeflatorisameasureofinflationovertime.RelatedTerminology:1.Real(constant) dollars:2.Nominal (current)dollars:3.PricedeflatorPricedeflator(currentyear)Pricedeflator(baseyear)Inflationbetweencurrentyearandbaseyear=Dollarfigure(currentyear)Dollarfigure(baseyear)=Dollarfiguresforanumberofyearsthatarestatedinachosen“base”year’sdollarterms(i.e.,inflationhasbeentakenout).Anyyearcanbechosenasthebaseyear,butalldollarfiguresmustbestatedinthesamebaseyearDollarfiguresforanumberofyearsthatarestatedineachindividualyear’sdollarterms(i.e.,inflationhasnotbeentakenout).Inflationisdefinedastheyear-over-yeardecreaseinthevalueofaunitofcurrency.7CU7112997ECABainMath

Inflation-U.S.PriceDeflators*1996isthebaseyearNote:ThesearetheU.S.PriceDeflatorswhichWEFAGrouphasforecastedthroughtheyear2020.ThelibraryhaspurchasedthistimeseriesforallBainemployeestouse.Adeflatortablelistspricedeflatorsforanumberofyears.8CU7112997ECABainMathInflation-Realvs.NominalFigures

Tounderstandhowacompanyhasperformedovertime(e.g.,intermsofrevenue,costs,orprofit),itisnecessarytoremoveinflation,(i.e.userealfigures).Sincemostcompaniesusenominalfiguresintheirannualreports,ifyouareshowingtheclient’srevenueovertime,itispreferabletousenominalfigures.Foranexperiencecurve,whereyouwanttounderstandhowpriceorcosthaschangedovertimeduetoaccumulatedexperience,youmustuserealfiguresNote:Whentouserealvs.Nominalfigures:Whetheryoushouldusereal(constant)figuresornominal(current)figuresdependsonthesituationandtheclient’spreference.Itisimportanttospecifyonslidesandspreadsheetswhetheryouareusingrealornominalfigures.Ifyouareusingrealfigures,youshouldalsonotewhatyouhavechosenasthebaseyear.9CU7112997ECABainMathInflation-Example(1)

(1970-1992)Adjustingforinflationiscriticalforanyanalysislookingatpricesovertime.Innominaldollars,GE’swasherpriceshaveincreasedbyanaverageof4.5%since1970.Whenyouusenominaldollars,itisimpossibletotellhowmuchofthepriceincreasewasduetoinflation.$2,00072Nominaldollars4.5%PriceofaGEWasher1970717374757677787980818283848586878889909192$0$500$1,000$1,500CAGR10CU7112997ECABainMathInflation-Example(2)PriceofaGEWasherCAGR(1970-1992)(1.0%)4.5%197071727374757677787980818283848586878889909192$0$500$1,000$1,500$2,000$2,500$3,000NominaldollarsReal(1992)dollarsIfyouuserealdollars,youcanseewhathashappenedtoinflation-adjustedprices.Theyhavefallenanaverageof1.0%peryear.11CU7112997ECABainMathInflation-Exercise(1)Considerthefollowingrevenuestreaminnominaldollars:Revenue($million)199020.5199125.3199227.4199331.2199436.8199545.5199651.0Howdowecalculatetherevenuestreaminrealdollars?12CU7112997ECABainMathInflation-Exercise(2)Answer:Step1:Chooseabaseyear.Forthisexample,wewilluse1990Step2:Finddeflatorsforallyears(fromthedeflatortable):(1990)=85.34(1991)=88.72(1992)=91.16(1993)=93.54(1994)=95.67(1995)=98.08Step3:Usetheformulatocalculaterealdollars:Pricedeflator(currentyear)Dollarfigure(currentyear)Pricedeflator(baseyear)Dollarfigure(baseyear)Step4:Calculatetherevenuestreaminreal(1990)dollarsterms:1990:1991:1992:1993:=,X=20.585.3485.341994:1995:1996:=20.5X=,X=24.388.7285.3425.3X=,X=25.791.1685.3427.4X=,X=28.593.5485.3431.2X=,X=32.895.6785.3436.8X=,X=39.698.0885.3445.5X=,X=43.5100.0085.3451.0XRevenue($Million)199020.5199124.3199225.7199328.5199432.8199539.6199643.5(1996)=100.0013CU7112997ECABainMathForeignExchange-DefinitionsInvestmentsemployedinmakingpaymentsbetweencountries(e.g.,papercurrency,notes,checks,billsofexchange,andelectronicnotificationsofinternationaldebitsandcredits)Priceatwhichonecountry’scurrencycanbeconvertedintoanother’sTheinterestandinflationratesofagivencurrencydeterminethevalueofholdingmoneyinthatcurrencyrelativetoinothercurrencies.Inefficientinternationalmarkets,exchangerateswilladjusttocompensatefordifferencesininterestandinflationratesbetweencurrenciesForeignExchange:ExchangeRate:14CU7112997ECABainMathForeignExchangeRates1)US$equivalent=USdollarsper1selectedforeigncurrencyunit2)CurrencyperUS$=selectedforeigncurrencyunitsper1USdollarTheWallStreetJournalTuesday,November25,1997CurrencyTradingMonday,November24,1997ExchangeRatesCountryArgentina(Peso)Britain(Pound)US$Equiv.11.00011.6910CurrencyperUS$20.99990.5914CountryFrance(Franc)Germany(Mark)US$Equiv.0.17190.5752CurrencyperUS$5.81851.7384CountrySingapore(dollar)US$Equiv.0.6289CurrencyperUS$1.5900Financialpublications,suchastheWallStreetJournal,provideexchangerates.15CU7112997ECABainMathForeignExchange-ExercisesQuestion1:Answer:Question2:Answer:Question3:Answer:650.28USdollars=?Britishpoundsfromtable:0.5914£=US$1.00$650.28x=384.58£1490.50Francs=?US$fromtable:$0.1719=1Franc1490.50Francx=$256.221,000GermanMarks=?Singaporedollarsfromtable:$0.5752=1Mark1.59Singaporedollar=US$11,000GermanMarksxx=914.57Singaporedollars0.5914£US$1$0.17191Franc$0.57521Mark1.59SingaporedollarUS$116CU7112997ECABainMathGraphing-LinearX0Y(X1,Y1)(X2,Y2)bXYTheformulaforalineis:y=mx+bWhere,m=slope==y2-y1x2-x1b=theyintercept=theycoordinatewhenthexcoordinateis““0””yx17CU7112997ECABainMathGraphing-LinearExercise#1Formulaforline:y=mx+bInthisexercise,y=15x+400,where,02004006008001,0001,2001,4001,6001,800$2,000Dollarschanging050100People(100,1900)(50,1150)Thecatererwouldcharge$1900fora100personparty.yxXaxis=peopleYaxis=dollarschargedm=slope==15b=Yintercept=400dollarscharged(whenpeople=0)Acaterercharges$400.00forsettingupaparty,plus$15.00foreachperson.Howmuchwouldthecatererchargefora100personparty?Usingthisformula,youcansolvefordollarscharged(y),givenpeople(x),andvice-versa18CU7112997ECABainMathGraphing-LinearExercise#2(1)Alampmanufacturerhascollectedasetofproductiondataasfollows:NumberoflampsProduced/DayProductionCost/Day1008509009501,000$2,000$9,500$10,000$10,500$11,000Whatisthedailyfixedcostofproduction,andwhatisthecostofmaking1,500lamps?19CU7112997ECABainMathGraphing-LinearExercise#2(2)08,00016,000ProductionCost/Day05001,0001,500Produced/Day(1,500,16,000)(1,000,11,000)Formulaforline:y=mx+bXaxis=#oflampsproduced/dayYaxis=productioncost/dayM=slope====10b=Yintercept=productioncost(i.e.,thefixedcost)whenlamps=0y=mx+bb=y-mxb=2,000-10(100)b=1,000Thefixedcostis$1,000y=10x+1,000For1,500lamps:y=10(1,500)+1,000y=15,000+1,000y=16,00011,000-2,0001,000-1009,000900(100,2,000)X=900Y=9,000yxThecostofproducing1,500lampsis$16,00020CU7112997ECABainMathGraphing-Logarithmic(1)Log:A“log”orlogarithmofgivennumberisdefinedasthepowertowhichabasenumbermustberaisedtoequalthatgivennumberUnlessotherwisestated,thebaseisassumedtobe10Y=10x,thenlog10Y=XMathematically,ifWhere,Y=givennumber10=baseX=power(orlog)Forexample:100=102canbewrittenaslog10100=2orlog100=221CU7112997ECABainMathGraphing-Logarithmic(2)Foralogscaleinbase10,asthelinearscalevaluesincreasebytentimes,thelogvaluesincreaseby1.98765432101,000,000,000100,000,00010,000,0001,000,000100,00010,0001,000100101Logpapertypicallyusesbase10Log-logpaperislogarithmiconbothaxes;semi-logpaperislogarithmicononeaxisandlinearontheotherLogScaleLinearScale22CU7112997ECABainMathGraphing-Logarithmic(3)Themostusefulfeatureofaloggraphisthatequalmultiplicativechangesindataarerepresentedbyequaldistancesontheaxesthedistancebetween10and100isequaltothedistancebetween1,000,000and10,000,000becausethemultiplicativechangeinbothsetsofnumbersisthesame,10ItisconvenienttouselogscalestoexaminetherateofchangebetweendatapointsinaseriesLogscalesareoftenusedfor:Experiencecurve(alog/logscaleismandatory-naturallogs(lnorloge)aretypicallyusedpricesandcostsovertimeGrowthSharematricesROS/RMSgraphsLineShapeofDataPlotsExplanationAstraightlineThedatapointsarechangingatthesameratefromonepointtothenextCurvingupwardTherateofchangeisincreasingCurvingdownwardTherateofchangeisdecreasingInmanysituations,itisconvenienttouselogarithms.23CU7112997ECABainMathAgendaBasicmathFinancialmathsimpleinterestcompoundinterestpresentvalueriskandreturnnetpresentvalueinternalrateofreturnbondandstockvaluationStatisticalmath24CU7112997ECABainMathSimpleInterestDefinition:SimpleinterestiscomputedonaprincipalamountforaspecifiedtimeperiodTheformulaforsimpleinterestis:i=prtwhere,p=theprincipalr=theannualinterestratet=thenumberofyearsApplication:SimpleinterestisusedtocalculatethereturnoncertaintypesofinvestmentsGiven:Apersoninvests$5,000inasavingsaccountfortwomonthsatanannualinterestrateof6%.Howmuchinterestwillshereceiveattheendoftwomonths?Answer: i=prti=$5,000x0.06xi=$5021225CU7112997ECABainMathCompoundInterest“Moneymakesmoney.Andthemoneythatmoneymakes,makesmoremoney.”-BenjaminFranklinDefinition:Compoundinterestiscomputedonaprincipalamountandanyaccumulatedinterest.Abankthatpayscompoundinterestonasavingsaccountcomputesinterestperiodically(e.g.,dailyorquarterly)andaddsthisinteresttotheoriginalprincipal.Theinterestforthefollowingperiodiscomputedbyusingthenewprincipal(i.e.,theoriginalprincipalplusinterest).Theformulafortheamount,A,youwillreceiveattheendofperiodnis:A=p(1+)ntwhere, p=theprincipalr=theannualinterestraten=thenumberoftimescompoundingisdoneinayeart=thenumberofyearsrnNotes:Asthenumberoftimescompoundingisdoneperyearapproachesinfinity(asincontinuouscompounding),theamount,A,youwillreceiveattheendofperiodniscalculatedusingtheformula:A=pertTheeffectiveannualinterestrate(oryield)isthesimpleinterestratethatwouldgeneratethesameamountofinterestaswouldthecompoundrate26CU7112997ECABainMathCompoundInterest-Application$1,000.00$30.00$1,030.00$30.90$1,060.90$31.83$1,092.73$32.78$1,125.51$0$250$500$750$1,000$1,250Dollarsi1i2i3i4A1A2A3A41stQuarter2ndQuarter3rdQuarter4thQuarterGiven:Whatamountwillyoureceiveattheendofoneyearifyouinvest$1,000atanannualrateof12%compoundedquarterly?Answer:A=p(1+)nt=$1,000(1+)4=$1,125.51rn0.124DetailedAnswer:Attheendofeachquarter,interestiscomputed,andthenaddedtotheprincipal.Thisbecomesthenewprincipalonwhichthenextperiod’sinterestiscalculated.Interestearned(i=prt): i1=$1,000x0.12x0.25 i2=$1,030x0.12x0.25 i3=$1,060.90x0.12x0.25 14=$1,092.73x0.12x0.25=$30.00=$30.90=$31.83=$32.78Newprinciple A1=$1,000+$30A2=$1,030+30.90 A3=$1,060.90+31.83 A4=$1,092.73+32.78=$1,030=$1,060.90 =$1,092.73=$1,125.5127CU7112997ECABainMathPresentValue-Definitions(1)TimeValueofMoney:Atdifferentpointsintime,agivendollaramountofmoneyhasdifferentvalues.Onedollarreceivedtodayisworthmorethanonedollarreceivedtomorrow,becausemoneycanbeinvestedwithsomereturn.PresentValue:PresentvalueallowsyoutodeterminehowmuchmoneythatwillbereceivedinthefutureisworthtodayTheformulaforpresentvalueis: PV=Where,C=theamountofmoneyreceivedinthefuturer=theannualrateofreturnn=thenumberofyearsiscalledthediscountfactorThepresentvaluePVofastreamofcashisthen:PV=C0+++WhereC0isthecashexpectedtoday,C1isthecashexpectedinoneyear,etc.1(1+r)nC

(1+r)nC11+rC2(1+r)2Cn(1+r)n28CU7112997ECABainMathPresentValue-Definitions(2)Thepresentvalueofaperpetuity(i.e.,aninfinitecashstream)ofis:PV=Aperpetuitygrowingatrateofghaspresentvalueof:PV=ThepresentvaluePVofanannuity,aninvestmentwhichpaysafixedsum,eachyearforaspecificnumberofyearsfromyear1toyearnis:Perpetuity:Growingperpetuity:Annuity:CrC

r-gPV=Cr-1(1+r)nCr29CU7112997ECABainMathPresentValue-Exercise(1)1) $10.00today2) $20.00fiveyearsfromtoday3) Aperpetuityof$1.504) Aperpetuityof$1.00,growingat5%5) Asixyearannuityof$2.00Assumeyoucaninvestat16%peryearWhichofthefollowingwouldyouprefertoreceive?30CU7112997ECABainMathPresentValue-Exercise(2)*Thepresentvalueisnegativebecausethisisthecashoutflowrequiredtodayreceiveacashinflowatalatertime1) $10.00today,PV=$10.002) $20.00fiveyearsfromtoday,ForHP12C:5163) Aperpetuityof$1.50,PV==$9.384) Aperpetuityof$1.00,growingat5%,PV==$9.095) Asixyearannuityof$2.00,PV=-=$7.37$1.500.16$1.000.16-0.05Theoptionwiththehighestpresentvalueis#1,receiving$10.00today$2.000.161(1+0.16)5$2.000.16FViPVN=(9.52)*20()()PV==$9.52$20.00(1+0.16)5Answer:31CU7112997ECABainMathRiskandReturnNotallinvestmentshavethesameriskinvestingintheU.S.stockmarketismoreriskythaninvestinginaU.S.governmenttreasurybill,butlessriskythaninvestinginthestockmarketofadevelopingcountryMostinvestorsareriskaverse-theyavoidriskwhentheycandosowithoutsacrificingreturnRiskaverseinvestorsdemandahigherreturnonhigherriskinvestmentsAsafedollarisworthmorethanariskyone.32CU7112997ECABainMathNetPresentValueNetpresentvalue(NPV)isthemethodusedinevaluatinginvestmentswherebythepresentvalueofallcaseoutflowsrequiredfortheinvestmentareaddedtothepresentvalueofallcashinflowsgeneratedbytheinvestmentCashoutflowshavenegativepresentvalues;cashinflowshavepositivepresentvaluesTherateusedtocalculatethepresentvaluesisthediscountrate.Thediscountrateistherequiredrateofreturn,ortheopportunitycostofcapital(i.e.,thereturnyouaregivinguptopursuethisproject)AninvestmentisacceptableiftheNPVispositiveIncapitalbudgeting,thediscountrateusediscalledthehurdlerateDefinition:33CU7112997ECABainMathInternalRateofReturnTheinternalrateofreturn(IRR)isthediscountrateforwhichthenetpresentvalueiszero(i.e.,thecostoftheinvestmentequalsthefuturecashflowsgeneratedbytheinvestment)TheinvestmentisacceptablewhentheIRRisgreaterthantherequiredrateofreturn,orhurdlerateUnfortunately,comparingIRRsandchoosingthehighestonesometimesdoesnotleadtothecorrectanswer.Therefore,IRRjectAcanhaveahigherIRRbutlowerNPVthanprojectB;thatis,IRRsdoNOTindicatethemagnitudeofanopportunityprojectswithcashflowsthatfluctuatebetweennegativeandpositivemorethanoncehavemultipleIRRsIRRscannotbecalculatedforallnegativecashflowsDefinition:34CU7112997ECABainMathNPVandIRR-Exercise*YoucanusethisabbreviatedformatsincetheotherdatahasnotchangedfrompartaGiven:Aninvestmentcosting$2MMwillproducecashflowsof$700,000inYear1,$700,000inYear2,and$900,000inYear3.Calculateitsnetpresentvalueatdiscountratesof(a)5%,(b)10%,and(c)15%.Also,(d)calculatetheproject’’sIRR.Answer:Usinga5%discountrate,NPV=-$2MM+++=$79,041$700,000(1.05)$700,000(1.05)2$900,000(1.05)3ForHP12c:AnothereasywaytocalculateanIRRistousetheIRRfunctioninExcel:=IRR(C1,C2,C3,………Cn)whereC1isthecashflowinYear1,C2isthecashflowinYear2,etc.Inthisexample,“=IRR(-2,000,000,700,000,700,000,900,000)”=7.01%fCLXfiCHSCF02,000,000g700,000CFjg700,000CFjg900,000CFjg5NPV=79,041ForHP12C:Using10%discountrate,NPV=-$2MM+++=-$108,941$700,000(1.10)$700,000(1.10)2$900,000(1.10)3ForHP12C:fi10NPV=-$108941Using15%discountrate,NPV=-$2MM+++=-$270,239$700,000(1.15)$700,000(1.15)2$900,000(1.15)3ForHP12C:fi15NPV=-$270,239fIRR=7.01%***a)b)c)d)35CU7112997ECABainMathBondandStockValuationTheconceptofNetPresentValuecanbeappliedtovaluingbondsandstocks.Bonds:Themarketvalue,MV,todayofabondwithafacevalueoffandacouponrateofcmaturinginnyearswhenthemarketinterestrateisris:Stocks:Thepricetoday,p0ofastockthatpaysdividendsofdtinYeartandthatyousellinYearnforpnis,forsomeoneexpectingarateofreturnofr:fc1+rfc(1+r)2fc(1+r)nf(1+r)nMV=++…++d11+rd2(1+r)2dn(1+r)npn(1+r)np0=++…++36CU7112997ECABainMathAgendaBasicmathFinancialmathStatisticalmathaveragesweightedaverageslinearregression37CU7112997ECABainMathAverages*SometimesreferredtoasthearithmeticmeanDefinition:(Sumofdatapoints)Exercise:Answer:Mean*=(Numberofdatapoints)Median=Themiddlevalueofasetofrankeddatapoints(i.e.,thedatapointforwhichthereisanequalnumberofdatapointswithvalueshigherandlower).Foranevennumberofdatapoints,themedianisthemeanofthetwomiddlevalues.Mode=Thevaluethatoccursmostfrequentlyinasetofdatapoints.Thereneednotbeamodeinasetofdata.Calculatethemean,median,andmodeofthefollowingdataset:1,2,3,4,5,6,7,8,8,8,9,10,50Mean=(Sumofdatapoints)(Numberofdatapoints)=(1+2+3+4+5+6+7+8+8+8+9+10+50)13Median==9.37(becausetherearesixdatapointswithhighervalues than7andsixdatapointswithlowervalues)Mode=8(theonlynumberthatoccursrepeatedly)38CU7112997ECABainMathUsingAverages(Arithmetic)Mean:Themeanisanartificialstatisticinthatitneednotcoincidewithanypointinyourdatasetinthepreviousexample,themeanof9.3isnotintheoriginaldatasetExtremepointshavemoreeffectthanthoseclosertothemiddleThemedianisaffectedbythenumberof,butnotthevalueof,extremepointsinthedatasetThemedianisleastusefulinsmalldatasets(e.g.,themedianof1.3and10is3,whichtellsuslittle)ThemodecanbemeaninglessinadatasetwithseveralvaluesthatoccurrepeatedlyMostusefulfordatasetsthathaveoutliersMostusefulwhenyouneedasimpleaveragethatweightsallofthedatapointsequallyMostusefulfordatasetswithoneorfewvaluesthatoccurrepeatedlyMedian:Mode:CharacteristicsWhentoUse39CU7112997ECABainMathWeightedAveragesDefinition:Weightedaverage=(Sumofeachdatapointmultipliedbyitsweight)(Sumoftheweight)Weightedaveragesareappropriatefordatasetsinwhichthepointstobeaveragedhavedifferentlevelsofimportance.Weightedaveragesadjustfortheimportanceofdifferentdatapointsbyusingtheirweights.Exercise:Amattressmanufacturerproducesmattressesofallsizes(twin,queen,andkingsize).Whatistheaveragepriceofamattress?Given:Answer:$(99x80+159x120+199x30)80+120+30=$143.35Application:MattressSizePriceMonthlyVolumeTwinQueenKing$99.00$159.00$199.008012030Theweightedaverageofmattresspriceis:40CU7112997ECABainMathLinearRegression-DefinitionsRegressionsareusedtounderstandhowoneormoreindependentvariablesaffectadependentvariable.DependentvariableIndependentvariablesy=a+b••x1+c••x2+……+eThevalueofthedependentvariablewhentheindependentvariablesarezeroCanbethoughtofasthepartofynotaffectedbythexsInterceptTellshowmucheachindependentvariableaffectsyIfb=0,thentheindependentvariablex1hasnoeffectonyCoefficients“Noise”thatyoucannotexplainwithyourchoiceofvariablesNoregressionisperfect!ErrorTermAlinearregressioncanbeexpressedasanequation:41CU7112997ECABainMathLinearRegression-KeyStatisticsR-squared:Tellstowhatextenttheindependentvariables““explain”thevalueofthedependentvariableGenerallyalowR-squaredmeansthatyourindependentvariablesdonotexplainthevalueofdependentvariablewell.Therefore,youmayneedtorethinkyourindependentvariables.TellswhethertheestimatedcoefficientsaresignificantlydifferentfromzeroIfacoefficientofaparticularindependentvariableisnotsignificantlydifferentfromzero,youmaynotconcludethattheparticularindependentvariablehasaneffectonthedependentvariableTellstherangeofvaluesinwhichanestimatedcoefficientislikelytofall(i.e.,howmuchlargerandsmallerthetruecoefficientmaybefromtheestimatedcoefficient)T-statisticsshouldbe>=2or<=-2(fora95%confidencelevel)R-squaredcanonlybebetween0and1UsuallyanR-squaredabove0.7isconsideredhighItistypicaltousetwostandarddeviations(ora95%confidenceinterval)T-statistics:Standarddeviation:ExplanationReferencePointsStatisticsTherearethreekeystatisticsthathelpexplainregressionoutput.42CU7112997ECABainMathSingleVariableLinearRegression-DataForacommercialbankclient,aBainAChasbeenaskedtoexplaintherelationshipbetweenthenumberofdepositaccountsandthedemanddepositaccount(DDA)balance.Hehasobtainedthefollowingdatafromthirtydifferentbranchesofthebank:Branch123456789101112131415DDABalance($000)2,240.02,210.21,368.41,840.01,884.22,016.21,890.81,345.01,448.21,505.01,525.81,603.41,884.21,528.21,384.2NumberofAccounts900906500741789889874510529420679872924607452Branch161718192021222324252627282930DDABalance($000)1,790.41,865.82,046.22,645.61,481.61,641.81,360.61,680.01,756.42,324.21,250.21,165.81,450.01,885.21,842.4NumberofAccounts7297948441,00562170367971878593857250862980187643CU7112997ECABainMathLinearRegression-StepsInputthedataintoanexcelspreadsheetEachvariableshouldbeputinitsowncolumnStep1:RuntheregressionChoose““dataanalysis””fromthe“tools”bar,andthenchoose““regression””Whenthe““regression”windowappears,dragthedependentvariabledata(inthiscase,the““DDAbalance””)into“InputYrange”,andtheindependentvariabledata(“numberofaccounts”)into““InputXrange)Toviewtheregressionresultsgraphically,checkofftheboxforalinefitplotStep2:InterprettheresultsStep3:Thethreestepsfordoingregressionanalysisare:44CU7112997ECABainMathSingleVariableLinearRegression-OutputSUMMARYOUTPUTRegressionStatisticsMultipleR0.86823719RSquare0.753835818AdjustedRSquare0.74504424StandardError174.6960575Observations30ANOVAdfSSMSFSignificanceFRegression12616833.8372616833.83785.745223955.10952E-10Residual28854523.949730518.71249Total293471357.787CoefficientsStandardErrortStatP-valueLower95%Upper95%Lower95.0%Upper95.0%Intercept384.9724417148.57328852.5911282270.01502387980.63351466689.311368780.63351466689.3113687XVariable1.8496295650.1997467819.2598717025.10952E-101.4404663732.2587927581.4404663732.258792758#ofaccountsExcelgeneratesthefollowingregressionoutput:45CU7112997ECABainMathSingleVariableLinearRegression-Actualvs.PredictedDataR²=0.75ActualDDABalancePredictedDDABalance00TheregressiondoesagoodjobofpredictingDDABalance.46CU7112997ECABainMathSingleVariableLinearRegression-AnswerR-squared=0.75.Thismeansthat75%oftheDDAbalancecanbe“explained’’bythenumberofaccounts.TheT-statisticsonthexvariable(i.e.,numberofaccounts)is9.3,whichissignificantlybiggerthanthereferencevalueof“2”,indicatingastrongrelationshipbetweenthenumberofaccountsandDDAbalanceThelinefitplotshowshowcloselythepredictedDDAbalancestiewiththeactualnumbersTheestimatedregressionlineis=DDAbalance=$385,000+($1,850xnumberofaccounts)Thismeansthateveryincrementalaccountbringsin$1,850ofadditionalDDAbalancesy-interceptCoefficien