The term trend implies a change over time. One type of forecasting is quantitative, and involves analyzing time-series data, and then predicting what the future might be. For example, sales at an ice cream stand at the town park in June of each of the last five years has been good, but in July it was about 20% more than in June. If this year, the stand took in \$10,000 in June (a new record), how much would you predict it will take  in July? Well, if we were correct in our assumption based on the historical data, we'd estimate the July figure would be 20% higher, or \$12,000.

Microsoft Excel offers some built-in tools for forecasting. One of these allows you to add a trendline to existing data points on a chart. This allows the user to interpolate (i.e., to find a data point between existing points) or to extrapolate (i.e., to find a data point past either end of the current data, either by forecasting foreword, or backcasting to an earlier period). Besides this, MS Excel provides built-in forecast function to predict future. Given the following historical data of a company, MS Excel is used to identify trend and to make prediction of upcoming five years data. Based on this prediction management can make better decisions to develop plans for future.

In the above table value of last five rows are predicted by using MS. Excels FORECAST function. The FORECAST(x, known_y's,known_x's) function returns the predicted value of the dependent variable (represented in the data by known_y's) for the specific value, x, of the independent variable (represented in the data by known_x's) by using a best fit (least squares) linear regression to predict y values from x values. The parameter x must have a numeric value, known_y's and known_x's must be arrays or cell ranges that contain equal numbers of numeric data values. If we plot graph for above data and add trend lines, it looks like below:

XYZ Companies Sales History, Trend Analysis and Prediction of Future Sales

 Year Items_Sold (in 00000) Sales Amount (in millions) Net profit (in millions) 2001 184 341 12 2002 230 523 23 2003 279 641 37 2004 324 660 51 2005 332 810 73 2006 470 915 98 2007 523 1045 132 2008 602 1040 175 2009 621 1205 211 2010 758 1295 252 2011 824 1372 260 2012 896 1506 274 2013 974 1637 289 2014 1049 1752 305 2015 1138 1894 322 2016 1171 1944 367 2017 1250 2043 395 2018 1330 2151 422 2019 1409 2264 448 2020 1487 2367 472

To plot this graph we can flow following steps

® Select the historical data

® Click insert menu and select proper scatter from it

® Select one of the data point representing value of items sold

® Right click on it and select add trendline option in popup menu

® Select proper trendline options. In above graph forecast is set to 5 forward periods

® Repeat previous step for all scatters in the plot