Purpose

Download Instructions

Select File ▶️ Download ▶️ Excel. Make sure to Save when filling out and when finished.

Click the button below to make a copy.

Completing the Reconciliation Workbook will reconcile differences in the Inventory Holding accounts on your Balance Sheet.

📌 Note: This reconciliation is only done for SAAS (Standard Aspire Accounting Solutions). For SAS (Simple Accounting Solutions) the Inventory Holding accounts should always be $0 at the end of each month after the EOM Expense tab journal entry has been entered into your Accounting System.

The Reconciliation Workbook can help you to identify timing differences between Aspire and your accounting system.

Before completing this workbook, make sure the following has been done:

✅Approved vendor bills in Aspire match Vendor bills in the accounting system.

✅Entered End of Month Expense tab Journal Entry into the accounting system.

✅End of Month Inventory tab Journal Entry has been entered

✅Extra costs Journal Entry has been made into accounting system

Make sure the Approved purchase receipts sent to accounting system (a) for the month match the vendor bills in the accounting system (b) for the month.

In Aspire, review list #3. EOM Receipts – Invoice Date Last Month

In your accounting system, create a list of vendor bills in the Inventory Holding Accounts for the month.

Make sure the total vendor bills in the accounting system $40,440.21 matches the total Purchase Receipts (vendor bills) in Aspire $40,386.73 for the month.

In this example, the accounting system has $53.48 more than Aspire.

If they do not match, review both lists vendor by vendor to see what is missing and fix the difference.

In the example above, you will need to delete the Site One invoice #8547 $53.48 that is in the accounting system two times or is a duplicate invoice in the accounting system.

Be sure to enter the End of Month Expense tab journal entry before filling out the Inventory Holding reconciliation workbook.

If at the end the inventory holding accounts do not reconcile, go to the end of month reports expense tab and compare the $ amounts there to what has been entered into the accounting system. Make sure each inventory holding account has the same $ amounts as the EOM expense tab.

Extra Costs are tax, freight and other costs added to a Purchase Receipt at the bottom right.

That only applies to the extra costs for Purchase Receipts added to Inventory and not work tickets.

If the Purchase Receipt has extra costs added to inventory, that cost does not get expensed.

That extra cost has to be manually calculated and then a journal entry should be entered into your accounting system.

Debit-materials (usually) $XXXXX

Credit-Inventory Holding material (usually). $XXXXX

Below are the steps of how to calculate the Extra Costs for your journal entry:

1 – Create a list in Purchasing ➡️ Purchase Receipts:

The list will show like the screenshot below. The Work Ticket #0 (the top group) will be the only extra cost that needs to be reviewed for the extra cost journal entry.

Filter the list to show correct Invoice Date period. Last Month or Custom Between (plus your dates) to show a different month.

Export your list to Excel.

Delete all rows that have a Work Ticket #. This will leave only the work tickets at the top group that show Work Ticket #0 in Column A.

The items with a Work Ticket of ‘0’ may or may not have been added to inventory. Each item might have been added to a work ticket.

If it shows a work ticket # on every item on the drill down, delete that line from the Excel sheet. It will be job costed on the work ticket.

The journal entry in QB would be:

In this example, the Purchase Type is materials, so the entire journal entry would be coded to COGS Materials & Inventory Holding Materials.

If the Purchase Type had been Equipment, Sub or Other, that portion of the Excel sheet would have gone to that purchase type COGS & that purchase type Inventory Holding account.

Inventory Holding Reconciliation Work Book

Add the Inventory Holding balances from your Balance Sheet your Accounting System to the Workbook.

Go to Inventory:

The screen will look like this.

Enter each Item Type total in each column for Equipment, Sub, Other & Materials. Sometimes there are balances in Aspire for Item Types other than materials.

If there is more than one Inventory Location, you will need to add the totals for each location.

The Net Difference row will show the differences between Accounting Inventory Holding accounts & Aspire Inventory accounts. If the Net Difference is -0-, there isn’t a need to reconcile.

Any Unapproved Purchase Receipts in Aspire will show in Aspire but not in your Accounting System Inventory. So, this amount needs to be added into the accounting inventory .

At list R1, go to the filter on the top left. Change the filter for Received Date – Custom-Less than or Equal to – to month end date currently reconciling.

2. Enter $ amounts from List R1 as a positive $ into the Workbook as shown below: (Enter a $ amount at each Inv Holding column for each Purchase Type. This example only shows Materials & Sub.)

Any Received Purchase Receipts in Aspire with an Invoice Date after the month reconciling will show in Aspire and your Accounting System Inventory will be lower So, this amount needs to be added into the accounting inventory.

1 – Go to Purchasing Icon, Purchase Receipts. View List R2 – Invoice after EOM-Received date in EOM. At list R2, go to the filter on the top left.

Change the filter for Invoice Date and Received date to the month end date currently reconciling.

