□（鄭州升達經貿管理學院會計系 河南鄭州451191）

企業(yè)日常經營決策時，經濟資源的稀缺性要求經營者必須考慮資源的約束問題，即如何合理地利用有限的人力、物力、財力等資源，以便得到最好的經濟效果。這便是線性規(guī)劃問題，也是本文所說的最優(yōu)化經營決策。常見的規(guī)劃問題求解方法有圖解法和單純行法。圖解法雖然直觀、簡單，但它不能解決變量多于三個的問題；單純行法手工求解步驟非常繁瑣，不適合快速、科學決策的需要。因而，要實現資源的合理配置，正確使用規(guī)劃求解工具是關鍵，Excel就是常見的規(guī)劃求解工具之一，其操作簡單、容易理解，受到了越來越多的重視和運用。

一、規(guī)劃求解問題的提出

在資源約束下，合理分配資源達到最好的經濟效果，這類問題都屬于線性規(guī)劃范疇，本文以如何安排產品生產為例來闡述線性規(guī)劃問題。

（一）問題的提出

某企業(yè)在計劃期內要安排生產A、B、C三種產品，每種產品都必須經過甲、乙、丙三個車間的加工生產，已知其資源消耗：（1）A、B、C三種產品的單位變動成本分別為10元、16元和30元；（2）A、B、C三種產品的總固定成本為300 000元；（3）只生產一種產品時，現有資源A、B、C三種產品最大生產量分別為40 000件、35 000件和35 000件；（4）生產工藝要求，A、B、C三種產品在甲車間的單位產品生產工時分別為5小時、4小時和4小時，在乙車間的單位產品生產工時分別為3小時、4小時和5小時，在丙車間的單位產品生產工時分別為4小時、3小時和4小時；（5）甲車間最大工時限量400 000小時，乙車間最大工時限量350 000小時，丙車間最大工時限量300 000小時。

該企業(yè)A、B、C三種產品的銷售單價分別為14元、20元和45元，問如何安排生產計劃使該企業(yè)獲利最多？

（二）建立數學模型

設 x1、x2、x3分別表示計劃期內 A、B、C三種產品的產量，用z表示利潤，則上述計劃問題的數學模型可以表示為：

其中：x1、x2、x3 分別為產品 A、B、C 的產銷量，且為整數。

二、建立Excel規(guī)劃求解模型

上述數學模型的求解是難點，圖解法在此已經無能為力了，手工單純行法通過迭代運算非常麻煩，然而，通過Excel建立規(guī)劃求解模型則簡單方便，模型的通用性和柔性都非常好。

（一）設計Excel規(guī)劃求解模型

根據已知的資源約束和數學模型，建立Excel利潤最大化模型，設計要點：（1）所有的資源約束條件都要考慮到，本模型主要有工時約束和生產能力約束；（2）便于設置公式和計算求解，比如本模型設計時把目標函數考慮在內；（3）具體的設計思路和方法如圖1所示。

設置實際工時、實際產量、利潤計算公式，E9單元格：=SUMPRODUCT($E$20:$G$20,E5:G5)，將該公式復制到E10、E11 單元格 ；E14 單元格 ：=E20，F14 單 元格 ：=F20，G14單元格：=G20，其中E20、F20、G20單元格為可變單元格；利潤E24單元格：=SUMPRODUCT(E20:G20,E21:G21-E22:G22)-E23。利潤（E24單元格）函數說明：利潤等于A、B、C三種產品的邊際貢獻之和減去總固定成本，運用SUMPRODUCT函數可以快速完成計算；{E20:G20}為三種產品產量數組，{E21:G21-E22:G22}為三種產品單價減去單位變動成本后的數組，使兩數組對應元素相乘后再求和即可得出三種產品邊際貢獻之和，然后再減去固定成本（E23單元格）即可。

（二）進行規(guī)劃求解

1.打開“規(guī)劃求解參數”對話框。Excel 2007及以后版本，選擇“數據→分析→規(guī)劃求解”命令，打開“規(guī)劃求解參數”對話框，如圖3所示。設置目標單元格為利潤E24單元格，然后選中“最大值”單元按鈕；可變單元格為實際產量E20:G20單元格區(qū)域，然后可以添加約束條件。在Excel 2003中，選擇“工具→規(guī)劃求解”命令，即可打開“規(guī)劃求解參數”對話框。

圖1 利潤最大化模型

2.添加約束條件。約束條件在進行規(guī)劃求解時非常重要，只有設置合理的約束條件，規(guī)劃求解才能找到滿足要求的解。單擊“添加”，打開添加對話框即可添加條件，根據模型當中約束條件進行添加。工時約束，見圖2，設置思路為：每個車間工時都不能小于零；同時也不能大于最大限量。

圖2 添加工時約束條件

同樣可以設置生產能力約束，注意產量必須是整數即int格式。添加完約束條件后，單擊“確定”按鈕，返回“規(guī)劃求解參數”對話框，在“約束”列表框中可以看到添加的5個約束條件，如圖3所示。

