In: Accounting
Describe the various forecasting methods. What are the steps needed to develop a forecast? Explain how you could use Excel to help develop a forecast. Provide an example of an Excel forecast for a three year period on any one income statement or balance sheet account.
Forecasting is the process of making predictions of the future based on past and present data and most commonly by analysis of trends.
Various of forecasting methods are:
1.Qualitative method:Qualitative forecasting techniques are subjective, based on the opinion and judgment of consumers and experts; they are appropriate when past data are not available. They are usually applied to intermediate- or long-range decisions. Examples of qualitative forecasting methods are informed opinion and judgment, the Delphi method, market research, and historical life-cycle analogy.
2.Quantitative method:Quantitative forecasting models are used to forecast future data as a function of past data. They are appropriate to use when past numerical data is available and when it is reasonable to assume that some of the patterns in the data are expected to continue into the future. These methods are usually applied to short- or intermediate-range decisions. Examples of quantitative forecasting methods are last period demand, simple and weighted N-Period moving averages, simple exponential smoothing, poisson process model based forecasting and multiplicative seasonal indexes. Previous research shows that different methods may lead to different level of forecasting accuracy. For example, GMDH neural network was found to have better forecasting performance than the classical forecasting algorithms such as Single Exponential Smooth, Double Exponential Smooth, ARIMA and back-propagation neural network.
3.Average approach:In this approach, the predictions of all future values are equal to the mean of the past data. This approach can be used with any sort of data where past data is available. In time series notation:
where is the past data.
Although the time series notation has been used here, the average approach can also be used for cross-sectional data (when we are predicting unobserved values; values that are not included in the data set). Then, the prediction for unobserved values is the average of the observed values.
4. Naïve approach:Naïve forecasts are the most cost-effective forecasting model, and provide a benchmark against which more sophisticated models can be compared. This forecasting method is only suitable for time series data.Using the naïve approach, forecasts are produced that are equal to the last observed value. This method works quite well for economic and financial time series, which often have patterns that are difficult to reliably and accurately predict. If the time series is believed to have seasonality, the seasonal naïve approach may be more appropriate where the forecasts are equal to the value from last season.
Other major approaches are Drift method, Seasonal naïve approach, Time series methods (Moving average, Weighted moving average, Kalman filtering, Exponential smoothing etc)
STEPS NEEDED TO DEVELOP A FORECAST :
1. Identify the Problem
Defining the problem can seem simple at first because it looks like you are simply asking how will the market react to a new product, or how the company’s sales will look like in a few months. Even more so if you have a good forecasting tool for small business.
However, this step is quite tricky because there aren’t actually any tools that can help here. It requires you to know who the forecast is directed too, how the market works, and what your customer base and competition are.
You should spend some time evaluating these issues together with the people who will be responsible for maintaining databases and gathering the data.
2. Collect Information
We say information here, and not data, because data may not be available yet if for example the forecast is aimed at a new product. Having said this, the information comes essentially in two ways: the knowledge gathered by experts and actual data.
If no data is yet available, the information must come from the judgments made by experts in the area. If the forecast is based solely on judgment and no actual data, we are in the field of qualitative forecasting.
If data is available on the subject, a model is used to analyze the data and predict future values. This is called quantitative forecasting. A good example is predicting the sales for a given product in order to replenish stocks accordingly. This can even be done on a daily basis if you use a good forecasting tool for small business.
3. Perform a Preliminary Analysis
An early analysis of the data may tell you right away if the data is usable or not. It may also reveal patterns or trends that can then be helpful, for example, in choosing the model that best fits it.
Another thing that can be done here is to check for redundant data and cut it down or make some educated assumptions. By reducing the amount of data to analyze you can greatly simplify the entire process.
4. Choose the Forecasting Model
Once all the information is collected and treated, you may then choose the model you think will give you the best prediction possible. There is not one single model that works best in all situations, it all depends on the availability and nature of the available data.
Qualitative Forecasting
As we’ve seen before, we may not even have any historical data, in which case we have to use qualitative forecasting.
Two models that are commonly used in qualitative forecasting are a market research and the Delphi method. A market research is performed by enquiring a large number of people about their willingness to purchase a possible product or service.
The Delphi method consists of gathering forecasts from several different experts in a given area, and then compiling all that information into a single forecast. It relies on the assumption that a collective forecast is more accurate than that of a single person.
Quantitative Forecasting
If sufficient data is available, the human factor can be removed from the equation and a raw data analysis can be performed to predict future values. A lot of mathematical values exist to do these predictions, including regression models, exponential smoothing models, Box-Jenkins ARIMA models and others.
Some forecasting tools for small business, like DataQlick, use an Exponential Moving Average Calculation model to predict product sales.
5. Data analysis
This step is simple. After choosing a suitable model, run the data through it.
6. Verify Model Performance
When the time comes, it is very important to compare your forecast to the actual data. This allows you to evaluate the accuracy of not only the model, but the entire process, and change each step accordingly. Hopefully, if you use a good forecasting tool for small business, there won’t be much tweaking needed!
EXCEL & FORECASTING
If you have historical time-based data, you can use it to create a forecast. When you create a forecast, Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. A forecast can help you predict things like future sales, inventory requirements, or consumer trends.
Create a forecast
In a worksheet, enter two data series that correspond to each other:
A series with date or time entries for the timeline
A series with corresponding values
These values will be predicted for future dates.
Note: The timeline requires consistent intervals between its data points. For example, monthly intervals with values on the 1st of every month, yearly intervals, or numerical intervals. It’s okay if your timeline series is missing up to 30% of the data points, or has several numbers with the same time stamp. The forecast will still be accurate. However, summarizing data before you create the forecast will produce more accurate forecast results.
Select both data series.
Tip: If you select a cell in one of your series, Excel automatically selects the rest of the data.
On the Data tab, in the Forecast group, click Forecast Sheet.
Forecast Sheet button on the Data tab
In the Create Forecast Worksheet box, pick either a line chart or a column chart for the visual representation of the forecast.
Screenshot of Create Forecast Worksheet dialog box with Options collapsed
In the Forecast End box, pick an end date, and then click Create.
Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data.
You'll find the new worksheet just to the left ("in front of") the sheet where you entered the data series.
Example: