The MS Excel program, even not being a full-fledged statistical package, has a fairly wide range of possibilities for predicting events based on already available data. One of the simplest, at first glance, methods of such prediction is the construction of a trend line.
Instructions
Step 1
The easiest way is to plot the trend function immediately after entering the available data into the array. To do this, on the sheet with the data table, select at least two cells of the range for which the graph will be built, and immediately after that insert the chart. You can use such types of charts as graph, scatter, histogram, bubble, stock. Other types of charts do not support trend building.
Step 2
From the Chart menu, select Add Trend Line. In the window that opens, on the "Type" tab, select the desired trend line type, which in mathematical terms also means the method of data approximation. When using the described method, you will have to do it "by eye", because you did not perform any mathematical calculations for plotting the graph.
Step 3
So just think about which type of function is best suited to the graph of the available data: linear, logarithmic, exponential, exponential, or other. If you doubt the choice of the approximation type, you can draw several lines, and for greater forecast accuracy, on the "Parameters" tab of the same window, check the box "put the approximation confidence value (R ^ 2)" on the diagram.
Step 4
By comparing the R ^ 2 values for different lines, you can choose the type of graph that characterizes your data most accurately, and, therefore, builds the most reliable forecast. The closer the R ^ 2 value is to one, the more accurately you have chosen the line type. Here, on the "Parameters" tab, you need to specify the period for which the forecast is made.
Step 5
This method of building a trend is very approximate, so it is better to do at least the most primitive statistical processing of the available data. This will make the forecast more accurate.
Step 6
If you assume that the available data is described by a linear equation, simply select it with the cursor and autocomplete it for the required number of periods, or the number of cells. In this case, there is no need to find the value R ^ 2, since you have fitted the forecast to the equation of a straight line in advance.
Step 7
If you think that the known values of a variable can best be described using an exponential equation, also select the original range and autocomplete the required number of cells while holding down the right mouse button. With autocomplete, you will not be able to draw other types of lines than the two indicated.
Step 8
Therefore, for the greatest accuracy of forecasting, you will have to use one of several statistical functions: "FORECAST", "TREND", "GROWTH", "LINEST" or "LGRFPRIBL". In this case, you will have to calculate the value for each subsequent forecast period manually. If you need to perform more complex regression analysis of data, you will need the "Analysis Package" add-in, which is not included in the standard MS Office installation.
