PROC COMPUTAB can be used with other SAS/ETS procedures and with macros to implement commonly needed decision support tools for financial and marketing analysis.
The following input data set reads quarterly sales figures:
data market; input date :yyq6. units @@; datalines; 1980Q1 3608.9 1980Q2 5638.4 1980Q3 6017.9 1980Q4 4929.6 ... more lines ...
The following statements illustrate how PROC FORECAST makes a total market forecast for the next four quarters:
/* forecast the total number of units to be */ /* sold in the next four quarters */ proc forecast out=outcome trend=2 interval=qtr lead=4; id date; var units; run;
The macros WHATIF and SHOW build a report table and provide the flexibility of examining alternate what-if situations. The row and column calculations of PROC COMPUTAB compute the income statement. With macros stored in a macro library, the only statements required with PROC COMPUTAB are macro invocations and TITLE statements.
/* set up rows and columns of report and initialize */ /* market share and program constants */ %macro whatif(mktshr=,price=,ucost=,taxrate=,numshar=,overhead=); columns mar / ' ' 'March'; columns jun / ' ' 'June'; columns sep / ' ' 'September'; columns dec / ' ' 'December'; columns total / 'Calculated' 'Total'; rows mktshr / 'Market Share' f=5.2; rows tunits / 'Market Forecast'; rows units / 'Items Sold'; rows sales / 'Sales'; rows cost / 'Cost of Goods'; rows ovhd / 'Overhead'; rows gprof / 'Gross Profit'; rows tax / 'Tax'; rows pat / 'Profit After Tax'; rows earn / 'Earnings per Share'; rows mktshr--earn / skip; rows sales--earn / f=dollar12.2; rows tunits units / f=comma12.2; /* initialize market share values */ init mktshr &mktshr; /* define constants */ retain price &price ucost &ucost taxrate &taxrate numshar &numshar; /* retain overhead and sales from previous quarter */ retain prevovhd &overhead prevsale; %mend whatif;
/* perform calculations and print the specified rows */ %macro show(rows); /* initialize list of row names */ %let row1 = mktshr; %let row2 = tunits; %let row3 = units; %let row4 = sales; %let row5 = cost; %let row6 = ovhd; %let row7 = gprof; %let row8 = tax; %let row9 = pat; %let row10 = earn; /* find parameter row names in list and eliminate */ /* them from the list of noprint rows */ %let n = 1; %let word = %scan(&rows,&n); %do %while(&word NE ); %let i = 1; %let row11 = &word; %do %while(&&row&i NE &word); %let i = %eval(&i+1); %end; %if &i<11 %then %let row&i = ; %let n = %eval(&n+1); %let word = %scan(&rows,&n); %end; rows &row1 &row2 &row3 &row4 &row5 &row6 &row7 &row8 &row9 &row10 dummy / noprint; /* select column using lead values from proc forecast */ mar = _lead_ = 1; jun = _lead_ = 2; sep = _lead_ = 3; dec = _lead_ = 4; rowreln:; /* inter-relationships */ share = round( mktshr, 0.01 ); tunits = units; units = share * tunits; sales = units * price; cost = units * ucost; /* calculate overhead */ if mar then prevsale = sales; if sales > prevsale then ovhd = prevovhd + .05 * ( sales - prevsale ); else ovhd = prevovhd; prevovhd = ovhd; prevsale = sales; gprof = sales - cost - ovhd; tax = gprof * taxrate; pat = gprof - tax; earn = pat / numshar; coltot:; if mktshr then total = ( mar + jun + sep + dec ) / 4; else total = mar + jun + sep + dec; %mend show; run;
The following PROC COMPUTAB statements use the PROC FORECAST output data set with invocations of the macros defined previously to perform a what-if analysis of the predicted income statement. The report is shown in Output 9.6.1.
title1 'Fleet Footwear, Inc.'; title2 'Marketing Analysis Income Statement'; title3 'Based on Forecasted Unit Sales'; title4 'All Values Shown'; options linesize=96; proc computab data=outcome cwidth=12; %whatif(mktshr=.02 .07 .15 .25,price=38.00, ucost=20.00,taxrate=.48,numshar=15000,overhead=5000); %show(mktshr tunits units sales cost ovhd gprof tax pat earn); run;
Output 9.6.1: PROC COMPUTAB Report That Uses Macro Invocations
Fleet Footwear, Inc. |
Marketing Analysis Income Statement |
Based on Forecasted Unit Sales |
All Values Shown |
Calculated March June September December Total Market Share 0.02 0.07 0.15 0.25 0.12 Market Forecast 23,663.94 24,169.61 24,675.27 25,180.93 97,689.75 Items Sold 473.28 1,691.87 3,701.29 6,295.23 12,161.67 Sales $17,984.60 $64,291.15 $140,649.03 $239,218.83 $462,143.61 Cost of Goods $9,465.58 $33,837.45 $74,025.80 $125,904.65 $243,233.48 Overhead $5,000.00 $7,315.33 $11,133.22 $16,061.71 $39,510.26 Gross Profit $3,519.02 $23,138.38 $55,490.00 $97,252.47 $179,399.87 Tax $1,689.13 $11,106.42 $26,635.20 $46,681.19 $86,111.94 Profit After Tax $1,829.89 $12,031.96 $28,854.80 $50,571.28 $93,287.93 Earnings per Share $0.12 $0.80 $1.92 $3.37 $6.22 |
The following statements produce a similar report for different values of market share and unit costs. The report in Output 9.6.2 displays the values for the market share, market forecast, sales, after-tax profit, and earnings per share.
title3 'Revised'; title4 'Selected Values Shown'; options linesize=96; proc computab data=outcome cwidth=12; %whatif(mktshr=.01 .06 .12 .20,price=38.00, ucost=23.00,taxrate=.48,numshar=15000,overhead=5000); %show(mktshr tunits sales pat earn); run;
Output 9.6.2: Report That Uses Macro Invocations for Selected Values
Fleet Footwear, Inc. |
Marketing Analysis Income Statement |
Revised |
Selected Values Shown |
Calculated March June September December Total Market Share 0.01 0.06 0.12 0.20 0.10 Market Forecast 23,663.94 24,169.61 24,675.27 25,180.93 97,689.75 Sales $8,992.30 $55,106.70 $112,519.22 $191,375.06 $367,993.28 Profit After Tax $-754.21 $7,512.40 $17,804.35 $31,940.30 $56,502.84 Earnings per Share $-0.05 $0.50 $1.19 $2.13 $3.77 |