應(yīng)用Excel規(guī)劃求解進(jìn)行最優(yōu)化經(jīng)營(yíng)決策

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

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

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

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

（一）問(wèn)題的提出

某企業(yè)在計(jì)劃期內(nèi)要安排生產(chǎn)A、B、C三種產(chǎn)品，每種產(chǎn)品都必須經(jīng)過(guò)甲、乙、丙三個(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元，問(wèn)如何安排生產(chǎn)計(jì)劃使該企業(yè)獲利最多？

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

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

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

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

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

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

根據(jù)已知的資源約束和數(shù)學(xué)模型，建立Excel利潤(rùn)最大化模型，設(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)量、利潤(rù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單元格為可變單元格；利潤(rùn)E24單元格：=SUMPRODUCT(E20:G20,E21:G21-E22:G22)-E23。利潤(rùn)（E24單元格）函數(shù)說(shuō)明：利潤(rùn)等于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ù)組對(duì)應(yīng)元素相乘后再求和即可得出三種產(chǎn)品邊際貢獻(xiàn)之和，然后再減去固定成本（E23單元格）即可。

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

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

圖1 利潤(rùn)最大化模型

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

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

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

圖3 約束條件

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

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

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

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

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

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

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

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

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

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

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

四、結(jié)語(yǔ)

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