《EXCEL预测与决策分析》

实验报告册

   20##- 20## 学年   第      学期

   班    级：                   

   学    号：                   

   姓    名：                   

   授课教师：        实验教师：         

   实验学时：        实验组号：         

信息管理系

目录

实验一  网上书店数据库的创建及其查询..................................................................... 3

实验二  贸易公司销售数据的分类汇总分析................................................................. 5

实验三  餐饮公司经营数据时间序列预测..................................................................... 7

实验四  住房建筑许可证数量的回归分析..................................................................... 9

实验五  电信公司宽带上网资费与电缆订货决策........................................................ 11

实验六  奶制品厂生产/销售的最优化决策.................................................................. 13

实验七  运动鞋公司经营投资决策.............................................................................. 14

实验一  网上书店数据库的创建及其查询

【实验环境】

?Microsoft Office Access 2003；

?Microsoft Office Query 2003。

【实验目的】

1.实验1-1:

?理解数据库的概念；

?理解关系（二维表）的概念以及关系数据库中数据的组织方式；

?了解数据库创建方法。

2.实验1-2：

?理解DOBC的概念；

?掌握利用Microsoft Query进行数据查询的方法。

3.实验1-3：

?掌握复杂的数据查询方法：多表查询、计算字段和汇总查询。

【实验步骤】

实验 1-1

一、表的创建和联系的建立

步骤1：创建空数据库“xddbookstore”。

步骤2：数据库中表结构的定义。

步骤3：保存数据表。

步骤4：定义“响当当”数据库的其他表。

步骤5：“响当当”数据库中表之间联系的建立。

二、付款方式表的数据输入

步骤1：选中需要输入数据的表（如付款方式表）。

步骤2：输入数据。

三、 订单表的数据导入

在本书配套磁盘提供的xddbookstore.xls文件中，包含了响当当数据库所有表的数据。可以利用该文件将订单表数据导入到“xddbookstore.mdb”数据库中。

步骤1：选择要导入的文件。

步骤2：规定要导入的数据表。

步骤3：指明在要导入的数据中是否包含列标题。

步骤4：规定数据应导入到哪个表中，可以是新表或现有的表。

步骤5：完成数据导入工作。

实验1-2

一、建立odbc数据源

在利用 microsoft office query对“响当当”网上书店进行数据查询之前，必须先建立一个用于连接该数据库的odbc数据源“bookstore”，具体步骤如下：

步骤1：启动microsoft office query应用程序。

步骤2：进入“创建新数据源”对话框。

步骤3：输入数据源名字。 经济管理中的计算机应用实验指导/报告 ——Excel 数据分析、

在“创建新数据源”对话框的“请输入数据源名称”项中输入要定义的数据源的名称（“bookstore”）。

步骤4：选择数据库驱动程序。

二、查询设计1—低库存量图书信息查询

对低库存量信息的查询，如查询库存量小于10的图书的isbn、书名和库存量，需要在查询时设定对库存量的查询要求，如“库存量<10”。查询步骤如下：

步骤1：选择“bookstore”数据源，进入“查询设计”窗口。

步骤2：选择查询中需要使用的表。

步骤3：选择要查询的字段。

步骤4：添加“条件”窗格，设置查询条件。

实验1-3

一、查询设计1—会员分布信息查询

响当当网上书店的营销人员需要了解如下的信息：各个地区会员的分布情况，例如北京和上海的会员人数。具体查询步骤如下：

步骤1：选择“bookstore”数据源并添加“会员”表。

步骤2：选择分类字段、汇总字段和汇总方式。

步骤3：设置查询条件。

若仅仅想了解上海和北京的会员人数，可以在条件窗格中设置相应的条件，具体步骤略。

二、查询设计2—图书总订购量和总销售金额查询

书店工作人员想了解会员在网上订购图书的信息，如20##年下半年和20##年上半年图书的总订购量和总销售金额。具体查询步骤如下：

步骤1：选择“bookstore”数据源，并添加“订单”、“订单明细”和“书”表。

步骤2：选择分类字段和汇总字段。

步骤3：设置查询条件。

三、查询设计3—会员订购图书详细信息查询

书店工作人员想了解各个会员的会员号、姓名、所在的城市、相应订单的订单号、订单上所订购的图书的书名和订购数量等信息，并要求按会员的城市、会员号和订单号的升序排列。具体查询步骤如下：

步骤1：选择“bookstore”数据源并添加“会员”、“订单”、“订单明细”和“书”表。在表之间建立合适的联系。

步骤2：选择字段。

在“查询设计”窗口的“表”窗格中，双击“会员”表的“城市”、“会员号”、“姓名”字段，“订单”表的“订单号”字段、“书”表的“书名”字段和“订单明细”表的“订购数量”字段。

步骤3：规定查询结果的排序方式。

选择“记录”菜单的“排序”命令，在随后出现的“排序”对话框中设置排序方式。

步骤4：观察查询结果。

查询结果共84条记录。如果查询结果中包含的记录有1680条的话，请检查一下“会员”与“订单”表之间的联系是否已建立。

四、查询设计4—各城市会员图书订购数量和销售金额统计

书店工作人员想了解位于各个城市的会员在网上订购的图书的总订购数量和总销售金额。查询步骤如下：

步骤1：选择“bookstore”数据源并添加“会员”、“订单”、“订单明细”和“书”表。在表之间建立合适的联系。

步骤2：选择分类字段和汇总字段。

在“查询设计”窗口的“表”窗格中，双击“会员”表的“城市”、“订单明细”表的“订购数量”字段。另外还要构造一个计算字段“销售金额”，方法是直接在某空白列的列标中输入公式“订购数量*单价”；

步骤3：规定汇总方式。

分别双击“订购数量”和“订购数量*单价”字段的列标，在编辑列对话框的列标项中分别输入“总订购数量”和“总销售金额”字样，并在总计项中选择“求和”。按“确定”按钮后即可查看结果。

五、查询设计5—被订购图书的作者和出版社信息查询

书店工作人员想了解20##年会员“刘丹”所订购图书的作者以及出版社的信息。查询步骤如下：

步骤1：选择“bookstore”数据源并添加“会员”、“订单”、“订单明细”、“书”、“作者”和“出版社”表。在表之间建立合适的联系。

步骤2：选择查询字段。

在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、“作者”表的“姓名”和“出版社”表的“出版社名称”字段。

步骤3：添加查询条件。

在条件窗格中添加关于订购日期和会员姓名的条件。

步骤4：查看查询结果。

会员“刘丹”在20##年共订购了7本图书。

【实验思考】

1·什么是表的主键？在同一个表中，记录的主键值是否可以相等？

答：主键是能够唯一地标识该表中的某一行记录的一个或多个字段的集合。记录的主键值不可以相等，因为记录的主键具有唯一性。

2·在数据库中各表之间的联系建立以后，将外部数据导入到表的过程中可能遇到什么问题？

答：外部数据字段与表中的字段没有对应；外部数据字段值得类型与定义的数据库字段值类型不一致；外部数据的字段对应于数据库中的主键时，有可能对应于主键值不唯一；外部数据不符合参照完整性。

3·在数据库查询过程中，如果所选择的某个表与其他表之间没有联系的话，会产生什么问题？

答：查询的结果只能是其中一个表的记录，而不能查询到关联表的数据和记录。

4·若“响当当”网上书店的某个会员想了解自己最近2年图书订购情况，请你为他设计一个查询。

答：步骤1  选择“bookstore”数据源并添加“会员表”、“订单表”、“订单明细表”和“书表”，在表之间建立合适的联系。

    步骤2  在“查询设计”窗口的“表”窗口中，双击“书”表的“书名”、“单价”，“会员表”的“会员号”、“姓名”，“订单表”中的“订购日期”，“订单明细表”中的“订购数量”字段。

    步骤3  在条件窗口第1列选择“会员号”，在“值”行输入“会员号”，在条件窗口第2列选择“订购日期”，在“值”行输入“<=现在日期 and  >=两年前日期”，回车。

5·在进行汇总查询的过程中，如果被选择的字段除了分类字段和汇总字段以外还包括了其他字段，查询结果是否正确，为什么？

答：查询结果会出错。因为查询过程中，无法分辨出那个是分类字段，就会按照能够唯一确定记录的字段来进行分类。

6·“响当当”网上书店的管理人员想了解最近两年中哪位作者的书最畅销？请设计一个查询找到相关作者。

答：步骤1  选择“bookstore”数据源并添加“订单表”、“订单明细表”、“书表”和“作者表”，在各表之间建立适当的联系。

