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