Excel企业管理数据分析案例：用excel建模分析产品库存情况

一、概述：

一种常用的库存管理方法是定期检查库存控制方法：管理者必须定期检查库存水平，并决定订货量，期望能够以稳定的服务水平满足企业内外部对存储货物的需求。如果企业内外部对货物需求是确定的，那么每次检查后的订货量就很容易满足。然而，当外界对货物的需求具有不确定性，管理者需要考虑和计算订货量满足预期服务水平的可能性。为了建立一个一致的科学的库存管理策略，需要确定企业内外部对货物的需求量变化情况和目标服务水平，并建立一个风险量化分析模型确定订货量。如果每次的订货量很大，那么可以保证较高的服务水平，但同时也会造成货物库存积压比较严重，造成库存成本增加。如果每次订货量较少，那么可能无法保证服务水平。此外，管理者在计算订货量时需要考虑两个时间段内市场对货物需求量大小：第一段时间时两次检查的间隔时间，第二段时间时从订货到收货的间隔时间。

确定合理的订货量时库存管理的一个关键决策，下面通过案例来展示在excel中建立量化风险模型，模拟计算一定的库存管理服务为水平下的订货量。

二、案例说明

一家大型贸易公式通过调查发现，市场对某一种货物的需求近似正态分布，其均值为100，标准差为25。该货物从订货到收货所需时间为6天。公式采用定期库存检查控制策略，检查间隔为10天，以365天为一个分析期，期初库存为500件，预定库存总体服务水平为0.98，即1-每年的缺货天数/总天数要大于等于0.98。

需要解决如下问题：

1、同时考虑库存检查时间间隔和订货-收货时间间隔，确定每期订货量、缺货量，计算全年缺货天数、总体服务水平、总库存量。

2、只考虑库存检查时间间隔，忽略订货-收货时间间隔，确定每期订货量、缺货量，计算全年缺货天数、总体服务水平、总库存量。

3、在满足全年总体服务水平为0.98的条件下，计算合理的每期订货目标服务水平值。

三、解决思路和方法

由于市场对货物的需求近似服从正太分布，而且预定的库存服务水平为0.98，所以可以计算定期检查间隔期以及订货-收货时间间隔内货物的需求量和订货量。因为市场每天对货物的需求服从正态分布，检查间隔为10天，订货-收货时间间隔为6天，所以16天的时间内市场对货物的需求量服从N（16*100，4*25）。假定每天的库存服务水平为0.98，那么订货量应该等于1600+Z-1（0.98）*25*4，其中Z-1（0.98）表示标准正太分布在p=0.98处的区间点。确定了订货量的计算方法，就可以建立模拟计算和分析各项库存管理相关指标。

下图展示了定期检查库存控制分析的完整模型：

通过对上表数据进行迭代和模拟各5000次，就可以回答上述提出的3个问题。

下面解决第一个问题。

下图展示了输出变量‘总缺货量1’也就是考虑库存检查间隔和订货-收货时间下的总缺货量的概率质量函数，从下图可以看出，总缺货量最小为0，最大为211，均值为23.32，标准差为33.79。

下图展示了输出变量‘总缺货量1’的累积概率函数图，从中可以看到，总缺货量为0的概率为0.5左右，并以90%的置信落入0-95之间。

下图展示了输出变量‘缺货天数1’（即考虑库存检查间隔和订货-收货时间下的总缺货天数）的概率质量函数图。因为缺货天数1是离散随机函数，所有这里给出其质量函数图，从下图可以看出，总缺货天数最小值为0，最大值为4。缺货天数为0的概率接近0.5，缺货天数为1的概率为0.47，缺货天数大于1的概率很小。

下图展示了输出变量‘服务水平1’的概率质量函数图，因为服务水平1与缺货天数1具有一一对应的关系，所有服务水平1也是离散随机变量，从下图可以看出服务水平1最大值为1，对应缺货天数1为0，概率接近0.5。服务水平1等于0.99726，对应缺货天数1为1天，概率接近0.47。下图也给出了缺货天数1为2、3和4对应的服务水平1，同样，发生的概率也很低。

下图展示了输出变量总库存量1的概率密度函数图，从图中可以看出，总库存量1的最小值为253399件，最大值为290196件，均值为273056件，标准差为5036.97件。总库存量这么高的原因在于库存策略提供了极高的服务水平，也就是极低的缺货天数。

下面解决第二个问题。

下图展示了输出变量‘总缺货量2‘（即只考虑库存检查间隔下的总缺货量）的概率质量函数图。从下图可以看出，总缺货量最小值为4970件，最大为7656件。

下图展示了模型输出变量总缺货量1和总缺货量2的概率质量函数叠加图和累积概率叠加图，从中国可以看出，是否考虑订货-收货时间对于总缺货量的影响非常大。

下图展示了模型输出变量总缺货天数2的概率质量函数图。从下图可以看出总缺货天数最小为62，最大为92，均值为78.6，标准差为3.76。

下图是输出变量总缺货天数2和总缺货天数1的概率质量函数叠加图，从中可以看出，是否考虑订货收货时间对于总缺货天数的影响也是非常大的。

下图展示了输出变量服务水平2的概率质量函数图，从下图可以看出，服务水平2的最小值的0.75，最大值为0.83，均值为0.78，标准差为0.10，也可以看出，在不考虑订货-收货时间的情况下制定的库存策略无法达到预定的服务水平要求。

同样地，从输出变量总库存量2也可以看出在不考虑订货-收货时间尽管无法保证总体服务水平，但是使得库存量大为减少。

下面来解决第三个问题。

从上述分析来看，在每期订货目标服务水平为0.98的情况下，期末总体库存服务水平的最小值为0.9890，均值为0.9985。如果目标是期末总体库存服务水平的最小值为0.98，均值为0.98，那么每期订货目标服务水平应该为多少满足要求呢？

我们使用0.75247作为每期订货目标服务水平，通过对限制条件进行5000次模拟计算后，得到下图，可以看到，服务水平1的最小值为0.9753，这个值就小于0.98，因此满足要求。