Ｅｘｃｅｌ在存貨管理決策中的應(yīng)用

[摘 要] 經(jīng)濟(jì)訂貨量法是現(xiàn)代庫(kù)存優(yōu)化的方法之一。本文以Excel為工具，分別就庫(kù)存管理決策基本的經(jīng)濟(jì)訂貨批量模型、擴(kuò)展的經(jīng)濟(jì)訂貨批量模型和有數(shù)量折扣不允許缺貨的經(jīng)濟(jì)訂貨模型進(jìn)行了分析設(shè)計(jì)。透過(guò)實(shí)例表明，這些模型簡(jiǎn)單、實(shí)用，可以作為存貨管理決策的有效工具。

[關(guān)鍵詞] 存貨管理；經(jīng)濟(jì)訂貨批量；訂儲(chǔ)費(fèi)用

[中圖分類號(hào)] F275;F270.7

[文獻(xiàn)標(biāo)識(shí)碼]A

[文章編號(hào)]1673-0194(2006)10-0041-03

一、引 言

存貨是企業(yè)在生產(chǎn)經(jīng)營(yíng)過(guò)程中，為銷售或者耗用而儲(chǔ)備的物資，包括原材料、在產(chǎn)品、產(chǎn)成品等。企業(yè)的存貨一般在流動(dòng)資產(chǎn)中占有較大的比重，存貨管理水平的高低會(huì)對(duì)企業(yè)的財(cái)務(wù)狀況和經(jīng)營(yíng)成果產(chǎn)生巨大的影響，因此，加強(qiáng)存貨管理具有重要意義。經(jīng)濟(jì)訂貨量法（Economic Order Quantity，EQQ）也稱經(jīng)濟(jì)批量法，是指企業(yè)每次訂貨的數(shù)量使得存貨費(fèi)用最低的方法，是現(xiàn)代庫(kù)存優(yōu)化方法之一。本文以Excel為工具，探討庫(kù)存管理決策的分析方法和模型。

二、基本的經(jīng)濟(jì)訂貨批量模型

存貨的功能是滿足生產(chǎn)經(jīng)營(yíng)的需要，而存貨必然發(fā)生相應(yīng)的成本。經(jīng)濟(jì)訂貨批量是存貨的相關(guān)總成本最低的一次訂貨批量。經(jīng)濟(jì)訂貨批量應(yīng)根據(jù)實(shí)際情況，分不同類型來(lái)確定?；镜慕?jīng)濟(jì)訂貨批量模型建立在以下的假定條件之上：

（1）訂購(gòu)的存貨瞬時(shí)到貨；

（2）不允許缺貨；

（3）全年的存貨需求是確定的；

（4）存貨的價(jià)格穩(wěn)定，沒(méi)有數(shù)量折扣；

（5）存貨的耗用比較均勻。

在上述假定條件下，存貨的相關(guān)成本包括以下兩項(xiàng)：

（1）訂貨費(fèi)用。訂貨費(fèi)用是指為組織進(jìn)貨所發(fā)生的各種費(fèi)用，包括采購(gòu)人員的差旅費(fèi)、通訊費(fèi)、運(yùn)輸費(fèi)、檢驗(yàn)費(fèi)等。這些費(fèi)用一般與訂貨的次數(shù)有關(guān)。在存貨的全年需求量一定的情況下，一次訂貨量越多，全年的訂貨次數(shù)越少，訂貨費(fèi)用越低。

（2）存儲(chǔ)費(fèi)用。存儲(chǔ)費(fèi)用是指企業(yè)為持有存貨而發(fā)生的費(fèi)用，包括存貨資金占用費(fèi)用或機(jī)會(huì)成本、倉(cāng)儲(chǔ)費(fèi)用、存貨保險(xiǎn)費(fèi)用等。這些費(fèi)用一般與平均存貨水平的高低成正比。在存貨的全年需求量一定的情況下，一次訂貨量越多，全年的平均存貨水平越高，存儲(chǔ)費(fèi)用越高。

基本的經(jīng)濟(jì)訂貨批量有關(guān)的計(jì)算公式如下：

其中：

Q*：經(jīng)濟(jì)訂貨批量

R：一定時(shí)期存貨的需求量

S：一次采購(gòu)費(fèi)用

C：存貨單價(jià)

