FORECAST
Updated: 15 January 2011
Use the aggregate FORECAST to calculate a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted using linear regression.
Syntax
SELECT [wctStatistics].[wct].[FORECAST] (
<@fmtFORECAST, nvarchar(4000),>)
The syntax has changed as of release 1.07 and the function has changed from a scalar to an aggregate. Please make the appropriate changes when you upgrade to 1.07.
Arguments
@fmtFORECAST
An nvarchar string formatted by the fmtFORECAST function containing the known-y and known-x and new-x values to be used by the FORECAST calculation.
Return Types
float
Remarks
· FORECAST is an AGGREGATE function and follows the same conventions as all other AGGREGATE functions in SQL Server.
· FORECAST is a one-pass solution. For a two-pass solution, use the FORECAST_q function.
Examples
In this example, we calculate the trend for a single set of x- and y-values with a single new x value
SELECT wct.FORECAST(wct.fmtFORECAST(12.5, y, x)) as FORECAST
FROM (
SELECT 0.75, 1 UNION ALL
SELECT 2.5, 2 UNION ALL
SELECT 6.75, 3 UNION ALL
SELECT 10, 4
) n(x,y)
This produces the following result
FORECAST
----------------------
4.79116945107399
(1 row(s) affected)
In this example, we will populate some temporary table with some historical financial information and then calculate a forecast. First, create the table and put some data in it:
CREATE TABLE #c(
SYM NVARCHAR(5),
YE BIGINT,
REV FLOAT,
GPROF FLOAT,
OPINC FLOAT,
NETINC FLOAT
)
INSERT INTO #c VALUES('YHOO',2009,6460.32,3588.57,386.69,597.99)
INSERT INTO #c VALUES('YHOO',2008,72.5,4185.14,12.96,418.92)
INSERT INTO #c VALUES('YHOO',2007,6969.27,4130.52,695.41,639.16)
INSERT INTO #c VALUES('YHOO',2006,6425.68,3749.96,940.97,751.39)
INSERT INTO #c VALUES('YHOO',2005,5257.67,3161.47,1107.73,1896.23)
INSERT INTO #c VALUES('GOOG',2009,23650.56,14806.45,8312.19,6520.45)
INSERT INTO #c VALUES('GOOG',2008,21795.55,13174.04,5537.21,4226.86)
INSERT INTO #c VALUES('GOOG',2007,16593.99,9944.9,54.44,4203.72)
INSERT INTO #c VALUES('GOOG',2006,10604.92,6379.89,3550,3077.45)
INSERT INTO #c VALUES('GOOG',2005,6138.56,3561.47,2017.28,1465.4)
INSERT INTO #c VALUES('MSFT',2009,62484,509,24167,18760)
INSERT INTO #c VALUES('MSFT',2008,58437,46282,21225,14569)
INSERT INTO #c VALUES('MSFT',2007,60420,48822,22271,17681)
INSERT INTO #c VALUES('MSFT',2006,51122,40429,18438,14065)
INSERT INTO #c VALUES('MSFT',2005,44282,36632,16064,12599)
INSERT INTO #c VALUES('ORCL',2009,26820,21056,9062,6135)
INSERT INTO #c VALUES('ORCL',2008,23252,18458,8321,5593)
INSERT INTO #c VALUES('ORCL',2007,22430,17449,7844,5521)
INSERT INTO #c VALUES('ORCL',2006,17996,13805,5974,4274)
INSERT INTO #c VALUES('ORCL',2005,14380,11145,4736,3381)
INSERT INTO #c VALUES('SAP',2009,10672,6980,2588,1748)
INSERT INTO #c VALUES('SAP',2008,11575,7370,2701,1847)
INSERT INTO #c VALUES('SAP',2007,10256,6631,2698,1906)
INSERT INTO #c VALUES('SAP',2006,9393,6064,2578,1871)
INSERT INTO #c VALUES('SAP',2005,8509,5460,2337,1496)
In this example, we will forecast the revenue (REV) against the year (YE)
SELECT SYM
,ROUND(wct.FORECAST(wct.fmtFORECAST(2010,REV,YE)), 0) as [2010 Revenue]
,ROUND(wct.FORECAST(wct.fmtFORECAST(2011,REV,YE)), 0) as [2011 Revenue]
,ROUND(wct.FORECAST(wct.fmtFORECAST(2012,REV,YE)), 0) as [2012 Revenue]
FROM #c
GROUP BY SYM
This produces the following result.
SYM 2010 Revenue 2011 Revenue 2012 Revenue
----- ---------------------- ---------------------- ----------------------
GOOG 29621 34243 38864
MSFT 64093 68465 72837
ORCL 27003 30016 33030
SAP 12033 12684 13335
YHOO 3853 3458 3063
(5 row(s) affected)
To calculate the net income using the revenue projections from the above query, we could enter the following statement.
SELECT #c.SYM
,ROUND(wct.FORECAST(wct.fmtFORECAST([2010 Revenue],NETINC, REV)), 0) as [2010 Net Income]
,ROUND(wct.FORECAST(wct.fmtFORECAST([2011 Revenue],NETINC, REV)), 0) as [2011 Net Income]
,ROUND(wct.FORECAST(wct.fmtFORECAST([2012 Revenue],NETINC, REV)), 0) as [2012 Net Income]
FROM (
SELECT SYM
,ROUND(wct.FORECAST(wct.fmtFORECAST(2010,REV,YE)), 0) as [2010 Revenue]
,ROUND(wct.FORECAST(wct.fmtFORECAST(2011,REV,YE)), 0) as [2011 Revenue]
,ROUND(wct.FORECAST(wct.fmtFORECAST(2012,REV,YE)), 0) as [2012 Revenue]
FROM #c
GROUP BY SYM) n, #c
WHERE n.sym = #c.sym
GROUP BY #c.SYM
This returns the following results.
SYM 2010 Net Income 2011 Net Income 2012 Net Income
----- ---------------------- ---------------------- ----------------------
GOOG 7107 8176 9246
MSFT 18222 19566 20910
ORCL 6367 7060 7753
SAP 1945 2002 2059
YHOO 807 789 771
(5 row(s) affected)
As the following query demonstrates, this returns a different result than if we had just looked at the net income over time.
SELECT SYM
,ROUND(wct.FORECAST(wct.fmtFORECAST(2010,NETINC, YE)), 0) as [2010 Net Income]
,ROUND(wct.FORECAST(wct.fmtFORECAST(2011,NETINC, YE)), 0) as [2011 Net Income]
,ROUND(wct.FORECAST(wct.fmtFORECAST(2012,NETINC, YE)), 0) as [2012 Net Income]
FROM #c
GROUP BY SYM
This produces the following result.
SYM 2010 Net Income 2011 Net Income 2012 Net Income
----- ---------------------- ---------------------- ----------------------
GOOG 7277 8403 9529
MSFT 18100 19383 20665
ORCL 6346 7029 7712
SAP 1918 1966 2014
YHOO -18 -311 -604
(5 row(s) affected)