在线等。(论文):Excel在敏感性(财务)分析中的应用

原材料配比问题

表1

原料药

甲基乙基丙基丁基

A 1 1 1 1

B 5 4 6 5

C 2 1 1 2

某药厂生产甲、乙、丙三种药品，有四种原料可供选择，成本分别为每公斤5元、6元、7元、8元。每公斤不同的原料可以提供多种药物，如表1所示。药厂要求每天生产恰好100克药物A，至少530克药物B，不超过160克药物C。要求选择各种原材料的数量，满足生产的需要，使总成本最小化。

解决方法:

(1)建立简单的数学模型。根据题意，设X1，X2，X3，X4分别代表原料A，B，C，D的用量，可以很容易的得到如下线性规划:

目标函数:最小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)按照表2的样式将线性规划问题的数学模型输入Excel。在表2中，相关单元格中包含的公式如下:

单元格公式

C5 =D3*D5+E3*E5+F3*F5+G3*G5

C6 =D3*D6+E3*E6+F3*F6+G3*G6

C7 =D3*D7+E3*E7+F3*F7+G3*G7

C8 =D2*D3+E2*E3+F2*F3+G2*G3

(3)选择“工具”菜单中的“加载项”选项，在安装提示处加载“求解器”(注意插入安装盘)。也可以将安装盘中“Pfiles\Office\Library”下的规划求解文件夹及其目录下的规划求解. xla和Solvr32.dll复制到Office安装目录“Office\Library”下，然后加载。

(4)在工具菜单中选择“规划求解”，然后在弹出的“规划求解参数”对话框中单击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)按“求解”按钮，在弹出的“规划求解结果”对话框中，可以根据需要生成计算结果、敏感性分析和限定范围的报告，然后按“确定”求解模型。

(6)如果发现数值解是小数，可以根据需要表示为整数，方法如下:

①按住Ctrl键，选择需要用整数表示的单元格D3、E3、F3、G3、C8。

②选择格式、单元格、数字和科学计数。

③在“小数”中选择“0”格式。按“确定”退出。

(7)按照上述步骤，可以得到如表3所示的该模型的计算结果。从表3可以看出，甲方为30 >，丙方为40kg，丁方为30 >:当kg和B为0时，成本达到最小，最小成本为670元。

表2 A B C D E F G

1 MEBO

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总成本

表3

英语单词

甲基乙基丙基丁基

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提供的编程方法来解决，如线性规划、指派、运输、机器分配、人员安排等。只要生产、制造、投资、金融、工程等方面的问题。都是利润最大化和成本最小化，用编程的方法基本可以很快得到答案。