Excel在存貨管理決策中的應用

1、在存貨管理決策中的應用(2008-07-08 10:05:28) 轉(zhuǎn)載標簽： 存貨管理財務管理                                  作者：楊鑒淞        

2、                  中國管理信息化 年10期字數(shù)：2113摘 要 經(jīng)濟訂貨量法是現(xiàn)代庫存優(yōu)化的方法之一。本文以Excel為工具，分別就庫存管理決策基本的經(jīng)濟訂貨批量模型、擴展的經(jīng)濟訂貨批量模型和有數(shù)量折扣不允許缺貨的經(jīng)濟訂貨模型進行了分析設計。透過實例表明，這些模型簡單、實用，可以作為存貨管理決策的有效工具。關鍵詞 存貨管理；經(jīng)濟訂貨批量；訂儲費用中圖分類號 F275;F270.7文獻標識碼A文章編號1673-0194(

3、2006)10-0041-03 一、引 言存貨是企業(yè)在生產(chǎn)經(jīng)營過程中，為銷售或者耗用而儲備的物資，包括原材料、在產(chǎn)品、產(chǎn)成品等。企業(yè)的存貨一般在流動資產(chǎn)中占有較大的比重，存貨管理水平的高低會對企業(yè)的財務狀況和經(jīng)營成果產(chǎn)生巨大的影響，因此，加強存貨管理具有重要意義。經(jīng)濟訂貨量法（Economic Order Quantity，EQQ）也稱經(jīng)濟批量法，是指企業(yè)每次訂貨的數(shù)量使得存貨費用最低的方法，是現(xiàn)代庫存優(yōu)化方法之一。本文以Excel為工具，探討庫存管理決策的分析方法和模型。 二、基本的經(jīng)濟訂貨批量模型存貨的功能是滿足生產(chǎn)經(jīng)營的需要，而存貨必然發(fā)生相應的成本。經(jīng)濟訂貨批量是存貨的相關總

4、成本最低的一次訂貨批量。經(jīng)濟訂貨批量應根據(jù)實際情況，分不同類型來確定?；镜慕?jīng)濟訂貨批量模型建立在以下的假定條件之上：（1）訂購的存貨瞬時到貨；（2）不允許缺貨；（3）全年的存貨需求是確定的；（4）存貨的價格穩(wěn)定，沒有數(shù)量折扣；（5）存貨的耗用比較均勻。在上述假定條件下，存貨的相關成本包括以下兩項：（1）訂貨費用。訂貨費用是指為組織進貨所發(fā)生的各種費用，包括采購人員的差旅費、通訊費、運輸費、檢驗費等。這些費用一般與訂貨的次數(shù)有關。在存貨的全年需求量一定的情況下，一次訂貨量越多，全年的訂貨次數(shù)越少，訂貨費用越低。（2）存儲費用。存儲費用是指企業(yè)為持有存貨而發(fā)生的費用，包括存貨資金占用費用或機會成

5、本、倉儲費用、存貨保險費用等。這些費用一般與平均存貨水平的高低成正比。在存貨的全年需求量一定的情況下，一次訂貨量越多，全年的平均存貨水平越高，存儲費用越高?；镜慕?jīng)濟訂貨批量有關的計算公式如下：其中：Q*：經(jīng)濟訂貨批量R：一定時期存貨的需求量S：一次采購費用C：存貨單價K：存貨的存儲費率，CK為單位存儲費用例如，某企業(yè)全年需要甲材料3 200千克，每訂購一次的訂貨成本為200元，每千克材料的年存儲成本為2元。當一次訂貨批量分別為200、400、600、800、1 000、1 200、1 400千克時，試計算其存貨成本和經(jīng)濟訂貨批量指標。（1）在單元格B8中輸入公式“$B$2/B7*$B$3”，

6、計算一次訂貨量200千克時的年訂貨成本。（2）在單元格B9中輸入公式“B7/2*$B$4”，計算一次訂貨量200千克時的年存儲成本。（3）在單元格B10中輸入公式“B8B9”，計算一次訂貨量200千克時的年訂貨存儲總成本。（4）選取單元格區(qū)域B8:B10，采用填充復制的方法一直向右復制到H8:H10，得到其他采購批量之下的年成本。（5）選取單元格區(qū)域A7:H10，單擊工具欄的【圖表向?qū)А堪粹o，在打開的【圖表類型】對話框中選擇【XY散點圖】，并依次按有關的提示逐步操作，即可得到訂貨批量與年成本的關系圖。（6）在單元格B12中輸入公式“SQRT(2*B2*B3/B4)”，計算經(jīng)濟訂貨批量。（7）在

7、單元格B13中輸入公式“B2/B12”，計算年最優(yōu)訂貨次數(shù)。(8)在單元格B14中輸入公式“SQRT(2*B2*B3*B4)”，計算年最低訂儲總成本。由計算結果可知，當年訂貨成本與年存儲成本相等時，全年的訂貨總成本最低，這種情況對應的訂貨批量為經(jīng)濟訂貨批量。甲材料的經(jīng)濟訂貨批量是800千克，年最優(yōu)訂貨次數(shù)為4次，年最低訂儲總成本為1 600元。 三、擴展的經(jīng)濟訂貨批量模型當基本的經(jīng)濟訂貨批量模型的某些假定條件改變以后，即可得到擴展的經(jīng)濟訂貨批量模型。擴展的經(jīng)濟訂貨批量模型主要有以下3種形式。（一）存貨陸續(xù)供應和耗用且不允許缺貨的經(jīng)濟訂貨批量模型在訂購的存貨不能瞬時到貨而是陸續(xù)供應、且邊送邊用的

