[HELP] Level 3 - Sales Planning Sprint 1 - TAR01 Detailed Sales Target Module | 1.4 & 1.5

davidlloyddeguzman

Hi Everyone,

 

I've been stuck the last few days working on User Story 1.4  & 1.5 | 5.2 Model Building Specifications.

 

There isn't enough information for me to comprehend what is needed to be done and thus all my numbers aren't correct.

I'm positive my other user stories are working as intended based on the examples they showed to compare.

However the stories below I'm really struggling with:  ▼▼▼

 

User Story 1.4

As a Regional Sales Executive, I need to determine the ratio of each account’s previous year actual product sales revenue to the country’s previous years actual sales revenue by product family.

This calculation will be used to establish the quarterly Initial Account Sales Targets while maintaining the product seasonality and sales patterns for each account.

I will know this is complete when I can see the percentage of the country’s actual sales revenue by product family that each account’s actual product sales contributed to per quarter. 

 

User Story 1.5

As a Regional Sales Executive, I need to calculate the Initial Account Sales Targets for each quarter of the current year. 

The quarterly Initial Account Sales Targets are determined by distributing the Initial Country Sales Target proportionally based on the ratio of each account’s previous year actual sales revenue by product to the country’s previous year actual sales revenue by product family.

My Result:

 

 

The intended result:

 

My Ratio Formula:

:

 

My Formulas:

 

The answers I'm trying to achieve:

 

My results:

 

 

My Questions:

-  Are my formulas correct or am I missing something?:

• PY Account Revenue
  • I don't think there is enough information as to what this is supposed to be. Is it asking for the revenue of the account itself? Or is it the individual revenues that make up the total account. Currently have it as each individual revenues that make up the total account revenue. Either way I've tried both.
• PY Account / PY Country Product Family
  • I'm assuming this is just division and using this as my testing. However as I've been working on this for a long time, I'm starting to lose trust.
    
    
• Initial Account Sales Target
  • The user story specification instructs that: Distributing the Initial Country Sales Target proportionally based on the ratio of each account’s previous year actual sales revenue by product to the country’s previous year actual sales revenue by product family.
  • I interpret the above as dividing Initial Country Sales Target by the PY Account Country Product Family Line Item
    
• PY Country and Product Family Sales AND Initial Country Sales Target
  • I'm positive that these are working as intended and I checked that the numbers match based on the country and product family numbers they are. But at this point I'm losing hope.

 

If someone is able to help me get past this I would appreciate it it a lot.

 

Thank you

Meghna

Hi,

 

The PY information is at a Product / Account level. The working needed for TAR01 is the Country level too. However, the Location / Country information is missing in the SYS02 & SYS03 in the Sales Planning module and also in SYS11 in the Datahub. Any clue where I can get this information from?

 

Thanks,

Meghna