爱华网报童问题Excel仿真模型
重庆三峡学院 关文忠
【报童问题】报童每日早上从报社以a元/份（批发价）购进报纸，以b元/份（零售价）在市场出售，若当日售不出去，回收公司以c元/份（收购价）收购（b>a>c）。若已知需求分布，则报童每早应购进多少报纸可使期望收益最大？
分析：此问题的关键点在于找出需求的概率分布。基本思路：
（1）对一个时间每天销售作个记载，然后绘制一个变量数列。可以以这个变量数列作为经验分布；也可利用拟合优度检验，考察需求服从何种分布（如正态分布、泊松分布等）；
（2）按已找出的分布律产生随机数进行仿真；
（3）利用模拟运算表列出购进不同数量报纸的期望盈利，并找出最大值。
案例：某报童记录了200天报纸的销售数量（图1A2:J21单元格域）。模拟步骤如下：
第1步：统计频数（公式显示如图2）
l在M2、M3单元格输入Excel函数，求得最小值与最大值，以确定组限；
l统计频数。选择M5:M12单元格区域，输入如图2所示函数，按住Ctrl+Shift键不放，再按Enter键，得频数统计结果，并其频数和（M13单元格）；
l求组中值（N列）；
l求频率（概率）：选择O5:O12，输入如图2所示公式，按住Ctrl+Shift键不放，再按Enter键，得频率。N4:O12作为经验频率，作为生成随机数的基准。
图1 报童销售记录表及频数统计
图2 频数统计公式
第2步：创建Excel模型
l输入已知数据：将第1步中得到的频率分布表复制到一张新的工作表的A6:B14单元格区域，并把进价、售价、处理价、订购量（预置值可任意）输入到A1:B4单元格。作为已知资料，用于模拟（图3）。
图3 报童问题随机模拟模型
l产生需求随机数（仿真）：执行菜单命令“数据/分析/数据分析/随机数发生器（Excel2003工具/数据分析/随机数发生器）”，设置对话框（如图4）：
变量个数为产生随机数的列数，本例产生1列；
随机数个数为产生随机数的行数，本例产生1000个；
分布选择“离散”。若为其他分布，则选择相应类型；
数值与概率输入区域为如前统计所得的经验分布；
输出选项选择了在本表输出，文本框中的引用为输出区域的左上角；
单击“确定”产生如图3中E列的1000个随机数（图中仅显示了前8行）。
图4 随机数发生器对话框设置
第3步计算盈利
F7单元格输入：=MIN(B$4,E7)*(B$2-B$1)
公式中销售量应等于订购量与需求量中的最小值，为了在复制过程中使订购量引用的位置保持不变，采取了行绝对引用（加了$符号），每份盈 利额为零售价与订购价之差。
G7单元格输入：=MAX(0,B$4-E7)*(B$1-B$3)
公式中如果B$4>E7，即订购量大于需求量，则会产生剩余，否则为0。每剩余1份，损失额为订购价与处理价之差。
H7单元格输入：=MAX(0,E7-B$4)*(B$2-B$1)
公式中如果需求量大于订购量，会产生缺货损失，每件损失额为零售价与订购价之差。
I7单元格输入：=F7-G7-H7，以计算盈利，即销售盈利与余、缺货机会损失之差。
选择F7:I7单元格区域，光标置于选择中区域右下角，变成实心“+”时，按住鼠标左键不放，向下拖动，复制到1000个。
I4单元格输入：=AVERAGE(I7:I1006)，以对1000次仿真结果求平均值。
第4步敏感性分析
订货量由报童决定，是可控的，不是随机的。利用模拟运算表计算期望利润。操作如下：
将订货量从70到140，步长10（也可步长再小一些）。表中K15和L15单元格是为了作图时添加直角参考线所用（图5）。
在L6单元格输入：=I4，以引用模拟模拟平均利用的单元格，作为函数表达式；
选择K6:L15单元格区域，从“数据”选项卡选择：数据工具/模拟分析/模拟运算表（Excel2003执行菜单命令：数据/模拟运算表），设置对话框如图6。由于模拟运算的自变量（订购量）是按列排列的，故在“输入引用列的单元格”选择仿真时所引用的订购量B4单元格。单击“确定”即生成模拟运算结果；
以K7:L14单元格区域为源数据，插入光滑型散点图；
在L17单元格输入：=MAX(L7:L15)，可找到盈利最大值；
在K17单元格输入：=INDEX(K7:K14,MATCH(L17,L7:L14,0))找出与最大盈利对应的订购量。但这一订购量由于区间划分以10为步长，不够精细，因此再做进一步的敏感性分析。
选择选项卡：开发工具/控件/表单控件/单击“微调项”在K15单元格画出，单击右键，“设置控件格式”（如图7），单击确，调节微调按钮，发现订货量100就是最大利润。
添加一条动态的直角参考线，其数据准备如下：
K20单元格输入：=K15；L20单元格输入：=L15，即曲线上一点；
纵轴上的点横坐标为70，纵坐标与曲线上的点相同，即L19输入=L20；
横轴上的点横坐标与曲线上的点相同，即在K21输入：=K20，纵坐标为15。
选择图表，单击右键：源数据/系列/添加，X区域选择K19:K21，Y区域选择L19:L21，图表类型选择折线型，并将线型数据标记设置如图所示。于是调节微调按钮，可得动态的直角参考线。
图5 不同订购量的敏感性分析
图6 模拟运算表对话框
结论：最优订购时100，期望盈利32.21。
图7 微调按钮控件格式设置
