Question

2. ABC Farm Patch sells a carton of vegetables for $50 each. For each day, they estimate that they will sell 15, 25, 35, or 45 cartons with respective probabilities of 0.35, 0.25, 0.20, and ... (figure it out). They can only produce cartons in lots of ten from their farms. And, 10, 20, 30, 40, and 50 cartons cost $40, 39, 37, 36, and 34 per carton respectively. Every day, ABC's has a clearance sale and will get rid of any unsold cartons for $24 each. Any customer that comes in during the day to buy a carton, but is unable to, costs ABC's $6 in lost goodwill. Find the best production size by performing a simulation using MS Excel. Summarize your results in a MS Word file.

Answer 1

Cost per carton of Vegetables	Lot size
40	10
39	20
37	30
36	40
34	50

	Price
Selling price of each carton	$50
Selling price of unsold cartons	$24
Cost of Goodwill Loss	$6

Demand Estimate	Probability
15	0.35
25	0.25
35	0.2
45	0.2

Expected Demand = 15*0.35 + 25*0.25 + 35*0.2 + 45*0.2 = 28

Orders Must be placed at a lot size of 10

Therefore , considering expected demand of 28, we need to either order 20 or 30 cartons

Scenario 1 : When the lot size of 20 is ordered:

Expected Demand = 28

Cost of 20 cartons = 20* 39 = $780

Cost of Goodwill loss = 8 * 6 = $48

Total cost = $780 + $48 = $828

Selling Price of each carton = $50

Total Sales = 20 *50 = $1000

Profit = 1000 - 828 = $172.

Scenario 2: A lot size of 30 is ordered:

Expected Demand = 28

Cost of 30 cartons = 30 * 37 = $1110

Total cost = $1110

Selling Price of each Cartons = $50

Selling Price of 28 Cartons= $50*28 = $1400

Selling Price of each leftover Cartons = $24

Selling Price of 2 leftover Cartons = $24*2= $48

Total Sales = $1400 + $48 = $1448

Profit = $1448 - $1100 = $348.

Therefore, the second scenario should be opted as it generates more profit as compared to scenario 1.