Level 3 calculate the Quaterly Initial Sales Target

titiboys
New Contributor

Level 3 calculate the Quaterly Initial Sales Target

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

 

 

 

 

 

 

 

 

 

 

 

16 REPLIES 16
ssicefox
Frequent Contributor

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
Moderator

@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

Jared Dolich
titiboys
New Contributor

I Jared

 

Many thanks for thé reply.

I will try next week after my vacations.

Best Regards

Thierry

 

ssicefox
Frequent Contributor

@JaredDolich 

 

Thank you for your reply and your hint.

 

I will share my formula with you.

 

Best regards. 

Wei

ssicefox
Frequent Contributor

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
Frequent Contributor

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
Moderator

@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