圖3 約束條件

3.規(guī)劃求解并生產相關報告。單擊圖3中【求解】按鈕即可打開規(guī)劃求解結果對話框，圖4所示，單擊【確定】，求解結果如下頁圖5所示。如果在“規(guī)劃求解結果”對話框中，選擇“報告”列表框中的“運算結果報告”、“敏感性報告”和“極限值報告”選項，然后再單擊“確定”按鈕，除了可以顯示出求解結果之外，Excel還會自動插入一張報告工作表。注意：具有整數約束條件的問題不生成敏感性報告及極值報告。

圖4 規(guī)劃求解結果對話框

圖5 利潤最大化模型計算結果

三、Excel規(guī)劃求解難題解析

應用Excel進行生產經營決策規(guī)劃求解，有助于提升企業(yè)的工作效率，給企業(yè)提供最優(yōu)決策參考，其在應用中有兩個難題需要加以解析。

（一）Excel規(guī)劃求解模型的柔性分析

由于企業(yè)經營環(huán)境的不確定性，柔性理論已經深入企業(yè)經營管理的各個方面，這種在高度動態(tài)的競爭環(huán)境下企業(yè)應具備的靈活性被稱為企業(yè)柔性。前面設計的Excel規(guī)劃求解模型中，約束條件往往是估計值和預測值，經營環(huán)境、生成工藝一旦發(fā)生變化，約束條件也會隨之改變。企業(yè)柔性理論要求建立的規(guī)劃求解模型要具有靈活性，當經營環(huán)境、生成工藝等變化后通過模型能夠迅速的做出決策，或者模型提前就把可能的方案做好，這有助于企業(yè)的決策效率。

Excel規(guī)劃求解模型的柔性可以通過Excel提供的“方案管理”功能實現。在進行規(guī)劃求解時，若經營環(huán)境、生成工藝等約束條件發(fā)生變化，將會出現不同的結果，通過方案管理將不同結果進行保存，使用方案管理器將保存的各種方案顯示、編輯和刪除。管理者可查看不同方案的結果，以便給決策提供依據。具體操作方法為：（1）在“規(guī)劃求解結果”對話框中，如圖4所示，選擇“保存方案”，打開“保存方案”對話框，輸入方案名稱，單擊“確定”保存方案；（2）若約束條件變化，只需改變圖1中約束條件的約束值（不用再重新添加約束條件），重新進行規(guī)劃求解，然后再保存該方案；（3）要查看方案，需要打開“方案管理器”對話框，可以進行顯示方案結果、添加新方案、刪除和編輯方案、查看方案摘要等操作。

（二）Excel規(guī)劃求解結果分析

一般線性規(guī)劃問題求解結果有四種情況：（1）唯一最優(yōu)解；（2）無窮多最優(yōu)解；（3）無界解；（4）無可行解。 無可行解說明數學模型中約束條件之間有矛盾，無界解則說明缺乏必要的約束條件，無界解和無可行解一般說明線性規(guī)劃問題的數學模型有問題。從經濟意義上看，這兩種情況在企業(yè)生產經營中都不會出現，因為正常情況下，經濟資源都能給企業(yè)帶來利益，當然，經濟資源也都是稀缺的，即利益不可能無窮大。Excel規(guī)劃求解結果若是無界解和無可行解的情況，Excel規(guī)劃求解結果對話框中都有明顯的顯示，據此結果去修改數學模型，然后再重新設計Excel規(guī)劃求解模型。

Excel不能直接判別規(guī)劃求解問題是唯一解還是無窮最優(yōu)解情況。若約束條件中不含整數約束，通過對偶問題規(guī)劃求解，再結合Excel的敏感性報告，可以判別線性規(guī)劃問題是無窮多最優(yōu)解還是唯一最優(yōu)解，該種方法應用時需要熟知單純行法原理和對偶問題理論，對使用者知識儲備要求比較高所以不宜推廣，在此不再闡述。還有一種比較簡單的方法來判別唯一解和無窮最優(yōu)解情況：在圖3中添加完了約束條件后，重復單擊【求解】，若每次結果不一樣則說明線性規(guī)劃問題是無窮多最優(yōu)解。若約束條件中包含整數約束，并且最優(yōu)解不唯一時，通過重復【求解】可以找到所有最優(yōu)方案。

四、結語

在有限的資源約束條件下，企業(yè)的經營決策方案都力爭做到最優(yōu)化，利用線性規(guī)劃模型進行最優(yōu)化決策便成了企業(yè)首選。在激烈的市場競爭中，決策的效率和科學性直接決定了經營的成敗，Excel作為最普遍的辦公軟件之一，已經被廣大工作者所熟知，其有助于提高企業(yè)決策的效率及準確性。Excel規(guī)劃求解能夠簡單、快捷地解決企業(yè)線性規(guī)劃問題，當經營環(huán)境、生產工藝等條件變化時，Excel求解模型也能迅速地做出反應，并把不同的方案保存下來，以便以后決策需要。