Dashboards Archives

Contractor Compass® – Technician Scoreboard

Field Technician Scoreboard

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.
Technician Dashboard Scoreboard Help

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.

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.
The data in this worksheet is only as good as the way the software was set up and being used. It is important to go through the Preparing Total Office Manager® for Contractor Compass™ help topic.

CntType

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.

How to Install and Use Contractor Compass Dashboards

Contractor Compass Introduction

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.

Please see our KB article titled Preparing Total Office Manager for Contractor Compass™.

System Compatibility

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

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.

Please read the following KB article and follow the steps very carefully. Preparing Total Office Manager for Contractor Compass™. Please see Related Topics below.

Customizing Your Dashboards

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.

Preparing Total Office Manager for Contractor Compass™

Contractor Compass™ – Super Dashboard

Our initial release includes a company-wide dashboard. Additional dashboards are available for additional investments. The company-wide board also contains sub-boards for each division and department.

Contractor Compass makes extensive use of the vast accounting system that makes up the foundation of Total Office Manager. Aptora’s Total Office Manager is a true all-in-one management system, meaning it is not hobbled by being a QuickBooks band-aid addon. The dashboards can access every corner of the database and makes use of all information in real-time.

Most of the information that Contractor Compass needs will come from everyday bookkeeping and accounting work. There are some fields within the software that were added or modified for use with Contractor Compass.

How the System Works

The dashboards are Microsoft Excel files. These files contain code that queries Total Office Manager, gathers data, and inserts that data into various tabs within the dashboard. That data is used to populate a wide variety of worksheets that include charts, graphs, reports, analysis, KPI’s ratios, and much more. Users can modify or add information as they would to any other Excel spreadsheet file.

Why We Use Microsoft Excel

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.

Double Check Certain Fields

The following is a list of places that contain important software features that we highly recommend you use. These forms contain a field labeled “What Best Describes”. Please make sure that you are using those fields. See Q&A below for more information.

What Best Describes This

Locate each “What Best Describes This” selection list and select the appropriate value.

Company > Lists > Item Categories List
Company > Lists > Department List
Company > Lists > Marketing Type List
Company > Lists > Log Topics List
Company > Lists > Work Order Type List
Banking > Chart of Accounts List (see below for additional notes)
Customers > Sales Opportunity Manager > Product List
Customers > Lists > Customer Type (to distinguish contact, prospect, customer, and job)

Other Selections to Check

There are other selection lists that you should update.

Banking > Chart of Accounts List. For all Expense types, select an Overhead Allocation Type. We highly recommend that you select “Labor” for all Chart of Accounts Expense types. That is the type used by the dashboard system and the method taught and recommended by EGIA. Labor is the default selection when you turn this feature on in Preferences > Chart of Accounts > Company Preferences.
Banking > Chart of Accounts List. For all Expense types, select Fixed or Variable.
Company > Sales Opportunity List > Sales Opportunity. Be sure to use the various controls including Department, Financing Activity, Stages, Estimate, Probability, Stage, and more.
Employee List > Employee > Payroll Setup > General tab. Be sure to set the Employment Status, Employee is Billable, and Available Person Days.
Employee List > Employee > Address Info tab > Reports To selection box. This would be the employee’s direct supervisor to who they directly report to (their manager).
Lists > Invoice Items > Accounting tab. On Inventory, Non-Inventory, and Serialized types, look for the “This item is Considered an Accessory” checkbox. This also used for IAQ.
Lists > Invoice Items > Accounting tab. On a Service Item type, look for the “Labor Only (used for labor only), and “This item is used as a Diagnostic Fee” checkboxes.
Lists > Invoice Items > Accounting tab. On the Other Charge type, look for the “This item is ONLY Used For” selection box.

Use These Features Fully

Departments and Marketing Types

Most forms have a department and marketing selection. This is a very important field. This information is used extensively throughout the accounting, reporting, and dashboard system. Please be sure that you have set up your departments and marketing type lists and that you are always selecting them when possible. Our dashboard system will be using this information too.

Timesheets

This feature will be used heavily. The dashboards will be looking at the labor time for employees and comparing that time to labor time on invoices. Be sure to add a department to each line entry. We are working on a system that will look at vehicle mileage entries. Add any other information that you can. We may use any of this information in future releases.

Work Orders

Work order information is used extensively by the dashboards. Please be sure that you are using this feature to its fullest extent. Specifically, you should be using the work order call-back feature. You will want to create invoices from work orders. In the invoice form, use the Menu > Assign Work Order feature. That allows you to associate one or many work orders and/or invoices to one another.

Asset Manager

Go to Company > Asset Manager. This is where you maintain a list of company property. Enter items such as trucks, equipment, tools, computers, and anything else that you wish to track or keep records for. When you are entering bills, credit card charges, writing checks, etc., you will notice an asset selection box. Use that box to associate the expense with the asset. An example of this could be vehicle maintenance or gasoline. By doing this, you will not only know the cost to acquire that vehicle but also the cost to own it. Our dashboard system will be using this information too.

Budgets

The dashboards will make use of budget information. You will find that feature here.

Company > Budget List

