# Exploration, Not Optimization: Using a Spreadsheet to Examine the Effect of Changing Variable Values in an Aggregate Scheduling Problem

#### 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 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.

### 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.

 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%

### 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.

• 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]

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.