Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Data,Model and Decisions
数据、模型与决策第三章
The Art of Modeling with
Spreadsheets
电子表格建模的艺术
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
本章内容（ Topics）
The Everglade Golden Years Co,Cash Flow Problem
3.1 案例研究,大沼泽地金色年代公司的现金流问题
（ 动态规划问题 ）
The Process of Modeling with Spreadsheets
3.2 电子表格建模程序的概述
Guidelines for Building,Good” Spreadsheet Models
3.3 建立一个好的电子表格模型的几个原则
Debugging a Spreadsheet Model
3.4 调试电子表格模型
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Case Study:The Everglade Golden Years Co,Cash Flow Problem
3.1 案例研究,大沼泽地金色年代公司的现金流问题
有两种不同的贷款：
10年长期贷款，利率 7%，只能在 2003年初贷 1次，每年还息（ 10次），第 10年后还本
1年短期贷款，利率 10%，可以在 2003－
2012年初贷,可贷 10次，下一年还本付息
问题：如何贷款（贷款组合），使得公司在 10年内可以正常运转（目前只有 100
万美元，每年的现金储备最少 50万美元
，公司未来 10年的净现金流预测如右表
），并希望在 2013年的期末现金余额最多（目标）。
年份 计划的净现金流
（百万美元）
2003 –8
2004 –2
2005 –4
2006 3
2007 6
2008 3
2009 –4
2010 7
2011 –2
2012 10
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Case Study:The Everglade Golden Years Co,Cash Flow Problem
3.1 案例研究,大沼泽地金色年代公司的现金流问题
（电子表格的位置安排与实现）
一年一行，包括现金流、贷款（长期、短期）、还款（长期、短期的利息和本金）、期末余额
公式,期末余额＝期初余额（上年的期末余额）＋现金流＋贷款（长期、短期）－还款（长期、短期的利息和本金） （ 动态规划的思想）
或（ P69）,期末余额＝期初余额＋现金流＋贷款－利息支出－贷款偿还 >= 最小现金余额 (50万 $)
位置安排,电子表格的顶部（左上角）是数据单元格（利率、期初余额、最小现金余额），中间是约束（每一行代表不同的年份），右下角是目标单元格（数据－ >约束－ >目标）
我（叶向）认为可以改进的地方：增加一列 期初余额（＝上年的期末余额）,这样 期末余额 的公式更为简单（只需用 SUM函数即可），且容易扩展，如 P87的习题 3.1，考虑将 期末余额（多余的现金）存入银行，以获取利息 3％
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
The Process of Modeling with Spreadsheets
3.2 电子表格建模程序的概述
计划－ > 建模－ > 测试－ > 分析
计划：设想一下你的目标、手工进行一些计算、草拟电子表格
建模：从小规模的电子表格开始
测试：测试小规模的规模
建模：将规模扩展完整
测试：测试完整规模
分析：分析模型
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Guidelines for Building,Good” Spreadsheet Models
3.3 建立一个好的电子表格模型的几个原则
首先输入数据
组织和清楚地标识数据
每个数据输入惟一的一个单元格
将数据与公式分离
保持简单化（ SUMPRODUCT,SUM,中间结果）
使用区域名称
使用相对和绝对地址简化公式的复制
使用边框、阴影和颜色来区分单元格类型
在电子表格中显示整个模型（包括符号和数据）
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Debugging a Spreadsheet Model
3.4 调试电子表格模型
保持区域名称与单元格对应，可用“插入－ >名称－ >粘贴－ >粘贴清单”
数据与公式的切换（ toggle）,ctrl＋～
，或“工具－ >选项－ >视图－ >公式”
审核：审核工具栏
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Exercise
作业（上机）
1,P87 习题 3.1,3.3,3,4
在 Excel中建立完整规模，并求解
2,案例 3.1 养老金的谨慎供应提示：决策变量,2003.1.1四种债券的投资数量单位和银行存款额目标,2003.1.1的最小投资额＝ 4种债券的投资额＋银行存款额＋
2003年的养老金支付 8百万约束：每年（ 2004－ 2012）的资金余额 >=0
注意：每年的资金余额（多余的资金）存入资本市场基金，以获取
5％的利息收入求解结果：
债券 1为 44.27千份，债券 2为 0.00千份，债券 3为 51.36千份，
债券 4为 43.55千份，银行存款额为 0百万＄
Min C = 124.74百万＄
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Data,Model and Decisions
数据、模型与决策第三章
The Art of Modeling with
Spreadsheets
电子表格建模的艺术
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
本章内容（ Topics）
The Everglade Golden Years Co,Cash Flow Problem
3.1 案例研究,大沼泽地金色年代公司的现金流问题
（ 动态规划问题 ）
The Process of Modeling with Spreadsheets
3.2 电子表格建模程序的概述
Guidelines for Building,Good” Spreadsheet Models
3.3 建立一个好的电子表格模型的几个原则
Debugging a Spreadsheet Model
3.4 调试电子表格模型
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Case Study:The Everglade Golden Years Co,Cash Flow Problem
3.1 案例研究,大沼泽地金色年代公司的现金流问题
有两种不同的贷款：
10年长期贷款，利率 7%，只能在 2003年初贷 1次，每年还息（ 10次），第 10年后还本
1年短期贷款，利率 10%，可以在 2003－
2012年初贷,可贷 10次，下一年还本付息
问题：如何贷款（贷款组合），使得公司在 10年内可以正常运转（目前只有 100
万美元，每年的现金储备最少 50万美元
，公司未来 10年的净现金流预测如右表
），并希望在 2013年的期末现金余额最多（目标）。
年份 计划的净现金流
（百万美元）
2003 –8
2004 –2
2005 –4
2006 3
2007 6
2008 3
2009 –4
2010 7
2011 –2
2012 10
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Case Study:The Everglade Golden Years Co,Cash Flow Problem
3.1 案例研究,大沼泽地金色年代公司的现金流问题
（电子表格的位置安排与实现）
一年一行，包括现金流、贷款（长期、短期）、还款（长期、短期的利息和本金）、期末余额
公式,期末余额＝期初余额（上年的期末余额）＋现金流＋贷款（长期、短期）－还款（长期、短期的利息和本金） （ 动态规划的思想）
或（ P69）,期末余额＝期初余额＋现金流＋贷款－利息支出－贷款偿还 >= 最小现金余额 (50万 $)
位置安排,电子表格的顶部（左上角）是数据单元格（利率、期初余额、最小现金余额），中间是约束（每一行代表不同的年份），右下角是目标单元格（数据－ >约束－ >目标）
我（叶向）认为可以改进的地方：增加一列 期初余额（＝上年的期末余额）,这样 期末余额 的公式更为简单（只需用 SUM函数即可），且容易扩展，如 P87的习题 3.1，考虑将 期末余额（多余的现金）存入银行，以获取利息 3％
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
The Process of Modeling with Spreadsheets
3.2 电子表格建模程序的概述
计划－ > 建模－ > 测试－ > 分析
计划：设想一下你的目标、手工进行一些计算、草拟电子表格
建模：从小规模的电子表格开始
测试：测试小规模的规模
建模：将规模扩展完整
测试：测试完整规模
分析：分析模型
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Guidelines for Building,Good” Spreadsheet Models
3.3 建立一个好的电子表格模型的几个原则
首先输入数据
组织和清楚地标识数据
每个数据输入惟一的一个单元格
将数据与公式分离
保持简单化（ SUMPRODUCT,SUM,中间结果）
使用区域名称
使用相对和绝对地址简化公式的复制
使用边框、阴影和颜色来区分单元格类型
在电子表格中显示整个模型（包括符号和数据）
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Debugging a Spreadsheet Model
3.4 调试电子表格模型
保持区域名称与单元格对应，可用“插入－ >名称－ >粘贴－ >粘贴清单”
数据与公式的切换（ toggle）,ctrl＋～
，或“工具－ >选项－ >视图－ >公式”
审核：审核工具栏
Chapter 3
The Art of Modeling with Spreadsheets
电子表格建模的艺术
RUC Information School,Ye Xiang,2007
Exercise
作业（上机）
1,P87 习题 3.1,3.3,3,4
在 Excel中建立完整规模，并求解
2,案例 3.1 养老金的谨慎供应提示：决策变量,2003.1.1四种债券的投资数量单位和银行存款额目标,2003.1.1的最小投资额＝ 4种债券的投资额＋银行存款额＋
2003年的养老金支付 8百万约束：每年（ 2004－ 2012）的资金余额 >=0
注意：每年的资金余额（多余的资金）存入资本市场基金，以获取
5％的利息收入求解结果：
债券 1为 44.27千份，债券 2为 0.00千份，债券 3为 51.36千份，
债券 4为 43.55千份，银行存款额为 0百万＄
Min C = 124.74百万＄
