Control Panel Lite Edition User Manual

Please take a few minutes to read this guide before you start using Control Panel. It contains important information that you will need to get the most out of the product. Before you start, we recommend you take a look at the demos to get an overview of how the product works.

We hope Control Panel proves to be a useful management reporting tool and we welcome any feedback you have and will endeavour to incorporate your suggestions into future releases.

Introduction

This Guide will take you through the steps necessary to create comprehensive monthly management financial reports for your enterprise. If you have also purchased our companion product Budget Controller, it will also explain how to import budgets and forecasts from the Lite version of our financial modelling tool.

Work from a copy

We strongly recommend you work from a copy of Control Panel. 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 Control Panel

Before running Control Panel 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 Control Panel double click the Control Panel 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.

Example data

The example data supplied with Control Panel is for a company with a fiscal year starting in January 2004, reporting on October 2004 with a yearly budget and a forecast which was last updated in September 2004. You can delete this data by using the following buttons on the main menu:

1) Delete Actual - Deletes the full two year's of actual data.

2) Delete Budget - Deletes 12 months of budget data.

3) Delete Forecast - Deletes 12 months of forecast data.

Note: Depending on the speed of your computer, this operation can take up to a minute or so to complete.

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 yellow comment cells on the digital dashboard, which are unprotected so you can add an additional explanation to the reports if necessary.
Numbers too large to display appear as #######. Hover over the cell with your cursor and the number will appear on the screen.

Terminology / Methodology

The terms used throughout this manual have the following meaning:

Actual Data - This is the financial data from your accounts as opposed to budget or forecast data.
Budget Data - This is data from you annual budget. A budget is normally prepared for the fiscal year a month of so before it starts. Once approved the budget normally remains unchanged throughout the year and serves as a base reference to compare your actual performance against.
Forecast Data - Forecasts are often updated several times during the year. Some companies will actually maintaining rolling monthly forecasts so they have a continuous indication of future performance (for a further discussion on rolling monthly forecasts, go to Budgeting: Getting it Right)
Last Year / Last Month - Comparing the current months actual values with the actual values for prior periods is another useful way of analysing results. Called Exception Reporting, it is a way managers spot anomalies in the data and expect explanations for their existence.
Last 12 Months - Actual data from the last 12 months is often used by managers as an indication of their full year results. Its not as useful as a rolling monthly forecast for predicting the future performance of your business, but it does have the advantage of using actual data and being easier to calculate. Perhaps more important is the trend of the data for last 12 months as a leading indicator of possible problems. If, for example, average monthly sales for the last 12 months are declining, this will be of obvious concern to management.

Data is also presented on the following time scales:

Monthly - Compares data for the current month
Year-to-Date - Gives accumulated data from the start of the fiscal year to the current reporting period
Annual - Gives an indication of full year results. Note: Year-to-date data for the last month of a fiscal year will be the same as the annual data.

Creating Your Management Reports

1. Enter start dates

Enter the date your fiscal year starts, the date of the current reporting period and date of your latest forecast. Assuming you are starting from the first month of a new fiscal year then all the dates will be the same.

2. Enter or Import your Budget

If you haven't purchased Budget Controller, you'll have to enter your budget data manually in the appropriated worksheet. There are three worksheets, one for each report:

Budget_PL - Budget Profit and Loss or Income Statement
Budget_BS - Budget Balance Sheet
Budget_CF - Budget Cash Flow Statement

If you have purchased Budget Controller's Lite Version, you can import your budget automatically by doing the following:

Make sure the Budget Controller file is in the same folder as Control Panel.
Add the name of the Budget Controller file in square brackets on the Control Panel Main menu, for example: [my_budget.xls]. Note: File names cannot contain spaces.
Open your Budget Controller file.
Click the Import Budget button.
Close your Budget Controller file.

Note: This operation would normally only be done once at the beginning of a new fiscal year.

3. Enter or Import your Forecast

Follow exactly the same procedure to add or import your forecast, except use the Forecast worksheets or click the Import Forecast button to import from Budget Controller.

