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