cancel
Showing results for 
Search instead for 
Did you mean: 

YTD values in Rolling Forecast

Former Member
0 Kudos

Hi,

I have requirement to create a Rolling forecast with totals as shown in the color items.

I have created rolling forecast, but getting these totals into EPM Local members need advice.

I have maintained, Current period as a property in Version.

So as the Current period changes, my rolling forecast YTD values should adjust.

Local members attached to report(Member, Member combination, Dimension, Colums axis, Row Axis and Local member) --> how to make it dynamic? 

Suppose if Current period is 2015.02, then YTD Actual will be 2 months actual, YTD should be 2 months actual + 10 months forecast and YTD forecast is 12 Forecast values.

If Current month is 2015.08, then the YTD actual = 8 months Actual, YTD = 8 months Actual + 4 months Forecast and YTD Forecast = 12 months Forecast.

Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

In our system we simply copy Actual months to Forecast. And Year total is coming automatically. I am talking about forecast on the Periodic model.

Vadim

Former Member
0 Kudos

Hi Vadim,

Can you please let me know what is the importance of Forecast Periodic model? I am having Actual, Forecast and Budget values in YTD model?

In reports, as column axis I can mention measure as Periodic, to get the Periodic values for YTD model?

Thanks.

former_member186338
Active Contributor
0 Kudos

I was talking about P&L planning - revenue and expenses! In this case it's natural to plan in Periodic model with revenue and expenses per month...

After planning is finished the results can be transferred to YTD model for consolidation.

Vadim

P.S. Look on the sample in IFRS kit!

Former Member
0 Kudos

Hi Vadim,

Thanks, I am using the same approach, forecast and budget are updated in another model and transferring those values to YTD Consolidation model.

Thanks.

Former Member
0 Kudos

Hi Vadim,

Suppose, if we are in month 2015.07 and we overwrite the values of actual(2015.01 to 2015.07) to forecast(2015.01 to 2015.07 actuals and 2015.08 to 2015.12 forecast values).

Rolling forecast is completed for next months(User make changes to 2015.08 to 2015.12 forecast values).

In 2015.08, we overwrite again actuals to forecast. If users want to see the values of 2015.07 forecast or earlier periods, they wont be available as we are overwriting forecast values with actuals.

Is there any way to give users the flexibility to see the earlier forecast values in the system?

Thanks.

former_member186338
Active Contributor
0 Kudos

Create FORECAST01-FORECAST11

FORECAST01 - JAN - ACTUAL

FORECAST02 - JAN,FEB - ACTUAL

...

Former Member
0 Kudos

Hi Vadim,

Thanks for your advice.

User wants Forecast 12 months Total.

Suppose we are in Period 2015.07.

Report should show:

Actual -> 7 months

Forecast --> 5 months

Actual + Forecast = 7 +5 = 12 months(Total, which is achieved by copying actual months to Forecast).

Forecast 12 months(5 months 2015 and 7 months 2016).

In my Report, I have the periodic values for all Forecast values.
i.e from Period 2015.08 to 2016.07.

How to get this total figure is my problem?

I have tried sumif(version = Forecast, then sum the values) but local member is giving error as impossible to apply local formula --> might be because I am using Column axis member as condition?

My Starting period column changes as the month increase or decrease? How to make this dynamic calculation?

former_member186338
Active Contributor
0 Kudos

I have already explained you a working solution. Our company is using it for 4 years... Why do you want to use local member?

Former Member
0 Kudos

Hi Vadim,

My Report requirement is:

Current month is 2015.07

2014.01 to 2014.12 (each month as column) ---> Actual

2014.12 Total ( as one column) --> Actual

2015.01 to 2015.07 (each month as column) --> Actual

2015.08 to 2015.12 (each month as column) --> Forecast

2015.12 Total ( as one column) --> 7 months Actual + 5 months Forecast ( this I copied actuals to Forecast and showing Forecast 12 month YTD)

