Jump to content

Using Excel to Create and Update QuickBooks Budgets

+ 2
  BonnieB's Photo
Posted Oct 25 2012 01:15 PM

QuickBooks budget reports can help you plan for the future and evaluate whether your business is going boom or bust. However, the program’s tools for building and editing budgets don’t compare to what you can do with a program like Excel. QuickBooks keeps only one budget at a time for the same type of budget and time period—for example, the profit-and-loss budget for fiscal year 2013—and it doesn’t offer a command for copying a budget. That’s where a spreadsheet program comes in handy. You can easily copy a budget spreadsheet, make the changes you want, and then import the final product into QuickBooks. For example, you might create next year’s budget from this year’s. Or, you might experiment with what-if scenarios with a bare-bones budget in case a client with shaky finances disappears and a second happy-dance budget if you snag that big new project.

There are three parts to working on budgets with Excel:

  • Export a budget with the accounts from your company file.
  • Edit budget values in Excel.
  • Import the final budget into your company file.

The following sections describe each step in detail.

Exporting Your Budget

Exporting a budget is the quickest way to create an Excel spreadsheet with the accounts from your chart of accounts and the additional information that QuickBooks needs to import the spreadsheet when it’s finished. This section begins with the steps for getting the budget ready to export, and then you’ll learn how to create the export file.

QuickBooks exports only accounts that contain at least one value. If you have a budget with numbers already, you can skip to the steps for creating the export file. However, if your budget is blank, you need to add at least one budget value to each account. Here’s what you do:

  • In the Set Up Budgets window (Company-->Planning and Budgeting-->Set Up Budgets), in the Budget drop-down list, choose the budget you want to add values to.
  • Fill in a value in the cell for January for each account in the budget. See the screenshot.
  • When all the cells in the January column contain values, click Save. Then, click OK to close the Set Up Budgets window.

Attached Image

Note: If you have more than one budget in your company file, you can’t export only the budget you want to work with—when you export QuickBooks’ Budgets list, the export file includes entries for every budget for every fiscal year. So if you created budgets for customers, jobs, and classes, you’ll get entries for those, too. When you work on the budgets in a spreadsheet, you can ignore the entries for those other budgets, but the best approach is to delete the extraneous rows so you can focus on one budget at a time.

Here’s how to export QuickBooks budgets:
  • Choose File-->Utilities-->Export-->Lists to IIF Files.
  • In this first Export dialog box, turn on the Budgets checkbox, and then click OK.
  • In the second Export dialog box, navigate to the folder where you want to save the exported file and, in the “File name” box, replace “*.IIF” with the name for your export file.
  • Click Save to export the budgets.

Working with a Budget in Excel

If your company file contains only one budget, you can open the exported file in Excel and start editing the budget values. On the other hand, if you exported several budgets, your work will flow more smoothly if you format the file. Here are the steps for opening and working on a budget in Excel:

  • In a spreadsheet program like Excel, open the exported file.
  • In Excel, to change the order of the budget rows to show budgets by fiscal year, select all the rows below the one that begins with !BUD (see the screenshot). Then choose Data-->Sort and sort the workbook first by start date (STARTDATE) and then by account (ACCNT).
  • To edit this file to represent only one budget, delete all the rows for budgets that you aren’t changing. The STARTDATE column contains the date that the fiscal year for the budget begins. So, for example, if you want to remove all the budgets except the one for fiscal year 2013, delete all the rows that have a date other than 1/1/2013 in the STARTDATE column.
  • If you want to use an existing budget to create next year’s budget, simply change the year in the STARTDATE column.
  • Use Excel’s Replace command (press Ctrl+H) to change the year to the new budget. For example, the budget entries for 2012 budgets include “1/1/2012” in the STARTDATE column. To create a budget for 2013, change the contents of these cells to “1/1/2013.”
  • To modify budget numbers, edit the budget values in the Excel worksheet.
  • In Excel 2010, choose File-->Save As. (In Excel 2007, choose Office-->Save As.) In the Save As dialog box, save the modified file with a new filename.

Attached Image

Note: If you create several what-if budgets in Excel, be sure to copy just the data for your final budget into a file for importing into QuickBooks. Compare the keywords and column headings in the Budget list export file to those in the file you plan to import to make sure that the data imports the way you want. For example, a row that begins with !BUD lists the keywords that identify columns, such as ACCNT for your accounts and AMOUNT for your budget values. (Rows for budget entries have to begin with a cell containing the keyword BUD.)

Importing a Budget into QuickBooks

After you create or edit your budget in Excel, importing the budget is easy. If you’re experimenting with several budgets for the same time period, you can import one and then run budget reports to examine it more closely. Just remember to finish by importing the budget you finally decide to use.

Here are the steps for importing a budget file:

  • To import the modified IIF file into QuickBooks, choose File-->Utilities-->Import-->IIF Files.
  • Double-click the name of the IIF file that contains your edited budget.
  • Choose Company-->Planning & Budgeting-->Set Up Budgets.

QuickBooks 2013: The Missing Manual

Learn more about this topic from QuickBooks 2013: The Missing Manual.

Your bookkeeping workflow will be smoother and faster with QuickBooks 2013. This Missing Manual—the Official Intuit Guide to QuickBooks 2013 for Windows—puts you in control: you get step-by-step instructions on how and when to use specific features, along with basic accounting advice to guide you through the learning process.

See what you'll learn

1 Reply

  Cosm_12776's Photo
Posted Apr 04 2014 06:22 AM

If you're interested in a quick tool to convert Excel files to Quickbooks, I recommend this free online xls to qbo converter.