用Excel建立最優(yōu)訂貨批量模型1

用Excel建立最優(yōu)訂貨批量模型.1.基本數(shù)據(jù)。假設(shè)某企業(yè)有四種存貨需要采購，供應(yīng)商也規(guī)定了各種存貨的數(shù)量折扣,各種存貨的基本數(shù)據(jù)如圖1所示。ABDE1最優(yōu)訂貨批量決策模型■3存貨窈工材電_丙材電丁材料4材料年需手2200030000180005一次訂貨兄3030密。306.'巨3.4■37每日送貨：■150200300200g每日耗用灌253040:20■9裁量折扣2%驟2%2%10單價16202330圖12.最優(yōu)訂貨批量求解分析區(qū)域的公式定義。在計算分析區(qū)域分別定義采購成本、儲存成本、訂貨成本、總成本、綜合成本、最佳訂貨次數(shù)、最佳訂貨周期和經(jīng)濟訂貨量占用資金的公式。定義方法是先定義B列的公式，然后復(fù)制到其他單元格，如圖2所示。ABC'.D-E14存貸名稱甲材料乙麗?丙材料丁材料1頃最優(yōu)訂貨批量?16;..米購成本=B4+B1OH1-B9)_二C4*C10*(l-C僉=D4+D1JQ+(1-D9X'^E4^E10+<l-E9)17藉存成本=.(G15-C1&/G7+CSV2=^D15-D15/D7+D3)Z2^(E15-E15/E7^E8>Z2=E4/E15^^_1S：訂貸成本:邛4/M砰BS二C4/C1煥CS=D4/D15*D519；■2021總成本最佳訂貨次數(shù)=Br6+B17+BlS綜合.成本:■■:B4/B15=C16+Q17+C18=B19+C19+D19+E19'=C4/C15-,.=D16+D17+D18二D4/D15-■■■^16+E17+E18=E4/E15；22、最佳訂貨周期（月L=12/^1^12/D21=12/ES123經(jīng)濟訂貨量占用資金二(B15，W)*B10=(C15/2)*C10-0)15/2)^10二(E15./2)*EW圖23.約束條件。供應(yīng)商提供的條件是：甲、乙、丙、丁的訂貨批量分別為不小于400、350、500、和300。根據(jù)以上條件，我們可以利用Excel提供的規(guī)劃求解工具計算各種存貨的最優(yōu)批量。操作如下：選擇［工具］/〈規(guī)劃求解>命令，彈出規(guī)劃求解參數(shù)對話框，如圖3。（如果在［工具］菜單下沒有“規(guī)劃求解”命令，可以執(zhí)行［工具］菜單下<加載宏>命令，從彈出的對話框中選擇“規(guī)劃求解”后即可。）設(shè)定規(guī)劃求解參數(shù)。如圖3所示。圖3求解。當(dāng)目標(biāo)單元格、可變單元格、約束條件不變時，無論基礎(chǔ)數(shù)據(jù)如何改變，都不需要修改上述設(shè)置，直接進行求解。單擊〈求解>按鈕，即可得出各種存貨的最優(yōu)批量，如圖4所示。A_B￡DE1ITT11%4trE7-4-LT^=r曲曰7TU14存貨名稱甲材料乙材料頊材電丁材料?|15最優(yōu)訂貨批量（1199：9997411246.171441.153.1095.441116米購成本'31360043120067620053920017湎存成上499.-999892252§6222624.499存492.949918訂貨成本50Q,0001078529；6228624.5492.950719總成本314600433259.J6774491530185.9■20綜合成本1954494■21最佳訂貨次數(shù)16?6666702617.6540920.816671覓43169：：22最佳訂貨周期0.7199998450\6797290.5764610.7^0296經(jīng)濟訂貨量占』9599.99793112461,7.16573.26.1^431.-.66圖4通過求解，求出了每種存貨的最優(yōu)批量，并自動計算出最優(yōu)訂貨批量下的總成本、每年最佳訂貨次數(shù)和最佳訂貨周期等，同時，豐規(guī)劃求解結(jié)果中，還提供了敏感性分析報告、運算結(jié)果報告和限制區(qū)域報告等，供企業(yè)了解經(jīng)濟訂貨批量求解的過程和結(jié)果。在模型中，總成本與各要素之間建立了動態(tài)鏈接。當(dāng)企業(yè)財務(wù)政策發(fā)生變化，如存貨年需要量改變，或經(jīng)濟條件發(fā)生變化，如每次訂貨變動成