BUSI2047 Supply Chain & Operations Planning

Solutions tutorial 1

Problem 1: Calculate MA(3), ES(0.2) for the data in Table 1. Compute MAD, MAPE
and MSE.

Ft - Dt
Period Demand MA(3) et abs(et) (et)2 (abs(et)/Dt)*100
1 200 **
2 225 **
3 175 **
4 202 200.0 -2.0 2.0 4.0 1.0
5 238 200.7 -37.3 37.3 1393.8 15.7
6 356 205.0 -151.0 151.0 22801.0 42.4
7 340 265.3 -74.7 74.7 5575.1 22.0
8 360 311.3 -48.7 48.7 2368.4 13.5
9 332 352.0 20.0 20.0 400.0 6.0
10 350 344.0 -6.0 6.0 36.0 1.7
Sum -299.7 339.7 32578.3 102.3
Average -42.8 48.5 4654.0 14.6
MAD MSE MAPE

Ft - Dt
Period Demand ES(0.2) et abs(et) (et)2 (abs(et)/Dt)*100
1 200 190 -10.0 10.0 100.0 5.0
2 225 192.0 -33.0 33.0 1089.0 14.7
3 175 198.6 23.6 23.6 557.0 13.5
4 202 193.9 -8.1 8.1 65.9 4.0
5 238 195.5 -42.5 42.5 1805.9 17.9
6 356 204.0 -152.0 152.0 23103.0 42.7
7 340 234.4 -105.6 105.6 11150.8 31.1
8 360 255.5 -104.5 104.5 10915.6 29.0
9 332 276.4 -55.6 55.6 3089.4 16.7
10 350 287.5 -62.5 62.5 3902.0 17.8
Sum -550.1 597.3 55778.7 192.4
Average -55.0 59.7 5577.9 19.2
MAD MSE MAPE

Problem 2: Company OLDCO manufactures product Y. The demand for Y is

stationary over time, but with a seasonal effect. The demand in weeks 1 to 8 is shown
in Table 2.

Table 2: Weekly demands for product Y.

Week 1 2 3 4 5 6 7 8
Demand 25 50 75 30 20 45 80 35

The series is stationary but with seasonal effect. A plot of the demand data shows that
there are two seasons of four periods long.

1
 100
80

Demand
60
40
20
0
0 2 4 6 8
Week

The average demand for Y is 45 units per week. Dividing the demand of periods 1 to
8 by the average weekly demand gives:
Week 1: 0.556 Week 5: 0.444
Week 2: 1.111 Week 6: 1
Week 3: 1.667 Week 7: 1.778
Week 4: 0.667 Week 8: 0.778
We obtain the seasonal factors by averaging the ratios (actual demand) / (average
demand) of corresponding periods in each season. This gives:
Seasonal factor 1: 0.5
Seasonal factor 2: 1.06
Seasonal factor 3: 1.72
Seasonal factor 4: 0.72
We can obtain a forecast for the demand in any period of the season by multiplying
the average demand by the corresponding seasonal factor. For the next eight periods
we find: 23; 48; 78; 33; 23; 48; 78 and 33. (Forecasts rounded to the closest integer).

Problem 3:
Assume it is now time t, and we make a forecast for t+1.
The weight associated in MA(5) with the observation 4 years ago (i.e. the oldest
observation) is 0.2; Hence, he should adjust his forecast by -200*0.2 =-40
In case he uses ES(0.2), the weight for Dt-4 is (1-)4; hence he should adjust the
forecast by -200(0.2)(1-0.2)4 = -16.384

Problem 4:
(a) Plot: The time series shows a clear trend. ES & MA are not
recommended because these approaches are for stationary series without
a strong trend.

2
 80

70

60

50

40 Sales (000)
Ft,t+1
30

20

10

0
2008 2010 2012 2014 2016 2018 2020

(b & c) Calculations shown below:

