经济管理软件上机实验指导手册

经济管理软件上机实验

指导手册

参考教材：《数据、模型与决策-运用电子表格建模与案例研究》Frederick S.Hillier and Mark.S.Hillier

浙江理工大学经济管理学院

周晓林编

2011年12月

实验一：EXCEL电子表格应用初步

一、实验目的

1、了解EXCEL电子表格的结构；

2、掌握EXCEL电子表格中单元格的表示方法；

3、掌握EXCEL电子表格中对单元格的各种操作；

4、掌握EXCEL电子表格中公式的输入方法。

二、实验内容

对某种产品进行盈亏平衡分析。

例题：特殊产品公司生产在商店销售的昂贵而不常见的礼品，礼品是为那些已经几乎什么都有的富人生产的。公司研发部最新的产品计划是有限版落地摆钟（limited edition grandfather clock）。公司管理部门需要决定是否生产这个新产品，生产量为多少时才能盈利？

•Data:

–If they go ahead with this product, a fixed cost（固定成本）of $50,000 is incurred.

–The variable cos t（变动成本）is $400 per clock produced.

–Each clock sold would generate $900 in revenue（销售收入）.

–A sales forecast will be obtained

决策：如果要生产，生产多少落地摆钟？

三、实验步骤

1、首先在草稿纸上对这个问题进行比较细致的分析

首先引入变量Q表示生产的落地摆钟的数量，即

Q =落地摆钟的生产数量

决策的目标是使公司从该产品中所获利润最大：

利润=总收入-总成本

总收入=900Q

总成本=固定成本+变动成本

固定成本=50000 当Q>0

变动成本=400Q

因此，

总成本

= 0 如果Q=0 50000+400Q 如果Q>0

利润=总收入-总成本=900Q-50000-400Q=-50000+500Q 如果Q>0

盈亏平衡点（利润=0）=固定成本/（单位产品销售收入-单位产品变动成本） 一般情况下，Q<=需求量 完整的数学模型：

Max 利润= 0 如果Q=0 50000+500Q 如果Q>0 其中：0<=Q<=S

其中：S 是预测所能售出的落地摆钟数量

s

Q 100,s 0Q 100,s 400-900 50000=

衡点盈亏 =>=≤则如果则如果平

2、在数据单元格输入：单位收入$900，固定成本$50000，变动成本$400，销售量预测值 300。

3、在输出单元格输入公式。在输入公式之前，首先对有关单元格定义名称， 如 单位产品收入, 产量, 固定成本，变动成本等。 在excel 中的操作：

excel2010中定义名称：

可以通过Ctrl + ~来切换数据和公式界面：

4、可通过改变蓝色区域内的数据进行灵敏度分析及决策分析。

实验二：应用EXCEL电子表格求解线性规划

一、实验目的

1、了解EXCEL规划求解宏模块的功能；

2、掌握EXCEL规划求解宏模块的加载；

3、掌握在EXCEL电子表格中建立线性规划模型；

4、掌握用EXCEL规划求解宏模块求解线性规划。

二、实验内容

EXCEL规划求解确定伟恩德玻璃制品公司产品组合问题

伟恩德玻璃制品公司生产高质量的玻璃制品，包括工艺精湛的窗和玻璃门。公司有三个工厂：

工厂1：生产铝框和五金件

工厂2：生产木框

工厂3：生产玻璃和组装窗与门

公司打算生产的新产品

8英尺玻璃门

4英尺×6英尺双层窗

现在管理部门要考虑下列问题：

决策：如果生产，两个产品的生产组合如何？-每周分别生产多少数量？

基本生产信息如下表：

运用电子表格建立数学模型（线性规划模型）的过程中有三个问题需要得到回答：1．要作出的决策(decisions)是什么？

2．在作出这些决策上有哪些约束条件(constrains)？

3．这些决策的全部绩效测度(measure of performance)是什么？

三、实验步骤

1、选择决策变量单元格C12：D12（称为可变单元格，changing cell），决策变量的初始值一般赋0，并用较醒目的颜色（黄色）表示。

2、确定目标单元格(对应目标函数，target cell)G24，用函数公式表示，并用较醒目的颜色（桔黄色）表示。

3、用公式输入每一个约束条件左边项，即确定输出单元格(output cell),E18：E20。

注：sumproduct函数在规划求解中很常用，含义：sumproduct(C4:D4,C12:D12)指把C4:D4变化范围内的每个值与C12:D12变化范围内对应的每个值相乘，然后将各个积相加。该函数中的参数要求同是行或同是列，并且单元格数一样。

如果要求行和列对应单元格乘积之和，可用MMULT函数。

MMULT(array1,array2)是要进行矩阵乘法运算的两个数组。

4、Excel Solver 的安装。Excel工具菜单中选择加载宏

Excel 2010的操作：

文件-选项-加载项-转到，即可出现加载界面。

Excel2010调用规划求解宏：数据-规划求解