步骤2  在查询设计窗口的“表”窗格中，双击“订单表”中的“订购日期”和“作者”表中的“姓名”，“订单明细表”的“订购数量”。

   步骤3  双击“订购数量”，在“编辑列”对话框的“列标”项中输入“总订购数量”，并在总计项中选择“求和”。单击确定按钮即可查看查询结果。

【实验总结】：

    

 

实验二  贸易公司销售数据的分类汇总分析

【实验环境】

?Microsoft Office Access 2003;

?Microsoft Office Query 2003；

?Microsoft Office Excel 2003。

【实验目的】

1.实验2-1:

?理解数据分类汇总在企业中的作用与意义；

?掌握数据透视表工具的基本分类汇总功能；

?掌握建立分类汇总数据排行榜，生成时间序列，绘制Pareto曲线图，计算各地区客户分布，统计各地区客户的平均销售额和大宗销售时间序列的方法与步骤。

2.实验2-2：

?理解数据分类汇总在企业中的应用；

?掌握利用DSUM函数汇总数据的基本方法；

?掌握利用控件选择汇总参考字段的方法；

?掌握按照指定时间汇总数据，制作前十大客户销售额观测板、主要销售人员销售业绩观测板、主要城市销售业绩观测板和主要产品销售业绩观测板等方法。

【实验步骤】

实验2-1

“北风贸易”公司客户经理希望能获得有关客户特征的分类汇总数据，以便根据客户的特点，预测未来的销售情况、制订有关销售的策略。

客户经理希望能够利用数据透视表完成以下的汇总工作：

1. 按照销售额汇总客户各年度销售排行榜，按照从大到小的方式排列，并且列出各客户在各类别上的销售额。

2. 针对前面所获得销售排行榜，挑选对公司最重要的前三大客户，汇总他们各年月的销售额，在所获得的时间序列基础上，绘制销售额时间序列图形，以发现销售变化的规律。

3. 汇总各客户销售额与客户数，绘制客户销售额与客户数的帕累托曲线，分析本公司重点客户销售情况，确定今后针对客户销售的工作重点。

4. 汇总客户销售额与销售次数，绘制各订单销售额与销售次数的帕累托曲线，分析单张订单销售中存在的问题，确定今后单张订单销售工作的重点。

5. 汇总“北风贸易”公司各地区客户的分布。

6. 汇总norrhwind公司各地区平均销售额，并利用前面汇总的数据，绘制各地区客户的分布以及平均销售额，以帮助分析各地区的销售情况。

7. “北风贸易”公司把单张订单销售额超过2000元的销售定义为大宗销售，汇总大宗销售各月销售额，绘制销售额时间序列图形，并预测未来2个月的销售情况。

实验2-2

步骤1：获取汇总所需数据。

步骤2：汇总前十大客户月销售额。

步骤3：汇总主要销售人员月销售额。

【实验思考】

1·还能从那些方面对客户的销售数据进行分析，帮助该公司促进销售，为客户提供更好的服务？

答：还可以对产品的销售量、价格、销售额、销售成本、变动成本等销售数据进行汇总分析，找出哪些产品需求大的、哪些产品需求量少，分析销售几个是否合理，找出哪些产品的销售额大，哪些产品的销售成本低，哪些产品的变动成本低，从而对产品的生产和销售进行合理的调整，帮公司销售更多的产品，获取更多的利润，为客户提供更好的服务。

2·pareto曲线可以帮助分析投入和产出之间的关系，它还能帮助该公司进行哪些方面的分析？

答：还可以分析客户数量百分比与客户销售变量累计百分比之间的关系；客户数与销售额之间的关系；销售额与销售次数之间的关系等。

3·如果销售经理希望同时观测某指定月份各类别、各地区、各运货商承运的销售额，应如何制作这样的观测板？

答：分别以类别、地区、各运营商为对象进行汇总销售额，然后以月份为主要动态参数进行调整，在一个观测板上同时包含所有的次要变量，就可以在一个观测板上同时看到所有的变化。

4·如果销售经理希望同时观测某指定月份、某指定类别的各地区、各客户、各城市和各产品的销售额，应如何制作这样的观测板？

答：分别以类别、地区、各运营商为对象进行汇总销售额，然后以月份为主要动态参数进行调整，将这几个变化的次要参数来制作不同的观测板，就可以在不同的观测板上看到每一个参数的变化。

【实验心得】：

实验三  餐饮公司经营数据时间序列预测

