A startup franchise needed a solution to integrate POS data into their accounting software. They wanted to automate as much as possible while keeping budget in mind. The POS system needed to send summary data to the backend accounting software, and the franchisees used this data for reporting. The franchisor used the data to create transactions.
The POS system created Excel-based exports weekly that included the locations of the franchisee’s sales. At the franchisor’s back office, QuickBooks kept track of the sales and franchise data. Weekly sales created charges for royalties and shared advertising costs, which were then charged back to the franchisees based on sales numbers. The file from the POS system provided most of the sales data in QuickBooks.
We used Transaction Pro Importer to bring sales data into QuickBooks from the POS exports. During the import process, Transaction Pro Importer allowed us to choose the type of transaction we were importing and allocate the data to the individual customers in QuickBooks. Transaction Pro Importer also auto-populated the imported data for missing fields that the accounting system required.
During the import process, Transaction Pro Importer matched field names in the POS exported file to import columns. If the names do not exist or are different names, select it in the “Import File Column.” To help with the mapping process, specify whether or not the first row has field names.
An import wizard walks through the import process quickly, stopping on the field mapping screen to make selections to map the columns or apply a previously created map.
Since our POS reports did not include certain fields, we used the static value to set up the data points we needed for importing. For example, we used the static field to assign our Accounts Receivable account, Template Form, and our Transaction Date. Any field can be assigned a default value by the import, so it is an important part of the process. You can even use various formulas to calculate data for the imported columns.
Once we assigned the fields, we saved our settings in a map. Once saved, maps can be used in the future for importing. Always remember to back up and document maps for future reference.
At the end of the process, a transaction log shows the imported data and any errors. It’s recommended to enable this feature to receive confirmation that the data was imported without error.
When finished, a message confirms the completion of the import process.
Once imported, the POS data automatically created invoices by date in QuickBooks. This data provided what was needed for franchisor calculations based on sales for each location. In addition, this data was used to create royalty payments for the franchisor to process.
Before your first import, see the sample import files and sample maps in this Knowledge Base article. If you require additional assistance, please schedule a consultation with us.
Martin J. Roth, CPA, CITP
Partner-in-Charge of Technology, QuickBooks ProAdvisor