K：存貨的存儲(chǔ)費(fèi)率，CK為單位存儲(chǔ)費(fèi)用

例如，某企業(yè)全年需要甲材料3 200千克，每訂購(gòu)一次的訂貨成本為200元，每千克材料的年存儲(chǔ)成本為2元。當(dāng)一次訂貨批量分別為200、400、600、800、1 000、

1 200、1 400千克時(shí)，試計(jì)算其存貨成本和經(jīng)濟(jì)訂貨批量指標(biāo)。

（1）在單元格B8中輸入公式“＝$B$2/B7*$B$3”，計(jì)算一次訂貨量200千克時(shí)的年訂貨成本。

（2）在單元格B9中輸入公式“＝B7/2*$B$4”，計(jì)算一次訂貨量200千克時(shí)的年存儲(chǔ)成本。

（3）在單元格B10中輸入公式“＝B8＋B9”，計(jì)算一次訂貨量200千克時(shí)的年訂貨存儲(chǔ)總成本。

（4）選取單元格區(qū)域B8:B10，采用填充復(fù)制的方法一直向右復(fù)制到H8:H10，得到其他采購(gòu)批量之下的年成本。

（5）選取單元格區(qū)域A7:H10，單擊工具欄的【圖表向?qū)А堪粹o，在打開(kāi)的【圖表類型】對(duì)話框中選擇【XY散點(diǎn)圖】，并依次按有關(guān)的提示逐步操作，即可得到訂貨批量與年成本的關(guān)系圖。

（6）在單元格B12中輸入公式“＝SQRT(2*B2*B3/B4)”，計(jì)算經(jīng)濟(jì)訂貨批量。

（7）在單元格B13中輸入公式“＝B2/B12”，計(jì)算年最優(yōu)訂貨次數(shù)。

(8)在單元格B14中輸入公式“＝SQRT(2*B2*B3*B4)”，計(jì)算年最低訂儲(chǔ)總成本。

由計(jì)算結(jié)果可知，當(dāng)年訂貨成本與年存儲(chǔ)成本相等時(shí)，全年的訂貨總成本最低，這種情況對(duì)應(yīng)的訂貨批量為經(jīng)濟(jì)訂貨批量。甲材料的經(jīng)濟(jì)訂貨批量是800千克，年最優(yōu)訂貨次數(shù)為4次，年最低訂儲(chǔ)總成本為1 600元。

三、擴(kuò)展的經(jīng)濟(jì)訂貨批量模型

當(dāng)基本的經(jīng)濟(jì)訂貨批量模型的某些假定條件改變以后，即可得到擴(kuò)展的經(jīng)濟(jì)訂貨批量模型。擴(kuò)展的經(jīng)濟(jì)訂貨批量模型主要有以下3種形式。

（一）存貨陸續(xù)供應(yīng)和耗用且不允許缺貨的經(jīng)濟(jì)訂貨批量模型

在訂購(gòu)的存貨不能瞬時(shí)到貨而是陸續(xù)供應(yīng)、且邊送邊用的情況下，經(jīng)濟(jì)訂貨批量有關(guān)的計(jì)算公式如下：

其中，g為送貨期內(nèi)每日平均送貨量，d為每日平均消耗量。

（二）存貨瞬時(shí)到貨且允許缺貨的經(jīng)濟(jì)訂貨批量模型

在訂購(gòu)的存貨瞬時(shí)到達(dá)但允許缺貨的情況下，持有存貨的相關(guān)總費(fèi)用包括了訂貨費(fèi)用、儲(chǔ)存費(fèi)用和缺貨費(fèi)用，經(jīng)濟(jì)訂貨批量有關(guān)的計(jì)算公式如下：

例如，某企業(yè)需要的幾種存貨具有不同的要求，可能遇到幾種不同的情況，如可能缺貨、存貨陸續(xù)供應(yīng)和陸續(xù)耗用等。下面建立一般情況下通用的經(jīng)濟(jì)訂貨批量計(jì)算模型。

