在線等（論文）：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 =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”下的Solver文件夾及其目錄下的Solver.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）根據以上步驟，可得到本模型的計算結果如表三所示。從表三可以看出，當甲30公斤、丙40公斤、丁30>公斤而乙為0時，成本達到最小，最小成本為670元。

表二 A B C D E F G

1 甲乙丙丁

2 數

量 5 6 7 8

3 單

價 1 1 1 1