Welcome to the CBA Blog!

Not sure how many W2's you need to order this year?  Below are the steps to get a count of the total employees paid in 2015 so you will know how many W2’s to order:

Need to order your 2015 tax forms that are guaranteed Sage 50 compatible?  Call 1.800.339.8224 or email us at Info@ComputerizedBusiness.com for a quote that is guaranteed to be lower than what you would pay Sage for the same tax forms!

One of the wish-list features that our Sage 50 clients want is the ability to calculate commission. Generally, sales reps earn a percentage income for any sales they produce in a given period and not many small business accounting software packages have the ability to calculate commission.

Fortunately for Sage 50 customers, Sage Intelligence Reporting offers you the Sales report which you can fine-tune and tailor according to your commission needs. You can add custom formulas, charts, graphs, and PivotTables to easily calculate sales rep commission, track sales rep performance and trends. The Sales report is saved for future use, so you don’t have recreate the report every time you need to calculate commissions.

Using the Sales Analysis report in Sage 50 US Intelligence Reporting follow these steps to create a sales commission calculator: 

1. Run the report

a) From the Report Manager, run the Sales report.

b) Choose a date range which suits you. For this exxample we will only be editing the template.

2. Choose your preferences

a) Once the report has loaded in Excel, go ahead and open up the Sales Analysis

b) To access the field list, click anywhere on the Pivot Table, then under the Analyze tab on the Ribbon, click Field List. Depending on the data you want to see, you can customize your report by checking/unchecking the boxes in the field list to add and remove columns.

Note: Ensure you have a column displaying the total sales amounts. We’ll use this to calculate the sales commission.

3. Set the filters

a) In the filter options of the PivotTable, ensure that you have Sales Rep and Transaction type. This will help you generate commission payouts for individual sales reps by clicking on their names.

b) Once you have created your filters set the transaction type to Invoice; this ensures that the commission calculation is based on sales only (not receipts, sales orders or credit memos etc.).

Note that if you can’t find the sales rep or transaction type fields, you may need to add the expressions to the container in the Connector and/or bring them through in your report in the Report Manager.

4. Enter formulas and Cell References

a) Choose any set of cells (outside the PivotTable) and enter the following “commission labels”: Sales Rep, Total Sales, Commission Percentage and Commission Payout.

Note: It is recommended to place the commission labels in the same column. To the right of these we will enter formulas for the corresponding data, i.e. the calculated fields.

b) To have a sales rep automatically show up in the Sales Rep field, select the cell and type “=” (equals) and simply click in the sales rep filter field in the Pivot Table. The Sales Rep field will now mirror whichever sales rep is selected in the Pivot Table filter.

c) To get the total sales amount from the Pivot Table, click in the Total Sales field and enter “=” (equals), then scroll down and select the Total Sales Amount Grand Total in the Pivot Table and press enter. The formula used is a GETPIVOTABLTEDATA formula which means it will always adjust to provide the right amount depending on how the Pivot Table changes.

d) Now, enter the desired commission percentage in its respective field, say 10%, but make sure you enter the value as a fraction (0.1) and then set the cell format to percent.

e) Lastly, we calculate the commission payout by entering a formula in the Commission Payout field. My Total Sales amount field is in cell F6 and the Commission Percent is in cell F7. So in this case, the formula to be entered is “=F7*F6” (or Total Sales multiplied by the Commission Percentage).

The PivotTable will adjust the figures any time a specific sales rep is selected and the amounts will be automatically calculated.

Some additional functionality you can add to enhance your report includes:

• Add Timelines to the Pivot Table to view commissions over a time range.
• Add Slicers to select more than one sales rep, getting a combined amount
• Add a graph to visually represent the commissions versus total sales for each sales rep

Note: You must have Microsoft Excel 2003 or later to use Sage 50 Intelligence Reporting.

The following standard report templates are available:

Income Statement - Actual vs Budget: This is an income statement that compares actual, budget, and prior year amounts broken out by period and year-to-date.

Income Statement - Actual vs Prior: This is an income statement that compares actual and prior year amounts broken out by period and year-to-date.

Income Statement - Current Period vs YTD: This is an income statement that compares the current period actual amounts with the year-to-date actual amounts and the prior year-to-date amounts.

Financial Reports (all): This is a way to access more than one financial report in the same workbook. When you select this report, a workbook will open in Excel with a menu page shown. This menu page has access points to several financial reports that you can view and edit. There is an Instructions tab in the workbook that can help you understand and use this workbook to get the financial information you need. 

This group of reports includes the Balance Sheet - Actual vs Prior, Income Statement-Actual vs Budget, Income Statement - Actual vs Prior, and Income Statement - Selected Period vs YTD. These are the same reports that are listed separately above with the exception of the last Income Statement. The Income Statement - Current Period vs YTD is not included in the Financial Statements (all) workbook. Instead, there is an Income Statement - Selected Period vs YTD. The selected period for this report is the last period of the year. You can change this period after you run the report in Excel (see the Instructions tab of the workbook for assistance).

Financial Reports Designer: This is an Intelligence Reporting report designer component that allows you to design your own financial statements. (Requires Microsoft Excel 2007 or higher)

PURCHASES

Purchase Analysis: This report gives you detailed information from all of your Accounts Payable transactions ( Purchase Orders, Purchases, Payments, and Vendor Credit Memos). You can filter the report different ways including by Transaction Type, Vendor, and Item. You can get valuable analysis information about your purchases including the quantity purchased and the amount you spent. You can also add fields to this report if there is other information you would like to see.

SALES

Sales Analysis: This report gives you detailed information from all of your Accounts Receivable transactions ( Quotes, Sales Orders, Proposals, Sales Invoices, Receipts, and Credit Memos). You can filter the report different ways including by Transaction Type, Customer, and Item. You can get valuable analysis information about your sales including cost, gross profit, and gross margin. You can also add fields to this report if there is other information you would like to see.

Note: The Intelligence Reporting reports shown in Sage 50 are really templates that are used to create the report in Excel. You can edit the generated report in Excel to create new reports with the generated data. You can use the Intelligence Reporting Report Manager to create and edit templates that can be used to generate reports.

•  Cross-year difference and changes
•  Gross profit per item
•  Location settings.
  
  

Sage 50 Business Intelligence Reporting sets itself apart as a step forward for users who may only be using a spreadsheet program for their current reporting needs. Sage Intelligence Reporting increases your productivity by eliminating manual report preparation, repetitive data extracts and complex back-end data joins to provide you with up-to-date, accurate and presentation-quality reports for informed decision-making.