“KING MANUFACTURING

King Manufacturing makes a variety of household appliances at a single manufacturing facility. The expected demand for one of these
appliances during the next six months is shown in Table 1 along with the
expected production costs (per unit) and the expected capacity available for
this item.

Table 1. Demand, production costs and production
capacities by month

Month

Jan

Feb

Mar

Apr

May

Jun

Demand

530

680

390

660

440

700

Production Cost

$49.00

$45.00

$46.00

$47.00

$48.75

$48.25

Production Capacity

600

625

550

650

675

625

King estimates that the cost of holding
inventory is $2.50 per unit per month (calculated based on average monthly
inventory, i.e., the average of beginning inventory and ending inventory). King estimates that it will begin January
with 180 units in inventory and wants to maintain a minimum safety stock
inventory of 100 units at the end of each month. For considerations of workforce stability,
King will require a minimum production level of 500 units each month.

Create a
Solver-based spreadsheet model to determine the number of units to be produced
each month in order to cover demand at the minimum total cost (production plus
inventory holding) and solve the model.

Comments and
Hints:

Create 12 formulas cells for productions
costs (6 cells) and inventory costs (6 cells) for each month and use these to
create the formula for total cost.KING
MANUFACTURINGKing Manufacturing makes a variety of
household appliances at a single manufacturing facility. The expected demand for one of these
appliances during the next six months is shown in Table 1 along with the
expected production costs (per unit) and the expected capacity available for
this item.Table 1. Demand, production costs and production
capacities by monthMonthJanFebMarAprMayJunDemand530680390660440700Production Cost$49.00$45.00$46.00$47.00$48.75$48.25Production Capacity600625550650675625King estimates that the cost of holding
inventory is $2.50 per unit per month (calculated based on average monthly
inventory, i.e., the average of beginning inventory and ending inventory). King estimates that it will begin January
with 180 units in inventory and wants to maintain a minimum safety stock
inventory of 100 units at the end of each month. For considerations of workforce stability,
King will require a minimum production level of 500 units each month.Create a
Solver-based spreadsheet model to determine the number of units to be produced
each month in order to cover demand at the minimum total cost (production plus
inventory holding) and solve the model.Comments and
Hints:Create 12 formulas cells for productions
costs (6 cells) and inventory costs (6 cells) for each month and use these to
create the formula for total cost.”