You have an option buy a prepaid weekly (Monday through Friday) lunch plan at your office.  You can buy up to five lunches per week at a cost of $10 per lunch and only you can use the pre-paid lunches.  For example, if you buy four pre-paid lunches the weekly cost is $40.  Any pre-paid lunches not use by Friday are lost.  If you run out of pre-paid lunches any week you can pay out of pocket for lunch and the cost for one non-prepaid lunch is normally distributed with a mean of $12.50 and a standard deviation of $2.00.  For example, if you prepay for two lunches and eat lunch at the office four times in a week you will pay for two lunches out of pocket.
 The expected cost and standard deviation of weekly cost for non-prepaid lunches is calculated for you in Excel and shown below:   For example, the cost of two non-prepaid lunches is normally distributed with a mean and standard deviation of $25.00 and $2.83 respectively.  The estimated discrete probability distribution for total lunches eaten at the office weekly.   Inputs are shown to the left.

Create a simulation with 100 trials for purchasing 0 to 5 lunches per week.  

How many PrePaid lunches should be purchased to yield the lowest expected cost?

What is the expected cost of the lowest cost plan?

Transcribed Image Text:

1 Lunch simulation 2 3 Inputs 4 Prepaid cost per lunch (fixed) $10 Trials 1000 5 Cost of one non-prepaid lunch (normally distributed) 6 Mean cost $12.50 Standard deviation of cost $2.00 8 Non-prepaid lunches Expected StDev weekly eaten at work weekly weekly cost cost 10 $0.00 $0.00 11 1 $12.50 $2.00 12 2 $25.00 $2.83 13 3 $37.50 $3.46 14 4 $50.00 $4.00 15 $62.50 $4.47 Total lunches eaten at 16 office Probability 17 0.05 18 0.10 19 2 0.25 20 3 0.30 21 4 0.20 22 0.10 23 24 Summary measures (from data table below) 25 Prepaid lunched purchased 2 4 26 Average total cost 27 StDev of total cost 28 Lower bound 95% confidence interval 29 Upper bound 95% confidence interval 30 31 Simulation trial - one week 32 Prepaid lunches purchased 3 33 Cost of prepaid lunches 34 0.67 35 Total lunches eaten at work (Discrete - use B34 in calculation) 36 Non pre-paid lunches eaten at work 37 Expected total cost of non-prepaid lunches 38 Standard deviation of non-prepaid lunches 39 0.40 Total cost of non-prepaid lunches (Normal - use B39 in calculation) (Note: if zero non-prepaid lunches are bought then 40 cost is zero) 41 Total cost of lunches 42 43 Data table 44 45 3. 2. 1. 1 Lunch simulation 2 3 Inputs 4 Prepaid cost per lunch (fixed) $10 Trials 1000 5 Cost of one non-prepaid lunch (normally distributed) 6 Mean cost $12.50 Standard deviation of cost $2.00 8 Non-prepaid lunches Expected StDev weekly eaten at work weekly weekly cost cost 10 $0.00 $0.00 11 1 $12.50 $2.00 12 2 $25.00 $2.83 13 3 $37.50 $3.46 14 4 $50.00 $4.00 15 $62.50 $4.47 Total lunches eaten at 16 office Probability 17 0.05 18 0.10 19 2 0.25 20 3 0.30 21 4 0.20 22 0.10 23 24 Summary measures (from data table below) 25 Prepaid lunched purchased 2 4 26 Average total cost 27 StDev of total cost 28 Lower bound 95% confidence interval 29 Upper bound 95% confidence interval 30 31 Simulation trial - one week 32 Prepaid lunches purchased 3 33 Cost of prepaid lunches 34 0.67 35 Total lunches eaten at work (Discrete - use B34 in calculation) 36 Non pre-paid lunches eaten at work 37 Expected total cost of non-prepaid lunches 38 Standard deviation of non-prepaid lunches 39 0.40 Total cost of non-prepaid lunches (Normal - use B39 in calculation) (Note: if zero non-prepaid lunches are bought then 40 cost is zero) 41 Total cost of lunches 42 43 Data table 44 45 3. 2. 1.