Excel forecasting

Copper Contributor

I have data in the below table format and want to forecast for the remaining four months, but the formula I am using is not giving a fruitful output

=FORECAST.LINEAR(BL3,$H5:$BK5,$H$3:$BK$3)

Jan 2015Feb 2015Mar 2015Apr 2015May 2015Jun 2015Jul 2015Aug 2015Sep 2015Oct 2015Nov 2015Dec 2015Jan 2016Feb 2016Mar 2016Apr 2016May 2016Jun 2016Jul 2016Aug 2016Sep 2016Oct 2016Nov 2016Dec 2016Jan 2017Feb 2017Mar 2017Apr 2017May 2017Jun 2017Jul 2017Aug 2017Sep 2017Oct 2017Nov 2017Dec 2017Jan 2018Feb 2018Mar 2018Apr 2018May 2018Jun 2018Jul 2018Aug 2018Sep 2018Oct 2018Nov 2018Dec 2018Jan 2019Feb 2019Mar 2019Apr 2019May 2019Jun 2019Jul 2019Aug 2019
$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$145$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0
$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$430$1,545$2,236$906$3,645$1,573$4,951$4,360$7,355$3,264$9,354$9,014$0$0$0$0$0$0$0$0$0$0$0$0
$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$6,355$4,329$5,992$5,772$4,678
$6,018$5,225$3,427$11,148$501$36$36$0$924$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0
$0$0$0$0$0$0$0$0$0$0$1,207$1,291$1,465$153$150$0$30$0$0$0$0$1,734$1,600$1,358$587$60$353$0$205$0$899$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0
$0$0$0$35,604$10,498$5,835$3,077$1,612$1,404$0$0$0$0$0$0$0$0$0$0$0$0$0$0$10,357$9,343$8,355$8,148$4,151$17,420$4,212$15,695$0$7,795$421$4,934$2,616$4,687$3,667$3,056$3,870$5,067$6,715$8,711$18,797$8,445$16,879$16,117$15,333$11,380$4,943$18,217$4,325$10,253$10,261$14,308$4,295
$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$2,778$312
$452$635$469$209$120$32$26$0$0$0$0$0$0$2,929$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0
3 Replies

@newlearner 

The data don't lend themselves to linear forecasting:

Chart.png

@Hans Vogelaar ...thanks ,then which formula to use, could you please help with that!

@newlearner 

If you look at the chart, you'll see that the data in each series don't follow any particular trend - they don't go up or down consistently, nor is there a clear seasonal trend. It all looks rather random. So no method of forecasting will provide a meaningful result.