Question

Please show me how to do this with Excel QM, not Solver.

Astro Winter LLC makes snowplowing equipment. The company buys the engines from a 3^rd party vendor – Marshall Engines. Astro Winter has the following production schedule for February, March, April, and May: February – 60; March – 85; April 100; May – 120.

Marshall Engines makes and delivers the products to Astro Winter during the month the engines are due. Marshall Engines can only manufacture 40 engines in February; 60 in March; 90 in April; and 50 in May. During the same time that Astro Winter makes their snowplows, Marshall Engines has a large order with another client.

Marshall Engine is exploring several options to meet Astro Winter’s production schedule. It can produce up to 30 engines in November, December, and January and carry them in inventory at a cost of $50 per engine per month until it ships them to Astro Winter. For instance, Marshall could build an engine in November, and deliver it to Astro Winter in February and incur $150 in inventory cost. Marshal Engines could also manufacture up to 20 engines in the month they are due on an overtime basis, with an additional cost of $400 per engine.

Marshall Engine wants to determine the least costly production schedule that will meet Astro Winter’s schedule.

Instructions:

1) Develop the LP model and determine the optimal production schedule for Marshall Engine.

Answer 1

GIVEN DATA :

Marshall can produce upto 30 engines in november,December and january and carry them in inventory at a cost of $50 per engine per month.

Marshalls engines could also manufacture upto 20 engines in the month they are due on an overtime basis with an additional cost of $400 per engine.

Solution:

Now consider the following cost analysis,

If marshalls increases november,december,january production from 30 to 40 they can meet out Astro winter may month demand.