2016.01 to 2016.07 ( each month as column) --> Forecast

2016.07 Total ( as one column) --> 2015.08 to 2016.07 - 12 months Forecast Total

2015.01 to 2015.12 (each month as column) --> Budget

2015.12 Total ( as one column) --> Budget

2016.01 to 2016.12 (each month as column) --> Budget

2016.12 Total (as one column) --> Budget

All blue colour are completed, except this Red colour one.

I am using local member, because this is rolling forecast and as the period changes, number of columns increase or decrease based on present month.

I have budget data after forecast, so I think I cannot use excel formula. Correct me if I am wrong.

former_member186338
Active Contributor
0 Kudos

"this I copied actuals to Forecast and showing Forecast 12 month YTD" - why YTD instead of 2015.TOTAL periodic?


For 12 month total you have to create new Custom MEASURE.


Vadim

Former Member
0 Kudos

Hi Vadim,

Why YTD instead of 2015.Total periodic? You are right, my mistake it is 2015.Total.

For Red colour total, I have to create custom measure?

I have below link how to document to create custom measure, it is valid for 10.0 version or any other document exist.

I cannot add the weblink some problem with system. Added the image of document.

please let me know if this is still valid document.

Thanks

former_member186338
Active Contributor
0 Kudos

Please look here:

Custom Measure Formulas - SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP L...

LastPeriods (MDX)

And evaluate LASTPERIODS with negative index to get future 12 months sum.

Vadim

Former Member
0 Kudos

Hi Vadim,

Rolling 12 Custom Measure in BPC 7.X version for MS and NW link is giving error as shown in screenshot.

former_member186338
Active Contributor
0 Kudos

Yes, and what? Just start creating custom measure yourself with something like:

SUM(LASTPERIODS(-12,[%TIMEDIM%].Currentmember),[Measures].[Periodic])

Former Member
0 Kudos

Hi Vadim,

I have Consolidation Model of YTD and I have Planning model also YTD.

I have 11 dimension in Consolidation model and 7 dimensions in Planning Model.

Consolidation  has extra dimensions like Cost center, profit center, segments, etc.

Client wants to do planning at High level with no cost center, profit center, so created another model Planning with less dimensions.

All data from BW is uploaded to Consolidation model and transfer the same to Planning.

Problem:

When I check the data in Consolidation model P&L report, I have YTD values and when switch the measure to Periodic, Model is showing correct periodic values.

Report with Measure = Periodic

Report with Measure = YTD

I have used Data Manager package to transfer the values from Consol to Planning. Here is the script.


Here is the report in Planning model

Report with Measure = YTD

Planning Report with Measure = Periodic

I think  at every Quarter, system is showing YTD values.

Please let me know how to correct this, I am using the same Time dimension for both the model. Is there something wrong with logic script? or something else?

Thanks.

former_member186338
Active Contributor
0 Kudos

BPC & BW version and SP?

Strange to use YTD model for planning...

Former Member
0 Kudos

Hi Vadim,

BPC version is 10.1, BW version is 7.4 SP - 010 and CPMBPC is 810 SP - 0005.

Why cant I use YTD for planning? Is there any logic to use YTD or Periodic for Planning?

I have uploaded flat file of transactional data to Consolidation Model to get the above results.

I have modified and uploaded the same file with exact same data deleting unnecessary dimensions in Planning model.

Consolidation shows correct values.

Planning model shows wrong values.

Thanks.

former_member186338
Active Contributor
0 Kudos

"BPC version is 10.1, BW version is 7.4 SP - 010 and CPMBPC is 810 SP - 0005." - Please upgrade to the latest for both!!

"Why cant I use YTD for planning? Is there any logic to use YTD or Periodic for Planning?" - The logic is simple - we are talking about P&L planning and for P&L accounts planners normally enter the results per month, not the accumulated result from the beginning of the year.

Vadim

Former Member
0 Kudos

Hi Vadim,

Other than upgrade there is no solution for this behaviour?

