Posted on

Microsoft Excel Intermediate Training Course Case Study: Working out Commission Payable to Sales Reps

Learn how to use Intermediate Microsoft Excel Skills to produce reports with complex formulas. Online Training Courses

Breaking News & Updates

Xero’s financial report reveals BIG loss but share price pop. -

About 11 months ago from Steve Slisar's Twitter via Mailchimp

Sales is an interesting career path which includes everything from real estate agents, account managers to direct sales people who walk from door-to-door or make calls on the phone.

Being a Sales Representative is something that every bookkeeper does if they have their own bookkeeping business. You speak with prospects, find out what they want, see if you can do the work for them and then sign them up and start working. You’re helping to match a business with a customer.

I’m often fascinated at how many Bookkeepers HATE hearing the word “sales” associated with any part of their work and as a result they act like a ROBOT when they speak to potential clients, but I’m digressing and that’s a topic for another blog post!

The benefit of being in sales is that you can earn a commission which is often much larger than your base salary – but keeping track of commissions payable each month can be complicated and we teach you how to use Excel to make it EZY.

In our Microsoft Excel Intermediate Training Course (Module 306) you use a spreadsheet to calculate the commission payable to a team of sales people based on the turnover they generated last month.

It might seem like an easy task but can escalate in complexity very quickly based on changes in commission rate, tiered commissions and base salary payments. When you start adding extra sales people but still want the spreadsheet to be easy to understand you’ll learn how to reduce the number of columns and rows by using Intermediate level Excel formulas.

Sales Managers like to vary the incentives to increase the urgency that their sales people put onto prospects to BUY NOW and using formulas that span multiple worksheets sales managers put all the variables in one sheet and the answers in another.

That’s the real power of formulas in Excel and makes you look like a real Microsoft Excel Pro.

Use Microsoft Excel to Identify the Sales Leaders and Calculate Their Commission

In one of the Intermediate Excel case studies and sample exercise file you’ll learn how to create a tiered commission structure that automatically calculates the commission payable to sales reps as well as identify which level of achievement they were able to attain.

The visual part of this case study uses conditional formatting to show the highest awards in one colour and less results in different colours or gradients.

Conditional Formatting can also be used to identify who succeeded in reaching their budget and who didn’t quite make it and need to try harder.


Microsoft Excel Complete includes all 9 of our Microsoft Excel Short courses and comes at a discounted price. If you are a medium sized company or want to have several of your staff trained in using Excel you’ll find our course prices are even cheaper with a Microsoft Excel Corporate Training Licenses.

Corporate Training pricing is designed to eliminate the Needs Analysis that can take a long time and cost a lot of money and enable all of your staff to be at the same level – which you can choose.

-- Did you like what you read? Want to receive these posts via email when they are published? Subscribe below.


* indicates required