基于EXCEL规划求解选择中央仓库

一、 问题描述

KLF电子公司是一个美国的电子设备制造商。公司有一个制造厂，位于加利福尼亚的圣何塞。 KLF电子公司将其产品配送到五个地区的仓库，分别位于亚特兰大、波士顿、芝加哥、达拉斯、和洛杉矶。

在目前的配送系统中，将美国分成五个主要的市场，每个市场由相应的一个仓库负责供货。顾客一般是零售商，他们直接向本地区的地区仓库订货。这意味着，在目前的配送系统中，每个顾客被分配到单一的市场，并只向一个地区仓库订货。

仓库向制造厂订货。制造厂一般要花费两个星期的时间来满足任何一个地区仓库的订货。目前，KLF公司向顾客提供的服务水平是90%。在最近几年中，KLF公司注意到来自竞争对手的竞争和顾客要求改善服务水平并降低成本的压力都在显著增加。

为了改善服务水平并降低成本，KLF将考虑更换其采用5个仓库的配送策略为2个中央仓库的策略。这2个中央仓库应该是现有仓库中的2个。公司首席执行官坚持无论使用什么配送策略，KLF的策略都应该保证服务水平上升到97%。

回答三个问题：

a． 一份对5个地区市场顾客需求的详细分析发现，5个地区市场的需求非常相似，也就是说，如果一个地区的周需求高于平均需求，其他地区的周需求也会偏高，这一发现对新系统的吸引力有何影响？

b． 为了进行严格的分析，你选择了一个典型的产品A。表1给出了该产品在过去12周里每个地区的周需求数据。

表1：

表1 历史数据

周

1

2

3

4

5

6

7

8

9

10

11

12

亚特兰大

33

45

37

38

55

30

18

58

47

37

23

55

波士顿

26

35

41

40

46

48

55

18

62

44

30

45

芝加哥

44

34

22

55

48

72

62

28

27

95

35

45

达拉斯

27

42

35

40

51

64

70

65

55

43

38

47

洛杉矶

32

43

54

40

46

74

40

35

45

38

48

56

一个订单（仓库发给制造厂）需要5550美元的准备成本，库存持有成本为每单位每周1.25美元。在目前的的配送系统中，从制造厂运输单位产品到仓库的平均成本在表2中（见入库一栏）。

表2

表2 单位产品运输成本

仓库

入库

出库

亚特兰大

12

13

波士顿

11.5

13

芝加哥

11

13

达拉斯

9

13

洛杉矶

7

13

表2还给出了运输单位产品到地区市场个商店的平均成本（见出库一栏），最后，表3提供了从现有仓库运输单位产品到其他地区市场的平均成本。可以假定这些地区仓库将变成中央仓库。

表3

表3 集中化系统中的单位产品的运输成本

仓库

亚特兰大

波士顿

芝加哥

达拉斯

洛杉矶

亚特兰大

13

14

14

15

17

波士顿

14

13

8

15

17

芝加哥

14

8

13

15

16

达拉斯

15

15

15

13

8

洛杉矶

17

17

16

8

13

假定你只对产品A比较两种系统，你将推荐哪个系统？为了回答这个问题，你必须基于历史需求数据比较两种策略下的成本和库存水平。同样，需要确定哪个地区仓库适合作为两个中央仓库。

c. 建议在集中化配送策略中，产品的配送采用UPS陆上服务，可以保证产品在3天内到达中央仓库（0.5周）。当然在这样的情况下，从制造厂到仓库的单位运输成本近来增加，事实上，这个例子中，运输成本将增加50%。例如，从制造厂运到亚特兰大仓库的单位成本变成18美元，你是否推荐这种策略？解释你的答案。

二、 求解过程

问题a，