As per standard ,it should work. I hope I didn't made any mistake.

I have to create a custom measure and this issue blocked creation of custom measure.

Meanwhile I will raise OSS note for the same.

Thanks.

former_member186338
Active Contributor
0 Kudos

"Meanwhile I will raise OSS note for the same." - no reason, upgrade will be recommended!

But why not to upgrade?

Former Member
0 Kudos

Hi Vadim,

Ok, as suggested, I will try to get the system upgrade and let you know the results.

We are planning for Balance Sheet items too, so it is fine to make Planning application as YTD or you recommend to use Periodic?

Thanks.

former_member186338
Active Contributor
0 Kudos

Please, read some documents Balance Sheet accounts are YTD even in Periodic model...

But for P&L items planning in YTD is something strange!

Former Member
0 Kudos

Hi Vadim,

We are having the Perodic and YTD values issue in all the models. When we open any report in Periodic, the values are correct. But when we add any XXXX.TOTAL or any Period with Measure = YTD.

Then the Quarters periods like period 4,7,10 are getting YTD values.

As suggested we tought of going for Upgrade, but it is lengthy process, luckily we found a SAP NOTE

We didn't upgrade the system but update the SAP NOTE:

2136030 - Incorrect periodic values for YTD model - BPC NW 10.0/10.1

This fix all the issues.

Now I am creating Custom Measure R12 i.e Rolling Forecast.

Here is the code I have created, even though I didn't understand how to write the code, somehow did it.

When I run my report with this code, it is giving values but not the values I am expecting.

Here is the code I have in Custom measure

Now how to check what values it is totalling, as per my calculations I have uploaded data for Forecast with every month 200 dollars to Sales Account.

R12 should bring me 2400, but it is showing 32400.

Here is the report, System should Sum the last 12 months.

Thanks.

former_member186338
Active Contributor
0 Kudos

Model type? Did you created Periodic Planning model?

Former Member
0 Kudos

Hi Vadim,

Model Type for Planning is still YTD.

If everything is working fine as clients requirement, then I will continue with YTD model.

If we get any error for data input, will create new Planning Model with Periodic.

Thanks.

former_member186338
Active Contributor
0 Kudos

Sorry, but with YTD model you will not be able to save planned Periodic data to P&L accounts! Is it really your client requirement???

Former Member
0 Kudos

Hi Vadim,

You are right, I cannot save Periodic values in YTD model . I have to change the model to Periodic.

Is there any easy way to do it?

Like any special program to change the model type.

I tried to change in Admin, but it is greyed out.

Thanks for the tip otherwise, i am in wrong impression, that we can do Periodic planning at YTD model.

Thanks

former_member186338
Active Contributor
0 Kudos

Simply delete YTD model and create a new Periodic model! Number of things are different between YTD and Periodic including all standard measure formulas.

You will also need a script to perform YTD -> Periodic conversion (please search forum - I have explained it many times).

Then you can continue with custom measure.

Vadim

P.S. Looks like none of my answers are useful?

Former Member
0 Kudos

Hi Vadim,

As all my dimension will be same.

I am creating a New Planning model, while creating i am selecting the Source Model from which to copy i.e Blank Model or existing model.

Here i am using my YTD model, so that my Data Manager Packages copies to new Periodic model.

I have created a Periodic Model similar to YTD model and copying the reports from YTD Model to Periodic.

Created new script as mentioned, there are 2 scripts u posted. One with Select Statement with Period 2 - Period 1 values. And other with Change in Measure - Periodic.

I am getting error in DM for running First script as Select statement is wrong.

*SELECT(%PL%,"[ID]",ACCOUNT,"[ACCTYPE]='EXP' OR [ACCTYPE]='INC'")

*XDIM_MEMBERSET ACCOUNT=%PL%

Could you please let me know where is the mistake, all the properties ACCTYPE exist n my model.

Thanks

former_member186338
Active Contributor
0 Kudos

Please open a new discussion!

Answers (0)