How to forecast on Excel based on weeks

Question

I have this data

¦Year-Week¦Sales¦Forecast¦
¦2015-42  ¦   7 ¦        ¦
¦2015-43  ¦   8 ¦        ¦
¦2015-44  ¦   2 ¦        ¦
¦2016-45  ¦4276 ¦        ¦
¦2016-46  ¦ 876 ¦        ¦
¦2016-47  ¦  7  ¦        ¦
¦2016-48  ¦  52 ¦        ¦
¦2016-49  ¦72876¦        ¦
¦2015-50  ¦  20 ¦        ¦
¦2015-51  ¦  35 ¦        ¦
¦2015-52  ¦  15 ¦        ¦
¦2016-01  ¦  24 ¦        ¦
¦2016-02  ¦   8 ¦        ¦
¦2016-03  ¦     ¦        ¦
¦2016-04  ¦     ¦        ¦
¦2016-05  ¦     ¦        ¦
¦2016-06  ¦     ¦        ¦
¦2016-07  ¦     ¦        ¦
¦2016-08  ¦     ¦        ¦

I've tried this formula to workout the forecast figures for Year-Week 2016-03 onward.

=FORECAST(A6,$A$2:$A$5,$B$2:$B$5)

and got

#Value! --Error

After researching I have seen that excel will not recognise Year-Week column as it's non numeric, hence the error.

I then changed the Year-Week column to just display the Week and it has returned values however they look incorrect to me:

¦Year-Week¦Sales¦Forecast¦
¦42       ¦   7 ¦        ¦
¦43       ¦   8 ¦        ¦
¦44       ¦   2 ¦        ¦
¦45       ¦4276 ¦        ¦
¦46       ¦ 876 ¦        ¦
¦47       ¦  7  ¦        ¦
¦48       ¦  52 ¦        ¦
¦49       ¦72876¦        ¦
¦50       ¦  20 ¦        ¦
¦51       ¦  35 ¦        ¦
¦52       ¦  15 ¦        ¦
¦01       ¦  24 ¦        ¦
¦02       ¦   8 ¦        ¦
¦03       ¦     ¦39.75545¦
¦04       ¦     ¦39.15559¦
¦05       ¦     ¦39.15573¦
¦06       ¦     ¦39.15587¦
¦07       ¦     ¦39.15587¦
¦08       ¦     ¦39.15615¦

Is there away to workout forecast based on weeks? as I don't have year month day in the data set and only year weeks to work with, or is the above result something sensible to get back with the formula?

Have a look here https://support.office.com/en-us/article/create-a-forecast-in-excel-2016-for-windows-22c500da-6da7-45e5-bfdc-60a7062329fd — MGP, Mar 15 '18 at 10:08
At a first glance from your data it looks pretty volatile... and therefore it will be tough to predict. — MGP, Mar 15 '18 at 10:09

score 1 · Answer 1 · edited Aug 15 '18 at 17:51

Transfer your data to cells A1 to C20.
Introduce a new column for Actual Dates. In cell D2, enter the formula, and drag it down.

=DATE(VALUE(LEFT(A2,4)),1,1)+(7*(VALUE(MID(A2,6,2))-1))

The formula will convert the text values corresponding to the date, to actual dates.

In cell C15, enter the below formula and drag it down

 =FORECAST(D15,$B$2:$B$14,$D$2:$D$14)

This reference dates (Known_Xs) for this Forecast formula are in column D.
The reference Y values (Known_Ys) for this formula are in column B.

Probable Error in you data. The data which you have provided is having Date up to week 44 as year 2015, while dates from week 45 onwards are having year as 2016. This appears to be an error in the data. Kindly confirm.

I hope this solves your problem.

How to forecast on Excel based on weeks

1 Answers1