基于Ｅｘｃｅｌ的庫存仿真決策

摘要：電子表格Excel具有強大的計算功能，它提供了一種描述問題、建立模型、處理數據與求解的有效工具。本文通過庫存問題的一個實例，說明它在計算機仿真決策中的應用。

關鍵詞：計算機仿真；數學建模；庫存問題；Excel

中圖分類號：F240文獻標識碼：A文章編號：1002-3100(2007)09-0044-03

Abstract: Electronic forms Excel has the formidable functions of computing， it provides a kind of effective tool for describing question， establishing model， processing data and seeking for solution. Through the example of inventory， this paper explains the application of Excel in the computer simulation decision-making.

Key words: computer simulation; mathematical modeling; inventory problem; Excel

現實生活中大多數系統的動態行為都十分復雜，涉及許多不確定或未知的變化因素，很難甚至無法運用數學上的解析方法對其進行建模或求解。計算機仿真就是利用計算機模型來模仿某個復雜系統的動態行為，對其行為變化進行重復的模擬實驗和動態演示，評價或預測它的行為效果，為決策提供依據。

本文通過庫存問題的實例，在電子表格Excel上建立仿真模型，尋求最佳參數，確定最佳庫存策略。

1庫存問題的提出

在商品的銷售中，由于庫存量和銷售量不可能做到完全同步，所以要保持一定的庫存儲備。若庫存過多，就會造成商品積壓，占用過多流動資金，倉儲費、保管費、自然損耗等的增加。如果庫存少，就可能造成缺貨。下面舉一種常見的例子。

某商家經銷某商品，采用的訂貨策略是：若此種商品的庫存量降到控制量M，并且以前沒有未到貨的訂單，則訂貨量為N。希望找到最佳的決策參數M和N，使累計利潤最大。給定的已知條件是：（1）訂貨后的第三個工作日收到廠家的送貨；（2）每售出一件商品可獲毛利15.00元；（3）每件商品積壓一天，流動資金占用、倉儲費等損失0.80元；（4）每次訂貨費用為75.00元；（5）商品每天的銷售量X是隨機的，根據以往的銷售記錄統計，銷售量X平均每天為50件，其均方差為10，近似地服從正態分布。

2仿真模型的流程圖（圖1）

3和本例有關的Excel函數

（1）NORMINV(probability， mean， standard_dev)

返回指定平均值和標準偏差的正態累積分布函數的反函數。

Probability：正態分布的概率值；

Mean：分布的算術平均值；

Standard_dev：分布的標準偏差。

（2）RAND()

返回大于等于0及小于1的均勻分布隨機數。

（3）MAX(number1， number2， ...)

返回一組值中的最大值。

Number1， number2， ...：是要從中找出最大值的1～30個數字參數。

（4）MIN(number1， number2， ...)

返回一組值中的最小值。

Number1， number2，...：是要從中找出最小值的1～30個數字參數。

（5）IF(logical_test， value_if_true， value_if_1)

執行真假值判斷，根據邏輯計算的真假值，返回不同結果。

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

Value_if_true：logical_test為TRUE時返回的值；

Value_if_1：logical_test為FALSE時返回的值。

（6）AND(logical1， logical2， ...)

所有參數的邏輯值為真時，返回TRUE；只要一個參數的邏輯值為假，即返回FALSE。

Logical1， logical2，...：表示待檢測的1～30個條件值，各條件值可為TRUE或FALSE。

（7）INT(number)

將數字向下舍入到最接近的整數。

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

根據概率論知識，已知定理：如果隨機變量X的分布函數Fx單調連續，則Y=Fx確定一個新的隨機變量，它服從于0，1上的均勻分布。利用這個定理，先產生0，1上的均勻分布的隨機數Y，則X=F^-1Y就是一個服從Fx的隨機變量。

因此，Excel中的隨機變量NORMINV(RAND()， 50， 10)服從期望值為50，標準差為10的正態分布。取整后，即可作為隨機銷售量X。

4Excel表上建模

表1是Excel表格中的一部分，用它說明表上建模的過程。方案中的決策參數M（控制量）=N（訂貨量）=150。

2、3、4行（時間序列為-2、-1、0）是初始化狀態；E列放置判斷值e，當庫存量b與售出量d之差小于M時，e=1，表示下訂單；否則e=0，沒有下訂單。

5行放置庫存仿真系統第一天（時間序列為1）運行的動態演示。

庫存量B5=B4-D4+E2*150，其中150是決策參數N的取值；

需求量C5=INT(NORMINV(RAND()， 50， 10))；

售出量D5=MIN(B5， C5)；

判斷E5=IF(AND(B5-D5＜150， E3=0， E4=0)， 1， 0)，其中150是決策參數M的取值。在AND(B5-D5＜150， E3=0， E4=0)中，第二和第三個邏輯參數的取值是為了保證當前工作日的前兩個工作日沒有下訂單，也就是保證了以前沒有未到貨的訂單；

毛利F5=D5*15；

損失G5=（B5-D5）*0.8+E5*75；

累計利潤H5=F5-G5+H4。

然后，選中A5：G5區域，拖動填充柄將該區域中的公式往下復制，直至結束。復制的過程就是仿真系統模仿真實系統的動態演示過程。

5仿真結果分析

選擇不同的決策參數M（控制量）和N（訂貨量），組成不同的庫存訂貨方案。

考慮到：

第一、銷售量X的期望值=50，均方差=10，依據正態分布的3σ原則，每天的銷售量X在20～80之間。

第二、下訂單的第三天送貨，并且下訂單時，以前沒有未到貨的訂單，即下訂單的后兩天沒有貨物補充。因此訂單量須考慮三天的銷售量。

因此，決策參數M（控制量）和N（訂貨量）都應在60～240之間。修改上例中庫存量B5=B4-D4+E2*150中的N=150，以及判斷E5=IF(AND(B5-C5＜150， E3=0， E4=0)， 1， 0)中的M=150，重新開始仿真系統的動態演示過程，得到不同的行為效果。先作表2中的前7個方案。

前面7個方案中，兩個具有最佳演示效果的方案參數分別是M=160、N=160和M=150、N=150，進一步在其附近做后面兩個方案，得到最佳方案的參數為M=160、N=150。

6補充說明

（1）本例中的數學模型是一個隨機模型，在參數不變的情況下，每一次仿真演示的效果會有差異。為了避免誤差過大，可采用多輪仿真演示，或采用較長周期的仿真演示的方法。

（2）庫存問題有多種類型，一般比較復雜，不一定都適合用Excel解決。但Excel簡單易學、功能強大、便于跟蹤，有利于程序調試和模型修改，應充分開拓它的應用范圍。

參考文獻：

[1] 復旦大學. 概率論[M]. 北京：人民教育出版社，1979.

[2] 周義倉，赫孝良. 數學建模實驗[M]. 陜西：西安交通大學出版社，2002.

[3]Microsoft Corporation. Office Excel2003幫助系統[CP/DK]. (2006-05-17)[2006-11-15]. http:／／office.microsoft.com／zhcn／help／FX100485362052.aspx?pid=CL100605172052.