Forcast Function

[Guest]

Hello All,
Background: I am trying to figure out how to project my sales numbers for
the year, given a few monthly entries. In Excel I can use the Forcast
function, but I am working in MS Access.

Question: Does anyone know how to break down the way that the Excel Forcast
function predicts it's next value given previous values?

Example: Jan - 2; Feb - 4; March - 6. The rest of the year's numbers should
project to....Apr - 8; May - 10; June - 12; July - 14 ...etc.

The equation for FORECAST is a+bx, where:
a = y - bx
AND
b = (sum(x-x)(y-y)/sum(x-x)^2)
AND
where x and y are the sameple means AVERAGE(known_x's) and AVERAGE(known_y's)

Thanks for any assistance

Rodney

 

[Guest]

Haven't you answered your own question here?

Dave

 

[Guest]

No,...Excel gives me the formual, but I don't really know how to decode that
algebraic formula enough to come up with a forcasted number. I may have
phased my question incorrectly though.
I think that we figured out the answer though:
Jan(1) - 3; Feb(2) - 6; March(3) - 9...
Plugging the number into this equation will give you the next forcasted
number (given any two months): y3 = (((y2-y1)/(x2-x1))*(x3-x1))+y1

Example: Third month sales (y3) equals (2nd month sales (y2) minus 1st month
sales (y1) divided by 2nd month (x2) minus 1st month (x1)) times (3rd month
(x3) minus 1st month (x1)) plus 1st month sales (y1)

Thanks

 

[Guest]

Hello ,
I need to use forecast formula outside excel.
I explored the forecast method in excel and could not figure out the
formula. X(Bar) & Y(Bar) signify what ?

Thanks in advance

 

[David Biddulph]

X bar is the average X value, and Y bar is the average Y value.

 

[Guest]

Hello David,

Please see the below formula which i took from excel help: -

The equation for FORECAST is a+bx, where:
a=y(bar) - bx(bar)
and:
b=Summation of{(x-x(bar)) (y-y(bar))} / Summation of (x-x(bar)) power2

and where x and y are the sample means AVERAGE(known_x's) and AVERAGE(known
y's).

So if X & Y are the average values then i want to know what x(bar)
& Y (bar) stand for ?

Please see the excel formula for FORECAST method incase the above formula is
not clearly stated.

 

[Guest]

I came up with the below formula which mimics the Excel Forecast formula.

Linear Regression Forecast Equation: =((((SUM(y2-y1) + (y2n-y1n)) /
(SUM(x2-x1))*(x2(n+1)-x1(n-1)))+y(n-1))-(x2(n-1)-x2(n-2)));…..n=last point;
y=variable points; x = constants

Example (put it in Excel and compare it):
Example Details: For a year...you have 3 known data points (must have at
least 2). Place the formula in the Variable Y column and keep increasing the
knwon X months. The formual will perform a linear regression forecast using
the previous data points,...plotint the future data points.

Header----------- Variable Y (Actual Data) Known X (Months)
row 1 ------------ 2 1
row 2 ------------ 4 2
row 3 ------------ 6 3
row 4 ------------ equation 1 below 4
row 5 ------------ equation 2 below 5
row 6 ------------ equation 3 below 6
row 7 ------------ equation 4 below 7
8

equation 1:
=(((((A2-A1)+((A3-A2)*2))/((B2-B1)+(B3-B2)+(B4-B3))*(B5-B3))+A3)-(A3-A2))

equation 2:
=(((((A2-A1)+(A3-A2)+((A4-A3)*2))/((B2-B1)+(B3-B2)+(B4-B3)+(B5-B4))*(B6-B4))+A4)-(A4-A3))

equation 3:
=(((((A2-A1)+(A3-A2)+(A4-A3)+((A5-A4)*2))/((B2-B1)+(B3-B2)+(B4-B3)+(B5-B4)+(B6-B5))*(B7-B5))+A5)-(A5-A4))

equation 3:
=(((((A2-A1)+(A3-A2)+(A4-A3)+(A5-A4)+((A6-A5)*2))/((B2-B1)+(B3-B2)+(B4-B3)+(B5-B4)+(B6-B5)+(B7-B6))*(B8-B6))+A6)-(A6-A5))

////////////////////////////////////////////////////////////////////////////////////////

 

[David Biddulph]

Please stick to one thread.
--
David Biddulph

Hello David,

Please see the below formula which i took from excel help: -

The equation for FORECAST is a+bx, where:
a=y(bar) - bx(bar)
and:
b=Summation of{(x-x(bar)) (y-y(bar))} / Summation of (x-x(bar)) power2

and where x and y are the sample means AVERAGE(known_x's) and
AVERAGE(known
y's).

So if X & Y are the average values then i want to know what x(bar)
& Y (bar) stand for ?

Please see the excel formula for FORECAST method incase the above formula
is
not clearly stated.

 

[Guest]

b is SLOPE(y,x)
a is INTERCEPT(y,x)
which are available as separate functions in Excel. Start with them and
build your way up.

In Excel 2003 the calculation formula was rearranged and a misprint was
introduced into Help for FORECAST (also in Help for SLOPE and INTERCEPT),
x(bar) and y(bar) are the sample means, x and y are the individual
observations.

You might also get some useful information from
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/d6a03470e7a1c650

Jerry