Reading the Higher Level figures

PrevContributor
PrevContributor
in Modeling

Dear Team, I have a business scenario (refer to attached file). The Formula for Net Volume is not working because Returns % are maintained by Year however even if the Returns % are maintained by Months it is not picking up the values In addition to above, YEARVALUE does not allow selection using SELECT or LOOKUP as parameter. For Example: YEARVALUE (Returns (LOOKUP: REGION) OR YEARVALUE ( Returns) [LOOKUP: REGION) gives error messages Given above I could ask Users to maintain the returns % by month,  add a formula as following to read return % IF Customers.Territory = Territory.US THEN Returns[SELECT: Territory.US] ELSE 0 Question is there is a better  way to achieve above? Thanks for reading my post (refer attachment) and look forward for your suggestions! Cheers, Umesh

Tagged:

Best Answer

  • PrevContributor
    PrevContributor
    Answer ✓
    Hi Umesh -

    I'd be inclined to just have a separate line item named something like 'Returns % by month' which is split by Territory and Month.  This would just have the formula YEARVALUE(Returns).

    Your net volume formula would then just be

    Net Volume = Gross Volume * (1 - Returns % by Month[LOOKUP: REGION])

    A nice extension on this might be to allow the users to override annual returns value in any individual month, i.e. have three line items in the Territory x Month module...

    'Returns % by month' = YEARVALUE(Returns)
    Returns Override which is data input
    Finalised Returns = IF Returns Override <> 0 THEN Returns Override ELSE 'Returns % by month'

    Hope this helps.

Categories