All-In-One Field Service Management Software by Aptora

Reports

/*#main .fusion-row { max-width: 100% !important;}*/ /*.fusion-header-wrapper .fusion-row { max-width: 100%; }*/ /*#content { width: 75%; float: right; text-align: left; } body.has-sidebar #content { width: calc(100% - 23% - 80px); } */

AR Aging Report Not Matching Balance Sheet

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.

  1. The AR Aging reports were not run with the same date parameters as the Balance Sheet.
  2. Transactions affecting Accounts Receivable are out of balance in the General Journal.
  3. Transactions affecting Accounts Receivable are missing from the General Journal.
  4. Credit application dates are not in sync with General Journal transaction dates.
  5. Accounts Receivable account is improperly being used in Invoice Item setup.
  6. 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.

AR Aging Report Not Matching Balance Sheet Check
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 Report for A/R Aging Report Not Matching Balance Sheet
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.

Invoice Item Setup - Locate AR Aging Report Information
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:

  1. 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.
  2. 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.

Related Content to AR Aging Report

A/R Aging Detail Report

A/R Aging Summary Report

Invoice/Sale/Credit/Estimate List CDV (report)

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.
Cust:Job Display Name Invoice/Sale/Credit/Estimate Associated Transaction Customer:Job Full Name
Tran Bill To Line 1 Invoice/Sale/Credit/Estimate Bill To Line 1
Tran Bill To Attn Invoice/Sale/Credit/Estimate Bill To Attn
Tran Bill To Address Invoice/Sale/Credit/Estimate Bill To Address
Tran Bill To Address2 Invoice/Sale/Credit/Estimate Bill To Address2
Tran Bill To City Invoice/Sale/Credit/Estimate Bill To City
Tran Bill To State Invoice/Sale/Credit/Estimate Bill To State/Province
Tran Bill To Zip Invoice/Sale/Credit/Estimate Bill To Zip/Postal Code
Tran Bill To County Invoice/Sale/Credit/Estimate Bill To County
Tran Work Line 1 Invoice/Sale/Credit/Estimate Work Address Line 1
Tran Work Attn Invoice/Sale/Credit/Estimate Work Address Attn
Tran Work Address Invoice/Sale/Credit/Estimate Work Address
Tran Work Address2 Invoice/Sale/Credit/Estimate Work Address2
Tran Work City Invoice/Sale/Credit/Estimate Work Address City
Tran Work State Invoice/Sale/Credit/Estimate Work Address State/Province
Tran Work Zip Invoice/Sale/Credit/Estimate Work Address Zip/Postal
Tran Work County Invoice/Sale/Credit/Estimate Work Address County
AR Account Invoice/Sale/Credit/Estimate Invoice/Credit Accounts Receivable COA Affected
Account Number Customer:Job Payment Tab Account Number
Sales Rep Invoice/Sale/Credit/Estimate Associated Transaction Sales Rep Name
Sales Rep Alias Invoice/Sale/Credit/Estimate Associated Transaction Sales Rep Alias
Sales Rep First Name Invoice/Sale/Credit/Estimate Associated Transaction Sales Rep First Name
Sales Rep Last Name Invoice/Sale/Credit/Estimate Associated Transaction Sales Rep Last Name
TranID Invoice/Sale/Credit/Estimate Table ID of Transaction
CustID Invoice/Sale/Credit/Estimate Table ID of Customer:Job
Generated Date Custom Data Viewer **** Date the CDV data was generated
Generated Time Custom Data Viewer **** Time the CDV data was generated

Employee Efficiency CDV

Introduction to the Employee Efficiency CDV

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

  1. From the main menu, click Reports | Custom Data Views
Custom Data View File Path
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
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:

Employee Efficiency CDV
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
Cust:Job Name Invoice/Sale/Credit/Estimate Customer:Job Name
Cust:Job Display Name Invoice/Sale/Credit/Estimate Customer:Job Full Name
Sales Rep Invoice/Sale/Credit/Estimate Tran Sales Rep
Sales Rep Alias Invoice/Sale/Credit/Estimate Associated Customer:Job Sales Rep Alias
Sales Rep First Name Invoice/Sale/Credit/Estimate Associated Customer:Job Sales Rep First Name
Sales Rep Last Name Invoice/Sale/Credit/Estimate Associated Customer:Job Sales Rep Last Name
Tran Department Display Invoice/Sale/Credit/Estimate Tran Header Dept Full Name
Tran Department Name Invoice/Sale/Credit/Estimate Tran Header Dept Name
Tran Department Alias Invoice/Sale/Credit/Estimate Tran Header Dept Alias
Tran User Created Invoice/Sale/Credit/Estimate Transaction Security Label
Tran Date Created Invoice/Sale/Credit/Estimate Transaction Security Label
Tran User Revised Invoice/Sale/Credit/Estimate Transaction Security Label
Tran Date Revised Invoice/Sale/Credit/Estimate Transaction Security Label
Bill To Address Invoice/Sale/Credit/Estimate Bill To Address
Bill To Address2 Invoice/Sale/Credit/Estimate Bill To Address2
Bill To City/State/Zip Invoice/Sale/Credit/Estimate Bill To City, State, Zip
Bill To County Invoice/Sale/Credit/Estimate Bill To County
Bill To Country Invoice/Sale/Credit/Estimate Bill To Country
Bill To Subdivision Invoice/Sale/Credit/Estimate Bill To Subdivision
Work Address Invoice/Sale/Credit/Estimate Work Address
Work Address2 Invoice/Sale/Credit/Estimate Work Address2
Work City/State/Zip Invoice/Sale/Credit/Estimate Work City, State, Zip
Work County Invoice/Sale/Credit/Estimate Work County
Work Country Invoice/Sale/Credit/Estimate Work Country
Work Subdivision Invoice/Sale/Credit/Estimate Work Subdivision
TranID Invoice/Sale/Credit/Estimate Table ID of Transaction
EmployeeID Employee Table ID of Employee
CustID Customer:Job Table ID of Customer:Job
Generated Date Custom Data Viewer **** Date the CDV data was generated
Generated Time Custom Data Viewer **** Time the CDV data was generated

