Chapter 6. Monitoring Complex Systems

Chapter 5 looked at Statistical Process Control. These techniques were developed before modern computers, so limiting the complexity of the calculations was an important consideration. This, however, imposes limits on power and flexibility. Business processes are often dependent on the day of the week and the traditional Statistical Process Control approach ignores this, reducing the accuracy and sensitivity of the process.

Business processes interrelate in complex ways, and these relationships need to be monitored along with the metrics themselves. If a day has an unusual amount of activity, it is important to know if the relationships in the data are normal. If you have a large amount of activity because a competitor has a problem, you don't want all your metrics to flag because they are high. But you do need to know if the product mix is normal or the percentage of items returned has changed.

Today we are not restricted to simple calculations that can be done by hand. Excel allows us to take on all the complexity and monitor the whole process. In this chapter we look at ways to monitor a complex business process using Excel. We also build a reusable application based on these techniques.

For each item to be monitored, we build a regression model using the last week's value for the item and current values for three other principal data items. The model will be built using enough history to give a good estimate of its accuracy. The current value of each item will be predicted and the accuracy of the prediction will be used to calculate the probability that the item is an anomaly. The basic approach is like the one used in Chapter 5, in which the average served as the prediction. Here we use a more complex and accurate prediction method, explained in the Workarea section.

The application discussed in this chapter uses no other files or components. You can replace the data used in the example without changing the application. It uses the Excel functions and features listed in Tables 6-1 and 6-2, respectively.

INDEX( )

INDIRECT( )

ADDRESS( )

NORMINV ( )

ROW( )

MAX( )

COL( )

SUM( )

LINEST() ( )

IF( )

AVERAGE( )

STDEV( )

NORMDIST( )

LEN( )

ABS( )

Formatting

Named Cells

Named Ranges

Array Formulas

Charting

VBA

Form controls