【实验环境】

?Microsoft Office Excel 2003；

?通过Excel中的“加载宏”加载“规划求解”和“分析工具库”工具。

【实验目的】

?理解指数平滑预测法、移动平均预测法、趋势预测法、非线性趋势预测法和季节指数的概念；

?掌握在EXCEL中建立指数平滑预测模型、移动平均模型、线性趋势预测模型、非线性趋势预测模型和季节指数预测模型的方法；

?掌握寻找最优平滑常数、最优移动平均跨度和线性趋势模型参数的各种方法。

【实验步骤】

实验3-1

一、运用“数据分析”工具进行指数平滑预测

步骤1：确定时间序列的类型。

步骤2：利用“数据分析”工具中的指数平滑功能进行预测。

二、运用指数平滑公式进行预测

步骤1：利用公式 计算指数平滑预测值。

步骤2：绘制指数平滑预测图。

三、寻找最优的平滑常数

步骤1：计算均方误差。

步骤2：利用模拟运算表及查找引用函数功能，寻找最优平滑常数。

步骤3：利用规划求解功能，寻找最优平滑常数。

实验3-2

一、运用“数据分析”工具进行移动平均预测

步骤1：确定时间序列的类型。

步骤2：利用“数据分析”工具的移动平均功能进行预测。

二、运用移动平均公式进行预测

步骤1：利用average()函数计算移动平均预测值。

步骤2：绘制移动平均预测图。

三、寻找最优的移动平均跨度

步骤1：计算均方误差。 

步骤2：利用offset()函数辅助进行不同移动平均跨度下的预测。

步骤3：利用模拟运算表及查找引用函数功能，寻找最优移动平均跨度。

【实验思考】

1·在实验3-1中，为什么用模拟运算表加查找引用函数功能得到的最优平滑常数（0.35），与规划求解功能得到的平滑常数（0.37）不一样？

答：因为利用模拟运算表加查找引用函数功能得到的最优平滑常数时左边是以0.05为间隔，其中没有0.37这个数据，查找最小值时是从左边中选取，但是规划求解功能却不一样，它是从满足条件的实数中选取，没有间隔的限制。

2·在实验3-1中，可否调整模拟运算表的输入数据间隔，再试一试，结果会如何？

答：如果输入的间隔变小，得到的最优平滑常数会更加精确，如果输入的间隔加大的话得到的最优平滑常数会粗糙些。

3·在实验3-2中，可否利用规划求解功能，寻找最优的移动平均跨度？

答：可以，只需要将MSE的值所在单元格作为目标单元格，条件为F1>0，F2>0。

4·EXCEL提供的移动平均趋势线功能，也可进行移动平均预测，但趋势线方法与实验3-2所介绍的方法有何不同？

答：移动平均趋势线是用来预测将来某个时段的数据，而趋势线是用来确定时间序列的类型。

5·本实验的几张图中，X轴式分类还是自动？

答：X轴分类，因为X轴上的年份不代表线性趋势线中的x。

6·在实验3-3中，预测点数据如果作为新数据系列添加到图形中，结果与图3-29有何不同？

答：如果作为新的数据点添加到图形中，它只是一个孤立的点，并不能代表预测的趋势。

7·为什么预测值一定在趋势线的延伸线上？

答：因为预测值是根据趋势线的线性方程得到的，因此会在趋势线的延伸线上。

8·若要预测公司20##年全国销售额，可以怎么做？若要预测公司20##年、20##年，甚至更远年份的销售额，会有什么问题？

答：将12,15,16代入线性趋势线得出的线性公式，将数据点添加到趋势线中即可。趋势线只能预测较短时间内的数据，一旦时间过长，就会与实际值有很大差。

9·除了实验3-3中介绍添加趋势线方法可以找到线性趋势预测模型的参数外，还可以用哪些方法找到线性趋势预测模型Y=a+bX中的参数a,b?

答：还可以利用规划求解功能或者最小二乘法求得a，b。

10·请试一下实验3-4中图3-39可够考虑用x-y散点图做？这时用什么数据作为x轴合适？

答：在图3-39中x轴以年份字段分类是个错误的做法，因为年份不可能作为自变量代入公式中的ln(x)中，应该用序号数据作为x轴。此题中，可以考虑用散点图，此时可以用年份字段作为X轴。

11·为什么预测值一定在趋势线的延伸线上？