Related Content

Invoice/Sale/Credit/Estimate List CDV

Inventory By Warehouse Bins CDV

Inventory Cost Review CDV

Inventory Cost Review – Custom Data View

Reports Included: Yes

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

PO Reimbursables CDV

Type: Custom Data View

Reports Included: No

Description: List of purchase order items with a customer selected on the line entries and associated purchasing and invoicing transactions. This CDV is good for making sure the items you have purchased for a specific job have been received and subsequently invoiced.  The use of the reimbursables feature on invoices is required to show the invoiced transactions.

Drill Down: Yes, opens purchase order.

Columns and data for the PO Reimbursables Custom Data View are outlined below:

 

Column Name Form Field Notes
Item Name Purchase Order Line Item Name
PO Item Qty Purchase Order Line Item Qty
PO Item Cost Purchase Order Line Item Rate
PO Item Amount Purchase Order Line Item Amount
Item Manufacturer Item Equipment Manufacturer
Item Equip Type Item Equipment Type
Item Type Item Type
PO Vendor Name Purchase Order Vendor
PO Number Purchase Order Number
PO Date Purchase Order Date
PO Expected Date Purchase Order Expected Date
PO Memo Purchase Order Memo
PO Closed? Purchase Order Header Closed
PO Vendor Address Purchase Order Vendor Address
PO Vendor Address2 Purchase Order Vendor Address 2
PO Vendor City/State/Zip Purchase Order Vendor City, State, Zip
PO Ship To Name Purchase Order Ship To Name
PO Ship To Address Purchase Order Ship To Address
PO Ship To Address2 Purchase Order Ship To Address
PO Ship To City/State/Zip Purchase Order Ship To City, State, Zip
Customer:Job Name Purchase Order Line Item Customer:Job Name
Customer:Job Display Purchase Order Line Item Customer:Job Full Name
PO Item Description Purchase Order Line Item Description
PO Line Item Order Purchase Order Line Item Order
PO Qty Rcvd Purchase Order Line Item Received Qty
PO Item Closed? Purchase Order Line Item Closed
Receipt Date Item Receipt/ Bill / Check /Credit Card Charge Receipt Date
Receipt Type Item Receipt/ Bill / Check /Credit Card Charge Receipt Transaction Type
Receipt Ref # Item Receipt/ Bill / Check /Credit Card Charge Receipt Reference #
Receipt Qty item Receipt/ Bill / Check /Credit Card Charge Receipt Qty
Receipt Warehouse Item Receipt/ Bill / Check /Credit Card Charge Line Warehouse
Receipt Serial Number Item Receipt/ Bill / Check /Credit Card Charge Reference Number
PO Item Department Name Purchase Order Line Item Dept Name
PO Item Department Display Purchase Order Line Item Dept Full Name
Reimb? Purchase Order **** This field will be Yes if the line has been place on an invoice or sale using the Reimbursables option from an invoice or sale.
Invoiced? Invoice or Sale **** This field will be Yes if the PO Line Item has been invoiced.
Invoice Date Invoice or Sale Associated Invoice/Sale Date
Invoice Number Invoice or Sale Associated Invoice/Sale Number
PO Item WO Number Purchase Order Associated WO Number
PO Item WO Status Purchase Order Associated WO Status
PO Item WO Date Purchase Order Associated WO Date
POItemID Purchase Order Table ID for PO Item Line
ItemID Item Table ID for Item
POID Purchase Order Table ID for Purchase Order
CustID Customer:Job Table ID for Customer
DeptID Department Table ID form Department
WOID Work Order Table ID for Work Order
BillID Item Receipt/ Bill / Check /Credit Card Charge Table ID for Transaction
InvoiceID Invoice or Sale Tabble ID for Transaction
VendorID Vendor Table ID for Vendor

Customer Equipment CDV Report

Type: Custom Data View

Reports Included: No

Description: List of customer equipment with details for the last work order completed, number of pending work orders, and customer demographics.

Drill Down: Yes, opens the equipment record.

Columns and data for the Customer Equipment Custom Data View are outlined below:

Column NameFormFieldNotes
Customer:Job NameCustomer:JobName 
Department NameCustomer:Job Additional Info TabDepartment Name 
Department Full NameCustomer:Job Additional Info TabAssociated Department Full Name 
Department AliasCustomer:Job Additional Info TabAssociated Department Alias 
    
