基于Excel的存貨管理模型設計

摘要:存貨是企業在生產經營中為生產或銷售而儲備的物資，企業持有足夠的存貨，不僅可以節約采購成本與生產時間，而且能夠迅速地滿足客戶各種訂貨的需要，從而為企業生產與銷售提供較大的機動性。然而，存貨增加必然要占用更多的資金，將使企業付出更大的持有成本，即存貨的機會成本。因此發揮存貨功能，在存貨收益與成本之間進行權衡，在充分發揮存貨功能的同是降低存貨成本，增加收益，實現它們的最佳組合，成為存貨管理的基本目標。

關鍵詞:Excel;存貨;模型設計

中圖分類號:F72文獻標志碼:A文章編號:1673-291X(2010)30-0199-03

存貨是指在日常經營過程中為了銷售而持有的、為了銷售而處于生產過程中的、為用于生產銷售的商品或勞務而持有的資產。存貨在資產負債表中列為流動資產，包括原材料、在產品、半成品、產成品、低值易耗品、委托加工材料、包裝物等。企業持有足夠的存貨，不僅有利于生產過程的順利進行，節約采購成本與生產時間，而且能夠迅速地滿足客戶各種訂貨的需要，從而為企業生產與銷售提供較大的機動性，避免因存貨不足帶來的機會損失。

一、存貨成本及其經濟訂貨批量

1.存貨成本

企業為存貨所發生的一切支出，稱之為存貨成本。主要包括采購成本、定貨成本、存儲成本、缺貨成本等部分。存貨成本的構成從理論上講，應該包括從購入到使商品處于可供銷售的地點和狀態的一切直接和間接的支出。

2.經濟訂貨批量

經濟進貨批量，是指能夠使一定時期存貨的總成本達到最低點的進貨數量。決定存貨經濟進貨批量的成本因素主要包括變動性進貨費用(簡稱進貨費用)、變動性儲存成本(簡稱儲存成本)以及允許缺貨時的缺貨成本。不同的成本項目與進貨批量呈現不同的變動關系，因此存在一個最佳的進貨批量，使成本總和保持最低水平。

3.存貨ABC分類管理

對存貨的日常管理，根據存貨的重要程度，將其分為ABC三種類型。A類存貨品種占全部存貨的10% ~15%，資金占存貨總額的80%左右，實行重點管理，如大型備品備件等。B類存貨為一般存貨，品種占全部存貨的20%~30%，資金占全部存貨總額的 15%左右，適當控制，實行日常管理，如日常生產消耗用材料等。C類存貨品種占全部存貨的60%~65%，資金占存貨總額的5%左右，進行一般管理，如辦公用品、勞保用品等隨時都可以采購。通過ABC分類后，抓住重點存貨，控制一般存貨，制定出較為合理的存貨采購計劃，從而有效地控制存貨庫存，減少儲備資金占用，加速資金周轉。

二、相關函數介紹

1.SQRT 函數

SQRT 函數的功能是返回正平方根。

函數語法:

SQRT(number)

Number要計算平方根的數。

函數說明:

如果參數 Number 為負值，函數 SQRT 返回錯誤值 #NUM!。

2.IF函數

IF函數是較為常用的邏輯函數之一，它執行真假值判斷，根據邏輯計算的真假值，返回不同結果。可以使用函數IF對數值和公式進行條件檢測。

函數語法:

IF(logical_test，value_if_true，value_if_1)

Logical_test表示計算結果為TRUE或FALSE的任意值或表達式。

Value_if_truelogical_test為TRUE 時返回的值。

Value_if_1logical_test為FALSE時返回的值。

函數說明:

函數IF可以嵌套七層，用value_if_1及value_if_true 參數可以構造復雜的檢測條件。

在計算參數value_if_true和value_if_false后，函數I 返回相應語句執行后的返回值。

如果函數IF的參數包含數組 (數組:用于建立可生成多個結果或可對在行和列中排列的一組參數進行運算的單個公式。數組區域共用一個公式;數組常量是用作參數的一組常量)，則在執行IF語句時，數組中的每一個元素都將計算。

3.INT函數

INT函數的功能是將數字向下舍入到最接近的整數。

函數語法:

INT(number)

Number 需要進行向下舍入取整的實數。

三、利用Excel建立經濟訂貨批量模型與存貨ABC模型

1.經濟訂貨批量綜合模型

(1)新建工作簿與工作表。啟動Excel電子表格，在BOOK1工作簿中新建一工作表，命名為“經濟訂貨批量”，建立“經濟訂貨批量綜合決策模型表”(如圖1所示)。

圖1經濟訂貨批量決策模型

