Sponsored

# Excel Model for Aggregate Planning

Spreadsheets are the most common software tool managers use to analyze data and model quantitative problems.

Departamento de Economía Financiera, Contabilidad y Dirección de Operaciones Facultad de Ciencias Empresariales Universidad de Huelva Plaza de la Merced, 11 — 21002 Huelva (Spain) [email protected]

Abstract Spreadsheets are the most common software tool managers use to analyze data and model quantitative problems. They have also become one of the preferred tools for teaching quantitative methods to business school students. In this paper we introduce an Excel model for aggregate planning, characterized by its great flexibility and for the use of Excel Solver, which in many cases allow us to find the optimal solution for a given set of conditions.

Back to "Search By Author"

## EXCEL MODEL FOR AGGREGATE PLANNING

Introduction It is a fact that spreadsheets are the preferred tool for teaching quantitative methods to undergraduate and graduate business students (Jordan et al., 1997). This has led to the publication of an increasing number of textbooks dealing with modeling and decision analysis using spreadsheets (Hesse, 1997, Eppen et al., 1998, Savage, 1998, Barlow, 1999, Monahan, 2000, Winston and Albright, 2000, Lapin and Whisler, 2001, Weida, Richardson and Vazsonyi, 2001, Render, Stair and Balakrishnan, 2002, Hillier, Hillier and Reagan, 2003, Ragsdale, 2003).

The reasons for the popularity of spreadsheets are many, but we could highlight the following: they are widespread (Mason and Keane, 1989), user-friendly, flexible, and allow the analysis of multiple scenarios. Therefore, many companies and education centers have found spreadsheets to be an easy method for business modeling as they offer a wide set of tools ranging from formulas —whether easy ones or more complex ones— to simulation of different scenarios, macros, charts, etc.

Perhaps the most remarkable fact is that spreadsheets have become an excellent delivery vehicle for teaching optimization (Evans, 1986), which in many cases allow us to find the optimal solution for a given set of conditions.

Microsoft Excel Solver has greatly helped in this, as it is the most widely distributed and almost surely the most widely used general-purpose optimization modeling system (Fylstra et al., 1998).

In fact, we use Microsoft Excel to teach our undergraduates aggregate planning. First, it is necessary to introduce them to basic spreadsheet concepts and later proceed to the modeling of problems, trying to include the greatest number of possibilities in order to analyze all the results generated and use the tools previously mentioned.

## EXCEL MODEL FOR AGGREGATE PLANNING

Aggregate Planning Aggregate planning involves translating business plans into broad labor and output plans for the intermediate term. Its objective is to minimize the cost of resources required to meet demand over that period.

To achieve this, it is necessary for the planner to consider various decision alternatives.

The two basic types of decision alternatives are reactive and aggressive (Krajewski and Ritzman, 2001).

Reactive alternatives admit forecasted demand as a given and try to fulfill it by introducing modifications in the work force levels, overtime, vacation schedules, inventory levels, subcontracting, undertime, and planned backlogs.

On the other hand, aggressive alternatives try to modify the demand in order to adapt it to the firm's resources by means of promotional campaigns, price incentives, complementary products, etc. This kind of alternative tends to be the responsibility of the marketing manager.

Assuming that the effect of aggressive alternatives has already been incorporated into the demand forecasts, the planner will have to focus on reactive alternatives. Therefore, he/she will have to establish a planning strategy which selects the best options. A possible way to achieve this is by starting up with both chase and level strategies and then moving later on into a mixed strategy.

Chase strategy This strategy adjusts production rates to match the demand requirements period by period. In order to achieve this objective, the planner cannot make use of the anticipation

## EXCEL MODEL FOR AGGREGATE PLANNING

inventory, or undertime. Therefore, he/she has to use other options such as hiring and laying off workers, overtime, and subcontracting. This approach has the advantage of low inventory investment and backlogs, although it has some disadvantages, such as the potential alienation of the work force and the loss of productivity and lower quality because of the constant churn in the work force.

Level strategy This strategy either keeps a constant output rate or work force level over the planning horizon. To achieve this, and contrary to the previous strategy, it can use the anticipation inventory and/or undertime. The main advantages are level output rates and a stable work force at the expense of increased inventory investment, undertime, overtime, and backlogs.

Obviously, between these two strategies there is a range of possible strategies and the planner should find the best mixed strategy to improve the solution obtained by the two previous strategies.

To do so, the planner will have to answer certain questions:

