Multi Level Formula

Occasional Visitor

Hi
I am trying to create a distribution channel model, and I am not sure how to get this done.
The assumptions are

 

 Total Customers fromTotal Customers from
Tier 1110
Tier 21120
Tier 32150
Tier 451+ 


1. I want to have 5,000 Customers by the end of 24 months
2. Each sales person will bring 8 new customers per month
3. Each new customer (sales) will be accumulated as a portfolio, to categorize the sales person into tiers based on the above table

 

I want to create a Table with the above assumptions, to categorize each sales person into different tiers based on the number of total customers they will have at the end of each month


How can I model this in the below format,

To get the number of sales persons at the end of each month at the different Tiers, based on the total customer portfolio, until I reach a total of 5000 customers

 Month 1Month 2Month 3Month 4Month 5Month 6
Tier 1      
Tier 2      
Tier 3      
Tier 4 
1 Reply
So many people say that when someone starts talking math, physics, .... their eyes gloss over and brain shuts down. Well that is me with sales and accounting. lol. That said, I don't quite understand exactly what you are doing/need but maybe can give you a start and then maybe you can explain some more.
Usually it is best to create a data entry sheet with all the relevant info and then you can have report sheets that analyze or summarize that data. So for example maybe your data entry is:
Date, Salesperson ID, Customer Name, ...
Then another sheet can either use PivotTable, Power Query, or just cell functions like UNIQUE, FILTER, etc... to SUM total number of customers/sales for each sales person each month and also assign the lookup value for what tier that is.
Then another table can create that tier vs month table you want.
I would do more but don't understand if the tier is based on # customers or # of sales and if those tiers are based on single month values or cumulative and probably a few other questions I missed when my eyes glossed over and my brain shut down