Projecting Supply & Demand

[misteryu1029]

Hi Guys,

First time here and would love your help.

I'm having difficulty finding the best way to project and forecast supply & demand given a data set.

I was thinking of either the =growth or =forecast function, but I'm not sure if that's the best way to do it.

So based on the attached spreadsheet, how would you project out the next 8 weeks of orders? How would you project how many driver hours & drivers are needed to support the growth?

Thanks in advance!

[mrice]

One way to do a linear projection is to select the values in a single row for weeks 1 to 8. Then grab the fill handle and drag over weeks 9 onwards.

Plot a scatter chart to have a look at the results.

[JieJenn]

There's a forecast function that I use occasionally.

In J2, enter =FORECAST(I2,$C$2:I2,$B$2:H2) and drag across and down

[Speshul]

Place in J2 and copy throughout
=FORECAST(J$1,$B2:I2,$B$1:I$1)

[Speshul]

There's a forecast function that I use occasionally.

In J2, enter =FORECAST(I2,$C$2:I2,$B$2:H2) and drag across and down

Interesting. We both use it differently, and get slightly different results...

But your Row 5 shows Known X's and Y's as large tables, I'm not sure if that is intended?
I5: =FORECAST(I5,$C$2:I5,$B$2:H5)

[JieJenn]

Yup. Intentionally.

Interesting. We both use it differently, and get slightly different results...

But your Row 5 shows Known X's and Y's as large tables, I'm not sure if that is intended?
I5: =FORECAST(I5,$C$2:I5,$B$2:H5)

[Speshul]

Is my way wrong or just another way to do it?

I use it this way because I can change the index (X, week number in this case) to whatever and I'll get the expected values assuming the trend continue (instead of 8, 9, 10, 11, my method calculates 8, 9, 10, 20 if need be), which is what forcasting is...or am I mistaken?

[misteryu1029]

So for Known Y's & Known X's, isn't "Weeks" known X's and orders "Known Y's?"

[misteryu1029]

I feel like Speshul's way would be the correct way to do this..can anyone confirm?

[Speshul]

Weirdly they both return the same result in this test.


Test the below:
Where D15:D23 is 10,20,30,40,50,60,70,80,90
and C15:C23 is 1,2,3,4,5,6,7,8,9

To find out what should be in C24, the 10th number in the series in column C which is 100, we use

Mine:
=FORECAST(10,D15:D23,C15:C23)

JieJenn:
=FORECAST(D23,$D$16:D23,$D$15:D22)

Edit: The results do vary greatly when you start changing the number in D to not be perfectly linear though.

[JieJenn]

When I try to forecast something, I look at the entire period rather than just every x amount of periods. By making the table dynamic, the result will be more accurate based on the historical trent.

Weirdly they both return the same result in this test.


Test the below:
Where D15:D23 is 10,20,30,40,50,60,70,80,90
and C15:C23 is 1,2,3,4,5,6,7,8,9

To find out what should be in C24, the 10th number in the series in column C which is 100, we use

Mine:
=FORECAST(10,D15:D23,C15:C23)

JieJenn:
=FORECAST(D23,$D$16:D23,$D$15:D22)

Edit: The results do vary greatly when you start changing the number in D to not be perfectly linear though.