Budget Controller Corporate Edition User ManualPlease 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. Also, we recommend you take a look at the online demo to get an overview of how the product works. 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. IntroductionThis 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. Work from a copyWe 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. If you purchased the CD version, keep your CD as the back-up. It is write protected so when you copy it to your hard disk you need to remove the read-only property. From the File/Open menu, right click on the file you copied to your hard disk. Select Properties and then remove the tick from the Attributes: Read-only field. Starting Budget Controller Before running Budget Controller check that Excel's macro security is set to Medium. From the Excel menu select Tools/Options. Click on the Security tab and click the Macro Security button in the bottom right hand corner. The Macro Security window will open; select Medium or Low security. Low should only be used if you have anti-virus software installed and you trust all your software suppliers. To start Budget Controller double click the Budget Controller Excel file. You will be warned that the file contains macros and macros can contain viruses. Select Enable Macros to continue. Note: Markitsoft takes every precaution against transmitting computer viruses. Anti-virus software is installed on all our systems and all incoming and outgoing e-mail is checked. Our anti-virus software is continuously updated to protect us against the ever changing threat from new viruses. Analysis ToolPak Add-In Budget Controller uses functions in Excel's Analysis ToolPak. Please insure it's installed on your computer. From the Excel menu select Tools/Add-Ins. In the Add-Ins window, make sure the Analysis ToolPak is checked. 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. Design conventionsThe following conventions have been used throughout Budget Controller:
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. 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:
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. Toolbar You can also use the Budget Controller Toolbar to quickly move to worksheets. The Toolbar is automatically added to the Excel when you start Budget Controller and removed when you close it or open another Excel worksheet.
The Previous, Home and Next buttons work in the same way as those mentioned above. The Go To Page box contains a drop down list of worksheets. Select the sheet to jump directly to it. If you want remove the Toolbar while using Budget Controller go to the Excel menu and select View/Toolbars/Budget Controller and remove the check mark. 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. Entering Data There are various ways to enter and manipulate data in Budget Controller:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||
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. 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. 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 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 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. Manufacturing Fixed Costs are also determined by your chosen revenue streams again to ensure consistency in the Cost of Sales report. The remaining fixed costs can be classified individually in anyway you find useful. Functional Use Its normal to classify costs by where or how they are consumed in the business.
The cost of producing goods or services. Also called Cost of Sales (C.O.S.).
The cost involved in getting people to buy or use your goods and services. This often (but not always) will include distribution costs.
Typically costs associated with support functions like accounts, personnel and general management.
The costs of inventing better goods and services to offer to your customers.
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.
Costs associated with the normal running of the core business.
Costs not directly associated with an organisation's core business but nevertheless considered normal in that they occur frequently.
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:
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 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:
Creating Your Forecast 1. Enter start datesSelect 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. 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:
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. 4. Enter your new forecast 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. 5. Check your results Recalculate and check results. 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. 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. Worksheet Notes 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). 2. Other Assumptions Other assumptions you will need to consider include:
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.
Total stocks, including work-in-progress is derived from the Production Schedule above.
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.
Add you forecast for the purchase, payment and depreciation of fixed and intangible assets.
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 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.
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. 3. Reports View or print the results of your forecast. 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. Tips & Tricks 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:
To remove the icon, drag it back to the Customize window. Note: The Paste icon is disabled while using Budget Controller to reduce the risk of accidentally copying cell formats. The Enter key, however, will paste formats so please take care not to use it inadvertently. 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.
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. Consolidate workbooks Excel allows you to link multiple worksheets from different workbooks together. Say, for example, you use Markitsoft's Sales Controller for sales forecasting, you could link it to the Sales worksheet 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 forecast. 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. 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: If you want to export data to Microsoft Access do the following:
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 worksheet, 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). 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. Yearly forecasts 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. Weekly forecasts 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. | ||||||||||||||||||||||||||||||||||||||||||||||||||||
