Budget Controller Lite User Manual

Please take a few minutes to read this guide before you start using Budget Controller Lite. It contains important information that you will need to get the most out of the product. We hope this tool proves useful for creating and maintain your budgets, forecast and business plans. We welcome any feedback you have and will endeavour to incorporate your suggestions into future releases.

1. Introduction

This Guide will take you through the steps necessary to create an interactive financial model of your organisation. Once created, we recommend you update it with actual data on a regular basis. This will allow you to quickly create rolling budgets, to do “what-if” simulations and sensitivity analysis to help you explore alternative scenarios and strategies and understand their impact on your business.

2. Work from a copy

We strongly recommend you work from a copy of Budget Controller Lite. In the unlikely event of the file becoming corrupted or damaged you will always have a backup available.

3. Starting Budget Controller Lite

To start Budget Controller Lite double click the Budget Controller Lite Excel file. You may be warned that the file contains macros and macros can contain viruses. Select Enable Macros to continue.

4. Design Conventions

The following conventions have been used throughout Budget Controller Lite:

  • White cells indicate user data entry areas.
  • Numbers too large to display appear as #######. Hover over the cell with your cursor and the number will appear on the screen.
  • Red triangles in the top right hand corner of a cell indicate the existence of help and tips on worksheets. Hover over the triangle and a pop-up box will appear. Alternatively use the Excel View Comments option.

5. Budget Structure

Every effort has been made to make Budget Controller Lite as flexible as possible. Being a high-end financial modelling tool, the detailed chart of accounts found in database budgeting applications has been replaced with a simpler, more generic structure which should be applicable to most organisations.

Sales

One revenue streams can be entered in each copy of Budget Controller Lite. If you need more, multiple copies of Budget Controller Lite can be linked together to consolidate sales from different companies, division, regions or departments (see Tips & Tricks below) or consider upgrading to the Full Version.

Costs

You can classify your costs in various ways:

Cost Behaviour

The way costs behave in relation to sales activity and time enables them to be classified as fixed or variable. It is a fairly arbitrary classification and depends greatly on the nature of your business and the time frame in question.

  • Variable

    Variable costs change with sales. Costs are expressed as a percentage of sales and so values will vary proportionally. Once entered, any changes made to sales will automatically be reflected in changes to variable costs. The variable costs only need altering if there are changes in your cost structure: changes in manufacturing techniques or prices of raw materials for example.

  • Fixed

    Fixed costs don't change with sales. Administration costs are generally considered fixed when analysed on a monthly basis, although over the life of an economic cycle there is inevitably a variable component as the business expands and contracts with market and economic conditions.

Functional Use

Its normal to classify costs by where or how they are consumed in the business.

  • Manufacturing Costs

    The cost of producing goods or services. Also called Cost of Sales (C.O.S.).

  • Sales & Marketing (S&M)

    The cost involved in getting people to buy or use your goods and services. This often (but not always) will include distribution costs.

  • General & Administrative (G&A)

    Typically costs associated with support functions like accounts, personnel and general management.

  • Research & Development (R&D)

    The costs of inventing better goods and services to offer to your customers.

  • Other Costs

    Use Other Costs to add your own functional classification.

Operational Significance

The operational significance of a cost is used to rank it in order of relevance and is used to define different levels of profitability.

  • Operational

    Costs associated with the normal running of the core business.

  • Non Operational

    Costs not directly associated with an organisation's core business but nevertheless considered normal in that they occur frequently.

  • Extraordinary

    Costs associated with rare or one-off events or adjustments.

Cost Type

For statistical analysis it is often useful to classify costs by their physical characteristics:

  • Materials
  • Labour
  • Expenses

Cost Timing

Costs are often accounted for before they're actually incurred. They take the form of provisions for known or perceived future events that are likely to have a material impact on the company. It's important to separate these provisions from the other costs, not only for calculating cash-flow, but also to provide an assessment of the risks and reliability of the forecast.

Cost Classification Summary

