第三章 电子表格建模艺术

TableofContents

Chapter3(TheArtofModelingwithSpreadsheets)TheEvergladeGoldenYearsCo.CashFlowProblem(Section3.1) 3.2–3.3TheProcessofModelingwithSpreadsheets(Section3.2) 3.3–3.11GuidelinesforBuilding“Good”SpreadsheetModels(Section3.3) 3.12–3.16DebuggingaSpreadsheetModel(Section3.4) 3.16–3.20TheEvergladeCashFlowProblemYearProjectedNetCashFlow(millionsofdollars)2003–82004–22005–42006320076200832009–4201072011–2201210TheEvergladeCashFlowProblemTheirshort-termcashflowrequirementscannotbemetwithoutborrowing.Along-term(10year)loancanbetakenwithaninterestrateof7%Aseriesofshort-termloans(1year)canbetakenwithaprojectedinterestrateof10%Question:Whichloan(orcombinationofloans)shouldbetaken,andinwhatamounts,inordertomaximizetheircash-balancepositionafter10years?TheProcessofModelingPlan:VisualizeWhereYouWanttoFinishAcommonstumblingblockinthemodelingprocessoccursrightatthebeginning—”HowdoIgetstarted?”Atthispointitcanbeusefultoaskthequestion—”WheredoIwanttoendup?”WhatinformationdoIneedtoprovideinareport?Whatshouldthe“answer”looklike?Whatkindsofnumbersneedtobeincludedintherecommendation?WhatinformationisneededintheEvergladeCashFlowproblem?Plan:DoSomeCalculationsbyHandAnothercommonstumblingblockariseswhentryingtoenteraformulainoneoftheoutputcells.Atthispoint,itcanbeusefultodosomecalculationsbyhand.Justpicksomenumbersforthechangingcellsanddeterminewhattheresultsshouldbe.SupposeEvergladetakesouta$6millionlong-termloanandthenaddsshort-termloansof$2millionin2003and$5millionin2004.Howmuchcashwilltheyhaveleftattheendof2003?Howmuchcashwilltheyhaveleftattheendof2004?Plan:SketchOutaSpreadsheetBuild:StartwithaSmallVersionIfthemodeliscomplicated,startwithasmall,readilymanageableversionofthemodel.Firstmakesureyou’vegotthelogicofthemodelworkedoutcorrectlyforthesmallversion.Onlythenexpandittofullsize.Test:TesttheSmallVersionoftheModelTestthesmallmodelthoroughly.Tryvaluesinthechangingcellsforwhichyouknowtheanswers(atleastapproximately).Examples:AllzeroesAllonesVerylargenumbersNumbersforwhichyou’vedonehandcalculationsExpandthemodeltofullsize(copyingformulastothelateryears).Testthelargemodelinasimilarway.Analyze:AnalyzetheModel(BeforeSolver)Analyze:AnalyzetheModel(AfterSolver)GuidelinesforBuilding

“Good”SpreadsheetModelsEntertheDataFirstAnyspreadsheetmodelisdrivenbythedataItiseasier(andusuallybetter)tobuildthemodelaroundthedataOrganizeandClearlyIdentifytheDataRelevantdatashouldbegrouped(abularform)AlldatashouldbelabeledUnitsshouldbeidentifiedEnterEachPieceofDataintoOneCellOnlyRefertotheoriginaldataasneededThismakesthemodelmucheasiertomodify(onlyneedtochangedatainoneplace)GuidelinesforBuilding

“Good”SpreadsheetModelsSeparateDatafromFormulasAvoidputtingnumbersdirectlyinformulasPutnumbersindatacellsandrefertothemasneededThismakesalldatavisibleandeasiertomodifyKeepItSimpleAvoid“powerfunctions”ofExcelifpossibleBreakoutcomplicatedformulasintosubtotalsUseRangeNamesRefertodatacellsandblocksofcellsusingExcel’srangenamefeatureRangenamesmakeformulasandtheSolvermodelmucheasiertoreadCaremustbetakennottooveruserangenamesandtomakesuretheyremaincorrectlydefined.GuidelinesforBuilding

“Good”SpreadsheetModelsUseRelativeandAbsoluteReferencestoSimplifyCopyingFormulasWhenevermultiplerelatedformulaswillbeneeded,trytoentertheformulajustonce,andthenuseExcel’sfillcommandstoreplicatetheformula.Thismakesthemodeleasertobuildandalsoreducestypos.UseBorders,Shading,andColorstoDistinguishCellTypesForexample:Datacellsinblue(noborder)Changingcellsinyellow(regularborder)Targetcellinorange(heavyborder)ShowEntireModelonSpreadsheetAlldatashouldbevisible.Allconstraintsshouldbeonthespreadsheet(notburiedinSolver),preferablyinthreeconsecutivecells.ThreeTestsfora“Good”SpreadsheetModelYoushouldbeabletoimmediatelyidentifythedatacells,changingcells,andtargetcell.Allelementsofthemodelshouldbevisibleonthespreadsheet(includingallconstraints).YoushouldnothavetolookintheSolverdialogueboxtofigureoutthemodel.Eachequationshouldbesimpleenoughthatyoucantellwhattheequationisineachoutputcellwithoutlooking.APoorSpreadsheetModelDebuggingaSpreadsheetModel:TheTogglePressingcontrol-~togglesback-and-forthbetweenshowingvaluesandshowingformulasinthespreadsheet.DebuggingwiththeAuditingToolbarThebuttonthirdfromtheleftcanbeusedtotracethedependentsofacell,t