• Skip to primary navigation
  • Skip to main content

QBGarage.com

The QuickBooks Specialists

  • Home
  • Blog
  • KnowledgeBase
  • Company
  • Show Search
Hide Search
You are here: Home / Archives for Excel

Excel

Does QuickBooks Support True Fund Accounting?

Chief Mechanic · September 10, 2010 ·

Out of the box, QuickBooks doesn’t completely support true fund accounting, but there are several simple, low cost ways to provide fund accounting in QuickBooks.

Fund accounting is a method of accounting used by public sector (e. g., cities and towns) and non-profit organizations because these entities have a need to track balances and expenditures across multiple purposes, or funds.  Money is normally appropriated to a purpose at the start of a fiscal year, and expenditures are made against the available balance for that particular purpose or fund over the course of time.  As a result, public sector and non-profit entities have a need to produce balance sheets and income statements for each fund as well as the overall entity.  In contrast, a for-profit business produces a balance sheet and income statement for the overall entity but doesn’t have the need to produce balance sheets for groups within the overall business, such as individual departments.

Support for fund accounting in QuickBooks consists of 2 needs:

  1. Preparing an income statement for each fund
  2. Preparing a balance sheet for each fund

The first need, preparing a fund income statement, is easily handled by QuickBooks through the use of classes.  Classes are a tool to track 1 aspect of an entity’s finances and are managed on the Lists->Class List menu selection.  Generally speaking, simply creating a class for each fund and assigning a class to all transactions solves the need to track income and expenditures across funds.  See our article for more information on the role of class tracking.

Where QuickBooks out of the box falls short in supporting fund accounting is in fulfilling the second need – preparing a balance sheet for each fund.  There are 2 ways to address the need for fund balance reporting:

  1. Use a third-party add-on
  2. Track fund balances in a separate program, such as Excel

Because of the popularity of QuickBooks, third-party developers have produced a variety of tools to add features and capabilities to the program.  Intuit maintains a list of many of these add-ons on the Intuit Marketplace.  For QuickBooks, there are several add-ons listed in the Fund Accounting category.  One add-on, FundsFix by Qfix Software Llc, addresses the need to produce a balance sheet for a fund.  Based on the user comments reported on the Intuit Marketplace, this add-on has been well-received.

Another approach is to use a separate program such as Microsoft Excel to track fund balances.  Normally, a spreadsheet would consist of the opening fund balance at the start of the fiscal year with additional columns for the year-to-date expenditures manually entered from a QuickBooks report and the calculated remaining fund balance.  For simple fund accounting needs, this approach is workable.  However, given the availability of a very low-cost add-on to address this need, it’s an approach that is only suitable for the simplest fund accounting requirement.

Vote This Post DownVote This Post Up (0 rating, 2 votes)
Loading...

What Are the System Requirements To Operate QuickBooks?

Chief Mechanic · September 8, 2010 ·

System requirements for QuickBooks products vary by version.

The minimum system requirements can be found in various Intuit knowledgebase articles based on the product version:

  • QuickBooks 2010 and Enterprise Solutions 10.0
  • QuickBooks 2009 and Enterprise Solutions 9.0
  • QuickBooks 2008 and Enterprise Solutions 8.0

System requirements include:

  • operating systems supported
  • processor, RAM, display, and free disk space requirements
  • CD drive specifications for installation from optical media
  • network requirements
  • additional software requirements, such as Microsoft .NET runtime environment
  • compatibility with other QuickBooks software such as Point of Sale (POS)
  • third-party office productivity software compatibility, such as Microsoft Office
  • firewall and anti-virus compatibility
Vote This Post DownVote This Post Up (No Ratings Yet)
Loading...

What Does the Error “Unable To Open Statement” Mean When Using Intuit Statement Writer?

Chief Mechanic · September 8, 2010 ·

In some situations, users of Intuit Statement Writer (ISW) may encounter a problem where the program fails to open properly.  Intuit Statement Writer is a financial report writer introduced along with QuickBooks 2009 that makes use of Microsoft Excel.

After clicking on the Intuit Statement Writer menu selection, Microsoft Excel opens to a blank spreadsheet and displays the fatal error message:

