Transcript BizPro Pete Integrating Business Systems

Today’s Agenda
Tools for QuickBooks Import & Export
 Cleanup & Merge of Company Files
 Advanced QuickBooks Reporting

 Combined Financials
 Xpanded Reports
Rules of Engagement:
short questions during presentation
Tweet answers
BizPro Consulting
[email protected]
www.bizpropete.biz

Reasons to use these tools:
 Reduce or eliminate manual procedures
 Eliminate redundancy
 Eliminate intermediate data repositories
 Spreadsheets
 Databases (i.e., Access, FileMaker, etc.)
 “Mine” existing QuickBooks databases
 Synchronicity between data source and QuickBooks
 Guarantee accuracy! Save time and money!
BizPro Consulting
[email protected]
www.bizpropete.biz





Intuit Interchange Format file (.iif)
 Banks now switching to Direct-Connect
Excel spreadsheets (.xls, .xlsx)
Comma- and tab-delimited (.txt, .csv)
Direct-connect (i.e., bank to QuickBooks)
Web-connect (i.e., e-commerce to QB)
BizPro Consulting
[email protected]
www.bizpropete.biz

Source: Export Lists

Choose List(s) to export

The export is formatted as follows:

Import List(s) (after modifying the above spreadsheet)
BizPro Consulting
[email protected]
www.bizpropete.biz
part #
description
manufacturer part #
GC002X00050
Stereo
MXD55
GC002MC0100
cost
qty
reorder point
reorder qty
130
3
1
3
39.95
0
2
6
GC002X00100
Car Starter Battery sp30-r
GPS/Audio Vehicle
Battery
DCS-33H
67.95
2
2
2
GC012MC0100
Tires - Front
Front tire 120/70-12
35.46
4
6
12
GC012MC0200
Tires - Back
rear tire 130/60-13
49.21
3
3
6
1) File > Utilities > Import > Excel Files
2) Point to above spreadsheet (columns in red)
3) Mapping template is to the right -------
4) Import all five mapped items!
BizPro Consulting
[email protected]
 .CSV file – Downloaded from Authorize.net (same as .IIF)
 Text or Tab Delimited – Usually created by a web site (.txt)
BizPro Consulting
[email protected]
www.bizpropete.biz
 Typical Source: Online banking or credit card interface
In QuickBooks, go to Banking > Online Banking Center
Once you “Receive Transactions”, you will bring up a
“matching” window where you can select individual or groups
of transactions, and post them to your QuickBooks register.
Note that the matching process in the Online Banking Center has improved significantly in
2010, and is much more user friendly. The possibility of duplicate transactions in your bank
account register has been virtually eliminated!
BizPro Consulting
[email protected]
www.bizpropete.biz


Web connect - .qbo downloaded from a web
site and then “manually” imported into
QuickBooks
Direct connect – transactions brought in
directly for review (preferred method) – web
page within QuickBooks
BizPro Consulting
[email protected]
www.bizpropete.biz

Why do it?
 Reduce data export/import to a range of dates







Intuit recommends that no company file exceed 100mb
Eliminate obsolete names (customers, vendors…)
Clean up and restructure inventory
Restructure and consolidate chart of accounts
Merge multiple company files into one co. file
Transfer work from remote offices to home office
Receive journal entries from accountants
BizPro Consulting
[email protected]
www.bizpropete.biz





Best darn utilities around!
Check out www.q2q.us
Release covers 2 versions
Requires MS Access
Other useful utilities
6.07
QB 2006 & 2007, Enterprise 6 & 7
7.33
QB 2007 & 2008, Enterprise 7 & 8
8.15
QB 2008 & 2009, Enterprise 8 & 9
9.09
QB 2009 & 2010, Enterprise 9 & 10
BizPro Consulting
[email protected]
www.bizpropete.biz
 Lists
 Transactions
Usually done in many small “stages” with backups between each stage.
Many useful reports facilitate error correction.
BizPro Consulting
[email protected]
www.bizpropete.biz
One source company and one destination company
BizPro Consulting
[email protected]
www.bizpropete.biz


The dilemma: Payroll Checks cannot be transferred from one
company file to another!
The Check Transfer Utility allows you to transfer Payroll Checks,
Payroll Liability Checks and Sales Tax Liability Checks from one
QuickBooks file to another QuickBooks file.
◦ Note that the checks are imported as regular checks and will not appear
on payroll specific reports such as 941's and W-2's.


Imported checks will contain all of the lines of data from the
original checks and will affect the same accounts, jobs and
classes as the original checks, but will not be Payroll checks.
This program is for use by users who:
◦ a. Prepare payrolls in one QuickBooks company file but need the data
added to another file for bank reconciliation or other purposes.
◦ b. Need to transfer historical data as part of the process of starting a new
QuickBooks file or when converting from Enterprise to Pro or Premier.
BizPro Consulting
[email protected]
www.bizpropete.biz
 The Irvin Report Combiner extracts data from 1 to 999
QuickBooks company files.
 It creates combining P&Ls and Balance Sheets based upon
identical chart of account names.
 In addition, the Combiner can transfer the companies’ data to a
new or existing QuickBooks "Consolidated" company file where the
data from each company is identified by a separate class.
BizPro Consulting
[email protected]
www.bizpropete.biz

Objectives





“Mine” the QuickBooks database
Perform “What-if” scenarios
“Join” data from multiple files
Extract results to spreadsheets or other databases
Database types – The old and the new
 Flat files (i.e. Files with records)
 Hierarchical
(i.e., IBM’s IMS)
 Relational (i.e., MS Access, MS SQL Server, IBM DB2)
 QuickBooks has been relational since 2007
 File = Table, Record = Row, Field = Column
BizPro Consulting
[email protected]
www.bizpropete.biz








ODBC is a Microsoft spec that allows users to share information
in a relational database across multiple and diverse platforms.
Examples: Microsoft SQL Server, MS Access, QuickBooks, Crystal
Reports, MS Visual Basic, DB2 for PCs
The application must be ODBC-compliant
QODBC is included with QuickBooks Enterprise (read-only)
It is available (at a price) from www.flexquarters.com
This facility allows you to “mine” the QuickBooks database while
“joining” multiple “Tables” based upon selection criteria
Results of any selection may be exported to Excel or Access
Interactive sync between QuickBooks and other SQL databases
BizPro Consulting
[email protected]
www.bizpropete.biz
XML – Extended Markup Language
 Java script – Building GUIs
 OLTP – online transaction processing
 Shopping cart interfaces – i.e. GoDaddy
 Custom websites – Joe the builder
 Intuit third party solutions at
http://marketplace.intuit.com/
 Intuit’s SDK for developers

BizPro Consulting
[email protected]
www.bizpropete.biz

For more information, contact:
◦
◦
◦
◦
Pete Bornstein
(925) 964-9393
www.bizpropete.biz
[email protected]
Thank you for your interest!