Be sure to create a budget for the current year. Consider one budget per department or division. Double-check to make sure you do not have two or more budgets for the same year. If you need help with this feature, please check the help topic. https://www.aptora.com/help/budgets/

Required Software

You will need Microsoft Excel 2019 or 365®. Prior versions might work fine but we do not program for or test versions prior to these.

Enable Macros

Contractor Compass dashboards make extensive use of macros (VBA code). You will need to “enable macros” for this system to work. Excel normally considers Excel files that include macros and VBA code to be a potential danger. You may need to takes other steps to “Assure” Excel that the files are safe.

Trusted File Location

You should also let Excel know that you trust the folder that contains your dashboard files. This done by opening Excel and going to File > Options > Trust Center > Trust Center Settings > Trusted Locations. Navigate to your Total Office Manager installation. It is typically located here (this might be different on your computer): C:\ProgramData\Aptora\J530\

Scope of Support and Training

Technical Support

Our support technicians will be very happy to explain where a certain dashboard value comes from in Total Office Manager or how cells get their information. They will also show you the formulas that make up that value. They will help determine if a value or formula is working the way we intended. If you are having trouble opening the dashboard from Total Office Manager, they will troubleshoot that problem.

Technical support will not be able to answer questions that are best addressed by an accountant or business consultant. They will not be able to offer you advice on what the various numbers mean and where your company should compare. Technical support will not be able to assist with modifying a dashboard, creating a new dashboard, modifying fields/formulas, or working in Excel.

Training Department

Our trainers will be happy to help explain the “what, why, and how” on any piece of dashboard data. They will show you specifically where in Total Office Manager the information is coming from and give you suggestions on what information might be most important to your business. They will also be able to give detailed information on how to properly set up Total Office Manager so that you can take full advantage of the Contractor Compass dashboards. If you are building or modifying a dashboard, our trainers will show you where you might find that information in Total Office Manager. They will not be able to help you with Excel, VBA, queries, or any type of database work or programming.

General Q&A

Q: What type of company was this dashboard built for?

A: The dashboard system was built for companies that primarily perform residential and commercial HVAC work. They may also do plumbing and electrical work. The main part of the dashboard system that relates to this focus is the division and department list. This list is the official Divion and Department list for EGIA Contractor University members. Otherwise, the dashboard system is relevant to almost any contracting business.

Q: Why are there selection boxes called “What Best Describes”?

A: We are looking for certain information in the database and we do not always know what our users will call that information. An example of this is can be found in the Contact Log. We want to count injuries that required OSHA reporting. Since users might call this something different, or have two or more names for the same thing, we offer the ability to mark Contact Log Topics as “OSHA Reportable Injuries”. There are other options under that list.

Q: Can we add to the list of “What Best Describes”?

A: No. Those are what we call hard-coded and cannot be changed in any way. We need those lists to contain an expected set of options that we can search for.

Q: Do we need a budget to use the dashboard feature?

A: No. The budget information will simply be blank. The same is true for any other feature that you do not use.

Q: Our dashboard seems to be missing a lot of information. How do we find out what information needs to be added for it to work fully?

A: The dashboards rely on a wide variety of data points that are sourced from the various forms throughout the software. If you are not using work orders, entering sales leads, filling our estimates, selecting departments, entering bills, tracking inventory, and so forth, the dashboards will information it needs to make calculations.

Q: Will the dashboards match various related reports in Total Office Manager?

A: Not necessarily. The dashboard might draw information that is different than a report, even if that report seems to be reporting the same information. The dashboards rely on numerous selection boxes, checkboxes, and other things to gather information. It may also perform different calculations. Rounding might cause differences too. Dashboards also rely on the fact that certain features be used in certain ways (outlined in the article).

Q: Why do our dashboards include information labeled “Uncategorized” and “Non-Departmentalized”?

A: That information is coming from transactions that do not include a department selection. For example, you might have entered a bill or an invoice, and no department was selected in the header.

Q: How do we know where all the dashboard information is coming from in Total Office Manager?

A: Much of the information will be fairly obvious. Most of the information is normal accounting information derived from transactions. We have added notes to the various fields in Excel. When a note is available, you will sell a red triangle in the upper right side of the cell. When you hover over it, a note should pop up. We have also included worksheets that include tips and other information. There should also be links to help topics and other resources.

Q: We have not been using some of the features that Contractor Compass seems to need. What can we do about changing our historical records?

A: You should be able to go back and update many of your records. For example, you can open invoices, bills, credit card charges, sales opportunities, and so forth. You can select departments and Save & Close. You may need to change or turn off your Cutoff Date. Some records have what we typically call a “Batch Update” feature. This feature will help but it is not available for all transactions or records. There is no fast way to get caught up.

Q: What other dashboards are you planning or creating?

A: We already have plans to create boards for technicians, sales, marketing, financial planning, and HR. We will create others based on suggestions. We will be adding features to Total Office Manager to make the dashboards more robust and easier to work with. For example, we will be working to improve the budgeting feature so that budgets are easier and faster to create and maintain. There will be a nominal fee for additional dashboards.

