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.

Is Hosted QuickBooks Right for You?

If you are currently using the popular QuickBooks desktop software, you now have a fairly new option available to you:  hosted QuickBooks.  In this article, we’ll talk about what it is, what type of businesses it’s right for, and how to get started if you decide it’s for you.

A Host of Opportunities

Hosted QuickBooks changes the location of your QuickBooks company file from your local computer to one of the dozen authorized QuickBooks hosting companies.  You then access your QuickBooks file through a secure Internet connection.  The good news is you continue using the exact same QuickBooks software, screens, forms, and reports that you are comfortably familiar with, so the additional learning curve is extremely low.   The two biggest differences are:

  • You access your QuickBooks differently; instead of accessing your local software, you will access the same version of QuickBooks software via the cloud on a secure server provided by a hosting vendor.  You will most likely access your QuickBooks by clicking on a desktop icon or accessing a screen and entering your login information.
  • The pricing is different.  Instead of paying a large software fee at the beginning and then optionally paying for annual upgrades, you pay monthly, like a lease.

There are a few other very minor differences, such as how you back up your file, how you print checks, invoices, and other forms, and how you interface with other software such as Microsoft Outlook® or Word®.  At most, the learning curve for each of these minor changes is five minutes top for any user.

Who Benefits

You will benefit from hosted QuickBooks if any of the following are true:

  • You, your team, your bookkeeper, or your CPA needs to be able to access your QuickBooks files from multiple locations.
  • You are spending at least one hour per month restoring the file from one location to another.
  • You have experienced errors in the past from backing up and restoring the company file or the Accountant’s Copy because of passing it back and forth among people who need to update it or to get information from it.
  • You prefer to save the time it takes installing QuickBooks and applying the upgrades to QuickBooks software.  With hosted QuickBooks, the hosting vendor takes care of all of that.
  • You do not have a recent backup of QuickBooks and forget to take backups on a regular basis.  With hosted QuickBooks, backups are a routine part of the process.
  • You’re great at working on the core items of your business, but want to reduce time spent on IT-related tasks.
  • You dislike or feel inadequate when it comes to technology, and you agree it makes sense to outsource as much as possible.

Any Concerns

Hosted QuickBooks is great, but it’s not right for everyone.  If you feel “safer” with no one having access to your QuickBooks, then hosting it may not be right for you.  Although the data centers are far more secure than the PCs in most people’s homes and offices because they have to undergo a rigorous security audit to become a hosting vendor, some people are simply uncomfortable passing their financial data to others.  If you want to consider hosted QuickBooks and wonder about security, we’ll be happy to have a conversation with you about that.

Hosted QuickBooks is also not right for people that are using very old software versions because you may be forced to upgrade to a newer version.

Hosted QuickBooks is also not right for people who have much more free time than budget.  Although hosted QuickBooks is not particularly expensive, there is a cost outlay that will buy you time savings.  If the free time you gain (that you can apply to completing more important priorities in your business) is not valuable to you, then hosted QuickBooks may not be right for you.

Getting Started

Before moving to a hosted QuickBooks solution, your accounting professional will want to ask you questions about how you are using QuickBooks, if they aren’t already familiar with your requirements.  Selecting the right hosting solution means evaluating:

  • What version and line of QuickBooks you are currently using because this has to be exactly matched with the hosting vendor.
  • What other applications access QuickBooks, such as online banking and payroll.
  • What add-ons you are using with QuickBooks, if any.
  • What printers, Microsoft software, email software, and other peripheral needs you have when using QuickBooks.

Once those answers are gathered, your accounting professional can provide you with some hosting solutions, costs, and implementation plans.  Most accounting professionals partner with one or more hosting companies so that you can get a seamless one-stop shop experience.  You may also be able to benefit from volume or package pricing through your accounting professional.

If you are thinking that hosted QuickBooks might be right for your business, please email us or give us a call so we can talk more about it.

 

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.