Consolidation to create forecast

[Mark RMBC]

Hi all,

version 10.1
Apologies in advance if there is not enough information, obviously more can be provided, I guess at this stage I am hoping for the 'in theory' answer.

I have a model which, among other things, calculates a staffing forecast and allows users to make adjustments to the calculated forecast. Currently the user adjustment adds a value to the calculated forecast. So for example if the total of the calculated forecast is 100 and the user enters an adjustment of 10 the new forecast is 110. However the client would like to move away from this method and instead have the user adjustment replace the forecast rather than adding to it. So for example if the total of the calculated forecast is 100 and the user enters an adjustment of 10 the new forecast is 10.

I am trying to think of the best way to achieve this and cannot think much beyond adding in some N level rules, I pretty much have this approach figured out. But I was hoping I could change the consolidations instead, however thus far without much success!

So I am looking for some help!

I have attached a file which provides a bit more information.

Consolidation for Forecast.docx

(57.19 KiB) Downloaded 256 times

cheers, Mark

[tomok]

There are several options you can try but the easiest will likely be to add two new nodes: a consolidated node called "Period Total w/o Adj", that includes everything except the adjustment and another called "Adjustment". I would then move the "Manual Adjustment" node out of the "Period_Total" rollup. Then you would write a rule to make the Adjustment be equal to the Period Total w/o Adj minus Manual Adjustment. Something like this:

Code: Select all

['Adjustment'] = IF(['Manual Adjustment'] <> 0, ['Manual Adjustment'] - ['Period Total w/o Adj'], 0);

This will work if your adjustment is to make the Period_Total equal to a number other than what it calcs to. It will NOT work if you want the final number to be zero. This is because it will fail the IF condition. You have two options; 1) tell the users to input .0001 when you want it to equal zero (assuming this is a financial app that is a sufficiently small number) or 2) add another element called "Adjustment Flag" that falls outside any hierarcy, change your IF condition to use this node, and tell users they have to enter two things when they make an adjustment, enter the adjustment amount and put a 1 in Adjustment Flag.

Code: Select all

['Adjustment'] = IF(['Adjustment Flag'] <> 0, ['Manual Adjustment'] - ['Period Total w/o Adj'], 0);

Don't forget you'll need to feed "Adjustment".

[Mark RMBC]

Hi Tom,

Thanks for the reply.

With a few tweaks, to take into account the particulars of the model that I failed to mention in the initial post, this does seem to work.

My only concern is that the Manual Adjustment element is now an orphan. Now in my experience orphaned children in a dimension have caused some MDX issues, are you aware of any pitfalls with orphaned children in a dimension?

Incidentally the new dimension is attached, does this look as you envisaged?

New Consolidation for Forecast.docx

(21.84 KiB) Downloaded 273 times

Cheers, Mark