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

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

3、工作。計算機的出現，為會計人員帶來 了一項非常有用的工具，使一些大型復雜的實際問題的解決成為現實。微軟公司的電子表格軟件Excel提供的規劃求解加載宏，“加載宏”是為EXCEL提供附 加命令和函數的VBA模塊。可以非常方便的完成線性規劃的求解任務（事實上該工具也可以 處理其他規劃問題）。貳、提出問題企業在資源合理配置，產品品種決策等方面經常要使用線性規劃法來求解，在產品生產 安排的決策中，如果企業生產的產品品種有兩種以上，且產品之間在資源需求、加工能力、 市場需要等方面有一定的相互影響關系，就可以使用線性規劃法進行決策，獲得最優的產品 生產數量，取得最大的邊際貢獻。企業在運用線性規劃法進行決策

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

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

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

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

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

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