Financial Model for Salesmen Bonus/Effects in Excel

The goal of this model is to allow owners/managers to develop a model that they want to use in order to incentivize their salesmen with a bonus based on hitting quarterly sales numbers. It allows for the user to put in all assumptions related to the sales/revenue generated by the salesmen, what sales hurdles they have to beat to get a certain bonus, and all the costs related to their activity/maintaining their accounts.




Pricing
The template will be sent to you after purchase via your PayPal/purchasing e-mail. This is included in the HR Tools in Excel template bundle.

So at the end of the day you get a nice monthly and quarterly roll-up that shows a context of how the salesmen has impacted the bottom line based on their gross revenue. There are two separate revenue items to play with as well if your company gets kickbacks/rebates from doing a various sales amount.

What is Included:
  • Assumption based inputs that flow through the financial model and relate to bonus structure, base salary, payroll tax/401k/etc..
  • 12-month summary of activity.
  • 1-year quarterly roll-up
  • Visual chart showing the effects of total sales, gross profit, and bonus given.
  • Summary of how to build the sheet/comments in cells where applicable.
  • Allows for support/costs to be entered i.e. customer service/account maintenance
  • See all the way down to gross revenue.
This is not just something that says enter a given sales performance and see show bonus. It goes further and drills all the way down to the gross revenue the company makes from all activity (income/expenses) by the employee. You can see how much you are paying and what the company is making off of the entire module.

The idea of this is to use as a stand-alone template to plan how sales goals for given salesmen and to actually calculate what their quarterly bonus is. Of course you will have to determine what bonus incentives make sense for your business i.e. how much they have to make in sales in order to achieve a given bonus level for the quarter.

Right now it's setup just to handle a single employee, but this can be modified to handle as many salesmen as you want to track. To add this functionality tailored specifically to your situation, I would charge my regular hourly rate to get it working in a way that fits your needs on top of the up-front cost of the model.

Further automation is possible if you have a raw data set of sales by salesmen and time of sale. This would require more work by me to get it going, but then you could just do a data dump and the financial model populates in the 1-year cycle.

After using this, you will have a much better idea of what kind of bonuses you can afford to give to your salesmen employees (how much they need to earn to get a certain bonus level) after factoring in all the costs related to the sales they bring in.

No comments:

Post a Comment