Section 6.3. Settings


6.3. Settings

The Settings sheet contains processing options for the application. If you are using new data, you will need to change the settings. The Settings sheet is shown in Figure 6-6.

6.3.1. Independent Columns

There are three independent columns, indicated by column labels in the range A2:A4. The regression model in the application uses these three columns from the Data sheet. It is important to select items that are central to the process being monitored but they must not be highly correlated with each other. In this case, I am using columns O, AC, and AF. These are CASH TRANSACTIONS, REGULAR STOP PICKUP, and TRANSPORTATION CHARGES - TOTAL. One deals with money, one with the operation, and one with billing. They are all high level metrics, but they come from different parts of the process. These cells on the Settings sheet are named ind1, ind2, and ind3.

6.3.2. The Alternate

But what happens when we predict one of the independent items? If we predict CASH TRANSACTIONS and it is in the model as an independent variable, our prediction will be as useless as it is accurate. This is where the alternate comes in.

When one of the independent variables is being predicted, the alternate takes its place in the model. The alternate item, WEIGHT - TOTAL, is in column AJ. This value is named Alt.

Figure 6-6. The Settings sheet


6.3.3. The Lag

The lag is the number of days in a week of data. In the example we are looking at a five days per week operation, so the lag is 5. The lag can be as high as 7, and naturally it is named Lag.

6.3.4. The Out of Limits Message

The named value outmessage is the message displayed when an item is out of the control limits. It appears on the main display under the chart.

The other values on the sheet should not be changed manually. They are set by controls or macros. They are the current sensitivity, and the row and columns settings.

6.3.5. The Current Column

The current column is calculated as in Figure 6-7.

The value in B7 is 1. It is set by a combo-box control on the main display and by the macros that run on the Next Item and Previous Item buttons. Here the 1 means the first column is selected. But column A contains dates, so the first usable column is B. We want the letter B because we use it in the indirect functions on the Workarea sheet.

Figure 6-7. The current column calculation


The address function in C7 adds 1 to skip the dates, then converts the number to an address. The MID function in A7 strips the letter B out of the address. This formula will work with two letter columns, like AB, as well. The final value in A7 is named CurCol and is used in other formulas.

6.3.6. The Current Row

The current row calculation is in Figure 6-8.

Figure 6-8. The current row calculation


Here things are simpler because we want the row number, not a letter. The value in cell B16 is set by a combo box or a macro when the user changes the current date. The formula in A16 adjusts the value to skip the first 35 rows plus 7 additional rows to allow for the lag. This is the historical data the model needs to monitor the 43rd day. The value is named CurRow and is used in other formulas.

6.3.7. The Sensitivity

Figure 6-9 contains the calculation for sensitivity.

Figure 6-9. Calculating the sensitivity


The spinner control on the Display sheet sets the value in cell B10. The control changes the value by 1 each time it is clicked, and is constrained to a maximum value of 99. The calculations need a number between 0 and 1, therefore the value is divided by 100 in cell A11.



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