EXCEL用于生產(chǎn)決策的線性規(guī)劃法

1、EXCEL用于生產(chǎn)決策的線性規(guī)劃法朱建國(guó)會(huì)計(jì)系上海理工大學(xué)商學(xué)院上海市復(fù)興中路1195號(hào)64337978e-mail: HYPERLINK mailto: 摘要：本文將介紹運(yùn)用微軟公司的Excel軟件，來解決成本會(huì)計(jì)中生產(chǎn)決策的線性規(guī)劃問題。企 業(yè)生產(chǎn)兩種或兩種以上產(chǎn)品時(shí)，如果這些產(chǎn)品在企業(yè)生產(chǎn)能力、經(jīng)濟(jì)資源利用上存在著相互影響 的關(guān)系，如何確定生產(chǎn)產(chǎn)量一般可以使用先行規(guī)劃方法予以解決。一般在解線性規(guī)劃問題時(shí)，多 采用單純形法，計(jì)算過程比較繁瑣。微軟公司的Excel軟件有專用的規(guī)劃求解加載宏可以利用， 本文就此問題予以探討。關(guān)鍵詞：Excel線性規(guī)劃 生產(chǎn)決策壹、引言線性規(guī)劃是運(yùn)籌學(xué)的一個(gè)重要

2、組成部分，專門用來對(duì)具有線性聯(lián)系的極值問題進(jìn)行求解 的一種現(xiàn)代數(shù)學(xué)方法。所謂“線性”，是指所有變動(dòng)因素的相互影響是直線關(guān)系。即約束條 件和目標(biāo)函數(shù)都是呈線性關(guān)系的。在成本管理會(huì)計(jì)中線性規(guī)劃研究的問題主要有以下兩類：對(duì)于已擁有一定數(shù)量的人力、物力和財(cái)力資源，研究如何合理使用，才能發(fā)揮它們的最 大經(jīng)濟(jì)效益。對(duì)于已確定的一項(xiàng)任務(wù)，研究如何統(tǒng)籌安排，才能以最少的人力、物力和財(cái)力資源去完 成該項(xiàng)任務(wù)。線性規(guī)劃及其解法一單純形法的出現(xiàn)，對(duì)運(yùn)籌學(xué)的發(fā)展起了重大的推動(dòng)作用。許多實(shí)際 問題都可以化成線性規(guī)劃來解決，而單純形法有是一個(gè)行之有效的算法，但是，單純形法的 手工計(jì)算對(duì)于會(huì)計(jì)人員來講，仍然是一項(xiàng)比較繁雜的

3、工作。計(jì)算機(jī)的出現(xiàn)，為會(huì)計(jì)人員帶來 了一項(xiàng)非常有用的工具，使一些大型復(fù)雜的實(shí)際問題的解決成為現(xiàn)實(shí)。微軟公司的電子表格軟件Excel提供的規(guī)劃求解加載宏，“加載宏”是為EXCEL提供附 加命令和函數(shù)的VBA模塊?？梢苑浅７奖愕耐瓿删€性規(guī)劃的求解任務(wù)（事實(shí)上該工具也可以 處理其他規(guī)劃問題）。貳、提出問題企業(yè)在資源合理配置，產(chǎn)品品種決策等方面經(jīng)常要使用線性規(guī)劃法來求解，在產(chǎn)品生產(chǎn) 安排的決策中，如果企業(yè)生產(chǎn)的產(chǎn)品品種有兩種以上，且產(chǎn)品之間在資源需求、加工能力、 市場(chǎng)需要等方面有一定的相互影響關(guān)系，就可以使用線性規(guī)劃法進(jìn)行決策，獲得最優(yōu)的產(chǎn)品 生產(chǎn)數(shù)量，取得最大的邊際貢獻(xiàn)。企業(yè)在運(yùn)用線性規(guī)劃法進(jìn)行決策

4、時(shí)，一般采用單純形法進(jìn)行求解，求解的步驟如下：確定目標(biāo)函數(shù)確定約束條件添加松弛變量列出單純形計(jì)算表經(jīng)過多次單純形迭代計(jì)算，得到計(jì)算結(jié)論。一般情況下，僅有兩個(gè)產(chǎn)品時(shí)，手工計(jì)算尚可，如果，產(chǎn)品較多的話，計(jì)算工作量很大， 只能求助于計(jì)算機(jī)。微軟公司的Excel電子表格軟件，專門提供了解決規(guī)劃求解的工具，可以很方便地完成 不論是幾種產(chǎn)品的生產(chǎn)合理安排線性規(guī)劃求解。Excel的“規(guī)劃求解”有很強(qiáng)的功能，可以對(duì)有多個(gè)變量的線性和非線性規(guī)劃問題進(jìn)行求 解，省去了人工編制程序和手工計(jì)算的麻煩。以下是一個(gè)說明如何使用Excel電子表格解決 規(guī)劃求解的工具，來實(shí)現(xiàn)合理安排生產(chǎn)的線性規(guī)劃求解方法。叁、合理安排生產(chǎn)線

