The COMPUTAB Procedure
Example 9.6 A What-If Market Analysis
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; ... 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
|