Sales Controller Lite 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. 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 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.

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