Q&H is a major detergent manufacturer with a demand forecast for the coming year as shown in the following table $($in tons$).$ Capacity at Q&H is governed by the number of employees it hires. The firm works $20$ days a month, with a regular operating shift of eight hours per day. Any time beyond that is considered overtime. Regular$-$time pay is $$20$ per hour and overtime is $$25$ per hour. Overtime is limited to $25$ hours per month per employee. The plant currently has $300$ employees. Each ton of detergent requires $3$ hours of labor input. It costs $$5$ to carry a ton of detergent in inventory for a month. Materials cost per ton of detergent is $$30.$ Detergent is sold to retailers at a price of $$150$ per ton. We assume that no stock outs are allowed and the starting inventory entering January is $6,000$ tons and the desired ending inventory in December is $8,000$ tons. We assume that Q&H can change the size of the workforce by laying off or hiring employees. Hiring a new employee incurs a cost of $$800$; laying off an employee incurs a lay off cost of $1,500.$ We also assume that a third party has offered to produce detergent at $$105$ per ton. Market research has indicated that a promotion dropping prices by $5$ percent in a given month will increase sales in that month by $20$ percent and bring forward $15$ percent demand from each of the following two months.

Anticipated Monthly Demand at Q&H

Month

Demand

January

$10,000$

February

$11,000$

March

$15,000$

April

$18,000$

May

$25,000$

June

$26,000$

July

$30,000$

August

$29,000$

September

$21,000$

October

$18,000$

November

$14,000$

December

$11,000$

What is the optimal production plan for the year if we assume no promotion? Please solve it in Excel $($decision variables, objective function and constraints.$)$

Is it better to promote in April or July? How much increase in profit can be achieved as a result.