The Budget Compiler QuickBooks. Apply and Format Profit Loss Data From QuickBooks Into Excel

The Budget Compiler QuickBooks

Microsoft Excel - From Beginner to Expert in 6 Hours

Apply and Format Profit Loss Data From QuickBooks Into Excel

Budget Compiler

The Budget Compiler will apply and format your Profit and Loss data from QuickBooks. Using this information it will compile a flexible Excel Profit and Loss report allowing you to select the amount of detail displayed and use the data for performance review and budget formulation.

Applying your existing data in a flexible and easy to read layout with the capacity to automate budget creation provides substantial benefits. These include an improved understanding of business operations, enhanced budget development, and significant time saving in performance reviews and budget creation. Display view options include by Month, Quarter, and Full Year with Current Year, Last Year, and Budget values. Charts display both Actual and Budget values.

The Complier will import and format your monthly Profit and Loss data from QuickBooks. Using this information it will compile a flexible Profit and Loss structure in Excel allowing you to select the amount of detail displayed, use the data for budget formulation and create a file (Imp.iff) that can be imported into QuickBooks to automatically set up a budget. Actual and Budget charts will also be generated. Sample files are included with the software, just click UpdatePL to see them compiled.

Place your monthly QuickBooks Profit and Loss data in a file named PL.csv in the same directory as the Compiler file. To do this from QuickBooks go Reports, Profit & Loss, Standard, set Dates to the required Full year and Columns as Months to show each month of the year. You must use a full year not YTD. To save this as an Excel file go Print, select File, and file type Excel/Lotus spreadsheet then click Print. Save the file as PL.csv in the same directory as your Compiler file. Items with zero value should use "0" not a blank. If you wish to make budget to actual comparison's in your Compiler you should ensure all active accounts are included in your report, from the displayed report go Customize, Advanced, and set Display Rows to Active. If Active Rows are not used or new accounts are added there may be a mismatch between budget and actual data sets. The PL.csv file should be closed when you click Update PL, it is controlled by the Compiler.

When you click Update PL your QuickBooks data for each month is imported into the Compiler. The last month with values is set to the Current Month. Formula are set to determine Quarter, Year to Date, Full Year, Variance, and Percentage values. You control the level of detail displayed by selecting the required views and then clicking Update View. You can add new data by repeating the process, all old data will be replaced. Reset will clear all added data returning the Compiler to its initial state. Chart transfers the current Compiler Profit and Loss data to Table Data for display by the Charts.

New Budget copies your data to a new file and saves it as "New Budget.xls". You must rename this file before you build another New Budget. The New Budget file includes a Relative Percentage column (select New Budget % and then Update View to display) for inputting year on year changes to calculate new budget values. Relative Percentages are input in the blue cells. New budget values are calculated as Relative Percentage X Month Actual. A value of 100% indicates the Month Budget value will equal the Month Actual value. If you do not have a full years Month Actual data you can copy your current budget data or use forecast data. You can also manually input values or formula directly into the budget month cells. When you adjust the Relative Percentages budget values are adjusted.

In general, Relative Percentages for Income line items should be based on your forecast for the following year (considering market and business forces) i.e. 110% for a forecast increase of 10% . Fixed Cost line items should be set at 100% (these do not change with variations in Income), Variable Cost line item Relative Percentages should be the same as the total Income change i.e. if Total Income increases by 10% then the Relative Percentage for each Variable Cost should be 110% (as Income changes Variable Costs change). If the business structure or operational procedures will differ in the budget year from current then the Relative Percentages should consider these.

When you are satisfied with your values click Apply. This will hide the Relative Percentage column, Budget Values will be set (formula replaced by values), Month Actual values moved to the Last Year Actual and Actual Month values cleared ready to UpdatePL with your new years data. It will also create an Import file Imp.iff containing your new budget data. This file can be imported into QuickBooks. From QuickBooks go File, Import, IFF, and select the Imp.iff file. Once imported the new budget data will appear in your QuickBooks budget. Once you have applied budget data you must Reset the file if you wish to clear budget data for a new P & L.

The way your QuickBooks report is structured will impact on Compiler formatting. Descriptions are set from the first column of the PL.csv file and Months from the first row that includes "Total" in a cell up to the 50th column. Formatting data is done by finding the first description including the word "Income" and setting this as the first data line and the description "Net Profit / (Loss)" or "Net Income" and setting this as the last data line. Formatting is applied from the first data line down. If a description includes "Income/Expense" and the row has no values it is set as a Title (bold, aligned left). If a description includes "Income" and the row has no values it is formatted as a Heading (shaded yellow). Once a Heading has been identified following line items without values are formatted as Sub-Headings (bold, no shading). Sub-Totals (bold, underlined, no shading) are set when their description includes "Total" and it matches with a Sub-Heading. Total lines (shaded gray) are identified when their description includes "Total" and it matches with a Heading (Cost of Goods Sold and COGS are treated as a match). Profit and Net lines (shaded green) are identified by the words "Profit" or "Net" in their description. Once a Total, Profit or Net line has been identified a new Heading can be set. "Total Income" identifies the sum of all operating income and is used in percentage calculations. QuickBooks data including totals are imported as values.

When you build a New Budget total, sub-total, profit, and net formulas are set however they should be checked and adjusted as required to ensure they are in line with your P & L structure. For automatic formatting and formula your QuickBooks report should include the description "Total Income" for the operational income total line. All other total lines should include the word "Total" with the Heading or Sub Heading description. Net lines should include the word "Net" with the Heading or Sub Heading description. Profit lines should include the word "Profit" and refer to income and expense lines above them.

This software is in Microsoft Excel format. To run this software you must have Excel installed on your computer and macros must be enabled.

If the file you downloaded has an exe extension (***.exe) it includes a Setup program. To install run the Setup program and follow the prompts to unpack and install the software. You can then run the software from the program icons or by simply opening the Excel file (***.xls).

If the file you downloaded has a zip extension (***.zip) it is packaged in a standard compressed zip file. After download it must be unzipped. Trial Zip software is available free from www.winzip.com . You can then run the software by simply opening the Excel file (***.xls).

If the file you downloaded has an Excel extension (***.xls) it is a standard Excel file. You can run the software by simply opening the Excel file (***.xls).

The first time you open the Excel file a Welcome page is displayed, clicking the Access button on the Welcome page displays the software input sheet. For the software to function macros must enabled

No system files are changed when you use this software. To uninstall run the Uninstall program or simply delete all associated files.

Non-Commercial License:

1 user	$33.00 USD
2+ users	$23.10 USD

Commercial License:

1 user	$99.00 USD
2+ users	$69.30 USD

Microsoft Excel - From Beginner to Expert in 6 Hours

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft