Tying together two time dimensions of a model

3.87K viewsTips and Techniques


I am modeling expansion of a retail chain, with one identical store opening every couple of weeks, though not necessarily in a regular manner.

Obviously costs and revenues per one store can easily be captured in a matrix with the time dimension tied to ELAPSED weeks to/since store opening.

But then there’s the CALENDAR weeks dimension of the chain operating as a whole.

Here’s where I hit the wall. An obvious solution is to manually create full copies of a single store cashflow, each evolving in its elapsed time, and stagger them along the calendar time dimension. But this seems to have the disadvantage of redundancy, with related drawbacks in changing the model.

It’s dawning on me there must be a better way. Maybe I can keep the store model in one master matrix and only somehow virtually link it as many times as I need to the calendar time dimension of the chain as a whole…

Can you point me to a solution?

Thank you,



Old thread, but this should to the trick, although it has a tendency to create large files i.e 700,000 cells from the start. When I use it for modelling it can sometimes take 30min to recalulate.

I’m sure there is an easier way… let me know if anybody has one.

It uses a single locations forecast for the first 12 months of operation to model additional locations that open up at later dates… i.e presumes that additional location will have the same growth rates for the first 12 month from their launch date. Although the model goes to 5 years I have purposefully flat lined growth past the first 12 months, ideally a global growth rate should be used past this point in my opinion.

Have play with it, let me know if you have any questions.



Hi Marek – thanks for the post. I am wondering if you could post a sample model describing your use case…?

Latest Questions