Decision-Analysis-Chapter-09

SpreadsheetModeling&DecisionAnalysisAPracticalIntroductiontoManagementScience5theditionCliffT.RagsdaleRegressionAnalysisChapter9IntroductiontoRegressionAnalysisRegressionAnalysisisusedtoestimateafunctionf()thatdescribestherelationshipbetweenacontinuousdependentvariableandoneormoreindependentvariables.Y=f(X1,X2,X3,…,Xn)+eNote:•f()describessystematicvariationintherelationship.erepresentstheunsystematicvariation(orrandomerror)intherelationship.AnExampleConsidertherelationshipbetweenadvertising(X1)andsales(Y)foracompany.Thereprobablyisarelationship......asadvertisingincreases,salesshouldincrease.Buthowwouldwemeasureandquantifythisrelationship?SeefileFig9-1.xlsAScatterPlotoftheData0.0100.0200.0300.0400.0500.0600.02030405060708090100Advertising(in$1,000s)Sales(in$1,000s)TheNatureofaStatisticalRelationshipRegressionCurveProbabilitydistributionsforYatdifferentlevelsofXYXASimpleLinearRegressionModelThescatterplotshowsalinearrelationbetweenadvertisingandsales.Sothefollowingregressionmodelissuggestedbythedata,Thisreferstothetruerelationshipbetweentheentirepopulationofadvertisingandsalesvalues.YXie011iiTheestimatedregressionfunction(basedonoursample)willberepresentedas,YXibbi011XoflevelgivenaatYofvaluefitted)(ofestimatedtheisYiˆDeterminingtheBestFitNumericalvaluesmustbeassignedtob0andb1ESSYYYX()(())iiniiinbbi1210112Themethodof“leastsquares”selectsthevaluesthatminimize:IfESS=0ourestimatedfunctionfitsthedataperfectly.WecouldsolvethisproblemusingSolver...UsingSolver...SeefileFig9-4.xlsTheEstimatedRegressionFunctionTheestimatedregressionfunctionis:..YXii3634255501UsingtheRegressionToolExcelalsohasabuilt-intoolforperformingregressionthat:–iseasiertouse–providesalotmoreinformationabouttheproblemSeefileFig9-1.xlsTheTREND()FunctionTREND(Y-range,X-range,X-valueforprediction)where:Y-rangeisthespreadsheetrangecontainingthedependentYvariable,X-rangeisthespreadsheetrangecontainingtheindependentXvariable(s),X-valueforpredictionisacell(orcells)containingthevaluesfortheindependentXvariable(s)forwhichwewantanestimatedvalueofY.Note:TheTREND()functionisdynamicallyupdatedwheneveranyinputstothefunctionchange.However,itdoesnotprovidethestatisticalinformationprovidedbytheregressiontool.Itisbesttwousethesetwodifferentapproachestodoingregressioninconjunctionwithoneanother.Evaluatingthe“Fit”R2=0.96910.0100.0200.0300.0400.0500.0600.02030405060708090100Advertising(in$000s)Sales(in$000s)TheR2StatisticTheR2statisticindicateshowwellanestimatedregressionfunctionfitsthedata.0R21ItmeasurestheproportionofthetotalvariationinYarounditsmeanthatisaccountedforbytheestimatedregressionequation.Tounderstandthisbetter,considerthefollowinggraph...ErrorDecompositionYXYY=b0+b1X^*Yi(actualvalue)Yi-YYi(estimatedvalue)^Yi-Y^Yi-Yi^PartitionoftheTotalSumofSquares(()()YY)YYYY2iininiiini11212or,TSS=ESS+RSSRRSSTSS1ESSTSS2MakingPredictionsEstimatedSales=36.342+5.550*65=397.092Sowhen$65,000isspentonadvertising,weexpecttheaveragesalesleveltobe$397,092...YXii3634255501Supposewewanttoestimatetheaveragelevelsofsalesexpectedif$65,000isspentonadvertising.TheStandardErrorThestandarderrormeasuresthescatterintheactualdataaroundtheestimateregressionline.Snkeiiin()YY211wherek=thenumberofindependentvariablesForourexample,Se=20.421Thisishelpfulinmakingpredictions...AnApproximatePredictionIntervalAnapproximate95%predictionintervalforanewvalueofYwhenX1=X1hisgivenbyYheS2YXhbbh011where:Example:If$65,000isspentonadvertising:95%lowerpredictioninterval=397.092-2*20.421=356.25095%upperpredictioninterval=397.092+2*20.421=437.934Ifwespend$65,000onadvertisingweareapproximately95%confidentactualsaleswillbebetween$356,250and$437,934.AnExactPredictionIntervalA(1-a)%predictionintervalforanewvalueofYwhenX1=X1hisgivenbyYXhbbh011(/,)YthnpS122awhere:SSnpeinhi1112121()()XXXXExampleIf$65,000isspentonadvertising:95%lowerpredictioninterval=397.092-2.306*21.489=347.55695%upperpredictioninterval=397.092+2.306*21.489=446.666Ifwespend$65,000onadvertisingweare95%confidentactualsaleswillbebetween$347,556and$446,666.Thisintervalisonlyabout$20,000widerthantheapproximateonecalculatedearlierbutwasmuchmoredifficulttocreate.Thegreateraccuracyisnotalwaysworththetrouble.ComparisonofPredictionIntervalTechniques1251752252753253754254755255752535455565758595AdvertisingExpendituresSalesRegressionLinePredictionintervalscreatedusingstandarderrorSePredictionintervalscreatedusingstandardpredictionerrorSpConfidenceIntervalsfortheMeanA(1-a)%confidenceintervalforthetruemeanvalueofYwhenX1=X1hisgivenby(/,)YthnaS122aYXhbbh011where:SSnaeinhi112121()()XXXXANoteAboutExtrapolationPredictionsmadeusinganestimatedregressionfunctionmayhavelittleornovalidityforvaluesoftheindependentvariablesthataresubstantiallydifferentfromthoserepresentedinthesample.MultipleRegressionAnalysisMostregressionproblemsinvolvemorethanoneindependentvariable.IfeachindependentvariablesvariesinalinearmannerwithY,theestimatedregressionfunctioninthiscaseis:Y