Chapter 3. The Art of Modeling with Spreadsheets 第三章. 电子表格建模的艺术 运筹学

Table of Contents (主要内容) The Everglade Golden Years Co. Cash Flow Problem (Section 3.1)(大沼泽地金色年代公司的现金流问题[第3.1节]) The Process of Modeling with Spreadsheets (Section 3.2)(使用电子表格建模的过程[第3.2节]) Guidelines for Building “Good” Spreadsheet Models (Section 3.3)(建立好的电子表格模型的原则[第3.3节]) Debugging a Spreadsheet Model (Section 3.4)(调试电子表格模型[第3.4节])

The Everglade Cash Flow Problem Year Projected Net Cash Flow (millions of dollars) 2003 –8 2004 –2 2005 –4 2006 3 2007 6 2008 2009 2010 7 2011 2012 10

The Everglade Cash Flow Problem Their short-term cash flow requirements cannot be met without borrowing. (短期的现金流难于满足需求，有必要贷款) A long-term (10 year) loan can be taken with an interest rate of 7%. (为期10年的长期贷款利率为7%) A series of short-term loans (1 year) can be taken with a projected interest rate of 10%. (为期1年的短期贷款利率为10%)

The Everglade Cash Flow Problem Which loan (or combination of loans) should be taken, and in what amounts, in order to maximize their cash-balance position after 10 years? 要使10年以后的现金余额最大，应该选择哪种贷款(或贷款组合)？贷款数目应多大？

The Process of Modeling Visualize where you want to finish (设想一下你的目标) Do some calculations by hand (手工进行一些计算) Sketch out a spreadsheet (建立一个电子表格) Start with a small-scale model (先建一个小模型) Expand the model to full scale (扩展为完整的模型) Try different trial solutions to check the logic (利用不同的测试数据分析模型的逻辑关系) Evaluate proposed solutions and/or optimize with Solver (评估建议的解和/或利用Solver优化) Plan (计划) Build (建模) Test (测试) Analyze (分析) 如果得出的解显示模型不完善则回到计划或建模步骤

Plan: Visualize Where You Want to Finish A common stumbling block in the modeling process occurs right at the beginning—How do I get started? (建模过程中的一个常见问题出现在最开始的时候——我应该如何开始工作？) At this point it can be useful to ask the question—Where do I want to end up? (此时询问“我想在哪里结束”这样的问题会很有帮助) What information do I need to provide in a report? (我应该在报告中提供什么信息？)

Plan: Visualize Where You Want to Finish What should the “answer” look like? (“答案”看起来应该像什么样子？) What kinds of numbers need to be included in the recommendation? (建议中应该包含哪些数字？) What information is needed in the Everglade Cash Flow problem? (大沼泽地金色年代公司的现金流问题中，需要什么样的信息？)

Plan: Do Some Calculations by Hand Another common stumbling block arises when trying to enter a formula in one of the output cells. (另一个常见问题出现在为一个输出单元格输入公式时) At this point, it can be useful to do some calculations by hand. (此时进行一些手工计算是很有用的) Just pick some numbers for the changing cells and determine what the results should be. (为可变单元格选出一些数字并计算出结果)

Plan: Do Some Calculations by Hand Suppose Everglade takes out a $6 million long-term loan and then adds short-term loans of $2 million in 2003 and $5 million in 2004. (假设大沼泽地公司借入了600万美元的长期贷款，并于2003年借入了200万美元的短期贷款，2004年借入了500万美元的短期贷款) How much cash will they have left at the end of 2003? (2003年底将留下多少现金？) How much cash will they have left at the end of 2004? (2004年底将留下多少现金？)

Plan: Sketch Out a Spreadsheet

Build: Start with a Small Version If the model is complicated, start with a small, readily manageable version of the model. (如果模型比较复杂，可以先建立一个较小的易于管理的模型开始) First make sure you’ve got the logic of the model worked out correctly for the small version. Only then expand it to full size. (首先要保证你从小规模的模型中获得了模型的逻辑推理方式，然后才扩展到完整的模型)

Build: Start with a Small Version

Test: Test the Small Version of the Model Test the small model thoroughly. (全面地测试这个小模型) Try values in the changing cells for which you know the answers (at least approximately). (在可变单元格中输入一些数值，然后看电子表格给出的结果是否与你所知道的输出单元格的数值相一致) Examples: (例如) All zeroes (都为0) All ones (都为100万美元)

Test: Test the Small Version of the Model Very large numbers (非常大的数值) Numbers for which you’ve done hand calculations (经过手工验算的数值) Expand the model to full size (copying formulas to the later years). (将模型扩展完整[把公式拷贝到后面的年份]) Test the large model in a similar way. (采用类似的方法测试大型的模型) 运筹学

Analyze: Analyze the Model (Before Solver)

