Problem:
Acme estimates it costs $1.50 per month for each unit of this appliance carried in inventory (estimated by averaging the beginning and ending inventory levels each month). Currently, Acme has 120 units in inventory on hand for the product. To maintain a level workforce, the company wants to produce at least 400 units per month. They also want to maintain a safety stock of at least 50 units per month. Acme wants to determine how many of each appliance to manufacture during each of the next four months to meet the expected demand at the lowest possible total cost.
For the following problem, I need the following typed. Must also include copy of Excel Solver Screenshot used to solve the model
a) Formulate LP problem b) Create spreadsheet model (show screen shot from computer, using Excel/Solver. Must actually be a screenshot of the entire table, not just the data from the table)
c) What is optimal solution?
d) How much money could Acme save if they were willing to drop the restriction about producing at least 400 units per month?
Month
|
|
1
|
2
|
3
|
4
|
| Demand |
420
|
580
|
310
|
540
|
| Production cost |
$49.00
|
$45.00
|
$46.00
|
$47.00
|
|
Production Capacity
|
500
|
520
|
450
|
550
|