How to Reproduce Your Excel Work Across Many Categories, Part 2 of 3

As I explained in Part 1 of this series, Excel users often need to apply many instances of data to one model or forecast, list the results, and then analyze or summarize them.

I call this process replication or spawning.

In this post, I’ll show you a simple layout for each worksheet required for the spawning process. My next post will show the macro that makes the whole process work.

Click the button to download a free copy of the worksheets and macro discussed in this series.

This example forecasts the next three months of performance for each of four sales regions. I DON’T recommend that you use the simple-minded forecast technique I’ll show you. I had to use some calculation method to illustrate the spawning technique, and this is the first simple one that occurred to me.

The Data Sheet

(Note: I first wrote this article before Excel gave us Tables. I plan to update these examples to work with Tables.)

The following figure shows the Excel database that the Data worksheet contains. We’ll use the monthly data to generate our simple-minded forecast.

The Regions column contains the name of each Region. The Offices and Emps (employees) columns represent properties or values that the model might require when it analyzes each region. You could, of course, include any number of columns like this in your database.

This figure uses five range names:

Notice that all range names begin and end in a gray border cell. Therefore, you always can add rows or columns with more data by inserting them between the gray border rows or columns.

The Control Sheet

This is the entire contents of the Control sheet.

The Spawn button runs the macro. I’ll discuss it in Part 3.

The RowNum setting specifies the current row of data (from the Data worksheet) for the Model worksheet to analyze. Because row 1 always is the first gray row, row number 2 designates the first row of actual data, and because we have four rows of data in the Data Sheet, row number 5 designates the last row of actual data.

The NumRows cell returns the number of rows of data, including the gray border rows. The formula in this cell is:

B8:    =ROWS(Data)

The Capture cell in this figure contains the value TRUE. If you want to spawn selectively, enter a formula that returns TRUE when you want to spawn the results and FALSE otherwise.

For example, if you want to capture results only when all results are positive, the formula in cell B9 might be something like this:

B9:    =MAX(Results)>0

(The Results range name is defined in the Model worksheet.)

Similarly, the Print cell helps you to print selectively. If you want to print the model only under certain conditions, write a formula in cell B10 that returns TRUE when the conditions exist, and FALSE otherwise. If you don’t want to print at all, just enter FALSE as shown in the figure.

Don’t overlook this capability; it’s very powerful. In the past, the Capture and Print cells have allowed my spawning processes to find complex errors in data, to find products with unusually high or low growth rates, to print reports only for departments with unusual characteristics, and so on.

That is, because you have a whole workbook that you can use for an analysis of data that can come from any number of Excel databases, the Capture and Print cells allow you to put your exception reporting on steroids.

The Model Sheet

The Model worksheet is where your professional training and experience come into play. The model could be a sophisticated forecast, a detailed analysis, a complex exception report, or whatever. Here, however, I’m using a simple-mind forecast:

Rows 1-3 contain the values shown. And here are key formulas used in row 4:

A4:    =INDEX(Regions,RowNum)
B4:    =INDEX(Data,RowNum,B$2)

Copy cell B4 to the right as needed.

Notice, of course, that if you change the RowNum value in the Control sheet to 3, the model returns data for the West region from the database. This is the key to the spawning process; our model uses the dataset specified by the RowNum value.

Rows 6-8 also contain values in this simple model, as does cell A9. Here are the key formulas for row 9:

B9:    =INDEX(Regions,RowNum)
C9:    =INDEX(Offices,RowNum)
D9:    =TREND($B$4:$F$4,$B$2:$F$2,D$7)

As you can see, this simple-minded forecast merely extends recent trends into the future. Your own forecast or analysis will be more sophisticated, I’m sure. But for this example, copy cell D9 to the right as needed.

Also notice that the values of cells B9 and C9 aren’t used in the forecast. But they contain data we’ve decided we want to include in our Results table.

Finally, in this sheet, I’ve defined the name Results as follows:

Results        =Model!$B$9:$F$9

The macro will copy these results to our Results sheet.

The Results Sheet

This is the entire contents of the Results sheet before we run the macro I’ll give you in the next post. There are no formulas here, only values.

This is the same sheet after the macro runs. As you can see, the sheet now contains the monthly forecasts below each date value. It also contains the additional data we’ve decided to include in our Results table.

Part 3 of this series explains the macro I used to populate the table in this final figure.