集中式配送系统通过风险分担机制，使得KLF公司可以以更低的库存水平获得同样的服务水平。这其中设计一个重要的概念—风险分担。风险分担说明需求汇集会减少波动，前提是一个顾客的高要求很可能会倍另一个顾客的低要求所补偿。需求变动的减少使得安全库存减少，从而降低了平均库存。变异系数可以很好的衡量顾客需求相对平均需求的变动。变异系数=标准差/平均需求。在总平均需求不变的情况下，标准差越小，顾客需求相对平均需求的变动越小。

基于历史数据计算求各个地区的变异系数及总的变异系数。

1. 计算公式及EXCEL单元格设置：

1）平均周需求AVG=

，设置单元格N2==SUM(B2:M2)/12，按ENTER即可求出亚特兰大地区市场的平均周需求AVG，并利用填充手柄填充到N3，N4, N5, N6, N7，即可同样的求出其他4个地区与总的平均周需求；

2）周需求标准差STD=

,设置单元格O2==STDEV(B2:M2)，按ENTER即可求出亚特兰大地区市场的平均周需求标准差，并利用填充手柄填充到O3，O4,O5,O6,O7，即可同样的求出其他4个地区与总的平均周需求标准差；

3）变异系数变异系数=标准差/平均周需求, 设置单元格P2= =O2/N2，按ENTER即可求出亚特兰大地区市场的变异系数，并利用填充手柄填充到P3，P4,P5,P6,P7，即可同样的求出其他4个地区与总的变异系数；选择较集中配送（即新系统），变异系数降低。计算结果如图1，

图1

2. 从变异系数可以看出，当题目中所描述的情景“5个地区市场的需求非常相似，也就是说，如果一个地区的周需求高于平均需求，其他地区的周需求也会偏高，”在这个情景中，市场需求是正相关，会使新系统由风险分担带给KLF的利益减少。另外，集中配送可能增加运输成本，所以，这一新发现会使得新系统的吸引力降低。

问题b，对单一产品A比较两种系统，基于历史需求数据比较两种策略下的成本和平均库存水平。确定选择哪两个地区仓库作为中央仓库。需要用到以下关系式:

1）平均周需求AVG=

,

2) 周需求标准差STD=

,

3)提前期内平均需求=L*AVG，L为补货期=2，

4）安全库存=

, 服务水平=97%，Z通过函数NORMSINV( 97%)求得=1.88,

5)经济批量订货量Q=

，

6）订货成本K=5550，

7）单位库存持有成本h=1.25，

8）重新订货点s=提前期内平均库存需求+安全库存=L*AVG+

9)最大库存S=s+Q，

10）平均库存水平=经济批量订货量/补货期+安全库存,

11）库存成本=库存成本=(Q/2)* h

两种系统的计算思路，对于分别配送方案（现有配送系统），如下图表示：

图2

五个地区的货仓直接为本地区的市场供货，计算1）---10）式的指标，步骤如下：

一、 将题目已知数据按以下表格输入到EXCEL表格当中，其中

M9,M10,M11,M12设置为固定引用单元格，

图3

二、 计算关系式1）-11）的指标，表格及单元格公式设置如下：

图4

首先计算亚特兰大货仓直接为本地区的市场供货的各项指标，其他的地区类似，输入公式计算出结果后，利用填充手柄向下拉填充到相应的单元格即可。而合计一栏，是由五个地区的各项指标的列相加所得，比如单元格B23为五个地区的平均周需求的和，求的B23的值之后，利用填充手柄向后拉填充到相应的单元格即可求得五个地区的相应的指标之和。

表5 单元格公式设置

名称

单元格

公式

平均周需求

B18

=AVERAGE(B3:M3)

周需求标准差

C18

=STDEV( B3:M3)

提前期内平均需求

D18

=B18*$M$9

安全库存

E18

=NORMSINV($M$12)*C18*SQRT(M9)

重新订货点

F18

=D18+E18

订货量

G18

=SQRT(2*B18*$M$10/$M$11)

平均库存水平

H18

=G18/2+E18

固定成本

I18

