Question

Use Excel

A pastry store wants to know how many dozen muffins to bake each day. Every dozen they sell fresh in the shop returns a profit of $5.00. Every dozen they bake but do not sell on the day they are baked is given to a local charity at a loss of $3.00 a dozen. The business is fairly stable in that they never sell less than 50 dozen nor more than 80 dozen muffins. Their sales history, rounded to the nearest ten dozen muffins is as shown:

?Dozens of        Number of Days

Muffins Sold     That many Sold

50                      12

60                    37

70                      45

80                      18

?

Maximum daily muffin baking capacity is 110 dozen muffins, with current staff.

They want to run a 1-year simulation (i.e., 365 days, non-leap year) for daily production rates of 50, 60, 70, and 80 dozen muffins to:

(5 pts) Determine the profit (loss) and identify the ‘Best’ daily production rate

(5 pts) Assess customer service implications (i.e., determine # stockouts, i.e., # occurrences of no muffins to sell).     Identify the Best production rate, to minimize stockout.

Answer 1

Simulation model is following

Formulas:

C2 =B2/$B$6   copy to C2:C5

D3 =D2+C3 copy to D3:D5

B11 =RAND() copy to B11:B375

C11 =LOOKUP(B11,$E$2:$E$5,$A$2:$A$5) copy to C11:C375

D11 =MIN(C11,$B$8) copy to D11:D375

E11 =MAX(0,$B$8-D11) copy to E11:E375

F11 =D11*5-E11*3     copy to F11:F375

C378 =AVERAGE(F11:F375)

The maximum profit is achieved with daily production rate of 70 dozen muffins

Simulation model for assessing the customer service implications based on # of occurrences of stockout is following

Additional formulas:

G11 =IF(C11>$B$8,1,0) copy to G11:G375

C379 =SUM(G11:G375)  

From a customer service perspective, the best production lot size is 80 dozen muffins (which is the maximum daily demand). This production lot size results in 0 occurrences of stockouts.