Equipment TypeEquipmentType 
Equipment ManufacturerEquipmentManufacturer 
ModelEquipmentModel 
Serial NumberEquipmentSerial Number 
Equipment DescriptionEquipmentCalculated FieldEstimated Cost minus Cost to Date
Equipment LocationEquipmentCalculated FieldCost to Date divided by Estimated Cost
Install DateEquipment  
Install Age DaysEquipmentCalculated FieldNumber of days from the Install Date to the date the CDV was run.
Install Age MonthsEquipmentCalculated FieldNumber of months from the Install Date to the date the CDV was run.
Install Age YearsEquipmentCalculated FieldNumber of years from the Install Date to the date the CDV was run.
Mfg YearEquipmentYear Mfr. 
Mfg Age YearsEquipmentCalculated FieldNumber of years from the Year Mfr. to the date the CDV was run.
Equipment NotesEquipmentNotesNotes are found under the Menu option.
General Our WarrantyEquipmentOur Warranty General 
General MFG WarrantyEquipmentManufacturer Warranty  General 
Our Labor WarrantyEquipmentOur Warranty Labor 
MFG Labor WarrantyEquipmentManufacturer Warranty  Labor 
Our Parts WarrantyEquipmentOur Warranty Parts 
MFG Parts WarrantyEquipmentManufacturer Warranty  Parts 
In ServiceEquipmentIn Service 
Equipment ParentEquipmentParent Equipment 
Service AgreementService AgreementTypeThe service agreement information is based on the equipment being assigned to the service agreement.
SA Term BeginsService AgreementTerm Begins 
SA Term EndsService AgreementTerm Ends 
SA NumberService AgreementNumber 
Last Completed Work OrderWork Order : General TabDate 
Last Work Order DetailsWork Order******From the last work order completed, a copy of the date, technician’s name, brief description, detailed description, private notes, and technician notes.
Last Work Order TypeWork Order : Additional Info TabType 
Last Work Order DeptWork Order : Additional Info TabDepartment 
# Pending WOWork OrderCalculated FieldThe number of work orders for the customer which have not been completed or cancelled.
Customer:Job DisplayCustomer:JobDisplay 
Billing ParentCustomer:JobParent Name of child accountThis is the parent name of the record.  When the customer is the parent, this field will be the same as the customer name.
Company NameCustomer:Job : Address TabCompany 
DBACustomer:Job : Address TabDBA 
Bill To AddressCustomer:Job : Address TabBill To Address 
Bill To Address2Customer:Job : Address TabBill To Address2 
Bill To City/State/ZipCustomer:Job : Address TabBill To City, State Zip 
Bill To CountyCustomer:Job : Address TabBill To County 
Bill To CountryCustomer:Job : Address TabBill To Country 
Bill To SubdivisionCustomer:Job : Address TabBill To Subdivision 
Work AddressCustomer:Job : Address TabWork  Address 
Work Address2Customer:Job : Address TabWork Address2 
Work City/State/ZipCustomer:Job : Address TabWork City, State, Zip 
Work CountyCustomer:Job : Address TabWork County 
Work CountryCustomer:Job : Address TabWork Country 
Work SubdivisionCustomer:Job : Address TabWork Subdivision 
SalutationCustomer:Job : Address TabSalutation 
First NameCustomer:Job : Address TabFirst Name 
Middle InitialCustomer:Job : Address TabMiddle Initial 
Last NameCustomer:Job : Address TabLast Name 
ContactCustomer:Job : Address TabContact 
PhoneCustomer:Job : Address TabPhone/Ext 
Phone ExtCustomer:Job : Address TabPhone/Ext 
Cell PhoneCustomer:Job : Address TabCell Phone 
FaxCustomer:Job : Address TabFax 
PagerCustomer:Job : Address TabPager 
PinCustomer:Job : Address TabPin 
Alt PhoneCustomer:Job : Address TabAlt. Phone 
Alt ContactCustomer:Job : Address TabAlt. Contact 
EmailCustomer:Job : Address TabEmail 
WebsiteCustomer:Job : Address TabWebsite 
OwnerCustomer:Job : Address TabOwner 
Map CodeCustomer:Job : Address TabMap Code 
Account NumberCustomer:Job : Payment TabAccount Number 
Sales Rep AliasCustomer:Job :Sales Rep Alias/Name if Vendor 
Additional Info Tab
Sales Rep NameCustomer:Job :Sales Rep Name 
Additional Info Tab
EquipmentIDEquipmentTable ID of Equipment 
ParentEquipIDEquipmentTable ID of Parent Equipment 
CustomerIDCustomer:JobTable ID of Customer:Job 
ParentIDCustomer:JobTable ID of Parent Record 

Work Order Costing CDV

Type: Custom Data View

Reports Included: Yes

Description: List of Work Orders and the Invoices, Sales, Estimates, and Credits they are assigned to with costing and sales totals analysis.

Drill Down: Yes, opens the work order

Columns and data for the Work Order Costing Custom Data View are outlined below:

Column Name

Form

Field

Notes

Technician Assigned

Work Order : General Tab

Assigned To

 

Employee Alias

