Analyzing Business Data with Excel

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