Southeast Soda Pop, Inc., has a new fruit drink for which it has high hopes. John Mittenthal, the production planner, has assembled the following demand forecast: Q1 1,800, Q2 1,100, Q3 1,600, Q4 900

COSTS/OTHER DATA

Previous quarter's output = 1,300 cases

Beginning inventory = 50 cases

Back-Order cost = $150 per case

Inventory holding cost = $40 per case at end of quarter

Hiring employees = $40 per case

Terminating employees = $80 per case

Subcontracting cost = $60 per case

Unit cost on regular time = $30 per case

Overtime cost = $15 extra per case ($45)

John's job is to develop an aggregate plan. The three initial options he wants to evaluate are:

a. Plan A: a strategy that hires and fires personnel as necessary to produce the current period's forecast. Include any costs due to the change in the production level from the previous output level. Total cost = $ [ Select ] ["314,000", "325,000", "315,000", "$322,000", "315,575"] . What was the total inventory holding cost? $8,000

b. Plan B: a level strategy, producing an average demand rate. Include any costs due to the change in the production level from the previous output level. Cost = $ [ Select ] ["329,000", "259,500", "$308,500", "328,500", "327,000"] . Was there a need to back-order units? [ Select ] ["back-ordering is not an option in this case", "no, there was no need to back-order", "yes"] . If so, how many units and what was the total back-ordering cost? [ Select ] ["1200 units; $180,000", "400 units; $60,000", "$150", "zero, no need to backorder, all demand levels were met with regular time production", "950 units; cost $142,500"]

c. Plan C: a level strategy that produces 1,200 cases per quarter and meets the forecast demand with inventory and subcontracting (your subcontractor only has the capacity to fill a maximum of 300 units per quarter). Include any costs due to the change in the production level from the previous output level. Total Cost = $ [ Select ] ["225,750", "222,000", "220,567", "330,237", "$257,500"]

d. Which strategy is the lowest-cost plan?

Please show excel formulas. Thank you!