Dashboard Archives

Preparing Total Office Manager for Contractor Compass™

Contractor Compass™ – Super Dashboard

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.

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.

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.

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/

Dashboard