Table of Contents
Purpose
The EOM Revenue and Expense journal entries can be exported to Excel and that file can be imported into QuickBooks using a program called Transaction Pro. The program is available for both QuickBooks Desktop and QuickBooks Online. Importing the journal entry can save you time each month because you will no longer need to manually enter the revenue and expense journal entries into QuickBooks.
In order to use the Export File Feature with QuickBooks Online or QuickBooks Desktop, you will need to purchase and download Transactions Pro Importer. You may also need to add some additional accounts to your chart of accounts in your accounting system.
📌Note: To purchase and download Transaction Pro contact QuickBooks.
GL Account Numbers
For the import to work properly you will need to use Account Numbers in QuickBooks and possibly add some account numbers to your Chart of Accounts.
If you are not currently using Account Numbers in your Chart of Accounts, you must be in single-user mode and then can turn the functionality on in Preferences.
QuickBooks Desktop
To enable account numbers in QuickBooks Desktop:
A. Click on the Edit option in the menu bar and select Preferences from the drop-down menu.
B. Select Accounting from the options on the left.
C. Select the Company Preferences tab.
D. Under the Accounts section at the top of the window, click to put a checkmark in the option, Use Account Numbers.
E. Select the OK button.
QuickBooks Online
To enable account numbers in QuickBooks Online:
A. Go to the Settings icon and select Account and Settings.
B. Select Advanced.
C. Select Edit icon in the Chart of Accounts section.
D. Select Enable Account Numbers. If you want the account numbers to show on reports and transactions, select Show Account Numbers.
E. Select Save.
F. Exit the window.
Account Numbers to Add to the Chart of Accounts
The following accounts (Account Name - Account Type) will need to be added to your accounting system if they are not already set up.
Assign account numbers that are consistent with your current account numbering format.
Checking Account - Bank
Accounts Receivable – Accounts Receivable
Accounts Payable – Accounts Payable
Inventory Holding – Other Current Asset
Inventory Holding Material – Other Current Asset
Sub account of Inventory Holding
Inventory Holding Equipment – Other Current Asset
Sub account of Inventory Holding
Inventory Holding Subcontractor – Other Current Asset
Sub account of Inventory Holding
Inventory Holding Other – Other Current Asset
Sub account of Inventory Holding
Over/Under (Earned Revenue > Invoiced Revenue) – Other Current Asset
Only required if you are reporting Earned Revenue
Sales Tax Payable – Other Current Liability
Needed only if you bill tax to your customers
Over/Under (Invoiced Revenue > Earned Revenue) – Other Current Liability
Only required if you are reporting Earned Revenue
Revenue – Income
Revenue Account for each division if the COA is setup to that detail
Revenue Variance – Income
Only required if you are reporting Earned Revenue
Invoice Adjustments – Income
Miscellaneous Invoice Revenue - Income
Cost of Goods Sold Material – Cost of Goods Sold
Cost of Goods Sold Equipment – Cost of Goods Sold
Cost of Goods Sold Subcontractor – Cost of Goods Sold
Cost of Goods Sold Other – Cost of Goods Sold
COGS Account for each division if the COA is setup to that detail
Aspire Setups
After you have downloaded Transaction Pro and added the required GL Account Numbers in your Accounting System, you will need to complete the setups in Aspire located in Application ▶️Configuration and in the Divisions and Payment Categories of the Lists tab.
Application Configuration
In the Application Configuration you will need to enable the End of Month Accounting Export feature and map accounts to the Accounting System Chart of Accounts.
To open the Application Configuration screen:
Click on the profile icon in the lower left corner of Aspire and select Administration from the menu.
From the Administration screen, click on Configuration in the upper right-hand corner to open the Application Configuration screen.
Select the Accounting Sync tab.
Click to put a checkmark in the field labeled, Enable EOM Export. When this box is selected additional fields will be displayed at the bottom of the screen. Complete the fields that are appropriate for your company.
The additional fields that are displayed include:
Export Revenue Type (required): Select either Invoiced or Earned depending on how you report revenue on your Profit & Loss statement. If you select Invoiced, the debit amounts under A/R on your End of Month report on the Revenue tab will be used as the credit amounts to your revenue accounts. If you choose Earned, the credit amounts under Earned Revenue will post as credits to your revenue accounts.
Sales Tax Payable (optional): If you charge sales tax to your customers, enter the GL Sales Tax Payable, liability account number from your Chart of Accounts. The amount from the End of Month report, Revenue tab, under the Revenue section for Sales Tax will be posted as a credit to this account.
AR Account (required): Enter your Accounts Receivable account number. The total of the amounts from the End of Month report, Revenue tab, in the A/R section will post as a debit to this account.
Sales Tax Vendor Account (optional): If you charge sales tax to your customers you will need to complete this field. Enter the Vendor name, exactly as it appears in QuickBooks in the Vendor Center.
Equipment Account (required): Enter your Inventory Holding – Equipment Rental account number. The amount from the End of Month report, Expense tab, for Inventory Holding Equipment will post as a credit to this account.
Other Account (required): Enter your Inventory Holding – Other account number. The amount from the End of Month report, Expense tab, for Inventory Holding Other will post as a credit to this account.
Invoice Adjustments Account (required): Enter the Invoice Adjustments account number. The amount from the End of Month report, Revenue tab, under the Revenue section for Invoice Adjustments will be posted as a credit to this account.
Over/Under Account: This field will be required if you selected Earned for the Export Revenue Type. Enter either the Invoiced Revenue > Earned Revenue account number or the Earned Revenue > Invoiced Revenue account number. The amount from the End of Month report, Revenue tab, under the Revenue section for Over/Under will post as a debit or credit to this account.
Misc. Invoice Account (required): Enter the Miscellaneous Invoice account number. The amount from the End of Month report, Revenue tab, under the Revenue section for Misc. Invoices will be posted as a credit to this account.
Material Account (required): Enter your Inventory Holding – Material account number. The amount from the End of Month report, Expense tab, for Inventory Holding Material will post as a credit to this account.
Sub Account (required): Enter your Inventory Holding – Subcontractor account number. The amount from the End of Month report, Expense tab, for Inventory Holding Sub will be posted as a credit to this account.
Division Account Numbers
When the Enable EOM Export checkbox is checked, Aspire will add additional fields to the Division screen in the Lists in the Application in Administration. These fields allow you to specify account numbers in your accounting system that tie to each division.
To open the Manage Lists screen:
Hover over your profile icon in the lower left corner of Aspire and select Administration from the menu.
From the Administration screen, click on Lists in Application.
In the List Name drop-down field, select Division.
Click on a Division name to select it.
The additional fields that will be added to this screen include:
Revenue Account Number (required): Enter the Revenue account number for this division. The amount from the End of Month report, Revenue tab will be posted to this account for this division.
If you are reporting Invoiced Revenue, the amount in the A/R section will post as a credit to this account number.
If you are reporting Earned Revenue, the amount under the Revenue section will post as a credit to this account number.
Material Expense Account Number (required): Enter the Cost of Goods Sold Material account number for this division. The amount from the End of Month report, Expense tab, for the Material expense will be posted as a debit to this account.
Equipment Expense Account Number (required): Enter the Cost of Goods Sold Equipment Rental account number for this division. The amount from the End of Month report, Expense tab, for the Equipment expense will be posted as a debit to this account.
Sub Expense Account Number (required): Enter the Cost of Goods Sold Subcontractor account number for this division. The amount from the End of Month report, Expense tab, for the Sub expense will post as a debit to this account.
Other Expense Account Number (required): Enter the Cost of Goods Sold Other account number for this division. The amount from the End of Month report, Expense tab, for the Other expense will post as a debit to this account.
Payment Category Account Number
When the Enable EOM Export checkbox is checked, Aspire adds an Account Number field to the Payment Category screen available under the Lists option on the Administration screen in Applications. A Payment Category is a list of choices that can be added to Credit Memos in Aspire if the Credit as Expense box is checked.
Hover over your profile icon in the lower left corner of Aspire and select Administration from the menu.
From the Administration screen, click on Lists in the Application section.
In the List drop-down field, select Payment Category.
Click on a Payment Category. This field allows the user to specify account numbers in the accounting system that tie to each payment category (i.e. Maintenance Credit, Discount, Bad Debt, etc.). In the Account Number field enter in the account number you want associated with this payment category.
Exporting the End of Month Revenue and Expenses
Once end of month accounting export has been set up as described above, the End of Month Report provides options for performing the export:
1. Export Revenue to Excel
2. Export Expense to Excel
By default, the file will be created for the last month’s amounts. If you need amounts for an earlier period, select the date range before creating the export file. The export for Revenue and Expense can be done from any of the tabs in the End of Month report.
After selecting the export needed, a message will appear letting you know the download has completed. The exported .csv file can then be opened or saved and used to import the data into your QuickBooks.
Importing the .csv File into QuickBooks
QuickBooks Online
To open the Transaction Pro Importer in QuickBooks Online and import the file:
A. Click on Apps in the navigation bar on the left-hand side of the screen.
B. Click on the My Apps option at the top of the screen.
C. Find the Transaction Pro app and select Launch in the Action drop down box.
D. Select the Import option to open the Choose Import Type window.
E. Select Journal Entries from the options listed.
F. Drag and drop the Revenue or Expense .csv file into the box OR click to open File Explorer to browse to the location of the saved Revenue or Expense .csv file from Aspire and click Open.
G. Click the Select File and Sheet button in the lower, right-hand corner
H. At the Select File and Sheet screen, click Import Sheet to open the Match Columns to QuickBooks Fields screen.
I. Match the QuickBooks Fields with Import Column headers from the Aspire export file.
J. Click the Preview and Import button in the lower, right-hand corner to open the Preview screen.
K. Review the import file and make any necessary corrections.
L. Click on the Options button in the upper, right-hand corner to open the Import Options screen and select the option to Enable Account Numbers on Transactions.
M. Click the Import button in the lower, right-hand corner. A confirmation box will open; click Yes.
N. If there are any errors in the file a warning message will appear at the top of the screen in red font and the cell(s) causing the error will be highlighted in red.
O. If the import is successful you will receive a message highlighted in green at the top of the page reading, Your data was imported successfully .
P. Close the window.
Q. Repeat steps D – P to import the other (Revenue or Expense) Journal Entry.
R. Review the journal entry that is exported in the accounting system for accuracy.
QuickBooks Desktop
A. Open the Transaction Pro Import Wizard program
B. Click the Browse button to open File Explorer and find the Revenue or Expense .csv file that was exported from Aspire and click Open.
C. If the box to select the Field Delimiter shows, choose Comma.
D. Select the QuickBooks transaction Import Type of Journal Entry.
E. Select Next to open the File Contents screen.
F. Review the Aspire file and click Next to open the field mapping screen.
G. Map the Import File Column field in the Aspire .csv file to the QuickBooks Fields in the drop-down list.
H. Review the data and make any necessary edits and then click the Next button to open the QuickBooks Item Settings screen.
I. Click Finish to start the import of the file. THe progress bar will display the status of the import.
J. Repeat steps A - I to import the other (Revenue or Expense) Journal Entry.
K. Review the journal entry that exported in the accounting system for accuracy.