Multi-Variable Formula for Calculating Attribution of a Sale

[Jack_Skinner]

Hello Airtable friends.

I have a somewhat simple formulaic excel sheet that becomes very complicated when breaking it up to be used in Airtable.

The goal:

Based on the type of sale, each of 6 static 'sale stages' should have defaul percentage (out of 100) assigned to them. Then for each of those 6 stage percentages, I need to allocate all of them across a variable amount of Sales Team members. Here's an example:

Sales Type (variable) = New Lead

Sales stages:

1 = 10%

2 = 30%

3 = 5%

4 = 25%

5 = 15%

6 = 15%

Now for this sale, Person1, Person2 and Person3 all contributed. So for each stage I need to distribute their percentage (this is manual input)

1 = 10% - Person1 = 3%, Person2 = 3%, Person3 =4%

And so on through all of the stages.

There's like 8 different sales types, of which the % distribution across 'sales stages' varies and the potential number of people involved in a sale can be up to 6. So to sum up variables:

• Sales Type 
• % Allocation per Stage (based off of Sales Type)
• # Of People Involved (Select from a List)
• The Specific People Involved (Select from a table)
• % Involvement of each person, across each stage (manual entry)

I feel like there's too much of a mix of variables, dependencies and manual entries to make this doable in Airtable BUT I'm also relatively new to Airtable and perhaps I'm missing something!

Thanks for the help!

[TheTimeSavingCo]

I think you'll need the following tables:
1. Sales
2. Sales stages - Linked to "Sales", each record represents a single stage of a single sale
3. People
4. Sales stages <> People - Linked to "Sales stages" and "People", each record represents a single person's contribution to a single sales stage

This way you can set the % amount for each "Sales stage", and the percentage amount each person contributed to a particular sale's stage

[Jack_Skinner]

Hi Adam,

Thanks for the response. I'm not sure if I completely follow and perhaps it's because my brain is still stuck in excel land.

I created a "sales type" sale, that has the percent of each stage that needs to be distributed based on the type, but the "sales stages" table where each record represnets a single staf of a single sale I'm not sure I see how that would work. Also, let me provide some screenshots of what the excel table looks like because again I think I'm just visualizing this format and it's making it hard for me to see how this works in airtable:

So basically, the "sales type" in the top right will shift the percentages that are allocated per stage (Basically it's a brand new lead, the people who worked on the lead deserve a bigger cut than if it was an extension of ongoing work 2 years later is sort of the logic behind all the shifting percentages)

So while "sales type" is a variable, as are the "people" involved in the "sale", those really won't shift much or at all. Sales type should never shift (though I suppose there's room for interpretation that could necessitate the ability to swap between different types), and the people involved will be known.

The main purpose of the table is to allow flexibility to the team to allocate sales % across stages - the process isn't an exact science and the ability to shift the percentages and get validation on whether theyve allocated everything (or too much) per stage. So in a way it's more of a working document that I'm trying to achieve here, but it will eventually be finalized and used to drive other processes. Hope this makes sense. Thanks again for the reply.

[TheTimeSavingCo]

Roger that, thanks for the details!

Check out this base I've put together for you

We have a table with all the sales:

A table with all the stages for every sale:

And finally a table that indicates how much of each sale's stage is allocated to each person:

And we could further customize it to set the sales stage percentages dynamically like you mentioned

[Jack_Skinner]

Thanks Adam!

I see what you mean. What I worry about this is that it's going to require enough setup steps that the team will not find it a greater timesaver than the Excel. Is there a way to automate all of the record creation to get to the point where you at least have each sale stage created for each deal?

[TheTimeSavingCo]

Oh yeah, definitely.  You could use an automation with a repeating group to create the required sales stages for each new deal you create automatically