aggregate planning linear programming - use excel solver

Industrial Engineering and Engineering Management Department

0405567: Supply Chain Management

Fall 2021 / 2022 Instructor: Dr. Ridvan Aydin Date: 15th November 2021

Case Study Assignment 3 – Due on November 28, 2021 @ 11:59 PM

Note: The assignment can be done with a group of three students. Submit a

single Excel file with your solutions and comments.

Black Eagles is one of the biggest manufacturers of toys. Managers at Black Eagles are

working on planning their demand and supply for the next year accordingly. Monthly demand

forecast in the next 12 months is given as follows:

Month Demand

1 10,000 2 11,000 3 15,000 4 18,000 5 25,000 6 26,000 7 30,000 8 29,000 9 21,000

10 18,000 11 14,000 12 11,000

Capacity at Black Eagles is managed by the number of machine operators they hire. The

company works eight hours a day for 20 days in a month with a regular operating shift. Any

time beyond that is considered overtime. Regular time pay is $15 per employee and overtime

is $22 per hour. Each employee can work overtime up to 20 hours per month. Black Eagles

currently has 250 employees. Two labor hours are needed to produce a toy. The inventory cost

is $3 for a month. Materials cost is $40 for a toy. The retail price of a toy is $125. It is assumed

that no stockouts are allowed and the starting inventory in January is 5,000 units and the desired

ending inventory in December is also 5,000 units.

Note: The cost of hiring a new labor is $1,000; while the cost of laying off a labor is $2,000.

No stockout and no subcontracting is allowed.

According to the market study, a promotion dropping prices by 1% in a given month will

increase sales in that month by 20%, and bring forward 10% demand from each of the following

two months.

a. [50 Points] What is the optimal production plan for the year if we assume no

promotions? What is the annual profit from this plan? What is the cost of this plan?

b. [30 Points] Is it better to promote in April or July? How much increase in profit can be

achieved as a result?

c. [20 Points] If toys are sold for $250 instead of $125, does the decision about the timing

of the promotion change? Why?