Sometimes you have to work backwards to move forwards. It may seem counterintuitive but experience shows that is to the best approach to your first Quantrix modeling exercise. What do I mean by that? First let’s take a look at how you model in a typical spreadsheet.
The “Old” Way
When you model in a spreadsheet, the very first thing you do is assemble a bunch of data. You hope that the data you gather is complete because you need it in order to know where to place your logic (i.e. formulas) in the spreadsheet cell. If the data is not complete, then you have to rework your spreadsheet since the formulas will not be in the appropriate places.
Once you have the data, you move it around to get it looking the way you want to see it presented. Once you start creating formulas, moving things around can get pretty risky. It is ironic because for all of our professional lives, we have been taught we, as modelers, are responsible for making sure everything lines up. Wholesale rearranging of a completed spreadsheet model is not usually looked upon as an enjoyable endeavor. In reality, it is quite dangerous.
Now that you have data and it is appropriately arranged, you begin to make your formulas. You make one formula, check to make sure it works and then copy and paste it down a range of cells. Then you revise the formula, make sure it works, and copy and paste it down or across the range of cells again. Then you add some new information, check to make sure the ranges in the formula continue to work, and modify if necessary and copy and paste again.
You are now happy with the results and decide it is time to make a chart. You name a range, insert a chart and things look pretty good.
Now it is time to move on to the next worksheet because you need a separate worksheet for each division, product, or country. You try to minimize the size of a single worksheet or organize the model along some sort of dimension. The whole process starts again except now each time you make a change to one worksheet, you have to make sure the summary picked up the new cell reference and everything is still holding together.
The “New” Way
Ok, enough about the “Old Way”. So what does this have to do with going backwards first? Well, in a Quantrix model you approach model design from the opposite perspective. Here’s how it works:
First, with Quantrix, we don’t need to bring in the data first. As a matter of fact, bringing in the data first unnecessarily adds complexity. That is not to say we don’t care about the data, it just we don’t need it in the model initially. We just want to know if we can get to it and what form (data structure) it is in.
The first step is to define the dimensions of the problem. What are our “category tiles” in Quantrix terms? We don’t need them all to start but just some fundamental dimensions such as time, line of business, products, services, employees and so forth. We then place those category tiles in the matrix in a way that they relate to each other. (STRUCTURE)
Next, you can put in some sample data; 10, 100 or 1000 are my usual choices. I can then start writing my formulas based on the category or items, but never ever for a single cell. The data helps ensure my formulas work correctly. (LOGIC)
As I continue to build my model, I will add more matrices and more categories, while resisting the temptation to work on presentation or bringing in the complete data set. In Quantrix, all the relationships between matrices , categories and formulas are managed by the software. You do not have to worry that if you make a change to a category (what might look like a column or row header) that it properly replicates or adjusts itself throughout the model. It is done for you.
Once the model is completed, I add the presentation elements such charts, table views, and canvasses. (PRESENTATION)
Now it is time to bring in the data set. I recommend bringing in a small subset of the data initially (if the target data is very large) so you can do some further testing. A smaller set will be easier to work with and you can quickly develop some control totals. Once you’re satisfied that all is well, go ahead and bring in the full data set. (DATA)
With this approach, you see that you actually work backwards compared to the spreadsheet way. Once you use this approach regularly, you will find it feels quite intuitive. The reason is that it is the way most people look at their business challenges and problems. You understand your business structure, your business rules and how best to portray your business. The data is just the outcomes.
Here is a summary of the generalized approaches:
Old Way: Data=>Presentation=>Logic=>Structure
New Way: Structure=>Logic=>Presentation=>Data