=$M$10*B18*52/G18

库存成本

J18

=52*H18*$M$11

运输成本

K18

=(C11+B11)*B18*52

总成本

L18

=I18+J18+K18

Sum

B23

=SUM(F20:F24)

计算结果如图5，

图5

KLF公司现有的配送系统是分散化系统，新系统是集中配送系统，其由两个地区的仓库作为中央仓库向五个市场供货。为了找出使得成本最低的运输路线，需要设定好哪两个仓库作为中央仓库。

在未确定中央仓库之前，先假设每个地区仓库成为中央仓库，求每个地区的仓库向各个市场供货的固定成本，库存成本，运输成本及总成本，比如亚特兰大向波士顿供货，其固定成本为：波士顿的平均周需求*52*订货成本/订货量，即固定成本与分散化系统配送的固定成本一样，库存成本也与分散化系统的库存成本一样，运输成本则为：波士顿的平均周需求*52*亚特兰大向波士顿供货的单位运输成本，而亚特兰大向波士顿供货的总成本则为：分散化系统配送的固定成本+分散化系统的库存成本+亚特兰大向波士顿供货的单位运输成本，其他的运输路线的成本计算与此类似。另外，假定的中央仓库向本地区的市场供货的成本与分散化系统的成本一样。

表格及单元格公式设置如下：

图6

表6 假定的各个仓库为中央仓库的情况下，各中央仓库的总成本单元格公式设置

中央仓库

市场

单元格

公式

亚特兰大

亚特兰大

B28

=L18

波士顿

C28

=I19+J19+52*B19*(B11+G11)

芝加哥

D28

=I20+J20+B20*52*(B11+H11)

达拉斯

E28

=I21+J21+B21*52*(B11+I11)

洛杉矶

F28

=I22+J22+B22*52*(B11+J11)

波士顿

亚特兰大

B29

=I18+J18+B18*52*(B12+F12)

波士顿

C29

=L19

芝加哥

D29

=I20+J20+B20*52*(B12+H12)

达拉斯

E29

=I21+J21+B21*52*(B12+I12)

洛杉矶

F29

=I22+J22+B22*52*(B12+J12)

芝加哥

亚特兰大

B30

=I18+J18+B18*52*(B13+F13)

波士顿

C30

=I19+J19+B19*52*(B13+G13)

芝加哥

D30

=L20

达拉斯

E30

=I21+J21+B21*52*(B13+I13)

洛杉矶

F30

=I22+J22+B22*52*(B13+J13)

达拉斯

亚特兰大

B31

=I18+J18+B18*52*(B14+F14)

波士顿

C31

=I19+J19+B19*52*(B14+G14)

芝加哥

D31

=I20+J20+B20*52*(B14+H14)

达拉斯

E31

=L21

洛杉矶

F31

=I22+J22+B22*52*(B14+J14)

洛杉矶

亚特兰大

B32

=I18+J18+B18*52*(B15+F15)

波士顿

C32

=I19+J19+B19*52*(B15+G15)

芝加哥

D32

=I20+J20+B20*52*(B15+H15)

达拉斯

E32

=I21+J21+B21*52*(B15+I15)

洛杉矶

F32

=L22

计算结果如图7,

图7

采用建模的方法，把已知的情况放入EXCEL单元格，然后使用单元格表示位置是否被选中（即决策变量B36:B40，共5个，0表示未被选中，1表示被选址作为中央仓库），设置决策变量单元格，目标单元格，约束单元格区域，如图8所示，进行规划求解，找出两个仓库组合下的成本最小的组合。见以下图8，

图8

中央仓库位置选择模型中的公式设置如表7所示，

名称

单元格

公式

总数

B41

=SUM(B36:B40)

D41

=SUM(D36:D40)

E41

=SUM(E36:E40)

F41

= SUM(F36:F40)

G41

= SUM(G36:G40)

H41

= SUM(H36:H40)

