Spreading the Forecast value down different Versions

[Former Member]

Hello Experts,

I have a report in the following format - please find attached diagram of the report format

Version	Org Hierarchy	Key Figures	Oct-Forecast	Nov-Forecast	Dec-Forecast
V1	H1	Order	100	200	300
	H2	Order	100	200	300
V2	H1	Order	500	600	700
	H2	Order	500	600	700
	H3	Order	500	600	700
V3	H1	Order	100	500	900

Basically the data above is all Forecast Data, that has been entered by the Users - by means of an Input Form.

This is just an example, for us we have way more Org Hierarchies in our Input Form and there is a Version for each set of Hierarchies - Set (selected hierarchies).

We are running a Six Month Forecast and for that we have Six Versions- so for example if we have selected 2 Org Hierarchies for the Six Months to which we will need to Forecast; then we will have Six Versions for these Two Org Hierarchies.

Now the initial Forecast that the User does (in V1), at the start of the term, we will like it to be spread across the other Versions as well by means of a Copy Function, as shown in the Table above.

In the start of the Term, the Forecast will be done on the First Version; then the other Versions (V2 - V6) should take the values of V1 and its respective Cost Center in their (V2 - V6) respective rows.

Kindly advise if we have a Dynamic way to approach on this functionality.

Regards,

Sachin

[Former Member · Accepted solution]

Hello Vadim & JP,

For the above requirement, here is a detailed explanation.

With reference to the above Table -

Version	Org Hierarchy	Key Figures	Oct-Forecast	Nov-Forecast	Dec-Forecast
V1	H1	Order	100	200	300
	H2	Order	100	200	300
V2	H1	Order	500	600	700
	H2	Order	500	600	700
	H3	Order	500	600	700
V3	H1	Order	100	500	900

We run this report for two terms - Fiscal Term 1 - 6 months (Apr - Sep); Fiscal Term 2 - 6 months (Oct - Mar)

The First time the user runs the report for a new term, the user can enter forecast data. This initial entry of the Forecast data will be entered against V1. As soon as Save Data is hit, the data that is spread across V1 should be copied for V2 - V6 for the same combination of Cost Center, Key Figure and Time Dimension (Periods).

Version is used to calculate the Performance for a given Period. Performance of the first period is calculated after Actual Data is loaded for the First period of the term.

Hence I mentioned earlier, V1 - Period1; V2 - Period2; V3 - Period3; V4 - Period4; V5 - Period5; V6 - Period6.

Now based on the Actual data of the closed month and the performance of that period, forecast of the future months will be edited to maintain the Performance going forward.

Version	Org Hierarchy	Key Figures	Oct-Actual	Nov-Current	Dec-Forecast
V1	H1	Order	300	400	300
	H2	Order	400	400	300
V2	H1	Order	600	800	700
	H2	Order	700	800	900
	H3	Order	900	800	900
V3	H1	Order	800	800	1000

This copy of the Forecast Data in the start of the Term, from V1 to V2 - V6 needs to be done only once, that is, at the start of the term. After every periods closing Forecast will be maintained manually, no requirement for copying them across the other versions.

So what I will show for performance tracking is, when I select a particular period, eg. Period2 then V2 will be displayed and its performance during that Period.

I hope you have understood the requirement and hope to receive favorable opinions.

Regards,

Sachin.

[former_member186338 · Accepted solution]

Please list all dimensions used! Real dimension names and some id's samples.

I am not able to understand your terminology

"We run this report for two terms - Fiscal Term 1 - 6 months" - ???? What do you mean by "term"?

What do you mean by "Key Figure"?

Do you use BPC standard?

[Former Member · Accepted solution]

Hello Vadim,

I am using  BPC Standard Model.

The Time Dimension is maintained in the following way -

2000.Total

     2000.Year

          2000.K - Fiscal Term 1

               April

               May

               June

               July

               Aug

               Sep

          2000.S - Fiscal Term 2

               Oct

               Nov

               Dec

               Jan

               Feb

               Mar

This report is a 6 month Rolling Forecast report and we display, at the time of First Forecast, based on the Fiscal Term, where the data will be displayed as the screen shot above.

The selection is done as below -

