Forecasting based on a Driver in SAP BPC 10.0 NW

[Former Member]

Hi,

I'm trying to create an input template that has say for example actuals for 6 months and 6 months of forecast that I want to save. I have a column "Driver" that the user enters the number of months of actuals he wants to consider for forecasting for a particular account. So I've used excel offset formulas to come up with the forecast data so he can just submit the data.

The actuals that are on the excel are dynamically generated based on a property I have on the Time dimension. So when a new month of actuals are present I update this field on the Dimension and the excel should get the actuals for that month. Here is my problem. When I refresh the excel workbook, because the new Actual column had formulas (I selected "keep formulas on Sheet") I don't see the actual data from the database as the value in the formula is overriding the data.

Is there an alternate option for me to accomplish what I want. I want the Actual data for the months I have actuals and the forecast data is based on a calculation.  Not all accounts have a standard calculation. Please advice.

[former_member186338]

Hi Vamshi,

It's a long text description of complex input schedule but it's close to impossible to understand your requirements without number of screenshots and Excel formula samples...

Vadim

[Former Member]

The first screenshot is the Before, the second the After and The last screenshot is what I want.

On the second screenshot the formula is saved on the worksheet and the values are based on the formula instead of what the actuals are in the database. Only if I clear that column and refresh, I get the correct actual values. This is shown in the last screenshot. I hope this helps.

[former_member186338]

Hi Vashmi,

There are 2 solutions of your issue, both not perfect, but...

1. Use VBA to do the job!

2. You can use 2 reports (one as input form, second - read only) and a separate data entry area:

B.R. Vadim

[Former Member]

Hi Vadim,

I want the user to change just the value in the driver column and the values should be calculated automatically only for Forecast Time Periods and the user submitting the new values.

There are a number of accounts for which he needs to do this and therefore it is not possible for me to create 2 reports and a separate data entry as it will make the workbook difficult to understand.

Vamshi

[former_member186338]

Hi Vamshi,

Just add Excel formulas to calculate the value from driver instead of Data Entry section. What's the issue?

I have shown you the just the idea how to dynamically change the read only area and dynamically get data from different ranges.

Vadim