Ｅｘｃｅｌ在企業生產與庫存決策中的應用

［摘 要］ Excel電子表格軟件以其靈活、易用、方便的特點，在企業管理中發揮著重要作用。在生產與庫存決策中應用其規劃求解的方法，快速建立求解模型，可以使企業管理者減輕勞動強度，并得到及時、準確的企業決策信息。

［關鍵詞］ Excel；生產；庫存；規劃求解

［中圖分類號］F232［文獻標識碼］A［文章編號］1673-0194（2007）04-0005-03

在信息社會中，利用方便的計算機軟件，來解決企業生產、管理中的難題已成為一種趨勢和必然。通過使用簡單的Excel電子表格軟件，就可以將手工中復雜的數學計算轉為簡單的對單元格公式的確定，即：用計算機為企業管理建模。利用Excel 2000提供的規劃求解功能可以解決作業研究的多種問題，諸如線性規劃、運輸問題、人事安排等，只要與生產、制造、分配、財務、工程等有關的求最大利潤、最小費用等問題均可使用規劃求解法找到答案。在此將以企業管理中的“生產—庫存”模型為依據，闡明如何將Excel更好地應用于企業管理之中。

設某廠計劃全年生產某種產品，其4個月的訂貨量分別為600件，700件，500件和1 200件。已知生產該產品的生產費用與產品件數的平方成正比，其比例系數為0.005，廠內有倉庫可以存放未銷售掉的產品，其存儲費為每件每月1元。求該廠應如何確定最優的生產計劃，使企業能夠按時完成訂貨任務并且總的生產費用與庫存費用最低。

在手工計算企業的“生產—庫存”最優決策中，可以運用管理控制中的動態規劃法來解決，即先建立管理系統的狀態方程，寫出它的目標函數，然后應用動態規劃的基本方程遞推求解。在解題過程中，列出相鄰兩步目標函數的最優值之間的遞推關系式，但是在計算過程中其遞推數據復雜而且數據量大，比較容易犯錯誤，從而得不到正確的結論。

如果考慮用計算機建立相應的模型，則其處理過程如下。

1 整理模型所需數據

（1） 據題意可知此模型的決策變量是各個月的最優生產量。

（2） 各月庫存量是與期初數量和本期生產、需求數量相關的變量。

（3） 本問題的目標函數是生產與庫存的總費用最小。

（4） 本問題的約束條件是：各個月的可供量應大于或等于各月的需求量。

2 用Excel建立模型

（1） 錄入初始數據

首先設計表格單元內容，將上例中已有的可知條件錄入表格單元中（見圖1）。

（2） 錄入公式

將事先設計的每個計算公式、約束條件公式錄入到各個單元格中。在本例題中，應該在B7單元格中錄入最小成本公式：0.005*(B4*B4+C4*C4+D4*D4+E4*E4)+SUM(B5:E5)，在B9~E9中錄入各約束條件公式，在全部公式及數據錄入完畢之后，形成的模型畫面如圖2所示。

在Excel中，一般只顯示各個單元格的內容，如果想要顯示各個單元格中的公式，則可以點擊“工具”菜單選項，在菜單項中選取“選項”，并在“視圖”選項卡中點擊“公式”復選框，則返回Excel主畫面時，畫面則顯示如圖2所示的各個單元格公式。

這里需要注意的問題：一是公式的位置，必須與要計算的指標在同一行；二是公式的寫法，如果引用其他單元格的資料要用該單元格的地址來表示，至于用絕對單元格還是用相對單元格則視需要而定，一般用相對單元格即可。

3 求最優解

點擊Excel中的“工具”菜單選項，在其下拉菜單中選取“規劃求解”，在該對話框中輸入目標單元格地址、可變單元格地址、約束條件參數地址。在本例題中，對約束條件參數的選擇，還要考慮所求變量的整數特征，因為產量件數不能為小數，所以在約束條件中添加一項指定單元格中的數據為整數（見圖3）。

選擇目標函數為“最小值”；單擊“選項”按鈕，在“選項”對話框中，選擇“假定非負”；最后單擊“求解按鈕”，即可求出最優解。其總體設置如圖4所示：

其最優解為每月生產量為600，700，800，900，最小成本為11 800，如圖5所示。

4 模型的應用

在上述模型建立以后，如果企業中的數據發生變化，則企業管理人員可以在此模型的基礎上，修改相應的數據，可以得到其對應的最優解，從而避免了手工計算的復雜、易出錯的缺點。在本例中，可以改變的參數有企業每個期間的訂貨量、生產函數與生產費用的關系式、庫存費用與庫存量的關系式。如：當企業4個月的訂貨量分別為200，500，600，100時，則最優生產計劃應為每個月分別生產333、433、534和100件，4個月的總生產與庫存費用最低為3 166.67（見圖6）。

當然，在條件發生重大變化時，可以考慮重新設計一個模型。

5 模型的保護措施

為了防止已輸入單元格中的公式被誤刪、誤改或人為篡改，為了防止數據的泄密和修改，同時為了工作表下次繼續使用，可以將表的格式，即各項目單元格和帶有公式的單元格設定密碼保護。如果擬不顯示公式，還可將公式予以隱藏。其做法是：首先將某些單元格排除在保護范圍之外，即：選定—格式—單元格—保護—對上述單元格選定為不“鎖定”；然后，再對擬保護的帶有公式的單元格設定為鎖定、隱藏，方法同上；最后，再對工作表進行保護，即：工具—保護—保護工作表—選項—選擇對話框的“內容”等項—輸入密碼—確定—再輸入密碼—確定。這樣就實現了保護。當下次再打開時，必須輸入密碼。當然，對整個文件還可以設定打開口令和修改權限口令保護。

主要參考文獻

[1] 丁以中，Jennifer S.Shang. 管理科學——運用Spreadsheet建模和求解[M]. 北京:清華大學出版社，2003.

[2] 鐘愛軍.Excel在邊際成本規劃中的應用[J]. 中國管理信息化，2006，(6).