Unable to open statement: System.ArgumentException: Invalid license number

Here’s a screen shot of the exact error message:

Intuit Statement Writer UnableToOpenStatement

According to Intuit, this error occurs because the file iswruntime.dat contains an incorrect license number; the error has only been observed on computers running Windows Vista.

To fix this error message, simply rename or delete iswruntime.dat, which is located in the C:ProgramDataIntuitStatement Writer folder.  When Intuit Statement Writer next starts, it will re-create a new file.

This error message is discussed in this Intuit knowledge base article.

Vote This Post DownVote This Post Up (-1 rating, 1 votes)
Loading...

Should I Use Account Numbers In My General Ledger?

Chief Mechanic · September 7, 2010 ·

We think you should use account numbers in your general ledger, but we think it’s more important that you’re consistent in either using them correctly or not using them at all.

To use account numbers, you need to enable that preference by visiting the Edit->Preferences menu and clicking on the Company Preferences tab of the Accounting submenu, which is shown below.

QuickBooks Premier 2009 Preferences Use Account Numbers

Once the account number preference is enabled, you’ll be able to manually assign account numbers to your general ledger accounts.  QuickBooks won’t automatically assign them for you or insure that your numbers follow a consistent structure.  It will insert the account number before the account name, and you’ll then be able to quickly locate accounts by typing the first few characters of an account name or the first few digits of the account number.

When assigning account numbers, use numbers that:

  • have a consistent length
  • have multiple unused integers between numbers to accommodate changes to your general ledger
  • follow a consistent structure

New businesses or companies installing QuickBooks for the first time are more likely to make changes to their general ledgers, so it’s especially important for these firms to have the flexibility to add or change account numbers.

In older versions of QuickBooks, account numbers were a way to re-arrange the order of accounts rather than having an account list in alphabetical order based on the account description.  Since you can now drag accounts in your chart of accounts list to rearrange the order, account numbers no longer serve that purpose.  Account numbers are primarily a tool to organize account numbers outside of QuickBooks, such as in Excel, and to improve the readabilty of your account list.

Intuit recommends the following ranges for assigning account numbers:

10000 to 19999 Assets
20000 to 29999 Liabilities
30000 to 39999 Equity or Capital
40000 to 49999 Income or Revenue
50000 to 59999 Cost of Goods Sold, Job Costs, or Project Costs
60000 to 69999 Expenses or Overhead Costs
70000 to 79999 Other Income
80000 to 89999 Other Expenses

QuickBooks organizes accounts on reports based on the account type, not the account number.  Whether you follow this structure or another one of your own choosing is up to you.  Just keep in mind that account numbers are another tool to help organize information.  If you use account numbers but don’t use them in a way that results in a better organized chart of accounts, you’re actually worse off.

Vote This Post DownVote This Post Up (+4 rating, 4 votes)
Loading...

What Should I Do If My Accrual Basis Balance Sheet Is Out Of Balance?

Chief Mechanic · September 5, 2010 ·

A balance sheet in QuickBooks can be produced on either a cash or accrual basis.  A balance sheet that’s in balance is one where total assets are equal to the sum of total liabilities plus total equity.  Sometimes, if your company file (.qbw) has become damaged, this fundamental accounting relationship can be broken because of data damage to individual transactions.

Intuit has several troubleshooting steps to attempt to resolve problems with a balance sheet that’s out of balance.  These steps depend on which reporting basis balance sheet is out of balance.  In this article, we’ll summarize the steps to address an accrual basis balance sheet that’s out of balance.  See our related article on resolving problems with a cash basis balance sheet.  There are also other ways to use reports to assist rebuilding a damaged company file.

The most likely cause of an out-of-balance accrual basis balance sheet is an income or expense account with an account balance but without transactions that add up to that balance.  To fix this problem, Intuit recommends a multi-step process that involves:

  1. calculating the amount by which the balance sheet is out of balance
  2. identifying the problem account
  3. testing your account identification
  4. recording a check for $.01 to that account
  5. running the Rebuild Data utility
  6. deleting the check for $.01 previously recorded and testing the outcome

Step 1 – Calculate the Amount Your Balance Sheet is Out of Balance

