Assignment $2$

$(75$ Points$)$ Using Excel to complete the following question. You may give your answers to all

of the questions in the Excel file and upload the Excel file only to Blackboard. If so$,$ you must

indicate your answers clearly. Alternatively, you may give your answers in the Word file and

upload both the Word and Excel files to Blackboard.

The ABC Company is making a production plan for the next year, given the sales forecast for

the next year as:

Currently, the firm has $30$ employees, and each employee can produce up to $250$ units per

quarter. The firm pays each employee $$2,500$ per quarter. The firm estimates its inventory

carrying cost to be $$2$ per unit of ending inventory per quarter, its hiring cost to be $$1,500$

per employee, and its layoff cost to be $$3,000$ per employee. The company currently has an

inventory of $1,000$ units and wishes to have an ending inventory of at least $2,000$ units at the

end of the next year $($i$.$e$.,$ quarter $4$ only$).$ The company does not plan to incur inventory

shortages.

a$)(25$ points$)$ Using a chase strategy, determine a workforce level in each quarter and the

total cost of the next year $($labor cost$,$ hiring cost, layoff costs, and inventory carrying cost$).$

b$)(25$ points$)$ Using a level strategy, determine a constant workforce level and the total cost

of the next year $($labor cost$,$ hiring cost, layoff costs, and inventory carrying cost$).$

c$)(3$ points$)$ Compare the production plans obtained in parts a$)-$b$).$ Which production plan

would you recommend, and why?

d$)(22$ points$)$ Using Excel Solver, determine the optimal plan and the total cost of the next

year. Use the "SOP Example" discussed in class as a reference.

$(25$ Points$)$ Using discrete available$-$to$-$promise $($ATP$)$ logic, calculate the master production

schedule $($MPS$)$ for the following conditions.