Section 6.1. The Application


6.1. The Application

The application follows the same design principles we have been using. The sample data is based on a transportation operation. It includes 40 daily metrics looking at both operations and billing, with 242 days of data. The main display is shown in Figure 6-1.

Figure 6-1. The application's main display


This display shows the monitoring status for the current item and allows the user to control the data item being monitored, the date, and the confidence level. It also allows the user to view three reports.

Item 1 contains a combo box that lets the user select an item to be monitored. There are also buttons allowing the user to cycle through the items one by one. Item 2 provides the same functions for date. In Item 3 there are buttons to view reports on three other displays. The spinner control in Item 4 allows the user to change the confidence level . This level sets the sensitivity of the application; in Figure 6-1 it is set to 97%. This means that items will flag if the difference between the prediction and the actual value is so great that it would only occur by chance 4% of the time.

The actual values for the last 20 days are shown in the chart along with the control limits in Item 5. If the current value is between the control limit markers, it is inside the confidence level.

The System Status button (Item 3) brings up the sheet shown in Figure 6-2.

Figure 6-2. The system status sheet


The application is designed to be reusable. The Data sheet can be repopulated with different data as long as there are headings, there are dates in column A, and the values are numeric. The application uses up to 1,000 rows or days of information. The Settings sheet contains information about how the data is to be handled. The display in Figure 6-2 shows the dates and how many usable rows and columns there are. It takes 35 days of history for the application to work and the maximum lag is 7. It has a heading row, so the first 43 rows need to be there but are not usable.

This display also shows the lag and independent items for the regression calculations.

The Anomaly Report button checks all the items for the current day and builds a report with an entry for each item out of the control limits. An example of this report is in Figure 6-3.

Figure 6-3. The Anomaly Report


The sensitivity is set to 96%. So, seven items for this day have an error (difference between actual and predicted) so great that it would only occur by chance 4% of the time.

For each item the report presents the name, the predicted value, the actual value, the error amount, the error in standard deviations, and the probability. In the case of CREDIT TO SHIPPER, there is virtually no chance that an error of 116,736 could be caused by chance alone.

The Predictions button brings up a chart showing predictions and actual values for the last 20 days as in Figure 6-4.

Figure 6-4. The Predictions chart


This chart shows the accuracy of the predictions. Most items are predictable and in general the accuracy is high. This technique's advantage is its accuracy. More accurate predictions allow more sensitive monitoring.



Analyzing Business Data with Excel
Analyzing Business Data with Excel
ISBN: 0596100736
EAN: 2147483647
Year: 2006
Pages: 101
Authors: Gerald Knight

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net