Work Order : General Tab

Assigned To

 

Customer Name

Work Order

Customer:Job

 

Customer Display Name

Work Order

Customer:Job

 

WO Date

Work Order : General Tab

Date

 

WO Number

Work Order

Work Order #

 

WO Duration

Work Order : General Tab

Duration in Minutes

 

WO Start Time

Work Order : General Tab

Start Time

 

WO End Time

Work Order : General Tab

Calculated Field

Start Time times Duration in Minutes

WO Status

Work Order

Status

 

WO Type

Work Order : General Tab

Work Order Type

 

WO Department

Work Order : Additional Info Tab

Department

 

WO Full Department

Work Order : Additional Info Tab

Associated Department Full Name

 

WO Dept Alias

Work Order : Additional Info Tab

Associated Department Alias

 

WO Hours

Timesheets

Calculated Field

Sum of hours for technician where the Work Order is assigned on timesheet entries

WO Labor Cost

Timesheets

Calculated Field

Sum of timesheet hours times current 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.

WO Labor Liability

Timesheets

Calculated Field

Sum of timesheet hours times current liability 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.

WO Total Labor Cost

Timesheets

Calculated Field

Sum of WO Labor Cost and WO Labor Liability.

Labor Price Total

Timesheets

Calculated Field

Sum of Amount for items that are service types with the Used for Labor Only option checked within the item setup Accounting Tab.

WO Parts

Work Order : Items Tab

Calculated Field

Sum of Items Estimated Cost for all items that are type Inventory Part and Non-inventory Part.

WO Equipment

Work Order : Items Tab

Calculated Field

Sum of Items Estimated Cost for all items that are type Serialized.

WO Parts Total

Work Order : Items Tab

Calculated Field

Sum of WO Parts plus WO Equipment.

WO Other

Work Order : Items Tab

Calculated Field

Sum of Items Estimated Cost for all items that are type Other Charge, Discount, and Service that are not marked as Used for Labor Only.

WO Total Cost

Work Order : Items Tab

Calculated Field

Sum of WO Labor Cost plus WO Labor Liability plus WO Parts plus WO Equipment plus WO Other.

WO Total

Work Order : Items Tab

Calculated Field

Sum of Amount for all parts.

WO GP

Work Order : Items Tab

Calculated Field

WO Total Cost minus WO Total.

WO GPM%

Work Order : Items Tab

Calculated Field

(WO Total Cost Minus WO Total) divided by WO Total times 100.

WO Brief Description

Work Order : General Tab

Brief Description (formerly Description)

 

WO Detailed Description

Work Order : General Tab

Detailed Description (formerly Directions/Special Instructions)

 

WO Private Notes

Work Order : Notes Tab

Private Notes

 

WO Technician Notes

Work Order : Notes Tab

Technician’s Notes (formerly Notes)

 

Tran Type

Invoice/Sale/Credit/Estimate

Type located in Title Bar

 

Tran Date

Invoice/Sale/Credit/Estimate

Date

 

Tran Number

Invoice/Sale/Credit/Estimate

Number

 

Tran Total

Invoice/Sale/Credit/Estimate

Total

 

Invoice Tax Total

Invoice/Sale/Credit/Estimate

Tax Total

 

Invoice Labor Cost

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.

Invoice Material Cost

Invoice/Sale/Credit/Estimate

Calculated Field

Sum of Total Cost for all items that are type Inventory Part and Non-inventory Part.

Invoice Equipment Cost

Invoice/Sale/Credit/Estimate

Calculated Field

Sum of Total Cost for all items that are type Serialized.

Invoice Other Cost

Invoice/Sale/Credit/Estimate

Calculated Field

Sum of Total Cost for all items that are type Other Charge, Discount, and Service that are not marked as Used for Labor Only.

Invoice Total Cost

Invoice/Sale/Credit/Estimate

Calculated Field

Sum of Invoice Labor Cost plus Invoice Material Cost plus Invoice Equipment Cost plus Invoice Other Cost.

Invoice GP

Invoice/Sale/Credit/Estimate

Calculated Field

Invoice Total minus Invoice Total Cost.

Invoice GPM%

Invoice/Sale/Credit/Estimate

Calculated Field

(Invoice Total minus Invoice Total Cost) divided by Invoice Total times 100.

Cost Difference

Invoice/Sale/Credit/Estimate

Calculated Field

Invoice Total minus WO Total Cost.

Revenue Difference

Invoice/Sale/Credit/Estimate

Calculated Field

Invoice Total minus WO Total.

Invoice Details

Invoice/Sale/Credit/Estimate

Details

Details

Original Tech Name

Work Order : General Tab

Callback Label Employee Name

 

Company Name

Work Order

Associated Customer:Job Company

 

Customer Contact

Work Order : Address Tab

Contact

 

Phone

Work Order : Address Tab

Phone/Ext

 

Phone Ext

Work Order : Address Tab

Phone/Ext

 

Cell Phone

Work Order : Address Tab

Cell Phone

 

Fax

Work Order

Associated Customer:Job Fax

 

Alt Phone

Work Order : Address Tab

Alt. Phone

 

Alt Contact

Work Order : Address Tab

Alt. Contact

 

Email

Work Order : Address Tab