Year Sales (000) St Gt Ft,t+1 error abs error abs % error error2
0.00 8.00
2010 7 7.80 7.98 8.00 1.00 1.00 14.29 1.00
2011 14 15.42 7.94 15.78 1.78 1.78 12.71 3.17
2012 22 23.09 7.92 23.37 1.37 1.37 6.22 1.87
2013 31 31.01 7.92 31.01 0.01 0.01 0.04 0.00
2014 40 39.14 7.94 38.93 -1.07 1.07 2.68 1.15
2015 50 47.66 8.00 47.08
2016 65 57.53 8.18 55.66
2017 70 66.57 8.27 65.71
2018 74.84 MAD MAPE MSE
1.05 7.19 1.44

(d) the forecast for 2018 at the end of 2014 is 39.14 + 4*7.94 = 70.89
This forecast could be inaccurate if the trend changes (trend extrapolation).

Problem 5:

K = $100 Hex Nuts Molly Screws

I= 25% per year
c = $.15 c = $38
 = 20,000 per year  = 14,000 per year

For hex nuts: Q*1 = = 10,328 units

*
T1 = Q 1 / = .5164 years

*
For molly screws: Q 2 = = 5,429 units

3
 *
T2 = Q 2/ = .3878 years

Problem 6:

(a) Monthly demand is normal ( = 28,  = 8)

ι = 14 weeks = 3.5 months  Lead time demand ~ normal
with  = (28)(3.5) = 98  = (8) 3.5 = 15

h = Ic = (.3)(6) = 1.8  = (28)(12) = 336 per year

K = 15

(2)(336)(15)
Q = EOQ = = 75
1.8

1 - F(R) = .10, from the table we find z = 1.28, R = z +  = (15)

(1.28) + 98 = 117

(Q,R) = (75,117)

(b) The safety stock is 19 units

Problem 7:

(a) Repeat orders, hence use (R, Q) model e.g. type 1 service level of 95%.
Calculate R and Q via managerial approach:
Q = EOQ = sqrt(2*50*200/1) = 141
R = demand during lead time + safety stock = 2*200 + safety stock
Safety stock = z*standard dev demand during lead time = z*40*sqrt(2)
For type 1 service of 95%: z = 1.63
Hence, safety stock = 1.63*40*sqrt(2) = 92.2
Reorder point is then 400 + 92 = 492

(b) Deterministic constant demand (no need for safety stock)

Q = EOQ = 141; R = demand during leadtime = 400

Problem 8:
Make (POQ model): optimal cost = sqrt(2*K*demand rate*holding cost *(1-demand
rate/production rate)) = sqrt(2*110*5000*20*0.25*(1-5000/20000)) = £2031 per year.
We also need to add the production cost = 5000*20 = 100,000 so the total cost is
£102,031 per year.

Buy (EOQ model): optimal cost = sqrt(2*K*demand rate*holding cost) =

sqrt(2*25*5000*30*0.25) = £1369 per year. We also need to add the purchase cost =
5000*30 = 150,000 so the total cost is £151,369 per year.

Problem 9:

4
 (a) See calculations below
Min no of workers
Month Days Forecast (00) Net Cum Net Productivity Cum Min workers needed
per worker Productivity

Starting inv = 4500

April 21 8500 4000 4000 96.9234 96.9234 41.2697037
May 22 9300 9300 13300 101.5388 198.4622 67.01528049
June 20 12200 12200 25500 92.308 290.7702 87.69812037
July 23 17600 17600 43100 106.1542 396.9244 108.5849094
August 16 14000 14000 57100 73.8464 470.7708 121.2904454
September 20 6300 6300 63400 92.308 563.0788 112.5952531

productivity 12000 cases by 100 workers over 26 days We need 122 workers;
or 4.6154 cases per worker per day We have 86, so we need to hire 36

4.6154

Production & Inv levels with 122 workers

Month Days Forecast (00) Net production Inventory

Starting inv = 4500

April 21 8500 4000 11824.65 7824.6548
May 22 9300 9300 12387.73 10912.3884
June 20 12200 12200 11261.58 9973.9644
July 23 17600 17600 12950.81 5324.7768
August 16 14000 14000 9009.261 334.0376
September 20 6300 6300 11261.58 5295.6136

Total 39665.4356

