GROWTH_q

Updated 2023-11-01 10:49:41.037000

Syntax

SELECT [westclintech].[wct].[GROWTH_q] (
  <@Known_y_Known_x_RangeQuery, nvarchar(4000),>
 ,<@New_x, float,>)

Description

Use the scalar function GROWTH_q to calculate predicted exponential growth by using existing data. GROWTH returns the y-value for a new x-value that you specify using existing x-values and y-values.

GROWTH=ar^t

Wherea = 10mr = 10bb = SLOPE(log(known_y) / log(10), known_x)m = INTERCEPT(log(known_y) / log(10), known_x)t = new x

Arguments

@Known_y_Known_x_RangeQuery

the select statement, as text, used to determine the known y- and x-values to be used in the GROWTH_q calculation.

@New_x

the new x-value for which you want GROWTH to calculate the y-value.

Return Type

float

Remarks

If the number of y-data points is not equal to the number of x-data points, GROWTH will return an error.

If any of the values in the known-y dataset is less than or equal to zero, GROWTH returns an error.

No GROUP BY is required for this function even though it produces aggregated results.

Examples

Using the de-normalized table #f2, project the sales for MSFT in 2009.

CREATE TABLE #f2

(

    [SYMBOL] [nchar](10) NOT NULL,

    [FY] [float] NOT NULL,

    [SALES] [float] NOT NULL,

    [EBIT] [float] NOT NULL,

    [DEPREC] [float] NOT NULL,

    [NETINC] [float] NOT NULL,

    [EPS] [float] NOT NULL,

    [TAXRATE] [float] NOT NULL

);

INSERT INTO #f2

VALUES

('GOOG', 2008, 21795.55, 5853.6, 1499.89, 4226.86, 13.31, 27.79);

INSERT INTO #f2

VALUES

('GOOG', 2007, 16593.99, 5673.98, 967.66, 4203.72, 13.29, 25.91);

INSERT INTO #f2

VALUES

('GOOG', 2006, 10604.92, 4011.04, 571.94, 3077.45, 9.94, 23.28);

INSERT INTO #f2

VALUES

('GOOG', 2005, 6138.56, 2141.68, 293.81, 1465.4, 5.02, 31.58);

INSERT INTO #f2

VALUES

('GOOG', 2004, 3189.22, 650.23, 148.47, 399.12, 1.46, 38.62);

INSERT INTO #f2

VALUES

('GOOG', 2003, 1465.93, 346.65, 50.19, 105.65, 0.41, 69.52);

INSERT INTO #f2

VALUES

('GOOG', 2002, 439.51, 184.92, 18.03, 99.66, 0.45, 46.11);

INSERT INTO #f2

VALUES

('GOOG', 2001, 86.43, 10.07, 10.03, 6.99, 0.04, 30.62);

INSERT INTO #f2

VALUES

('GOOG', 2000, 19.11, -14.69, 0, -14.69, -0.22, 0);

INSERT INTO #f2

VALUES

('GOOG', 1999, 0.22, -6.08, 0, -6.08, -0.14, 0);

INSERT INTO #f2

VALUES

('EBAY', 2008, 8541.26, 2183.56, 719.81, 1779.47, 1.36, 18.51);

INSERT INTO #f2

VALUES

('EBAY', 2007, 7672.33, 750.85, 576.61, 348.25, 0.25, 53.62);

INSERT INTO #f2

VALUES

('EBAY', 2006, 5969.74, 1547.06, 521.63, 1125.64, 0.79, 27.24);

INSERT INTO #f2

VALUES

('EBAY', 2005, 4552.4, 1549.33, 369.54, 1082.04, 0.78, 30.16);

INSERT INTO #f2

VALUES

('EBAY', 2004, 3271.31, 1128.23, 249.42, 778.22, 0.57, 30.48);

INSERT INTO #f2

VALUES

('EBAY', 2003, 2165.1, 661.5, 156.46, 447.18, 0.34, 31.25);

INSERT INTO #f2

VALUES

('EBAY', 2002, 1214.1, 398.13, 76.64, 249.89, 0.21, 36.66);

INSERT INTO #f2

VALUES

('EBAY', 2001, 748.82, 162.94, 78.39, 90.45, 0.08, 49.1);

INSERT INTO #f2

VALUES

('EBAY', 2000, 431.42, 77.96, 38.13, 48.29, 0.04, 41.98);

INSERT INTO #f2

VALUES

('EBAY', 1999, 224.72, 18.14, 19.75, 9.57, 0.01, 46.7);

INSERT INTO #f2

VALUES

('AMZN', 2008, 19166, 901, 340, 645, 1.49, 27.41);

INSERT INTO #f2

VALUES

('AMZN', 2007, 14835, 660, 271, 476, 1.12, 27.88);

INSERT INTO #f2

VALUES

('AMZN', 2006, 10711, 377, 210, 190, 0.45, 49.6);

INSERT INTO #f2

VALUES

('AMZN', 2005, 8490, 428, 118, 333, 0.78, 22.2);