5、性規(guī)劃求解某公司今年二季度擬生產(chǎn)甲、乙兩種產(chǎn)品，其售價(jià)、成本及約束條件的資料，如表一所 示：表一某公司基本情況單位名稱售價(jià)及成本資料約束條件銷售單價(jià)單位變動(dòng) 成本固定成本 總額原材料消耗 定額電力消耗 定額市場(chǎng)最大 銷售量甲85元55元9800 元2千克/件6千瓦/件無限制乙90元65元4千克/件4千瓦/件500件最高用量2400千克3600千瓦公司管理層希望根據(jù)已知的資料，確定甲、乙兩種產(chǎn)品的最優(yōu)生產(chǎn)組合。具體操作步驟如下：在進(jìn)行規(guī)劃求解時(shí)，首先要確認(rèn)在“工具”菜單中出現(xiàn)“規(guī)劃求解”命令，如果沒有則 需要安裝“規(guī)劃求解”加載宏，單擊“工具”菜單中，單擊加載宏命令，如果在“加載宏” 命令中，沒

6、有列出“規(guī)劃求解”項(xiàng)，單擊瀏覽，確定驅(qū)動(dòng)器、目錄、文件名或運(yùn)行加 載程序，然后進(jìn)行安裝。安裝了 “規(guī)劃求解”之后，在“工具”菜單下可能仍然找 不到“規(guī)劃求解”，此時(shí)您可以選擇“工具” * “加載宏”，在打開的“加載宏”對(duì)話 框中選中“規(guī)劃求解”復(fù)選框，確定后，就可以將“規(guī)劃求解”命令添加到“工具” 菜單欄中。如圖一所示：圖一加載宏在新建工作表中的sheetl工作簿中，按照?qǐng)D二的樣式，建立規(guī)劃求解工作表格， 將相關(guān)資料填入規(guī)劃求解工作表格。圖二 規(guī)劃求解工作表在B3、C3單元格填入初始產(chǎn)量，初始產(chǎn)量是規(guī)劃求解運(yùn)算時(shí)的初始數(shù)據(jù)，您可以 填入任何比較接近結(jié)論的預(yù)計(jì)數(shù)。在B6單元格中輸入公式=B4-B

7、5”確認(rèn)后，填充到C6單元格，即單價(jià)減單位變動(dòng) 成本，計(jì)算兩個(gè)產(chǎn)品的單位邊際貢獻(xiàn)。在D8單元格中輸入公式“=$B$3*B8+$C$3*C8”確認(rèn)后向下填充到D9單元格，計(jì)算 兩種產(chǎn)品預(yù)計(jì)對(duì)原材料和電力的消耗量。在D7單元格中輸入公式=B3*B6+C3*C6”計(jì)算兩種產(chǎn)品預(yù)計(jì)邊際貢獻(xiàn)總額。單擊“工具” / “規(guī)劃求解”菜單命令后，顯示如圖三所示的數(shù)據(jù)輸入窗口。圖三規(guī)劃求解參數(shù)在“設(shè)置目標(biāo)單元格”文本框中填入“$D$7”，即邊際貢獻(xiàn)總額；在“等于”單選框中選擇“最大值”；在“約束”文本框中逐條填加約束條件：“$D$8=$E$8” 原材料消耗必需小于限制量；“$D$9=$E$9” 電力消耗必需小于限

8、制量；“$C$3=0”甲產(chǎn)品必需大于或等于零；“$C$3=0”甲產(chǎn)品必需大于或等于零；單擊“求解”按鈕，系統(tǒng)便開始運(yùn)算，運(yùn)算完成后，提示是否要保存規(guī)劃求解的報(bào) 告和保存規(guī)劃求解的方案以備下次使用。圖四所示：圖四規(guī)劃求解結(jié)果10,完成后的工作表顯示企業(yè)生產(chǎn)400件甲產(chǎn)品和300乙產(chǎn)品的產(chǎn)量組合為最佳產(chǎn)量組 合。如圖五所示：圖五 規(guī)劃求解結(jié)論肆、幾個(gè)問題的討論Excel 在“規(guī)劃求解”中使用的算法是由 Leon lasdon， (University of Texas at Austin) 和Allan Waren(Cleveland State University)改進(jìn)的通用非線性規(guī)劃最佳化代

9、碼。當(dāng)“規(guī) 劃求解選項(xiàng)”對(duì)話框中的“采用線性模型”復(fù)選框被選定時(shí)，改變可變單元格的初始值不會(huì)影 響最終數(shù)值和求解時(shí)間。在“規(guī)劃求解”中可以指定500個(gè)約束條件，對(duì)每一個(gè)可變單元格來 說，分別有一個(gè)上、下限，還可另加100個(gè)附加約束條件，應(yīng)用約束條件的單元格不應(yīng)多于 1000 個(gè)。在運(yùn)行時(shí)應(yīng)注意以下幾個(gè)問題：若規(guī)劃求解過程中出現(xiàn)無解時(shí)，說明幾種產(chǎn)品的產(chǎn)量要達(dá)到約束條件是不可能的， 此時(shí)，應(yīng)該注意分析可能存在的原因。各項(xiàng)約束條件是否有沖突有時(shí)可能由于“規(guī)劃求解參數(shù)”選項(xiàng)中的“最長(zhǎng)運(yùn)算時(shí)間”、“迭代次數(shù)”、“精度”、“允 許誤差”等取值不當(dāng)。伍、小結(jié)利用Excel電子表格合理安排產(chǎn)品生產(chǎn)的產(chǎn)量是非常簡(jiǎn)便、快捷，表中數(shù)值的排列靈活 多變，可根據(jù)用戶要求自行設(shè)置，且約束條件不受限制(一般情況下)，除了在合理安排產(chǎn) 品的決策可以使用外，對(duì)于研究如何合