Sales Controller Web Edition User Manual

Please take a few minutes to read this guide before you start using Sales 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 Sales, forecast and business plans. We welcome any feedback you have and will endeavour to incorporate your suggestions into future releases.

Introduction

Sales Controller uses time-series analysis to provide the foundations on which to build your forecast. Once you have the foundations, you will also need to consider other issues that time-series analysis cannot forecast, such as: new technologies, markets, products, competitors and changes in marketing strategies or in the economic or political environment. Your final version should also incorporate your own insight and judgement, your company’s goals and aspirations and ideas from colleagues and experts as to what the future might hold.

How does it work?

Using time-series analysis, Sales Controller extracts trends from your past sales data, breaking it down into three principle components:

The Trend Component:

Regardless of other fluctuations, there is generally an overall sales trend. Over a period of time sales may be increasing, decreasing or remain static. Typically, changes in sales growth rates are caused by new technologies, population dynamics, changes in tastes, changes in the firm's marketing strategies or more or less competition in the market place.

The Cyclical Component:

Sales are often effected by swings in general economic activity as consumers have more or less disposable income available. These fluctuations normally follow a wave-like pattern being at a crest when the economy is booming and a trough in times of recession.

The Seasonal Component:

During the year, whether it’s on an hourly, weekly, monthly or quarterly basis, there is normally a distinguished pattern to sales. The Seasonal Component is generally effected by such things as the weather, holidays, local customs and general consumer behaviour.

Having established past trends, Sales Controller simply projects these into the future to form the basis of your forecast.

Work from a copy

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

File Formats

Sales Controller Web comes with two file formats:

Excel Format

The Excel file is similar to Sales Controller Lite. Some design and formatting differences exist to make it compatible for conversion to HTML for publishing on your corporate intranet site.

Once you have completed your forecast you can save it as a standard Excel workbook and then convert it to internet format as follows:

• Select File/Save as Web Page from the Excel menu

• In the Save As options window select Entire Workbook and Add interactivity if you want people to be able to change the forecast online. Enter a File name for the new file and select Save.

• You can now add the HTML file to your intranet. People can use the file to change and do "what-if" analysis on the forecast and can view and print their results. However, the underlying data is protected and remains unchanged.

If you want to give users permission to save their work as a normal Excel workbook from the internet, you must add the toolbar containing the Export to Microsoft Excel option before publishing the file on your intranet. The toolbar is activated in the Protection tab of the Active X Control Properties dialog window. Select the Commands and Options dialogue box in the Workbook section to activate the toolbar.

For more information see Microsoft's Excel Help: About putting Excel data on the Web.

Internet Format

The HTML file is a copy of the Excel file with the example data already converted for publishing on the internet. Fell free to use this as an example on your intranet. People can delete the example data and add their own forecasts.

Starting Sales Controller

To start Sales Controller double click the Sales Controller Excel file. You navigate through the workbook by using the standard Excel sheet tabs along the bottom of the spreadsheet.

Saving Sales Controller

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

Design conventions

The following conventions have been used throughout Sales Controller:

• White cells indicate user data entry areas. All other cells are protected and cannot be changed.

• Numbers too large to display appear as #######. Hover over the cell with your cursor and the number will appear on the screen.

Step by Step User Guide

Sales Controller is very simple to use. Simply follow the steps below:

1. Add your past sales history

Enter the start and end dates of your sales history in the Main Menu. Click on the Sales History tab to go to the spreadsheet where you can enter your data. Either enter the data manually or copy / paste it from some other application.

You can enter up to six years of data.  We recommend you add between 3 to 6 years of history to give Sales Controller a better chance of finding the trends.

2. The Trend Component

The Trend Component is calculated automatically using linear regression to fit the best trend line to deseasonalised sales.

3. Fitting the business or economic cycle

To fit the cycles you need to adjust the variables below to minimize the unexplained sales index:

i. Centre

Normally this will be approximately 1. In effect, it’s the mean value about which the economic cycle oscillates. A value close to 1 is to be expected as the cycle generally has the same positive as it has negative effect on sales. For the rough fit enter 1.0000 under the Current column for the appropriate product.

ii. Top

The top of the cycle is the month number when it reaches its maximum.
If, for example, the top of the business cycle is in the 3rd month of the forecast you should enter 3 for the Top value. This value is found by trial and error and your own experience of the business cycle of your products.

iii. Life

The life of the cycle refers to the number of months between the first top of the cycle and the next. If the forecast started in January with a Top of 1 and a Life of 12, the minimum point in the cycle would be July and the next maximum would be in the following January. As above, this value is found by trial and error and your own experience of the business cycle of your products.

iv. Height

The height of the economic cycle will be close to the Standard Error of the Y Estimate (Std. Error Est.) shown at the top of each products business cycle variables. It basically tells you the amount by which the data deviates from the mean value. For now, enter this value as the height of the economic cycle. The next step is to try and improve the cycle's fit by using various alternative values.

Once you’ve entered the above values try a few alternatives to see if you can reduce the unexplained sales value. You can use the Last Try column to keep a record of your last results.

4. The Seasonal Component

The seasonal component is calculated automatically using a 12-month moving average to remove the seasonal fluctuations from the sales.

5. Results

All that remains to do now is check your results. There are three worksheets that you can use for this:

i. Actual V’s Predicted Sales

Click the Actual V’s Predicted tab to see how well your sales history fits the time-series analysis you have just created. This will give you a good indication as to the accuracy of your forecast and whether or not you need to review some of the above procedures.

Remember your Sales Controller forecast is only a basis for further discussion. Once you’ve transferred it to Sales controller you can modify it based on your own judgement.

ii. Forecast Graphs

The Forecast Graphs worksheet gives you your forecast both graphically and summarized by total sales per year. Use this for the basis of your on-going discussions to refine your forecast.

iii. Forecast

Use this worksheet to copy / paste the values into Budget Controller or another application or spreadsheet. Remember to use Paste Special and select values unless you want to dynamically link Sales Controller with Budget Controller.

Once in Budget Controller you can make your final adjustments either directly to the data or using Budget Controller’s various adjustment functions.

Congratulations, the first stage of you forecast is complete. Remember, however, that trends based on historical sales data provide no guarantees for the future and, in this first stage, you have only created a basis for further discussion and review.

We recommend you transfer the data into Budget Controller where you can make the final adjustments. You can either Copy/Paste the values or add a dynamic link between the two applications (see Budget Controller’s User Guide – “Tips & Tricks” to see how to link workbooks).