EXCEL在復(fù)雜存貨訂購批量決策中的應(yīng)用

從亮

EXCEL在復(fù)雜存貨訂購批量決策中的應(yīng)用

從亮

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

陸續(xù)到貨；數(shù)量折扣；規(guī)劃求解；存貨訂購批量

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

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

存貨的最優(yōu)經(jīng)濟(jì)批量（EOQ）是指企業(yè)選擇合理的采購批量與采購時(shí)間，使存貨相關(guān)總成本最低時(shí)的采購量，與存貨相關(guān)成本主要包括采購成本、儲存成本、訂貨成本和缺貨成本?；灸Ｐ椭豢紤]了其中的儲存成本和訂貨成本，與現(xiàn)實(shí)情況大多不相符合。在這里在放寬基本模型的兩項(xiàng)假定：第一，企業(yè)采購的全部存貨沒有一次到位（存貨陸續(xù)到達(dá)）；第二，不同的采購數(shù)量有不同的采購單價(jià)（存在數(shù)量折扣）；同時(shí)假定不存在缺貨。由于供應(yīng)商實(shí)行了數(shù)量折扣，陸續(xù)到貨條件下的決策模型中涉及的成本除了訂貨成本和儲存成本之外，采購成本也成了決策中的相關(guān)成本。

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

二、存在數(shù)量折扣、陸續(xù)到貨條件下的規(guī)劃求解模型的建立

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

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

存貨名稱　甲材料　乙材料　丙材料　丁材料材料全年需要量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數(shù)量折扣S　0.02　0.04　0.03　0.06單價(jià)U　10　20　30　25

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

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

最后，求最優(yōu)解。這時(shí)決策就轉(zhuǎn)化為：去找到相關(guān)總成本T最低時(shí)的Qi（i=1，2，3，4）。

三、存貨訂購批量決策在EXCEL中的實(shí)現(xiàn)

下面借助于excel2003的規(guī)劃求解功能，舉例說明該模型下多種產(chǎn)品存貨訂購批量的決策實(shí)現(xiàn)剛過程。

1.基本數(shù)據(jù)區(qū)的設(shè)計(jì)

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

2.最優(yōu)訂貨批量規(guī)劃求解分析區(qū)域的設(shè)計(jì)

單元格A16：E24是四種材料的規(guī)劃求解分析區(qū)域，A24：B24為目標(biāo)函數(shù)的設(shè)計(jì)區(qū)域。采購成本、儲存成本、訂貨成本的公式見下圖；目標(biāo)相關(guān)總成本為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.規(guī)劃求解參數(shù)的設(shè)計(jì)

打開EXCEL2003的規(guī)劃求解功能，設(shè)置相關(guān)的規(guī)劃求解參數(shù)?？勺儐卧駷锽5：E5，分別是甲、乙、丙、丁四種材料的訂購批量，初始值全部假定為1千克；目標(biāo)單元格為B24，表示相關(guān)總成本T。約束條件包括，第一是數(shù)量折 扣 的約 束：B17＞=400，C17＞=450，D17＞=500，E17＞=500；第二是實(shí)際條件的約束，讓材料采購數(shù)量為整數(shù)。具體設(shè)計(jì)見下圖。

4.最優(yōu)訂貨批量的求解

規(guī)劃求解參數(shù)設(shè)定完畢后，用鼠標(biāo)單擊上圖中的求解按鈕，便可得到最終結(jié)果，見下圖。

由上圖可知，甲材料的最優(yōu)經(jīng)濟(jì)訂貨批量為671千克，乙材料的最優(yōu)經(jīng)濟(jì)訂貨批量為626千克，丙材料的最優(yōu)經(jīng)濟(jì)訂貨批量為721千克，丁材料的最優(yōu)經(jīng)濟(jì)訂貨批量為861千克，此時(shí)相關(guān)的最低總成本T是202.8392萬元。

［1］王化成主編，財(cái)務(wù)管理學(xué)（第6版）［M］，中國人民大學(xué)出版社，2012.6.

（作者單位：黃淮學(xué)院）