答：因为预测值是根据趋势线的线性方程得到的，因此会在趋势线的延伸线上。12·除了实验3-4中介绍的添加趋势线的方法可以找到对数趋势预测模型的参数外，是否可

以利用规划求解法找到对数预测模型Y=a+bln(X)中的参数a和b?

答：可以利用规划求解功能得到a，b

13·实验3-5的图3-47中的“序号”一列有什么作用？

答：序号一列作为公式“=Forecast（A2，F2:F17，A2:A17）”中的第一个参数。

14·计算趋势预测值时，若不用FORECAST（）函数，还可以有什么方法？请至少用2中方法试试看。

答：根据预测趋势线得到的公式来预测；或者利用TREND函数来预测。

15·季节指数模型是否只能用于季节数据的预测？若是年度、月度、甚至周数据，可以用季节指数模型吗？

答：可以用于年度、月度、周数的预测，只是数据统计的间隔不一样。

【实验心得】

 

 

 

实验四  住房建筑许可证数量的回归分析

【实验环境】

?Microsoft Office Excel 2003；

?通过Excel中的“加载宏”加载“规划求解”和“分析工具库”工具。

【实验心得】

1.实验4-1

?理解一元线性回归分析的概念；

?针对不同的问题，能够建立一张适当的一元线性回归模型；

?掌握内建函数SLOPE（）、INTERCEPT（）、 与LINEST（）的用法；

?掌握用规划求解法、添加线性趋势线法、回归分析报告法确定线性回归方程的系数；

?在给定自变量的情况下，根据线性回归模型预测因变量的值。

2.实验4-2:

?理解一元非线性回归分析的概念；

?针对不同的问题，能够建立适当的一元非线性回归模型；

?掌握用规划求解法、添加非线性趋势线法、变换法确定非线性回归方程的系数；

?在给定自变量的情况下，根据非线性回归模型预测因变量的值。

3.实验4-3：

?理解多元线性回归分析的概念；

?针对不同的问题，能够建立适当的多元线性回归模型；

?掌握运用向前增减法确定回归自变量；

?在给定自变量的情况下，根据多元线性回归模型预测因变量的值。

4.实验4-4：

?理解多元非线性回归的概念；

?针对不同的问题，能够建立适当的多元非线性回归模型；

?掌握用线性规划求解法、变换技术加回归分析报告法确定多元非线性回归方程的系数；

?在给定自变量的情况下，根据多元非线性回归模型预测因变量的值。

实验4-1

步骤1：确定因变量与自变量并输入观测值。

步骤2：绘制因变量与自变量关系散点图。

步骤3：求出回归系数a、b的取值，计算判定系数r2，并进行预测。

步骤4：假定回归系数的值，建立线性回归模型。

步骤5：启动规划求解工具，确定模型最优参数。

步骤6： 计算判定系数r2，说明建筑许可证数量的预测值的可信度。

实验4-2

步骤1：确定因变量与自变量。

步骤2：选择合适的回归方程。

步骤3：假定回归系数的值，建立非线性回归模型。

步骤4：确定参数a与b的值。

步骤5：添加趋势线，显示值。

步骤6：进行预测。

实验4-3

步骤1：输入原始数据。

步骤2：分别绘制三个候选自变量与因变量之间的关系图。

步骤3：针对每一个候选变量生成回归分析报告。

步骤4：根据调整后的值确定回归分析所要采用的两元自变量。

步骤5：根据调整后的值确定回归分析所要采用的三元自变量。

步骤6：根据回归分析报告结果，确定多元自变量及回归模型。

实验4-4

步骤1：确定因变量与自变量。

步骤2：确定模型并对模型初始化。

步骤3：启用规划求解工具。

步骤4：根据获得的参数进行预测。

步骤5：将非线性模型与线性模型结果比较。

【实验思考】

1·除了用规划求解的方法外，还可以用哪些方法求出建筑许可证数量与每平方公里人口密度之间关系的回归方程Y=a+bX的系数，请用其他方法求得系数，并检验与实验4-1所获得系数是否一致？

答：回归分析报告，最小二乘法，或者可以根据人口密度与建筑许可证的颁发数量数据做折线图，然后添趋势线，得到线性方程。系数基本一致。

2·如果每平方公里的人口密度与建筑许可证数量之间是非线性关系，该如何选择非线性模型，并针对任意给定每平方公里的人口密度，预测建筑许可证的颁发数量。

