Carlton Goods Co Makes Household Appliances Single Manufacturing Facility Expected Demand Q28965634

Carlton Goods Co. makes household appliances at a singlemanufacturing facility. The expected demand for one of theseappliances during the next four months is shown in the table belowalong with the expected production costs and the expected capacityfor producing these items.

Carlton estimates that it costs $2.50 per month for each unit ofthis appliance carried in inventory, estimated by averaging thebeginning and ending inventory levels for each month. For instance,if the beginning inventory of one month is 180 and the endinginventory is 220, the “average” inventory for that month will be(180+220)/2 = 200 and therefore inventory cost will be 2.5 × 200 =500.

Currently, Carlton has 120 units in inventory on hand for thisproduct. To maintain a level workforce, Carlton wants to produce atleast 350 units in each month. It also wants to maintain a safetystock of at least 60 units at the end of each month. Carlton wantsto know how many appliances to manufacture during each of the next4 months to meet expected demand at the lowestcost.  

a)    Formulatethe problem as a linear program. Clearly define your decisionvariables and write the objective function and all constraints inalgebraic form.

b)    Create aspreadsheet model for this problem in Excel and solve it withSolver. Attach three snapshots of your setup:  Finalview, Formula view, and Solver setup.

c)     What is the optimal solution? Whatis the total cost of this production plan?