Title: A Finger in the Wind: Forecasting Techniques for Capacity Planning
1A Finger in the WindForecasting Techniques for
Capacity Planning
- Linwood Merritt
- Bank of America
- May, 2005
- lin.merritt_at_bankofamerica.com
2Purpose of Presentation
- Robust Capacity Planning entails the analysis of
data to project future demand. This analysis can
be as simple as a linear trend of historical
demand data, and can be as complex as
multivariate regression using business drivers
combined with analysis of changing business
functionality. This presentation is intended as a
Forecasting 101 introduction to trending
techniques, and discusses the use of Excel and
SAS to perform trend analysis of computer
resources and produce Capacity Planning
forecasts.
3Categories of Forecasting Approaches
- Date-based trending
- Linear
- Exponential
- Business driver trending
- Single variable
- Multiple variables
4Comparison of Tools
- Basic analysis of a relatively small amount of
data spreadsheet product such as Excel. - Automation of projections over a large amount of
data and instances programming language such as
SAS.
5Date-Based Trending
- Steering by your wake using the past to
predict the future. - Linear use of Least Squares algorithm single
answer to set of differential equations. - Exponential Least Squares trending of
exponents.
6Types of Growth
- Linear straight line (same delta each interval)
- Exponential compound growth (same percentage
each interval)
7Linear Calculations
- Linear straight line (y b mx)
- Linear annual growth take difference of
projections one-year-apart. - Linear monthly growth
- (annual growth difference) / 12
8Linear Growth Example
- Jan 2004 100, Jan 2005 120
- Linear Annual Growth 120-100 20
- Monthly Growth 20/12 1.67
- Monthly projections 100, 101.7, 103.3,
- Annual Projections 100, 120, 140,
9Exponential Calculations
- Exponential compound growth (y b m x)
- log (y) log b x log m
- y b m x
- Take linear trend of log(y), convert back with
exp(y). - Compound annual growth take ratio of projections
one-year-apart. - Compound monthly growth
- (compound annual growth) (1/12)
10Exponential Growth Example
- Jan 2004 100, Jan 2005 120
- Compound Annual Growth 120/100 1.2
- Jan 2005 is 120 of Jan 2004, a 20 compound
annual growth. - Monthly Growth 1.2(1/12) 1.015
- Monthly projections 100, 101.5, 103.1,
- Annual Projections 100, 120, 144,
11Linear Regression Basics
- Formula y b mx
- x independent variable (Date or Business
Driver) - y dependent variable (Result)
- m slope of straight line
- Excel X Variable 1
- SAS date or business driver coefficient
- b Intercept
- Excel (if date-based) days since 12/31/1899
- SAS (if date-based) days since 1/1/1960
12Linear Date-Based Trending Using Excel Trend
Function
TREND(B2B32,A2A32,A2)
13Excel Data Analysis Regression Tool
Regression Statistics
Multiple R 0.863991366
R Square 0.746481081
Adjusted R Square 0.73773905
Standard Error 33085.82124
Observations 31
ANOVA
df SS MS F Significance F
Regression 1 93473879391 93473879391 85.38988514 3.83704E-10
Residual 29 31745475448 1094671567
Total 30 1.25219E11
Coefficients Standard Error t Stat P-value Lower 95
Intercept -7118703.027 816014.4395 -8.723746398 1.32806E-09 -8787640.858
X Variable 1 201.8965717 21.84870646 9.240664757 3.83704E-10 157.2109253
Month Jan-04 Feb-04 Mar-04 Apr-04 May-04
550742.04 557000.84 562855.84 569114.63 575171.53
B17B18B20
Intercept (X Variable 1) Month
14Linear Date-Based Trending in SAS
proc reg noprint datacmg outestreglin
tableout model hist date / selectionrsquare
data proj set reglin (inr) cmg (inc) retain
il dl 0 if r then do if hist -1 il
intercept dl date delete end projl
il datedl
15SAS Proc Reg Output
Obs _MODEL_ _TYPE_ _DEPVAR_ _RMSE_
Intercept date hist _IN_ _P_ _EDF_
_RSQ_ 1 MODEL1 PARMS hist 33085.82
-2693937.76 201.897 -1 1 2 29
0.74648
Obs date hist projl 1
01JAN01 350000 329665.30 2 01FEB01
340000 335924.09 3 01MAR01 410000
341577.19 4 01APR01 320000
347835.99 37 01JAN04 .
550742.04 38 01FEB04 . 557000.84
39 01MAR04 . 562855.84 40
01APR04 . 569114.63 41 01MAY04
. 575171.53
16Exponential Date-Based Trending Using Excel
Growth Function
GROWTH(B2B32,A2A32,A2)
17Exponential Date-Based Trending Using Logarithms
EXP(TREND(C2C32,A2A32,A2))
18Exponential Date-Based Trending in SAS
histl log(hist)
proc reg noprint datacmg outestregexp
tableout model histl date / selectionrsquare
data proj set regexp (inr) cmg (inc) retain
il dl 0 if r then do if hist -1 ie
intercept de date delete end proje
exp (ie datede)
19SAS Exponential Regression Output
Obs _MODEL_ _TYPE_ _DEPVAR_ _RMSE_
Intercept date histl _IN_ _P_ _EDF_
_RSQ_ 1 MODEL1 PARMS histl 0.081097
5.49908 .000482211 -1 1 2 29 0.73655
Obs date hist proje 1
01JAN01 350000 334592.56 2 01FEB01
340000 339631.79 3 01MAR01 410000
344248.56 4 01APR01 320000
349433.23 37 01JAN04 .
567326.04 38 01FEB04 . 575870.44
39 01MAR04 . 583980.04 40
01APR04 . 592775.26 41 01MAY04
. 601412.86
20Linear vs. Exponential Results
21Footnote SAS/Graph Linear Trending
- symbol1 irl colorblue vw h.5
- proc gplot datacmg plot hist date / regeqn
22Business Driver Based Forecasting
- Linear least squares regression using business
driver instead of date as independent variable - Formula y b mx
- x independent variable (Business Driver)
- y dependent variable (Result)
- m slope of straight line
- Excel X Variable 1
- SAS business driver coefficient
- b Intercept
23Business Driver Based Forecasting Steps
- Map business drivers to servers.
- Use historical data to correlate.
- Use business driver projections to build
forecast.
24Single Business Driver Trending Using Excel
Trend Function
TREND(B2B32,C2C32,C2)
25Excel Tools, Data Analysis, Regression
26Single Business Driver Trending Using Excel Data
Analysis
Business1!B17Business1!B18H2
Intercept (X Variable 1) BusDriverA
27Single Business Driver Trending in SAS
proc reg noprint datacmg outestregbusa tableout
model hist busa / selectionrsquare
data proj set regbusa (ina) cmg (inc) retain
ia da 0 if a then do if hist -1 ia
intercept da busa delete end proja
ia busada
28SAS Business Driver Regression Output
Obs _MODEL_ _TYPE_ _DEPVAR_ _RMSE_
Intercept busa hist _IN_ _P_ _EDF_
_RSQ_ 1 MODEL1 PARMS hist 33330.20
128231.62 59.0956 -1 1 2 29
0.74272
Obs date hist
busa proja 1
01JAN01 350000
3500 335066.38 2 01FEB01
340000 3600
340975.94 3 01MAR01
410000 3700 346885.51
4 01APR01 320000
3900 358704.64 37
01JAN04 .
7900 595087.21 38 01FEB04
. 7800
589177.65 39 01MAR04
. 7900 595087.21
40 01APR04 .
8000 600996.78 41
01MAY04 .
8200 612815.91
29Multiple Business Drivers
- Linear least squares multivariate regression
using multiple business drivers as independent
variables - Formula y b ma nb
- a, b, independent variables (Business
Drivers) - y dependent variable (Result)
- m, n, coefficients of each Business Driver
30Multiple Business Driver Trending Using Excel
Trend Function
TREND(B2B32,C2D32,C2D2)
31Multiple Business Drivers Using Excel Data
Analysis Regression
32Excel Data Analysis of Multiple Variables
33Data Analysis Projections for Multiple Business
Drivers
Business2!B17 Business2!B18'Regression
Data'!H2 Business2!B19'Regression Data'!I2
Intercept (X Variable 1) BusDriverA
(X Variable 2) BusDriverB
34Multiple Business Driver Trending in SAS
proc reg noprint datacmg outestregbusab
tableout model hist busa busb /
selectionrsquare
data proj set regbusab (inb) cmg (inc) retain
ib da db 0 if b then do if hist -1 and
_IN_ 2 ib intercept da busa db
busb delete end projb ib busada
busbdb
35SAS Multivariate Regression Output
Obs _MODEL_ _TYPE_ _DEPVAR_ _RMSE_ Intercept
busa busb hist _IN_ _P_ _EDF_ _RSQ_ 1
MODEL1 PARMS hist 33330.20 128231.62
59.0956 . -1 1 2 29 0.74272 2
MODEL1 PARMS hist 54624.94 205255.71 .
253.480 -1 1 2 29 0.30895 3
MODEL1 PARMS hist 33901.16 131950.59
60.1175 -10.300 -1 2 3 28 0.74301
Obs date hist
busa busb projb 1
01JAN01 350000
3500 500 337211.58 2
01FEB01 340000
3600 600 342193.28 3
01MAR01 410000
3700 640 347793.01 4
01APR01 320000
3900 800 358168.43 37
01JAN04 .
7900 1000 596578.32 38
01FEB04 .
7800 1020 590360.56 39
01MAR04 .
7900 950 597093.34 40
01APR04 .
8000 990 602693.07 41
01MAY04 .
8200 980 614819.58
36Negative Coefficient
- A negative coefficient should send a warning
flag - Intercept 131950.59
- X Variable 1 60.1175
- X Variable 2 -10.300
- For 01JAN04,
- Projection 131950.6 60.17900 - 10.31000
596578 - If Business Driver B decreases, the projection
increases - Projection 131950.6 60.17900 - 10.3500
601729 - If the business driver increases enough, the
projection may be negative!
37Negative CoefficientsNonlinear regression of a
linear equation (w/ bounds on coefficients)
proc nlin noprint datacmg outestregnlin
maxiter10000 methodNEWTON parms intercept0
Ren1 .00000003 Ren2 .00000003 bounds
0ltIntercept,0ltRen1, 0ltRen2 model hist
intercept busaRen1 busbRen2
data proj set regnlin (inn) cmg (inc) retain
ni na nb 0 if n then do if _TYPE_
'FINAL' ni intercept na ren1 nb
ren2 delete end projn ni busana
busbnb
38SAS Non-Linear Regression Output
Obs _TYPE_ _STATUS_ _NAME_ _ITER_
_SSE_ intercept Ren1 Ren2 1
ITER 2 Iteration 0 5.6357E12
0.00 0.00 3E-8 2 ITER
2 Iteration 1 5.6357E12
0.00 0.00 1.081384E-8 3 ITER 2
Iteration 2 5.6357E12
0.00 0.00 4.818163E-9 4 ITER 2
Iteration 3 5.6357E12
0.00 0.00 1.070866E-9 5 ITER 2
Iteration 4 5.6357E12
0.00 0.00 4.8535E-10 6 ITER 2
Iteration 5 5.6357E12
0.00 0.00 1.19403E-10 7 ITER 2
Iteration 6 5.6357E12
0.00 0.00 5.04498E-12 8 ITER 2
Iteration 7 5.6357E12
0.00 0.00 -6.6429E-11 9 ITER 2
Iteration 8 5.6357E12
0.00 0.00 -8.8765E-11 10 ITER 2
Iteration 9 5.6357E12
0.00 0.00 -9.5745E-11 11 ITER 2
Iteration 10 5.6357E12
0.00 0.00 -9.7926E-11 12 ITER 2
Iteration 11 5.6357E12
0.00 0.00 -9.9289E-11 13 ITER 2
Iteration 12 5.6357E12
0.00 0.00 -9.9715E-11 14 ITER 2
Iteration 13 5.6357E12
0.00 0.00 -9.9981E-11 15 ITER 2
Iteration 14 5.6357E12
0.00 0.00 -9.9992E-11 16 ITER 2
Iteration 15 5.6357E12
0.00 0.00 -9.9998E-11 17 ITER 2
Iteration 16 5.6357E12
0.00 0.00 -9.9998E-11 18 ITER 2
Iteration 17 32216155095
128231.62 59.10 0 19 FINAL 0
Converged . 32216155095
128231.62 59.10 0 20 COVB 0
Converged intercept . 32216155095
1063952717.10 -207093.13 0 21 COVB
0 Converged Ren1 . 32216155095
-207093.13 41.71 0 22 COVB 0
Converged Ren2 . 32216155095
0.00 0.00 0
39SAS Non-Linear Regression Results
Obs date hist
busa busb projn
1 01JAN01 350000
3500 500 335066.38 2
01FEB01 340000
3600 600 340975.94 3
01MAR01 410000
3700 640 346885.51 4
01APR01 320000
3900 800 358704.64 37
01JAN04 .
7900 1000 595087.21 38
01FEB04 .
7800 1020 589177.65 39
01MAR04 .
7900 950 595087.21 40
01APR04 .
8000 990 600996.78 41
01MAY04 .
8200 980 612815.91
40Business Driver Scenario Analysis
- Example Business initiative with incremental
daily projections - Approach
- Create new monthly totals for affected business
driver(s). - Run Business Driver based projections with new
business driver forecast.
41Business Driver Scenario Example
Sum daily projections for each month
New Business Driver projections (add Scenario to
baseline, create new graphical projections)
Create document with text and graphical analysis
42Factoring Functionality Growth
- Single business driver
- Compare business driver growth with resource
metric growth - Calculation
- (Business Driver input) / (Business Driver
Trended) - (Resource Trended)
43Functionality Trending Using Growth Comparisons
D2/E2C2
BusA/(Trended BusA) (Trended Hist)
44Functionality Regression Using Date and Business
Drivers
TREND(C2C32, A2B32,A2B2)
45Comparison of Results
46Using Growth Rates
IF(C3gt0,D3C3(1/12),D3B3(1/12))
47Growth Rate Calculations (Bulk Capacity Planning)
- (Years) ( Log(Threshold) - Log(Base) ) /
Log(1AnnualGrowth) - (Years) (UpgradeDate - BaseDate) / 365.25
- UpgradeDate 365.25 (Log(Threshold)-Log(Base))
/ Log(1AnnualGrowth) BaseDate - Examples
- Base Date September 2003, Base Utilization
60, Threshold 80, Annual Growth 20 - Base Date September 2003, Base Utilization
50, Threshold 80, Annual Growth 15 - Base Date September 2003, Base Utilization
40, Threshold 70, Annual Growth 25
48Bulk Capacity Planning Example
365.25 (LOG(D14)-LOG(C14)) / LOG(1E14) B14
365 (LOG(Upgrade)-LOG(Base CPU))/LOG(1CAGR)
Base Date
49Conditional Formatting
50Mainframe Workload Projections
Mainframe Average Dayshift Projected
MIPS by Month System Group
AUG04 SEP04 OCT04 NOV04 DEC04 JAN05 CAGR
Av/Pk _______ __________________________ ______
______ ______ ______ ______ ______ ______
______ Node 1 425.0
431.5 438.1 444.8 451.6 458.5 20.0 .7500
Application A 11.1 11.5
11.9 12.3 12.7 13.2 50.0 .4400
Application B 2.2 2.3 2.4
2.5 2.6 2.7 50.0 .8298
Application C 28.7 25.2 22.2
19.5 17.2 15.1 -78.2 .6461
Application D 14.1 14.3 14.6
14.8 15.1 15.3 22.1 .6258
Application E 1.4 1.0 0.7
0.5 0.4 0.3 -98.1 .6518
Application F 6.8 5.9 5.1
4.4 3.8 3.3 -81.7 .8257
Application G 15.0 15.5 16.0
16.6 17.1 17.7 50.0 .5037 Small
163.6 163.5 163.3 163.2
163.1 162.9 -1.0 .7539
51Automated Workload Trending
Exponential Trending
Workload Data
Trended Resource by Month
Override Resource and Growth Rates
52Other Options
- Lag variables as input to regression
- Adjustment factors for upgrades
- Other regression techniques
- Automatic batch runs
- Start Date configuration
- Actual vs. Projected analysis
- Timewise regression
53Automated SAS Regression with Open Number of
Business Drivers
proc reg noprint dataforecast outestregdata
tableout by machine shift model cpureg
CtReg / selectionrsquare noint
54Actual vs. Projected
55Individual Actual vs. Projected Analysis
Count 232
Avg Error 0.25439
Application
Server Bus1 Bus2 Bus3 Bus4 Bus5 Bus7
ServA 0.233
ServB 0.585
ServC 0.454
ServD 0.343 0.343
ServD 0.596
ServE 0.108
56Actual vs. Projected Graphical Analysis
57Aggregate Actual vs. Projected (Relative)
58Aggregate (Average Actual vs. Average Projected)
59Aggregate Actual vs. Projected (Absolute)
60Timewise Regression (Proc Forecast)
61Summary
- There are many types of regression trending
approaches. - The most useful output is often compound annual
growth. - Mileage will vary Each situation should be
addressed individually.
62Thanks!
- Linwood Merritt
- Bank of America
- lin.merritt_at_bankofamerica.com