FocusPOS can integrate into QuickBooks by generating a Quickbooks-compatible .iif file. WIth this .IIF file, you can import sales data from FocusPOS into Quickbooks.
From time to time, the export from Focus into Quickbooks may not import successfully due to an Out of Balance message. Some versions of Quickbooks may display the amount that is out of balance.
When importing sales data into Quickbooks, the debits & credits must match for Quickbooks to successfully import. If changes are made to FocusPOS transaction components that impact the Quickbooks Import, and those changes are not mapped in FocusPOS Setup > Miscellaneous > Accounting Interfaces, an out of balance IIF file may result.
After attempting to import an IIF file, you will receive a message indicating that the file is Out of Balance. Newer versions of Quickbooks will provide you with the amount that is out of balance. Once the amount is displayed, you can then follow a few steps to pinpoint the amount and track it down using Focus PDF reports.
- Using the .iif file you received via emailed or generated as a file, you can import this into Excel to determine what is causing the out of balance amount.
- Since Excel cannot read native .iif files, you'll need to view All Files (*.*) to find the .iif file in the folder you saved it to.
- Launch Excel and go to Open. Once there, you can view All Files (*.*) and find your saved .iif file.
- By default, Excel uses the All Excel Files (*.*) to filter by, so you must choose the All Files (*.*) filter to find your .iif file so Excel can open it.
- You'll then see a message letting you know you're on Step 1 of 3 of the Text Import Wizard.
- The header will read, "The Text Wizard has determined that your data is Delimited".
- All that is needed is to press Next for each of the 3 steps. Once you finish the final step, press Finish.
- Once finished, Excel will now load the spreadsheet for you to determine the out of balance amount.
- Find the amounts column with all of debits and credits listed for each account entry.
- In the column below the last entry amount, Sum the entire column. If a whole number is present, whether positive or negative, the day is out of balance. If it is 0, the day in question should balance and import normally.
- You can then now view the Daily Sales PDF report to see if this whole number amount can be found. Out of balance import problems occur due to Accounts not being mapped properly in Accounting Interfaces. Typically, this can be linked to new Discounts, Report Groups, RG Discounts, Payment types and/or Paid Ins/Outs that are not yet configured in Accounting Interfaces. It is important if using Quickbooks to instruct the restaurant staff that any new categories of Sales, Discounts or Payment Types will need to be mapped also in Accounting Interfaces to avoid Out of Balance messages.
- For Example, if your out of balance amount is $50, scan through the Daily Sales PDF report to determine where you may see that $50. Check Report Group Sales, Discounts, and all Payment types. Usually, this can be found quite simply. Once you find the amount, see what category it's reporting to and map that in Accounting Interfaces. You can also check a PMIX report in Focus, showing all Report Groups, may also come in handy if you believe new Items and Report Groups are what's not mapped correctly. Basically, any Report in Focus you can use that can show Sales, Item Sales and Discount/Paid In information can be of great help in finding this out of balance amount.
- Once the new Account categories are mapped, you can now generate a new .iif file and see if it imports correctly. To verify, you can open up a newly generated file and Sum the column once more. If the column comes out as a whole number, you're still out of balance. However, if the sum of the column equals to a large negative non-whole number or a 0, it was able to balance out. Thus, when you go to import the .iif file into Quickbooks, it should import without issues.
If you do not have access to Excel, you may use Google Sheets.
The same process applies, except with Google Sheets, you MUST rename the .iif file to a .csv extension.
Once you open up Google Sheets, you may then go to File>Open>Upload>Find your .csv file and view in spreadsheet format. You can then sum your amounts column as described above to get your whole amount that is off.
Please sign in to leave a comment.