« »

Wednesday, April 27, 2011

How to create a sales forecast

There are many ways to create a sales forecast, and how a sales forecast is made is not the same as what a sales forecast is. Some sales forecasts are more accurate than others, but the information used in making the sales forecast also contributes to its validity and largely pertains to existing and pre-existing selling activities. Sales forecasts can be made by hand on paper with a calculator, using Microsoft Excel, or with a specialized sales forecast software. Essentially one or more of the following are needed to make a sales forecast:

1. Past sales numbers
2. Sales and marketing budget numbers
3. Intervals at which the numbers are attributable to
4. Other dependent variables ex. Changes in sales force
5. Calculator, or Forecasting software

Determining which sales forecast to use for your business depends on market consistency, historical sales, changes to marketing, project management or product and service line, economic conditions and  forecast objective. This article will illustrate how to make different sales forecasts with different tools and methods, and then discuss the importance, relevance, pros and cons of sales forecasts. The instructional presentation below illustrates the first method of creating a sales forecast.

Simple sales forecast using moving average

The moving average is a trend line that calculates an average number based on historical data. For example, if sales for the last two years averaged $120K per month and rose an average of 3% per month, a sales forecast that uses both moving average and the percentage rise in the moving average will give a sales prediction based on past sales patterns.

1. List historical sales on a piece of paper or spread sheet
2. Calculate moving average
3. Determine periodic percentage increase in sales
4. Create trend line by applying percentage increase to moving average

Sales forecasts using Microsoft Excel

A few types of sales forecasts can be made using Microsoft Excel. These different forecasts use data differently or adjust data to arrive at more accurate predictions. All forecasts are predictions unless the variables are 100% constant into the future. The sales forecasts below use moving average, dependent variable i.e. causal relationships, and independent variables i.e. sales values. (Microsoft.com)

1. Create three or more columns in a Microsoft Excel Spreadsheet
2. One column for sales and another for time period
3. Click on the chart wizard
4. Highlight data box, proceed through wizard steps
5. Click on Chart tab and add trend line

For more detailed analysis of the above sales forecasting method using Microsoft Excel, regression analysis can be used to calculate accuracy of the sales forecast, and correlations. The following Microsoft website illustrates how to do this and create a forecast in excel with more detail.

Sales forecast using statistical or specialized software

Software that is designed just for creating sales forecasts can provide easier to you applications that save precious time and energy. Additionally, specialized sales forecasting software may offer a variety of forecasting options such as multiple dependent variables, easier data input templates, more dynamic presentation, and greater analytical capacity. Sales forecasting software can be found for free online via sites such as http://www.freedownloadscenter.com

Advantages and disadvantages of sales forecasts

Sales forecasts are useful tools in business management because they help refine the business process and improve the accuracy of business financial planning. Sales forecasts can also be helpful in training teams, illustrating sales performance and planning future sales efforts. Additionally, sales forecasts may assist a business obtain financing for new projects or demonstrate the need for sales related changes.

• Reduce excess inventory and storage costs
• Allow management to gauge effectiveness of sales force
• Improve strategic planning and product testing 
• Assist sales force assess their efforts and market
• Creates records for budget considerations and analysis

Sales forecasts are only as good as the variables and factors put into the equation, software or calculation. Some forecasts may be mere estimates or ball park figures of what sales might be like given non measured past patterns. Other forecasts may fail to take into account significant changes in sales staff, budgets or product line(s). Thus, knowing the business in detail can help improve the accuracy of a sales forecast.

• May not account for changes in economic conditions
• Requires business conditions to remain constant
• Does not necessarily incorporate future projects
• Numbers affected from

In summary, sales forecasts are made relatively easily but do require a record of past sales and business variables to more accurately create a forecast using a moving average trend line. Sales forecasts can compare multiple variables on a graph, in addition to being displayed on data tables. The forecast accuracy can be measured with statistical analysis and multiple methods of creating sales forecasts exist including by hand, Microsoft Excel or specialized sales forecasting software downloads. Sales forecasts are generally not 100% accurate but do assist a sales team, business management and business ownership plan for and project future business situations and revenue which helps in inventory control, assessing human resources, budget and strategic decisions.