Sales Controller 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 budgets, forecast and business plans. We welcome any feedback you have and will endeavour to incorporate your suggestions into future releases.

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

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

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

4. Installing Sales Controller

Before starting Sales Controller Excel needs to be configured to run macros. Sales Controller also uses Excel's Solver Add-in. Please follow the installation instructions for your version of Excel.

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

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

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

7. Step by Step User Guide

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

7.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 for a maximum of ten revenue streams, i.e. products, services, regions, companies, divisions, etc. We recommend you add between 3 to 6 years of history to give Sales Controller a better chance of finding the trends.

7.2 The Trend Component

The Trend Component is calculated automatically using linear regression to fit the best trend line to deseasonalised sales. If you need to know the resulting straight line equation for each product, click on the Trend tab, otherwise you can skip this section.

Note: The trend equation is defined as: y = ax + b
y = trend
a = slope
x = number of months
b = constant

7.3 Fitting the Business or Economic Cycle

Fitting the Business Cycle is semi-automatic. Once you have fitted the curve roughly by eye, Sales Controller can automatically optimize the fit. Click on the Business Cycle tab to go to the worksheet. Each product can have an independent cycle, or alternatively you can work with the total for all products.

The choice between fitting the cycle product by product or globally depends what you intend to do with the forecast. If you are going to transfer all ten products to Budget Controller, then you should fit the cycles individually. If, however, you want to consolidate the ten product forecasts into one and only transfer the total to Budget Controller or another forecast then you’re should fit the business cycle to the total for all the products.

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

  1. Centre
  2. 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.

  3. Top
  4. 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.

  5. Life
  6. 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.

  7. Height
  8. 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 and have tried a few alternatives to see if you can reduce the unexplained sales value, you can then use the Improve Fit button to optimize the fit and minimize the unexplained sales index.

Each time you press the button, Sales Controller will record your last results in the Last Try column. After pressing the button wait for Sales Controller to finish. Each product can take between 30 seconds to a minute to go through all the alternative values before it finishes. Continue to Improve Fit until there’s no further reduction in the unexplained sales index, i.e. the Current value equals the Last Try value.

7.4 The Seasonal Component

The seasonal component is calculated automatically using a 12-month moving average to remove the seasonal fluctuations from the sales. If you need to know the resulting index for each product, click on the Seasonal Index tab, otherwise you can skip this section.

Note: The seasonal index can be used in Budget Controller in conjunction with the Multiplier Method of distributing sales seasonally. See the Budget Controller User Guide of more details.

7.5 Results

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

  1. Actual V’s Predicted Sales
  2. 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 Budget controller you can modify it based on your own judgement.

  3. Forecast Graphs
  4. 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.

  5. Forecast
  6. 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.

    You will have to copy each block of ten products one year at a time. 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 (learn more about Qualitative Forecasting)

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 spreadsheets (see Budget Controller’s User Guide – “Tips & Tricks” to see how to link spreadsheets/workbooks).