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.

Talk Python's Mastodon Michael Kennedy's Mastodon