第2讲-Excel.ppt

Excel的使用技巧与建模艺术 Excel是表格处理软件，它能够提供足够大的表格，Excel最大可以提供65536行和256列的工作表。(2003版 数据参数） Excel能提供强大的数据处理功能。 表格中的各种数据并不是简单的数据堆积，而是需要产生多种多样的关系，以便在原始数据的基础上获得新的所需要的信息，Excel可以实现加、减、乘、除等常规运算，还可以借助其中丰富的内建函数完成包括统计、财务、逻辑判断、时间运算等在内的高级数据处理工作，同时，它还可以把各种数据关系用形式多样的图形形象地展示出来。 ( 本讲内容包括 教材1“管理系统模拟 蔡建峰 主编” 中第二，四章。 在本讲教学中，我们将要从中抽取3个例题重点讲解。通过这3个例题的讲解，了解怎样利用Excel进行简单的模拟，以及Excel适用的模拟范围。 ） 例1.新产品开发问题（教材1，P28，例2-1） 背景：根据分析得知影响某新产品开发项目未来现金流量的主要不确定因素包括产品市场前景和原材料价格水平两方面。未来的市场前景可能有三种：畅销、销路一般、滞销，分别记作a、b、c；类似地，原材料价格水平也可能存在三种情况，即高价位、中价位、低价位，分别记作e、f、g。假定产品市场状况与原材料价格水平之间是相互独立的。不同产品市场状况和原材料价格水平发生的概率如表 该新产品未来可能面临的状态共有9种，各种状态组合及其相应的现金流量如表 该项目可能面对未来多种不同的情况，特定状况下其收益额如何？这可借助Excel中的财务函数来完成。结果如图 在此题中，用到了Excel填充技巧中的向下填充功能。我们只要把F3单元格的内容填为“=-PV(12%,5,E3,,)+D3”，然后选取F4到F11 这一列，选取“编辑-填充-向下填充”功能，即可完成F4到F11这几个单元格的公式填写，即“F4=-PV(12%,5,E4,,)+D4”，“F5= =-PV(12%,5,E5,,)+D5”，等等，以此类推。 （具体见文档 Excel技巧.doc ） Excel的自动填充功能非常实用，能帮助我们在制作表格时快速填写具有递推规律的表格内容。在本章后面的例题“例题3. 生产批量确定问题”中也会用到。大家一定要熟练掌握！ 数据表- 单变量数据表（教材1，P38，例2-2） 例2：　假定某人想通过按揭购买一辆15万元的汽车，首付5万元，其余部分实行按揭，年利率为5%，贷款部分要求在3年内每月底等额偿还并届时付清所有本金和利息。根据预测，未来3年内的贷款利率有可能从4%到6%之间变动。 输入基本数据并建立关系模型，即如何根据已知量计算出月还款额和应付利息总额； 在一行（或列）中建立一初始值为4%、终值为6%、间距为0.25%（也可以选择其它数据的间距）的等差序列来表示变化的贷款利率； 在上述等差序列上方右移一个单元格始分别建立与月还款额和应付利息总额所在单元格之间的链接； 选择一个能将上述等差序列和两个链接单元格包含在内的区域，然后在菜单“数据”中选择“模拟运算表”，并在“模拟运算表”对话框中将“输入引用列的单元格”设为“年利率”后单击“确定”。 每月偿还额计算公式 B4*B5/12*POWER(1+B5/12,B6)/(POWER(1+B5/12,B6)-1) 偿付利息总额计算公式 B8*B6-B4 数据表- 双变量数据表 单变量数据表可以就单个变量对一个或多个变量的影响作出分析，而双变量数据表只能分析两个变量对一个变量的影响，两者在分析过程上基本相同，不同之处是未来数据表的首行和首列分析对应两个自变量。 在上例中，若根据银行的贷款政策，还款期可在2至4年内、首付可在4万至6万间作出选择，为了搞清利息总额与还款期限和首次付款额的关系，可建立如下图所示的双变量数据表： 思考题： 如果在例题2中，需要考虑的变动因素由2个增加要3个，4个，甚至更多，怎么处理？Excel还适用吗？还有其他软件可用于此种情况的模拟吗？（在此情况下，可以借助Excel的方案功能。具体见教材1，P86） 你能想到那些解决方法？ 例3. 生产批量确定问题 （教材1，P90，例4-1） 确定型动态问题模拟 假定一公司生产某种产品的速度为100件/天，生产每批产品的设置成本为100元，每件产品每天的存储成本为0.1元，该产品每天的需求量为25件。若最大生产批量为400件，年初该产品的初始库存量为90件，试用模拟法分析： （1）100天内累积生产量与累积需求量的变动情况； （2）100天内库存量的变动情况； （3）生产批量为多少时可使年总成本最低（一年按360天计算） 利用Excel的自动填充等功能，做出如下的表格。 根据模型分析提供的100天之内累