如何用Excel做决策支持系统的敏感度分析

以一具体实例来阐明Excel在经济数学模型中的应用。
原料配比问题
表 一
原 料 药物
甲 乙 丙 丁
A 1 1 1 1
B 5 4 6 5
C 2 1 1 2
某药厂生产A、B、C三种药物，可供选择的原料有甲、乙、丙、丁四种，成本分别是每公斤5元、6元、7元、8元。每公斤不同原料所能提供的各种药物如表一所示。药厂要求每天生产A药品恰好100克、B药品至少530克、C药品不超过160克。要求选配各种原料的数量，即满足生产的需要，又使总成本最少。
求解方法：
（1）建立简单的数学模型。根据题意，设X1、X2、X3、X4分别表示甲、乙、丙、丁原料的用量，易得到如下线性规划：
目标函数： Min Z＝5X1＋6X2＋7X3＋8X4
约束条件： X1+X2+X3+X4=100
5X1+4X2+5X3+6X4≥530
2X1+X2+X3+2X4≤160
X1≥0,X2≥0，X3≥0
（2）将该线性规划问题的数学模型按表二样式输入Excel中，在表二中，有关单元格所含公式如下：
单元格 公 式
C5 =D3D5+E3E5+F3F5+G3G5
C6 =D3D6+E3E6+F3F6+G3G6
C7 =D3D7+E3E7+F3F7+G3G7
C8 =D2D3+E2E3+F2F3+G2G3
（3）选择“工具”菜单中“加载宏”选项，在安装提示下装入“规划求解”（注意要插入安装盘）。也可以把安装盘中“Pfiles\Office\Library”下的Solver文件夹及其目录下的Solverxla、Solvr32dll复制到Office安装目录“Office\Library”下，然后加载即可。
（4）在“工具”菜单中选择“规划求解”，然后在d出的“规划求解参数”对话框中通过点击C8单元格使“目标单元格”出现$C$8的绝对引址，并根据本题题意在其后的小框框内选择“最小值”。在“可变单元格”中通过从表格中选择D3:G3区域，使之在文本框内出现$D$3:$G$3。在“约束条件”处按“增加”，然后在出现的“增加约束”对话框中的“单元格引用位置”处通过点击C5单元格使之出现$C$5,在后面的框框内选“＝”，“约束值”编辑为$B$5。类似地，第二、三、四个约束条件分别编辑为“$C$6≥$B$6”,“$C$7≤$B$7”,“$D$3:$G$3≥0” 按“确定”退出。
（5）按“求解”按钮，在d出的“规划求解结果”对话框内可根据需要生成运算结果、敏感性分析和限制范围的报告，然后按“确定”对模型进行求解。
（6）如发现数字解为小数，可按需要该为用整数表示，方法如下：
① 按住Ctrl键，分别选定需改为用整数表示的单元格D3、E3、F3、G3、C8。
② 选取“格式”、“单元格… …”、“数字”、“科学计数”。
③ 在“小数位数”中选定“0”格式。按“确定”退出。
（7）根据以上步骤，可得到本模型的计算结果如表三所示。从表三可以看出，当甲30公斤、丙40公斤、丁30>公斤而乙为0时，成本达到最小，最小成本为670元。
表 二 A B C D E F G
1 甲 乙 丙 丁
2 数
量 5 6 7 8
3 单
价 1 1 1 1
4 约
束
条
件 最
适
结
果
5 a 100 1 1 1 1
6 b 530 5 4 5 6
7 c 160 2 1 1 2
8 总成本
表 三
A B C D E F G
甲 乙 丙 丁
2 数
量 5 6 7 8
3 单
价 1 1 1 1
4 约
束
条
件 最
适
结
果
5 a 100 100 1 1 1 1
6 b 530 530 5 4 5 6
7 c 160 160 2 1 1 2
8 总成本 670
用Excel的规划求解工具线性规划问题，简单易行，很容易掌握。其规律及技巧可归纳为：在实际的求解过程中，只需确定目标函数单元格及“可变单元格”区域位置两处单元格位置，然后正确地输入约束条件和确定所求的目标是最大还是最小即可求得正确结果。
利用Excel提供的规划求解法可以解运筹学中的许多问题，譬如线性规划、指派问题、运输问题、机器分配问题、人事安排… …等，只要是对生产、制造、投资、财务、工程等求最大利润、最小成本等问题，就基本上可以用规划求解法快速得到答案。

