Excel-財務(wù)應(yīng)用--經(jīng)濟價值最優(yōu)的生產(chǎn)決策問題

1、Excel 財務(wù)應(yīng)用 經(jīng)濟價值最優(yōu)的生產(chǎn)決策問題人們在各種生產(chǎn)活動和經(jīng)濟活動中，總是希望最大限度的利用資源和設(shè)備，完成最佳的任務(wù)；或者在統(tǒng)籌安排下，在最短的時間內(nèi)，使用最少的人力、物力和投資，即利用最小的資源消耗，完成規(guī)定的任務(wù)。因此，就需要管理者做出相應(yīng)的生產(chǎn)決策。生產(chǎn)決策是根據(jù)企業(yè)的經(jīng)營戰(zhàn)略方案和企業(yè)內(nèi)外經(jīng)營環(huán)境的狀況，來確定企業(yè)的生產(chǎn)方向、生產(chǎn)目標、生產(chǎn)方針以及生產(chǎn)方案的過程或職能。下面就利用線性規(guī)劃，解決企業(yè)中最優(yōu)經(jīng)濟價值的生產(chǎn)決策問題。在使用線性規(guī)劃做出分析決策之前，需要先加載規(guī)劃求解宏，以便日后使用。依次單擊Office按鈕和【Excel選項】按鈕，在彈出的對話框中，選擇【加載項】

2、選項卡，并單擊【轉(zhuǎn)到】按鈕，即可彈出【加載宏】對話框。然后，在該對話框中，啟用【規(guī)劃求解】復(fù)選框，如圖9-1所示。啟用圖9-1 加載規(guī)劃求解宏提 示在【加載宏】對話框中單擊【確定】按鈕之后，系統(tǒng)將自動彈出安裝過程對話框，安裝完成之后重新啟動計算機，即可在【數(shù)據(jù)】選項卡的分析組中，添加【規(guī)劃求解】按鈕。例如，某鋼廠生產(chǎn)螺紋鋼和帶鋼兩種類型的鋼材，已知生產(chǎn)一噸螺紋鋼需要A種原材料2噸，B種原材料6噸；生產(chǎn)一噸帶鋼需要A種原材料5噸，B種原材料3噸，而工廠要求所用的A、B兩種原料分別不能超過10噸和18噸。每噸螺紋鋼可以獲得4萬元的利潤，售出每噸帶鋼可以獲得3萬元的利潤，要求計算生產(chǎn)螺紋鋼和帶鋼兩種

3、產(chǎn)品各多少噸時，產(chǎn)值最大？在利用線性規(guī)劃模型求解時，任何一個線性規(guī)劃模型都包含有表示要做決策的決策變量、表述這些決策變量的可行值、現(xiàn)值的約束條件和表示問題完全績效測度的目標函數(shù)。因此，可以將問題中的已知條件、約束值以及所求變量都輸入到Excel工作表中，如圖9-2所示。輸入條件圖9-2 輸入已知條件、約束值和變量上圖所表述內(nèi)容是對該問題在Excel工作表中的數(shù)學描述。其中，生產(chǎn)每噸螺紋鋼和帶鋼所需要的兩種原材料和銷售產(chǎn)品產(chǎn)生的利潤，均屬于已知條件；工廠要求使用的兩種原材料噸數(shù)為約束條件；而綠色單元格區(qū)域則表示問題中需要計算的兩個變量值，默認情況下，其值為0。規(guī)劃模型的創(chuàng)建完成之后，需要計算原材

4、料A和原材料B的合計值。選擇原材料A合計值所對應(yīng)的單元格，即E4單元格，單擊【函數(shù)庫】組中的【數(shù)學和三角函數(shù)】下拉按鈕，在其列表中選擇SUMPRODUCT函數(shù)。然后，在彈出的【函數(shù)參數(shù)】對話框中，分別設(shè)置參數(shù)Array1和Array2的數(shù)據(jù)范圍為C4:D4和C7:D7，如圖9-3所示。單擊【確定】按鈕，即可在原材料A合計值所對應(yīng)的單元格中，計算出其結(jié)果為0，如圖9-4所示。輸入設(shè)置選擇 圖9-3 設(shè)置函數(shù)參數(shù) 圖9-4 原材料A的合計值技 巧另外，選擇原材料A所對應(yīng)的單元格后，也可以直接在【編輯欄】中輸入“=SUMPRODUCT(C4:D4,C7:D7)”公式，得出計算結(jié)果。選擇原材料B合計值

