

by
Roland S. Hanson r_hanson@gasou.edu Department of Engineering Technology Georgia SouthernUniversity

Michael K. Ogle ogle@gasou.edu 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 handson 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.
I. Introduction
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.
II. Initial Problem Conditions
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) 
Jan 
22 
8,000 
July 
22 
26,000 
Feb 
19 
12,000 
Aug 
11 
16,000 
Mar 
21 
18,000 
Sept 
21 
18,000 
Apr 
22 
20,000 
Oct 
22 
14,000 
May 
21 
28,000 
Nov 
18 
9,000 
June 
21 
25000 
Dec 
21 
7,000 
Additional
Problem Information and Sample Values
 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.
The emphasis is
on developing a plan which will yield the minimum possible total cost for next year's aggregate output.
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
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 
Summary of Costs
Average Unit Cost 
Total Cost 
Minimum Possible Cost 
Percent of Minimum Cost 
$2.94 
$591,348 
$502,500 
117.7% 
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 handson 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
columns
 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 monthtomonth employment has decreased then
multiply the Absolute Value of the Change in Employees [D] times $300)
 N  Hiring Costs (If monthtomonth 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]
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
A Strategy
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.
V. Conclusions
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.
Bibliography:
