论文部分内容阅读
摘要：电子表格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_false)
执行真假值判断，根据逻辑计算的真假值，返回不同结果。
Logical_test：表示计算结果为TRUE或FALSE的任意值或表达式；
Value_if_true：logical_test为TRUE时返回的值；
Value_if_false：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.
关键词：计算机仿真；数学建模；库存问题；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_false)
执行真假值判断，根据逻辑计算的真假值，返回不同结果。
Logical_test：表示计算结果为TRUE或FALSE的任意值或表达式；
Value_if_true：logical_test为TRUE时返回的值；
Value_if_false：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.