Analyze: Analyze the Model (After Solver)

Guidelines for Building “Good” Spreadsheet Models Enter the Data First (先输入数据) Any spreadsheet model is driven by the data (所有电子表格模型都是通过数据驱动的) It is easier (and usually better) to build the model around the data (围绕数据构建模型更加简单适用) Organize and Clearly Identify the Data (组织和清楚地标识数据) Relevant data should be grouped (e.g. in tabular form) (相关的数据应该组合在一起，如采用表格的形式) All data should be labeled (所有的数据都应该有标题) Units should be identified (应当注明数据单位)

Guidelines for Building “Good” Spreadsheet Models Enter Each Piece of Data into One Cell Only (每个数据输入唯一的一个单元格) Refer to the original data as needed (如果需要都指向原始数据) This makes the model much easier to modify (only need to change data in one place) (这样做使模型便于修改，只需在一个地方更改数据) Separate Data from Formulas (将数据与公式分离) Avoid putting numbers directly in formulas (避免在公式中直接使用数字)

Guidelines for Building “Good” Spreadsheet Models Put numbers in data cells and refer to them as needed (将数字输入数据单元格，在需要时引用) This makes all data visible and easier to modify (这样做可以使所有数据可视并且便于修改) Keep It Simple (保持简单化) Avoid “power functions” of Excel if possible (避免在可以使用简单函数的情况下，采用Excel的功能强大的函数) Break out complicated formulas into subtotals (把复杂的公式分成几段再求和)

Guidelines for Building “Good” Spreadsheet Models Use Range Names (使用区域名称) Refer to data cells and blocks of cells using Excel’s range name feature (采用Excel的区域名称特征指向数据单元格或单元格方块) Range names make formulas and the Solver model much easier to read (区域名称使公式和Solver模型易读性更强) Care must be taken not to overuse range names and to make sure they remain correctly defined. (必须确保不滥用区域名称，而且区域名称一定要定义准确)

Guidelines for Building “Good” Spreadsheet Models Use Relative and Absolute References to Simplify Copying Formulas (使用相对和绝对坐标简化公式的复制) Whenever multiple related formulas will be needed, try to enter the formula just once, and then use Excel’s fill commands to replicate the formula. (当需要重复公式时，试着只输入一次公式，然后利用Excel的填充命令复制公式) This makes the model easer to build and also reduces typos. (这不仅能使建模更加简便，同时也可以减少出错的可能)

Guidelines for Building “Good” Spreadsheet Models Use Borders, Shading, and Colors to Distinguish Cell Types (使用边框、阴影和颜色来区分单元格类型) For example: (例如) Data cells in blue (no border) (数据单元格采用蓝色无边框的格式) Changing cells in yellow (regular border) (可变单元格采用黄色正常边框的格式) Target cell in orange (heavy border) (目标单元格采用橙色粗边框的格式)

Guidelines for Building “Good” Spreadsheet Models Show Entire Model on Spreadsheet (在电子表格中显示整个模型) All data should be visible. (所有数据应该都可视化) All constraints should be on the spreadsheet (not buried in Solver), preferably in three consecutive cells. (所有的约束都应该在电子表格中，而不是隐藏在Solver中，并且最好是在三个相邻的单元格中) 电子表格建模有 这么多好原则！

Three Tests for a “Good” Spreadsheet Model You should be able to immediately identify the data cells, changing cells, and target cell. (你应该能够立即识别出数据单元格、可变单元格和目标单元格) All elements of the model should be visible on the spreadsheet (including all constraints). You should not have to look in the Solver dialogue box to figure out the model. (模型的所有元素，包括约束，都应该在电子表格中显示出来；你不必查看Solver对话框就能确定整个模型)

Three Tests for a “Good” Spreadsheet Model Each equation should be simple enough that you can tell what the equation is in each output cell without looking. (每一个方程式都应该足够简单，使你不必仔细查看就能知道每一个输出单元格的公式是什么) 好的电子表格模 型的3种测试方法

A Poor Spreadsheet Model

Debugging a Spreadsheet Model: The Toggle Pressing control-~ toggles back-and-forth between showing values and showing formulas in the spreadsheet. (在电子表格中同时按“Ctrl”和“~”键，可以在数值和公式之间来回切换)

Debugging with the Auditing Toolbar The button third from the left can be used to trace the dependents of a cell, that is, those cells with formulas that refer to this cell. (从左往右的第三个按钮可以用来追踪从属单元格，即哪些含公式的单元格引用了该单元格) The leftmost button can be used to trace the precedents of a cell, that is, those cells that appear within the formula for this cell. (最左边的按钮可以用来追踪引用单元格，即该单元格的公式包含了哪些单元格的数据)

Dependents of the LT Loan Cell

Precedents of the ST Interest (2004) Cell

The end of chapter 3