总成本

D44

=SUMPRODUCT(B28:F32,D36:H40)

调用规划求解宏模块，规划求解宏模块的参数设置如下：

目标单元格设置为：$D$44，为求最小值，可变单元格设置为：$B$36:$B$40,$D$36:$H$40。

约束条件设置为：$B$36:$B$40=二进制，$D$36:$H$40=二进制，

$B$36:$B$40>= $D$36:$D$40

$B$36:$B$40>= $E$36:$E$40

$B$36:$B$40>= $F$36:$F$40

$B$36:$B$40>= $G$36:$G$40

$B$36:$B$40>= $H$36:$H$40

$B$41=$B$43，$D$41:$H$41= $D$43:$H$43

单击“求解”，规划求解的结果如图9所示，

结论：最优化方案为选芝加哥和洛杉矶作为中央仓库的组合成本最小。且配送选择为洛杉矶向4个市场供货。芝加哥向1个市场供货，最小成本为447645.981，较分散配送的总成本481346.31小。因此，推荐选择新系统，即选芝加哥和洛杉矶作为中央仓库的组合成本最小。

问题c:

采用UPS陆上服务，可以保证产品在3天内到达中央仓库（0.5周）。当然在这样的情况下，从制造厂到仓库的单位运输成本近来增加，事实上，这个例子中，运输成本将增加50%。

据题意，先计算工厂到各个仓库的单位产品运输成本，即入库成本,如表 8所示：

表8 单位产品运输成本

仓库

入库

出库

亚特兰大

18

13

波士顿

17.25

13

芝加哥

16.5

13

达拉斯

13.5

13

洛杉矶

10.5

13

由于使用集中化配送系统，芝加哥向波士顿1个市场供货，洛杉矶向其余的4个市场供货,而工厂到这2大中央仓库(配送城市)的运输改为UPS后，提前期由以前的2周变成0.5周,这样各项指标也随之而变化,但是计算方法三一样的。其中，需要特别注意的是两个中央仓库的平均周计划如下：

芝加哥的平均周需求为其所配送的市场的平均周需求，即波士顿的平均周需求，而洛杉矶的平均周需求为波士顿以外4个市场的平均周需求之和。各个中央仓库的运输成本是由工厂到中央仓库运输成本与中央仓库到各个市场的运输成本构成。以下为所求的各项指标的EXCEL的单元格公式设置，

表9 使用UPS服务，0.5周到达2个中央仓库情况下，芝加哥中央仓库的各项指标单元格公式设置，

名称

单元格

公式

平均周需求

B55

=B19

周需求标准差

C55

=C19

提前期内平均需求

D55

=B55*$M$46

安全库存

E55

=NORMSINV($M$49)*C55*SQRT($M$46)

重新订货点

F55

=D55+E55

订货量

G55

=SQRT(2*$M$47*B55/$M$48)

平均库存水平

H55

=G55/$M$46+E55

固定成本

I55

=B55*52/G55*$M$47

库存成本

J55

=52*H55*$M$48

运输成本

K55

=52*B55*(B50+G48)

总成本

L55

=SUM(I55:K55)

Sum

B23

=SUM(L55:L56)

以同样的方法求洛杉矶中央仓库的各项指标，特别留意的是，其运输成本是由工厂到洛杉矶的运输成本与洛杉矶分别配送到4个市场的运输成本构成。

经计算，结果如图10，

把订货提前期改为0.5周，把工厂到各大配送城市的运输改为UPS后，其相关费用各增加50%后,得到此表的总成本为487920.87。比较前后两个方案可知，改动后的方案中，虽然平均库存水平和库存水平都下降了，但是，总成本比之前的方案是增加的，如果以洛杉矶与芝加哥为中心，总成本是增加的，成本比由2周为提前期两个中央仓库配送的成本447645.981 增加到487920.87，其成本也比分散系统配送的成本高，所以，此策略不值得推荐。