You can update your forecast as many times as necessary throughout the year. In the first month of the year your budget and forecast are probably the same. If that's the case you can either leave the forecast blank for the time being or import the budget data into the forecast worksheet and update it as and when you prepare your first forecast.

Some organisations have budgeting processes that have to go through many levels of management approval and take months to prepare and are often out-of-date by the time they are approved. If this is your case, you might find it helpful to do your own forecast from the beginning of the year as a supplement to the official budget.

As the year progresses you can update your forecast as necessary. Simple change the Forecast Starts date and either manual enter the new values or import them from the forecast file you prepared with Budget Controller.

More and more companies are adopting monthly rolling forecasts. If you decide this is the best option for your company, then the following is an example of a reporting timeline you might follow for the month of September:

August accounts closed by the 10th of September.
August's actual closing values entered into your Budget Controller forecast file. The forecast is updated with management's perspectives for the remainder of the year.
Change the Forecast Starts date to 01/09/2004 and import the forecast into Control Panel.
September accounts closed by the 10th of October and values added to Control Panel
Control Panel reports for September emailed to Management.

4. Enter your Actual Values

Once you have closed your accounts for the reporting period, simply enter the values manually in the appropriated worksheet and change the Reporting Period date to the current month.

Note: Manually updating Control Panel with your actual data shouldn't take more than 30 minutes per month. However, if your accounts software can export data to a CSV file (comma separated values or comma delimited file), you could create a separate Excel file to automate the data entry process for the actual values. Your accounts need to be grouped and aggregated in the same format as Control Panel. You can then either link the interface dynamically to Control Panel (see the Tips & Tricks section below) or create a macro to copy / paste the values from one file to the other. Alternatively, we can develop an interface for you, please contact us for details.

5. 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 Control Panel. There is no restriction on the number of copies you make: Control Panel is licensed by the number of users not the number of copies. You may well decide to save each month into its own file, for example reports-08-2004.xls.

6. Adjusting the "Traffic Light" Sensitivity

The Sensitivity field on the main menu adjust the "traffic light" flags in the Digital Dashboard. The higher the percentage the longer the flags stay at amber. The "traffic light" flags are there to draw management's attention to problem areas. You probably won't need to highlight a 0.1% drop in sales but a 5% drop is most certainly of interest. Select a sensitivity appropriate for your own circumstances.

7. Print or email your reports

All worksheets are designed to be printed on A4 paper. 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.

If you want to email the reports to colleagues, we recommend you save the file and send it as an attachment, zipped (compressed) if necessary. It is possible to email worksheets as part of the email message body via Excel. However, there are some formatting issues that aren't correctly resolved during the conversion possess, so until these are addressed by Microsoft, we recommend simply sending the file as an attachment.

8. New year

When you're ready to start a new year, we recommend you save Control Panel as a new file. Enter the new dates and click the Start New Year button. This will move the current year's actual data to the previous year and delete the old last year's data. You're now ready to start the reporting process all over again!

Note: Depending on the speed of your computer, this operation can take a couple of minutes to complete.

Tips & Tricks

Linking workbooks

Excel allows you to link multiple worksheets from different workbooks together. 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.

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.

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 hold the Ctrl key down and click on the cells to add.

Forecast short-cuts

If you want to get a rough idea of your future performance without updating the forecast, you can simple change the Forecast Starts date on the main menu. This will have the following effect:

Use the same date as the reporting period - In the Year-to-date and annual reports, Control Panel will use the actual values up to the month before the forecast, and forecast values for the current month and for the rest of the year in annual / full year sections.
The date following the reporting period - Control Panel will use the actual values up to the current month and forecast values for the rest of the year in annual / full year sections.

Caution! This method is not technically correct and should only be used to get an idea of future performance between a full update of your forecast and is only appropriate for the Income Statement values. Balance Sheet and Cash Flow values will not reconciled with the values in the Income Statement when using this method and items like interest and depreciation in the Income Statement will not necessarily be forecast accurately.