Budget Controller User Manual

Please take a few minutes to read this guide before you start using Budget Controller. 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. In the unlikely event of the file becoming corrupted or damaged you will always have a backup available.

3. Starting Budget Controller

Before starting Budget Controller Excel needs to be configured to run macros. Budget Controller also uses the Analysis ToolPak Add-In. Please follow the installation instructions for your version of Excel.

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

4. Example Data

The example data supplied with Budget Controller is for a company updating their 2002 forecast from October until December, creating a new budget for 2003 and a strategic plan from 2004 until 2007. We suggest you keep this data in the original version for future reference but clear it from the copies you will be working from. See Entering Data for a quick method of clearing data from the spreadsheets.

5. Design Conventions

The following conventions have been used throughout Budget Controller:

  • White cells indicate user data entry areas. All other cells are protected and cannot be changed. The only exception are white cells in the first year. If the forecast starts after the start of the fiscal year, cells for periods before the start of the forecast change to the background colour to indicate data isn't require. You can add data to these cells; Budget Controller will ignore it, however.
  • Yellow cells indicate non-critical errors and warnings.
  • Red cells indicate critical errors that should be corrected before continuing.
  • Numbers too large to display appear as #######. Hover over the cell with your cursor and the number will appear on the screen.

6. Forecast Limit

Budget Controller can forecast a maximum of six years in each file. However, as the forecast approaches its limit, inventories may decline in the last few months. This happens in months were inventories are based on assumptions in the Production Schedule which fall outside the forecast range.

If you need to forecast a full six or more years, you can do it by dividing the forecast into two or more five year forecasts. Simply enter the closing balances from the workbook with years 1 to 5 as the opening balances of the workbook with years 6 to 10.

We recommend you always forecast a few extra months to ensure your purchasing requirements and stocks are accurate right up to the official end of your forecast.

7. Navigation

Worksheets are generally large and won't even fit on the widest screens. Apart from the standard Excel scroll bars and keyboard controls, there are various ways you can move around the worksheets to find what you want:

Home button Home Wherever you are in Budget Controller, the Home button will always take you back to the main menu.
Next button Next The Next button will take you from your current worksheet to the next worksheet on the main menu.
Previous button Previous The Previous button will take you from your current worksheet to the previous worksheet on the main menu. Not to be confused with the back icon in Microsoft's Internet Explorer, which takes you back to your last location.
Hide buttonShow button Hide/Show The Hide/Show buttons decreases or increase the level of detail on the screen so you can view or print your own summarised reports.

The buttons in the top right of the worksheets Show or Hide ALL details. Buttons along the top and sides only show or hide associated sections of the worksheet.
Drill down button Drill Down Click to open the database to analyse underlying data or to add or edit comments. To find the information you are looking for, click the arrows on the database header fields to filter the data. For example, select a specific month and/or product to analyse their details.

Tip: For graphs hover over data plots to reveal the underlying values.
Return button Return Click to return from the detailed database.
Help button Help Red triangles 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.
Links symbol Links When you move the cursor over underlined menu descriptions the cursor changes into a hand when there is a link to follow.

Tip: The quickest way to find what you want, is to summarise both columns and rows with the Hide All buttons at the top right-hand corner of each sheet, then open just the part you want to work on with the Show buttons.

Note: Sheet tabs have been turned-off by default to reduce the clutter on your screen. You can change the defaults from the Excel menu Tools/Options/View.

8. Entering Data

There are various ways to enter and manipulate data in Budget Controller:

  Direct Method The simplest way is to type the values directly into the white cells.
Distribution method button Distribution Method
This simply copies the value in the Distribute Across Year field to each month and category in the year. You can then edit months or categories that differ from the default value.