Email

 

Address

Work Order : Address Tab

Bill To Address

 

Address2

Work Order : Address Tab

Bill To Address2

 

City

Work Order : Address Tab

Bill To City

 

State

Work Order : Address Tab

Bill To State

 

Zip

Work Order : Address Tab

Bill Zip

 

Work Address

Work Order : Address Tab

Work  Address

 

Work Address2

Work Order : Address Tab

Work Address2

 

Work City

Work Order : Address Tab

Work City

 

Work State

Work Order : Address Tab

Work Address State/Province

 

Work Zip

Work Order : Address Tab

Work Address Zip/Postal

 

Tran Sales Rep Alias

Invoice/Sale/Credit/Estimate

Associated Customer:Job Sales Rep Alias

 

Tran Sales Rep First Name

Invoice/Sale/Credit/Estimate

Associated Customer:Job Sales Rep First Name

 

Tran Sales Rep Last Name

Invoice/Sale/Credit/Estimate

Associated Customer:Job Sales Rep Last Name

 

Cust:Job Sales Rep Alias

Work Order

Associated Transaction Sales Rep Alias

 

Cust:Job Sales Rep First Name

Work Order

Associated Transaction Sales Rep First Name

 

Cust:Job Sales Rep Last Name

Work Order

Associated Transaction Sales Rep Last Name

 

WO User Created

Work Order

WO Security Label

 

WO Date Created

Work Order

WO Security Label

 

WO User Revised

Work Order

WO Security Label

 

WO Date Revised

Work Order

WO Security Label

 

Tran User Created

Invoice/Sale/Credit/Estimate

Transaction Security Label

 

Tran Date Created

Invoice/Sale/Credit/Estimate

Transaction Security Label

 

Tran User Revised

Invoice/Sale/Credit/Estimate

Transaction Security Label

 

Tran Date Revised

Invoice/Sale/Credit/Estimate

Transaction Security Label

 

WorkOrderID

Work Order

Table ID of Work Order

 

EmployeeID

Work Order

Table ID of Assigned To Employee

 

CustJobsID

Work Order

Table ID of Customer:Job

 

DeptID

Work Order

Table ID of Department

 

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.

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

Other Selections to Check

There are other selection lists that you should update.

  1. 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.
  2. Banking > Chart of Accounts List. For all Expense types, select Fixed or Variable.
  3. Company > Sales Opportunity List > Sales Opportunity. Be sure to use the various controls including Department, Financing Activity, Stages, Estimate, Probability, Stage, and more.
  4. Employee List > Employee > Payroll Setup > General tab. Be sure to set the Employment Status, Employee is Billable, and Available Person Days.
  5. 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).
  6. 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.
  7. 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.
  8. 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.

  1. 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.
  2. 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.
  3. 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.

  1. Be sure you are not opening the file directly from Excel. The Excel file must only be opened with the Contractor Compass software program.
  2. 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.
  3. 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/

Related Content

Recommended Department Setup

Creating and Maintaining Budgets

How to Install and Use Contractor Compass

Financial Ratios. Their Meaning and Usage.

Inventory Valuation Methods

Introduction to Inventory Costing

Inventory accounting may sound like a huge undertaking but in reality, it is quite straightforward and easy to understand. You start with the inventory you have on hand. No matter when you sell a product, the value of your inventory will remain constant based on accepted and rational methods of inventory accounting. Those methods include perpetual average cost, specific identification method (the two used by Total Office Manager), weighted average, first in/first out, and last in/first out.

Perpetual Average Cost

This method is used by the Total Office Manager when the “Inventory Part” invoice item type is used.

Under the perpetual system, the Inventory account is constantly (or perpetually) changing. When a retailer purchases merchandise, the costs are debited to its Inventory account; when the retailer sells the merchandise to its customers the Inventory account is credited and the Cost of Goods Sold account is debited for the cost of the goods sold. Rather than staying dormant as it does with the periodic method, the Inventory account balance under the perpetual average is changing whenever an increase in quantity occurs.

Under the perpetual system, two sets of entries are made whenever merchandise is sold: (1) the sales amount is debited to Accounts Receivable or Cash and is credited to Sales, and (2) the cost of the merchandise sold is debited to Cost of Goods Sold and is credited to Inventory.

Under the perpetual system, “average” means the average cost of the items in inventory as of the date of the sale. This average cost is multiplied by the number of units sold and is removed from the Inventory account and debited to the Cost of Goods Sold account. We use the average as of the time of the sale because this is a perpetual method.

Average Cost History

Total Office Manager tracks the average cost by date. When you backdate or postdate an invoice, it will use the average cost as of that date. You can see what the average cost of an item was at any given time. Open the Invoice Item List | Right Click on that item | click Item History | go to the Avg. Cost History tab.  If an item has no average cost, the estimated cost of the item will be used on invoices and sales to record the cost to debit to Cost of Goods Sold and credit from the inventory asset account.  Once the item is purchased to bring the quantity positive, any difference in the cost removed from the asset account will be reconciled at the time of purchase.

Specific Identification Method (AKA: Exact Cost)

This method is used by Total Office Manager when the “Serialized” invoice item type is used.

