Use of the Forecast Function for Predicting Future Sales

FireandRain

New Member
Joined
Jun 10, 2014
Messages
1
Hello,

I am a total newbie at forecasting and have a question about the forecasting function.

I have three months worth of sales data for 8 SKUs. I need to predict the next six months of sales for these SKUs. Would the =FORECAST(x, known_y's, known_x's) function work for me here?

Here's the set up I have with some dummy numbers. So I need to predict April to Sept-17 sales data:

SKUJan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17
11,1112,2223,333
22,2223,3334,444
33,3334,4445,555
44,4445,5556,666
65,5556,6667,777
76,6667,7778,888
87,7778,8889,999

<tbody>
</tbody>

I used the =FORECAST(x, known_y's, known_x's) function to get the following answers:

SKUJan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17
11,1112,2223,3334480564667757942910810237
22,2223,3334,44455916757788690531021911348
33,3334,4445,555670278688997101641133012459
44,4445,5556,6667813897910108112751244113570
65,5556,6667,77789241009011219123861355214681
76,6667,7778,888100351120112330134971466315792
87,7778,8889,999111461231213441146081577416903

<tbody>
</tbody>


These are the formulas behind the numbers:

SKU42766=+EOMONTH(N2,1)=+EOMONTH(O2,1)=+EOMONTH(P2,1)=+EOMONTH(Q2,1)=+EOMONTH(R2,1)=+EOMONTH(S2,1)=+EOMONTH(T2,1)=+EOMONTH(U2,1)
1111122223333=FORECAST($Q$2,N3:P3,$N$2:$P$<wbr>2)=FORECAST($R$2,$N3:$P3,$N$2:$<wbr>P$2)=FORECAST($S$2,$N3:$P3,$N$2:$<wbr>P$2)=FORECAST($T$2,$N3:$P3,$N$2:$<wbr>P$2)=FORECAST($U$2,$N3:$P3,$N$2:$<wbr>P$2)=FORECAST($V$2,$N3:$P3,$N$2:$<wbr>P$2)
2222233334444=FORECAST($Q$2,N4:P4,$N$2:$P$<wbr>2)=FORECAST($R$2,$N4:$P4,$N$2:$<wbr>P$2)=FORECAST($S$2,$N4:$P4,$N$2:$<wbr>P$2)=FORECAST($T$2,$N4:$P4,$N$2:$<wbr>P$2)=FORECAST($U$2,$N4:$P4,$N$2:$<wbr>P$2)=FORECAST($V$2,$N4:$P4,$N$2:$<wbr>P$2)
3333344445555=FORECAST($Q$2,N5:P5,$N$2:$P$<wbr>2)=FORECAST($R$2,$N5:$P5,$N$2:$<wbr>P$2)=FORECAST($S$2,$N5:$P5,$N$2:$<wbr>P$2)=FORECAST($T$2,$N5:$P5,$N$2:$<wbr>P$2)=FORECAST($U$2,$N5:$P5,$N$2:$<wbr>P$2)=FORECAST($V$2,$N5:$P5,$N$2:$<wbr>P$2)
4444455556666=FORECAST($Q$2,N6:P6,$N$2:$P$<wbr>2)=FORECAST($R$2,$N6:$P6,$N$2:$<wbr>P$2)=FORECAST($S$2,$N6:$P6,$N$2:$<wbr>P$2)=FORECAST($T$2,$N6:$P6,$N$2:$<wbr>P$2)=FORECAST($U$2,$N6:$P6,$N$2:$<wbr>P$2)=FORECAST($V$2,$N6:$P6,$N$2:$<wbr>P$2)
6555566667777=FORECAST($Q$2,N7:P7,$N$2:$P$<wbr>2)=FORECAST($R$2,$N7:$P7,$N$2:$<wbr>P$2)=FORECAST($S$2,$N7:$P7,$N$2:$<wbr>P$2)=FORECAST($T$2,$N7:$P7,$N$2:$<wbr>P$2)=FORECAST($U$2,$N7:$P7,$N$2:$<wbr>P$2)=FORECAST($V$2,$N7:$P7,$N$2:$<wbr>P$2)
7666677778888=FORECAST($Q$2,N8:P8,$N$2:$P$<wbr>2)=FORECAST($R$2,$N8:$P8,$N$2:$<wbr>P$2)=FORECAST($S$2,$N8:$P8,$N$2:$<wbr>P$2)=FORECAST($T$2,$N8:$P8,$N$2:$<wbr>P$2)=FORECAST($U$2,$N8:$P8,$N$2:$<wbr>P$2)=FORECAST($V$2,$N8:$P8,$N$2:$<wbr>P$2)
8777788889999=FORECAST($Q$2,N9:P9,$N$2:$P$<wbr>2)=FORECAST($R$2,$N9:$P9,$N$2:$<wbr>P$2)=FORECAST($S$2,$N9:$P9,$N$2:$<wbr>P$2)=FORECAST($T$2,$N9:$P9,$N$2:$<wbr>P$2)=FORECAST($U$2,$N9:$P9,$N$2:$<wbr>P$2)=FORECAST($V$2,$N9:$P9,$N$2:$<wbr>P$2)

<tbody>
</tbody>

Is this an appropriate way to use the forecast function? Is my use of the month in question the right "x" (cells Q2, R2,S2, T2,U2, V2) and the months I have data for the right "known_x's" (N2:P2)?

I can send this excel sheet if that is helpful. I REALLY appreciate your help! Thank you! :confused:
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,239
Messages
5,509,991
Members
408,768
Latest member
ndg4405

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top