These two worksheets provide a commission compensation plan you can use for each of your salespeople, in addition to an appointment calculator to determine how many leads you must generate to allow your salespeople to meet their sales goals. They include:
Salesperson Purpose: For each salesperson, determines monthly commission and bonuses, chargeback allowance amount, estimated average monthly compensation and a ratio of compensation to sales dollars.
How it works: For a given salesperson for each month, you can input actual sales dollars, historical sales (if needed for calculations), commission percent, target sales dollars, bonus percents, chargeback allowance rate, base weekly salary, au to and phone allowance, and other expenses. The spreadsheet calculates commission amount, bonus amounts, chargeback dollars, average monthly compensation and a ratio of total compensation to sales dollars.
Appointment Calculator Purpose: Lets you know how many appointments you must provide to a salesperson to make sure he or she achieves his sales goal for a given month.
How it works: Lets you input a salesperson's monthly sales goal, average close ratio, average dollar sale, number of days to be worked in a month and your appointment to lead ratio. The spreadsheet calculates the number of orders the sales person needs to write to attain that sales goal, along with the number of appointments he/she must be provided, the number of appointments per day he/she must make and the number of leads you must generate to achieve that number of appointments.
Sales Planning Tools
These four worksheets can help you with overall sales and marketing planning. They include:
Sales Strategy Planning Purpose: Gives you better visibility about gross profit, marketing cost per lead and profitability after advertising costs.
How it works: Lets you input your current annual sales dollars, gross profit margin, annual advertising budget, total annual leads, average sale and appointment ratio. For current sales, worksheet calculates gross profit dollars, marketing cost per lead, total orders, close ratio, total appointments and profit after advertising. You can input the same parameters listed above for four different scenarios and see how the calculations change.
Monthly Sales Goals Purpose: Lets you establish a plan for monthly sales in a coming year, then gives you visibility as to how your actual sales compare to plan.
How it works: Lets you input last years actual sales for each month and your planned sales for each month for the coming year. As actual sales come in during the year, you can type in those numbers. The worksheet calculates a comparison of this year's sales to last year's sales and variance of actual sales to planned sales.
12 Month Rolling Trend Purpose: Provides a graphic representation of your 12-month rolling trend in planned and actual sales dollars.
How it works: The numbers used to construct the graph on this spreadsheet are based on spreadsheet number 2 above. Numbers for spreadsheet 3 are automatically updated when you type new numbers into spreadsheet 2. No new input is required for this spreadsheet, but all entries for spreadsheet 2 must be complete for you to see a full graph in this spreadsheet.
Key Sales Indicator Planning
Purpose: Lets you see how many total appointments you need to book and the number or sales you need to make each month to achieve the planned sales you'd like to achieve.
How it works: Planned sales from spreadsheet 2 are automatically shown in this spreadsheet. You input your marketing cost per month, average sale amount, appointment ratio and close ratio from spreadsheet 1 (or a different close ratio if you'd like). The spreadsheet calculates the total leads you need to generate to achieve planned sales dollars, the marketing cost per lead, the total number of appointments you need to book and the total number of sales you need to make.