Cost Hiring (36 workers) 4500

Inventory (39665 cases) 29749.08

(b) Students should plot cumulative demand and cumulative production over time.
The production levels should plot as a straight line (if they use a continuous time scale
e.g. days).

(c) Better in September because this won’t affect the minimum number of workers
required. Currently we have overproduction in September; even if we had only 15
working days with 122 workers we could easily meet the demand in September. If we
would do the shutdown in April, the minimum number of workers will go up to 128
(August still being the bottleneck). We ignore here everything that happens after
September…

Problem 10:
See spreadsheet tutorial 3

Problem 11:
(part d was not asked – this is a compromise plan)

5
 # hours Jan Feb Mar Apr May
Table 10 20 15 10 12 15
Chairs 5 100 70 60 40 50
Desks 20 10 20 20 30 20
working days 22 15 21 20 23
(Note demand for tables adjusted with starting inventory)

Chase strategy (b)

Aggr forecast (in hours) 900 900 800 920 800
No of hours per worker 176 120 168 160 184
Min # workers 5.11 7.50 4.76 5.75 4.35 Total unit cost
# workers needed 6 8 5 6 5 30 2000
# worker hired (av. 6) 0 2 0 1 0 3 500
# workers fired 0 0 3 0 1 4 1200
Hours available 1056 960 840 960 920 66300
Idle time 156 60 40 40 120 416

Constant workforce (c)

Cum demand 900 1800 2600 3520 4320
Cum hours 176 296 464 624 808
Min constant workforce 5.113636 6.081081 5.603448 5.641026 5.346535
6 7 6 6 6
We need 7 workers Total unit cost
# workers needed 7 7 7 7 7 35 2000
# worker hired (av. 6) 1 0 0 0 0 1 500
# workers fired 0 0 0 0 0 0 1200
Hours available 1232 840 1176 1120 1288 70500
Idle time 332 -60 376 200 488 1336
(60 hours work needed from January,
so there will be some inventory carried over from Jan to Feb)

Compromise plan (d) retain workforce of 6 and hire one more just for Feb
Aggr forecast (in hours) 900 900 800 920 800
No of hours per worker 176 120 168 160 184
Total unit cost
# workers needed 6 7 6 6 6 31 2000
# worker hired (av. 6) 0 1 0 0 0 1 500
# workers fired 0 0 1 0 0 1 1200
Hours available 1056 840 1008 960 1104 63700
Idle time 156 -60 208 40 304 648
(60 hours work needed from January,
so there will be some inventory carried over from Jan to Feb)

Problem 12:
(a) see table below: we first calculate the time phased net requirements for A and B
(taking into account the inventory at period 3). In the same way we calculate the time
phased net requirements for X, taking into account that 5 units X will arrive in period
4. The gross requirements for X are the same as the time phased net req for A. Next
we calculate the total gross requirements for Y: these are: twice the time phased net
req for X plus once the time phased net req for B. Accounting for the scheduled
receipts in week 50, we obtain the total net req for Y. By shifting everything one week
earlier we get the time phased net req for Y (= lot for lot).

6
 Week 1 2 3 4 5 6 7 8 9 10 11
Gross req A 25 50 50 25 75 50 10 60
Inv A 30 5 0
Net req A 0 45 50 25 75 50 10 60
Time phased Net A 45 50 25 75 50 10 60
Gross req B 35 40 10 40 10 20 5 70
Inv B 25 0
Net req B 10 40 10 40 10 20 5 70
Time phased Net B 10 40 10 40 10 20 5 70
Gross req X 45 50 25 75 50 10 60
Scheduled receipts 5
Net req X 40 50 25 75 50 10 60
Time phased Net X 40 50 25 75 50 10 60
Gross req Y (for X) 80 100 50 150 100 20 120
Gross req Y (for B) 10 40 10 40 10 20 5 70
Total gross Y 90 140 60 190 110 40 125 70
Scheduled receipts 50
Net req Y 90 140 60 140 110 40 125 70
Time phased net Y 90 140 60 140 110 40 125 70
= lot-for lot

