A member of an Excel forum recently asked how to solve a problem that many Excel users face in various forms.
In his particular case, he wanted to forecast total revenue for a large chain of movie theaters. The most accurate approach would be to forecast the performance of each theater, and then add up the results.
That is, he needs to replicate his model (in this case, a forecasting model) across many categories of data.
This is a challenge that extends beyond forecasting. To illustrate, the challenge could apply to:
● Analytical Filtering. For example, you could set up an analysis using extensive data about one stock to calculate how under- or over-valued it is; apply the same analysis to each of many stocks; list the results by stock; and then find the most under-valued stocks in the list.
● Error Finding. For example, you could analyze the trends in the monthly changes for every General Ledger account, or other data series susceptible to error. Then list those items where the change for the current period is unreasonably high or low.
● Early Warning. For example, you could analyze the trends in page views on your web site, or look at other data series where early warning would be useful. Then list the pages with the fastest-growing or the fastest-falling trends.
● Report Printing. For example, you could create a dashboard or other dynamic report that presents the results for one category of data…for one region, product, department, or whatever. Then loop through every category of data, but rather than listing each result, print the result.
In all these cases, you create a model, analysis, or presentation that could be very sophisticated; you spawn that model across many categories of similar data; you list (or print) key results from those analyses; and then you analyze the results.
Analytical spawning with Excel requires four worksheets and a short macro:
● The Data sheet contains a simple Excel database with all items – all stocks, GL accounts, web pages, products – to be analyzed or forecasted. Each item – each row in the database – has all the data the model needs for the item.
● The Control sheet contains a cell that specifies the row number of the current item for the model to analyze. It also contains side calculations that the model or macro might need.
● The Model sheet performs the analysis and ends with a single Results row that summarizes the model’s results. Or, for printing, the result is a dynamic report that’s typically defined by a Print Area.
● The Results sheet initially contains only column headings, which correspond with data in the Model’s results row.
● The Short Macro loops through each item in the list, increments the row number in the Control sheet, recalculates to update the model, and then copies the model’s results to the Results sheet.
Part 2 of this series explains how to set up the spreadsheet, and Part 3 explains how to set up the macro.
