RILA 2009 SHRINK PREDICTOR TOOL HELP GUIDE

This document is designed as a help guide on how to use the 2009 RILA Shrink Predictor Tool workbook.

Snapshot:

When you first open the workbook this is the view you will see. Notice at the bottom there are two tabs – One labeled Shrink Predictor Tool and the other being Coefficient Updater. For the workbook to work correctly do not delete either one these sheets.

The workbook is unlocked. A copy of the workbook is on the CD provided. If your worksheet is corrupted, you can reload the original workbook from the CD.

Updating Data – Inserting more rows

The example provided has 192 data rows in 8 columns of user data. The range of the user data is A8..H199. These cells are highlighted in the picture below. This is where you will update your data to complete the predictive analysis.

Range A8..C199 is static data related to chain hierarchy (Region, District, Store). This data can be changed to match your organization. You can change the titles in A7..C7 if you want to use different descriptions.

If you have more locations than 192, then insert the number of rows you will need at cell A25.

For instance if you have 550 stores, then add 358 rows (550-192) starting at cell A25 to A382. By inserting rows at A25, you will preserve the Pearson formulas that are listed at the top of your dependent variables.

Updating Data – Copying Formulas

Once you insert the rows, you will have to copy the formulas in J24..V24 (Highlighted) down to fill in the empty rows inserted.

Cells J24...V24 are formulas that auto calculate. By copying these formulas down you will insure that all your stores have a predicted shrink.

Updating Data – Regression Variables

At this point you will want to update your regression variables. The regression variables are in columns D thru H. Shrink is always listed first as this is your dependent variable. Columns E thru H are the independent variables (metrics used to predict shrink). Bring in the data to populate each stores shrink rate.

Next you will have to populate the independent variables. Please note that cells E7…H7 can be labeled anything you like - For instance, if you want to use refunds % instead of O/S % than just type it in cell F7. Special Note – Do not type information directly into cells J7…V7 as these cells are formula driven.

Be sure that each store has a result for each metric. Blank entries will result in the regression not working correctly.

Running the Regression

Once your data is completely updated in the columns you can begin the regression. Select Tools, then select Data Analysis…

The data analysis dialogue box will open up.

Select Regression

Running the Regression (continued)

At this point the Regression dialogue box will open up. For purposes of this tool we are only concerned with three elements – Input Y Range, Input X Range, and Labels.

What should be in these boxes?

Input Y Range – Select the range of cells containing the Shrink Rate % (dependent variable) for every store including cell D7 - the column header.

Input X Range – Select the range of cells containing the metrics (i.e. Apps, O/S, etc) you want to use to predict shrink (independent variables) for every store including cells E7...H7 – the column headers.

Labels – The labels box should be checked.

See figure on next page for how these cells will look when populated correctly.

Running the Regression (continued)

As a special note under Output options make sure that the radio button is selected next to New Worksheet Ply:

Once you complete these three boxes, hit the OK button.

A new sheet will be added to your workbook containing the summary output of the regression analysis.

Running the Regression (continued)

The summary output sheet will look like this. You will need to take values on this output and move them to the Coefficient Updater tab in the

Copy the value of Multiple R (cell B4, 0.441283549 in this example) to the Coefficient Updater tab in the box shaded in yellow next to the Multiple R label (cell B2).

Copy the values from the Coefficients (cells B17..B21 in this example) to the Coefficient Updater tab in the boxes shaded in yellow next to the metric descriptions – (cells b5..b9)

Running the Regression (continued)

Before you update, the Coefficient Updater will be blank and appear as follows:

After you paste the values into the Coefficient Updater the worksheet will now look like this:

Regression Results

Click on the Shrink Predictor Tool tab and you will now see that each store has a predicted result.

You have completed the Regression.

Before distributing this document we suggest that you hide columns J thru S as these are cells used to calculate the end result in column T.