Chapter 3. Workload Forecasting

Forecasting takes information available in the present and uses it to predict the future . Everyone forecasts things. We use our knowledge of the past to decide what time to get up in the morning, how much food to buy at the grocery, and where to go on vacation. In daily life we don't usually think about how we make these decisions or how we evaluate the outcome of our choices. Business forecasting is more structured. There are specific techniques to model the relationships between present information and a future value.

Few forecasts are exactly right. Some error is expectedsome difference between the forecast and the actual value. In most cases, then, it is not enough to make a forecast. We also need to know how accurate the forecast will be. This means that the prediction is not an exact value but a range with a known probability.

This chapter looks at predicting the workload for a typical business process. The example forecasts the volume of calls coming into a call center, but the techniques can be applied to many other problems. This type of forecasting uses only the past values of the item being predicted. Future call volumes are predicted using past call volumes.

This chapter also demonstrates techniques for creating a complete application in Excel. The application uses a combination of organization, workbook functions, formatting, and a little VBA. At the end of the chapter we will have a complete Excel application for predicting workload.

Since this is the first application in the book, we will take a detailed look at the entire process, beginning with the Excel functions and features used to create the application.

The application uses the Excel functions listed in Table 3-1. Most of these functions are discussed as we encounter them in the application. But the INDEX, INDIRECT, and ADDRESS functions are used in several of the applications in the rest of this book and have a unique job. Understanding how they work is critical to understanding this chapter's application, so make sure you have read the explanations of how they work in Chapter 1.

INDEX( )

INDIRECT( )

ADDRESS( )

WEEKDAY( )

ROW( )

MAX( )

COL( )

SUM( )

MIN( )

IF( )

AVERAGE( )

STDEV( )

CONFIDENCE( )

INT( )

ABS( )

STANDARDIZE ( )

NORMSDIST( )

The application will also use the Excel features explained in Table 3-2.

Table 3-2. Excel features used in this chapter's example application

Excel feature	Explanation
Formatting	Formatting lets you control the look and feel of the application. It draws attention to important information, while it keeps supporting item in view.
Named Cells and Named Ranges	Named cells and ranges create a simple and clear interface between the parts of the application.
Array Formulas	Array formulas concentrate the logic and avoid the need for multiple columns for a single calculation.
Charting	Charting allows numbers to be presented as pictures. This focuses attention on the meaning and conclusions rather than on the numbers themselves.
VBA	VBA gives you a way to extend and customize the power of Excel.