Problem 13:
(a) The product structure diagram of product Y:

Y
1 week

X (2) U (1)
1 week 2 weeks

V (1) W (1) X (1) T (1)

0 weeks 0 weeks 1 week 0 weeks

V (1) W (1)
0 weeks 0 weeks

(b) Forecasting: The series is stationary but with seasonal effect. A plot of the
demand data shows that there are two seasons of four periods long.

7
 100
80

Demand
60
40
20
0
0 2 4 6 8
Week

The average demand for Y is 45 units per week. Dividing the demand of
periods 1 to 8 by the average weekly demand gives:
Week 1: 0.556 Week 5: 0.444
Week 2: 1.111 Week 6: 1
Week 3: 1.667 Week 7: 1.778
Week 4: 0.667 Week 8: 0.778

We obtain the seasonal factors by averaging the ratios (actual demand) /

(average demand) of corresponding periods in each season. This gives:
Seasonal factor 1: 0.5
Seasonal factor 2: 1.06
Seasonal factor 3: 1.72
Seasonal factor 4: 0.72

We can obtain a forecast for the demand in any period of the season by
multiplying the average demand by the corresponding seasonal factor. For the
next four periods we find: 23; 48; 78; 33 (Forecasts rounded to the closest
integer).

(c) The MRP calculus is shown in the table below:

Week 1 2 3 4 5 6 7 8 9 10 11 12
Gross requirements Y 0 0 0 0 20 45 80 35 23 48 78 33
End inventory Y 15 0
Net requirements Y 0 0 0 0 5 45 80 35 23 48 78 33
Time phased net
0 0 0 5 45 80 35 23 48 78 33
requirements Y
Planned order release
0 0 0 5 45 80 35 23 48 78 33
(L4L) Y
Gross requirements U 0 0 0 5 45 80 35 23 48 78 33
Net requirements U 0 0 0 5 45 80 35 23 48 78 33
Time phased net
0 5 45 80 35 23 48 78 33
requirements U
Planned order release
0 5 45 80 35 23 48 78 33
(L4L) U
Gross requirements 1 X
0 0 0 10 90 160 70 46 96 156 66
(from Y)
Gross requirements 2
0 5 45 80 35 23 48 78 33
X (from U)
Total gross requirements 0 5 45 90 125 183 118 124 129 156 66

8
X
Inventory X 60
Net requirements X 0 0 0 80 125 183 118 124 129 156 66
Time phased net
0 0 80 125 183 118 124 129 156 66
requirements X

Problem 14:
(Improvement step is not required.)

(a) Zero inventory plan

Units per Min
Month Days Demand Hired Fired Production Inventory
worker workers
July 21 105 1020 10 2 1050 30
Aug 22 110 950 9 1 990 70
Sept 21 105 800 8 1 840 110
Oct 23 115 1000 9 1 1035 145
Nov 19 95 1250 14 5 1330 225
Dec 20 100 650 7 7 700 275
Total 126 5670 57 6 11 5945 855

Cost hiring 6*500 3000

firing 11*750 8250
salary 57*1500 85500
production 200*5945 1189000
inventory 5*855 4275
total 1290025