(2)計算“經濟訂貨批量”。單擊B13單元格→在編輯欄中輸入“=IF(A11=1，SQRT(2*B4*B5/B6)，IF(A11=2，SQRT(2*B4*B5/B6*(B7+B6)/B7)，SQRT((2*B4*B5*B8)/((B8-B9)*B6))))”→按【回車】鍵確認，即可求出在不允許缺貨、允許缺貨以及陸續到貨三種不同情況下的經濟訂貨批量。

其中，IF 是邏輯函數、SQRT是平方根函數。當A11單元格等于1時，計算的是不允許缺貨的經濟訂貨批量;當A11單元格等于2時，計算的是允許缺貨的經濟訂貨批量;當A11單元格不等于1或2時，計算的是陸續到貨的經濟訂貨批量。

(3)計算“年訂貨次數”。單擊B14單元格→在編輯欄中輸入“=INT(B4/B13+0.5)”→按【回車】鍵確認，即可求出三種不同條件年訂貨次數。

(4)計算“年最低訂貨費用”。單擊B15單元格→在編輯欄中輸入“=IF(A11=1，SQRT(2*B4*B5*B6)，IF(A11=2，SQRT(2*B4*B5*B6*B7/(B7+B6))，SQRT((2*B4*B5*B6*(1-B9/B8)))))”→按【回車】鍵確認，即可求出三種不同條件年最低訂貨費用。

(5)添加控件。調出窗體工具欄，在B10單元格添加一組合框，將其數據源鏈接到D1:D3單元格區域，條件單元格鏈接到A11單元格;最后結果(如圖2所示)。

圖2計算結果

(6)建立動態圖表。單擊A19單元格→在編輯欄中輸入” =B4/B13*B5” ;單擊A20單元格，在編輯欄中輸入” =B13/2*B6” ;單擊A21單元格→在編輯欄中輸入” =SUM(A19:A20) →按【回車】鍵確認，即計算出不允許缺貨情況下最低總成本。

單擊C21單元格→在編輯欄中輸入“=A21”，選取B29:B34單元格區域，分別輸入不同的訂貨量;選取B28:B34單元格區域，單擊“數據”菜單，選擇“模擬運算表”，在打開的“模擬運算表”對話框“輸入引用列的單元格”中輸入“B13”，單擊“確定”后，即形成了總成本與經濟訂貨批量模擬運算表(如圖3所示)。

圖3模擬運算表

選取A36:A40單元格區域，在編輯欄中以數組方式輸入公式“=B13”，選取B36:B40單元格區域輸入相關數值(如圖4所示)。

圖4參考線表

分別選取上述兩個區域，利用圖表向導，建立一XY散點圖。調出窗體工具欄，在圖表區添加一“微調控件”，右擊該“微調控件”，選擇“設置控件格式”在“單元格鏈接”文本框中輸入“B4”，這樣就形成了一個動態圖表，反映在不同需求量情況下，訂貨量與總成本之間的變動情況(如圖5所示)。

圖5不同需求量下的經濟訂貨批量

2.存貨ABC管理模型

(1)新建表。在“BOOK1.xls”工作簿中另建一工作表，命名為“庫存ABC”，建立“庫存ABC管理模型表”，并將庫存金額降序排列(如圖6所示)。

圖6庫存ABC管理模型

(2)計算“庫存金額所占比重”。單擊D3單元格→在編輯欄中輸 “=C3/$C$24”→按【回車】鍵確認，復制公式至D23單元格，這樣就計算出了各庫存金額所占的比重。

(3)計算“累計比重”。單擊E4單元格→在編輯欄中輸入“=D4+E3”→按【回車】鍵確認，復制公式至E23單元格，這樣就計算出了各庫存金額的累計比重。

(4)庫存分類。單擊A3單元格→在編輯欄中輸入“=IF(E3>=95%，“C”，IF(E3>=80%，“B”，“A”))”→按【回車】鍵確認，復制公式至A23單元格即可，這樣就將其庫存商品進行了A、B、C分類(如圖7所示)。

(5)品種分析。單擊I3單元格→在編輯欄中輸入“=COUNTIF($A$3:$A$23，“A”)”→按【回車】鍵確認，復制公式至I5單元格，這樣就求出了不同分類的品種個數。

圖7ABC分類

單擊J3單元格→在編輯欄中輸入“=I3/$I$6”→按【回車】鍵確認，復制公式至J5單元格，這樣即可求出各品種所占的比重(如圖8所示)。

圖8 品種分析結果

從計算結果可以看出，ly205、ly206和ly203這三種存貨占用資金比重合計達73.81%，而其品種數只占14.29%，故企業應對這三種存貨加強監督與管理。

參考文獻:

[1]劉捷萍.Excel 在財務管理中的應用[M].北京:高等教育出版社，2008.[責任編輯 郭偉]