The ability to make forecasts, predicting (at least approximately!) the future course of events is an integral and very important part of any modern business. Of course, this is a separate, very complex science with a bunch of methods and approaches, but often simple techniques are enough for a rough everyday assessment of the situation. One of them is the function FORECAST (FORECAST), which can calculate the forecast on a linear trend.
The principle of operation of this function is simple: we assume that the initial data can be interpolated (smoothed) by a certain straight line with the classical linear equation y=kx+b:
By constructing this straight line and extending it to the right beyond the known time range, we obtain the desired forecast.
To build this straight line, Excel uses the well-known least square method. In short, the essence of this method is that the slope and position of the trend line is chosen so that the sum of the squared deviations of the source data from the constructed trend line is minimal, i.e. the trend line smoothed the actual data in the best possible way.
Excel makes it easy to build a trend line right on the chart by right-clicking on the row – Add Trendline (Add Trendline), but often for calculations we do not need a line, but the numerical values of the forecast that correspond to it. Here, just, they are calculated by the function FORECAST (FORECAST).
The function syntax is as follows
=PREDICTION(X; Known_values_Y; Known_X values)
where
- Х – the point in time for which we make a forecast
- Known_values_Y — known to us values of the dependent variable (profit)
- Known_X values – the values of the independent variable known to us (dates or numbers of periods)
- Optimizing business models with the Solver add-in
- Selection of terms to obtain the desired amount