9
(a) Improved Zero inventory plan (this was not asked - but we can reduce the number of workers when the
inventory we build up becomes larger than the productivity of a worker that month. The first month where
this happens is in Sept (inventory is 110 and productivity is 105).
Reducing the number of workers in Sept to 7:
Units per Min
Month Days Demand Hired Fired Production Inventory
worker workers
July 21 105 1020 10 2 1050 30
Aug 22 110 950 9 1 990 70
Sept 21 105 800 7 2 735 5
Oct 23 115 1000 9 2 1035 40
Nov 19 95 1250 14 5 1330 120
Dec 20 100 650 7 7 700 170
Total 126 5670 56 7 12 5840 435

And again, we can reduce the number of workers in November from 14 to 13

Units per Min
Month Days Demand Hired Fired Production Inventory
worker workers
July 21 105 1020 10 2 1050 30
Aug 22 110 950 9 1 990 70
Sept 21 105 800 7 2 735 5
Oct 23 115 1000 9 2 1035 40
Nov 19 95 1250 13 4 1235 25
Dec 20 100 650 7 6 700 75
Total 126 5670 55 6 11 5745 245

Cost hiring 6*500 3000

firing 11*750 8250
salary 55*1500 82500
production 200*5745 1149000
inventory 5*245 1225
total 1243975

(b) Develop a constant workforce plan when no backorders are allowed.

What is its cost? Plot the demand and the production cumulatively over
time.
We need 10 workers.

10
(b) Minimum constant workforce plan - compare cumulative demand and cumulative productivity
Units per Units per Demand Workers Production
Month Days Demand Inventory
worker worker cumul. needed (10 workers)
July 21 105 1020 105 1020 9.71 1050 30
Aug 22 110 950 215 1970 9.16 1100 180
Sept 21 105 800 320 2770 8.66 1050 430
Oct 23 115 1000 435 3770 8.67 1150 580
Nov 19 95 1250 530 5020 9.47 950 280
Dec 20 100 650 630 5670 9 1000 630
Total 126 5670 6300 2130

Cost hiring 0 0 We need 10 workers

firing 2*750 1500
salary 60*1500 90000
production200*6300 1260000
inventory 5*2130 10650
total 1362150

7000

6000

5000

4000

3000

2000

1000

0
0 20 40 60 80 100 120 140

Demand Production (10 workers)

The production is always larger than the demand. This indicates that there are
no backorders and that there is some ending inventory in each period.

(c) Is it possible to construct a constant workforce plan requiring fewer

workers, if backorders were allowed? Explain and illustrate your answer
graphically (without detailed calculations). [20%]

Yes, dividing the total demand (5670) by the productivity per worker over
126 days (5*126 = 630) shows that we can meet the demand by employing
only 5670/630 = 9 workers.

11
 with backorders - 9 workers
6000

5000

4000

3000

2000

1000

0
0 20 40 60 80 100 120 140

Demand Production (9 workers)

The production curve plots as a straight line. It also intersects the cumulative
demand curve: when demand > production, we have backorders; when demand
< production, we have ending inventory.

(d) Due to a long-range capacity plan, the assembly department will be

relocated, which requires the department to shut down. There are two possible
times for this to happen. One is to shut down seven days in November, and
the other will necessitate a ten-day shutdown in December. Based on the
constant workforce plan in (b), when would you schedule the shutdown?
Why? [20%]

A seven-day shutdown in November raises the minimum number of

workers to 11. Employing a constant workforce of 11 workers will result in
much higher ending inventory and also higher salary costs. For a ten day
shutdown in December, it is still feasible to meet demand (without
backorders) using only 10 workers. The shutdown in December seems thus
preferable (although it is at the end of the planning period and we do not
know what demand in January or February will be.)

Problem 15:
See spreadsheet on Moodle

Problem 16:
See spreadsheet on Moodle

12
Problem 17 (Extra problem on Inventory Mgmt):

The ABC Company purchases parts 1 and 2 from suppliers 1 and 2 respectively. Part
1 has a constant demand of 200 units per month. The monthly demand for part 2 is
normally distributed with mean 200 units and standard deviation 40 units. The lead
time for both suppliers is 1 month. The fixed cost of placing an order (from supplier 1
or 2) is £25. The holding cost for part 1 is £0.50 per month and the holding cost for
part 2 is £0.35 per month.

(a) Calculate the optimal order quantity for part 1. What is the reorder point
for part 1? Calculate the annual cost of controlling part 1 according to
this policy.
(b) Calculate order quantity and reorder point for part 2 assuming that you
want to achieve a type 1 service level of 95%. What is the safety stock
for part 2? What type 2 service level does this policy achieve?
(c) Calculate the order quantity and reorder point for part 2 assuming that
you want to achieve a type 2 service level of 95%? What is the safety
stock for part 2? What type 1 service level does this policy achieve?
(d) Redo all the calculations but now assume that the procurement lead time
is 3 months.

(a) Part 1 has constant deterministic demand, so we use the EOQ model:
EOQ = sqrt(2*25*200/0.5) = 141 units
R = demand during lead time = 200x1 = 200 units
Optimal monthly cost = sqrt(2*25*200*0.5) = £70.71 or £848.53 per year

(b) Part 2 has stochastic demand, so we use the (R, Q) model:

Q = EOQ = sqrt(2*25*200/0.35) = 169 units
R = expected demand during lead time + safety stock = 200x1 + z*
The standard deviation of demand during lead time (1 month) is given:  = 40
The z factor for a type 1 service level (probability of no stock out) of 95% we find in
the table of the standard normal distribution: F(z) = 0.95 gives z ≈ 1.64
The safety stock is 1.64*40 ≈ 66
The reorder point R is 200 + 66 = 266

What is the corresponding type 2 service level? A z value of 1.64 corresponds to an

L(z) value of 0.0211 (see Table standard normal distribution). The expected number
of units short n(R) = L(z) = 0.0211x40 = 0.844
The corresponding type 2 service level (fill rate) is 1 - n(R)/Q = 1 – 0.844/169 = 0.995
or 99.5%

(c) Part 2 has stochastic demand, so we use the (R, Q) model:

Q = EOQ = sqrt(2*25*200/0.35) = 169 units
R = expected demand during lead time + safety stock = 200x1 + z*
The standard deviation of demand during lead time (1 month) is given:  = 40
For the z factor for a type 2 service level (fill rate) of  = 95% we need to calculate
first the number of units short per cycle: n(R) = (1-)Q = 0.05x169 = 8.45

13
n(R) is linked to the partial expectation (or standard loss function) L(z) of the standard
normal distribution via the relation: n(R) = L(z), or L(z) = 8.45/40 = 0.21125;
A value L(z) = 0.21125 (see Table) corresponds to a z value between 0.45 and 0.46
(and corresponding F(z) values of 0.6736 and 0.6772). Let’s take z = 0.46, then the
safety stock is 0.46x40 = 18.4 units ≈ 18 units, and the reorder point R = 200+18 =
218. The corresponding type 1 service level for this policy is 67.72% (see the F(z)
value for z = 0.46).

(d) See changes highlighted: only changes in the reorder point! Note that for parts (b)
and (c) the safety stock is scaled according to the square root of the lead time
(see slide 32 in the inventory management lecture)

Part (a) revised: R = demand during lead time = 200x3 = 600 units
Part (b) revised:
R = expected demand during lead time + safety stock = 200x3 + z*
The standard deviation of demand during lead time (3 months) is:  = 40sqrt(3) =
69.3
The z factor for a type 1 service level (probability of no stock out) of 95% we find
in the table of the standard normal distribution: F(z) = 0.95 gives z ≈ 1.64
The safety stock is 1.64*69.3 ≈ 114
The reorder point R is 600 + 114 = 714

What is the corresponding type 2 service level? A z value of 1.64 corresponds to

an L(z) value of 0.0211 (see Table standard normal distribution). The expected
number of units short n(R) = L(z) = 0.0211x69.3 = 1.462
The corresponding type 2 service level (fill rate) is 1 - n(R)/Q = 1 – 1.462/169 =
0.991 or 99.1%

Part (c) revised:

R = expected demand during lead time + safety stock = 200x3 + z*
The standard deviation of demand during lead time (3 months) is:  = 40sqrt(3) =
69.3
For the z factor for a type 2 service level (fill rate) of  = 95% we need to
calculate first the number of units short per cycle: n(R) = (1-)Q = 0.05x169 =
8.45
n(R) is linked to the partial expectation (or standard loss function) L(z) of the
standard normal distribution via the relation: n(R) = L(z), or L(z) = 8.45/69.3 =
0.1219;
A value L(z) = 0.1219 (see Table) corresponds to a z value around 0.79 (and
corresponding F(z) ≈ 0.7852). The safety stock is 0.79x69.3 = 54.7 units ≈ 55
units, and the reorder point R = 600+55 = 655. The corresponding type 1 service
level for this policy is 78.52% (see the F(z) value for z = 0.79).

14