7.1存貨概念與持有成本7.2基本經濟訂貨模型7.3保險儲備模型7.4實行數量折扣的經濟訂貨模型Excel在存貨管理中的應用內容簡介：本章主要講解利用Excel工具進行存貨管理，包括運用Excel建立基本經濟訂貨模型以及考慮保險儲備和數量折扣時的存貨管理，為便于對存貨分類控制，可利用Excel建立ABC分類模型。學習目標：通關本章的學習，學生應在掌握存貨管理的方法理論基礎上，熟練運用Excel工具進行訂貨管理和對存貨的分類控制。內容簡介和學習目標與存貨相關的成本有如下四種：采購成本：是指為取得某種存貨而支出的成本，包括買價、運雜費等。訂貨成本：是指訂購材料、商品而發(fā)生的成本。訂貨成本一般與訂貨的數量無關，而與訂貨的次數有關。儲存成本：是指儲存過程中發(fā)生的倉儲費、搬運費、保險費、占有資金支付的利息等。一定時期內的儲存成本總額等于該時期平均存貨量與單位儲存成本相乘。缺貨成本：是指由于存貨儲備不足而給企業(yè)造成的經濟損失。例如由于原材料儲備不足造成的停工損失、由于商品儲備不足造成銷售中斷的損失、由于成品供應中斷導致延誤發(fā)貨的信譽損失等。企業(yè)存貨的最優(yōu)化就是使企業(yè)存貨總成本最少7.1存貨的概念與持有成本

7.2基本經濟訂貨模型7.2.2設計經濟訂貨模型已知A集團每年需要耗用甲材料14400噸,該材料的單位采購成本1000元,單位儲存成本200元,平均每次進貨費用400元。假設甲材料不允許缺貨,耗用比較均衡,價格穩(wěn)定。要求:計算企業(yè)的最佳經濟訂貨量。具體操作步驟如下。①新建Excel工作簿，雙擊工作表標簽“sheet1”,輸入新工作表名稱“基本經濟訂貨模型”;設置該模型,并對其格式進行優(yōu)化,輸入有關數據7.2基本經濟訂貨模型②已知全年訂貨次數=全年材料需求量/每次訂貨批量。將企業(yè)每次訂貨批量假設為一個數字,在B7單元格中輸入假設數字240,在B6單元格中輸入公式“=B3/B7”,并按“Enter”鍵。③計算訂貨費用。在B9單元格中輸入公式“=B6*B4”,并按“Enter”鍵。④計算儲存成本。在B10單元格中輸入公式“=B7*B5/2”,并按“Enter”鍵。⑤計算相關總成本。在B11單元格中輸入公式“=B9+B10”,并按“Enter”鍵7.2基本經濟訂貨模型7.2.3運用“模擬運算表”計算存貨相關成本7.2基本經濟訂貨模型模擬運算表是一個單元格區(qū)域,它可顯示一個或多個公式中替換不同值時的結果。有兩種類型的模擬運算表:單輸入模擬運算表和雙輸入模擬運算表。單輸入模擬運算表中,用戶可以對一個變量鍵入不同的值從而查看它對一個或多個公式的影響。雙輸入模擬運算表中,用戶對兩個變量輸入不同值從而查看它對一個公式的影響。7.2.3運用“模擬運算表”計算存貨相關成本①設置模擬運算表中的內容及格式。在E3單元格中輸入“=B11”,在F3單元格中輸入“=B9”,在G3單元格中輸入“=B10”7.2基本經濟訂貨模型7.2.3運用“模擬運算表”計算存貨相關成本②選中D3:G11單元格區(qū)域,選擇“數據”→“預測”→“模擬分析”→“模擬運算表”命令,在“模擬運算表”對話框中,在“輸入引用列的單元格”中輸入“$B$7”，單擊“確定”7.2基本經濟訂貨模型7.2.4求解經濟訂貨量【方法一】利用規(guī)劃求解計算具體步驟如下。①加載“規(guī)劃求解”命令。按照本書第三部分任務五中所講方法,在工具欄中加載“規(guī)劃求解”命令。②單擊“數據”→“規(guī)劃求解”命令,在“規(guī)劃求解參數”對話框中,在“設置目標”中輸入“$B$11”;在“到”選擇項中選擇“最小值”;在“通過更改可變單元格”中輸入“$B$7”;增加約束條件,單擊“添加”按鈕,增加約束條件“$B$7=整數”,單擊“確定”按鈕后,用同樣的方式增加約束條件“$B$7<=14400”;勾選“使無約束變量為非負數”;在“選擇求解方法”中選擇“非線性GRG”7.2基本經濟訂貨模型7.2.4求解經濟訂貨量【方法一】利用規(guī)劃求解計算7.2基本經濟訂貨模型7.2.4求解經濟訂貨量【方法一】利用規(guī)劃求解計算③單擊“求解”按鈕,彈出“規(guī)劃求解結果”對話框,選擇“保存規(guī)劃求解的解”,單擊“確定”按鈕，結果如下。7.2基本經濟訂貨模型7.2.4求解經濟訂貨量【方法二】利用公式求解點擊B7單元格，輸入函數“=SQRT(2*B3*B4/B5)”即可返回與規(guī)劃求解算出的結果一樣的最優(yōu)訂貨量7.2基本經濟訂貨模型在存貨管理基本模型中,需要假定存貨供需穩(wěn)定且確知,交貨時間也固定不變。在實際生產過程中,企業(yè)每日對原材料的需求量可能變化,交貨時間也可能變化。在交貨期內,如果需求量增大或交貨時間延誤,就會發(fā)生缺貨。為防止由此造成的損失,企業(yè)應有一定的保險儲備。保險儲備的多少可以通過保險儲備模型計算。7.3保險儲備模型7.3.1設計保險儲備模型續(xù)[例8-1],假設甲材料從訂貨到交貨的時間為10天,單位缺貨成本為300元,交貨期內的存貨消耗量及其概率分布見表，要求:計算企業(yè)最佳的保險儲備量。7.3保險儲備模型需求量340360380400420440460概率0.010.040.20.50.20.040.01

