Roland S. Hansonr_hanson@gasou.eduDepartment of Engineering Technology Georgia SouthernUniversity |
Michael K. Ogleogle@gasou.eduDepartment of Engineering Technology Georgia Southern University |

**Abstract:** A spreadsheet can be an effective tool for exploring
a complex aggregate scheduling problem. This web page introduces a sample
aggregate scheduling problem and dicusses how it may be used to experiment
with a variety of problem solving strategies leading to a lowest cost solution.
The emphasis is not on simply finding an optimum solution, but to provide
a hands-on exploratory tool that must be built by the student. In this way, the
student is immersed in the foundations of the problem and variables, not simply
given a solution.

One of the difficulties normally encountered in manufacturing related engineering technology classes is how to get students to recognize and understand the interrelationship between variables in a problem. This can be even more difficult when trying to develop solutions for which there is no exact solution, or trying to develop strategies to optimize solutions. The emphasis is on trial and error exploration to drive home the impact of the interrelationships.

Aggregate scheduling offers an opportunity to explore the relationships between variables. The use of spreadsheets for aggregate scheduling allows the student to quickly recalculate alternate solutions. and therefore explore the interrelationships between variables. Various optimization techniques, i.e., linear programming, will yield a solution when the data and constraints are supplied in the proper form. However, it may be difficult to see how the solution was determined and what results occur from each change in the variables. Interactive exploration with the spreadsheet makes the student concentrate on the variables, realistic ranges of values, and the resulting range of solutions. The spreadsheet is certainly not a replacement for a technique like linear programming, but is instead a tool for developing understanding.

This web page introduces a specific aggregate planning problem and shows how it can be developed and analyzed to examine the effects of changes in variables. The particular problem described below has changeable conditions which must be examined to achieve results. Introducing a specific goal, such as determining the lowest cost solution, offers a perspective through which variables must be manipulated to calculate a trial result. The same spreadsheet can be used to determine the cost of other strategies, such as a level production, a production chase plan, a hybrid plan, and others. These other strategies have components within them which require a qualitative determination of what is the "best" plan. Using the lowest cost as the sole determinant of the "best" plan provides an opportunity to develop a plan with a discrete solution. In the process of determining the lowest cost, a student must develop an understanding of each variable and how they affect other variables.

The students simulate the results of changes in the aggregate plan through changes in the variables over the 12 month planning horizon. Factors which affect the costs within the problem make some variables more cost effective. The problem constraint of developing the lowest possible cost means they must focus on the effects of changes in each variable.

The Worldwide Widget Works (WWW) has estimated the demand for its products for the upcoming year as:

Month |
Working Days |
Demand (in units) |
Month |
Productive Days |
Demand (in units) |

22 | 8,000 | 22 | 26,000 | ||

19 | 12,000 | 11 | 16,000 | ||

21 | 18,000 | 21 | 18,000 | ||

22 | 20,000 | 22 | 14,000 | ||

21 | 28,000 | 18 | 9,000 | ||

21 | 25000 | 21 | 7,000 |

- There are 100 employees on the payroll. Any change in employment must be accounted for in layoff or hiring costs.
- Productivity is 12 units per day per employee.
- Regular time salaries average $80 per day.
- Capacity can be increased up to an additional 30 percent through overtime.
- Units produced on overtime cost an additional $2 per unit.
- Units in inventory are charged at $2 per unit per month.
- Inventory shortages are charged at $10 per unit per month.
- Hiring and training an employee costs $300.
- Laying off an employee costs $200.
- Additional capacity is available through subcontracting at a cost of $8 per unit.
- The initial inventory level is 5,000 units.
- There should be 5,000 units in inventory at the end of the problem.
- Any units less than 5,000 in inventory at the end of the problem should be charged at the shortage cost charge.

This problem presents an opportunity to develop a simple or a complex solution to the problem. One cost factor could greatly outweigh another and lead to a simple solution or there could be a complex interaction requiring examination of each variable to determine their impact on the solution.

The data above can be easily displayed, evaluated, and summarized in a spreadsheet. Initially the sheet can be set up to display the initial conditions of the problem. Columns and information can be added, as needed, to add data necessary to create a solution to the problem. A specific solution is illustrated in the tables below.

Changes can be made to the values of individual variables and the results are recalculated and displayed. Modifying the number of employees can yield an individual solution. Just have enough employees on the payroll to meet the forecast demand (chase plan). The solution will not be optimal but will easily show a total cost for that condition.

A | B | C | D | E | F | G | H | I | J | K |

Mon | Working Days | Number of Employees | Change in Employees | Regular Time Production | Forecast Demand | Starting Inventory | Net Additions to Inventory | End of Month Inventory | Monthly Average Inventory | Storage Cost ($2/Month) |

Start | 5000 | |||||||||

