KAU Supply Chain Configuration Excel Problems
Consider a supply chain configuration problem with the following four stages.
The parameters for the supply chain are provided in the table below:
Cycle service level 
0.98 
zvalue 
2.05 
Horizon length (periods) 
52 
Holding cost rate 
35% 
Average demand per period 
100 
Standard Deviation of Demand per period 
180 
Each stage functions can be satisfied with two options: a slow option or a fast option. The details of these options are provided below:
Stage 1 (RM) 
Stage 2 (CM) 
Stage 3 (FM) 
Stage 4 (Dist) 

Slow option’s cost 
$140 
$100 
$200 
$30 
Slow option’s lead time 
90 
20 
30 
20 
Fast option’s cost 
$200 
$125 
$250 
$45 
Fast option’s lead time 
20 
5 
20 
5 
Answer the following questions about the formulation of this supply chain configuration problem.
Please note: Stage 4 must quote an outgoing service time of zero, by assumption.
Problem VI.2.e
Note that in an optimal solution, holding a safety stock at a stage is equivalent to setting the outgoing service time to zero.
Problem VI.2.f
We will now consider a configuration in which each stage selects the slow option and only stage 4 will hold a safety stock. That is, stages 1,2, and 3 hold a zero safety stock. For this model, a stage will hold zero safety stock when we set its outgoing service time equal to its incoming service time plus the process lead time.
What are the outgoing service times for each stage, assuming each stage selects the slow option?
Problem VI.2.
Problem VI.2.h
What are the service times?
Hint: remember that the optimal service time is either zero or equal to the replenishment time for the stage; hence you need only evaluate 2^3 = 8 possible settings for these service times.
Now use the spreadsheet to identify the supply chain configuration that minimizes the response time (i.e., chooses the fastest option at each stage).
Problem VI.2.
Suppose each stage now holds a safety stock (service time = 0). What is the total landed cost?
Problem VI.2.j
0.0/4.0 points (graded)
For the configuration in which each stage selects the fast option, suppose that only stage 4 will hold a safety stock. That is, stages 1,2 and 3 hold a zero safety stock.
What are the outgoing service times for each stage, assuming each stage selects the fast option?
Problem VI.2.k
Problem VI.2.
What are the service times?
Hint: remember that the optimal service time is either zero or equal to the replenishment time for the stage; hence you need only evaluate 2^3 = 8 possible settings for these service times.
Stage 1
Stage 2:
stage 3:
Stage 4
Total landed cost:
Use the spreadsheet to optimize supply chain configuration cost, defined as the selected options and safety stock levels that minimize total landed cost to answer the following questions:
(Note: for each stage there are 2 configuration options, slow or fast, so you have 2^4 = 16 possible configurations. You also have 2^3 = 8 possible safety stock placement strategies. So, in theory, there are 16*8 = 144 possible configurations to consider. However, from the prior examples, you should now have a good idea as to what the best safety stock placement strategy is; you can use this insight to reduce the number of combinations that you consider.)
Problem VI.2.m
Problem VI.2.o
For this configuration, what are the outgoing service times?
Stage 4:
Problem VI.2.p