SAC Planning based on the project starting month and duration of a project.

[parth_thakkar]

Hi All,

We have a requirement in SAC Planning where we have revenue data till the present month. A few projects for which amount is maintained are assigned to each company code. These projects may / may not have the same starting date of project implementation and this starting date is maintained in a generic dimension. We have another generic dimension which contains the duration for which each of these projects would go on, in number of months.

The requirement is that we need to equally allocate these amounts per project to the months for which the project will be implemented from the date / month of project implmentation.

for eg. if Project ABC has INR 10,00,000 amount allocated and the project starting date is from August 2021 for 4 months of project implementation duration, then I want to allocate this amount equally for August, September, October and November 2021. But this duration and starting date may vary from project to project. Hence the allocation step should dynamically calculate the allocation amount based on duration of project and starting date and then equally allocate the amount from that particular starting month to the number of months the project is supposed to go on.

Please view the below image for your reference. I need to allocate amount 814554 from 008.2021 to 010.2021 equally. Can anybody please suggest what could be the best way to perform this, either any advanced formula or data action script or embedded allocation process?

[geeta2021]

Hello Parth,

Yes, one of doing is with Advanced formula in Data action.

In Advance formula you can calculated no.of months based on start date and end dated of the projects and distribute your values equally based on no of months. Include this data action in you story and execute based on requirement. This would help you , in case any projects end dates are changes then you can recalculate your values.

Please don't forget to scope the data so that your can filter as much as you can for better performance.

Thanks & Regards,

Geeta

[parth_thakkar]

Hello Geeta,

Thank you for your suggestion. I tried dividing the project amount in data action step and then allocating the divided amount to the months using allocation process but I wasn't able to allocate it to the months.

Also, Can you elaborate about scoping the data?

[madhi_2308]

Hi Parth Thakkar,

I am also having the same requirement, need to divide the revenue amount and split on years. Like if the project starts on 2022 & ends on 2024. My amount should be divide it for 2022,2023,2024. Could you please help me to do this in SAP analytics Cloud.

Thanks in advance

MS

[JefB]

Using data actions, check out the DATERATIO() formula. It calculates how many days between the start and end date overlaps the given period. Then divides the overlapping days by the total number of days in the period.
See help files here:

All Script Formulas and Calculations in Advanced Formulas - SAP Help Portal

Here's also a nice blog on a more advanced use case:
Adding a New Project and Distributing the Charges Across Fiscal Periods | SAP Blogs

[parth_thakkar]

Hello Jef,

Thank you for your suggestion and the blogs you shared. I have gone through the blogs.

However, my requirement isn't that complex I believe. My model and story already have the starting period and end period for each project which are to be distributed amongst the periods of this year. I may have many projects and all may have a different duration of project implementation in months and differenting start periods. So can you help me on how can I design the solution using data action + advanced formulas / allocations to allocate my 'Revenue DS - Domestic Sales' equally to these periods? You can refer to the screenshot that I am sharing of my story. The revenue amount can be divided equally by the number of months for project implmentation and allocated to as many periods in a new version.

Also, do we need to have the start and end date in 'date' format only or 'period' format too would suffice the need?

Refering to the first project in the screenshot attached, I need 500,000.00 to be divided equally i.e 1,25,000 to be allocated to 202105, 202106, 202107 and 202108 in a new version using data actions + advanced formaulas / scripts / allocation step.

Would be grateful if you help us out elaborately. Thanks in advance.