5、所對應(yīng)的單元格，即E5單元格，再次選擇SUMPRODUCT函數(shù)，并設(shè)置函數(shù)參數(shù)，或者在【編輯欄】中，輸入“=SUMPRODUCT(C5:D5,C7:D7)”公式，按Enter鍵，即可計算原材料B的合計值，如圖9-5所示。輸入圖9-5 原材料B的合計值在計算原材料A和原材料B的合計值時，由于其計算結(jié)果與產(chǎn)量值相關(guān)，而產(chǎn)量的默認值為0，因此，兩種原材料的合計值也為0。在計算兩種原材料的合計值時，利用了SUMPRODUCT函數(shù)。該函數(shù)可以在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。其語法格式為：SUMPRODUCT (array1, array2, array3,)其中，參數(shù)arra

6、y1, array2, array3,為2到255個數(shù)組，其相應(yīng)的元素需要進行相乘并求和。因此，計算兩種原材料的合計值時，其遵循的公式為：原材料合計值=每噸螺紋鋼使用的噸數(shù)螺紋鋼的產(chǎn)量每噸帶鋼使用的噸數(shù)帶鋼的產(chǎn)量。然后，需要確定目標函數(shù)值，選擇目標值所對應(yīng)的單元格，即G7單元格，在【編輯欄】中，輸入“=SUMPRODUCT(C6:D6,C7:D7)”公式，即可計算出目標函數(shù)值，如圖9-6所示。效果顯示輸入圖9-6 目標函數(shù)值提 示目標函數(shù)值是描述問題所求的目標函數(shù)的計算結(jié)果，在本例中，表示工廠的最大利潤。合計值和目標函數(shù)值計算完成之后，即可開始使用規(guī)劃求解宏對該問題進行計算。選擇最大利潤值所對

7、應(yīng)的單元格，即G7單元格，并選擇【數(shù)據(jù)】選項卡，單擊【分析】組中的【規(guī)劃求解】按鈕，在彈出的【規(guī)劃求解參數(shù)】對話框中，設(shè)置【可變單元格】的范圍為C7:D7，如圖9-7所示。然后，單擊【添加】按鈕，在彈出的【添加約束】對話框中，分別設(shè)置【單元格引用位置】和【約束值】的數(shù)據(jù)范圍，如圖9-8所示。添加約束設(shè)置單擊設(shè)置 圖9-7 設(shè)置可變單元格 圖9-8 添加約束在【規(guī)劃求解參數(shù)】對話框中，主要包含的各部分內(nèi)容以及按鈕的功能作用如下所示：l 設(shè)置目標單元格在【設(shè)置目標單元格】文本框中，輸入目標單元格的單元格引用（用于表示單元格在工作表上所處位置的坐標集）或名稱（代表單元格、單元格區(qū)域、公式或常量值的單

8、詞或字符串。）。目標單元格中必須包含公式。其中，在該欄中，還包含3個單選按鈕。若要使目標單元格的值盡可能大，請選擇【最大值】單選按鈕。若要使目標單元格的值盡可能小，請選擇【最小值】單選按鈕。要使目標單元格為確定值，請選擇【值為】單選按鈕，然后在文本框中輸入數(shù)值。l 可變單元格輸入每個可變單元格的名稱或引用，用逗號分隔不相鄰的引用，用冒號分隔相鄰的單元格?？勺儐卧癖仨氈苯踊蜷g接與目標單元格相關(guān)。最多可以指定200個可變單元格。另外，如果要使“規(guī)劃求解”根據(jù)目標單元格自動建議可變單元格，請單擊【推測】按鈕。l 約束在【約束】文本框中，輸入要應(yīng)用的約束條件，其中，約束條件是“規(guī)劃求解”中設(shè)置的限制

9、條件。可以將約束條件應(yīng)用于可變單元格、目標單元格或其他與目標單元格直接或間接相關(guān)的單元格。其中，在該欄的右側(cè)主要包含3個按鈕。若單擊【添加】按鈕，可以添加約束條件。單擊【更改】按鈕，可以更改約束條件。單擊【刪除】按鈕，可刪除約束條件。l 選項單擊該按鈕，彈出【規(guī)劃求解選項】對話框。在其中可加載或保存規(guī)劃求解模型，并對求解過程的高級功能進行控制。l 全部重設(shè)單擊該按鈕，即可清除規(guī)劃求解中的當前設(shè)置，將所有的設(shè)置恢復(fù)為初始值。提 示單擊【選項】上方的【關(guān)閉】按鈕，則可以關(guān)閉【規(guī)劃求解參數(shù)】對話框，此時不對問題進行求解，但保留通過【選項】、【添加】、【更改】或【刪除】按鈕所做的更改。在【規(guī)劃求解參數(shù)

