USA: +1 725-224-8795 
Sign in
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 |
