Ask the Expert

Using SQL to forecast growth, part 2

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: