Excel對(duì)經(jīng)典存貨管理模型的拓展與超越

Ｅｘｃｅｌ對(duì)經(jīng)典存貨管理模型的拓展與超越(1)[摘要]Excel的“規(guī)劃求解”。工具是求解最優(yōu)化決策問題的一個(gè)十分方便而有效的工具，本文以存貨管理決策為例，向讀者介紹了Excel規(guī)劃求解工具的強(qiáng)大功能、及其在求解存貨最優(yōu)化問題時(shí)對(duì)經(jīng)典存貨管理模型的拓展和超越。[關(guān)鍵詞]Excel；規(guī)劃求解；最優(yōu)化決策一、經(jīng)典存貨模型的局限存貨管理決策是企業(yè)流動(dòng)資產(chǎn)管理的重要組成部分，其最終目的就是通過確定合理的進(jìn)貨批量和進(jìn)貨時(shí)間使存貨的總成本最低。存貨管理決策的成功與否不僅決定著企業(yè)短期生產(chǎn)經(jīng)營資金的周轉(zhuǎn)時(shí)間和效率，而且也影響著企業(yè)經(jīng)營的成本與利潤。在西方管理會(huì)計(jì)領(lǐng)域，采用經(jīng)濟(jì)訂貨方法進(jìn)行存貨決策和控制，經(jīng)過長期的研究和實(shí)踐，已經(jīng)趨于成熟和定型化了，關(guān)于經(jīng)濟(jì)訂貨量的基本公式也被譽(yù)為管理會(huì)計(jì)領(lǐng)域的經(jīng)典成果。那么，這一經(jīng)典成果是否真的在實(shí)際的經(jīng)營管理中有著廣泛的應(yīng)用空間和前景呢?在正式回答這個(gè)問題之前，我們不妨先看下面兩個(gè)存貨決策的例子：[例1]假設(shè)通友公司生產(chǎn)甲產(chǎn)品，每年需消耗10000個(gè)部件A，以單價(jià)325元向某供應(yīng)商購進(jìn)，每次需花費(fèi)500元。每年每單位存儲(chǔ)成本主要是存貨占用資金的利息，估計(jì)為購貨單價(jià)的12％．要求為該公司做出決策，每次購貨批量應(yīng)為多少，每年應(yīng)分幾批購貨，才能保證在這種購貨方式下的總成本最小?(假定該公司的實(shí)際情況滿足存貨模型假設(shè)的所有條件)解：不難看出，這是一個(gè)存貨管理中典型的經(jīng)濟(jì)批量問題，根據(jù)管理會(huì)計(jì)的經(jīng)濟(jì)訂貨量公式可以很容易地得出：∵經(jīng)濟(jì)訂貨量(Q*)=sQRI(2KD/KC)其中，“K”表示單位訂貨成本，“D”表示年存貨需求量，“kc”表示儲(chǔ)存的年變動(dòng)成本∴Q*＝SQRT(2*10000*500／(325*0．12))=506那么，如果我們將條件進(jìn)行擴(kuò)展成例2，我們是否仍能如法炮制呢?[例2]假設(shè)通友公司生產(chǎn)甲產(chǎn)品，每年需消耗10000個(gè)部件A，以單價(jià)325元向某供應(yīng)商購進(jìn)，每次需花費(fèi)500元。通友公司為了生產(chǎn)部件A，還需要部件B、C、D。這三種部件的年需求量均為10000件，其購貨單價(jià)分別為200元、275元和150元，其每訂貨成本分別為350元、600元和300元。所有存貨每年每單位存儲(chǔ)成本主要是存貨占用資金的利息，估計(jì)均為購貨單價(jià)的12％。通友公司的倉庫可容納15000立方米的存貨部件，A產(chǎn)品及B、C、D三種零件的單位部件容積分別為30m3、25m3、45m3、15m3，并且該公司估計(jì)每種部件的存儲(chǔ)空間最大不會(huì)超過該部件的最優(yōu)批量的75％。要求為該公司做出決策，每次購貨批量應(yīng)為多少，每年應(yīng)分幾批購貨，才能保證在這種購貨方式下的總成本最小?很顯然，這也是一個(gè)存貨管理的最優(yōu)訂貨批量決策問題，且該例條件更接近企業(yè)生產(chǎn)經(jīng)營的實(shí)際情況，我們是否仍能用以上最優(yōu)經(jīng)濟(jì)訂貨批量模型求解呢?我們不妨結(jié)合存貨管理模型特點(diǎn)對(duì)例2所給的條件進(jìn)行詳細(xì)的分經(jīng)典存貨管理模型的應(yīng)用是有著極其嚴(yán)格條件的。首先是模型建立的基本假設(shè)條件，如：需求量穩(wěn)定，存貨供應(yīng)及現(xiàn)金充足、及時(shí)到貨、不缺貨等，在本題沒有特別說明的前提下，我們可以假設(shè)其成立。其次，經(jīng)典存貨模型還存在其建立的隱含條件，如：(1)模型是在單一存貨決策的前提下討論建立的，盡管大多數(shù)企業(yè)的存貨品種都不只一種，但經(jīng)典模型并沒有提供對(duì)多品種存貨管理決策的解決方案和辦法，而例2恰恰是三種存貨的管理決策問題，不符合存貨模型的隱含條件；(2)存貨經(jīng)典模型并未提到存貨的儲(chǔ)存空間是有限的，即假定存貨的儲(chǔ)存空間總是足夠的，例2也不滿足這一條件。即例2不能直接應(yīng)用經(jīng)典存貨管理模型的公式去求解，而重新建立一個(gè)相應(yīng)的高等數(shù)學(xué)模型求解又令大多數(shù)會(huì)計(jì)人員望而卻步。還有其他途徑能突破經(jīng)典存貨管理模型的局限，找到廣泛實(shí)用的、能切實(shí)解決企業(yè)存貨管理問題方案嗎?誰能擔(dān)此重任呢?二、Excel解決最優(yōu)化問題的強(qiáng)大實(shí)力上述存貨的管理決策問題其實(shí)就是一個(gè)典型的最優(yōu)化決策問題，只是傳統(tǒng)的會(huì)計(jì)只能解決單一約束條件和單目標(biāo)的量優(yōu)決策問題，對(duì)于約束條件或多目標(biāo)規(guī)劃求解的最優(yōu)求解問題往往顯得無能為力。Excel的出現(xiàn)，完全解決了會(huì)計(jì)人員長期面臨的這樣一種尷尬。下面筆者就以前面所提出的多品種存貨管理的最優(yōu)化決策問題為例，說明如何運(yùn)用Excel的Solver(規(guī)劃求解)工具實(shí)現(xiàn)對(duì)經(jīng)典存貸管理模型的拓展與超越。(一)運(yùn)用Excel解決最優(yōu)化決策的步驟1．分析問題，建立決策問題的方案評(píng)價(jià)基本關(guān)系表。2．確定決策的最終目標(biāo)，即確定目標(biāo)單元格及其運(yùn)算表達(dá)式。如確定決策目標(biāo)為追求利潤最大化，則放置總利潤的單元格為目標(biāo)單元格，總利潤的數(shù)學(xué)求解表達(dá)式則為目標(biāo)單元格的運(yùn)算公式。3．確定影響目標(biāo)的因素，即確定放置自變量或可變量的單元格。一個(gè)最優(yōu)決策問題的自變量或可變量(即影響因素)可以是一個(gè)，也可以是多個(gè)。4．找出問題的所有限制因素，或稱約束條件，并根據(jù)所設(shè)定的變量關(guān)系寫出相應(yīng)約束條件的一般數(shù)學(xué)表達(dá)式。5．利用Excel的“規(guī)劃求解”工具進(jìn)行求解，井得出求解運(yùn)行結(jié)果報(bào)告。共2頁:1[2]下一頁Ｅｘｃｅｌ對(duì)經(jīng)典存貨管理模型的拓展與超越(2)(二)舉例說明下面仍以前面的例2為例，具體說明如何運(yùn)用Excel求解最優(yōu)化決策問題。1．根據(jù)會(huì)計(jì)的基本原理和步驟為決策問題建立方案模型評(píng)價(jià)表如圖1。在圖1中輸入求解最優(yōu)訂貨量的有關(guān)公式，具體如下，(1)每年的訂貨批次啤需要量／購貨批量，故B11=B5／B3，將此公式自制到C11、D11和E11中。(2)平均存貨=購貨批量／2，故B12=B3／2，同理將此公式自制到C12、D12及E12中。(3)年購貨成本=年需求量*購貨單價(jià)，即B14=C5*C6，將此公式自制到C14、D14和E14中。(4)各部件占用的最大容積=0．75*購貨批量*單位部件容積。即B13=B3*B9*75％，將此公式復(fù)制到C13、D13和E13中。(5)年存儲(chǔ)成本=平均存貨量*單位存儲(chǔ)成本，故B16=B8*B12，將此公式復(fù)制到C16、D16和E16。(6)年訂貨成本=年訂貨批次*每批訂貨成本，即Bl5=B1l*B7，將此公式復(fù)制到C15、D15和E15中。全年總成本=年購貨成本+年存儲(chǔ)成本+年訂貨成本，即B18：B14+B15+B16，將此公式復(fù)制到C18、D18和E18中。2．將方案評(píng)價(jià)模型轉(zhuǎn)化為最優(yōu)化價(jià)模型建立了方案評(píng)價(jià)的基本模型后，接下來應(yīng)做的是通過Excel所提供的“規(guī)劃求解”工具將其轉(zhuǎn)化為最優(yōu)化價(jià)模型。其基本方法是：首先選擇工具菜單中的“規(guī)劃求解”命令，則出現(xiàn)規(guī)劃求解對(duì)話框，在本例中依照如下步驟分別依次設(shè)定對(duì)話框內(nèi)方案求解的各個(gè)參數(shù)。具體求解的基本步驟是：(1)打開“工具”菜單。(2)選擇“工具”中的“規(guī)劃求解”命令，彈出“規(guī)劃求解參數(shù)”對(duì)話框，按下列程序完成“規(guī)劃求解參數(shù)”對(duì)話框項(xiàng)目的填入。①選定并設(shè)置目標(biāo)單元格。該單元格的數(shù)值是進(jìn)行最優(yōu)化決策的目標(biāo)，因此也稱目標(biāo)變量。本例的決策目標(biāo)是存貨成本總額最低，因而放置存貨成本總額的單元格便是目標(biāo)單元格，即將F18設(shè)置為目標(biāo)單元格。(注：該單元格必須包含一個(gè)公式，通過公式把目標(biāo)變量跟決策變量直接或間接地聯(lián)系起來。)②設(shè)定方案的可變單元格。該單元格放置備選方案的可變量，也稱作方案的決策變量。本例中^產(chǎn)品及B、C、D三種部件的采購批量為決策變量，即將B2，D2、C2、E2設(shè)為可變單元格。③確定決策的約束條件。本例的約束條件簡單，其一是各部件占用的最大存儲(chǔ)容積不能超過15000立方米，用公式表示即：F13≤15000，其中，F(xiàn)13=B13+C13+D13+E13。其二是每一產(chǎn)品的購貨件數(shù)應(yīng)為正整數(shù)，即＄B＄3：＄E＄3=整數(shù)；且B3≥1，D3≥，E3≥1。(注：對(duì)于這個(gè)模型，其起始賦值不能為0，否則會(huì)出現(xiàn)“#DIV／O!”這樣的標(biāo)志，即分母為0溢出的現(xiàn)象(數(shù)學(xué)上也稱迭代不收斂)。(3)翰入完畢后單擊“求解”按鈕，Excel將自動(dòng)進(jìn)行計(jì)算，完成后彈出“規(guī)劃求解結(jié)果”對(duì)話框，單擊“確定”按鈕，則Excel將自動(dòng)保存規(guī)劃求解結(jié)果，并將優(yōu)化計(jì)算結(jié)果顯示在電子表格中的可變單元格和目標(biāo)單元內(nèi)，其結(jié)果如圖2所示。通過本文的介紹可見，借助Excel的規(guī)劃求解工具，會(huì)計(jì)人員只需深刻地理解決策