Creating Large Model Quandary!

3.07K viewsGeneral Discussion
0

Hello All!

I am having a lot of trouble trying to figure out the best way to create a Quantrix Model for a large project. In the past it has always been done in HUGE Excel workbooks with tons of tabs. I know Quantrix can handle it better, faster and without all of the potential errors.

We have to track sales units by type, by day, week, by month, by year. Everything must be able to be grouped by day of week for a week, month, quarter, year. We track at least 3 years back and 2 years forward.

I need some guidance in the best way to set this up. I have started and pulled off this project 10 times because it just never seems right.

Any help or guidance is very much appreciated. :confused: :eek:

spudmcc

0

[quote="spudmcc":31vgyj7n]Hello All!

I am having a lot of trouble trying to figure out the best way to create a Quantrix Model for a large project. In the past it has always been done in HUGE Excel workbooks with tons of tabs. I know Quantrix can handle it better, faster and without all of the potential errors.

We have to track sales units by type, by day, week, by month, by year. Everything must be able to be grouped by day of week for a week, month, quarter, year. We track at least 3 years back and 2 years forward.

I need some guidance in the best way to set this up. I have started and pulled off this project 10 times because it just never seems right.

Any help or guidance is very much appreciated. :confused: :eek:

spudmcc[/quote:31vgyj7n]

Hi, Spud!

I have created a variety of large models in Quantrix. Here are some of the steps I have followed.

1) Think of the model as a collection of tables, where each table has one set of tightly related row and column labels. Excel lends itself to a lot of sloppiness where people just place all kinds of data structures in a single worksheet and then link them together. In Quantrix you can’t do this and this restriction actually helps make your models a lot cleaner.

2) Think of the model as if it were a relational database. You will want “reference tables” where you define key parameters and names. Such reference tables will also be the source for category labels that you may reuse many times across the models. These “linked categories” are critical to master for large models. Then you will want to use the linked categories and reference data / parameters in various places across your model.

3) Think of the model as if it were a set of matrices. Imagine multiplying two matrices to get a third (A DOT B = C). This is “the Quantrix” way and it lets you use very simple formulas on large volumes of data. It also makes your models very maintainable.

4) Plan your model so it can be rearranged. You may change your mind many times as you construct your model. You may want to move categories across axes, recombine categories on the same axis in different orders. So keep your categories as independent as possible.

5) Avoid cell formulas like the plague. The only time they make sense is to set the value of a particular cell in an override of a default.

6) Name your matrices, categories, and item groups in every case. It is tempting to ignore this, and you can come back to it later, but it can really help you think through the organization of a large model when the names make sense initially.

For your specific problem, my guess is that you have three primary categories: “Product”, “Date” and “Year”. The “Product” category lists all of the items whose sales you are interested in. The “Date” category is the finest level of date that this sheet will be used to track. Use item groups to collect the dates together into higher groups (if the finest category is day, then use item groups for week, month, and quarter); you can also use them to collect related products together. For each item group, use a sum formula (this is on the toolbar near the grouping button). You will almost always want to have Date and Year in the same matrix and will want the Date tile subordinate to the Year tile. This makes it easy to add new years. You can also have future years, but while you have real data in past years, you will need formulas to project into the future years. But these formulas might be no more than (“2008” = “2007” * 115%) or perhaps will be more complex as in “2008”:Product A = “2006”:Product A * 115%) – of course you can create a really complex forecasting model with all kinds of parameters behind this as well.

I hope this is helpful.

– Mark

0

[quote="spudmcc":1ye5rwen]Hello All!
I am having a lot of trouble trying to figure out the best way to create a Quantrix Model for a large project. In the past it has always been done in HUGE Excel workbooks with tons of tabs. I know Quantrix can handle it better, faster and without all of the potential errors.
spudmcc[/quote:1ye5rwen]

Could you answer two things.

1. What do you normally use the different tabs for? I read what you mentioned as the data that is being grouped. Note that I found the information similar to the model used in the two tutorials (15 minute and 30 minute; more the 30 minute one) that are used as marketing materials on the main site. I have no idea if you have seen them. They might provide some ideas.

2. It can sometimes help to talk through the issue. You mentioned that you have tried this 10 times already. What did you learn each time or overall? Did you get a model to work and you feel it is less than optimal? Or did you never finish? In any case can you share some of the places where you got stuck? Maybe you are on the right path but are just feeling that it is all a bit strange compared to Excel.

Baker

0

Hello!

Probably I can help get you going, but I’ll need to understand a bit more about the way in which data should enter the Quantrix model (eg. keyed in Quantrix, pulled from a sales management system; at one location, at many locations) and what output you need. These things will influence the choice of approach.

If you want to get into the details, perhaps an e-mail discussion would help… [email:3nbtfl9u]philip.turner@turneraa.com[/email:3nbtfl9u].

Cheers,

Philip