《{电子公司企业管理}运筹学2类电子表格建模艺术》由会员分享，可在线阅读，更多相关《{电子公司企业管理}运筹学2类电子表格建模艺术（32页珍藏版）》请在金锄头文库上搜索。
1、Chapter 3. The Art of Modeling with Spreadsheets,第三章. 电子表格建模的艺术,运筹学,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)(建立好的电子表格
2、模型的原则第3.3节) Debugging a Spreadsheet Model (Section 3.4)(调试电子表格模型第3.4节),Table of Contents (主要内容),The Everglade Cash Flow Problem,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 a
3、n 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%),Which loan (or combination of loans) should be taken, and in what amounts, in order to maximize their cash-balance position after 10 years?,The Evergla
4、de Cash Flow Problem,要使10年以后的现金余额最大，应该选择哪种贷款(或贷款组合)？贷款数目应多大？,The Process of Modeling,A common stumbling block in the modeling process occurs right at the beginningHow do I get started? (建模过程中的一个常见问题出现在最开始的时候我应该如何开始工作？) At this point it can be useful to ask the questionWhere do I want to end up? (此时询
5、问“我想在哪里结束”这样的问题会很有帮助) 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 E
6、verglade Cash Flow problem? (大沼泽地金色年代公司的现金流问题中，需要什么样的信息？),Plan: Visualize Where You Want to Finish,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
7、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
8、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 mo
9、del is complicated, start with a small, readily manageable version of the model. (如果模型比较复杂，可以先建立一个较小的易于管理的模型开始) First make sure youve got the logic of the model worked out correctly for the small version. Only then expand it to full size. (首先要保证你从小规模的模型中获得了模型的逻辑推理方式，然后才扩展到完整的模型),Build: Start with a
10、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: Te
11、st the Small Version of the Model,Very large numbers (非常大的数值) Numbers for which youve 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 (B
12、efore 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 Cl
13、early 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 (每个数据输入唯一的一
14、个单元格) 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” Spreads
15、heet 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 subtot
16、als (把复杂的公式分成几段再求和),Guidelines for Building “Good” Spreadsheet Models,Use Range Names (使用区域名称) Refer to data cells and blocks of cells using Excels 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. (必须确保不滥用区域名称，而且区域名称一定要定义准确),Use Relative and Absolute References to Simplify Copying Formula