答：若为非线性，则将预测方程改为Y=a+b*x+c*x²,进行预测，或者添加其他趋势线，看R²，以来判断那种最接近于非线性关系。

3·请将实验4-2转换为线性回归模型，求解模型的参数和R²的值，并于规划求解法的结果进行比较。

答：在添加趋势线的时候，选择添加线性趋势线即可知道线性趋势线回归模型中的参数R²值。非线性回归模型中R2=0.9441，方程为：y = 16878Ln(x) – 78877，当转换为线性方程时：R2= 0.8729，线性方程为：y = 49.453x + 1664.8。

4·在用回归分析报告求解参数时，自变量和因变量之间应该满足什么关系？

答：满足线性关系，因为回归分析报告求解参数只针对线性关系的方案。

5·为什么实验4-3的结果只适用于二元线性回归模型而不适用于三元线性回归模型进行建筑许可证数量的预测？

答：预测模型为线性关系，而在实验4-2中已经证实自有房屋均值与建筑许可证之间是非线性关系，而故选用二元关系。

6·在用多元线性回归时，如何确定候选变量，确定的依据是什么？

答：根据拟合优度R²来判断最优变量。

7·从实验4-2了解到自有房屋的均值（X2）与建筑许可证数量是对数相关，如果用回归方程Y=a+b₁X₁+b₂lnx₂+b₃x₃来进行预测是否更精确？那么又怎么样确定此方程的各项系数呢？

答：是会更加精确，因为影响因素越多对预测值的限定会越大；要确定此方案的参数可利用规划求解。

8·如果要用回归分析报告求解多元非线性模型的参数，首先要做的是什么工作？怎么做？

答：首先要将非线性模型转换为线性模型，确定自变量与因变量。

9·现王经理经过深入调查研究发现：建筑许可证的颁发数量既与每平方公里的人口密度有着密切的关系，也与自由房屋的均值有着密切的关系，此外还与平均家庭收入、人口增长百分比、失业率、人均缴纳的税收有着密切的关系，并给出相关数据如表4-5，根据表4-5所给的数据，想想用什么方法能够较快地找出合适的自变量建立多元回归分析模型，并以此预测建筑许可证的颁发数量。

答：如果要用线性回归方式找拟合优度最大的自变量，那样要做21次回归分析报告，很麻烦，可以采用其他的方法来寻找最优拟合优度。

【实验心得】

 

 

实验五  电信公司宽带上网资费与电缆订货决策

【实验环境】

?Microsoft Office Excel 2003。

【实验目的】

?掌握盈亏平衡决策模型、成本决策分析模型、经济订货量模型的建立方法；

?掌握一维模拟运算表和二维模拟运算表的运用方法；

?掌握动态图形和可选图形的绘制方法；

?掌握均匀分布随机数的生成方法；

?掌握活动扫描系统模拟的一般方法。

【实验步骤】

实验5-1

步骤1：建立利润模型，判断包年制资费方案是否盈利。

步骤2：建立盈亏平衡模型，寻找资费方案的保本点。

步骤3：绘制盈亏平衡图形，观测资费方案亏损点与保本点的差距。

步骤4：使用年新增客户数控件，寻找目标利润点。

步骤5：运用扩展的盈亏平衡模型，确定目标利润对应的新增客户数。

实验5-2

步骤1：建立资费表、带宽列表框和上网时间微调项。

步骤2：根据两种方案的资费标准，建立包年制和限时制的年总资费计算模型。

步骤3：建立模拟运算表，查出包年制与限时制无差别点，并给出决策建议。

步骤4：绘制两种方案的年总资费曲线图。

步骤5：使用控件建立两种资费方案的决策图形。

实验5-3

步骤1：分析问题，建立计算经济订货量与整车运输优惠订货量模型。

步骤2：检查模型中各单元格的公式。

步骤3：绘制不同单位订货费下的年总成本曲线簇。

步骤4：绘制成本最低点水平参考线和参考点。

步骤5：在图形上添加控件，进行最优电缆订货方案的决策选择。

实验5-4 电信公司电缆库存与订货决策的模拟分析

步骤1：建立经济订货量理论模型。

步骤2：考虑电缆需求量的不确定性，模拟一年的库存变化与年总成本。

步骤3：绘制理论模型成本与模拟模型成本曲线图。

