|Roland S. Hanson
Department of Engineering Technology
|Michael K. Ogle|
Department 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
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.
II. Initial Problem Conditions
The Worldwide Widget Works (WWW) has estimated the demand for its products
for the upcoming year as:
||Demand (in units)
||Demand (in units)|
Problem Information and Sample Values
The emphasis is
on developing a plan which will yield the minimum possible total cost for next year's aggregate output.
- 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.
III. Establishing a Spreadsheet Solution
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.Spreadsheet Used
in the Discussion
||Number of Employees
||Change in Employees
||Regular Time Production
||Net Additions to Inventory
||End of Month Inventory
||Monthly Average Inventory
||Storage Cost ($2/Month)|
|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|
Summary of Costs
|Average Unit Cost
||Minimum Possible Cost
||Percent of Minimum Cost|
IV. Evaluating the Data and Establishing a Solution
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.
Explanation of Spreadsheet
- 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)
- 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
- H - Net Additions to Inventory (Starting inventory[G] +/- Regular
- 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
- 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]
Explanation of Summary of Costs
- 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
for Analyzing the Problem
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.