Line Item Subset SUM

HugoManchon

Hi there!

I have growth assumptions on different cost lines in module(A) where am using a Line Item Subset for cost lines and regular line items in different years.

In another module(B), I calculated the Net revenues also using Line Item Subsets and the COLLECT() formula.

Now I am trying to build a third module(C) where I will estimate costs for different years using the different growth assumptions from (A) and the Net Revenues from (B). But I am stuck when it comes to combining the result of my 2 first modules into the third one. The result of the calculation is 0... By checking the formula it seems that I don't get to pull the revenue figures.

Any idea on how I could solve this ?

Thanks a mill for your help ;)

rob_marshall

@HugoManchon

Usually it is not a good practice to create line items for specific years in that they will need to be updated as new years come and go. Instead, why not have the modules dimensionalized by Time at the Year level? For example, in your first module, the line item would be Growth Assumption % and the Timescale would be Calendar, but at the Year level. This will end up having a downstream effect on modules 2 and 3 as you will only need one line item.

Rob

HugoManchon

Hi @rob_marshall,

First of all, thanks for your feedback, really appreciated!

Basically, I have my planning figures for 2023 and the idea here is based on these 2023 figures to draw 24-28.

The idea is to forecast every year after 2023 based on Y-1. Meaning that the revenues of FY 2024 will be = Revenue FY 2023 * x%, Revenues of 2025 = Revenue FY 2024 * x% etc...

And then based on the revenue figures apply a % of Net Revenues to estimate costs.

If I am using modules dimensionalized by Time I can not give every year a different formula. Is there a way to do that?

rob_marshall

@HugoManchon

Would using the Previous() function not solve this? Create a line item called Data with Time (at the year level) with the formula being Previous (Data) * x%

HugoManchon

@rob_marshall

Thanks Rob, I have managed using the LAG() function. It might have been easier with PREVIOUS() but it worked ;)

Nevertheless, it brings up another question. Now that I have my Net Revenue, I would need to have my "Group Net Revenue" below it. I have removed the applies to for this specific line item but it brings me 0. Would you know how I could do that?

Cheers,

rob_marshall

@HugoManchon

I believe you have missed my point as your line items are still over-dimensionalized and should not be named a certain time period. They are over-dimensionalized because the timescale is CY+5, but the 2023 amount only happens for one year.

What does your 1.F23 Revenues module look like? If that is just the first year and only your first year, you can can create the following:

In your SYS Time module, create a line item for First Year, the formula will be the following.

In the 3. Y+5 Cost Allocation module, for Revenue, the formula would be

IF SYS Time.First Year? then '1.F23 Revenues'.Revenue else Previous(Revenue) * Net Revenue %

Now you can get rid of the other "time" line items.

HugoManchon

@rob_marshall Thank a mill Rob, I don't why I didn't think about the boolean for the first year. That is much leaner. I guess it will come with the experience🤞

If I may would you have any thoughts on how to bring the Total revenue, independently of my projects?

Cheers,

rob_marshall

@HugoManchon

Is your Total Revenue the addition of all members in the LISS?

HugoManchon

Morning @rob_marshall,

yes exactly and independently of the projects.

rob_marshall

@HugoManchon

Where you define your LISS, create a line item ("Total Revenue") where it is the sum of the other line items. In the blueprint view, scroll to the right and uncheck the Is Summary

and add that line item to the LISS. By doing this, it will act like a top level member and then you can do a SELECT on that member.