一、在excel中输出敏感性分析的图表 第一、建立基础数据 可以利用EXCEL的滚动条调节百分比值 第二、多因素变动对利润的综合影响 1、计算预计利润额 利润额=销售量（产品单价—单位变动成本）—固定成本敏感分析应用广泛，主要是在求得某个模型的最优解后，研究模型中某个或若干个参数允许变化到多大。

打开“工具”下拉菜单，找找看有无“数据分析”功能键，如果没有，找原盘加装一下，方法是依然在此菜单点“加载宏”，然后再探出窗口选后5项，点确定，以后照提示去装就可以了。装完后应该就可以进行分析

假定生产产品A时，年固定成本为50000元，年产销量 ，单位售价，单位变动成本同上。试分析生产该产品的盈亏平衡情况。
盈亏平衡分析
1 制工作表2：
盈亏平衡分析的公式：
销售收益 = 产销量 单位售价
总成本 = 固定成本 + 产销量 单位变动成本
利润 = 销售收益 – 总成本
产销量盈亏平衡点 = 固定成本 / （单位售价 – 单位变动成本）
在B2: C6单元格：输入已知的条件：B2:C2格 用乙机器生产产品A
B3格固定成本、B4格年产销量、B5格单位售价、B6格单位变动成本、C3:C6格输入已知数据：C3 50000 C49000 C510 C6 6
在B8:C10单元格输入： B8格 销售收益、B9格 总成本、B10格 利润
在 C8输入 =C4C5
C9输入 =C3+C4C6
C10输入 =C8-C9
选定B2:C10 套框同上
在B12:C15输入：B12格盈亏平衡时产销量、B13格销售收益、B14
格总成本、B15格利润
在 C12输入 =C3 /（C5-C6）
C13输入 =C12C5
C14输入 =C3+C12C6
C15输入 =C13-C14
选定B12:C15 套框同上
作一维模拟运算表，分析在不同产销量的情况下，销售收益、总成本、
利润的变化。
在E2:H16输入：E2格年产销量、F2格销售收益、G2格总成本、H2格
利润。
在 E4格输入 8000 工具栏选编辑 \ 填充 \ 序列 \ 选 列 、等差序列、步长值 1000 、终止值20000 确定
F3格输入 =C8 、G3格输入 =C9 、H3格输入 =C10
选定E3:H16 工具栏选择 数据 \ 模拟运算表 \ 输入引入列单元格 $C$4 \ 确定 会自动填充数据反映和利润随产销量的变化情况
选定 E2:H16 套框同上
数字换位：
选定数字区域 工具栏选格式 \ 单元格 \ 数字 \ 数值 \ 小数点后0位数、使用千分位分隔符、确定

敏感分析应用广泛，主要是在求得某个模型的最优解后，研究模型中某个或若干个参数允许变化到多大，仍能使原最优解的条件保持不变，或者当参数变化超过允许范围，与那最优解已不能保持最优性时，提供一套简洁的计算方法，重新求解最优解。
在本量利关系的敏感分析中,主要包括两个部分
1、研究分析有关参数发生多大变化时盈利转为亏损。
基本方程式：销量（单价-单位变动成本）-固定成本=0
每次令一个参数为变量，其他为常量。
2、个参数变化对利润变化的影响程度。
主要采用敏感系数计量。
敏感系数=目标值变动百分比/参量值变动百分比。
例如计算利润对单价的敏感度
假设单价变动20%，利润为r，单价为s，单位变动成本为c，销量为q，固定成本为f。
单价变动前
r1=q(s-c)-f
单价变动后
r2=q[s(1+20%)-c]-f
则利润对单价的敏感系数=（r2/r1）/20%
表示的含义是单价变动1%，利润变动变动多少个百分点
在工作中，为了让你的分析报告更好看或者内容充实，有时候还可以附上敏感分析表和敏感分析表。所谓敏感分析表，就是假定单价、单位变动成本、销量、固定成本上下浮动0、5%、10%、15%、20%（这些间距可以自由设定）时，利润的绝对额是多少。弥补敏感系数只能反映利润随项目变化而变化的相对量的缺陷。而敏感分析图则连续表示变量间的关系，绘制时，参照敏感分析表的数据绘出点，然后连线即可。

要用相应的模型中的变量，确定那个模型的函数最好，然后利用不同情况下，因变量改变的结果作对比。自己绘图很麻烦，变量设置多尤其是。巨灵很好用，上面有很多可下载的敏感性分析的图形，可以自己调节，还是立体的。不然就是autoCAD。
如果是管理学的研究可以直接利用巨灵上的图与表。autoCAD是正统的绘图工具，变量很多，可以自己随意设置，还可以设置立体图效果。只用Excel好像不比autoCAD简单，excel只能自己研究了偶对excel也不是很精通

---