Q: Can we modify existing dashboards?

A: Yes, but please be very careful about doing so. We decided to create our system using Excel so that advanced users could do almost any customizing they wanted to. It would be very easy to break what we have created. Be sure to back up your Excel file often. We can always email you another Excel file to replace yours with. Important: Our normal software update process might overwrite your Excel dashboard file. Please keep copies.

Q: Can we create our own dashboards?

A: Yes. Please see the following Help Topic: How to Install and Use Contractor Compass. It is linked below.

Q: How does the dashboard (Excel file) get information from Total Office Manager?

A: The dashboard Excel file must be launched from Total Office Manager. When it opens, it “logs into” Total Office Manager with the user’s credentials. It then runs through a series of queries that pull information from your database. These queries are stored inside the database and they are called from the Excel file using VBA code.

Q: Can we view and modify the VBA code?

A: Yes. You will need some VBA skills. The code will be found under the Developer’s button. We have provided robust comments inside the code to help a person knowledgeable with VBA (Visual Basic for Applications). You can add or modify the VBA code to run other queries already stored in your Total Office Manager database or even add your own.

Q: What type of support do you offer to help us modify the MS Excel dashboard file?

A: The Aptora technical support department will not be able to help you with any work in Microsoft Excel. Our pay-as-you-go training and consulting services will help you with some of this work. We do this by appointment. Please contact our Help Desk for more information and scheduling.

Aptora will not provide any type of support related to VBA coding, SQL, or any work inside your Total Office Manager database.

Troubleshooting

Issue: The Diagnostic Only information is zero or does not change. This could be any field in the Excel file.

Open the item that you use for Diagnostic Fees and open the Accounting tab. Be sure the “This Service Item is used as a Diagnostic Fee” box is checked. If you do not see this option, the item type is not correct. The item type must be “Service”. Repeat this process for any and all Service Items used for a diagnostic fee.
Double check the value in the Invoice > Department (in the header, the line item does not matter). Make sure this is the correct Department and that the Department is setup correctly.
Make sure that there are no other items on the invoice. The Diagnostic Fee must be the ONLY item.

Note: This issue could involve any field in the Excel file. The steps are similar. Check the various “What Best Describes” selection and other options and settings.

Issue: The numbers do not look correct and they are the same each time I open the dashboard Excel file.

Be sure you are not opening the file directly from Excel. The Excel file must only be opened with the Contractor Compass software program.
If there is a Enable Content button, click it and try again. Under Add-ins, click Refresh. If you do not see this button or if it appears to not work, save and close the Excel file and try the Contractor Compass software program again.
Contractor Compass uses Macros and they are a requirement. In Microsoft Excel, all macros are disabled by default. Click File > Options > Trust Center. Please follow the instructions on this link to enable macros: https://www.ablebits.com/office-addins-blog/2020/03/11/enable-disable-macros-excel/

Dashboards

Field Technician Scoreboard

You Must Setup and Use Total Office Manager Properly

Preparing to use the Technician Scoreboard

Minimum Microsoft Excel Versions Required

Using the Technician Scoreboard

Reload and Refresh Dashboard Data

Automatic Reload and Refresh

Presentation Mode

Using Filters

Always Open from Contractor Compass®

Understanding the Scoreboard Information

Popup Notes on Columns

Frequently Asked Question about the Scoreboard

Q: Why do some of the Scoreboard columns have a light red boarder around them?

Q: Why do I see a technician called “UnAssigned” on the Scoreboard?

Q: If two or more technicians are on an invoice, how is the invoice divided out among those technicians?

Q: Why do some numbers use a decimal point when that number always has zeros at the end? Example: Number of Callbacks = 4.00.

Q: We have certain information that seems to be missing or is correct. How do we resolve this?

Q: When I click on a cell in the Scoreboard sheet, I do not see a formula. Why?

Q: How is Gross Profit Per Person Day Calculated?

Tips

Known Issues

Editing the Excel Workbook

No Support for Excel or VBA

How the Scoreboard is Populated

Related Help Topics

Preparing Total Office Manager for Contractor Compass™

How to Install and Use Contractor Compass

Financial Ratios. Their Meaning and Usage.

Scoreboard Workbook Structure and Operation (advanced info)

Intended Audience

How it Works

How Formulas are Used

Viewing the Scoreboard Formulas

Miscellaneous Information

Dept-Data Worksheet – Column Definitions

Key Points to Remember

CntType

CntValue

CntMonth

CntYear

Department

Industry

Division

DeptType

Tech-Last-First

Technician

TechAlias

TranDate

TranSubtotal

TranTax

TranTotal

TranBalance

TechHours

BilledHours

TechLabor

COGS-NonLabor

Discount$

SADiscount$

SASold

SASold$

DiagOnly?

DiagOnly$

IAQ-Acc Sold

IAQ-Acc Sold$

EquipSold

EquipSold$

PercOfHours

PercOfIncome

ManDays

TechID

FiscalMonth

FiscalStart

FiscalEnd

Contractor Compass Introduction

System Compatibility

Minimum Microsoft Excel Versions Required

How the System Works

Why We Use Microsoft Excel