運籌學類電子表格建模藝術(shù)

Chapter3.

TheArtofModelingwithSpreadsheets第三章.電子表格建模的藝術(shù)運籌學TheEvergladeGoldenYearsCo.CashFlowProblem(Section3.1)(大沼澤地金色年代公司的現(xiàn)金流問題[第3.1節(jié)])TheProcessofModelingwithSpreadsheets(Section3.2)(使用電子表格建模的過程[第3.2節(jié)])GuidelinesforBuilding“Good”SpreadsheetModels(Section3.3)(建立好的電子表格模型的原則[第3.3節(jié)])DebuggingaSpreadsheetModel(Section3.4)(調(diào)試電子表格模型[第3.4節(jié)])TableofContents(主要內(nèi)容)TheEvergladeCashFlowProblemYearProjectedNetCashFlow(millionsofdollars)2003–82004–22005–42006320076200832009–4201072011–2201210TheEvergladeCashFlowProblemTheirshort-termcashflowrequirementscannotbemetwithoutborrowing.(短期的現(xiàn)金流難于滿足需求，有必要貸款)Along-term(10year)loancanbetakenwithaninterestrateof7%.(為期10年的長期貸款利率為7%)Aseriesofshort-termloans(1year)canbetakenwithaprojectedinterestrateof10%.(為期1年的短期貸款利率為10%)Whichloan(orcombinationofloans)shouldbetaken,andinwhatamounts,inordertomaximizetheircash-balancepositionafter10years?TheEvergladeCashFlowProblem要使10年以后的現(xiàn)金余額最大，應該選擇哪種貸款(或貸款組合)？貸款數(shù)目應多大？TheProcessofModelingVisualizewhereyouwanttofinish(設想一下你的目標)Dosomecalculationsbyhand(手工進行一些計算)Sketchoutaspreadsheet(建立一個電子表格)Startwithasmall-scalemodel(先建一個小模型)Expandthemodeltofullscale(擴展為完整的模型)Trydifferenttrialsolutionstocheckthelogic(利用不同的測試數(shù)據(jù)分析模型的邏輯關(guān)系)Evaluateproposedsolutionsand/oroptimizewithSolver(評估建議的解和/或利用Solver優(yōu)化)Plan(計劃)Build(建模)Test(測試)Analyze(分析)如果得出的解顯示模型不完善則回到計劃或建模步驟Acommonstumblingblockinthemodelingprocessoccursrightatthebeginning—HowdoIgetstarted?(建模過程中的一個常見問題出現(xiàn)在最開始的時候——我應該如何開始工作？)Atthispointitcanbeusefultoaskthequestion—WheredoIwanttoendup?(此時詢問“我想在哪里結(jié)束”這樣的問題會很有幫助)WhatinformationdoIneedtoprovideinareport?(我應該在報告中提供什么信息？)Plan:VisualizeWhereYouWanttoFinishWhatshouldthe“answer”looklike?(“答案”看起來應該像什么樣子？)Whatkindsofnumbersneedtobeincludedintherecommendation?(建議中應該包含哪些數(shù)字？)WhatinformationisneededintheEvergladeCashFlowproblem?(大沼澤地金色年代公司的現(xiàn)金流問題中，需要什么樣的信息？)Plan:VisualizeWhereYouWanttoFinishPlan:DoSomeCalculationsbyHandAnothercommonstumblingblockariseswhentryingtoenteraformulainoneoftheoutputcells.(另一個常見問題出現(xiàn)在為一個輸出單元格輸入公式時)Atthispoint,itcanbeusefultodosomecalculationsbyhand.(此時進行一些手工計算是很有用的)Justpicksomenumbersforthechangingcellsanddeterminewhattheresultsshouldbe.(為可變單元格選出一些數(shù)字并計算出結(jié)果)Plan:DoSomeCalculationsbyHandSupposeEvergladetakesouta$6millionlong-termloanandthenaddsshort-termloansof$2millionin2003and$5millionin2004.(假設大沼澤地公司借入了600萬美元的長期貸款，并于2003年借入了200萬美元的短期貸款，2004年借入了500萬美元的短期貸款)Howmuchcashwilltheyhaveleftattheendof2003?(2003年底將留下多少現(xiàn)金？)Howmuchcashwilltheyhaveleftattheendof2004?(2004年底將留下多少現(xiàn)金？)Plan:SketchOutaSpreadsheetBuild:StartwithaSmallVersionIfthemodeliscomplicated,startwithasmall,readilymanageableversionofthemodel.(如果模型比較復雜，可以先建立一個較小的易于管理的模型開始)Firstmakesureyou’vegotthelogicofthemodelworkedoutcorrectlyforthesmallversion.Onlythenexpandittofullsize.(首先要保證你從小規(guī)模的模型中獲得了模型的邏輯推理方式，然后才擴展到完整的模型)Build:StartwithaSmallVersionTest:TesttheSmallVersionoftheModelTestthesmallmodelthoroughly.(全面地測試這個小模型)Tryvaluesinthechangingcellsforwhichyouknowtheanswers(atleastapproximately).(在可變單元格中輸入一些數(shù)值，然后看電子表格給出的結(jié)果是否與你所知道的輸出單元格的數(shù)值相一致)Examples:(例如)Allzeroes(都為0)Allones(都為100萬美元)Test:TesttheSmallVersionoftheModelVerylargenumbers(非常大的數(shù)值)Numbersforwhichyou’vedonehandcalculations(經(jīng)過手工驗算的數(shù)值)Expandthemodeltofullsize(copyingformulastothelateryears).(將模型擴展完整[把公式拷貝到后面的年份])Testthelargemodelinasimilarway.(采用類似的方法測試大型的模型)運籌學Analyze:AnalyzetheModel(BeforeSolver)Analyze:AnalyzetheModel(AfterSolver)GuidelinesforBuilding“Good”SpreadsheetModelsEntertheDataFirst(先輸入數(shù)據(jù))Anyspreadsheetmodelisdrivenbythedata(所有電子表格模型都是通過數(shù)據(jù)驅(qū)動的)Itiseasier(andusuallybetter)tobuildthemodelaroundthedata(圍繞數(shù)據(jù)構(gòu)建模型更加簡單適用)OrganizeandClearlyIdentifytheData(組織和清楚地標識數(shù)據(jù))Relevantdatashouldbegrouped(e.g.intabularform)(相關(guān)的數(shù)據(jù)應該組合在一起，如采用表格的形式)Alldatashouldbelabeled(所有的數(shù)據(jù)都應該有標題)Unitsshouldbeidentified(應當注明數(shù)據(jù)單位)GuidelinesforBuilding“Good”SpreadsheetModelsEnterEachPieceofDataintoOneCellOnly(每個數(shù)據(jù)輸入唯一的一個單元格)Refertotheoriginaldataasneeded(如果需要都指向原始數(shù)據(jù))Thismakesthemodelmucheasiertomodify(onlyneedtochangedatainoneplace)(這樣做使模型便于修改，只需在一個地方更改數(shù)據(jù))SeparateDatafromFormulas(將數(shù)據(jù)與公式分離)Avoidputtingnumbersdirectlyinformulas(避免在公式中直接使用數(shù)字)GuidelinesforBuilding“Good”SpreadsheetModelsPutnumbersindatacellsandrefertothemasneeded(將數(shù)字輸入數(shù)據(jù)單元格，在需要時引用)Thismakesalldatavisibleandeasiertomodify(這樣做可以使所有數(shù)據(jù)可視并且便于修改)KeepItSimple(保持簡單化)Avoid“powerfunctions”ofExcelifpossible(避免在可以使用簡單函數(shù)的情況下，采用Excel的功能強大的函數(shù))Breakoutcomplicatedformulasintosubtotals(把復雜的公式分成幾段再求和)GuidelinesforBuilding“Good”SpreadsheetModelsUseRangeNames(使用區(qū)域名稱)RefertodatacellsandblocksofcellsusingExcel’srangenamefeature(采用Excel的區(qū)域名稱特征指向數(shù)據(jù)單元格或單元格方塊)RangenamesmakeformulasandtheSolvermodelmucheasiertoread(區(qū)域名稱使公式和Solver模型易讀性更強)Caremustbetakennottooveruserangenamesandtomakesuretheyremaincorrectlydefined.(必須確保不濫用區(qū)域名稱，而且區(qū)域名稱一定要定義準確)UseRelativeandAbsoluteReferencestoSimplifyCopyingFormulas(使用相對和絕對坐標簡化公式的復制)Whenevermultiplerelatedformulaswillbeneeded,trytoentertheformulajustonce,andthenuseExcel’sfillcommandstoreplicatetheformula.(當需要重復公式時，試著只輸入一次公式，然后利用Excel的填充命令復制公式)Thismakesthemodeleasertobuildandalsoreducestypos.(這不僅能使建模更加簡便，同時也可以減少出錯的可能)GuidelinesforBuilding“Good”SpreadsheetModelsUseBorders,Shading,andColorstoDistinguishCellTypes(使用邊框、陰影和顏色來區(qū)分單元格類型)Forexample:(例如)Datacellsinblue(noborder)(數(shù)據(jù)單元格采用藍色無邊框的格式)Changingcellsinyellow(regularborder)(可變單元格采用黃色正常邊框的格式)Targetcellinorange(heavyborder)(目標單元格采用橙色粗邊框的格式)GuidelinesforBuilding“Good”SpreadsheetModelsShowEntireModelonSpreadsheet(在電子表格中顯示整個模型)Alldatashouldbevisible.(所有數(shù)據(jù)應該都可視化)Allconstraintsshouldbeonthespreadsheet(notburiedinSolver),preferablyinthreeconsecutivecells.(所有的約束都應該在電子表格中，而不是隱藏在Solver中，并且最好是在三個相鄰的單元格中)GuidelinesforBuilding“Good”SpreadsheetModels電子表格建模有這么多好原則！ThreeTestsfora“Good”SpreadsheetModelYoushouldbeabletoimmediatelyidentifythedatacells,changingcells,andtargetcell.(你應該能夠立即識別出數(shù)據(jù)單元格、可變單元格和目標單元格)Allelementsofthemodelshouldbevisibleonthespreadsheet(includingallconstraints).YoushouldnothavetolookintheSolverdialogueboxtofigureoutthemodel.(模型的所有元素，包括約束，都應該在電子表格中顯示出來；你不必查看Solver對話框就能確定整個模型)ThreeTestsfora“Good”SpreadsheetModelEachequationshouldbesimpleenoughthatyoucantellwhattheequationisineach