Level 3 calculate the Quaterly Initial Sales Target

titiboys

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 ?

Many thanks for your help.

Here is an export of my module TAR01 Detail Sales Target

Many thanks for your help.

Best Regards.

Thierry

TAR01_Detailed_Sales_Targets_(4)_94398.xls

Comments

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

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

Anaplan_-_Level_3_Model_Building_TBE_94538.png

Anaplan_-_Level_3_Model_Building_TBE_Init_Sales_Target_94538.png

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

Baseline_Financial_Forecast_et_ANAPLAN_96987.pdf

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

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

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:

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 :

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

@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:

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

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

Hi @titiboys

The percentage increase of US is 1.0%.

ssicefox

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).

The result of @KonstantinGrishachev is following:

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

@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.

titiboys

I Jared

Many thanks for thé reply.

I will try next week after my vacations.

Best Regards

Thierry

ssicefox

@JaredDolich

Thank you for your reply and your hint.

I will share my formula with you.

Best regards.

Wei

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.

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

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

@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

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:

Thank you.

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

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)

Quick Links

Recent release news

August 2025 platform releases and what’s next
Check out the latest Anaplan feature updates and enhancements in our August 2025 official release notes. The information below offers supplemental information to that post. Planning experience Multi-browser tab syncing When changing context or data on a UX page, you can now view real-time data updates across up to five…
July 2025 platform releases and what’s next
Check out the latest Anaplan feature updates and enhancements in our July 2025 official release notes. The information below offers supplemental information to that post. Planning experience External URL navigation button Page builders can configure navigation action buttons to link to external URLs, including non-Anaplan…