EXCEL在復雜存貨訂購批量決策中的應用

從亮

EXCEL在復雜存貨訂購批量決策中的應用

從亮

在企業采購的存貨陸續到達、供應商提供數量折扣的情況下，傳統通過手工計算來確定存貨的經濟訂購批量計算過程相當繁瑣。本文借助通用財務軟件EXCEL，利用其規劃求解功能，設計了相關的具體方案，并解決了存在數量折扣、陸續到貨、多品種同時采購的情況下，各種材料經濟訂貨批量的確定難題，改進了傳統存貨模型下的經濟訂貨批量確定方法，提高了企業存貨采購決策的效率。

陸續到貨；數量折扣；規劃求解；存貨訂購批量

企業存貨決策所要解決的主要問題是：企業應該怎樣采購存貨？這包含兩方面的內容，首先是應當訂購多少存貨；其次是應當在何時開始訂貨。進行有效的存貨訂購批量決策對于企業維持正常生產經營，合理控制資金占用水平，降低營運成本具有重要意義。本文主要解決第一個問題即企業應當采購多少存貨，主要利用excel的規劃求解功能，闡明存在數量折扣、陸續到貨的情況下，如何決定存貨的訂購批量。

一、存在數量折扣、陸續到貨條件下的基本模型

存貨的最優經濟批量（EOQ）是指企業選擇合理的采購批量與采購時間，使存貨相關總成本最低時的采購量，與存貨相關成本主要包括采購成本、儲存成本、訂貨成本和缺貨成本。基本模型只考慮了其中的儲存成本和訂貨成本，與現實情況大多不相符合。在這里在放寬基本模型的兩項假定：第一，企業采購的全部存貨沒有一次到位（存貨陸續到達）；第二，不同的采購數量有不同的采購單價（存在數量折扣）；同時假定不存在缺貨。由于供應商實行了數量折扣，陸續到貨條件下的決策模型中涉及的成本除了訂貨成本和儲存成本之外，采購成本也成了決策中的相關成本。

考慮數量折扣、陸續到貨等條件的經濟訂貨批量模型為：T=采購成本+訂貨成本+儲存成本=D×U× （1-S）+D/Q×K+Q/2×（1-d/P）×C。式中：U為采購單價，D為全年存貨需要量，Q為訂貨批量，S為數量折扣，K為每次訂貨成本，d為每日消耗量，P每日送貨量。決策目標是找到使總成本T最低時的采購批量。

二、存在數量折扣、陸續到貨條件下的規劃求解模型的建立

例：大華公司擬采購甲、乙、丙三種存貨，存貨將陸續到達。另外供應商提出的數量折扣的條件是：甲材料訂貨數量大于或等于400千克，折扣2%；乙材料訂貨數量大于或等于450千克，折扣4%；丙材料訂貨數量大于或等于500千克，折扣3%；丁材料訂貨數量大于或等于500千克，折扣6%；企業存貨基本數據情況見下表：

要求：確定甲材料、乙材料、丙材料、丁材料的最佳訂購批量。

存貨名稱　甲材料　乙材料　丙材料　丁材料材料全年需要量D　18000　20000　30000　25000一次訂貨成本K　20　25　30　40單位儲存成本C　2　3　4　3每日送貨量P　100　200　300　250每日耗用量d　20　30　40　25數量折扣S　0.02　0.04　0.03　0.06單價U　10　20　30　25

首先，設定目標函數。設四種材料相關的總成本為T，Di為某種材料的全年總需要量，Qi為某種材料的訂貨量，Ki為某種材料的一次訂貨成本，Ui為某種材料的單價，Si為某種材料的數量折扣，di為某種材料的每日消耗量，P每種材料的每日送貨量。則目標函數總成本：T=∑［Di×Ui×（1-Si）+Di/Qi×Ki+Qi/2×（1-di/Pi）×Ci］，（i=1，2，3，4）。

其次，明確約束條件。條件一：甲材料訂貨數量大于或等于400千克，Q1＞=400千克；條件二：乙材料訂貨數量大于或等于450千克，Q2＞=450千克；條件三：丙材料訂貨數量大于或等于500千克，Q3＞=500千克；條件四：丙材料訂貨數量大于或等于500千克，Q4＞=500千克。

最后，求最優解。這時決策就轉化為：去找到相關總成本T最低時的Qi（i=1，2，3，4）。

三、存貨訂購批量決策在EXCEL中的實現

下面借助于excel2003的規劃求解功能，舉例說明該模型下多種產品存貨訂購批量的決策實現剛過程。

1.基本數據區的設計

把上表中的四種材料基本數據輸入到B5：E11單元格中，見下圖。

2.最優訂貨批量規劃求解分析區域的設計

單元格A16：E24是四種材料的規劃求解分析區域，A24：B24為目標函數的設計區域。采購成本、儲存成本、訂貨成本的公式見下圖；目標相關總成本為B24單元格，公式為：=B5*B11*（1-B10）+B5/B17*B6+0.5*（B17-B17/ B8*B9）*B7+C5*C11*（1-C10）+C5/C17*C6+0.5*（C17-C17/ C8*C9）*C7+D5*D11*（1-D10）+D5/D17*D6+0.5*（D17-D17/ D 8*D9）*D7+E5*E11*（1-E10）+E5/E17*E6+0.5*（E17-E17/E8*E9）*E7。

3.規劃求解參數的設計

打開EXCEL2003的規劃求解功能，設置相關的規劃求解參數。可變單元格為B5：E5，分別是甲、乙、丙、丁四種材料的訂購批量，初始值全部假定為1千克；目標單元格為B24，表示相關總成本T。約束條件包括，第一是數量折 扣 的約 束：B17＞=400，C17＞=450，D17＞=500，E17＞=500；第二是實際條件的約束，讓材料采購數量為整數。具體設計見下圖。

4.最優訂貨批量的求解

規劃求解參數設定完畢后，用鼠標單擊上圖中的求解按鈕，便可得到最終結果，見下圖。

由上圖可知，甲材料的最優經濟訂貨批量為671千克，乙材料的最優經濟訂貨批量為626千克，丙材料的最優經濟訂貨批量為721千克，丁材料的最優經濟訂貨批量為861千克，此時相關的最低總成本T是202.8392萬元。

［1］王化成主編，財務管理學（第6版）［M］，中國人民大學出版社，2012.6.

（作者單位：黃淮學院）