2. Enter $ amounts from List R2 as a positive $ into the Workbook as shown below: (Enter a $ amount at each Inv Holding column for each Purchase type. This example only shows Material & Other.)

Any Approved Purchase Receipts in Aspire with a received date after the month reconciling will show Accounting System Inventory to be higher than Aspire inventory by this amount. So, this amount needs to be deducted.

1 – Go to Purchasing Icon, Purchase Receipts. View List R3 – Invoice date in EOM-Received date after EOM. At list R3, go to the filter on the top left. Change the filter for Invoice Date and Received date to the month end date currently reconciling.

2. Enter $ amounts from List R3 as a negative $ into Workbook as shown below: (Enter a $ amount at each Inv Holding column for each Purchase type. This example only shows Equipment.)

If there is job inventory in the month reconciling, this amount needs to be deducted because it is not included in the Aspire Inventory $ amount.

Go to EOM Reports, date it for the month reconciling. The screen will look like this:

Drill into the Job Inventory Materials $ amount (o). Group by Item Type.

2. Enter $ amounts from Aspire Inventory into the Workbook as a negative as shown below: (Enter a $ amount at each Inv Holding column for each Purchase type. This example only shows Material.)

Purchase Cost Differs or Variances are reflected on the End of Month report Inventory tab at Purchase Cost Differs from Item Catalog. Aspires inventory is a standard cost system. The costs do not change as you are purchasing items into inventory.

Aspire does not show inventory by first-in, first-out or average. The item cost remains the cost they were set up as. When an item is purchased, the item cost can be changed on the purchase receipt to be different than the item cost in the catalog. If the item is being added to inventory, the difference between the purchase price & the item catalog cost creates a Purchase Cost Differs.

That difference shows on the EOM Inventory tab at Purchase Cost Differs from Item Catalog. It will show the purchase difference multiplied by the quantity purchased. The Purchase Cost Differs or Variance will show on the EOM report the day & month the purchase receipt is approved. It will not show in the Invoice Date month.

For example, if an Invoice Date is entered as July 30, 2020 and approved on August 6th , the Purchase Cost Differs will show on the EOM reports for August. Any Purchase Cost Differs that was approved after the month closed with an invoice date of the month closed or prior will need to be entered at Task #11.

1 – Follow these steps to get the $ amount for Purchase Cost Differs approved.

2. Enter $ amounts from List into the Workbook at Task #11 as shown below: (Enter a $ amount at each Inv Holding column for each Purchase type. This example shows Equipment, Material and Other.)

After the Workbook is complete, the Difference after Reconciling Task #11 for Inventory Holding Equipment, Inventory Holding Sub and Inventory Holding Other should always be $0.

If not, see section 5. Inventory Holding Materials should not have more than a 4% variance of total inventory.

Reasons for Reconciliation Differences/Balances Inventory Holding Materials will always have a $ amount at ‘Difference after Reconciliation’. This difference should be managed on a month to month basis to ensure the difference doesn’t increase consistently every month. Make sure the balances stay at a small % of total inventory.

Differences can be caused from:

If the received date is in a different month than the Invoice Date, the EOM Inventory tab will show the Purchase Cost differ by the month of the Invoice date.

Aspire will show the variance in the month of the Received date. This can cause a reconciliation difference. Inventory Holding Equipment, Sub and Other should always show a Difference balance of $0. If there is a balance, the EOM expenses for that month need to be compared to the vendor bills in the Inventory Holding accounts for that month. To find the differences, follow the steps in the next section.

Make sure your Purchase Pivot report matches your EOM Expense for Holding Materials, Equipment, Sub and Other. There may be a few dollars difference in materials due to Received/Invoice date purchase received date differences.

Compare the approved vendor bills for the month in the accounting system with the vendor bills on the Purchase pivot to match EOM expenses for each Allocation (purchase/item type) Separately.

The balance in any Inventory Holding accounting for Inventory Holding Account for Sub, Eqpt or Other should only be a Received Date/Vendor timing difference. Received date in a different month than Invoice date.

These timing differences should show on the Reconciliation Work Book and reconcile to $0. If not, there is an error in either the EOM expenses, the vendor bills between both systems don’t match or allocations from inventory.

The pivot & EOM expenses can be filtered to show more than one month. Go back in the accounting system to the last month the Inventory holding (purchase type specific) reconciled to $0.

Date the accounting system from the 1st of the month after until the end of the month you're currently reconciling. Filter the pivot to show the same months.

1. On the pivot, filter ‘Allocation Type’ (e) to show only one of the Purchase Types. (example below shows Allocation Type Subcontractor)

5. Follow the same process for a different Allocation Type. On the pivot, filter ‘Allocation Type’ (e) to show only one of the Purchase Types.

7. In accounting system click into the Vendor bill (h) to view the Aspire Purchase receipt #. Example shows ‘Harco Equipment’, $885 in accounting and not on the pivot.