（1）設(shè)計(jì)存貨是否陸續(xù)供應(yīng)和耗用的控件以及是否允許缺貨的控件。其中是否陸續(xù)供應(yīng)和耗用的控件的【設(shè)計(jì)控件格式】中【數(shù)據(jù)源區(qū)域】為“$D$4:$D$5”，【單元格連接】為“B10”，【下拉顯示項(xiàng)數(shù)】為2，而在單元格D4中填入“瞬時(shí)到貨”，在單元格D5中填入“陸續(xù)到貨”；是否允許缺貨的控件的【設(shè)計(jì)控件格式】中【數(shù)據(jù)源區(qū)域】為“$D$7:$D$8”，【單元格鏈接】為“B11”，【下拉顯示項(xiàng)數(shù)】為2，主單元格D7中填入“不允許缺貨”，在單元格D8中填入“允許缺貨”。

（2）在單元格B13中輸入經(jīng)濟(jì)訂貨批量計(jì)算公式“＝IF(AND(B10=1，B11=1)，SQRT(2*B4*B5/B8)，IF(AND(B10=1，B11=2)，SQRT(2*B4*B5/B8*(B9+B8)/B9)，IF(AND(B10=2，B11=1)，SQRT(2*B4*B5/B8*B6(B6-B7))，IF(AND(B10=2，B11=2)，SQRT(2*B4*B5/B8*B6/(B6-B7)*(B9+B8)/B9)))))”。

（3）在單元格B14中輸入年最佳訂貨次數(shù)計(jì)算公式“＝INT(B4/B13+0.5)”。

（4）在單元格B15中輸入年最低總訂儲(chǔ)費(fèi)用計(jì)算公式“＝IF(AND(B10=1，B11=1)，SQRT(2*B4*B5*B8)，IF(AND(B10=1，B11=2)，SQRT(2*B4*B5*B8*B9/(B9+B8))，IF(AND(B10=2，B11=1)，SQRT(2*B4*B5*B8*(1-B7/B6))，IF(AND(B10=2，B11=2)，SQRT(2*B4*B5*B8*(1-B7/B6)*B9/(B9+B8))))))”

這樣通用的經(jīng)濟(jì)訂貨批量計(jì)算模型建立完畢。

例如，某企業(yè)對(duì)甲零件的年需要量為6 000件，每次訂貨費(fèi)用為250元，單位保管費(fèi)用15元/年，若允許缺貨，每缺貨1件的年成本為10元。零件每日到貨量25件，零件每日消耗量20件。則：

（1）假設(shè)零件陸續(xù)供應(yīng)和耗用，且允許缺貨，則計(jì)算出經(jīng)濟(jì)訂貨批量為1 581件，年訂貨次數(shù)4次，最低訂儲(chǔ)費(fèi)用1 397.37元。

（2）若不允許缺貨，但零件仍為陸續(xù)到貨和消耗，則經(jīng)濟(jì)訂貨批量為1 000件，年訂貨次數(shù)6次，最低訂儲(chǔ)費(fèi)用3 000元。

（3）若不允許缺貨，但零件為瞬時(shí)到達(dá)，則經(jīng)濟(jì)訂貨批量為447件，年訂貨次數(shù)13次，最低訂儲(chǔ)費(fèi)用6 708.20元。

（4）若允許缺貨，且零件為瞬時(shí)到達(dá)，則經(jīng)濟(jì)訂貨批量為707件，年訂貨次數(shù)8次，最低訂儲(chǔ)費(fèi)用4 242.64元。

四、有數(shù)量折扣，不允許缺貨的經(jīng)濟(jì)訂貨批量模型

以上的經(jīng)濟(jì)訂貨批量都是假定采購(gòu)數(shù)量不論多少，價(jià)格是一樣的。如果銷售企業(yè)為了鼓勵(lì)客戶購(gòu)買更多的商品，采用“采購(gòu)數(shù)量增加，可以享受折扣”這樣的銷售政策，那么客戶一次購(gòu)買量越多，所獲得的價(jià)格優(yōu)惠往往越大。因此，存貨的相關(guān)總費(fèi)用不僅包括訂貨費(fèi)用和儲(chǔ)存費(fèi)用，還包括存貨的采購(gòu)費(fèi)用。計(jì)算經(jīng)濟(jì)訂貨批量的基本思路是按打折后的價(jià)格計(jì)算，如果計(jì)算出的經(jīng)濟(jì)批量大于折扣點(diǎn)，則此經(jīng)濟(jì)批量即為最優(yōu)解。反之，必須比較以經(jīng)濟(jì)批量進(jìn)行采購(gòu)和以折扣點(diǎn)進(jìn)行采購(gòu)兩者的總費(fèi)用。具體步驟為：