You can use any combination of the various types of cost listed above. Some cost types may not be appropriate for your business and can be ignored, but be sure to clear the worksheets of any example data shipped with Budget Controller Lite. The table below gives a summary of the possible combinations available:

Function Behaviour Significance Type Timing
Manufacturing Variable
Fixed
Operational Materials
Labour
Expenses
Actual
Sales & Marketing Variable
Fixed
Operational Materials
Labour
Expenses
Actual
General & Administration Variable
Fixed
Operational Materials
Labour
Expenses
Actual
Research & Development Variable
Fixed
Operational Materials
Labour
Expenses
Actual
Other Costs Variable
Fixed
Operational Materials
Labour
Expenses
Actual
Other Items Not Applicable Operational
Non Operational
Extraordinary
Not Applicable Actual
Provision

6. Creating Your Budget/Business Plan

6.1 Enter Start Dates

Select the month your fiscal year starts and the start date of your current forecast.

6.2. Enter Opening Balances

Use the closing balances from the month immediately preceding the start of your forecast.

6.3 Enter Your New Budget/Business Plan

Return to the Main Menu and go through each sheet entering your new assumptions as you go.

Budget Controller Lite ignores data entered in months prior to the start of the forecast. These values can either be left for future reference or cleared if no long required.

Unless instructed otherwise, enter positive values. Budget Controller Lite will treat the value as positive or negative depending on the logic of the account description.

6.4 Check & Save Your Results

You use the Excel menu to save your work like any other Excel file. You can use Save As to change the name to something more appropriate than Budget Controller. If your using old versions of Excel you will receive the following message:

This file was created using a later version of Microsoft Excel.

If you save the file using Microsoft Excel 97, information created with features in the later versions may be lost.

Click Yes to continue.

6.5 Print Your Reports

All worksheets are designed to print one year per A4 page. However, there are small differences between printers in terms of their maximum printable area. If your reports don't fit, go to the Excel menu File/Page Setup/Margins and try reducing the left margin. If that doesn't work, go to File/Page Setup, Page and try adjusting the size until it fits.

7. Tips & Tricks

7.1 Copy/Paste

Use Excel's Copy/Paste functions to enter data more quickly.

Caution! When pasting data, always use Paste Special/Values or Formulas to avoid copying cell boarders and formats to other cells. If you forget, use the Excel Undo icon. We recommend you add the Past Values icon to your toolbar. To add the icon, do the following:

  • Select View/Toolbars/ ....Customize from the Excel menus
  • Select Edit from the list of Categories in the Customize window.
  • Scroll down the list of icons to Paste Values
  • With the mouse drag and drop the icon onto your toolbar next to the Copy icon.

To remove the icon, drag it back to the Customize window.

7.2 Add Your Own Formulas

Use the full power and flexibility of Excel to do calculations in data fields or link to other spreadsheets (see below).

7.3 Consolidate Workbooks/Files

Excel allows you to link multiple spreadsheets from different workbooks/files together. Say, for example, you use the Sales Controller Lite template for sales forecasting, you could link it to the Sales spreadsheet so Budget Controller Lite updates automatically.

The simplest way to link cells in different workbooks is to open both workbooks, then enter + in the cell where you want to consolidate the data, click on the cell in the other workbook to add the link, enter + to add a second cell, and so on. See "Create a link between cells in different workbooks" in Microsoft Excel Help (F1) for more information on linking cells.

You could use this method to consolidate several versions of Budget Controller. Each file could represent a different company or region, for example, which would then automatically update the Group budget.

If you don't want a dynamic link between different workbooks you can also use the Copy/Paste Special/Add to consolidate data from various sources.

7.4 Calculator

You can use the Status Bar at the bottom of the screen as a calculator. Use your mouse to select the range of data; the total will appear on the right-hand side of the Status Bar. To add several disconnected cells, for example the 1st and 2nd quarter's net profit, hold the Ctrl key down and click on the cells to add.

Further Information

For further information refer to FAQs (Frequently Asked Questions) or contact us directly. We would be happy to arrange on-site training or consultancy services tailored to your specific needs.