Jan | 22 | 12 | -88 | 3168 | 8000 | 5000 | -4832 | 168 | 2584 | 2016 |

Feb | 19 | 60 | 48 | 13680 | 12000 | 168 | 1680 | 1848 | 1008 | 2016 |

Mar | 21 | 65 | 5 | 16380 | 18000 | 1848 | -1620 | 228 | 1038 | 2076 |

Apr | 22 | 76 | 11 | 20064 | 20000 | 228 | 64 | 292 | 260 | 50 |

May | 21 | 110 | 34 | 27720 | 28000 | 292 | -280 | 12 | 152 | 304 |

Jun | 21 | 100 | -10 | 25200 | 25000 | 12 | 200 | 212 | 112 | 224 |

Jul | 22 | 100 | 0 | 26400 | 26000 | 212 | 400 | 612 | 412 | 824 |

Aug | 11 | 117 | 17 | 15444 | 16000 | 612 | -556 | 56 | 334 | 668 |

Sep | 21 | 72 | -45 | 18144 | 18000 | 56 | 144 | 200 | 128 | 256 |

Oct | 22 | 53 | -19 | 13992 | 14000 | 200 | -8 | 192 | 196 | 392 |

Nov | 18 | 45 | -8 | 9720 | 9000 | 192 | 720 | 912 | 552 | 1104 |

Dec | 21 | 45 | 0 | 11340 | 7000 | 912 | 4340 | 5252 | 3082 | 6164 |

--- | ------ | --------- | --------- | ---------- | ------ | --------- | --------- | --------- | --------- | ---------- |

TOT | 241 | 201252 | 201000 | 9984 | $19,716 |

L | M | N | O | P | Q | R | S | T | U |

Monthly Labor Cost | Layoff Costs ($300/Layoff) | Hiring Costs ($200/hire) | Maximum Overtime Units | Overtime Units Built | Cost of Overtime Units | Number of Units Subcontracted | Cost of Units Subcontracted | Number of Units Short | Cost of Units Short |

7920 | 17600 | 0 | 950 | 0 | 0 | 0 | 0 | 0 | 0 |

34200 | 0 | 14400 | 4104 | 0 | 0 | 0 | 0 | 0 | 0 |

40950 | 0 | 1500 | 4914 | 0 | 0 | 0 | 0 | 0 | 0 |

50160 | 0 | 3300 | 6019 | 0 | 0 | 0 | 0 | 0 | 0 |

69300 | 0 | 10200 | 8316 | 0 | 0 | 0 | 0 | 0 | 0 |

63000 | 2000 | 0 | 7560 | 0 | 0 | 0 | 0 | 0 | 0 |

66000 | 0 | 0 | 7920 | 0 | 0 | 0 | 0 | 0 | 0 |

38610 | 0 | 5100 | 4633 | 0 | 0 | 0 | 0 | 0 | 0 |

45360 | 9000 | 0 | 5443 | 0 | 0 | 0 | 0 | 0 | 0 |

34980 | 38000 | 0 | 4917 | 0 | 0 | 0 | 0 | 0 | 0 |

24300 | 1600 | 0 | 2916 | 0 | 0 | 0 | 0 | 0 | 0 |

28350 | 0 | 0 | 0 | 3402 | 0 | 0 | 0 | 0 | 0 |

------- | ------------- | ----------- | -------- | --------- | --------- | -------------- | ------- | ----- | ------ |

$503,130 | $34,000 | $34,500 | 0 | 0 | 0 | 0 | 0 | 0 |

Average Unit Cost | Total Cost | Minimum Possible Cost | Percent of Minimum Cost |

$2.94 | $591,348 | $502,500 | 117.7% |

Simpler versions of a spreadsheet for aggregate scheduling are introduced prior to the assignment of this problem. Alternate strategies are presented and lead to direct hands-on interactive exploration. Level production schedules are discussed showing the effect of the level schedule on inventories, employment, and total cost. In general, with level employment, inventories will vary directly with the ups and downs of the production schedule. The next general strategy is to construct a chase plan, matching employment to production so that inventories will be zero. In such a plan the use of labor may greatly vary. A third type of plan, subcontracting, could be used to provide a minimum investment in capacity. As the name implies, subcontracting relies on a ready supply of outside capacity to satisfy demand. A combination of all three basic types of plans, hybrid plans, could be used to strike a compromise between plan types.

The use of a spreadsheet enables individual examination of interactions between the variables. The results are summarized into a total cost of the plan and shown for comparison. Changes to individual variables are shown in the summary of the individual variable costs to see their contribution to the total cost. The four individual variables: level of employment, overtime, use of subcontracting, and the cost of shortages can be examined individually and collectively. The ability to enter a value and see the resulting changes aids a student's understanding of the interaction between the variables.