The specific identification method of inventory costing attaches the actual cost to an identifiable unit of product. This is accomplished by looking at the serial number of the item.

This method is easy to manage and 100% accurate. We highly recommend that you used serialized items because they are typically a large portion of your direct costs. Serialized items will improve your job costing and other financial reporting.

Inventory Tips

  1. When you buy a “Non-inventory Part” invoice item type, it does not go on the balance sheet as an inventory asset. It immediately becomes a cost of goods sold or it reduces the income account that it is associated with. Use this type of item sparingly.
  2. Purchase orders do not affect inventory. It is when you enter an item receipt or a bill, that “Inventory Part” and “Serialized” item types will affect your balance sheet. Note: There are other activities where these items will affect your balance sheet. Purchase transactions include checks and credit card charges.  You can receive against a purchase order on these transactions as well.
  3. We recommend you use purchase orders. When the items arrive, enter an item receipt from that purchase order (usually you will have a packing slip). The item receipt is entered as soon as you take possession of the inventory assets. Your last step is to enter a bill from that item receipt.
  4. Inventory control is an essential part of job costing and financial reporting. The better your inventory control, the more accurate your financial and job cost reports will be.
  5. The Item History form will show you the history of the average cost of an item with the change in quantity for each month.
  6. The Invoice/Sale/Credit/Estimate List includes a utility to scan transactions to see if there is new information that might change the average cost of one or more items on that transaction. For example, you may have backdated a bill. If the invoice has already been created, that invoice would not “know” about the new information. That invoice would need to be resaved. You can resave invoices in a batch too. This may completed under Invoice/Sale/Credit/Estimate List menu.  Highlight a range of transactions, under the Actions menu, click Recalculate Selected. The utility will update the recorded cost for any transactions with differences.  Use this utility on the list with caution.  A more powerful version of this utility is located at Tools | Utilities | Database Checkup | Scan Invoices/Sales/Credits. Using the utility option will enable you to review the changes before updating in batches.
  7. To ensure the cost you pay for an item is the most recent, Total Office Manager does has the ability to update this cost when purchase transactions are created. Each time the cost entered is different from the estimated cost on the item, the software will prompt to update this information.  If you are not seeing this prompt, navigate to Edit | Preferences | Company and click the button Bring back all one time messages.

Additional Reading

https://www.accountingcoach.com/inventory-and-cost-of-goods-sold/explanation/4

Related Content

Average Cost Calculation Fix

Inventory Valuation Content

Unit of Measure (UOM)

Job Costing Overview Report

How to Setup and use the Job Costing Overview Report

The Job Costing Overview Report was created for a quick review of your cost on a particular job. This single page report will show you everything you need to know from open purchase orders, unbilled reimbursables, a breakout of the type of COGS and expenses you have spent on the job, pending estimates, the job’s accounts receivable and job profitability.

Access the Report Options

  • From the main menu, click Reports| Contacts & Jobs| Jobs| Job Costing Overview Report, or use the Report Navigator.

Report Options

The following options are available on this report:

  • Date Range Choose a date range that includes that date of the transaction affecting the cost of a job.
  • Customer:Jobs Choose a Customer:Job that has Invoices, Credits, Sales Receipts and is assigned to any purchase related transaction within the Cust/Vend column.
  • Salesperson Filter by the Sales Rep from the Sales Rep field within the Additional Info tab of the Customer:Job account.

Addition Report Options

  • Include Children – Includes all Job Costing related transactions for both the parent and all of its “children” or Sub Accounts.

Other Controls

  • Cancel The cancel button closes the window.
  • Preview The preview button will show an on-screen display of the report, from which a hard copy can be printed. The preview screen is also capable of exporting the report into a PDF format for email use.
  • Close Window on Preview Selecting this checkbox will have the options window automatically close when the preview button is clicked.

Report Setup

In order for your information to display in the proper fields on this report you will need to do the following:

Go to Banking| Chart of Accounts List. Edit Chart of Account types of COGS and Expenses that may be related to the cost of a job. You will want to edit any chart of accounts used for Material Purchases, Equipment Purchases, and Sub Contractor costs.

  • When editing the account, you will see the Job Costing Type drop down which will allow you to select the proper type.

Report Information

Job Information

Billing Address – This displays the Bill To Address as it is filled out within the Customer:Job account. The Customer:Job Display Name is listed first followed by the Bill To Address, Address 2, City, State/Province, Zip/Postal, Phone/Ext and Fax Number.

Work Address – This displays the Work Address as it is filled out within the Customer:Job account. The Work Address is listed first followed by Work Address 2, City, State/Province, and Zip/Postal.

Job Description – This displays the Job Description from the Description field within the Job Info tab of the Customer:Job account.

Account # – This displays the Account Number from the Account # field within the Payment Info tab of the Customer:Job account.

Job # – This displays the Job Number from the Contractor’s Job No. field within the Job Info tab of the Customer:Job account.

Job Type – This displays the type of job from the Job Type field within the Job Info tab of the Customer:Job account.

Salesperson – This displays the Salesperson from the Sales Rep field within the Additional Info tab of the Customer:Job account.

Job Status – This displays the status of the job from the Job Status field within the Job Info tab of the Customer:Job account.

