New Contributor

## Level 3 calculate the Quaterly Initial Sales Target

Hi community,

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

According the user story ,

I checked all my data and

according your screenshot for Nutzo bar I must find 670.8

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.

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

Here is an export of my module TAR01 Detail Sales Target

Best Regards.

Thierry

16 REPLIES 16
Frequent Contributor

Hi @JaredDolich ,

I got the result of TAR01 following:

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

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

Moderator

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.

Jared Dolich
New Contributor

I Jared

I will try next week after my vacations.

Best Regards

Thierry

Frequent Contributor

I will share my formula with you.

Best regards.

Wei

Frequent Contributor

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.

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

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

Moderator

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.