Help - Budget Compiler QuickBooks Excel

Version 20 - The latest version is always available from the Software and Models folder at www.bizpeponline.com

Software is provided as shareware with a free 7 day evaluation period. If you find this software useful or maintain it beyond the evaluation period payment of US$33.00 is required. Payment is online at http://www.bizpeponline.com/Purchase.html

Overview
Instructions
Scope
Evaluation
Install / Uninstall
Purchase
License
Software Support
Software Design Service
Intellectual Property
Hold Harmless
Distribution
Contact Details

Overview

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.

Instructions

Summary

Update the compiler with your Profit and Loss data from the PL.csv file (exported from QuickBooks).
Display your data in the compiler by selecting the check boxes to identify the level of detail and then click Update View
New Budget creates a new file in which you can setup your budget data by inputing percentage changes in the New Budget Relative Percentage Change column (blue cells). You can also input values or formula directly into the budget month cells.
Apply sets your New Budget input as values, moves Month Actual Values to Last Year Values, and Month Actual Values ready to Update with the new years data. It also creates a Imp.iff file containing your budget data that can be imported by QuickBooks.

Update

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.

Display

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

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.

Apply

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.

Format Requirements

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.

If your report structure does not align with this layout we can modify the formatting algorithms to suit your specific requirements, please email mail at bizpeponline.com.

Scope

Conditions of use apply to all Bizpep and bizpeponline.com services, software, and information presented. They cover Bizpep, bizpeponline.com and all principles, employees, distributors and other associated parties. Use acknowledges acceptance. Full copyright is maintained by Bizpep.

Evaluation

Software is provided free for 7 day evaluation. If you find this software useful or maintain it beyond the evaluation period payment is required. Print and Save functions are restricted during Evaluation. All other features including cell and macro protection are the same in evaluation and registered software. All non-input cells and macros remain protected to ensure the integrity of the formula and programming.

Install / Uninstall

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.

Purchase

If you find this software useful or maintain it beyond the evaluation period payment is required. If payment is not provided all software must be deleted. Payment is online at:

http://www.bizpeponline.com/Purchase.html.

Upon receipt of payment Bizpep will provide by return email registration code to fully enable evaluation software. Registering software removes restrictions on Print and Save functions. All other features including cell and macro protection are the same in evaluation and registered software. All non-input cells and macros remain protected to ensure the integrity of the formula and programming. When you have your registration code click the Registration Button on the Terms and Conditions or Thank You sheet and follow the prompts.

License

Payment gives you license to use software on one system, for one user. The license is for non-commercial private and internal business use only. This license does not cover the commercial use of software for inter-business support, advice, or consulting. For details on Commercial and Multi-User licenses please visit http://www.bizpeponline.com.

Software Support

Bizpep offer support for operation and application of registered software. Simply email your registration details and an outline of your question to support at bizpeponline.com

Software Design Service

If this software does not fully encompass all your requirements consider our Software and Model Design service. Designing a solution specifically to cover your needs can provide increased functionality and flexibility. Software and Model Design details are available on our web site http://www.bizpeponline.com or send an email to mail at bizpeponline.com.

Intellectual Property

Bizpep retain all intellectual property rights. It is prohibited to use the software, concepts, formats, and systems obtained from Bizpep in any manner outside their intended use as designed and defined by Bizpep. You may not modify, translate, reverse engineer, decompile, disassemble or create derivative works based on Bizpep software, services or concepts.

Hold Harmless

Bizpep provide tools to support business. The specific suitability of these tools must be independently assessed. Services including software, concepts, formats, and systems are provided as is and with no warranties of any kind, whether expressed or implied. The user must assume the entire risk of using Bizpep software or services, and agrees to hold Bizpep harmless from any claims or losses relating to service or software use. In no event shall Bizpep, its principals, distributors, employees, or other associated parties, be liable for any incidental, consequential, or punitive damages whatsoever relating to software or service use. In addition use of software and services is entirely at the users risk, and use acknowledges that Bizpep is held harmless from any claims or losses relating to software or services provided.

Distribution

You may freely distribute this software in evaluation form without registration codes. Any software distributed must require the user to provide payment to Bizpep to maintain software beyond the evaluation period as originally designed. You may not sell or require payment in any form for Bizpep software. Evaluation software must be distributed without cost or penalty to the user.

All rights of any kind which are not expressly granted are entirely and exclusively reserved to and by Bizpep.

Contact Details

For software support email: support at bizpeponline.com.

Additional business support software and design services are available at:
http://www.bizpeponline.com

Bizpep
15 Island View Court
Queensland, 4556, Australia.
ABN: 24 312 166 103

Web Site: www.bizpeponline.com

Regards
David Morcom
bizpeponline.com