运筹学2类电子表格建模艺术

Chapter3.TheArtofModelingwithSpreadsheets第三章.电子表格建模的艺术运筹学Copyright2007©深圳大学管理学院运筹学2TheEvergladeGoldenYearsCo.CashFlowProblem(Section3.1)(大沼泽地金色年代公司的现金流问题[第3.1节])TheProcessofModelingwithSpreadsheets(Section3.2)(使用电子表格建模的过程[第3.2节])GuidelinesforBuilding“Good”SpreadsheetModels(Section3.3)(建立好的电子表格模型的原则[第3.3节])DebuggingaSpreadsheetModel(Section3.4)(调试电子表格模型[第3.4节])TableofContents(主要内容)Copyright2007©深圳大学管理学院运筹学3TheEvergladeCashFlowProblemYearProjectedNetCashFlow(millionsofdollars)2003–82004–22005–42006320076200832009–4201072011–2201210Copyright2007©深圳大学管理学院运筹学4TheEvergladeCashFlowProblemTheirshort-termcashflowrequirementscannotbemetwithoutborrowing.(短期的现金流难于满足需求，有必要贷款)Along-term(10year)loancanbetakenwithaninterestrateof7%.(为期10年的长期贷款利率为7%)Aseriesofshort-termloans(1year)canbetakenwithaprojectedinterestrateof10%.(为期1年的短期贷款利率为10%)Copyright2007©深圳大学管理学院运筹学5Whichloan(orcombinationofloans)shouldbetaken,andinwhatamounts,inordertomaximizetheircash-balancepositionafter10years?TheEvergladeCashFlowProblem要使10年以后的现金余额最大，应该选择哪种贷款(或贷款组合)？贷款数目应多大？Copyright2007©深圳大学管理学院运筹学6TheProcessofModeling•Visualizewhereyouwanttofinish(设想一下你的目标)•Dosomecalculationsbyhand(手工进行一些计算)•Sketchoutaspreadsheet(建立一个电子表格)Startwithasmall-scalemodel(先建一个小模型)Expandthemodeltofullscale(扩展为完整的模型)Trydifferenttrialsolutionstocheckthelogic(利用不同的测试数据分析模型的逻辑关系)Evaluateproposedsolutionsand/oroptimizewithSolver(评估建议的解和/或利用Solver优化)Plan(计划)Build(建模)Test(测试)Analyze(分析)如果得出的解显示模型不完善则回到计划或建模步骤Copyright2007©深圳大学管理学院运筹学7Acommonstumblingblockinthemodelingprocessoccursrightatthebeginning—HowdoIgetstarted?(建模过程中的一个常见问题出现在最开始的时候——我应该如何开始工作？)Atthispointitcanbeusefultoaskthequestion—WheredoIwanttoendup?(此时询问“我想在哪里结束”这样的问题会很有帮助)WhatinformationdoIneedtoprovideinareport?(我应该在报告中提供什么信息？)Plan:VisualizeWhereYouWanttoFinishCopyright2007©深圳大学管理学院运筹学8Whatshouldthe“answer”looklike?(“答案”看起来应该像什么样子？)Whatkindsofnumbersneedtobeincludedintherecommendation?(建议中应该包含哪些数字？)WhatinformationisneededintheEvergladeCashFlowproblem?(大沼泽地金色年代公司的现金流问题中，需要什么样的信息？)Plan:VisualizeWhereYouWanttoFinishCopyright2007©深圳大学管理学院运筹学9Plan:DoSomeCalculationsbyHandAnothercommonstumblingblockariseswhentryingtoenteraformulainoneoftheoutputcells.(另一个常见问题出现在为一个输出单元格输入公式时)Atthispoint,itcanbeusefultodosomecalculationsbyhand.(此时进行一些手工计算是很有用的)Justpicksomenumbersforthechangingcellsanddeterminewhattheresultsshouldbe.(为可变单元格选出一些数字并计算出结果)Copyright2007©深圳大学管理学院运筹学10Plan:DoSomeCalculationsbyHandSupposeEvergladetakesouta$6millionlong-termloanandthenaddsshort-termloansof$2millionin2003and$5millionin2004.(假设大沼泽地公司借入了600万美元的长期贷款，并于2003年借入了200万美元的短期贷款，2004年借入了500万美元的短期贷款)Howmuchcashwilltheyhaveleftattheendof2003?(2003年底将留下多少现金？)Howmuchcashwilltheyhaveleftattheendof2004?(2004年底将留下多少现金？)Copyright2007©深圳大学管理学院运筹学11Plan:SketchOutaSpreadsheetLTRateSTRateStartBalanceMinimumCashCashLTSTLTSTLTSTEndingMinimumFlowLoanLoanInterestInterestPaybackPaybackBalanceBalance20032004:=:20122013Copyright2007©深圳大学管理学院运筹学12Build:StartwithaSmallVersionIfthemodeliscomplicated,startwithasmall,readilymanageableversionofthemodel.(如果模型比较复杂，可以先建立一个较小的易于管理的模型开始)Firstmakesureyou’vegotthelogicofthemodelworkedoutcorrectlyforthesmallversion.Onlythenexpandittofullsize.(首先要保证你从小规模的模型中获得了模型的逻辑推理方式，然后才扩展到完整的模型)Copyright2007©深圳大学管理学院运筹学133456789101112BCDEFGHIJKLLTRate7%STRate10%StartBalance1(allcashfiguresinmillionsofdollars)MinimumCash0.5CashLTSTLTSTLTSTEndingMinimumYearFlowLoanLoanInterestInterestPaybackPaybackBalanceBalance2003-8621.00=0.502004-25-0.42-0.20-2.001.38=0.509101112FGHIJKLLTSTLTSTEndingMinimumInterestInterestPaybackPaybackBalanceBalance=StartBalance+SUM(C11:I11)==MinimumCash=-LTRate*LTLoan=-STRate*E11=-E11=J11+SUM(C12:I12)==MinimumCashBuild:StartwithaSmallVersionCopyright2007©深圳大学管理学院运筹学14Test:TesttheSmallVersionoftheModelTestthesmallmodelthoroughly.(全面地测试这个小模型)Tryvaluesinthechangingcellsforwhichyouknowtheanswers(atleastapproximately).(在可变单元格中输入一些数值，然后看电子表格给出的结果是否与你所知道的输出单元格的数值相一致)Examples:(例如)Allzeroes(都为0)Allones(都为100万美元)Copyright2007©深圳大学管理学院运筹学15Test:TesttheSmallVersionoftheModelVerylargenumbers(非常大的数值)Numbersforwhichyou’vedonehandcalculations(经过手工验算的数值)Expandthemodeltofullsize(copyingformulastothelateryears).(将模型扩展完整[把公式拷贝到后面的年份])Testthelargemodelinasimilarway.(采用类似的方法测试大型的模型)运筹学Copyright2007©深圳大学管理学院运筹学16Analyze:AnalyzetheModel(BeforeSolver)3456789101112131415161718192021BCDEFGHIJKLLTRate7%STRate10%StartBalance1(allcashfiguresinmillionsofdollars)MinimumCash0.5CashLTSTLTSTLTSTEndingMinimumYearFlowLoanLoanInterestInterestPaybackPaybackBalanceBalance2003-8621.00=0.502004-25-0.42-0.20-21.38=0.502005-40-0.42-0.50-5-8.54=0.50200630-0.4200-5.96=0.50200760-0.4200-0.38=0.50200830-0.42002.20=0.502009-40-0.4200-2.22=0.50201070-0.42004.36=0.502011-20-0.42001.94=0.502012100-0.420011.52=0.502013-0.420-605.10=0.50Copyright2007©深圳大学管理学院运筹学17Analyze:AnalyzetheModel(AfterSolver)3456789101112131415161718192021BCDEFGHIJKLLTRate7%STRate10%StartBalance1(allcashfiguresinmillionsofdollars)MinimumCash0.5CashLTSTLTSTLTSTEndingMinimumYearFlowLoanLoanInteres