Using SQL to forecast growth, part 2

Here is the continuation of Jason's answer. See part 1.

    Requires Free Membership to View

    Login

Here's the actual SQL and its resulting rows:

select Cardinal + 1 + ( select max(TimePeriod) 
                          from History ) TimePeriod,
       ( select max(Magnitude) 
           from History 
           where TimePeriod = ( select max(TimePeriod) 
                                  from History ) ) + 
       ( ( select avg(H.Magnitude-PrevH.Magnitude)
                  from History H
                       inner join
                       History PrevH
                    on H.TimePeriod-1 = PrevH.TimePeriod )
         * ( Cardinal + 1 ) ) Forecasted
  from TwoDigitCardinals
  order by Cardinal;  

TIMEPERIOD FORECASTED
---------- ----------
5                2466
6                2832
7                3198
8                3564
9                3930
10               4296
11               4662
...
102             37968
103             38334
104             38700
For exponential growth, the SQL to get our multiplier looks like this:
select avg(H.Magnitude/PrevH.Magnitude) Multiplier
  from History H
       inner join
       History PrevH
    on H.TimePeriod-1 = PrevH.TimePeriod;

Multiplier
----------
1.284
Mathematically, my formula for exponential growth looks like f(x) = a^x + b, where x is the time period extending into the future, and f(x) is the projected magnitude.

Here is the SQL and result set for exponential growth, which uses the POWER function to raise the multiplier to a power relative to the forecasted time period. (Please note, the POWER function or a function with similar functionality should exist in your SQL implementation. I know POWER exists in Oracle, MS SQL Server, and MySQL.)

select Cardinal + 1 + ( select max(TimePeriod) 
                          from History ) TimePeriod,
       ( select max(Magnitude) 
           from History 
           where TimePeriod = ( select max(TimePeriod) 
                                  from History ) ) * 
       power( ( select avg(H.Magnitude/PrevH.Magnitude)
                  from History H
                       inner join
                       History PrevH
                    on H.TimePeriod-1 = PrevH.TimePeriod ),
              Cardinal + 1) Forecast
  from TwoDigitCardinals
  order by Cardinal;  

TIMEPERIOD FORECASTED
---------- -------------------
5                     2695.897
6                     3460.887
7                     4442.951
8                     5703.686
9                     7322.167
10                    9399.911
11                   12067.236
...
102         89879002753597.187
103        115383130030686.330
104	148124325902641.780
You can see how quickly the exponential growth model can grow. You'll want to tailor which model you use to your specific circumstances. Perhaps, even an average or a weighted average of the two will give you a better forecast. We can create any mathematical function such that f(x) is our magnitude and use the view based on the Cardinals table to create forecasts.

For More Information


This was first published in March 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top