Forecast Phasing with Adjustment Issue

Monpero
Monpero
in Modeling

Hi All,

I have a module that I am trying to do a phasing

Phasing Example.xlsx

.

(a) Forecast Amount: have an even spread amount as (current best guess) and Total spending amount for FY23 is 22,000.

(b) In the Adjustment Line: I want to enter an adjustment for certain months with a new amount (Due to the knowledge of certain month will incur more spend). And balancing figure is -500

(c ) In the Target Adjustment Line: I want to put the balancing figure in this case -500 and I want to put it in December amount also I want my total to result to 22,000.

(d) In the Total Line: I want to add my forecast amount + Adjustment to result to 22,000 for FY23.

What is the best way / workaround to achieve this?

Any suggestion will be really helpful.

image.png

Attached is the copy of the excel

Best Answer

  • pyrypeura
    pyrypeura
    Answer ✓

    Do you want your balancing figure to always be in December? If always december then your formula for Target Adj could be something like "IF December? Then YEARVALUE(-Manual Adj) ELSE 0"

Answers

  • Dikshant
    Dikshant

    @innocent, Not able to understand the complexity or blocker to achieve this? Amount, Manual Adj & Target Adj looks like an manual input fields/ imported line items. And Total is an summary of all these 3 line items. let me know if there is more complexity to this which I might be missing.

  • Monpero
    Monpero

    Hi @Dikshant what I'm trying to explain is, in my module for example in the "TEST" line item the formula is =

    IF Manual Adjustment = 0 THEN Amount ELSE Manual Adjustment + YEARVALUE(Manual Adjustment). The problem is in the total I want it to be 220,000 but it is totalling to 1,545,000.

    (b) The Final Cost to Use line item = Manual Adjustment + Target Adjustment

    and totals is 220,000. (But I don't think this is the better way of doing it)

    The ask is, how can I add the adjustment and the amount line item to result to 220, 000.

    Is there a work around or a best way to achieve this?.

    image.png

  • Monpero
    Monpero

    @pyrypeura yes I want my balancing figure to be in December but I also want the total of the forecast spend to remain unchanged 220,000.

    The reason for the adjustment is that certain month will incur more spending and we want to increase the spending for those month but the total target set for forecast remain unchanged.

  • pyrypeura
    pyrypeura

    Then instructed formula will keep your forecast spend unchanged as the target adj is same as yearvalue of manual adjustment.

  • Monpero
    Monpero
    edited January 4

    Hi @pyrypeura thanks a lot!.

Categories