INSERT INTO #f2

VALUES

('AMZN', 2004, 6921.12, 355.87, 75.66, 588.45, 1.39, -65.36);

INSERT INTO #f2

VALUES

('AMZN', 2003, 5263.7, 38.99, 72.74, 35.28, 0.08, 9.51);

INSERT INTO #f2

VALUES

('AMZN', 2002, 3932.94, -150.63, 81.66, -149.93, -0.4, 0);

INSERT INTO #f2

VALUES

('AMZN', 2001, 3122.43, -526.43, 264.03, -556.75, -1.53, 0);

INSERT INTO #f2

VALUES

('AMZN', 2000, 2761.98, -1106.68, 404.78, -1411.27, -4.02, 0);

INSERT INTO #f2

VALUES

('AMZN', 1999, 1639.84, -643.2, 251.5, -719.97, -2.2, 0);

INSERT INTO #f2

VALUES

('CSCO', 2008, 39540, 10255, 1977, 8052, 1.31, 21.48);

INSERT INTO #f2

VALUES

('CSCO', 2007, 34922, 9461, 1569, 7333, 1.17, 22.49);

INSERT INTO #f2

VALUES

('CSCO', 2006, 28484, 7633, 1353, 5580, 0.89, 26.9);

INSERT INTO #f2

VALUES

('CSCO', 2005, 24801, 8036, 1020, 5741, 0.87, 28.56);

INSERT INTO #f2

VALUES

('CSCO', 2004, 22045, 6992, 1199, 4968, 0.7, 28.95);

INSERT INTO #f2

VALUES

('CSCO', 2003, 18878, 5013, 1463, 3578, 0.5, 28.63);

INSERT INTO #f2

VALUES

('CSCO', 2002, 18915, 2710, 1957, 1893, 0.25, 30.15);

INSERT INTO #f2

VALUES

('CSCO', 2001, 22293, -874, 2236, -1014, -0.14, 0);

INSERT INTO #f2

VALUES

('CSCO', 2000, 18928, 4343, 863, 2668, 0.36, 38.57);

INSERT INTO #f2

VALUES

('CSCO', 1999, 12173, 3203, 489, 2023, 0.29, 36.84);

INSERT INTO #f2

VALUES

('MSFT', 2008, 60420, 23814, 1872, 17681, 1.87, 25.75);

INSERT INTO #f2

VALUES

('MSFT', 2007, 51122, 20101, 1406, 14065, 1.42, 30.03);

INSERT INTO #f2

VALUES

('MSFT', 2006, 44282, 18262, 990, 12599, 1.2, 31.01);

INSERT INTO #f2

VALUES

('MSFT', 2005, 39788, 16628, 884, 12254, 1.12, 26.31);

INSERT INTO #f2

VALUES

('MSFT', 2004, 36835, 12196, 817, 8168, 0.75, 33.03);

INSERT INTO #f2

VALUES

('MSFT', 2003, 32187, 11054, 1090, 7531, 0.69, 31.87);

INSERT INTO #f2

VALUES

('MSFT', 2002, 28365, 7875, 1014, 5355, 0.48, 32);

INSERT INTO #f2

VALUES

('MSFT', 2001, 25296, 11525, 1238, 7721, 0.69, 33.01);

INSERT INTO #f2

VALUES

('MSFT', 2000, 22956, 14275, 900, 9421, 0.85, 34);

INSERT INTO #f2

VALUES

('MSFT', 1999, 19747, 11891, 483, 7785, 0.71, 34.53);

select 'MSFT' as [Ticker],

       wct.GROWTH_q('Select SALES, FY from #f2 where SYMBOL = ' + CHAR(39) + 'MSFT' 

                 + CHAR(39), 2009) as [2009 Sales];

This produces the following result.

{"columns":[{"field":"Ticker"},{"field":"2009 Sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Ticker":"MSFT","2009 Sales":"65495.2084347367"}]}

Using the de-normalized table #f2, calculate the EPS for MSFT for the next 6 years.

with mycte

as (Select 2009 as FY

    UNION ALL

    Select FY + 1

    from mycte

    where FY < 2014)

select 'MSFT' as [Ticker],

       mycte.FY as [FY],

       wct.GROWTH_q('Select EPS

      ,FY

      from #f2

      where SYMBOL = ' + CHAR(39) + 'MSFT' + CHAR(39), mycte.FY) as [EPS]

from mycte;

This produces the following result.

{"columns":[{"field":"Ticker"},{"field":"FY","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"EPS","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Ticker":"MSFT","FY":"2009","EPS":"1.63289654265364"},{"Ticker":"MSFT","FY":"2010","EPS":"1.81780539503368"},{"Ticker":"MSFT","FY":"2011","EPS":"2.02365328598437"},{"Ticker":"MSFT","FY":"2012","EPS":"2.25281134771825"},{"Ticker":"MSFT","FY":"2013","EPS":"2.50791921894833"},{"Ticker":"MSFT","FY":"2014","EPS":"2.79191545050624"}]}