Level 3 calculate the Quaterly Initial Sales Target

titiboys
titiboys
in Academy Discussions

titiboys_0-1609696278800.png

Hi community,

 

I am investigating for a long time to find the formula to calculate the Quarterly Initial Sales Target.

According the user story ,

 

titiboys_1-1609696452670.png

I checked all my data and 

according your screenshot for Nutzo bar I must find 670.8

titiboys_2-1609696614059.png

 

Using my values I cannot obtain this figure for US, Nutzo Bar/Candyate (family Chocolate) and in Q1.

The detail of my calcul is :

Quaterly Initial Sales Target = Initial Country Sales Target * (PY Product Sales Revenue/PY Country and Product Family Sales)

For US /Nutzo Bar/Family Chocolate in Q1

Quaterly Initial Sales Target =635477* 589,66/290787 =  1289 and it is different with the value of 670,8 giving in the User story.

titiboys_3-1609697539588.png

 

 

So could you please give me hints and advice to have a the same data comparing with the US ?

Many thanks for your help.

Here is an export of my module TAR01 Detail Sales Target

 

Many thanks for your help.

Best Regards.

Thierry

 

Tagged:

Comments

  • Misbah
    Misbah

    @titiboys 

     

    PY Country and Product Family Sales Numbers are not looking right, although the formula looks ok to me which means your numbers in TAR01 Sales Target Module are incorrect. 

     

    Hint: Your Numbers should be 537,863 for US/Chocolate/Q1FY20 intersection in TAR01 module

     

    Hope that helps

    Misbah

  • titiboys
    titiboys

    Hi Academy,

     

    Sorry but my case is not resolved.

    It seems that my PY Revenu Country and Family is wrong.

    I checked my data Actual PY and the values are correct according your file A actual.

    All my formula are correct with LOOKUP  and SUM fonctions.

    So could you please give me hints to obtain the good figure  ?

    In my exemple : I would like to obtain 558647 for Nuzco / Chocolate/US Q1

    to calculate the Quatery Initial Target Sales

    Could you please confirm the PY Revenu Country and Family Product ?

    Perhaps the computed figure must have a ponderation coefficient.

     

    i.e : 589,7 * 635477/558647 = 670,8 FOR Nuzco Bar

    I really don't know how can I obtain 558647 ? I founded 290787 !

    So I don't understand why ?

    See my screenshot below :

     
     

    Thanks for your help and for reply.

    Best Regards.

    Thierry

     

     

     

     

  • titiboys
    titiboys

    Hi ,

     

    Thanks for your answer.

    But are you  using the same file  to import Baseline Financial forecast  !

    According to me the Base line for Chocolate/US/Q1 is 605 216 not 537,863 

     

     

    So what can I do to obtain the right figures !.

    Thanks for reply

    Thierry

     

    titiboys_0-1612611719378.png

     

     

     

     

  • JaredDolich
    JaredDolich

    @titiboys 

    @Misbah is correct. The issue in your formula is the PY Country and Product Family Sales which should = 537,863.

    Check these line items. If they are all the same, then reload your DAT02.

    The formula for that line item is: 

    'TAR10 Actuals Country and PF'.Sales Actual[LOOKUP: 'SYS03 Account>Product Details'.'P1 Product Family', LOOKUP: 'SYS03 Account>Product Details'.'G2 Country']

    So, most likely there's an issue with TAR10 Sales Actual line item

    'DAT02 PY Revenue to CY'.PY Sales[SUM: 'SYS03 Account>Product Details'.'P1 Product Family', SUM: 'SYS03 Account>Product Details'.'G2 Country']

    Which finally leads us to DAT02 PY Sales line item

    PY Sales Import[LOOKUP: 'SYS00 Time Settings'.PY Qtr]

    If all these formulas are the same, then you need to reload PY Sales Import.

  • titiboys
    titiboys

    Hi Jared,

     

    Thanks you very much for the tips.

     

    Unfortunately the figure (PY Country and PF)  is not correct  for me.

     

    • Step 1 : I imported Revenue for PY in Data02 with the formula :

    Revenu from previous Year[LOOKUP: 'SYS00 Time Settings'.PY Qtr]

    And according the story the values are correct.

    See the the screenshot

    titiboys_0-1612804537489.png

     

    • Step 2 : I calculated the PY with sum fonction in the module TAR 02 Sales Target with the formula : 

    'DAT02 PY Revenue to CY'.Actual revenu[SUM: 'SYS03 Account>Product Details'.'G2 Country', SUM: 'SYS03 Account>Product Details'.'P1 Product Family']

    See the screenshot below:

    titiboys_1-1612804874002.png

     

    • Step 3 : I calculated in the TAR 01 detailled sales target the PY Revenue by Country and PF with  lookup fonction

    'TAR01 Sales Target'.PY Revenue Good[LOOKUP: 'SYS03 Account>Product Details'.'P1 Product Family', LOOKUP: 'SYS03 Account>Product Details'.'G2 Country']

    See the screen below :

    titiboys_2-1612805304467.png

     

    So I don't understand why I can't get the right figures for PY Country and PF.

    Thank you very much for your help.

     

    You understand I spend a lot of time analyzing the problem.

     

    Thanks for reply.

    Best Regards.

    Thierry

     

     

  • JaredDolich
    JaredDolich

    @titiboys 

    I think you're very close but we need to go back to my original response. I'd recommend you reload PY Sales Import. Your numbers are slightly lower suggesting you missed some data. When you load there should be no errors. Here's the summary of PY Sales:

    DAT02.png

  • mickynike7
    mickynike7

    @JaredDolich - I am actually stuck in the same thing and need your help.

     

    1. In the Level 3 Sales Planning Model I have created a module and successfully imported the data from the data hub for Actual Sales Revenue. Its dimension include - A1 Accounts, P2 Products

    mickynike7_0-1618712406720.png

     

    2. I have another module in which I have dimension as G2 Country, P2 Product Family, A2 Account>Product#

    I am not able to do a lookup from the earlier module and using the SYS03 Account>Product Details. It says level mismatch on common dimension.

     

    I am just stuck here please can you help me how to move forward after importing the data from the data hub - I understand I will have to use LOOKUP and SUM and I have used those formulas earlier - In this particular scenario I am not able to understand how to use them.

     

    Thanks a lot!!!

  • ssicefox
    ssicefox

    Hi @titiboys 

     

    The percentage increase of US is 1.0%.

     

    ssicefox_1-1629720348995.png

     

     

    ssicefox_0-1629720328823.png

     

  • ssicefox
    ssicefox

    Hi @JaredDolich , 

     

    I got the result of TAR01 following: 

     

    ssicefox_0-1629720463987.png

     

    In this result, for US, Initial Account Sales Target / PY Account Revenue=101% that corresponds on Percentage Increase by Country (User Story 1.2).

     

    ssicefox_1-1629720463981.png

     

     

    ssicefox_2-1629720463985.png

     

     

     

     

     
     

    ssicefox_3-1629720463990.png

     

     

    In his result, for US, Initial Account Sales Target / PY Account Revenue=114% that doesn't correspond on Percentage Increase by Country (User Story 1.2).

     

    Could you please tell me if my result is right?

     

    Best regards.

     

    Wei

  • JaredDolich
    JaredDolich

    @ssicefox 

    You're logic looks good - I can't tell if the data is right unless I see the formulas. A couple of notes for you though.

    • TAR01 is not a system module. Ideally, Account, Product, Product Family, and Country should not be line items in this module. Try using your system modules for that: SYS03 Account>Product Details in particular.
    • Here's one way you can organize your grid. This one shows 5% for US, yours might be 1%. My recollection is that these percentages will change throughout the course so stay focused on the formulas and test your values using Excel if you must.
    • TAR01.png
  • titiboys
    titiboys

    I Jared

     

    Many thanks for thé reply.

    I will try next week after my vacations.

    Best Regards

    Thierry

     

  • ssicefox
    ssicefox

    @JaredDolich 

     

    Thank you for your reply and your hint.

     

    I will share my formula with you.

     

    Best regards. 

    Wei

  • ssicefox
    ssicefox

    Hi @JaredDolich 

     

    Thank you for your reply.

    Please find my formulas following:

    1. PY Country and Product Family Sales:

    Baseline Financial Forecast.Baseline Financial Forecast[LOOKUP: 'SYS03 Account>Product Details'.'G2 Country', LOOKUP: 'SYS03 Account>Product Details'.'P1 Product Family']

     

    I don't use the formula following for PY Country and Product Family Sales

    'DAT02 PY Revenue to CY'.Revenue[SUM: 'SYS03 Account>Product Details'.'P1 Product Family', SUM: 'SYS03 Account>Product Details'.'G2 Country', LOOKUP: Country, LOOKUP: Product Family]

     

    2. Initial Country Sales Target:

    'TAR01 Sales Targets'.Initial Country Sales Target[LOOKUP: 'SYS03 Account>Product Details'.'G2 Country', LOOKUP: 'SYS03 Account>Product Details'.'P1 Product Family']

     

    Here is my result.

     

     

     

     

     

    If the User Story 1.4 is the continuity of the User Story 1.2, the Initial Account Sales Target / PY Account Revenue should be 101% for US, or 105% for Italy.

    ssicefox_2-1629744892507.png

     

    I created the TAR01 Sales Target following that corresponding the example below.

     

     

     

     

     

    Please tell me if my result is right, specially PY Country and Product Family Sales and Initial Country Sales Target.

     

    I am looking forward to hearing from you soon.

     

    Best reagrds.

     

    Wei

     

  • ssicefox
    ssicefox

    HIi,@JaredDolich 

     

    I got the result of User Story 1.6 following:

     

     

     

    The data comes from 'TAR02 Account Review'.Initial Account Sales Targets using SUM for Country and Segment.

    The formula is following:

    'TAR02 Account Review'.Initial Account Sales Targets[SUM: 'SYS02 Account Details'.Country, SUM: 'SYS02 Account Details'.Segment]

     

    Could you tell me if my result is right? Please.

     

    Best regards.

     

    Wei

     

  • rob_marshall
    rob_marshall

    @ssicefox @JaredDolich 

     

    Folks,

     

    I understand you are either asking for help/clarification or replying, but please refrain from posting actual blueprints for Level 3.  Asking for help is one thing, getting on a Webex to help is good as well, but posting "solutions" is frowned upon.  If you have any questions, please let me know.

     

    Thanks you for your understanding,

     

    Rob

  • keitsramirez21
    keitsramirez21

    Hi Jared,

     

    I have been comparing my PY to CY sales revenue summary and found out that what is in the course is different from what you replied in this previous thread replies. Here is the one from the course which has a slight difference in the summary of All Accounts:

    keitsramirez21_0-1658774073766.png

    Thank you.

     

  • Olek P
    Olek P

    Hello folks, Was this ever resolved? I am having the same issue, Q1FY20 Chocolate US is 605,216. This is what the underlying data is showing me. However it should be 537,863?

  • Olek P
    Olek P
    edited August 2023

    I figured it out! I was having the same issue (605,216) as Q1 Chocolate , US. I'll give you a hint: check what the source data for this number is. You should be using PYASR, not the baseline forecast. (good luck staging it)

Categories