**This question is from**

*Management Science: The Art of Modeling with Spreadsheets*

Stephen G. Powell and Kenneth R. Baker

John Wiley and Sons 2010 Third Edition

October 15, 2010

**Production planning**

Your company has agreed to produce 8000 ounces of shampoo for a retailer. This product is manufactured in batches that take 5 to 11 days according to the following distribution (this table is also in the file *Shampoo.xlsx*).

Time to make a batch (days) | Probability |

5 | 0.05 |

6 | 0.10 |

7 | 0.20 |

8 | 0.30 |

9 | 0.20 |

10 | 0.10 |

11 | 0.05 |

Only one batch can be made at a time. Each batch yields an uncertain amount of product, ranging between a minimum of 600 ounces and a maximum of 1100 ounces, with a most likely value of 1000 ounces. Finally, 20% of the time a batch fails inspection and the entire batch must be discarded.

Your task is to build a model with which to forecast the *number of batches* and the *number* *of days* that will be required to meet the customer’s order of 8000 ounces.

## Expert Answer

Formula

A2 |
B2 |
C2 |
D2 |
E2 |
F2 |
G2 |

1 |
=RAND() | =$M$2+($M$4-$M$2)*IF(B2<=$M$5,SQRT(B2*$M$5),1-SQRT((1-$M$5)*(1-B2))) | =IFERROR(VLOOKUP(RAND(),$O$2:$P$3,2,TRUE),-1)+1 | =C2*D2 | =SUM($E$2:E2) | =IFERROR(VLOOKUP(RAND(),$I$2:$J$8,2,TRUE),4)+1 |

A3 |
B3 | C3 | D3 | E3 | F3 | G3 |

=IF(OR(F2>=8000,A2=0),0,1) |
=RAND()*A3 | =A3*($M$2+($M$4-$M$2)*IF(B3<=$M$5,SQRT(B3*$M$5),1-SQRT((1-$M$5)*(1-B3)))) | =A3*(IFERROR(VLOOKUP(RAND(),$O$2:$P$3,2,TRUE),-1)+1) | =A3*C3*D3 | =A3*SUM($E$2:E3) | =A3*(IFERROR(VLOOKUP(RAND(),$I$2:$J$8,2,TRUE),4)+1) |

We can simulate the time taken and the number of batches and find the mean value and 95% confidence intervals.

I have simulated them 2000 times and the results and histograms are as following –

No. of batches | Time taken | |

Average | 11.71656687 | 93.36831584 |

Confidence interval (+/ -) | 0.078162631 | 0.673012787 |