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. But right now, as we adjusted down the reseller and partner rates, we now have a shortfall in our budget.
0:10
So let's see if we model some different retail rates to bump up the total commission amounts. Let me walk through what we've done here.
0:30
We've been talking about pandas, but we still have all the Python functionality. So what we can do is create a list of potential rates,
0:39
iterated through each of those, update the rate for the retail transactions, re calculate our sales commission and then print out the value.
0:46
So what this tells us is, if we go with a retail rate, that is 0.17 we get to almost a million dollars and commissions.
0:55
So that seems like a smart move. And let's see what the distribution looks like for each of our sales people. Let's run our pivot table again,
1:06
and now we see our total here, which matches what we did our last calculation, and we can see how the commissions are spread out.
1:15
So we now have mawr commissions for Malcolm units. But we still have this distribution between Sutton and Tiner that we need to investigate further
1:24
So the one thing we haven't looked at when we have a commission's programme, we can vary it by the products they're selling.
1:32
But we can also vary the territories that someone has. So let's take a look and see what states people are covering.
1:41
So this gives us each of our sales reps states air covering and how much in commission they are earning. If we look at this data,
1:49
one of the things we might consider doing is Virginia and West Virginia have ah lot of commission, and Tiner has a lot of states that he's covering.
1:58
So maybe we should shift that to the Northeast. And then if we look at Nebraska, that's another one where there's, ah,
2:06
decent amount of sales. And geographically, maybe it makes sense. Move that to the West.
2:11
So let's update our alignments and now see how that impacts commissions. So let me walk through what we did.
2:28
We did a Boolean serious for a Virginia and West Virginia, and then we did another Boolean Siris for Nebraska and for all of those transactions that
2:37
were are in Virginia and West Virginia, we've moved them to the Northeast, who is covered by Shannon Muniz and Nebraska,
2:46
is moved to the West, who is covered by Malcolm Leonard. And let's rerun are Pivot Table now,
2:54
and we've made some improvements. So Tiner and Sudden Immune is are all a little bit closer. Now we're still a little bit under budget,
3:03
so this is starting to get a little bit better, and it looks like we are getting close to a good commission's recommendation.
3:11
So the final step is we need to summarize all this in an Excel file so that we can share it with our commercial leadership team.