7.3.1設計保險儲備模型具體操作步驟如下。①設置考慮保險儲備模型項目,并對其格式進行優(yōu)化,假設保險儲備量為20噸,輸入其他有關數據7.3保險儲備模型7.3.1設計保險儲備模型具體操作步驟如下。②由例8-1中計算可知,企業(yè)的每次最佳訂貨批量為240噸,在B9單元格中輸入“240”（假定）,在B10單元格中輸入公式“=B8/B9”。③計算缺貨量。在B4單元格中輸入公式“=IF(B2<=$B$15,0,(B2-$B$15)*B3)”,然后橫向復制該公式計算缺貨量;計算總缺貨量,在B5單元格中輸入公式“=SUM(B4:H4)”。④計算再訂貨點。在B15單元格中輸入公式“=B8/360*B13+B14”。⑤計算相關成本。在B17單元格中輸入公式“=B5*B10*B11”;在B18單元格中輸入公式“=B12*B14”;在B19單元格中輸入公式“=B17+B18”。7.3保險儲備模型7.3.1設計保險儲備模型假設數值運行結果如下7.3保險儲備模型7.3.2求解最佳保險儲備量利用Excel中的“規(guī)劃求解”工具可以快速計算出最佳保險儲備量的數值。①單擊“數據”→“規(guī)劃求解”命令,在“規(guī)劃求解參數”對話框中,在“設置目標”中輸入“$B$19”;在“到”選擇項中選擇“最小值”;在“通過更改可變單元格”中輸入“$B$14”;增加約束條件,單擊“添加”按鈕,增加約束條件“$B$14=整數”,單擊“確定”按鈕后,用同樣的方式增加約束條件“$B$14<=60”;勾選“使無約束變量為非負數”;在“選擇求解方法”中選擇“非線性GRG”7.3保險儲備模型7.3.2求解最佳保險儲備量利用Excel中的“規(guī)劃求解”工具可以快速計算出最佳保險儲備量的數值。②單擊“求解”按鈕,彈出“規(guī)劃求解結果”對話框,選擇“保存規(guī)劃求解的解”,單擊“確定”按鈕7.3保險儲備模型基本經濟訂貨模型中假設價格恒定，而在實際中往往會發(fā)生數量折扣。此時，存貨的采購成本就與進貨批量的大小有直接聯系，屬于決策的相關成本。企業(yè)對經濟訂貨批量的確定，除了要考慮相關的訂貨成本與儲存成本外，還應考慮存貨的采購成本。

存貨相關總成本=采購成本+訂貨成本+儲存成本7.4實行數量折扣的經濟訂貨模型【案例】A集團對甲材料的年需要量為16000千克，每千克標準價格為20元。銷貨方約定：客戶每批采購量少于1000千克的，按照標準價格計算；每批采購量大于1000千克小于2000千克的，價格優(yōu)惠2%；每批采購量2000以上的，價格優(yōu)惠3%。已知每批訂貨費用600元，單位材料的年儲存成本30元。計算最優(yōu)經濟訂貨批量。7.4實行數量折扣的經濟訂貨模型具體操作步驟如下：①新建Excel工作簿，雙擊工作表標簽“sheet1”,輸入新工作表名稱“有數量折扣經濟訂貨批量的確定”;設置該模型,并對其格式進行優(yōu)化,輸入關數據。7.4實行數量折扣的經濟訂貨模型具體操作步驟如下：②計算不考慮數量折扣時的經濟訂貨批量。在單元格B7中輸入公式“=SQRT(2*B1*B5/B6)”，可得到此時經濟訂貨批量為800千克。計算不考慮數量折扣時，經濟訂貨批量為800千克時，存貨的相關總成本。在B8單元格中輸入公式“=B7/2*B6+B2*B1+B1/B7*B5”，可得到相關總成本為344000元7.4實行數量折扣的經濟訂貨模型④分別計算采購量為1000千克、2000千克時，存貨的相關總成本。在B9單元格中輸入公式“=1000/2*B6+B2*B1*