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

[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

[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

[ssicefox]

Hi @titiboys 

The percentage increase of US is 1.0%.

[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!!!

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