Move from Excel to Python with Pandas Transcripts
Chapter: Case study walk through: Sales commissions
Lecture: Commission state adjustments
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
we have a million dollar commission budget.
0:02
But right now, as we adjusted down the reseller and partner rates,
0:06
we now have a shortfall in our budget.
0:09
So let's see if we model some different retail rates to bump up the total commission
0:14
amounts. Let me walk through what we've done here.
0:29
We've been talking about pandas, but we still have all the python functionality.
0:34
So what we can do is create a list of potential rates,
0:38
iterated through each of those, update the rate for the retail transactions,
0:42
re calculate our sales commission and then print out the value.
0:45
So what this tells us is,
0:47
if we go with a retail rate,
0:49
that is 0.17 we get to almost a million dollars and commissions.
0:54
So that seems like a smart move.
0:57
And let's see what the distribution looks like for each of our sales people.
1:02
Let's run our pivot table again,
1:05
and now we see our total here,
1:07
which matches what we did our last calculation,
1:10
and we can see how the commissions are spread out.
1:14
So we now have mawr commissions for Malcolm units.
1:17
But we still have this distribution between Sutton and Tiner that we need to investigate further
1:23
So the one thing we haven't looked at when we have a commission's programme,
1:27
we can vary it by the products they're selling.
1:31
But we can also vary the territories that someone has.
1:34
So let's take a look and see what states people are covering.
1:40
So this gives us each of our sales reps states air covering and how much in
1:44
commission they are earning. If we look at this data,
1:48
one of the things we might consider doing is Virginia and West Virginia have ah lot
1:54
of commission, and Tiner has a lot of states that he's covering.
1:57
So maybe we should shift that to the Northeast.
2:01
And then if we look at Nebraska,
2:03
that's another one where there's, ah,
2:05
decent amount of sales. And geographically,
2:08
maybe it makes sense. Move that to the West.
2:10
So let's update our alignments and now see how that impacts commissions.
2:25
So let me walk through what we did.
2:27
We did a Boolean serious for a Virginia and West Virginia,
2:30
and then we did another Boolean Siris for Nebraska and for all of those transactions that
2:36
were are in Virginia and West Virginia,
2:39
we've moved them to the Northeast,
2:41
who is covered by Shannon Muniz and Nebraska,
2:45
is moved to the West, who is covered by Malcolm Leonard.
2:48
And let's rerun are Pivot Table now,
2:53
and we've made some improvements. So Tiner and Sudden Immune is are all a little
2:58
bit closer. Now we're still a little bit under budget,
3:02
so this is starting to get a little bit better,
3:05
and it looks like we are getting close to a good commission's recommendation.
3:10
So the final step is we need to summarize all this in an Excel file so
3:15
that we can share it with our commercial leadership team.