1. Develop a spreadsheet decision tool that will allow the production manager to experiment with different plans....

Question:

1. Develop a spreadsheet decision tool that will allow the production manager to experiment with different plans.

2. Develop an optimization model using Solver to allow the manager to find the minimal-cost plan. Make what you think are reasonable assumptions about the maximum number of back orders allowed, as well as the maximum number of employees that could be hired and laid off in any one quarter. After solving the problem, you may decide to make additional assumptions.

3. Write a short summary of your findings, comparing several plans entered manually as well as the optimal plan.

1. Develop a spreadsheet decision tool that will allow the

Exeter Enterprises produces premium golf clubs. It is in need of a planning tool that will help it make decisions over the next eight quarters. The demand forecast for the upcoming eight quarter period is developed every quarter, and the production manager must determine how many people to hire and lay off and also how much overtime and subcontracting to schedule.
Information relevant to the problem is shown. The demand for the upcoming eight quarters is for the following number of sets of clubs, respectively: 1920, 2160, 1440, 1200, 2040, 2400, 1740, and 1500. Clubs can be inventoried from one period to the next, incurring the holding cost. Back orders are also allowed, although the company does not prefer to use them.
The production manager would ideally like a tool in which one could enter the decision values (number of sets to assign to regular time, overtime, and subcontracting; number of sets to back-order; and number of employees to hire and lay off each quarter). The manager could then compute a cost breakdown to compare different plans. Ultimately, the production manager would like the tool to be able to find the plan minimizing the total costs over the next eightquarters.