Move from Excel to Python with Pandas Transcripts
Chapter: Case study walk through: Sales commissions
Lecture: Commission state adjustments
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.