Do You Import and Export QuickBooks Transactions?

Do You Import and Export QuickBooks Transactions?
By Bob Swetz
Controller Consultant | Tier One Services, LLC
Lately, I have been doing a lot of exporting data from one QuickBooks Desktop company and importing into another. Transferring complete and accurate data from Point A to Point B is critical to the integrity of your financial records. Here are some tips to help you complete this task with efficiency and accuracy.
Exporting Information from QuickBooks
There are 2 ways to export information from QuickBooks that have risen to the top as my favorites. First you can create a report and hit the Excel button to “Create a New Worksheet.” When using this method, I recommend selecting the last option to create a .csv file. It goes much quicker this way and you have fewer formatting issues to deal with. Although the file is more suited for import, you will still have to “play” with it so that it will be import-ready. For example, beware of any account numbers, items, zip codes and other number fields that may begin with zero. Chances are when you open the .csv file the zeros will be removed. There is a workaround for this, so feel free to get in touch if you have this issue.
The second method of export is a 3rd party app. I have really come to like Transaction Pro Exporter (TPE). TPE does a nice job of exporting all the information you will need in an import-ready format.
Importing Information to QuickBooks
There are multiple ways to get information into the new QuickBooks Desktop file, depending on the type of information that you’re moving. As with the exports, some importing can be done directly from within QuickBooks. In the Lists menu there is an option to Add/Edit Multiple List Entries. From here, you can import customers, vendors and various inventory items. Be careful though, because you may not be able to import all data fields using this method, so if you have a lot of attributes in each of those lists you may want to use another method. From the Accountant menu, which is not available in all versions, you can import transactions. By selecting Batch Enter Transactions, you can import checks, deposits, credit card transactions, bills and invoices. One thing I like about this method – especially for bills and invoices – is that you can import positive and negative transactions together and QuickBooks breaks them out for you.
Finally, you can import transactions and lists using a 3rd party app. For this I recommend Q2Q or Transaction Pro Importer (TPI). They both have their merits, but I would say Q2Q is better for less complex data. The drawback is you cannot manipulate the data before import, whereas with TPI you can make changes to the .csv or Excel file before you import which is sometimes necessary.
Do You Import and Export?
If you have questions or want to dig deeper, feel free to schedule a 15-minute troubleshooting session with me at http://bit.ly/Scheduling_Troubleshooting or connect with me on Facebook at https://www.facebook.com/bobswetzonline.

How to Consolidate Financial Statements from Multiple QuickBooks Files

 

Two ways to consolidate are:

EXCEL TRIAL BALANCES

[1] Export all trial balances to Excel, position them on the same tab but each below the previous one, tag add a column to tag each account with the entity name, combine the DR and CR into one DR (CR) column.
[2] Create your financial statements with your desired accounts and headings
[3] On your tab with the trial balances, create a column called Balance Sheet and assign a B/S account from your B/S page by linking to it…for all line items. Add another column for Income Statement Accounts.
Be sure to skip all intercompany accounts.
[4] On your financials, use the SUMIF function to pull from the columns on the T/B tab using your mapping.

Pros: You can easy-to-update, professional-looking consolidated financials.
Con: It takes a while to set up the first time.

 

QUICKBOOKS ENTERPRISE

[1] Make sure the Charts of Accounts are identical across all 3 entities for any accounts that you wish to consolidate. Account number, spelling, parent/sub status.
[2] Also make sure that any intercompany accounts are all on the same line, i.e. if you have an asset in one and a liability in the other, change one of them to an asset before consolidating.
[3] Use the tool in the Reports menu to consolidate.

Pro: Doesn’t take a lot of time.
Cons: You have to set up those intercompany accounts each time so they get zeroed out, and you end up with consolidated financials that are in Excel and are very cheesy-looking.

What Should I Expect From My QuickBooks Trainer?