You can also use this method to clear data by deleting the value in the Distribute Across Year field and copying the blank field by clicking the icon.
Distribution method button Multiplier Method The data you have already entered will be multiplied by the factor in the Distribute Across Year field. This can be used in 2 ways:
  1. By creating a distribution matrix in the month/category fields you can distribute a total across the year, based on a seasonal and/or product-share index.

    Select the Sales Index Utility from the Main Menu. Use this worksheet to create your index. Recalculate by pressing F9 and copy/paste the matrix to your forecast. Be sure to use copy/paste values (see Tips & Tricks below). Add total yearly sales divided by 12 in the Distribute Across Year field and click the Copy/Multiply icon. Budget Controller will multiply the monthly sales figure by each value in the matrix to reflect your distribution matrix.


  2. You can also use the Multiplier Method to increase or decrease your existing data by a constant value. For example to increase sales by 5% enter 1.05 in the Distribute Across Year field and click the Copy/Multiply icon. Alternatively, 0.95 would decrease sales by 5%.

9. Adjustment Fields

Use the Adjustment fields to perform "what-if" scenarios on your forecast. Whereas the Copy/Multiply function changes the actual data, the Adjustment fields leave your original data unchanged allowing you to quickly see the implications of an event on your forecast without loosing your original assumptions.

For example, to increase sales by five percent, enter 5 in the Adjustment field. Alternatively minus 5 will decrease sales by five percent. Budget Controller will change the data in the adjusted sections, leaving your original data unchanged in the unadjusted sections.

You can use this method to remove the effect of a revenue or cost stream from your forecast by entering minus 100%. Simply delete the adjustment value and recalculate the workbook to return to your original scenario.

10. Deleting Data

Select the data to delete and use the Del key to remove it from your forecast.

Caution! Don't use the Edit/Clear option on Excel's menu as this can delete the conditional formatting used to flag errors.

11. Budget Structure

Every effort has been made to make Budget Controller 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

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

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.

    Your own classification of variable costs is determined by the revenue streams you chose in the Sales worksheet. This is to ensure consistency in the Cost of Sales report allowing you to analyse the profitability of each revenue stream.

  • 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.).

    Note: Manufacturing Fixed Costs are also determined by your chosen sales revenue streams to ensure consistency in the Cost of Sales report. The remaining fixed costs can be classified individually in anyway you find useful.

  • 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. 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

12. Creating Your Budget/Business Plan

12.1 Enter Start Dates

Select the month your fiscal year starts and the start date of your current forecast. Recalculate Budget Controller with F9 before continuing.

Caution! Wait until the recalculation is complete before moving around the workbook, as this can interrupt the process. You can see the recalculation status on the left of the Status Bar at the bottom of the screen.

Note: To speed up data entry, calculation is switched to manual by default. In addition, manual calculation is useful; it enables you to select a particular report and see the effects of changes made in other worksheets. When you press F9 you can watch the impact of the changes on the report.

If you want to change calculation to automatic go to the Excel menu: Tools/Options/Calculation, and click on Automatic.

12.2. Enter Opening Balances

Use the closing balances from the month immediately preceding the start of your forecast. Opening balances are required in the following worksheets:

Capital Investments Add the acquisition value and accumulative depreciation for your fixed asset categories, together with the outstanding balance of unpaid investments.
Financial Add equity, bank overdraft, loan and investment opening balances.
Tax Enter any past losses that are tax deductible.
Provisions
Operational
Non Operational
Extraordinary
Enter opening balances for any provisions made prior to the forecast, excluding provisions for bad debts which should be entered in the balance sheet.
Balance Sheet With the exception of those entered above, enter the remainder of the balance sheet values. If the forecast doesn't start at the beginning of the fiscal year, enter last year's values as well. Budget Controller needs these to calculate the cash-flow during the first part of the forecast year.
Income Statement If the forecast doesn't start at the beginning of the fiscal year, enter the year-to-date values up to and including the month before the forecast starts in the Income Statement.
Cash Flow The Cash Flow Statement obtains its opening balances from the Balance Sheet and Income Statement but an optional comparison with last year's closing balances can be entered if required.
Collections & Payments Phase out the Accounts Receivable and Payable opening balances.

12.3 Check Opening Balances

