Stuck with formula referencing another Matrix

Solved4.48K viewsFormulas and Functions
0

I am stuck in how to create a formula that references another matrix. I think the confusing part is a time dimension that I’ve set up, based on a Quantrix Sample model.

I have a matrix P & L, with a Year dimension (2012..2016), and a Seasonality Matrix, which I use to create a Year.Month Revenue estimate.

I am trying to populate the 2015, 2016 and 2017 Month cells here:
[img:1uv8vq8l]http://i.imgur.com/xEPxhHS.jpg[/img:1uv8vq8l]

From the Seasonality matrix (below), and I just can’t seem to figure out what the formula needs to look like.
[img:1uv8vq8l]http://i.imgur.com/4yNo0kX.jpg[/img:1uv8vq8l]

Can anyone point me in the right direction?

1

You might need to link your month dimension? Right click on the month category and choose ‘Link Category’ and you will get an option to link with the other month category in the model.

Once done, the formula engine will know to rendezvous the values across the month dimension with the formulas as written.

If you don’t want categories to be linked for some reason then you will need to evaluate using a Select statement that matches the month names together. something like this.

‘4060 – Other’=select (Seasonality::’2015’,@Seasonality::Month,@Month)

But if you can rely on category linking it will do the work for you.

Hope this is helpful.

-Mike

0

Thank you, James! I agree about the Year dimension – I plan to fix that in future revisions.

I really appreciate your insight and assistance.

0

Now written the select to bring in the rev-breakdown*seasonlity into the P&L for Sales.

My only comment is that this could have been a lot easier if the Year dimensions were linked and consistent across the entire model.

James

[attachment=0:20tkrwpv]Financial Analysis %26 Projections 150609d (1)_JK_Edits_2.model[/attachment:20tkrwpv]

0

Hi Golden,

I took another look at your model and re-engineered your seasonality matrix and added two more, a forecast controller and an aggregate seasonality matrix. I have written the formulae in such a way that once a year’s actuals have been entered, only then will the % of the year be accurate for that given year, then they will be included in the aggregate calculation.

I also changed some of the dimensions and added one more as I said in my previous post as this makes the model more scalable since when you add a new year – you won’t now have to go and re-write your formulae – Quantrix will take care of that for you.

Hopefully this all makes sense, let me know if you have any further questions.

James

[attachment=0:bolhnfgv]Financial Analysis %26 Projections 150609d (1)_JK_Edits.model[/attachment:bolhnfgv]

0

Forgot to add picture!

[attachment=0:rhxn41t0]Seasonality.png[/attachment:rhxn41t0]

0

Hey Golden,

I had a really quick look at your model, you might find it easier to make your seasonality matrix similar to the picture I’ve attached. It may allow you more flexibility as opposed to using groups.

I know this doesn’t solve your select problem, and I will have a look at that when I can unless another forum member gets there before me :)

James

0

But now, I can’t seem to create a new Select statement that works for the next step. I’ve been reading and re-reading the help, and looking at the sample models without luck.

Seasonality now correctly calculates expected revenue by Year:Month
[img:1w9x74vi]http://i.imgur.com/QjewrSt.jpg[/img:1w9x74vi]

RevBreakdown is a table that shows expected breakdown into revenue types, also by Month
[img:1w9x74vi]http://i.imgur.com/KfLLMce.jpg[/img:1w9x74vi]

I now need the P&L to multiply expected Rev (from Seasonality) by RevBreakdown (for each Revenue type) to populate the Revenue lines as below (currently I have it putting all revenue into Other):
[img:1w9x74vi]http://i.imgur.com/SeLRyNm.jpg[/img:1w9x74vi]

In the P&L, I’m trying a select statement like this:

0

Thank you, Mike – you were quite correct. My Categories were not linked (as I thought they were), and linking them fixed it.

I really appreciate it!

0

I think what I’m asking is why this doesn’t return the values I’m expecting:

‘4060 – Other’:’2015′:Month = Seasonality::Month:’2015′

Instead, it populates the Jan 2015 Seasonality number for all months of 2015 in the P&L.