中国人民大学信息学院：《运筹学》课程教学资源（PPT课件讲稿）第三章 电子表格建模的艺术

Chapter 3 The Art of Modeling with Spreadsheets 电子表格建模的艺术 Data Model and decisions 数据、模型与决策 第三章 The art of Modeling with Spreadsheets 电子表格建模的艺术 RuC Information School, Ye Xiang 2007

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 本章内容( Topics 电子表格建模的艺术 The Everglade golden Years Co Cash Flow Problem 31案例研究:大沼泽地金色年代公司的现金流问题 (动态规划问题) The Process of Modeling with Spreadsheets 32电子表格建模程序的概述 Guidelines for building"Good"Spreadsheet models 33建立一个好的电子表格模型的几个原则 Debugging a spreadsheet mode 34调试电子表格模型 RuC Information School, Ye Xiang 2007

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 电子表格建模的艺术 Case Study: The Everglade golden Years Co Cash Flow Problem 3.1案例研究:大沼泽地金色年代公司的现金流问题 有两种不同的贷款 10年长期贷款,利率7%,只能在2003年 年份计划的净现金流 初贷1次,每年还息(10次),第10年后 (百万美元) 还本 2003 2004 1年短期贷款,利率10%,可以在2003 2012年初贷可贷10次,下一年还本付息 2005 2006 问题:如何贷款(贷款组合),使得公 2007 司在10年内可以正常运转(目前只有100 2008 万美元,每年的现金储备最少50万美元 2009 ,公司未来10年的净现金流预测如右表 2010 ),并希望在2013年的期末现金余额最 2011 多(目标)。 2012 10 RuC Information School, Ye Xiang 2007

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 Case stud: The everglade golden years co. Cash flowi而南格建模的艺术 3.1案例研究:大沼泽地金色年代公司的现金流问题 (电子表格的位置安排与实现) 年一行,包括现金流、贷款(长期、短期)、还款(长期、短期的 利息和本金)、期末余额 公式:期末余额=期初余额(上年的期末余额)十现金流+贷款(长期、短 期)一还款(长期、短期的利息和本金)(动态规划的思想) 或(P69):期末余额=期初余额十现金流+贷款一利息支出一贷款 偿还>=最小现金余额(50万$) 位置安排:电子表格的顶部(左上角)是数据单元格(利率、期初余 额、最小现金余额),中间是约束(每一行代表不同的年份),右下 角是目标单元格(数据一>约束一>目标) ■我(叶向)认为可以改进的地方:增加一列期初余额(=上年的期末 余额),这样期末余额的公式更为简单(只需用SUM函数即可),且 容易扩展,如P87的习题3.1,考虑将期末余额(多余的现金)存入银 行,以获取利息3% RuC Information School, Ye Xiang 2007

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 电子表格建模的艺术 The Process of modeling with SpxxE adsheets 32电子表格建模程序的 计划一>建模一>测试一>分析 ■计划:设想一下你的目标、手工进行一些计算、草拟电子表格 建模:从小规模的电子表格开始 ■测试:测试小规模的规模 建模:将规模扩展完整 测试:测试完整规模 分析:分析模型 RuC Information School, Ye Xiang 2007

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 电子表格建模的艺术 Guidelines for building" Good" Spreadsheet models 33建立一个好的电子表格模型的几个原则 首先输入数据 组织和清楚地标识数据 每个数据输入惟一的一个单元格 将数据与公式分离 保持简单化( SUMPRODUCT,SUM,中间结果) 使用区域名称 使用相对和绝对地址简化公式的复制 使用边框、阴影和颜色来区分单元格类型 在电子表格中显示整个模型(包括符号和数据) RuC Information School, Ye Xiang 2007

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 电子表格建模的艺术 Debugging a Spreadsheet model 34调试电子表格模型 保持区域名称与单元格对应,可用“插 入一>名称一>粘贴一>粘贴清单” 数据与公式的切换( toggle):ctrl+~ 或“工具一>选项一>视图一>公式” 审核:审核工具栏 RuC Information School, Ye Xiang 2007

Chapter 3 The Art of Modeling with Spreadsheets 电子表格建模的艺术 RUC Information School ,Ye Xiang ,2007 Debugging a Spreadsheet Model 3.4 调试电子表格模型 ▪ 保持区域名称与单元格对应，可用“插 入－>名称－>粘贴－>粘贴清单” ▪ 数据与公式的切换（toggle）：ctrl＋～ ，或“工具－>选项－>视图－>公式” ▪ 审核：审核工具栏

Exercise Chapter 3 作业(上机) e Art of Modeling with Spre eadsheets 电子表格建模的艺术 l、P87习题3.1,3.3,3.4 在Exce中建立完整规模,并求解 2、案例3.1养老金的谨慎供应 提示:决策变量:20031.1四种债券的投资数量单位和银行存款额 自标:2003.1.1的最小投资额=4种债券的投资额十银行存款额+ 2003年的养老金支付8百万 约束:每年(2004-2012)的资金余额>=0 注意:每年的资金余额(多余的资金)存入资本市场基金,以获取 5%的利息收入 求解结果: 债券1为44.27千份,债券2为0.00千份,债券3为51.36千份, 债券4为43.55千份,银行存款额为0百万$ MinC=124.74百万$ RuC Information School, Ye Xiang 2007

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百万＄