Excel來進行資本預算編制計劃

根據下面的實例，我們來了解學習下如何通過Excel來進行資本預算編制計劃。

某食品生產商為了擴大生產規模和提高生產效率，要制定今后兩年的資本預算編制計劃。共有6個候補項目，要從中選擇最優項目組合（圖1）。

圖1

資源的約束條件如下所示。

第一年度的總支出：350（百萬日元）

第二年度的總支出：400（百萬日元）

技術人員所需時間：8000（小時）

需要選擇新增生產線或者改善現有生產線繼續使用：自動設備只能

應用到新生產線中；整頓物流渠道、新增原料加工廠、完善信息系統必須和其他項目分離，單獨實施。

在上述約束條件下，為了使凈現值最大，應該選擇怎樣的項目組合呢？

凈現值是指投資計劃所產生的現金凈流量（Net Presenl Vlalue）以資金成本為貼現率折現之后與原始投資額現值的差額。求解時不能把不同時間點的金額單純相加，而要把未來價值換算成目前時間點的價值后減去投資額。因此，凈現值是正數。而且數值越大，收益越高。

把6個項目從No.1開始，依次用X1，X2,…，X6等變量表示。假設當Xi=1時，采用項目i；當Xi=0時，不采用項目i。因此，Xi只有0或1這種雙值（二進制）變量。用下述方程表達例題4的問題。

目標函數=凈現值總和

=70X1+130X2+50X3+30X4+80X5+30X6-最大化    （4.2）

當不采用時，Xi的值是0。因此，凈現值總和等于采用項目的凈現值總和，而使這個總和達到最大值的組合是最優規劃。

第一年度的總支出 =200X1+100X2+0X3+9X4+100X5+50X6≤350    （4.3）

第二年度的總支出 =0X1+250X2+150X3+10X4+140X5+50X6≤400    （4.4）

技術人員所需時間 =2000X1+4500X2+1000X3+500X4+2500X5+2000X6≤8000   （4.5）

生產線的約束條件：X1+X2=1  （4·6）

當方程1成立時，有下述兩種情況。

方程1

只能選擇改善現有生產線或新增生產線的其中一項。不能同時采用兩者（X1和X2都是1），也不能都不采用（X1和X2都是0）。

自動設備只適用于新生產線的約束條件：X2-X3≥O    （4.7）

當方程2成立時，有下述三種情況。

方程2

X1是雙值（0-1）變量：X1=0,1（i=1,2,…，6）    （4.8）

使用規劃求解時，需要制作如圖2所示的工作表。單元格C3一C8顯示變量X1,X2X6。

圖2

下面，輸人數學公式。

在單元格D9中，輸入計算第一年度總支出（方程3）的數學公式。 =SUMPRODUCT（$C$3：$C$8，D3：D8）

把單元格D9的數學公式復制到E9——G9。

結果，在單元格E9中，會自動輸入計算第二年度總支出（方程4.4）的數學公式： =SUMPRODUCT（$C$3：$C$8，E3：E8）l

在單元格F9中，會自動輸入計算技術人員所需時間總和的數學公式： =SUMPRODUCT（$C$3：$C$8，F3：F8），

在單元格G9中，會自動輸入計算凈現值總和的數學公式： =SISMPRODUCT（$C$3：$C$8，G3：GS）。

然后。在單元格E16中，輸入生產線的約束條件（方程4.6）。=C3+C4”，在單元格E17中，輸入“自動設備只能應用于新生產線”的約束條件（方程4.7）“=C4一C5”。

完成上述工作后，使用Excel規劃求解。

單擊“工具”-“規劃求解”，彈出“規劃求解參數”對話框。在“設置目的單元格”中指定顯示總成本的單元格G9，在“等于”中選擇“最大值”，在“可變單元格”中指定單元格c3——c8（圖2）。

圖3

設定方程4.3一方程4盤的約束條件后，單擊“求解”。顯示如圖4.23所示的最優解（圖3）。

圖4

最優解是：選擇“新增生產線”、“整頓物流渠道”，“新增原料加工廠”這三個項目，凈現值的總和是240（百萬日元）。

Excel來進行資本預算編制計劃，在約束條件方面，第一年度總支出290<約束條件350，第二年度總支出400=約束條件400，技術人員所需時間的總和7500<約束條件8000。雖然第二年度總支出等于全部預算，但是其他項目仍有富余。