步骤4：对模拟总成本进行试验与结果分析，寻找更准确的模拟经济订货量。

【实验思考】

1·电信公司除了从增加客户数量上来增加利润外，还能从其他哪些渠道来获取利润？

答：降低运营和销售成本；增加服务的质量，还有就是根据不同的人群，给予相应的资费。

2·如果电信公司受到竞争公司的价格挑战，可能调低每年每户资费，能够修改模型，使模型能够反映资费价格的调整对保本点和目标利润点得影响吗？

答：可以，相当于增加了一个新的变量，此时模型中就有了新增客户和资费价格两个变量，此时只需要修改保本点函数和目标利润点函数中的变量就可以了，重新构造函数。

3·如果是电信公司的用户，带宽要求是512KB，每月的上网时间估计150小时，他将如何选择资费方案呢？

答：他应该选择限时制。因为限时制的年总资费为：1230元；而包年限年总资费为：1400元。

4·有两个电信公司的客户，他们的上网宽带要求都是2M，但其中一个客户每天上网时间不超过3小时，而另外一个客户每天上网时间超过10小时，他们该选择同样的资费吗？为什么？

答：每天上网时间不超过3小时，那么平均上网时间即不超过90个小时；每天上网时间超过10个小时，那么平均上网时间就超过了300个小时，所以每天上网不超过3小时的应选择限时制套餐，而每天上网时间超过10小时的，应选择包年限套餐。

5·如果电缆供应商将整车运输起始吨位调高20吨，一次订货的订货费也因采购员的努力而降到了40元，那么电信公司该选择那种订货方案？

答：此时若采用整车运输的起始订货量订货的话，订货费用1=33500元，若是采用经济订货量订货的话，订货费用2=32746元，因此电信公司应该采用经济订货量订货。

6·如果电信公司的业务拓展受到市场竞争的影响，电缆的年需求量是不确定，那么，如何修改模型才能帮助电信公司进行订货方案的决策呢？

答：只需要在上述的基础上将电缆的年需求量也作为变量，而不是常量，然后重新修改各个成本的公式，以便计算总成本即可。

7·模拟订货模型是否比理论理论订货模型更适用实际应用？为什么？

答：是，因为现实中订货量是随机出现的，而模拟订货模型是以订货量随机出现的，更加有现实作用。

8·如果电缆供应商不能保证及时供货，交货期可能是1到3天不等，能否修改模型以符合随机交货的情况？

答：在原来模型的基础上增加一个1至3的随机变量，将随机变量交货期考虑到成本中。

【实验心得】

实验六  奶制品厂生产/销售的最优化决策

【实验环境】

?Microsoft Office Excel 2003；

?通过Excel中的“加载宏”加载“规划求解”和“分析工具库”工具。

【实验心得】

1.实验6-1：

?理解最优化模型的概念；

?掌握在EXCEL中构造线性规划模型的方法；

?掌握用“规划求解”工具正确求解最优化问题的步骤。

2.实验6-2：

?理解“选址问题”的规划模型；

?掌握在EXCEL中构造0-1规划模型的方法；

     ?掌握用“规划求解”工具正确求解选址问题的步骤。

3.实验6-3：

?理解“运输问题”的规划模型；

?掌握在EXCEL中构造整数规划模型的方法；

?掌握在”规划求解“工具正确求解运输问题的步骤。

【实验步骤】

实验6-2

步骤1：分析问题，确定解决方案。

步骤2：在excel中建立模型框架。

步骤3：参考图6-9，检查模型中各单元格的计算公式。

步骤4：设置规划求解的各项参数并求解。

步骤5：根据求解结果，标出各供应站连接的先后顺序。

实验6-3

步骤1：分析问题，整理出决策变量、目标变量、目标函数和各项约束条件函数。

步骤2：根据目标函数和约束条件，在excel中建立参考模型。

步骤3：参考图6-13，检查模型中各单元格的计算公式。

步骤4：设置规划求解的各项参数并求解。

【实验思考】

1·在实验6-1中，“猛牛”奶制品厂如果另有500元的资金可投入到两种产品的生产中，请问：应该将这笔资金用于增加生产能力、人力还是原材料？请详细说明理由。

答：应该增加原材料，因为根据运算结果报告知：只有安排生产数量即实际量这个变量没有达到极限值，其他的都达到极限值，也就是说增加另外两个变量的值不会增加利润，只有增加安排生产数量才会增加利润。还有根据材料的影子价格为48，也就是说每增加一个单位的原材料供应量，可以使总利润增加48，由此可以看出供应量对总利润的影响最大。