Start by sharing your business strategy and business model so your trainer can activate features that you’ll need for external and internal reporting, such as:
* bank accounts, including PayPal
* receivables
* jobs vs. customers
* customer types
* inventory features
* item specifications
* item groups
* price levels
* credit card accounts
* payables
* sales tax
* class tracking
* 1099 setup
Then learn how to pull meaningful information from your QuickBooks file and how to interpret that information and make money with that information. If your company is new, have the trainer use a sample file from your own industry to teach you. Examples are: Financial statements, aging reports, job profitability reports, Profit & Loss by business segment or location.
Next: Back-fill into the data entry that’s required to produce those reports. Sales cycle, purchasing cycle, how to do an inventory count & inventory adjustment if you have inventory. Your training should include bank feeds, information about the best 3rd party apps (if any) for you, and the massive time-saving merits of attaching files to transactions and list items.
Next learn how to check your own data with error trapping techniques. For example, if you’re using class tracking, then the Profit & Loss Unclassified report should always be empty. Undeposited Funds should never be stale; the bank reconciliation detail will show transactions that have been outstanding for too long. Stay on top of this and you’ll avoid an irate vendor who hasn’t gotten paid because the check that you wrote is sitting on someone’s desk. You’ll also avoid making a decision based on incorrect financial statements.
To put a bow on the pre-structured piece, learn best practices in backing up the file, data security.
Don’t sign on to a training without a final phase that includes trainer availability for questions that will arise as you actually use the file. You can discuss with your trainer if you want anytime availability for questions as they arise vs. weekly sessions to get the answers, and you’ll document your questions in a list as they come up.

Cutting Expenses Key To Profitability, But How? Some Tips

It may seem like a no-brainer – cut expenses to make more money – but many small changes can result in significant savings.

It looks like the economy may finally be looking up. Still, this is no time to loosen the purse strings in terms of your business expenses. Rather, why not re-double your efforts to cut costs and boost your profitability?

Excessive expenses cause debt, which in itself can be very costly. So any money-saving actions you take will be doubly rewarding.

 More Effective Money Management

 To cut expenses significantly, poke into every corner of your company’s finances. Inventory is a good place to start. If you sell multiple units of the same item and reorder regularly, you should be using QuickBooks’ inventory-tracking tools. Go to Edit | Preferences | Items & Inventory | Company Preferences.

 Ghost ALL STAR logo

 Figure 1; Make sure that these settings are correct. If you need advanced features like FIFO costing, serial number and lot-tracking or inventory management at multiple locations, contact us about upgrading to Enterprise Solutions.

 

You should be stocking your inventory to match the pace of sales. You don’t want to be caught short, nor do you want to be sitting on too much and tying up money unnecessarily. QuickBooks can help, but you’ll need to calculate the sweet spot for each item. Several built-in reports can help, including:

  •  Inventory Valuation Summary. Displays the current asset and retail value of each item and inventory as a whole
  • Inventory Valuation Detail. Shows how individual transactions have affected the value of your inventory
  • Inventory Stock Status By Item. Helps you set up smart reordering procedures
  • Open Purchase Orders
  • Outlines each purchase order and its expected delivery date

Ghost ALL STAR logo

Figure 2: To maintain profitable inventory levels and minimize expenses, you’ll need to study QuickBooks’ related reports regularly. When you’re making buying decisions, consider factors like reorder turnaround time and seasonal sales upticks.

 

Ratio reports, like profit over sales, can also be very telling. QuickBooks does not supply these, but we can help you create them in Excel.

Using Available Tools

The efforts you make toward reducing expenses in other ways can result in more savings than you might think. Here are some actions you can take that will accelerate your cash flow:

Use QuickBooks’ budgeting tools

. This doesn’t need to be as onerous as you might expect – you can start by pulling in your real data from the previous year as a base. Build in line items for ongoing accounting support like QuickBooks maintenance. Click on Company | Planning & Budgeting | Set Up Budgets

Ghost ALL STAR logo

Figure 3: Selecting this option simplifies your task.

 

Minimize your April 15 obligation with year-round tax planning. Work with us throughout the year on the next year’s taxes to, for example, make smarter quarterly payments, and we’ll help you reduce your tax bill by making better decisions every day.

 Get discounts by paying invoices early. Set up a custom field in vendor records to track this.

 Ghost ALL STAR logo

 Figure 4: Get to know which vendors offer early discounts – and take advantage of them.

 

Analyze the cost-effectiveness of your transportation. Can you replace some in-person sales calls with web-based communication? Make sure that your delivery routes and sales call paths are efficient.

Change product/service prices to build in your own cost increases. Do it across the board, in small increments. It may not even be that noticeable to customers.

Talk to us about establishing a line of credit. We’ll help you determine if this is a viable option for emergencies. It’s cheaper than using credit cards.

Cross-train employees. Have employees train each other on their tasks where it makes sense. You can avoid costly temp help and relieve overworked departments.

 Don’t try to change everything at once. Establishing these new procedures will require some extra work. And you may not notice a reduction in expenses immediately. But over time, you will see a positive change – one that will give you extra dollars and hours to invest in making your company flourish.