Sample Using Microsoft Excel and Budgets

Sample Using Microsoft Excel and Budgets

 

Overview

 

Annual budgeting in QuickBooks can be an easy way to compare actual results with the plan.  The budget can be entered by account, customer:job, or class.  You can enter an amount for two of the three, but you cannot enter a budget for all three at the same time.  Once you enter the budget for the first month, the amounts can be calculated and filled in by the software for the remaining months by either 0% change (flat budget for the year), a percentage increase/decease, or a dollar amount increase/decrease.

 

Prior to setting up the budgets, consider which reports will be most important to ensure that the data is entered correctly when creating the budget figures in the software.  For example, if the report to be created is the Profit and Loss Budget Versus Actual report by time period, you must set up the budget figures without a customer:job or class.

 

Since the budget is technically a list, it is possible to export or import the list.  The advantage to this approach is the ability to use Excel to calculate the budget (permits formulas between accounts where as the data entry function described above only calculates as compared to the month before).

 

For Premier, version 2003 there is a new feature that permits creating a budget automatically within the software based on the historical data. 

 

Creating a Budget in Excel from QuickBooks Data

 

To actually calculate the budget for the year, using the Excel interface available in QuickBooks Pro and higher is very helpful.  Below are the steps to use the actual results for the year as a base line for the new budget.

 

  1. 1.      Reports > Company & Financial > Profit & Loss Standard
  2. 2.      Set the date range at the top of the report
  3. 3.      Change the columns at the top of the report to month
  4. 4.      Click on the Excel button to send the report to Excel for additional calculations.  From within Excel, it is now possible to insert formulas to calculate the budget for the next period.

 

This is the most efficient way to create the amounts to be entered into the new budgets since there is not a way to calculate one account balance based on another from within QuickBooks itself: The only calculation is the fill down feature.  For example, if the payroll figure for the next period has been estimated, it is not possible in the budget figure itself to calculate a percentage of that amount for payroll taxes, workers compensation insurance, etc.  Within Excel, however, simple formulas can be used to efficiently accomplish the calculations.

 

Importing the Budget into QuickBooks

 

The process of importing the budgets includes several steps, but it is still more efficient than completing the data entry of all accounts into QuickBooks.

 

  1. 1.      Complete the data entry for one account.
  2. 2.      Choose File then Export for the budget list from QuickBooks.
  3. 3.      In Excel, open the exported budget list (the file name will be *.iif) so it is necessary to change the file type to all in order to find the file.
  4. 4.      Copy and paste the header information to the appropriate columns in the Excel spreadsheet created for the budget that will be imported into QuickBooks.
  5. 5.      Save the file as *.iif.  The extension of “iif” is critical to be able to find the file to import into QuickBooks.
  6. 6.      Open QuickBooks and import the file.
  7. 7.      Create a report, or view the budget to double check that everything was imported properly.  If it was not, the header information is typically the problem, so return to the exported file and confirm that everything was entered correctly.

 

TIP: Results can be analyzed as either reports or graphs in QuickBooks or Excel.

 

TIP: If you choose to do the budgets by class, begin with one of the standard reports, then change the columns option to class.

 

TIP: To provide an opportunity to assist your client while increasing billable hours consider approaching the idea of budgets without mentioning that word specifically.  The conversation could go something like . . . What do you anticipate the business doing in sales this year (increase, decrease, flat)?  How about in expenses, do you anticipate any significant changes?  I would like to take the information we have discussed to see how that will affect your business in the upcoming year.  The result will be your ability to track how you are doing throughout the year and we can do more effective tax planning.  It has been the experience of this author that the change in the profitability and cash flow of the business subsequent to developing a written plan or budget is unbelievable.  The simple function of thinking and talking about it in financial terms as well as paying attention to reports (and therefore details) that were previously ignored result in exponential improvements.

 

TIP: If the budget from year to the next is going to be the same, simply export the budget list, open it in Excel, change the date column, close and save the file, and import it back into QuickBooks.

More information on this topic

Managing Lists