8、情況下，經(jīng)濟訂貨批量有關的計算公式如下：其中，g為送貨期內(nèi)每日平均送貨量，d為每日平均消耗量。（二）存貨瞬時到貨且允許缺貨的經(jīng)濟訂貨批量模型在訂購的存貨瞬時到達但允許缺貨的情況下，持有存貨的相關總費用包括了訂貨費用、儲存費用和缺貨費用，經(jīng)濟訂貨批量有關的計算公式如下：例如，某企業(yè)需要的幾種存貨具有不同的要求，可能遇到幾種不同的情況，如可能缺貨、存貨陸續(xù)供應和陸續(xù)耗用等。下面建立一般情況下通用的經(jīng)濟訂貨批量計算模型。（1）設計存貨是否陸續(xù)供應和耗用的控件以及是否允許缺貨的控件。其中是否陸續(xù)供應和耗用的控件的【設計控件格式】中【數(shù)據(jù)源區(qū)域】為“$D$4:$D$5”，【單元格連接】為“B10”，【下

9、拉顯示項數(shù)】為2,而在單元格D4中填入“瞬時到貨”，在單元格D5中填入“陸續(xù)到貨”；是否允許缺貨的控件的【設計控件格式】中【數(shù)據(jù)源區(qū)域】為“$D$7:$D$8”，【單元格鏈接】為“B11”，【下拉顯示項數(shù)】為2,主單元格D7中填入“不允許缺貨”，在單元格D8中填入“允許缺貨”。（2）在單元格B13中輸入經(jīng)濟訂貨批量計算公式“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

10、(B10=2,B11=2),SQRT(2*B4*B5/B8*B6/(B6-B7)*(B9+B8)/B9)”。（3）在單元格B14中輸入年最佳訂貨次數(shù)計算公式“INT(B4/B13+0.5)”。（4）在單元格B15中輸入年最低總訂儲費用計算公式“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

11、/(B9+B8)”這樣通用的經(jīng)濟訂貨批量計算模型建立完畢。例如，某企業(yè)對甲零件的年需要量為6 000件，每次訂貨費用為250元，單位保管費用15元/年，若允許缺貨，每缺貨1件的年成本為10元。零件每日到貨量25件，零件每日消耗量20件。則：（1）假設零件陸續(xù)供應和耗用，且允許缺貨，則計算出經(jīng)濟訂貨批量為1 581件，年訂貨次數(shù)4次，最低訂儲費用1 397.37元。（2）若不允許缺貨，但零件仍為陸續(xù)到貨和消耗，則經(jīng)濟訂貨批量為1 000件，年訂貨次數(shù)6次，最低訂儲費用3 000元。（3）若不允許缺貨，但零件為瞬時到達，則經(jīng)濟訂貨批量為447件，年訂貨次數(shù)13次，最低訂儲費用6 708.20元。（

12、4）若允許缺貨，且零件為瞬時到達，則經(jīng)濟訂貨批量為707件，年訂貨次數(shù)8次，最低訂儲費用4 242.64元。四、有數(shù)量折扣，不允許缺貨的經(jīng)濟訂貨批量模型以上的經(jīng)濟訂貨批量都是假定采購數(shù)量不論多少，價格是一樣的。如果銷售企業(yè)為了鼓勵客戶購買更多的商品，采用“采購數(shù)量增加，可以享受折扣”這樣的銷售政策，那么客戶一次購買量越多，所獲得的價格優(yōu)惠往往越大。因此，存貨的相關總費用不僅包括訂貨費用和儲存費用，還包括存貨的采購費用。計算經(jīng)濟訂貨批量的基本思路是按打折后的價格計算，如果計算出的經(jīng)濟批量大于折扣點，則此經(jīng)濟批量即為最優(yōu)解。反之，必須比較以經(jīng)濟批量進行采購和以折扣點進行采購兩者的總費用。具體步驟為

13、：（1）分別計算不同折扣價格下的經(jīng)濟訂貨批量。（2）判斷這些經(jīng)濟訂貨批量是否有效（計算出的某經(jīng)濟訂貨批量是否與該折扣區(qū)間的價格相符）。（3）計算有效的經(jīng)濟訂貨批量下的總存貨費用，并同時計算不同折扣起點批量下的總存貨費用。（4）在這些總存貨費用中找出最低費用對應的經(jīng)濟訂貨批量或折扣起點批量。（5）比較經(jīng)濟訂貨批量或折扣起點批量與需求量的關系，若小于需求量，則該經(jīng)濟訂貨批量或折扣起點批量就是最優(yōu)解，否則，需求量是最優(yōu)解。例如，某企業(yè)乙零件的年需要量為35 000件，每次訂購費用為30元，存儲費率是零件單價的20%，該零件的供應商為爭取業(yè)務，特制定臺階式折扣價格，以鼓勵買方每批多購，其價格表如表1所

14、示。訂貨決策的具體步驟如下：（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,"無效")單元格F8:B8*$B$3+$B$3*$B$4/A8+$B$5*B8*A8/2單元格G8:IF(D8="有效",MIN(E8:F8),"無意義")單元格H8:IF(G8=F8,A8,C8)（2）選取單元格區(qū)域C8:H8,將其向下復制到單元格區(qū)域C13:H13。（3）將單元格D13中的公式修改為：“IF(C13>=A13,"有效","無效")”。（4）在單元格B14中輸入公式“INDEX(H8:H13,MATCH(MIN(G8:G13),G8:G13,0),0)”。（5）在單元格B15中輸入公式“IF(B3>B14,B14,B3)”。（6）在單元格E15中輸入公式“B3/B15”。由此可見，以經(jīng)濟批量8 101件訂貨時的存貨總費用最低，故企業(yè)的經(jīng)濟訂貨批量為8 101件，年訂貨次數(shù)4次。 五、結 論由此可以看出，利用Excel強大的功