**1. Should inventories be used to absorb changes in demand?**

**2. Should changes in demand be accommodated by varying the size of the work force?**

**3. Should overtime and undertime be used to absorb changes in demand?**

**4. Should subcontracting be used?**

Despite the many possibilities this approach offers, it makes aggregate planning much more complicated due to the large number of alternative plans.

## EXCEL MODEL FOR AGGREGATE PLANNING

To analyze aggregate planning in the classroom we use the trial-and-error approach using graphs and charts that can easily be developed with a spreadsheet.

Defining the problem The planning department of a company is faced with determining the aggregate plan for a product family over the next twelve months. In this plan, the company wants to determine the number of workers needed each month, the number of regular and overtime hours required, the number of units to be produced and subcontracted, and the total cost associated with the plan.

Table 1 presents the demand forecast and other relevant parameters.

TABLE 1: Forecasted Demand and Other Relevant Parameters Demand Forecast 6,000 6,000 6,000 8,000 14,000 18,000 24,000 12,000 7,000 6,000 4,000 10, Initial Inventory 1, Maximum Subcontracting Units/Month 5,000 Subcontracting Cost/Unit The size of the work force is set at the beginning of each month. Workers may be hired or fired to change the available amount of production. We do not try to plan for vacations, sickness, relocation, or death of employees during the year.

Hourly rates are associated with regular and overtime labor and it is assumed that all workers are paid to work an entire month, even if they are not productive. The company uses a maximum subcontracted production of 5,000 units per month.

## EXCEL MODEL FOR AGGREGATE PLANNING

Inventory at the beginning of the first month is 1,000 units. Because the demand forecast is imperfect, the company has determined that a safety stock of 500 units should be established.

In addition, inventory holding cost is calculated on ending inventory.

The company policies establish that unsatisfied demand can be backordered and filled in a future period. Additionally, according to policies, the minimum number of employees must be 15 at all times, and the maximum number 60, which corresponds to the maximum capacity of the facility. A final policy establishes that overtime on a daily basis cannot exceed one hour per worker.

Finally, the total cost is made up of the sum of costs derived from the wages of regular work and overtime, undertime (this cost is only computed if the number of workers needed in a given month is lower than the minimum number of employees), subcontracting, carrying inventory, backorders, and hires and fires.

The goal is to create a feasible low-cost production plan.

The trial-and-error charting and graphic technique This approach is easy to understand and convenient to use, and involves costing out various aggregate planning alternatives and selecting the one that is best on a trial-and-error basis.

The main problem is that the vast amount of calculations required makes it difficult to apply the process manually, and so a spreadsheet is required. In our case, we use Microsoft Excel, which allows the students to simulate different alternatives within a few seconds.

However, before investigating alternative aggregate plans, it is often useful to convert demand forecasts into production requirements (Chase, Aquilano and Jacobs, 1998), which take

## EXCEL MODEL FOR AGGREGATE PLANNING

into account the amount of inventory on hand at the beginning of the forecast period and the safety stock estimates. Note that these requirements implicitly assume that the safety stock is never actually used, so that the ending inventory each month equals the safety stock for that month.

The three aggregate plans included in the spreadsheet are a chase (i.e., variable production rate model), a level (i.e., constant work force model), and a manual aggregate plan.

The first two plans are automatically calculated, while the third plan, the manual, is user inputted based upon the information provided in the other two plans. In this latter case, the student has the button Set Workers Required which, after establishing the strategy offering fewer costs, equates the number of workers needed in the mixed strategy with those in the lower-cost strategy (see table 2 and figures 1 and 2). From this moment on, the student can modify the number of workers on a trial-and-error basis to look for the best solutions. Changes in this row have an influence on all the other spreadsheet numbers, and therefore on the final total cost. For further details, Appendix 1 shows the formulas required in each cell, based on Excel protocols.

TABLE 2: Summary of Costs for Alternative Aggregate Plans

## EXCEL MODEL FOR AGGREGATE PLANNING

FIGURE 1: Mixed Strategy However, in spite of its relative simplicity, finding a minimum cost solution is not guaranteed. In other words, given that we are dealing with an iterative process, the “quality” of the solution for the mixed or manual plan will depend on the actual skills of the planner. Therefore, it is reasonable to try to find a method that could lead to an optimal or satisfactory solution, independently of the skills of the planner. The Solver tool bundled with Microsoft Excel can help to achieve this.

## EXCEL MODEL FOR AGGREGATE PLANNING