Date Started – This displays the date that the job was started from the Start Date field within the Job Info tab of the Customer:Job account.

Projected End Date – This displays the projected date of when the job is projected to be completed from the Projected End field within the Job Info tab of the Customer:Job account.

Completion Date – This displays the date that all work was completed on the job from the Job Completion Date field within the Job Info tab of the Customer:Job account.

Alerts and Warnings

Alerts and Warnings are current job activities that you may want to pay close attention to. This shows you the total open purchase orders and the number of items from those purchase orders that have not yet been received. This also shows a total of reimbursable items that have not been invoiced to the Customer:Job.

Note: You can double click on the open purchase order alert to see the Purchase Orders by Customer:Jobs report. You can also double click on the reimbursable items alert to see the Reimbursables Report.

Time Sheet Hours

Time Sheet Hours includes the total amount of hours worked on the job based off the date range selected. This may assist with ensuring the proper amount of labor was charged for all of the work performed on the job.

Note: You can double click on the Time Sheet Hours to see the Time Sheet by Customer:Job report.

Budgeted Hours

Budgeted hours is calculated by totaling the labor minutes on any service item (with the Labor Only box check on the item’s accounting tab) included on an estimate with a status of awardedin progress, or closed that fall in the defined date-range.

Summary of Transactions

The summary of transactions will show you the totals for the different transactions that you have entered. This is where some of the setup information discussed earlier will be displayed. Most of this information is pulled directly from the General Journal within Total Office Manager which allows a precise total for each of the reported categories. Below is a detailed explanation of what each category includes:

Total Income from Sales and Invoices: This will display any increase in the chart of account types Income and Other Income that are assigned to the Customer:Job (from within the Assign Commissions form in the invoice).

Less Credits and Refunds: This will display any decrease in the chart of account types Income and Other Income that are assigned to the Customer:Job.

Labor Costs: Labor Costs consist of Time Sheet entries, payroll liabilities and commissions assigned to the Customer:Job.

Important Notice for Labor Costs

Total Office Manager will store the wages and liability rates based off when the time sheet entry was created. If a time sheet entry is deleted, the rate information stored for that entry will also be deleted. If the entry is then entered back into the system and the current rate has changed, this will change the labor cost calculated for the job.Total Office Manager used to calculate labor costs based off paychecks created from the time sheet entries assigned to the Customer:Job. Paychecks are no longer used which allows you to job cost labor by the day rather than the pay period. The job costing type, “Payroll and Labor Only” is no longer used for the Labor Costs section on Job Costing Overview Report. “Payroll and Labor Only” now affects the Summary of Transactions sections COGS or Expenses with no Job Costing Type. This information changed in June of 2012. Historical Time Sheet entries will use current wage and tax rates to calculate labor.

Time Sheet Entries: Time sheet entries are split out by each earning assigned to the Customer:Job. This includes earning calculations of Hourly, Salary and Fixed Amount. Hourly takes the employee’s hourly wage multiplied by the amount of hours worked in a single time sheet entry. Salary is figured into an hourly wage based off the annual salary amount divided by 2080 which is then multiplied by the amount of hours worked in a single time sheet entry. Fixed Amount earnings can be setup for job costing with an assigned hourly wage which is then multiplied by the amount of hours worked in a single time sheet entry. Note: For more information on using Fixed Amount earnings, see the instructions below.

In order to use Fixed Amount Earnings on Time Sheets you will need to do the following:

  1. Go to Employees| Lists| Payroll Items List.  Right click to create a New Earning or double click to edit an existing Earning with a calculation type of Fixed Amount. Select the check box “Used for Job Costing” and then click OK to save.

  2. Once the Earning is setup for Job Costing you will need to assign the earning and enter the rate by going to Employees| Employee List. Right click on the Employee and select, “Employee Payroll Items”. Click the Earnings tab, within the Item drop down select the Earning. Click the Save button. The Job Costing Rate field will be available to enter the hourly rate.

Payroll Liabilities: Payroll Liabilities are calculated using the combine current rates for FICA Med and FICA SS as well as the rate selected for FUTA and the rates entered for SUTA within Employees| Unemployment Tax Rates. This combined rate for the four major liabilities is then multiplied by the hours worked within a single time sheet entry.

Commissions: Commissions come from the Assign Commissions feature on Invoices/Sales Receipts. The Assign Commissions feature allows you to select an employee with a commission method which figures a commission amount. Each commission entry has a “Job Cost” check box that will include the commission amount in the labor cost. Note: For more information about assigning commissions, see the help topic on Commissions.

Equipment: This will display both an increase and decrease in the chart of account types Cost of Goods Sold and Expense that have a Job Costing Type of Serialized Inventory and is assigned to the Customer:Job.

Parts and Materials: This will display both an increase and decrease in the chart of account types of Cost of Goods Sold and Expense that have a Job Costing Type of Parts and Material and is assigned to the Customer:Job.

Sub-Contractors: This will display both an increase and decrease in the chart of account types of Cost of Goods Sold and Expense that have a Job Costing Type of Sub Contractor and is assigned to the Customer:Job.

Cost of Goods Sold (no job costing type assigned): This will display both an increase and decrease in the chart of account type of Cost of Goods Sold where no job costing type has been assigned and the Customer:Job is selected. This excludes COGS assigned to the job costing type “Payroll and Labor Only” since labor is now figured off timesheets.

