It’s that time of the month again, end of the month, time to close the books and time to calculate commissions. Have you been exporting reports and calculating commissions manually in Excel? Are sales reps always waiting on you (and probably bugging you) to get those numbers? What if Intacct could automatically create those reports for you? With one click of a button you’d have all your commissions calculated in separate reports ready to be sent out to all your Sales Reps (okay maybe a few clicks – still way easier!).
Recently I created a very specific (but cool!) P&L report for exactly this reason. There were a few things the client was looking to get out of the report – two of them were:
- Calculate commission per employee (where % commission varies based on the employee).
- Separate reports based on employee.
We also wanted to be able to run a report based on a date located in the project dimension. (That was a tough one to figure out! Since Intacct only allows you to run reports based on dates of the transactions – so for example November 1 – November 30 would include all transactions for a specific project BUT what we were looking to find was all transactions for a project that started on a certain date whether the transactions occurred in November, December, January, etc. We wanted this to include all projects with a start date in a certain month. A little mind bending – I’ll let you think about that one for a bit. If you’d like help on setting that up – I can definitely give some direction!).
Back to the requirements! Separating the reports based on employee was easy. I used Intacct Dimension Groups (check out how to set those up here!). So I set up the 6 employees based on the primary contact field in the project dimension and the 6 P&L reports (easy enough). (I needed to set up 6 reports because of the time dimension requirement).
Now, back to number 1 (and where I’m getting at with this post)…. We wanted to calculate the commission on each of these reports and have the percentage vary based on the employee. That’s where Intacct Account Groups came to the rescue! Keep reading if you’d like to learn how to set up an Account Group!
What are Account Groups?
So you already have some Account Groups set up in Intacct (like Revenue). The ones you already have set up include a range of GL accounts.
Now, I bet you didn’t know that you can set up Account Groups as calculations! That’s what we’ll be using to calculate their commission so let me take you through the step by step!
General Ledger > Account Groups > +
(Note: If you’re already in the financial report writer you can add an account group by going to Rows > Select Account Groups > *select the drop down menu > Add)
Step 1: Names
Name your Account Group whatever you like (this is not the field that shows up on a report it’s just used when you search account groups). Not Editable after saving.
Display Name is what will show up on your report. Editable after saving.
Balance is either Debit or Credit.
Step 2: Structure Type
We’ll be using the Computation structure.
Step 3: The Formula
You’ll notice 3 drop-down fields. The middle field is the type of mathematical function we want Intacct to complete: multiply, divide, add, subtract (only simple operations here).
The other two fields allow you to choose whether you would like to operate on 2 account groups, 2 accounts, account group and a constant (bingo! that’s us!) or any other combo.
For our example we are multiplying an account group by a constant. I created an account group for each sales rep that calculates their commissions based on their commission rate. Let me explain that I had to make an assumption that each sales rep’s commission rate stays constant and is completely independent of any other factors (doesn’t depend on sales, no accelerators of any kind which I’m sure we could still figure out but it would be more complicated).
Step 4: The Final Step!
Now you can create your report using the Financial Report Wizard and include your account group!