Now, for example, when we go to the current month - September, 2015 (only for example purpose), the display should be as below

The yellow color denotes the Current Period showing Forecast values - not editable. But based on the current Periods Performance, which is tracked by Term%, the Forecast thereafter are adjusted to meet the Performance needs. In this case we cannot as September is the last period of the term, but if it was August that was being displayed as current month, then we will adjust forecast values of September to meet Performance needs.

I hope this is understood much better now. Kindly let me know your thoughts on this.

Regards,

Sachin

[former_member186338 · Accepted solution]

Ok, more or less clear with the TIME dimension (Fiscal Term is HALFYEAR )! What is the real name of TIME dimension?

Now please the same for other dimensions!

CATEGORY type

ENTITY type

ACCOUNT type

other user defined type

And something strange about Key Figure - what is the dimension? what is the dimension type?

Why do you use strange Z -id's...

[former_member186338 · Accepted solution]

P.S. If you are working with BPC standard model you have to use BPC standard terminology...

[Former Member · Accepted solution]

Hello Vadim,

Real name of Time Dimension - Time

Cost_Center - Account Type

VType - Category Type

ZKeyfigure - Entity Type - (Zord,Zordpft,...)

Version - User defined Dimension.

ZIDs - are for the multiple Key Figures that I need from BW which I have loaded into BPC ZKeyfigure dimension of Entity type using MVAL property.

Regards,

Sachin.

[former_member186338 · Accepted solution]

Cost_Center - Account Type?????????? Are you sure???

ZKeyfigure - Entity Type - (Zord,Zordpft,...)?????????? Are you sure???

VType - Category Type - members, structure?

Version - User defined Dimension. - members, structure? why do you need extra dimension?

"ZIDs - are for the multiple Key Figures that I need from BW which I have loaded into BPC ZKeyfigure dimension of Entity type using MVAL property."

Why do you need to use Z prefix????

[Former Member · Accepted solution]

Cost_Center - Account Type?????????? Are you sure??? - Yes

ZKeyfigure - Entity Type - (Zord,Zordpft,...)?????????? Are you sure??? - Yes

VType - Category Type - Actual, Plan, Forecast

Version - User defined Dimension - 2015-01, 2015-02, 2015-03, 2015-04, 2015-05, 2015-06, 2015-07, 2015-08, 2015-09, 2015-10,2015-11,2015-12

ZIDs - because they are custom generated KFs and same naming convention used in BW.

Regards,

Sachin

[former_member186338 · Accepted solution]

"Cost_Center - Account Type?????????? Are you sure??? - Yes

ZKeyfigure - Entity Type - (Zord,Zordpft,...)?????????? Are you sure??? - Yes"

Absolutely incorrect! You have to read something basic about dimension types in BPC!

"VType - Category Type - Actual, Plan, Forecast

Version - User defined Dimension - 2015-01, 2015-02, 2015-03, 2015-04, 2015-05, 2015-06, 2015-07, 2015-08, 2015-09, 2015-10,2015-11,2015-12"

Why do you need 2 dimensions? Can be combined in one Category:

ACTUAL,PLAN, FCST01,FCST02...FCST12

"ZIDs - because they are custom generated KFs and same naming convention used in BW."

Nobody use Z id's in BPC, bad practice!

[Former Member · Accepted solution]

OK, I will correct the Key Figures, and look into the basics of BPC for correcting the Dimensions.

But for this current requirement, can I ask if there is a solution with the above mentioned details.

Regards,

Sachin

[former_member186338 · Accepted solution]

Sorry, it's useless to discuss some scripts in the incorrectly configured system !

Also using 2 dimensions for Category/Version will create unnecessary complexity without benefits...

P.S. By the way, you can't change the dimension type, you have to create a new dimension with a correct type!

[Former Member · Accepted solution]

Ok, I have a Dimension Account that has been loaded with the Cost_center Master Data, Entity remains as a separate Dimension.

I have an User-defined Key Figure that has all Key Figures from BW using MVAL.

Category has Actual, Plan and Forecast.

Version is a separate Dimension.

Can you tell me how I can implement the same if I use just Category as a Single Dimension with members Actual, Plan, Forecast, FCST01,FCST02...FCST12.

Regards,