（1）分別計(jì)算不同折扣價(jià)格下的經(jīng)濟(jì)訂貨批量。

（2）判斷這些經(jīng)濟(jì)訂貨批量是否有效（計(jì)算出的某經(jīng)濟(jì)訂貨批量是否與該折扣區(qū)間的價(jià)格相符）。

（3）計(jì)算有效的經(jīng)濟(jì)訂貨批量下的總存貨費(fèi)用，并同時(shí)計(jì)算不同折扣起點(diǎn)批量下的總存貨費(fèi)用。

（4）在這些總存貨費(fèi)用中找出最低費(fèi)用對(duì)應(yīng)的經(jīng)濟(jì)訂貨批量或折扣起點(diǎn)批量。

（5）比較經(jīng)濟(jì)訂貨批量或折扣起點(diǎn)批量與需求量的關(guān)系，若小于需求量，則該經(jīng)濟(jì)訂貨批量或折扣起點(diǎn)批量就是最優(yōu)解，否則，需求量是最優(yōu)解。

例如，某企業(yè)乙零件的年需要量為35 000件，每次訂購(gòu)費(fèi)用為30元，存儲(chǔ)費(fèi)率是零件單價(jià)的20%，該零件的供應(yīng)商為爭(zhēng)取業(yè)務(wù)，特制定臺(tái)階式折扣價(jià)格，以鼓勵(lì)買方每批多購(gòu)，其價(jià)格表如表1所示。

訂貨決策的具體步驟如下：

（1）在單元格C8、D8、E8、G8和H8中分別輸入以下公式：

單元格C8:＝SQRT(2*$B$3*($B$4)/B8/$B$5)

單元格D8:＝IF(AND(C8>=A8，C8

單元格E8:＝IF(D8=\"有效\"，B8*$B$3+$B$3*$B$4/C8+$B$5*B8*C8/2，\"無(wú)效\")

單元格F8:＝B8*$B$3+$B$3*$B$4/A8+$B$5*B8*A8/2

單元格G8:＝IF(D8=\"有效\"，MIN(E8:F8)，\"無(wú)意義\")

單元格H8:＝IF(G8=F8，A8，C8)

（2）選取單元格區(qū)域C8:H8，將其向下復(fù)制到單元格區(qū)域C13:H13。

（3）將單元格D13中的公式修改為：“＝IF(C13>=A13，\"有效\"，\"無(wú)效\")”。

（4）在單元格B14中輸入公式“＝INDEX(H8:H13，MATCH(MIN(G8:G13)，G8:G13，0)，0)”。

（5）在單元格B15中輸入公式“＝IF(B3>B14，B14，B3)”。

（6）在單元格E15中輸入公式“＝B3/B15”。

由此可見(jiàn)，以經(jīng)濟(jì)批量8 101件訂貨時(shí)的存貨總費(fèi)用最低，故企業(yè)的經(jīng)濟(jì)訂貨批量為8 101件，年訂貨次數(shù)4次。

五、結(jié) 論

由此可以看出，利用Excel強(qiáng)大的功能可以設(shè)計(jì)出日常庫(kù)存管理決策中所需要的各種類型的數(shù)學(xué)模型。這對(duì)加強(qiáng)庫(kù)存管理，提高物資供應(yīng)的經(jīng)濟(jì)效益具有重要的意義。本文從基本的經(jīng)濟(jì)訂貨批量模型出發(fā)，設(shè)計(jì)的擴(kuò)展的經(jīng)濟(jì)訂購(gòu)批量模型和有數(shù)量折扣、不允許缺貨情況下經(jīng)濟(jì)訂貨批量模型有一定的實(shí)際應(yīng)用參考價(jià)值。

主要參考文獻(xiàn)

[1] 王建成.管理會(huì)計(jì)學(xué)[M].北京：中國(guó)礦業(yè)大學(xué)出版社，2001.

[2] 張瑞君.計(jì)算機(jī)財(cái)務(wù)管理[M].北京：中國(guó)人民大學(xué)出版社，1996.

[3] 唐五湘，程桂枝.Excel在管理決策中的應(yīng)用[M].北京：電子工業(yè)出版社，2001.