Variance Calculation between BPC reports

[Former Member]

Hi All,

I have created 2 side-by-side linked (the reports share the same axis) Income Statement reports in BPC. How do I add a 3rd report which will calculate the variance between these 2 existing reports. I think I would need to create a local member as the 2 reports are going to be dynamic across time and regions. Eg. I have 2013 & 2014 for EMEA - 15 entities, if I change the region from EMEA to Americas with 25 entities, I want the variance for all the 25 entities now instead of the previous 15. I also want the variance to show the drill down if I click on one of the reports to find the child members if they exist.

Any pointers?

Thanks!

[former_member186338]

Hi Monalisa,

Why do you need 2 reports? Variance can be calculated with a single report... Please, describe you report (page, column a row axis). Provide screenshot...

B.R. Vadim

[Former Member]

Hi Vadim,

Thanks for the prompt response.

I have the below:

Report 1: Made out of 5 reports, extending from Column C to Column X

Rows 10, 13, 14, 15 are local members.

Report 2: Made out of 5 reports, extending from Column Z to Column AU. These reports share the axis with all the reports comprising Report 1.

Starting column AW onwards, I want the variance between Report 1 & Report 2.

Reports 1 & 2 are not going to be static. Currently I have Report 1 for 2013 Actual & Report 2 for 2014 Actual. But I have the override formulas on A1, A2 & A3 using which I can change the entity display from EMEA, which is the current selection to some other region which can have different number of entities.

I need help in putting Report 3.

I am attaching the screenshots.

Looking forward to your response.

Thanks a ton!

[former_member186338]

Sorry,

But I still do not understand why do you need 2 reports?

And what do you mean by "Report 1: Made out of 5 reports"??? I don't see 5 reports...

Please describe axis! Page, column ad row! Dimensions and members!

Vadim

[former_member186338]

P.S. You can create a single report for years 2013 and 2014 with variance columns calculated as local members.

Vadim

[Former Member]

Hi Vadim,

Please find the screen shot which shows the number of reports. Default to Report 004 are part of Report 1 (2013) and 005 to 009 are for Report 2 (2014).

[Former Member]

How can I create both years in one report? I want the final report to be flexible such that I can pick and choose the reports to display the variance between 2012 & 2013 or any other time dimensions chosen.

[former_member186338]

Hi Monalisa!

I can't see your system and have no idea why you use so many reports... Your screenshots are not helpful

"Please describe axis! Page, column ad row! Dimensions and members!" - text description!

Vadim

[Former Member]

Please find the screenshot of the default report axis.

I need multiple reports as when I add local members for formulas they are getting misaligned if they are all pulled from a single report.

[former_member186338]

You don't "need multiple reports as when I add local members for formulas they are getting misaligned if they are all pulled from a single report" Something wrong is with your local member formulas!

But you don't want to provide a detailed text description for your axis with members... Then I don't want to ask more questions...

Vadim

[Former Member]

Hi Vadim,

I am not quite sure what details you are looking for. Could you please elaborate what other details can I provide?

Thanks!

[former_member186338]

All!

[former_member186338]

Just some idea:

You have 2 reports with dynamic number of columns (number of entities). 2 reports shows data for 2 years.

You can add 3rd report - the same as 2nd report with the same entities in columns.

For the 3rd report you can apply EPMCopyRange formula with the SourceRange as a line of cells longer than the maximum number of entities =EPMCopyRange("002",FALSE,V4:AK4,TRUE). In each cell of SourceRange (V4:AK4) put Excel formula calculating the variance between cell in 1st report and 2nd report.

Variance formulas will be complicated using Excel functions INDIRECT, ADDRESS, ROW, and COLUMN to calculate proper offset:

(V4) =INDIRECT(ADDRESS(ROW(V4);2))-INDIRECT(ADDRESS(ROW(V4);3+(COLUMN(V4)-3)/2))

(W4) =INDIRECT(ADDRESS(ROW(W4);3))-INDIRECT(ADDRESS(ROW(W4);3+(COLUMN(W4)-4)/2+1))

(X4) =INDIRECT(ADDRESS(ROW(X4);4))-INDIRECT(ADDRESS(ROW(X4);3+(COLUMN(X4)-5)/2+2))

...

Assuming that first data column of the first report is B - column number 2 and 2nd report is surrounded by empty columns: number of entities is calculated like 3+(COLUMN(V4)-3)/2)

The 3rd report will be overwritten by the variance formulas.

I have correct results when changing number of entities.

B.R. Vadim

[former_member186338]

Sample screenshot for this formula (H1)=EPMCopyRange("002";FALSE;I2:X2;TRUE):

Vadim

[Former Member]

Hi Vadim,

Thanks for your detailed messages. I am working with someone from work to figure out how we can use epmcopyrange on our report since we have multiple reports making up both the years as well. Unfortunately that is the way our data is stored and we cant really do anything apart from pulling multiple reports to make one income statement!

We have not used this EPMCopyRange before, but I believe it is like local members but works better than using multiple local members on the report.

Thanks again for your help and I will let you know if we are able to sort this out.

Regards,

Monalisa

[former_member186338]

Hi Monalisa,

I do not understand why are you using multiple reports for each year?

You have multiple reports in rows? Different models?

Vadim

[Former Member]

Hi Vadim,

It is all in the same model, but the way our data is entered we need multiple reports.

First we need to pull for: Cost Center - All, Account -  Subscription & PS our  2 rev streams

then 2nd report: Cost Center- Subscription, Account - Cost of Revenue & Operating Expenses

3rd report: Cost Center - PS, Account - Cost of Revenue & Operating Expenses

4th report: Cost Center - S&M, R&D, G&A, Account - Operating Expenses

5th report: Cost Center- All, Account - the 5 rows until Net Income.

We have now created multiple local members and got this working. EPMCopyRange function was extremely helpful.

Thanks a ton for your help.

Regards,

Monalisa

[former_member186338]

Hi Monalisa,

If you need so many reports for the simple P&L report then there is something wrong with your account dimension structure. Cost centers has to be somehow duplicated in the account members.

But if we are talking about rows then EPMCopyRange can do the job. You can close the discussion.

B.R. Vadim