If you applied a credit to an invoice and then realized that was the incorrect application, this will tell you how to reverse that.
Customer:Job History
In order to do this, you will first need to navigate to the customer history for that customer. You can do this by finding them in the Customer:Job list located at ‘Customers’ > ‘Customer:Job List’. Once you are here, you can right-click on the customer and click ‘History’.
Once you are in the history for that customer you should be able to go to the sales tab to see all of the sales, credits, or invoices from the current customer. You will want to right-click on the credit that has been applied incorrectly and click adjust.
How to adjust the credit
Once you click adjust you will see all of the applications of this credit. You can find the incorrect application and select it to be deleted.
Once you delete this application you will see that the balance has returned to the credit to be used.
Conclusion
You will want to use this adjust screen anytime a payment has been applied incorrectly. By using this screen you will unlink the credit to the invoice and should be able to apply the entire amount to another invoice of your choosing.
Job Estimates vs. Actuals Summary and Detailed Report Explanation
Job Estimates vs. Actuals Summary Report
This report shows how accurate your company is on estimating. This report summarizes estimated versus actual costs and compares estimated revenue to actual revenue for all customers that have an estimate.
Job Estimates vs. Actuals Detail Report
This report offers detailed costs and revenues for the customer or job you have selected. This report includes each item that you have on the estimate. It compares estimated and actual costs and estimated to actual revenue for each job that you have invoiced. This means you can see which parts of the job you estimated accurately and which parts you did not.
Note: This report does not look at the Expense tab on a check, credit card, or bill. It only looks at items.
Help Topics Related to Job Estimates versus Actuals Reports
The Technician Scoreboard is all about measuring a Field Service or Installation Technician’s (or any billable employee) performance. We have included every major key performance indicator and ratio that is taught by the EGIA Contractor University, Gary Elekes, Drew Cameron, and James R. Leichter.
The Scoreboard takes advantage of the enterprise level accounting and bookkeeping system built into Total Office Manager. Because of our true “all-in-one” software advantage, it contains more specific and accurate information about technicians than anything else we have ever seen. You will need to be using the software to its near fullest extent to get the most out of Contractor Compass. There are links to other related topics at the bottom on this page.
You Must Setup and Use Total Office Manager Properly
We have all heard the old saying “garbage in, garbage out”. You must be using Total Office Manager to its fullest potential. This includes using work orders, timesheets, invoices, estimates, sales opportunities, marketing sources, departments, and much more. You must also assign work orders to invoices and estimates.
There are many important fields on forms that are essential to Contractor Compass and the Scoreboard. Many forms in Total Office Manager include a selection box called “What Best Describes This”. The value of this field is very important to this workbook (Scoreboard). Our help topics include information on where to find most or all of these. See Related Help Topics at the bottom of this page.
Preparing to use the Technician Scoreboard
Please be sure to read and follow the help topic on preparing for Contractor Compass®. The same conditions and requirements apply. A link to that topic is at the bottom of this help topic.
Minimum Microsoft Excel Versions Required
You must be using Excel 365 or Excel 2021 (32-bit or 64-bit). We use the FILTER function in many of our workbooks. The FILTER function is only available in Excel for Microsoft 365 and Excel 2021. In Excel 2019, Excel 2016 and earlier versions, it is not supported.
Using the Technician Scoreboard
Using this technician scoreboard is super easy.
Open the Contractor Compass application in Windows.
You may need to select a Company File and enter some connection related information. This is typically only required when using the app for the first time. You can switch between multiple companies.
Select a username and password and click the Login button. This is the same username and password you use to log into Aptora’s Total Office Manager.
Click View Dashboards. Note: This can be single or double clicked. It doesn’t matter which.
Under the Company Dashboards windows, double click Technician Scoreboard.
Wait for the Excel file to open. This process can take thirty seconds to several minutes, if you have a lot of data to process.
You may filter the results using the Department filter or Date Range filter. Technicians are ranked by Gross Profit per Person Day (the column header is GP$ per Person Day). You may select a different way of ranking technicians using the Rank Technicians On selection.
Click the Refresh button (top left) to reload data from your Company File (if needed or desired).
Reload and Refresh Dashboard Data
There is a Refresh button to the left of the “Last Updated” label (top left). That will refresh the information without opening and closing the workbook from Contractor Compass.
Automatic Reload and Refresh
The Technician Scoreboard automatically reloads from Total Office Manager and refreshes every thirty minutes. This makes it easier to display the Technician Scoreboard on a TV or large monitor. The results will auto update without user intervention. The “Last Updated” label will let viewers know how fresh the results are.
Presentation Mode
When the Excel file opens from Contractor Compass, it will be in Presentation Mode. This mode hides things that the user would not normally wish to see or have access to. At the top of the Scoreboard worksheet, there is a button to place the Excel file into what we call Normal Mode. Doing so unhides the things that the application was hiding. You may click the button to go back to Presentation Mode.
Using Filters
When filters are used or the worksheet is otherwise changed, it automatically recalculates the numbers on the Scoreboard. When the worksheet recalculates, it automatically goes back to Presentation Mode.
Always Open from Contractor Compass®
The Excel file must be launched from the Contractor Compass app to function properly. This is the only way the file can receive information from Total Office Manager. If you open the file directly, it is not possible to connect to your Total Office Manager database.
Understanding the Scoreboard Information
Popup Notes on Columns
When you see a red triangle at the top right of a cell, that cell contains a Note. Hover over that cell and the note should display. It will help explain what the field means and where the information comes from.
Frequently Asked Question about the Scoreboard
Q: Why do some of the Scoreboard columns have a light red boarder around them?
The red boarders indicate that the number is a key number. In addition to the Technician’s rank, there are four other numbers that we consider to be the most important: GP$ per Person Day, Labor to Sales Ratio, SAs Sold, and Sales Leads Created.
Q: Why do I see a technician called “UnAssigned” on the Scoreboard?
A: That indicates that one or more records in Total Office Manager did not have a technician assigned to them. Examples could include invoices, sales opportunities, and many others. To fix this, please read the help topics related to preparing for Contractor Compass.
Q: If two or more technicians are on an invoice, how is the invoice divided out among those technicians?
A: It is proportionate to each technician’s timesheet hours. If there was a total of three hours paid for that invoice and one technician had one hour, they will get credit for 33.33% of the invoice. We use the Assign Work Orders feature found on the Invoice extensively.
Q: Why do some numbers use a decimal point when that number always has zeros at the end? Example: Number of Callbacks = 4.00.
A: That is done when the number has the potential of being less that one. Callbacks could be 4.50 or 4.75. There are times when a value can be shared amount two or more employees. Consider the NFL when a player can be credited with 2.5 QB sacks.
Q: We have certain information that seems to be missing or is correct. How do we resolve this?
A: This is usually the result of setup and or usage of Total Office Manager®. Be sure to read the Preparing Total Office Manager for Contractor Compass help topic. You must be using Total Office Manager to its fullest potential. That means using almost all the features.
Q: When I click on a cell in the Scoreboard sheet, I do not see a formula. Why?
A: Please see “How Formulas are Used” below for complete information.
Q: How is Gross Profit Per Person Day Calculated?
A: Gross profit is annualized and we consider how many Person Days have been used during the fiscal year. In version 2.20, we updated the formula (in column D) to report GP/PersonDays/YearFrac(StartDate-1,EndDate). We are now using the YearFrac function. It will accommodate any date span. If you show two years on the dashboard, the YearFrac will cause the final value to be multiplied by 2 (which is what is needed, because the Gross Profit will be for 2 years). Similarly, if the data runs from 1/1 to 2/13, then it will divide by (approx.) 0.12 . . . or essentially multiplying the gross profit by 8. This improves the accuracy of the data and makes the calculation far more reliable. Person Days are entered in each employee’s Payroll Setup.
Tips
If a number is #.#, then there is a possibility of a fraction. If a number is whole, then there is no possibility of a fraction. Percentages are all formatted as #.##% for consistency. Cents have been omitted from all dollar amounts.
The Date Range selectors include the oldest and newest dates from the data available. If the date you want is not available, that date does not exists in the Dept-Info worksheet.
When the workbook refreshes, it will always go back to Presentation View.
The scoreboard reloads and refreshes automatically every thirty minutes or when you click the manual refresh icon.
200 Employee Limitation: The Scoreboard worksheet display can handle up to 200 employees. If you have more than 200, please let us know.
You must assign work order to Invoices and Estimates. That means you will either create invoices and estimates from work orders or use the Assign Work Orders feature that is found under the Invoice or Estimate Menu button.
You must use the Timesheets feature for the technicians on the work orders.
Invoices have a field called Labor Minutes. When checking the accuracy of the dashboard, be sure to pay attention to this number. Remember that Group item types allow the Labor Minutes to be overridden.
The workbook will only populate when it is opened from Contractor Compass. When you open the workbooks directly from Excel, you will see information that was added the last time it was opened from Contractor Compass.
There is a Refresh button located near the logo (top left). This will refresh the dashboard data without the need to close and reopen it.
Known Issues
We were forced to use Comments instead of Notes due to formatting problems the Notes feature was causing. We believe that Notes look better, so we will continue to work on this issue.
The dropdown menus in Excel typically do not work with a mouse scroll wheel. This appears to be an issue with Excel that we cannot control.
Editing the Excel Workbook
Please be careful editing the worksheet (Excel file), work on copies, and always make backups.
The Excel file is 100% editable and customizable. That allows users to make both major and minor changes. Please do not edit any of the worksheets unless you are competent and comfortable doing that type of work. If you wish to modify the workbook, always work from a copy. Please see Related Help Topics below, if you would like to know more about how to edit a workbook.
You will need a password to edit any of the worksheets. That password is included in a worksheet called Help. If you change the password, the workbook may no longer function. Aptora will not be able to tell you what your new password is and we can’t reset it for you.
No Support for Excel or VBA
Aptora provides no support or advice about working in Microsoft Excel® or modifying the workbook.
How the Scoreboard is Populated
Contractor Compass® loads the Dept-Info tab when it launches Excel and opens the workbook file. The Scoreboard gets all of its information from the Dept-Info worksheet.
The Dept-Info worksheet will only populate when it is opened from Contractor Compass®. When you open the workbooks directly from Excel, you will see information that was added the last time it was opened from Contractor Compass. There is a Refresh button that will refresh the information without opening and closing the workbook from Contractor Compass.
This article includes additional advanced information on this topic and more, further down this page.
Related Help Topics
Preparing Total Office Manager for Contractor Compass™
Scoreboard Workbook Structure and Operation (advanced info)
Intended Audience
The following information is for those users that wish to know more about how this Excel was built and how it operates. This information will be helpful only to those wishing to make changes or modifications to the workbook. Most users will never need to know any of this.
How it Works
The Contractor Compass Technician Scoreboard is a Microsoft Excel® Workbook. Excel refers to each tab as a Worksheet. The entire Excel file is called a Workbook. When this workbook is opened from the Contractor Compass® app, a connection is made to your Total Office Manager® database. Once connected, code inside the workbook is ran. This code is known as Visual Basic for Applications (VBA). VBA is also used to create Macros. The code calls (runs) queries contained in the Total Office Manager database and that information is added to the Dept-Info tab. Information in the Dept-Info tab is deleted prior to being repopulated.
The VBA code can be viewed and edited from the Developer tab in Excel. The Dept-Info worksheet (tab) is where most of the Scoreboard information is located. The Dept-Info worksheet (tab) contains raw data. It can be helpful to examine this worksheet to help you determine why certain information is not being included.
The Lists worksheet contains information necessary for the Department, Date, and Ranking filters (in the Scoreboard worksheet) to function. This information is dynamically generated and should not be modified.
The Scoreboard worksheet is where the information is displayed. All other worksheets are for gathering and organizing the information.
How Formulas are Used
The formulas used to populate the Scoreboard Technician information will be found on row 500 of the Scoreboard sheet. When the Scoreboard worksheet is calculated, the formulas in row 500 are copied to each row that represents a technician, as long as that row includes a name in the Technician cell.
The formulas calculate the numbers, and those numbers are added to each cell in that row. After that process has completed, the formulas are deleted and only the value remains. This process is known as “Formulas_Copy”. All empty rows are then hidden using VBA code.
Viewing the Scoreboard Formulas
All empty rows (rows with no technicians) are hidden. To view them, click Review, Unprotect Sheet, and enter the password found in the Help worksheet. From the Scoreboard sheet, click the upper righthand corner of the rows (grid) to highlight the entire sheet, right click, and select Unhide.
Please Note: The rows will be rehidden and the entire workbook will be password protected each time it is refreshed or opened.
Miscellaneous Information
The dropdown selection boxes (dropdown menus) may have an image on an arrow where the arrow on the selection would normally be. That’s because Excel does not show that arrow unless the selector is clicked. The image helps users realize that the box is a selection box.
We used Comments rather than Notes to display popup information. When the workbook was created, there were known bugs with Excel that created problems when we used Notes.
Dept-Data Worksheet – Column Definitions
This section explains what information is found in each of the columns within the Dept-Info worksheet. They are presented in the same order, from left to right, as the worksheet.
Key Points to Remember
All data is returned for YTD & Last Year. This is based on your Fiscal Year. Company Information > Fiscal Year.
We are only including data for Billable Employees. This is a setting in Employee > Payroll Items Setup > Billable checkbox.
There are many different CntTypes that are included in the Dept-Info worksheet. This offers users a huge amount of very useful business information to build things with. Here is a list of definitions for each one of them.
Demand Service to Serv Agrmnt Conv: The number of demand service work orders that resulting in the sale of a service agreement. The SA must be on an invoice that is assigned to the work order. Be sure your work order types are using the “What Best Describes This” setting.
Invoices: Count the number of sales by Technician. Sales and Invoices will both be counted. It is looking at techs assigned to the invoice from the Work Order Assignment form. This number can be a fraction.
Other Leads DemandService-Prev Maint: The number (quantity) of sales leads created while doing planned maintenance work. A sales lead must be created.
Prev Maint to Service Agrnt Conv: The number of planned maintenance work orders that resulted in the sale of a service agreement. The SA must be on an invoice that is assigned to the work order. Be sure your work order types are using the “What Best Describes This” setting.
SA Expiring this Year: The total number (quantity) of all service agreements that have an expiration date within the current fiscal year.
Sales Leads Created: The total number (quantity) of all sales leads created YTD. Sales leads are created from the Sales Opportunity Manager.
Sales Leads from Demand Service: The number of sales leads (in SOM that were created while doing Demand Service. This calculation depends on you using the Department features extensively and the “What Best Describes This. . .” setting found in many forms.
SO Equip Leads: The number of (quantity) of all sales leads where some type of equipment was the selected product. The Product must have the correct What Best Describes value set.
SO Equip Leads Value: The dollar value of all sales leads where some type of equipment was the selected product, in currency format. The Product must have the correct What Best Describes value set.
SO IAQAWT Leads: The number (quantity) of all sales leads where IAQ, Accessories, or Water Treatment was the selected product. The Product must have the correct What Best Describes value set.
SO IAQAWT Leads Value: The dollar value of all sales leads where IAQ, Accessories, or Water Treatment was the selected product, in currency format. The Product must have the correct What Best Describes value set.
SOM Equip Leads: The number of sales leads that were created and had a Product Type of Equipment. The “What Best Described This Product” value must be set to any of the equipment types.
SOM IAQ Leads: The number of sales leads that were created and had a Product Type of IAQ or Accessories. The “What Best Described This Product” value must be set to any of the IAQ or accessory types.
SOM Leads: The total number of sales leads of any type, product, or circumstances. This value looks at Employee Leads.
SOM Other Leads: The total number of sales leads based on criteria other than the other listed SOM leads (all other sales leads).
WO Callbacks: The number of work orders that are classified as Callbacks. Be sure that you are using the work order Callback feature. Check your Work Order Types and make sure you are setting a What Best Describes this WO Type value.
WO Warranty: The number of work orders that are classified as Warranty. Be sure that you are using the work order Warranty feature. Check your Work Order Types and make sure you are setting a What Best Describes this WO Type value.
CntValue
The number of occurrences, during the same CntValue, for a given transaction of other data. This number can be a fraction. The technician may be getting credit for part of a transaction. He/she may have been one of two or more technicians assigned to an invoice.
CntMonth
The fiscal month that included the CntValue. Format = ##. The month is expressed as a number from 1 to 12. 1 is January and 12 is December.
CntYear
The fiscal year for the transaction or other data. Format = YYYY
Department
The name of the Department as set up in Total Office Manager. Departments with the same name will have their data combined. This value is what is used in the Department selection list.
Industry
The name that was selected from the Industry list in Total Office Manager. Each department has this selection list. This is not a required field, but it is recommended to select a value.
Division
The name selected from the Division list in Total Office Manager. Each department has this selection list. This is not a required field, but it is recommended to select a value.
DeptType
The name selected in the Type list in Total Office Manager. Each department has this selection list. This is not a required field, but it is recommended to select a value.
Tech-Last-First
The last and first name of the employee as found in the Edit Employee form.
Technician
The “Print Check As” field of the employee as found in the Edit Employee form.
TechAlias
The value of the Alias field found in the Edit Employee form (in Total Office Manager). This is a unique and required field.
TranDate
The full date of the transaction or the entry date of the record. Format = YYYY/MM/DD.
This date is then converted to the last day of that month. Example: 2022-07-05 becomes 2022-07-31.
We then combine all the same transactions within that month into one row. This helps reduce the number of rows. Otherwise, there would often be too much information (rows) to add to this sheet. Excel has limits that we could easily exceed.
TranSubtotal
The total amount of the transaction before sales tax.
TranTax
Sales tax applied to the transaction.
TranTotal
The subtotal plus sales tax.
TranBalance
The unpaid portion of TranTotal. This is what your customer still owes you.
TechHours
The number of hours entered on a timesheet by employee. Be sure you are including the work order that was assigned to the invoice.
BilledHours
The number of hours sold on the invoice. Be sure that you are using the Item’s Labor Minutes feature and assigning work orders to invoices.
TechLabor
The amount of labor sold on the invoice in currency format. Sales tax is never considered.
COGS-NonLabor
The amount of all non-labor related income on the invoice in currency format. Sales tax is never considered.
Discount$
The total amount of any discounts on the invoice. It looks at Discount Items. Sales tax is never considered.
SADiscount$
The total amount of any discounts, only related to a service agreement, was included on the invoice. It looks at discount items that were created when the SA was created. These are special item types not created by the user. Sales tax is never considered.
SASold
The quantity of Service Agreement Item types that were included on the invoice.
SASold$
Sales tax is never considered.
DiagOnly?
A count of invoices where the only thing included on it was a Diagnostic Fee item. Be sure to set the item up as a Diagnostic Fee.
DiagOnly$
The dollar amount of Diagnostic Fee only invoices in currency format. Sales tax is never considered.
IAQ-Acc Sold
The total quantity of IAQ related items found on invoices.
IAQ-Acc Sold$
The dollar amount of IAQ related items found on invoices in currency format. Be sure that your invoice item Is setup properly. Sales tax is never considered.
EquipSold
The total quantity of equipment related items found on invoices. This value comes from serialized item types.
EquipSold$
The dollar amount of equipment related items found on invoices in currency format. This value comes from serialized item types. Be sure that your invoice item is set up properly. Sales tax is never considered.
PercOfHours
Total Paid Hours on an Invoice / Total Paid Hours for the Tech on that Invoice. This is calculated from the total Hours on the Timesheet where the Work Order is assigned to the Invoice.
PercOfIncome
The percentage of the income based on hours assigned. If two techs and one worked 3 hours and one worked 2 hours, one gets 2/5 of income the other gets 3/5. We take percentage of hours (or CntValue) and multiple by TranTotal. It is expressed as currency.
ManDays
This comes from Employee > Payroll Items Setup > Person Days.
TechID
A unique number that is automatically assigned to the employee by the database when it is entered through the software program. This cannot be changed by a user.
FiscalMonth
The month of the transaction or record creation date. Format = ##. The month is expressed as a number from 1 to 12. 1 is January and 12 is December.
FiscalStart
The beginning date of the company’s fiscal year. Format = YYYY/MM/DD. This information comes from the Company Information form in Total Office Manager.
FiscalEnd
The ending date of the company’s fiscal year. Format = YYYY/MM/DD. This information comes from the Company Information form in Total Office Manager.
Reports Included: No Description: List of Invoices, Sales, Estimates, and Credits with work order analysis information attached. This CDV is current limited to transactions for the last two (2) years. Drill Down: Yes, opens transaction (Tran Type)
Description & Field Explanations
1. WO Technician – The assigned technician on the work order. If there is more than one work order attached, the invoice will appear multiple times. 2. Full Name – The work order’s assigned technician full name. This will be displayed as Last Name, First Name MI. 3. Employee Alias – The work order’s assigned technician alias. 4. Employee Number – The work order’s assigned technician employee number. 5. Invoice Number – The transaction number of the sales transaction. 6. Invoice Date – The date of the sales transaction. 7. Attached WOs – The sum of the work orders attached to the invoice. 8. WO Date – The date of the work order. If there is more than one work order attached, the invoice will appear multiple times. 9. WO Status – The status selected on the attached work order. If there is more than one work order attached, the invoice will appear multiple times. 10. WO Description – The description on the attached work order. If there is more than one work order attached, the invoice will appear multiple times. 11. Technician Notes – The private and technician notes on the attached work order. If there is more than one work order attached, the invoice will appear multiple times. 12. Work Order Types – The type selected on the attached work order. If there is more than one work order attached, the invoice will appear multiple times. 13. Is Callback – The indicator a work order is flagged for being a callback. This is a Yes/No field. 14. Callback WO # – The work order number of the callback, if Is Callback is Yes. 15. Callback Employee Name – The assigned technician’s name for the work order callback, if Is Callback is Yes. 16. Callback Employee Alias – The assigned technician’s alias for the work order callback, if Is Callback is Yes. 17. En Route Minutes – Sum of hours times 60 for the billable technician where the work order is assigned on timesheet entries, the work order is assigned to the transaction, and the timesheet notes contains the word “en route”. 18. Working Minutes – Sum of hours times 60 for the billable technician where the work order is assigned on timesheet entries, the work order is assigned to the transaction, and the timesheet notes contains the word “working”. 19. En Route Hours – Sum of hours for the billable technician where the work order is assigned on timesheet entries, the work order is assigned to the transaction, and the timesheet notes contains the word “en route”. 20. Working Hours – Sum of hours for the billable technician where the work order is assigned on timesheet entries, the work order is assigned to the transaction, and the timesheet notes contains the word “working”. 21. TS Minutes – Sum of hours times 60 for the billable technician where the work order is assigned on timesheet entries and the work order is assigned to the transaction. 22. TS Hours – Sum of hours for the billable technician where the work order is assigned on timesheet entries and the work order is assigned to the transaction. 23. Billed Minutes – Sum of transaction labor minutes for all items that are service items marked as Used for Labor Only. Note: Labor Minutes is an item preference which must be enabled and labor minutes allocated to the invoice items. If no labor minutes exist on the invoice line item, the minutes will be calculated from the default labor minutes on the labor item setup. 24. Billed Hours – Sum of transaction labor minutes divided by 60 for all items that are service items marked as Used for Labor Only. Note: Labor Minutes is an item preference which must be enabled and labor minutes allocated to the invoice items. If no labor minutes exist on the invoice line item, the minutes will be calculated from the default labor minutes on the labor item setup. 25. Total En Route Minutes – Sum of all hours times 60 for the billable technicians where the work order is assigned on timesheet entries, the work order is assigned to the transaction, and the timesheet notes contains the word “en route”. 26. Total Working Minutes – Sum of all hours times 60 for the billable technicians where the work order is assigned on timesheet entries, the work order is assigned to the transaction, and the timesheet notes contains the word “working”. 27. Total En Route Hours – Sum of all hours for the billable technicians where the work order is assigned on timesheet entries, the work order is assigned to the transaction, and the timesheet notes contains the word “en route”. 28. Total Working Hours – Sum of all hours for the billable technicians where the work order is assigned on timesheet entries, the work order is assigned to the transaction, and the timesheet notes contains the word “working”. 29. Total TS Hours – Sum of hours for the transaction where the work order is assigned on timesheet entries, the technicians are billable, and the work order is assigned to the transaction. 30. Total TS Minutes – Sum of hours times 60 for the transaction where the work order is assigned on timesheet entries, the technicians are billable, and the work order is assigned to the transaction. 31. % of Hrs – Percentage of hours for technician divided by the total hours for the transaction where the work order is assigned on timesheet entries and the work order is assigned to the transaction. 32. Billed Minutes Difference – Difference of Billed Minutes minus Total Working Minutes. 33. Billed Hours Difference – Difference of Billed Hours minus Total Working Hours. 34. % of Billed Minutes – Product % of Hrs times Billed Minutes Difference. 35. % of Billed Hours – Product % of Hrs times Billed Hours Difference. 36. Billed Efficiency % – Quotient of % of Billed Minutes divided by Working Minutes. 37. Diagnostic Only – The indicator a work order or invoice has only diagnostic item(s) selected. If no other billable items are present, the transaction is considered Diagnostic Only. This is a Yes/No field. Note: Diagnostic items are Service item types marked as This Service Item is used as a Diagnostic Fee on the Additional Info tab of the item setup. 38. Options Presented – Sum of the group items selected on the work order items tab. 39. Options Accepted – Sum of the group items selected on the work order which have been accepted. 40. Invoice Total before Discounts – The transaction Subtotal. 41. Discounts – Sum of all discounts entered on the sales transaction. 42. SA Discounts – Sum of all Service Agreement discounts entered on the sales transaction for 100% coverage. 43. Invoice Net Sales before Tax – Difference of Invoice Total before Discounts minus Discounts. 44. Invoice Tax – The transaction calculated tax amount. 45. Invoice Total w Tax – Sum of Invoice Net Sales before Tax plus Invoice Tax. 46. % of Total Revenue – Product of Invoice Total w Tax times % of Hrs. 47. Tran Cost – Sum of transaction cost for all items that are not service items marked as Used for Labor Only. 48. Labor Cost – Sum of timesheet hours times current rate plus hours times current liability where the Work Order is assigned to the Timesheet for the billable technician and the work order is assigned to the invoice. Note: These rates may be reviewed in the Job Costing Time Sheet Details CDV. For a technician hours to be considered billable, the option Employee is Billable must be enable in their Employee Payroll Items. 49. Labor Liab – Sum of Labor Cost plus Labor Liab. Note: The labor liability is recorded at the time of the timesheet entries. These amounts include the employer liability for FICA SS (6.2%), FICA Med (1.45%), FUTA and SUTA rates which are set under Employees | Unemployment Tax Rates. This amount may be reviewed in the Job Costing Time Sheet Details CDV. 50. Total Labor – Sum of Labor Cost plus Labor Liab. 51. Total Labor Burden – Sum of Labor Cost plus Labor Liab plus Labor Burden times Total Labor. 52. Labor Burden – Value set under Edit | Preferences | Customer:Jobs | Company Preferences | Apply Labor Burden to Job Costing. 53. Total Cost w Labor+Burden – Sum of Tran Cost plus Total Labor plus Total Labor Burden. 54. % of Total Cost – Results of % of Hrs times Total Cost w Labor+Burden. 55. GP $ – Difference of % of Total Revenue minus % of Total Cost. 56. GP % – Results of GP $ divided by % of Total Revenue. 57. Cust:Job Name – The name of the Customer:Job record. 58. Cust:Job Display Name – The full display name of the Customer:Job record. 59. Sales Rep – The full name of the sales rep selected in the header of the sales transaction. 60. Sales Rep Alias – The alias of the sales rep selected in the header of the sales transaction. 61. Tran Department Display – The department’s full name selected in the header of the sales transaction. 62. Tran Department Alias – The department’s alias selected in the header of the sales transaction. 63. Tran Department – The department’s What Best Describes This department selection selected in the header of the sales transaction.
Top Reasons Your AR Aging Report Does Not Match Your Balance Sheet
There are several reasons the AR Aging report is not matching the Balance Sheet. Generally, it is a date timing issue. The Balance Sheet strictly looks at the transaction date while the AR Aging reports look at the transaction debit date, the transaction credit date, and the date the credit was applied to the debit transaction.
Here are the most common reasons the AR Aging Report does not match your Balance Sheet Report.
The AR Aging reports were not run with the same date parameters as the Balance Sheet.
Transactions affecting Accounts Receivable are out of balance in the General Journal.
Transactions affecting Accounts Receivable are missing from the General Journal.
Credit application dates are not in sync with General Journal transaction dates.
Accounts Receivable account is improperly being used in Invoice Item setup.
Accounts Receivable account is improperly selected on transactions.
AR Aging Not Matching Balance Sheet – Report Date Verification
When running the Balance Sheet and A/R Aging reports, please verify the dates on the two reports are the same. If you find the report dates to be different, re-run the reports with the same date parameters.
Out of Balance Transactions
Occasionally a transaction may become out of balance if it was not properly saved to the General Journal. Many factors can contribute to this behavior. There may have been a network disconnect in the middle of the save procedure, incomplete data, a power surge, etc. Total Office Manager has built in utilities to scan for these types of transactions. The Database Checkup utility is located under Tools | Utilities | Database Checkup. Run the scan for Out of Balance Transactions.
Checking the AR Aging Report
Locate any Invoices, Credits, or Payments and double click to open. Once in the transaction, right click and choose the option Find In GJ. A review of the General Journal entry may indicate why this transaction is out of balance. Typically, typing a space on the Internal Memo and resaving the transaction will update the General Journal entry. Once you have reviewed and resaved all located out of balance transactions, rerun the scan again to validate you have in fact corrected these entries.
Missing GJ Entries
For the same reasons as the out of balance scan, entries may also be missing from the General Journal. Running the scan for Missing GJ Entries will locate any records that are missing from the General Journal that impact accounting.
Locate any Invoices, Credits, or Payments and double click to open. Review each transaction and resave by typing a space on the Internal Memo to input into the General Journal. Once you have reviewed and resaved all located missing entries, rerun the scan again to validate you have in fact corrected these entries.
Incorrect Payment Applications
Many times the number one culprit of the reports not matching is improper payment application dates. There are many ways to review these entries. Review the A/R Payment Applications Review CDV will help to identify where payment application dates may cause discrepancies between the reports. Custom Data Views (CDV) are located under Reports | Custom Data Views. To access this Custom Data View, please navigate to Reports | Custom Data Views. Under the Data Views menu, select A/R Payment Applications Review. If you do not see A/R Payment Applications Review listed, please select Download/Update Views. Check the box next to A/R Payment Applications Review and click the Update Selected Views button. Once confirmation of download has been displayed, close the Custom Data View Download form and choose A/R Payment Applications Review from the Data Views menu.
Custom Data View for Fixing the A/R Aging Report Not Matching Balance Sheet
When reviewing the transactions in the list, compare the Debit Date and Credit Date against the Date Applied. The Date Applied is to be the greater of the Debit and Credit dates. If you find a Date Applied that is incorrect, double click on the entry to open the Customer:Job record. Right click within the Customer:Job and choose the History option. Locate the Invoice or Sale on Sales tab, right click, and choose the option Adjust. Check the box next to the credit transaction and click the Delete and Close button.
Click on the Payments tab, right click and choose New. Locate the debit transaction and click on the line entry. There is no need to check the box. Click the Set Credit button in the lower left corner of the payments form. Ensure the Date Applied is the greater of the two dates. Click Save & Close on the credits form. Close out of the payment form. There is no need to save the payment. The credit application is saved when the credits form is saved and closed.
Item Account Setup
If Smart Account Selection Filtering turned off, it is possible to select an inappropriate account when setting up items. This could also lead to A/R Agint to not match the Balance Sheet
Open your Invoice Item List. Be sure that you have the Income Acct, COGS Acct, and the Asset Acct columns included in your list view.
Go through your list of items to be sure that the correct chart of account has been selected. You should only see “Income” type of accounts in the Income Acct column. You should only see Cost of Goods Sold accounts in the COGS Acct column. Don’t just rely on the name to indicate the COA type. If you are not sure, look at your Chart of Accounts list to double check names against COA types.
You may also review the Invoice Item Setup in the Custom Data Viewer.
Locate AR Aging Report Information with the Custom Data Viewer
Improper Account Usage
We have commonly seen Accounts Receivable selected as the Discount Account on payment entries. We have commonly seen Bad Debt setup using an Accounts Receivable chart of account. Neither should be setup this way.
When using an Accounts Receivable type as type as a discount, no customer is associated with this record. Typically discount transactions affect an expense account type. Total Office Manager no longer allows for the selection of an Accounts Receivable account in the discount account selection on a payment. If the intent was to move the balance to a bad debt receivable account, it is customary to create an invoice item for bad debt which is tied to an expense account and use this item as outlined in the Help Topic: Bad Debt – Dealing with Collection Agencies.
Is it also possible that, at one time, certain items could have had an incorrect COA and have since been corrected. If the user did not click “Yes” to the “Update Historical” option, old transactions using these items would still be causing problems. Currently, there is no easy way to locate transactions that may have been created using items with an incorrect Chart of Account selection. If you believe that this may describe your problem, please contact technical support for more information.
A/R Aging Not Matching Balance Sheet – Additional Troubleshooting
If all the above have been reviewed, verified, and corrected, the next step would be to compare the A/R Aging customer balances to the Balance Sheet customer balances. The Register Accounts Receivable CDV can assist in summing the customer entries in the General Journal and allow for easier comparison to the AR Aging reports. Locate the Customer:Jobs that are different from one report to the next. Determine why the source of the discrepancy and correct as outlined in the Incorrect Payment Applications section.
This is an easy fix; however it is a very time consuming fix. Other than taking the time to analyze the records as outlined above, there is no “easy” button to correct this. Please remember that patience is a virtue and patience is the key.
Please note: The same steps would be taken for Accounts Payable if the amounts on the AP Aging Report did not match the Executive Summary or the Balance Sheet. You would just need to replace AR forms and functions with AP forms and functions and continue to follow the steps as outlined.
Accounting Review
If you are unable to locate the differences, or would like assistance in troubleshooting the reports and balances, our Accounting Review service may be the way to go. Many times we are asked, “But isn’t that why we have a support plan?” Our technical support is more for the software functionality than for dissecting the numbers. When there is question with where a number is coming from and it requires digging through financials that we are not familiar with, we do refer to the Accounting Review service for two reasons:
Our Accounting Review team is very thorough. Not only do they look for data anomalies, they document discrepancies, offer resolutions, explain a particular process or procedure that will prevent the reason for the accounting review referral.
Our Accounting Review team validates that the software is functioning as the software is intended to where accounting aspects are concerned. If a bug is determined to be the cause of the issue, they fully acknowledge as such and you do receive a credit of $150.00 per bug determined to be applied towards to review fees.
If you are interested in our Accounting Review service, please contact our Help Desk via phone (913-322-4666) or by email (helpdesk@aptora.com) for further direction.
Understanding the Rounding Accounts on Financial Statements
This knowledgebase article explains why the income statement and balance sheet (might) contain an account called “Rounding” and what you may do to reduce that amount.
Why Rounding is a Problem
All accounting software struggles with the problem of rounding. Rounding situations most often occur when calculating taxes on sales, but other transaction can cause rounding issues too. This is a normal occurrence and is the result of complicated rounding rules and the order in which numbers must be rounded. The differences are usually small. Federal and state tax authorities understand the challenge with rounding. In fact, many tax forms do not require than cents be included.
A typical accounting database may contain 50,000,000 or more numeric amounts. Total Office Manager stores most of these numbers in their actual form – four or more numbers to the right of the decimal point (example: $537.897643). However, most forms and reports necessarily display numbers ROUNDED to only two numbers right of the decimal point (example: $537.90). Generally, the un-rounded numbers are totaled up before being rounded. Other circumstances require that individual numbers be rounded prior to being summed. This concept is known as “ordering” and can be very subjective. The difficulty of rounding becomes more profound when totals must be combined from several different areas of the database and then totaled.
Most accounting software applications that we have studied hide these rounding amounts by various methods. Our software exposes the rounding amounts so they can be considered.
How Numbers are Rounded (the methodology used)
We use a conventional method of rounding. Sometimes items are rounded before they are totaled and other times they are rounded before they are totaled. Here are two examples:
$10.0004 gets rounded to $10.00
$10.005 gets rounded to $10.06
Rounding Account on the Income Statement
Total Office Manager creates a special account (in Chart of Accounts) called “Rounding”. This is an “Other Expense” account type which, when needed, appears on your Income Statement. The amount may be a positive or negative number. You cannot delete this account and you cannot change its Type. You can rename it, renumber it, and change other properties.
This account may have a positive or negative balance. Over time, these slight differences can add up to several dollars or more. At any point, an Adjusting Journal Entry can be used to zero out this amount. Generally speaking, users do not need to do anything. Numbers are rounded up about as often as they are rounded down, so the differences are constantly being balanced fairly well.
Rounding Account on the Balance Sheet
This account is automatically created by Total Office Manager when the Balance Sheet has become out of balance. The amount may be a positive or negative number. This is a special account that does not appear on your Chart of Accounts. There is no way to select this rounding account when entering an adjusting journal entry or other transaction.
This rounding account may indicate that one or more transactions are out of balance. That means that the credit and debit balances of one or more transactions do not balance (equal each other). You may check the Trail Balance form to find out for sure. Click Banking > Trial Balance. Both of the credit and debit numbers at the bottom of the form should match.
Rounding Account FAQ
Q: My Income Statement includes an account called Rounding. I did not create it. What is it and what is it used for?
A: Behind the scenes, Total Office Manager does a lot of rounding to keep forms and reports as accurate as possible. Sometimes the program needs to add or subtract one or more cents to a transaction to get things in balance.
Total Office Manager creates a special account (in Chart of Accounts) called “Rounding”. This is an “Other Expense” account type which appears on your Income Statement when necessary. This account may have a positive or negative balance. Over time, these slight differences can add up to several dollars or more. At any point, you can add an Adjusting Journal Entry to zero out this amount. Generally speaking, users do not need to do anything.
Q: My Balance Sheet shows a “Rounding Adjustment”. I did not create it. What is it and why do I have it?
A: This account is automatically created by Total Office Manager when the Balance Sheet has become out of balance. This is a special account that does not appear on your Chart of Accounts.
Total Office Manager does not let you save transactions that are out of balance. In other words, the balance sheet is self-balancing and only becomes out of balance when one or more of the following situations occur.
The most common reason is rounding due to taxes.
The program has shut down improperly.
A power outage interrupted a transaction save.
The database has become corrupted.
There have been problems with network connectivity.
A hard disk drive is starting to develop “bad clusters” which may indicate the drive’s pending need of replacement.
The data was not properly saved to the database.
Fixing a Large Rounding Account
If the Rounding Adjustment account is less than a few dollars, we would not necessarily recommend that you do anything about it. It is likely a result of rounding – which is normal. However, if the amount is large, your database likely contains corrupted transactions. These transactions are probably either “out of balance” or are missing accounts (their account selections have become “lost”). These should be fixed.
Repair Procedures
Resave the Transaction
Corrupted transactions can usually be fixed by re-saving them. Here is how:
From the main menu, click Tools | Utilities. The Utilities form will appear.
Double click on Database Checkup. The Database Checkup utility will appear.
Search for any transactions which are out of balance. In the Scan selection box, select “Out of Balance Transactions” and click the Scan button. If any transactions are listed, follow the on-screen instructions. Generally, you will need to open each transaction listed and re-save the transaction. The best way to do this is to add something to the form (like a memo – even if just a period or a dash) then immediately remove it. Click the Save & Close button and continue to the next item on the list. Note that when you re-save, the item will not be removed from the list.
Now search for any transactions which are missing accounts. In the scan selection box choose “Find Missing Accounts” then click the Scan button. If transactions are listed, follow the on-screen instructions. Generally, you will need to open each transaction and re-save it in the manner described in step 3.
Recheck the Rounding Adjustment account on your balance sheet.
Recreate the Transaction
Occasionally re-saving a transaction will not fix it. In that case, it will need to be deleted and recreated. Be sure you have the proper permissions to delete and add transactions.
Backup your database (for safety reasons).
Using the Database Checkup utility, scan for “Out of Balance Transactions” and for “Find Missing Accounts” as outlined above.
Thoroughly document each transaction to be deleted. Create detailed screenshots of each transaction (Shirt+F2 prints the active window or form; Shift+F3 prints the entire screen). Make notes and print reports as needed. You need to make available all information required to recreate each transaction.
Delete the transaction from its applicable list. For example, delete a payment from the Payments List, and delete a bill from the Bills List. Now recreate the transaction based on your pre-deletion documentation, making sure to use the exact same dates, amounts, and other information.
Please contact technical support if you still suspect problems.
Tips
Remember, when deleting transactions, the order of your deletions is important. You must start at the “end” of the sequence and work backwards. For example, to delete a bill you must first delete the subsequent check which paid it. i.e.: start at the end of the chain of events (the check) and work backwards. To delete an invoice, you must first delete its subsequent payment. i.e.: start at the end of the chain of events (the payment) and work backwards. To delete a payment, you must first delete the subsequent deposit. See the help topic “Tips – Deleting – General How To” for more information.
When a transaction that has been marked as “Reconciled” is deleted and recreated, it will no longer be considered reconciled in the program. You will need to take care of this when you perform your next Bank Reconciliation.
Contractor Compass is a set of dashboards designed to offer business owners unparalleled insight into their business. These super dashboards are powered by EGIA Contractor University. By that, we mean they closely follow the recommendations and best practices taught by the teaching faculty at EGIA. That faculty includes Aptora’s president, James R. Leichter.
Currently, Contractor Compass is compatible as Windows Application only. At this time, it is not compatible with Apple (iOS) platform. Aptora recommends utilizing a work around by remoting into a computer that is running a Windows operating system or running a dual operating system on your Apple machine.
Minimum Microsoft Excel Versions Required
You must be using Excel 365 or Excel 2021 (32-bit or 64-bit). We use the FILTER function in many of our workbooks. The FILTER function is only available in Excel for Microsoft 365 and Excel 2021. In Excel 2019, Excel 2016 and earlier versions, it is not supported.
How the System Works
The system mainly consists of a desktop app and Microsoft Excel files. The Excel file is used to display your dashboard information. We have built two dashboards and there will be more to come. These will be offered for a nominal fee.
The Excel files are macro enabled and use the “.xlsm” file extension. They contain VBA source code that is used to securely connect to your Total Office Manager database. Once connected, the Excel file runs database queries called Views and Stored Procedures. These queries consume large amounts of information and place the data in a set of workbooks. This data is further processed using the power of Excel and fed into worksheets that are designed for presentation.
Why We Use Microsoft Excel
100% customization. Power software users demand extreme flexibility. They are always telling us they would like to see something different, and few people ever ask for the same thing. We built this system in MS Excel so that users could create nearly anything they wanted. MS Excel is the most popular spreadsheet software in the world. It is easy to learn and there is a lot of support for it.
Microsoft Excel along with Total Office Manager’s Microsoft SQL Server database, offer you complete and total access to your data and give you an almost unlimited opportunity to create any report imaginable.
Requirements
You will need to have Contractor Compass® and Microsoft Excel® version 2019 or 365 installed and fully updated. You will need to have an active support plan for Total Office Manager. You do not need to have Total Office Manager installed on the same computer. You ill need your original Total Office Manager serial number to install Contractor Compass. That can be found on your original invoice and the About box in Total Office Manager.
Security and Permissions
You must have a user name and password. You will use the same one that you use for Total Office Manager. You will need to have permission to launch the Contractor Compass.
In Total Office Manager, go to User List/Security form > double click on a user name > click Next > navigate to the Reports section. You should see an option called Company Dashboard. Click the box to select it. This gives the user permission to use the Contractor Compass Launcher app and therefor open any dashboard.
Installing the Dashboard Software
The following applies to both on-premises and hosted (cloud) users.
When you invest in this dashboard software system, you will immediately receive an email with a download link. The download is a zip file. That link will be live for thirty days, so you have plenty of time to download it.
Unzip the file called Contractor-Compass-Installer.zip, to a location of your choice and run the ContractorCompass.msi to install the software. The install is easy. You will basically need to click a few Next buttons and you should be finished. Please keep all the default settings, unless you have a good reason to change them.
Launching the Software
Double click on the Contractor Compass icon or search for the application using the Windows Search feature. If you do not see the icon, you can double click on the program file located at: “C:\Program Files (x86)\Aptora\Contractor Compass\ContractorCompassDashboardLauncher.exe”
Required Settings
The first time you run the app, you will be taken to the Settings page and asked to configure the program.
If Aptora hosts your software, please check the Is Hosted box. Otherwise, leave this unchecked.
Only if Hosted: From the Host Name list, select a Host Name. You should only see one choice.
Only if Hosted: Enter an Instance Name. The support department can provide you with this information.
Only if Hosted: Enter a Port Number. The support department can provide you with this information.
Enter a Serial Number. This is the same serial number that you use for Total Office Manager. You can get this by opening the About box in Total Office Manager. Is is also on your Total Office Manager invoice.
Select a Server. This is the same server you would have selected using your Total Office Manager software program. See Configuration Tips below, for more information.
Select the Database (aka: Company File) you wish to connect to. If hosted, you will need to enter the name of the database you wish to connect to. Example: ABC Service Company
The Company Dashboard Path and Custom Dashboard Path should default to the correct locations. You should not have to change these settings.
Click the Submit button.
Configuration Tips
If you need to know more about your server and database, open Total Office Manager, click File > Company File Information > click the File Properties tab.
For all on premise installations, please enter your Total Office Manager found under Help | About.
Software File Locations
This information is FYI for technical users. Most users will not need to know this.
The software program files are installed here by default: C:\Program Files (x86)\Aptora\Contractor Compass
The data files are installed here by default: C:\ProgramData\Aptora\CCompass
The Excel dashboard files are located here by default: C:\ProgramData\Aptora\CCompass\Dashboards
Your custom Excel dashboard files should be placed here: C:\ProgramData\Aptora\CCompass\CustomDb
Launching the Dashboards
Click the View Dashboards button to see a list of available dashboards.
Double-click the dashboard of your choice. Excel will open and the data will start loading into the Workbook.
Important Notes
There may be a delay between your click and a progress bar opening, indicating that Excel is loading data. This delay can take several minutes, depending on how much data Excel is trying to load. If you accidently open two or more Excel files, you can just close them.
You see a warning from Microsoft Windows or Excel that you are opening a potentially unsafe file. Please click Ok or Continue. We have no way of preventing this warning. See Tips below, for more information.
Microsoft Outlook Conflict: It may not be possible to open MS Outlook when you are loading a dashboard. We are looking into this issue.
Understanding the Dashboard Information
We have added a large number of comments in the Excel files. Hover over the arrow as shown to see them. Check for a Tips or Preferences tab for other helpful information.
Fixing Wrong or Incomplete Information
Many users will notice problems related to inaccurate, incomplete, or missing data. It is essential that you are using Total Office Manager to its fullest extent otherwise you will not be able to get the most out of this system.
Unclassified Information
Unclassified means that no Department was selected on the Customer:Job. Unclassified is used in other places too, to indicate that some information was not selected.
Start with a copy of an existing dashboard. Build your own files and place them in the custom file location. Your custom Excel dashboard files should be located here: C:\ProgramData\Aptora\CCompass\CustomDb
You must be comfortable with Microsoft Excel to make modifications or to build your own dashboards. You may also need to be familiar with VBA (Visual Basic for Applications). This is a type of source code and it is used extensively in our system. We have added robust comments in the source code to help developers.
Limited Support
Aptora Support agents will not be able to help you modify, alter, create, or fix custom dashboards or Excel files. They will not be able to help you understand or interpret the data presented in a dashboard. They cannot offer advice on what the various values should be other than direct you to existing documentation.
Aptora can refer you to trainers and or business consultants that you could hire to assist you.
Updating Contractor Compass
Click the Information icon or click the About button. You will be able to quickly see if there is an update available by looking for the Update Available label. Click the Check for Update to download and install it.
Caution: The update will overwrite the Excel files located in the Dashboards folder. If you modify a dashboard, you should make copies and use the copy for your own custom dashboard.
Tips
The Excel files must be launched (opened) from the Contractor Compass Launcher to load Total Office Manager data. The Contractor Compass Launcher uses a secure user name and password that it passes to the database before it can make a connection. You can open the Excel dash board files with Excel, but it will not load any information.
When you close Excel, you will be asked if you wish to save any changes, even when you did not really make any. The changes were related to the data being loaded. Saving allows you to open the Excel file directly from Excel and view the data that has been saved. If you made changes to formatting, you may wish to save those but be sure to make copies and use the copy for your own custom dashboard.
You see a warning from Microsoft Windows or Excel that you are opening a potentially unsafe file. This is because we are running macros and VBA code. Please see the KB article called Change Macro Security Settings in Excel.
If you see a red bar at the top of the Excel file, your computer security settings may be blocking the the file from running macros. This is a default setting when a file is downloaded from the internet. To unblock, you will need to edit the file properties and Unblock it. For more information on unblocking an Excel file, please review the article Macros from the internet will be blocked by default in Office.
Using the Invoice/Sale/Credit/Estimate List Report CDV
Type: Custom Data View
Reports Included: No
Description: List of Invoices, Sales, Estimates, and Credits with analysis information attached. This CDV is current limited to transactions for the last three (3) years.
Drill Down: Yes, opens transaction (Tran Type)
Columns and data for the Invoice/Sale/Credit/Estimate List Custom Data View are outlined below:
Column Name
Form
Field
Notes
Cust:Job Name
Invoice/Sale/Credit/Estimate
Associated Transaction Customer:Job Name
Tran No
Invoice/Sale/Credit/Estimate Transaction Header
Number
Tran Date
Invoice/Sale/Credit/Estimate Transaction Header
Date
Tran Type
Invoice/Sale/Credit/Estimate Transaction Header
Type
Tran Status
Invoice/Sale/Credit/Estimate
Status
Tran Subtotal
Invoice/Sale/Credit/Estimate
Subtotal
Tran Tax
Invoice/Sale/Credit/Estimate
Tax Total
TranUseTax
Invoice/Sale/Credit/Estimate
Use Tax Total
Tran Total
Invoice/Sale/Credit/Estimate
Total
TranBalance
Invoice/Sale/Credit/Estimate
Amount Due
Date Paid
Invoice/Sale/Credit/Estimate
Calculated Field
This is the date the last payment was applied making the Invoice or Credit Memo balance zero.
TranLaborCost
Timesheets
Calculated Field
Sum of line item cost or item’s labor cost for all items that are service items marked as Used for Labor Only Note: Labor Minutes is an item preference which must be enabled and labor minutes allocated to the invoice items.
TSLaborCost
Timesheets
Calculated Field
Sum of timesheet hours times hourly rate where the Work Order is assigned to the Timesheet for the technician. These rates may be reviewed in the Job Costing Time Sheet Details CDV.
Labor Burden
Company Preferences & Timesheets
Calculated Field
Total Labor
Timesheets
Calculated Field
Sum of TSLaborCost plus Labor Burden
SA Discounts
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of all discount type line items which as Service Agreement Discount types within the transaction
Total Discounts
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of all discount type line items within the transaction
TranPartCost
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of transaction cost for all items that are not service items marked as Used for Labor Only plus TranUseTax.
TranTotalCost
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of TranTotalCost plus Total Labor Burden
GrossProfit
Invoice/Sale/Credit/Estimate
Calculated Field
Difference Tran Total minus TranTotalCost
PercGP
Invoice/Sale/Credit/Estimate
Calculated Field
Results of TranTotalCost divided by % of Tran Total
Internal Memo
Invoice/Sale/Credit/Estimate
Internal Memo
Tran Department Display
Invoice/Sale/Credit/Estimate Transaction Header
Associated Department Display
Tran Department Name
Invoice/Sale/Credit/Estimate Transaction Header
Department Name
Tran Department Alias
Invoice/Sale/Credit/Estimate Transaction Header
Associated Department Alias
Tran User Created
Invoice/Sale/Credit/Estimate
Security Label
Tran Date Created
Invoice/Sale/Credit/Estimate
Security Label
Tran User Revised
Invoice/Sale/Credit/Estimate
Security Label
Tran Date Revised
Invoice/Sale/Credit/Estimate
Security Label
From Mobile?
Invoice/Sale/Credit/Estimate
*****
Yes if created in the Aptora Mobile II application. No if created in the desktop application. No includes migrated transactions.
Employee Efficiency CDV (Custom Data View) is a report that lists invoices, sales, and credits with the assigned work orders and labor hours associated. This CDV also contains columns to show efficiency of the time estimated from the work orders to the hours the technician worked to the time billed on invoices. The report has “drill down” capability to open the transaction record.
Form Access
From the main menu, click Reports | Custom Data Views
Custom Data View File Path
Update Your CDVs
Please be sure to update your CDVs. Go to the Custom Data Viewer menu (top left). Click Check for Updates.
Open the Employee Efficiency CDV
Then, in the Custom Data Viewer, click Data Views | Employee Efficiency
Custom Data Viewer – Employee Efficiency
Columns and Data Definitions – Employee Efficiency CDV
Columns and data for the Employee Efficiency Custom Data View are outlined below:
Custom Data View – Employee Efficiency
Column Name
Form
Field
Notes
WO Technician
Work Order : General Tab
Assigned To
Tran No
Invoice/Sale/Credit/Estimate
Number
Tran Date
Invoice/Sale/Credit/Estimate
Date
Tran Type
Invoice/Sale/Credit/Estimate
Type located in Title Bar
WO Minutes
Work Order : General Tab
Duration
If the work order is a multiday, the minutes are the Duration for the employee where the work order is assigned to the transaction.
WO Hours
Work Order : General Tab
Calculated Field
WO Minutes divided by 60
TS Minutes
Timesheets
Calculated Field
Sum of hours times 60 for technician where the work order is assigned on timesheet entries and the work order is assigned to the transaction
TS Hours
Timesheets
Calculated Field
Sum of hours for technician where the work order is assigned on timesheet entries and the work order is assigned to the transaction
Billed Minutes
Invoice/Sale/Credit
Calculated Field
Sum of transaction labor minutes for all items that are service items marked as Used for Labor Only Note: Labor Minutes is an item preference which must be enabled and labor minutes allocated to the invoice items.
Billed Hours
Invoice/Sale/Credit
Sum of transaction labor minutes divided by 60 for all items that are service items marked as Used for Labor Only Note: Labor Minutes is an item preference which must be enabled and labor minutes allocated to the invoice items.
Total TS Hours
Timesheets
Calculated Field
Sum of hours for the transaction where the work order is assigned on timesheet entries and the work order is assigned to the transaction
Total TS Minutes
Timesheets
Calculated Field
Sum of hours times 60 for the transaction where the work order is assigned on timesheet entries and the work order is assigned to the transaction
% of Hrs
Timesheets
Calculated Field
Percentage of hours for technician divided by the total hours for the transaction where the work order is assigned on timesheet entries and the work order is assigned to the transaction
WO Minutes Difference
Work Order/Transaction
Calculated Field
Difference of WO Minutes minus TS Minutes
WO Hours Difference
Work Order/Transaction
Calculated Field
Difference of WO Hours minus TS Hours
Billed Minutes Difference
Timesheets/Transaction
Calculated Field
Difference of Billed Minutes minus TS Minutes
Billed Hours Difference
Timesheets/Transaction
Calculated Field
Difference of Billed Hours minus TS Hours
Emp % Mins Billed
Timesheets/Transaction
Calculated Field
Percentage of % of Hrs times Billed Minutes
Emp % Hrs Billed
Timesheets/Transaction
Calculated Field
Percentage of % of Hrs times Billed Hours
Billed Efficency %
Work Order/Transaction
Calculated Field
Results of % of Hrs times Billed Minutes divided by TS Minutes
Worked Efficency %
Work Order/Transaction
Calculated Field
Results of % of Hrs times TS Minutes divided by WO Minutes
Hours Match?
Timesheets/Transaction
Calculated Field
Comparison of WO Minutes and TS Minutes
Full Name
Work Order : General Tab
Associated Assigned To Employee
Employee Alias
Work Order : General Tab
Associated Assigned To Employee
First Name
Work Order : General Tab
Associated Assigned To Employee
Middle Initial
Work Order : General Tab
Associated Assigned To Employee
Last Name
Work Order : General Tab
Associated Assigned To Employee
Employee Number
Work Order : General Tab
Associated Assigned To Employee Number
Tran Subtotal
Invoice/Sale/Credit/Estimate
Subtotal
Tran Tax
Invoice/Sale/Credit/Estimate
Tax Total
Tran Total
Invoice/Sale/Credit/Estimate
Total
% of Total
Timesheets/Transaction
Calculated Field
Results of % of Hrs times Tran Total
TranBalance
Invoice/Sale/Credit/Estimate
Amount Due
Discounts
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of all discount type line items within the transaction
Total + Discounts
Invoice/Sale/Credit/Estimate
Calculated Field
Difference of Tran Total minus Discounts
% of Total w Discounts
Timesheets/Transaction
Calculated Field
Results of % of Hrs times Total + Discounts
Tran Cost
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of transaction cost for all items that are not service items marked as Used for Labor Only.
Labor Cost
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of timesheet hours times hourly rate where the Work Order is assigned to the Timesheet for the technician. These rates may be reviewed in the Job Costing Time Sheet Details CDV.
Labor Liab
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of timesheet hours times current rate plus hours times current liability where the Work Order is assigned to the Timesheet for the technician. These rates may be reviewed in the Job Costing Time Sheet Details CDV.
Total Labor
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of Labor Cost plus Labor Liability
Labor Burden
Invoice/Sale/Credit/Estimate
Calculated Field
Value set under Edit | Preferences | Customer:Jobs | Company Preferences | Apply Labor Burden to Job Costing
Total Labor Burden
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of Labor Cost plus Labor Liability plus Labor Burden times Total Labor
% of Total Cost
Timesheets/Transaction
Calculated Field
Results of % of Hrs times Tran Cost
Total Cost w Labor
Invoice/Sale/Credit/Estimate
Calculated Field
Sum of % of Total Cost plus Total Labor Burden
GP $
Invoice/Sale/Credit/Estimate
Calculated Field
Difference of % of Total w Discounts minus Total Cost w Labor
GP %
Invoice/Sale/Credit/Estimate
Calculated Field
Results of Total Cost w Discounts divided by % of Total w Discounts
Description: List of invoices, sales, credits, and inventory/serialized adjustments with items containing costs that vary from the daily average cost.
Drill Down: Yes, opens transaction type
Columns and data for the Inventory Cost Review Custom Data View are outlined below:
Column Name
Form
Field
Notes
Tran Date
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Date
Tran Number
Transaction
Number
Tran Type
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Type located in Title Bar
Tran Date
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Date
Tran Number
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Number
Item Name
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Associated Item Name/Number
Item Type
Item
Associated Item Type
Tran Qty
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Associated Transaction Item Quantity
Tran Cost
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Associated Transaction Cost/Unit Price
AvgCost
Item Register
Calculated Field
The reported average cost for the item as of the transaction date
Cost Match?
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Calculated Field
Analysis of whether the transaction cost matches the item register reported average cost
Cost Difference
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Calculated Field
AvgCost minus Tran Cost
Expected Total Cost
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Calculated Field
AvgCost times Tran Qty
Tran Total Cost
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Calculated Field
Tran Cost times Tran Qty
Value Difference
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Calculated Field
(AvgCost times Tran Qty) minus (Tran Cost times Tran Qty)
Recommended Action
******
Proposed resolution to correction the difference calculated
Item User Created
Item
Item Security Label
Item Date Created
Item
Item Security Label
Item User Revised
Item
Item Security Label
Item Date Revised
Item
Item Security Label
Tran User Created
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Transaction Security Label
Tran Date Created
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Transaction Security Label
Tran User Revised
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Transaction Security Label
Tran Date Revised
Invoice/Sale/Credit/
Inventory Adjustment/
Serialized Adjustment
Transaction Security Label
TranID
Work Order
Table ID of Transaction
ItemID
Item
Table ID of Item Employee
Scroll to Top
Request a Personalized Demo
Fill out the form below, and we will be in touch shortly.
Demo Form 121422
By entering your information above and clicking the “Agree and Get Started” button, you agree to our Terms of Use and Privacy Policy that we may contact you, by SMS, at the phone number and email address you provide in this form in accordance with our Terms of Use. Promotion Terms & Conditions apply.