- A - Month (given)
- B - Working Days (given)
- C - Number of Employees (Initial
number is given,
*then this is a*)__decision__to be made by the student - D - Change in Employees (calculated difference in previous month) employment
- E - Regular Time Production (Number of Working Days [B] times Number of employees [C] times 12 [Units per Day per Employee])
- F - Forecast Demand (given)
- G - Starting inventory (Initial Condition, then previous month's ending value)
- H - Net Additions to Inventory (Starting inventory[G] +/- Regular Time Production[E])
- I - End of Month Inventory (Starting Inventory[G] +/- Net Additions to inventory [H] + any units produced on overtime [P] + any units subcontracted [R])
- J - Monthly Average Inventory (Sum of Starting Inventory [G] + End of Month Inventory [EI] divided by 2)
- K - Storage Cost (Monthly Average Inventory [J] times $2 per unit)
- L - Monthly Labor Cost (Number of Employees [C] times Working Days [B] times $30)
- M - Layoff Costs (If month-to-month employment has decreased then multiply the Absolute Value of the Change in Employees [D] times $300)
- N - Hiring Costs (If month-to-month employment has increased then multiply the Change in Employees [D] times $200)
- O - Maximum Overtime Units (30 % of Regular Time Production [E])
- P - Overtime Units Built (
*This is a*)__decision__by the student - Q - Cost of Overtime Units (Overtime Units Built P times $4.50 [Regular Time Unit Cost - $30/day divided by 12 units/day plus $2 additional cost/unit])
- R - Number of Units Subcontracted
(
*This is a*)__decision__by the student - S - Cost of Units Subcontracted [Number of Units Subcontracted [R] times $8 [Cost per Subcontract Unit)
- T - Number of Units Short (If the End of Month Inventory is negative)
- U - Cost of Units Short this is the absolute value of the number of units short [I] times $10 [the default cost of any units short]

- Average Unit Cost - Total cost divided by number of units produced
- Total Cost - Summation of costs K, L, M, N, Q, S, and S
- Minimum Possible Cost - Forecast Demand [201,000] times Regular Time Unit Cost [$2.50]
- Percent of Minimum Cost - Calculation of the percentage the Total Cost represents of the Minimum Possible Cost

Students are typically asked to develop the spreadsheet themselves. They are given the Initial Problem Conditions and the Additional Problem Information. They are then asked to develop a spreadsheet to analyze the problem and to develop a solution which will minimize the total costs. This constraint is utilized to force the students to develop a specific, objective solution. The primary objective of the problem is to teach the students how to explore the interrelationships of an aggregate scheduling problem. The secondary use of the problem is to expose the students to spreadsheets in a more advanced manner than any previously encountered in their curriculum. The typical aggregate scheduling problems generally involve analyzing a level production schedule, a production chasing solution, and then developing an intermediate solution. A level production plan generally results in high inventory costs. A chase plan generally results in high levels of employee turnover. As will be seen in this specific example, the solution being sought will be close to a pure chase plan.

A simple examination of the costs reveals that the first two categories of cost: regular production or overtime production, are significantly lower. By focusing first on the cost interactions between these two variables you can get to a point where only two variables, the number of employees and the number of units produced on overtime, can produce an acceptable solution.

An examination of the costs shown for the problem reveals four independent costs:

- Regular production cost per unit is $2.50.
- Overtime production cost per unit is $4.50.
- Subcontract purchase cost per unit is $8.00.
- The default cost of shortages is $10.00 per unit.

The costs show a significant difference in costs between regular production costs and overtime production costs versus the subcontract or shortage costs.

One approach to analyzing the problem is to vary the number of employees until to the point of just meeting the monthly production needs. This will provide a baseline cost against which to measure improvements. A second step is to then examine the effect of using overtime to produce some units. Overtime may be used to produce the units by reducing the number of employees for the month and then producing the needed units on overtime. A comparison of the total costs will show which result is lower. Choose the lower solution for the month and proceed to the next month. This result helps contribute to a low total cost solution.

Students tend to try to use subcontracting without fully realizing the effect on cost. The use of a spreadsheet is a natural way to analyze the interactions. Students can see what happens when each variable is changed. They will normally approach the minimum cost solution through extensive trial and error.

The spreadsheet has proven to be an excellent tool for exploring the variables
encountered in an aggregate scheduling problem. By changing the initial conditions
and constraints, the problem can be used to simulate a variety of aggregate scheduling
problems and the strategies employed to deal with these complex situations. The
problem has been used successfully for several years in the Production Planning
class in Georgia Southern University's Industrial Engineering Technology program. The
spreadsheet file is in Quattro Pro for Windows (Version 6.0, .wb2) format and is
available from the author's web sites at
**http://www2.gasou.edu/facstaff**.