Sachin

[former_member186338 · Accepted solution]

"Ok, I have a Dimension Account that has been loaded with the Cost_center Master Data, Entity remains as a separate Dimension."

Incorrect, redesign required!

Your so called KeyFigure has to be account type and Cost_Center - Entity type!

"Can you tell me how I can implement the same if I use just Category as a Single Dimension with members Actual, Plan, Forecast, FCST01,FCST02...FCST12"

You need versions for FORECAST! Then instead of single FORECAST category - create multiple forecast categories - one for each version!

[Former Member · Accepted solution]

No, that wouldn't do it.

Forecast is a Category of its own.

I am using Version as a separate Dimension so that using this dimension property I can report on Just Version, Cost Center and Term% to create Charts.

As a result I have a combination of Version, Cost Center and Term%; with Version existing for Individual Time Periods.

Regards,

Sachin

[former_member186338 · Accepted solution]

But you don't have versions for Actual and you have different versions for Plan (if any).

In our system used for years we have forecast versions in the category dimension without any issues in reporting. And it's clear from the business point of view: each category id represent some set of figures: Actual, Budget Plan, Forecast at the level of some month...

First budget is planned

Then in the beginning of the  2nd month of the Year the first forecast is planned: first month is copied from Actual and the rest - from budget. Then users can adjust months 2-12 copied from budget.

Etc...

Any extra dimension has to be added taking into consideration that it will affect performance...

[former_member186338 · Accepted solution]

P.S Also work status setup with single category dimension is simple and clear! And you will have to use it...

[Former Member · Accepted solution]

Alright!

Let me show you the report format, that we have been asked for, once again.

Version(User-Defined)	Cost_Center(Entity)	KeyFigure(Account)	2015.K	2015.01	2015.02	2015.03	2015.04	2015.05	2015.06	Term Total	Term%
			PLAN	ACTUAL	ACTUAL	ACTUAL	ACTUAL	FORECAST	FORECAST		TermTotal/PLAN
V1	D000CT1202	Order	150	20	20	30	30	25	15	140	93%
V2	D000CT1202	Order	250	30	30	40	40	35	25	200	80%
V3	D000CT1202	Order	155	25	25	30	30	20	15	145	93%
V4	D000CT1202	Order	355	40	40	50	50	100	80	360	101%
V5	D000CT1202	Order	655	80	90	90	100	200	200	760	116%
V6	D000CT1202	Order	455	40	50	50	50	100	200	490	107%

Now the Budget (ID - 20), Actual (ID - 10), Forecast (ID - 80); the Budget and Actual values are populated from BW for each Fiscal Period by Cost Center.

The values in the report displayed above, come from BW except for Forecast as that is Input by the User.

Dependent on the property of Time Dimension; CLOSED = A(Actual), C(Current), ""(Forecast), the above dynamically changes. Dynamic changes is done using Excel formulae and using cell references on the Row Headers. So even if a Half Year term has completed, which means all Periods under that term show Actual Data - then the Versions for each period will have the Term% for the Actual; which is also a Performance indicator to see if we need to improve on the performance in the next half year term.

Version will hold both Actual and Forecast - I understand from your reply what you have said; but this is the scenario we have been asked to implement and I have already implemented till this much, all I need is to, from my first question, copy the Forecast that is input in Version 1 at the start of the Term, to all other Versions. As we progress through the Term, through different periods, the Users will be entering the Forecast for the Current Period + 1 which will be stored against the Current Period +1's respective Forecast.

Regards,

Sachin

[former_member186338 · Accepted solution]

Sorry Sachin, but it's your job as a consultant to discuss with the customer what is the right way to implement forecasting in BPC. I see a lot of issues in the proposed approach (too many to list here). If you don't want to listen then try on your own

[Former Member · Accepted solution]

- Can you tell me how I can implement the same if I use just Category as a Single Dimension with members Actual, Plan, Forecast, FCST01,FCST02...FCST12

If I have it in this format, will I be able to use the members, FCST01,FCST02...FCST12 as Row Headers for each combination of CostCenter and Key Figure.

Kindly advise.

[former_member186338 · Accepted solution]

You are thinking in terms of report format instead of forecasting process! I can't help you.