Recalculate Budget Controller. If Check List in on the Main Menu is flagged red, errors exist in your opening balances. Select Check List to see the reasons for the errors.

12.4 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 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 will treat the value as positive or negative depending on the logic of the account description. Negative values can be entered if you want to reverse a specific movement. For example, a large sales credit note is to be issued which will reduce a particular product's monthly sales to less than zero.

12.5 Check Your Results

Recalculate and check results.

12.6 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. There is no restriction on the number of copies you make: Budget Controller is licensed by the number of users not the number of copies.

12.7 Print Your Reports

Use the Show/Hide buttons to select the level of detail require before printing. For reports with quarterly totals, select 1 (Yearly), 4 (Quarterly) or 12 (Monthly) to fit the reports on a page.

All worksheets are designed to print one year per A4 page or several years if the data has been summarised. 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.

13. Main Menu Options & Spreadsheet Notes

13.1 Sales & Costs

Add your forecast in the appropriate sheets for sales and operating costs. Costs are divided between Function, Behaviour and Type (see above).

13.2 Other Assumptions

Other assumptions you will need to consider include:

  • Production Schedule

    For manufacturing companies the Production Schedule provides a simple but powerful tool to forecast purchasing requirements and stocks using production lead-times.

    The lead-times are based on the total time it takes to sell the product, starting from the time the raw material is received , through production, and until the finished product is issued from stock as a sale. It includes raw material stock, work-in-progress (W.I.P.) and finished product stock.

    If production takes place in the same month as the product is sold, enter zero as the lead-time. If it takes one month to produce enter one, if it takes two months, enter two, etc. Budget Controller will use this schedule to calculate how many months in advance of forecast sales the production resources are required. For example, if 3 is entered in April, Budget Controller will receive or reserve resources in January (April = 0, March = -1, February = -2, January = -3.

    If lead-times takes the production start date to before that of the forecast, Budget Controller will ignore this production, assuming it is either work-in-progress or already in stock and, therefore, already accounted for in the inventory opening balances.

    Service companies can enter zero to reduce stocks to zero. If, on the other hand, your company accounts for work-in-progress, you can use the lead-times for jobs that take over a month to complete.

    Caution! If you're forecasting a new company with no initial stocks, be careful entering lead-times in the first few months to avoid selling products which theoretically are produced before the company started operating!

    Similarly, stocks will decline to zero or below in the last few months of the forecasts as there are no sales beyond Budget Controller's six year limit. If you need to forecast a full six or more years, you can do it by dividing the forecast into two or more five year forecasts. Simply enter the closing balances from the workbook with years 1 to 5 as the opening balances of the workbook with years 6 to 10. Alternatively, if you only need a full 6 years, use the Inventory & W.I.P. Other Increases field to manually increase the stock value.

    Negative stock balances are flagged in red and are often the symptom of the above problems.

  • Inventories & Work-in-Progress (W.I.P.)

    Total stocks, including work-in-progress is derived from the Production Schedule above.

  • Collections & Payments

    The way you collect your receivables and pay for your purchases will have a major impact on your cash-flow. The Collections & Payments Schedule is a powerful tool to help you forecast cash receipts and disbursements. It is also highly flexible, from the most simple scenario: receiving/paying everything as cash on delivery, to the most complex with up to six user defined terms of credit.

  • Capital Investments

    Add you forecast for the purchase, payment and depreciation of fixed and intangible assets.

  • Financial

    Financial assumptions allows you to forecast changes in equity, loans and investments required to finance your operations. Budget Controller can automatically calculate borrowing requirements and investment strategies based on user defined triggers. Alternatively, you can introduce your assumptions manually.

  • Tax

    Tax payment rules vary between countries. Some tax authorities require advances to be paid in certain months during the year based on last years earnings with an adjustment being made when the actual tax settlement is calculated. Budget Controller allows you to forecast tax payments, refunds and advances in the Tax worksheet.

  • Provisions

    You can include provisions in your forecast that effect your Income Statement but not your Cash-Flow. Provisions are classified as Operational, Non-Operational or Extraordinary. Within each classification you can define your own types of provision: Restructuring, Environmental, Complaints & Disputes, etc.

13.3 Reports

View or print the results of your forecast.

13.4 Utilities

There are various tools to help you create your forecast and maintain the integrity between the opening balances from your actual accounts and the start of the forecast.

14. Tips & Tricks

14.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.

Note: The Enter key will paste formats so please take care not to use it inadvertently.

14.2 Add Your Own Formulas

Use the full power and flexibility of Excel to do calculations in data fields. Consider the following example:

Some companies report net sales, i.e. sales - distribution costs. In the Sales worksheet you could have one of the sales categories for costs and link it to total sales as a negative percentage.

Adding formulas to your budget

In this example the cost of distribution is 25% of total sales. The negative percentage turns the sales into costs so the value is subtracted from total sales to give Net Sales. In cell L5 enter the equation =SUM(C5:K5)*-0.25 and copy it to the L6 - L16. This creates a dynamic link between sales and distribution costs. Every time you change sales, distribution costs will change accordingly.

14.3 Consolidate Workbooks/Files

Excel allows you to link multiple spreadsheets from different workbooks/files together. Say, for example, you use Sales Controller for sales forecasting, you could link it to the Sales spreadsheet so Budget Controller 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.

14.4 Exporting Data to Access

To export data to Microsoft's Access or another database application such as your corporate accounting package, the data needs to be arranged in a tabular format like the Purchasing Database.

The Budget Controller Database Utility contains sales and costs already tabulated to help you in this. It can be downloaded, free of charge, from:

Budget Controller Database Utility

If you want to export data to Microsoft Access do the following:

  • Download the database utility to a convenient location on your computer.
  • You may receive a standard warning about the danger of viruses. Click to continue; all Markitsoft's software has been scanned against viruses.
  • To extract the file (unzip) right click the folder and choose Extract all...
  • Copy the utility to the same folder as Budget Controller.
  • Open the utility and enter Budget Controller's new file name if you have changed it.
  • Recalculate and check the databases are consistent with your forecast.
  • Make a note of the field names in the database you want to export.
  • In Microsoft Access, create a table with the same fields as the Budget Controller database, or simply link directly to the Excel spreadsheet.
  • Select the data in Budget Controller and click Copy.
  • In the Access table choose Edit/Paste/Append.

14.5 Pivot Tables & Pivot Charts

Perhaps one of the most useful and powerful features of Excel is the Pivot Tables & Pivot Charts report. It enables you to analyse your data in whichever way suits you best for the particular job in hand. Its flexible, interactive and quick to create.

To get the full benefit from Pivot Tables and Charts, your data needs to be in a tabular format. The Purchasing Database or The Budget Controller Database Utility can be used for this. Alternatively, create your own spreadsheet, linked to Budget Controller, for this purpose.

The Budget Controller Database Utility contains two example Pivot Tables and Charts which you can use or change as needed. The file isn't protected so work from a copy in case you accidentally damage the original.

For more information on Pivot Tables and Charts see Microsoft Excel's Help (F1).

14.6 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.

14.7 Yearly Budgets

If you don't need a periodised forecast, simply divide the yearly values by 12 and copy them through the year. Use the Hide buttons to summarise the data into a yearly forecasts.

14.8 Weekly Budgets

Some companies do weekly forecasts. The months in each quarter are based on 5 + 4 + 4 or 4 + 4 + 5 weeks, i.e. each quarter has 13 weeks (4 x 13 = 52 weeks). Budget Controller, has 10 user defined revenue or cost streams per month. These can be classified in any way you want. You could for example use columns 1 to 5 for a weekly forecast of product A and columns 5 to 10 for a weekly forecast of product B. If you need to forecast more than 2 products on a weekly basis you could link several workbooks together as explained above. Each revenue/cost stream appears in Budget Controller's databases so they can be easily tracked and followed-up.

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.