2·在实验6-1中，如果要使1号生产线满负荷运转，则需要安排工人加班。请问：支付给加班工人每小时的加班工资最多为多少？

答：当1号生产线负荷为60时，1号生产线工时为240，总利润为3360，当1号满负荷运转，负荷为100时，1号生产线工时为400，总利润为4320；那么工人每小时的加班费最多=（4320-3360）/(400-240)=6

3·实验6-2中求得的最短路径是否唯一？

答：是唯一的，因为反复利用规划求解功能时无法找到另一个结果。

4·实验6-3中，如果除保证给客户承诺的供应量外，还要保证供给客户丁的数量两倍于供给客户丙的数量，那么求解时应做哪些改动？

答：只需要在约束条件中加上“F13=E13*2”即可。

【实验心得】：

 

 

 

 

 

实验七  运动鞋公司经营投资决策

【实验环境】

    ?Microsoft Office Excel 2003。

【实验目的】

?掌握财务函数的应用；

?掌握现金流量、净现值、投资回收期、内部报酬率等概念；

?掌握基于回收期、内部报酬率和净现值的多种投资决策建模方法。

?了解企业扩大生产规模的不同方案；

?掌握决策参数的不确定性对决策结果的影响。

?了解企业筹措资金的不同方案；

?掌握资金筹措的投资决策建模方法。

【实验步骤】

实验7-1

步骤1：建立基于投资回收期的投资评价型框架。

步骤2：利用控件调节，求出投资回收期。

步骤3：建立基于内部报酬率的投资评价模型框架。

步骤4：利用查表法，求出最大内部报酬率。

步骤5：建立基于净现值的投资评价模型框架。

步骤6：利用查表法，求出最大净现值。

步骤7：建立净现值的可调图形，进行图形化决策分析。

实验7-2

步骤1：分析问题，建立投资决策模型框架。

步骤2：整理现金流，求出三个方案的净现值。

步骤3：运用查表法，确定最优投资方案。

步骤4：绘制三个投资项目的净现值曲线图，进行图形化决策分析。

实验7-3

步骤1：分析问题，整理三种资金筹措方案每年的资金流进与流出。

步骤2：求出三个资金筹措方案的净现值，确定最优投资方案。

【实验思考】

1·比较投资回收期，内部报酬率和净现值指标在投资评价中的相互关系？

答：投资回收期是当收入超过投入的时间；内部报酬率是使投资项目的净现值等于零的贴现率，它实际上反映了投资项目的真实报酬。净现值是指投资方案所产生的现金净流量以资金成本为贴现率折现之后与原始投资额现值的差额。净现值法就是按净现值大小来评价方案优劣的一种方法。净现值大于零则方案可行，且净现值越大，方案越优，投资效益越好。

2·在实验7-1中，实际情况是市场上有许多不确定因素，未来3年的预期收入是未知的，需要进行预测，“太阳”运动鞋公司经过对过去市场上同类产品的调查得知，各产品存在生命周期，销售价格逐年下降，具体调查数据如下表。

请问：根据历史上产品价格的趋势分析，20##-20##年的各产品销售单价可能为多少？如果20##-20##年的生产成本及销售数量如下表所示，那么那种产品的投资回报率最高？

答：20##-20##年的各产品销售单价可能为投资回报率最高的产品为气垫鞋。

3·实验7-2除了用净现值指标衡量外，还可以用其他什么指标进行投资决策分析？

答：除了利用净现值指标衡量外，还可以利用内部回报率、投资回报期等来进行投资决策分析。

4·由于经济发展的周期性，金融市场的贷款利率呈周期性变化。市场贷款平均利率历史数据如下：

请问市场的周期性波动对投资决策有什么影响？结合所了解的企业情况搜集企业资金需求数据进行某方面的投资决策分析。

答：市场利率的波动影响着投资的数额及净现值等因素。市场利率较高时，资金回报率较高, 但投资风险大。此时应选择风险相对较小的项目进行投资或进行多项业务的投资以冲减 投资风险。

5·企业融资后，会有大量的现金，如何合理安排资金计划，对企业更加有力？

答：在恰当的时机拿出一部分钱进行投资，留一部分负责企业的资金运转。

【实验心得】