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

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.

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.

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