《物流數(shù)據(jù)分析》課件-任務(wù)5.4 倉庫訂貨決策

任務(wù)5.4倉庫訂貨決策【任務(wù)目的】1．掌握經(jīng)濟(jì)訂貨批量模型的原理和求解步驟；2．熟練運(yùn)用Excel進(jìn)行規(guī)劃求解建模并求解經(jīng)濟(jì)批量；3．掌握Excel模擬運(yùn)算表的使用?！局R鏈接】倉儲管理中隨著倉庫物資的不斷出庫，需要對庫存物資進(jìn)行訂貨補(bǔ)充，如果一次性訂貨量過大，會大大增加庫存物資管理成本，而一次訂貨量過小，又要滿足需求必然會增加訂貨的次數(shù)，而次數(shù)的增加又會增加訂貨的成本，因此，在倉庫訂貨中，存在一個(gè)最經(jīng)濟(jì)的訂貨量，這個(gè)訂貨量叫做經(jīng)濟(jì)訂貨批量（EOQ）。假設(shè)在不允許缺貨的情況下，年倉儲總成本、保管成本、訂貨成本、購置成本與訂購批量之間的關(guān)系可按圖來表述。倉儲管理中的定量訂貨法是指當(dāng)庫存量下降到預(yù)定的最低庫存量（這個(gè)最低庫存量叫做訂貨點(diǎn)）時(shí)，按規(guī)定數(shù)量（一般以經(jīng)濟(jì)批量為標(biāo)準(zhǔn)）進(jìn)行訂貨補(bǔ)充的一種庫存控制方法。在倉儲管理中，倉儲總成本由物品總價(jià)值、訂貨成本、儲存成本三個(gè)部分組成。用公式表達(dá)為TC——總成本D——商品的年需求量C——商品的單價(jià)Q*——經(jīng)濟(jì)訂貨批量S——每次訂貨的費(fèi)用H——單位商品的儲存成本【工作任務(wù)】某零售商的電冰箱年需求量為16000件，每次訂貨成本為600元，單位存貨的年儲存成本為30元，現(xiàn)需確定以下問題：1．每次訂貨量為600時(shí)的訂貨成本、年儲存成本、年總成本；2．經(jīng)濟(jì)訂貨批量(EOQ)及EOQ下的年訂貨成本、年儲存成本、年總成本；3．當(dāng)訂貨量在200~1500范圍內(nèi)變化時(shí)，以100為步長進(jìn)行變化時(shí)，年訂貨量、年訂貨成本、年儲存成本以及年總成本的變化情況，并用圖表進(jìn)行展示?！緦?shí)施步驟】1．輸入數(shù)據(jù)、建立模型2．經(jīng)濟(jì)訂貨批量求解3．建立模擬運(yùn)算表4．制作成本隨訂貨批量變化的圖表1．輸入數(shù)據(jù)、建立模型新建一個(gè)工作表，輸入如圖所示的基礎(chǔ)數(shù)據(jù)。在C7單元中輸入公式“=C3*C2/C6”，在C8單元格中輸入公式“=C4*C6/2”，在C9單元格中輸入公式“=C7+C8”；在C11單元格中輸入公式“=SQRT(2*C2*C3/C4)”，在C12單元格中輸入公式“=C3*C2/C11”，在C13單元中輸入公式“=C4*C11/2”，在C14單元格中輸入公式“=C12+C13”，如圖所示。計(jì)算結(jié)果顯示，當(dāng)訂貨量為600的時(shí)候，訂貨成本為16000、年儲存成本為9000、年總成本為25000。2．經(jīng)濟(jì)訂貨批量求解選擇“文件”選項(xiàng)卡，選擇“選項(xiàng)”，進(jìn)入“Excel選項(xiàng)”對話框，如圖所示。在“Excel選項(xiàng)”對話框中切換到“加載項(xiàng)”頁，點(diǎn)擊下方“轉(zhuǎn)到”按鈕，在“加載項(xiàng)”對話框中勾選“規(guī)劃求解加載項(xiàng)”，如左圖所示，點(diǎn)擊“確定”完成加載。單擊“數(shù)據(jù)”選項(xiàng)卡下“分析”組中的“規(guī)劃求解”，在“規(guī)劃求解參數(shù)”對話框中設(shè)置目標(biāo)單元格為“$C$14”，目標(biāo)取“最小值”，“通過更改可變單元格”設(shè)置為“$C$11”，其余參數(shù)取默認(rèn)值，如右圖所示。單擊“求解”即可完成計(jì)算，計(jì)算結(jié)果如圖所示：經(jīng)濟(jì)訂貨批量（EOQ）為800，在該訂貨批量下，年訂貨成本為12000，年儲存成本為12000，年總成本為24000。3．建立模擬運(yùn)算表在單元格F2到I2分別輸入“訂貨量”、“年訂貨成本”、“年儲存成本”、“年總成本”；在F3單元格中輸入公式“=C6”、在G3單元格中輸入公式“=C7”、在H3單元格中輸入公式“=C8”，在I3單元格中輸入公式“=C9”；在F4單元中輸入值“200”，然后縱向填充至F17，步長值為100，如圖所示。選定區(qū)域F3:I17，選擇“數(shù)據(jù)”選項(xiàng)卡下的“模擬分析”下拉列表中的“模擬運(yùn)算表”，在“模擬運(yùn)算表”對話框中“輸入引用列的單元格”中輸入“$C$6”，如圖所示。單擊“確定”可計(jì)算出在不同訂貨批量下的年訂貨成本、年儲存成本、年總成本計(jì)算結(jié)果，如圖所示。可以觀察到，當(dāng)訂貨批量取800的時(shí)候，年總成本最低，這與上一步規(guī)劃求解的結(jié)果一致。思考與點(diǎn)撥：模擬運(yùn)算表的作用是什么？Excel中的模擬運(yùn)算表分為單變量模擬運(yùn)算表和雙變量模擬運(yùn)算表，前者用于分析一個(gè)變量值的變化對公式運(yùn)算結(jié)果的影響，后者分析兩個(gè)變量值同時(shí)變化對公式運(yùn)算結(jié)果的影響。本任務(wù)中僅有訂貨量一個(gè)變量，屬于單變量模擬運(yùn)算表。任務(wù)中我們還發(fā)現(xiàn)，規(guī)劃求解只給出了滿足目標(biāo)的最優(yōu)訂貨批量，但模擬運(yùn)算表還能直觀看出不同訂貨批量下的倉儲成本變化趨勢。4．制作成本隨訂貨批量變化的圖表選定區(qū)域F2:I2以及區(qū)域F4:I17，單擊“插入”選項(xiàng)卡下“圖表”組中“帶直線的散點(diǎn)圖”，插入圖表后將圖表標(biāo)題更改為“倉儲成本隨訂貨批量變化趨勢”，如圖所示?！救蝿?wù)小結(jié)】經(jīng)濟(jì)訂貨批量（EOQ）是實(shí)現(xiàn)倉儲總成本最小的訂貨批量，任務(wù)中倉儲總成本由訂貨成本和儲存成本兩個(gè)效益背反的部分構(gòu)成，通過Excel的規(guī)劃求解功能來自動(dòng)調(diào)整訂貨批量的值來觀察倉儲總成本是否達(dá)到最小，當(dāng)倉儲總成本達(dá)到最小值時(shí)的訂貨批量即為經(jīng)濟(jì)訂貨批量，其計(jì)算結(jié)果與套用公式計(jì)