第三章电子表格建模艺术讲述.pptVIP

第三章 电子表格的建模艺术 3.1 大沼泽地金色年代公司的现金流问题 3.1 大沼泽地金色年代公司的现金流问题 公司出现的问题：目前仅有100万美元的现金余额，大沼泽地公司需要获得一些贷款来满足其财务负担。 公司的要求：为了预防一些不确定因素，公司政策规定现金储备在任何时候必须保持最小50万美元的余额。 解决问题的途径：第一种选择是十年期限的长期贷款，每年支付利息，全部的本金在十年后一次性付清，贷款利率是 7%。 另一种选择是一系列的一年期贷款。这些贷款可以在每年需要时获得，但必须在下一年将本金付清。这些贷款的利率是 10% 管理者需要解决的问题：应该选择哪一种贷款方式，贷款多少，才能保持公司的偿还能力，并使2013年的现金余额在支付完贷款之后最大化？ 模型建立的过程 计划：设想一下你的目标 建模过程中的一个常见问题出现在开始的时候—”我应该怎样开始呢?” 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? 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? 计划：手工进行一些计算 另一个常见问题出现在为一个办出单元格输入公式时。 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. 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. How much cash will they have left at the end of 2003? How much cash will they have left at the end of 2004? 计划：草拟电子表格 建立：从小规模电子表格开始 如果模型比较复杂，可以从一个小的易于管理的模型开始 从小的模型中获得模型的合理逻辑关系，然后再将其扩充成一个大的、比较复杂、完善的模型 测试：测试小规模的模型 Test the small model thoroughly. Try values in the changing cells for which you know the answers (at least approximately). Examples: All zeroes All ones 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. 分析：分析模型(Before Solver) 分析:分析模型 (After Solver) 建立一个好的电子表格模型的原则 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 Enter Each Piece of Data into One Cell Only Refer to the original data as needed This makes the model much ea