Need more help?

HTML Wrap

class	section group

HTML Wrap

class	col span_3_of_5

ANSWER ID:6326

If you have salespeople employed on a commission basis, you may need to calculate commission based on their sales for a particular period.

A template is provided at the bottom of this support note to allow for automatic Salesperson Commission calculations in conjunction with Microsoft Excel. This template, named ZSALSP.xlt, is an excel spreadsheet which links to the Analyse Sales (Salesperson) report in your MYOB softwareAccountRight.

Its use relies on the following:

You must be using the Salesperson field on Invoicesyour invoices.
The report that you send from your MYOB software to AccountRight to Excel must be filtered for a period CONSISTENT with the TARGET Lookup table contained on the template. For example, if your target lookup table has Sales targets for a MONTH, then you must filter the MYOB report for a month also.

Click the

Windows 8/7/Vista:

C:\Users\Public\Documents\MYOB\AccountRight\20XX.X\Reports\Spreadsheet

Windows XP:

C:\Documents and Settings\All Users\Shared Documents\MYOB\AccountRight\20XX.X\Reports\Spreadsheetpage and choose Save link as or Save Target as.

Save the file to the Spredsht folder in your AccountRight program folder. Here's the default location of the Spredsht folder:

UI Expand

title	1. Setup for commission

Download the report template

Right-click the file ZSALSP.xlt file at the bottom of this support note.
At the prompt, click Save and save the file in the Spredsht/Spreadsheet folder in your MYOB program folder. For example: In AccountRight Premier v19 this will be C:/Premier19/Spredsht In the new AccountRight the location depends on your AccountRight and Windows version:

AccountRight version

Spreadsheet folder location

(where 20XX.X refers to your AccountRight version, such as 2014.1, and <user.name> refers to the user currently logged into Windows)

2011 to 2013.5

Windows 8/7/Vista:

C:\Users\Public\Documents\MYOB\AccountRight\20XX.X\Reports\Spreadsheet

Windows XP:

C:\Documents and Settings\All Users\Shared Documents\MYOB\AccountRight\20XX.X\Reports\Spreadsheet

2014.1 and later (PC Edition)

Note: For computers with multiple users (each with their own Windows profile), any modified spreadsheet templates will need to be copied to each user's profile in the following location:

Windows 8 & 7:

C:\Users\<user.name>\Documents\MYOB\AccountRight\20XX.X\Reports\Spreadsheet

2014.1 and later (Server Edition)

If you're using	The Spredsht folder is here (v19.12 or earlier)	The Spredsht folder is here (v19.13 or later, Australia only)
AccountRight Standard	C:\Standard19\Spredsht	N/A
AccountRight Plus	C:\Plus19\Spredsht	C:\PlusClassic\Spredsht
AccountRight Premier	C:\Premier19\Spredsht	C:\PremierClassic\Spredsht
AccountRight Enterprise	C:\Enterprise19\Spredsht	C:\EnterpriseClassic\Spredsht

When asked if you want to overwrite the existing file, click Yes. This will replace the existing template.

Double-click the template to open it in Excel.

Select the cell range F16:G23. This range is NAMED "commtable", and is the range that the coding behind the buttons looks to when calculating the commission payable.

Change the Sales ranges in the commtable to suit your own needs however the format must stay the same, for example Sales and then Commission %.

Insert more cells from WITHIN the initial range should you need more rows for more ranges.

Select the range F31:G42. This range is NAMED "target" and represents each salesperson and the Sales target for the period.

Insert more cells from WITHIN the "target range" should you need more room, otherwise, edit the table to reflect your own employees and their respective targets.

Save the changes to the template and close Excel.

In the table below the Calculate Commission button, change the sales and commission % to suit your needs. The default table values mean a 5% commission will be calculated on sales up to $500, a 10% commission on sales up to $800, etc.

Image Added

In the table below the Target Analysis button, update the sales targets for your employees.

Image Added

Save your changes:

Go to the File menu and choose Save As.
Save the file in its original location (see the table above).
Ensure the File name is 'ZSALSP.xlt' and the Save as type is 'Excel 97-2003 template (*.xlt)'.
Image Added
If prompted to overwrite or replace the existing file, click Yes.

UI Expand

title	2. Using the commission template

In your MYOB software, go to the Reports menu and choose Index to Reports.
Click the Sales tab.
Under the Salesperson heading, click the Analyse Sales report.
Filter the report for the period you wish to calculate commission for.
Click Display.
Click Send To and choose Excel.Image Removed
When the information has filtered into the Excel worksheet, click the Calculate Commission button.
Image Added
Click the Target Analysis button.
Note: For the automation to work, you must click Calculate Commission before Target Analysis.