Produce a standard balance sheet (Reports->Company & Financial->Balance Sheet Standard) and calculate the amount your balance sheet is out of balance.  Be sure to click the Modify Report… button and set the Dates to All and the Report Basis to Accrual.

QuickBooks Premier 2009 Modify Report Balance Sheet All Dates Accrual Basis

Step 2 – Identify the Problem Account

First, export your chart of accounts.  From the File->Utilities->Export->Lists to IIF Files… menu selection, choose Chart of Accounts in the Export window and click Ok.

QB_Premier 2009 Export Chart of Accounts

This export procedure will produce a delimited IIF file.  Open this file in Microsoft Excel.  Intuit recommends deleting the first 2 lines of this file, but we’ve kept them in the file in the screenshot below so you can see how the file will appear when you first open it in Excel.  Note that Name is in Column B and OBAMOUNT is in Column F.  Find the out-of-balance amount you calculated in the previous step in Column F, OBAMOUNT.  There are 2 ways to do this: a) use Excel’s Find capability or b) sort all rows below the first 3 rows in the original Excel file (below the first 1 row if you choose to delete the first 2) and scroll through the sorted list of numbers to locate the out-of-balance amount you previously calculated.

Write down the account name from Column B that matches the OBAMOUNT you locate.

Excel Chart of Accounts OBAMOUNT

Step 3 – Test To See If the Account You Identified Is the Problem Account

Return to QuickBooks and open your chart of accounts (Company->Chart of Accounts or the keyboard shortcut Ctrl + a).  Locate the account you identified in the previous step and double click on it to produce a QuickReport.  Be sure to set the Dates setting to All dates.  If there are no transactions in this account, then the identification process in Step 2 was successful.  You’ve located an account with a balance in the chart of accounts but without transactions that add up to that balance.

Step 4 – Record a Check For $.01 To The Problem Account

Record a check to the problem account for $.01 from the Banking->Write Checks menu selection or the keyboard shortcut Ctrl + w.  On the Expenses tab of the Write Checks window, enter the problem account previously identified.

Step 5 – Run the Rebuild Data Utility

From the Files->Utilities->Rebuild Data menu selection, run the Rebuild Data utility.  This utility will attempt to match the transactions in an account with the account’s balance, thereby resolving the out-of-balance problem on the balance sheet.

Step 6 – Delete the Check Previously Recorded and Test the Results

Delete the check you recorded in Step 4.  Open your chart of accounts (Company->Chart of Accounts or the keyboard shortcut Ctrl + a) and locate the problem account.

If the account you identified was a balance sheet account (accounts payable, accounts receivable, bank, credit card, equity, fixed asset, loan, long-term liability, other asset, other current asset, or other current liability), double click on the account to QuickZoom into that account’s register.  Locate the check for $.01 and delete it by clicking the Edit->Delete menu selection or using the keyboard shortcut Ctrl + d.

If the account you identified was an income statement account (income, expense, other income, other expense, or cost of goods sold), double click on the account to produce a QuickReport.  Be sure to change the Dates setting to All.  Locate the check for $.01 and double click on that entry on the QuickReport.  Delete the check by clicking the Edit->Delete menu selection or using the keyboard shortcut Ctrl + d.

Repeat the steps in Step 1 to produce a standard balance sheet and verify that the balance sheet is in balance.  Be sure to confirm your Dates setting is set to All and your Report Basis is set to Accrual.

Sometimes, if you have several problem accounts, repeating this entire series of steps can restore balance to your balance sheet.  For more serious data problems, send us an email.

To follow Intuit’s discussion of these steps, consult this Intuit knowledge base article.

Vote This Post DownVote This Post Up (0 rating, 2 votes)
Loading...
  • Go to page 1
  • Go to page 2
  • Go to Next Page »

Accounting

  • Financial Accounting Standards Board

Developer

  • Intuit Developer Network Forums
  • qbXML Onscreen Reference

Intuit

  • Enterprise Solutions
  • Intuit
  • Intuit Marketplace
  • QuickBooks
  • QuickBooks Online Community

QBGarage.com

Copyright © 2008–2023 QBGarage.com · Privacy · Terms & Conditions · Site Help