Expenses: This will display both an increase and decrease in the chart of account type of Expense and Other Expense where no job costing type has been assigned and the Customer:Job is selected. This excludes Expenses assigned to the job costing type “Payroll and Labor Only” since labor is now figured off timesheets.

Additional Overhead: This will display if Additional Overhead has been assigned in the Additional Overhead field within the Job Info tab of the Customer:Job account. Additional Overhead can be assigned as a Percentage of the Final Amount or as a straight Dollar Amount. Additional Overhead is typically included for any miscellaneous overhead that must be accounted for. Note: For more information on using Additional Overhead, see the instructions below. See the bottom of the page on how to set this up.

Labor Burden: This will display an amount if a Labor Burden percentage has been applied within the Labor Burden field of the Customer:Job Company Preferences tab or as a different percentage to a specific job in the Labor Burden field within the Job Info tab of the Customer:Job account. A Labor Burden can be applied within the main preferences to assign a default percent to all jobs or on an individual basis to a specific Customer:Job if percentages of Labor Burden differ. The Labor Burden percentage calculates on the total of Labor Costs multiplied by the percentage entered on the Customer:Job. Note: For more information on assigning a Labor Burden, see the instructions below. See the bottom of the page on how to set this up.

Net Profit: This is the overall income of the job. Net Profit is figured by the total of Income minus credits/refunds and all cogs and expenses.

Summary of Transactions Percentages

% of Net Profit  – The Percentage of Net Profit is calculated based on the following formula: SummaryOfTransactionField / NetProfit

% of Total income – The Percentage of Total Income is calculated based on the following formula: SummaryOfTransactionField / Income. Total Income can be found in the “Profitability” section of this report.

Additional Info

The Additional Info section is used to examine additional information on the job. Some of the information contained in this section is the same information you would gather using the Job Estimates vs. Actual report and is mainly for Progressive Invoicing.

Estimates Awarded: This displays the total of all Estimates with a status of Awarded, In Progress or Closed assigned to the Customer:Job.

Total Estimates Invoiced: This displays the total of all Invoices that are created from an Estimate with a status of Awarded, In Progress or Closed that is assigned to the Customer:Job. Note: If this amount is less than the Total Income from Sales and Invoices above it typically indicates that the Invoice was not created from the Estimate.

Remaining Estimate Balance to Invoice: This displays the amount left on the Estimates to Invoice.

Total Funds Received: This displays the total of funds received from the Customer:Job. This section will not include any Discounts or Credits that have been given to the customer.

Accounts Receivable: This displays the total amount of funds that the Customer:Job owes.

Profitability

The Profitability section will show you exactly what was made on the job. This should provide a clear picture of all costs, expenses and the net profit.

  • Income: This is the total amount of income from the “Total Income from Sales and Invoices” section of the summary of transactions minus the total of “Less Credits and Refunds section of the summary of transactions.
  • Cost of Goods Sold: This is the total amount of COGS assigned to the job. COGS can come from the following sections of the summary of transactions; Equipment, Parts and Materials, Sub-Contractors and Cost of Goods Sold with no job costing type assigned.
  • Gross Profit: This is the total amount of Income minus the total amount of COGS.
  • Labor Costs: This is the total amount of Labor from the “Labor Costs” section of the summary of transactions.
  • Expenses: This is the total amount of Expenses assigned to the job. Expenses can come from the following sections of the summary of transactions; Equipment, Parts and Materials, Sub-Contractors and Expenses.
  • Overhead (additional): This is the Additional Overhead amount from the summary of transactions section.
  • Labor Burden: This is the Labor Burden amount from the summary of transactions section.
  • NPBT: This is the Net Profit Before Taxes. This is figured by taking the Gross Profit minus Labor Costs, minus Expenses, minus Overhead, minus Labor Burden which equals the NPBT.

Profitability Percentages

% of Net Profit  – The Percentage of Net Profit is calculated based on the following formula: Profit / Income

% of Total income – The Percentage of Total Income is calculated based on the following formula: NPBT / Income. Total Income can be found in the “Profitability” section of this report.

Assigning Additional Overhead and Labor Burden

Overhead

In order to assign Additional Overhead to a Customer:Job you will do the following:

Go to Customers| Customer:Job List and lookup the specific job you are working on. Once the job is found double click to edit the Customer:Job account. Click on the Job Info tab and select the method in which you would like to assign Additional Overhead. When finished, click Save and Close

Labor Burden

In order to assign a Labor Burden to the Customer:Job you will do the following:

Go to Edit| Preferences| Customer:Jobs| Company Preferences (tab) and enter a percentage to apply a labor burden to Job Costing for all jobs. This percent will auto populate within the Labor Burden field of each Customer:Job account.

To change the percentage on an individual job go to Customers| Customer:Job List, lookup the customer:job account and double click to edit. Click on the Job Info tab and change or enter the Labor Burden percentage for the Customer:Job.

Related Content

 

Scroll to Top

Request a Personalized Demo

Fill out the form below, and we will be in touch shortly.
Demo Form 121422

What number is equal to 7 plus 4?

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.

/* */