10、】對話框中，單擊【選項】按鈕，彈出【規(guī)劃求解選項】對話框。在該對話框中，分別啟用【采用線性模型】和【假定非負】復(fù)選框，如圖9-9所示。啟用圖9-9 規(guī)劃求解選項的設(shè)置在【規(guī)劃求解選項】對話框中，包含多個文本框、復(fù)選框以及單選按鈕的設(shè)置，可以設(shè)置規(guī)劃求解過程的一些高級功能，每一選項都有默認設(shè)置，可以滿足大多數(shù)情況下的要求，其功能作用如下所示：l 最長運算時間該項用于限制求解過程中所花費的最長時間。在該文本框中，可以輸入的最大值為32,767（秒），默認值100（秒）。l 迭代次數(shù)該選項可以通過限制中間計算的次數(shù)來限制求解過程花費的時間。在該文本框中，可以輸入的最大值為32767（次），其默認值為

11、100（次）。l 精度該文本框可以輸入用于控制求解精度的數(shù)字，以確定約束條件單元格中的數(shù)值是否滿足目標值或上下限。需要注意的是，精度值必須表示為小數(shù)（0和1之間），輸入數(shù)字的小數(shù)位越多，精度越高，如0.0001比0.01的精度高。l 允許誤差在該文本框中，可以輸入滿足整數(shù)約束條件、且可以被接受的目標單元格求解結(jié)果，與真實的最佳結(jié)果之間的百分偏差。該選項只應(yīng)用于具有整數(shù)約束條件的問題，設(shè)置的允許誤差值越大，求解過程就越快。l 收斂度該文本框用于輸入收斂度數(shù)值。當最近五次迭代后，目標單元格中數(shù)值的相對差別小于【收斂度】文本框中的數(shù)值時，規(guī)劃求解便會停止運行。該選項只應(yīng)用于非線性規(guī)劃求解問題，并且必

12、須表示為小數(shù)（0和1之間）。輸入數(shù)字的小數(shù)位數(shù)越多，收斂度就越小，收斂度的值越小，得到結(jié)果所需的時間就越長。l 采用線性模型當模型中的所有關(guān)系都是線性的，并且希望解決線性優(yōu)化問題時，啟用該復(fù)選框可以加速求解進程。l 自動按比例縮放如果啟用該復(fù)選框，那么當輸入和輸出值量級相差很大時（例如，基于百萬美元的投資將利潤百分比最大化），可以自動按照比例縮放數(shù)值。l 假定非負啟用該復(fù)選框，則對于在【添加約束】對話框的【約束】框中沒有設(shè)置下限的所有可變單元格，規(guī)劃求解將假定其下限為0（零）。l 顯示迭代結(jié)果如果啟用該復(fù)選框，則每進行一次迭代后都將中斷規(guī)劃求解，并顯示當前的迭代結(jié)果。l 估計該欄可以指定在每個

13、一維搜索中用來得到基本變量初始估計值的逼近方案。其中，若選擇【正切函數(shù)】單選按鈕，可以使用正切向量線性外推；若選擇【二次方程】單選按鈕，則使用二次方程外推法，提高非線性規(guī)劃問題的計算精度。l 導數(shù)在該欄中，可以指定用于估計目標函數(shù)和約束函數(shù)的差分方案。其中，【向前差分】單選按鈕用于解決大多數(shù)約束條件數(shù)值變化相對緩慢的問題；而【中心差分】單選按鈕用于解決約束條件變化迅速，特別是接近限定值的問題。雖然此選項要求更多的計算，但在規(guī)劃求解不能返回有效解時也可能會有一定幫助。l 搜索該欄用于指定每次迭代使用的算法，以便確定搜索方向。其中，選擇【牛頓法】單選按鈕，可以使用準牛頓法迭代，其需要的內(nèi)存比共軛法多，但所需的迭代次數(shù)少；若選擇【共軛法】單選按鈕，則使用共軛法進行迭代，該方法比牛頓法需要的內(nèi)存少，但需要的迭代次數(shù)較多。l 裝入模型單擊【裝入模型】按鈕，即可彈出【裝入模型】對話框，用戶可以在該對話框中指定對要加載模型的引用。l 保存模型單擊該按鈕，即可彈出【保存模型】對話框，該對話框用于指定保存模型的位置。只有當需要在工作表上保存多個模型時，單擊該按鈕，系統(tǒng)將自動保存第一個模型。接下來，單擊【規(guī)劃求解選項】對話框中的【確定】按鈕，返回【規(guī)劃求解參數(shù)】對話框，并單擊【求解】按鈕，在彈出的【規(guī)劃求解結(jié)果】對話框中，選擇【保存規(guī)劃求解結(jié)果】單選按鈕，如