[Former Member · Accepted solution]

Ok. Thank You!

Regards,

Sachin

[Former Member · Accepted solution]

Hello Vadim,

I have corrected the design and made the Key Figure dimension as Account type and Cost Center as Entity type dimension.

Vtype - Category has the following three members - Actual, Plan and Forecast.

The reason I have included a new User Defined Dimension - Version is because there is going to be another report just for checking on peformance - which will take Version and KeyFigure in the rows and Time and VType-Forecast in the columns. This was the report format that was asked of us to create, hence I have added this extra Dimension - Version. Do enlighten me if we can do the same approach (requirement as stated in this note), in the approach you suggested in the earlier notes.

Regards,

Sachin

[Former Member]

Hello,

The Org Hierarchy/Cost Center is a Dimension with Hierarchy loaded.

At the start of the term, we display the Six periods of that term for which Forecast has to be done.

So as I mentioned earlier, at the start of the term, we have the Versions V1-V6 and the user will input the data for V1.

As soon as the user clicks on Save Data(event) to save the Forecast values into the BPC cube, the data should be saved for all Six versions; with the V1 copied to V2- V6.

Example: FiscalTerm - April 2015 - September 2015; Version - V1 - V6

V1 - April 2015

V2 - May 2015

V3 - June 2015

V4 - July 2015

V5 - August 2015

V6 - September 2015

the versions are controlled dynamically by the Time Period selection. If I select FiscalTerm - V1 - V6 will be displayed else if period is selected then the respective Version will be displayed.

Regards,

Sachin

[former_member186338]

Ok, Simply you have CostCenter dimension (no reason to mention hierarchy).

To copy data after save default.lgf can be used.

But what about data entry to other versions later?

What is the complete process?

"the versions are controlled dynamically by the Time Period selection. If I select FiscalTerm - V1 - V6 will be displayed else if period is selected then the respective Version will be displayed." - absolutely not clear!

What is "FiscalTerm"? TIME dimension or?

[former_member186338]

P.S. Please use real dimension names and real member id's.

[Former Member]

HI Sachin,

Is Org Hierarchy a dimension? Do you want to only copy the data from V1 to other Versions V2 to V6?

Can write a script logic in the default logic to copy the values from version1 to other versions....so that it dynamically gets saved after the users inputs the data on V1.

sample script:

*WHEN CATEGORY

*IS V1

*WHEN COSTCENTER

*IS BAS(CC_ALL)

*REC(FACTOR=1,CATEGORY=V2,V3,V4,V5,V6)

*ENDWHEN

*ENDWHEN

Regards,

JP

[Former Member]

Hello JP,

When does the script get implemented? And how do I call this script.

Regards,

Sachin

[former_member186338]

You have to explain everything clearly, from the user point of view - then you will get a correct answer!

[Former Member]

Hi Sachin,

You have to copy this script in Default.lgf script logic. Default logic is called/triggered from the Excel whenever data is saved from Excel...its triggered automatically.

Regards,

JP

[former_member186338]

Hi JP,

I think that the topic author has to explain the business process first of all. Proposing script in default.lgf is too early

[Former Member]

Sure Vadim......He should answer the questions and explain. I gave a suggestion not a specific solution...hope you understand...:)

[former_member186338]

Sorry, but not clear...

What do you mean by "Org Hierarchies"?

What do you mean by "we will like it to be spread across the other Versions"?

[Former Member]

Hello Vadim,

Org Hierarchy -  Cost Center

Version	Org Hierarchy	Key Figures	Oct-Forecast	Nov-Forecast	Dec-Forecast
V1	H1	Order	100	200	300
V2	H1	Order	100	200	300

By Spread, I meant to copy the data of V1 - Version 1 to Version 2, automatically, for the same combination of Cost Center and Key Figures, for the different Time Periods.

Regards,

Sachin

[former_member186338]

If you are using bpc standard model then you are using slightly incorrect terms...

No multiple keyfigures are supported, instead of hierarchy you have to use dimension etc...

[former_member186338]

"By Spread, I meant to copy the data of V1 - Version 1 to Version 2, automatically, for the same combination of Cost Center and Key Figures, for the different Time Periods."

What do you mean by automatically? After what event???