□（鄭州升達(dá)經(jīng)貿(mào)管理學(xué)院會計(jì)系 河南鄭州451191）

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

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

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

（一）問題的提出

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

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

（二）建立數(shù)學(xué)模型

設(shè) x1、x2、x3分別表示計(jì)劃期內(nèi) A、B、C三種產(chǎn)品的產(chǎn)量，用z表示利潤，則上述計(jì)劃問題的數(shù)學(xué)模型可以表示為：

其中：x1、x2、x3 分別為產(chǎn)品 A、B、C 的產(chǎn)銷量，且為整數(shù)。

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

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

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

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

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

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

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

圖1 利潤最大化模型

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

圖2 添加工時(shí)約束條件

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

圖3 約束條件

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

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

圖5 利潤最大化模型計(jì)算結(jié)果

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

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

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

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

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

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

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

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

四、結(jié)語

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