Chapter Contents
Previous
Next
|
Chapter Contents |
Previous |
Next |
| The COMPUTAB Procedure |
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
1981Q1 4962.0 1981Q2 5804.6 1981Q3 5498.6 1981Q4 7687.1
1982Q1 6864.1 1982Q2 7625.8 1982Q3 7919.7 1982Q4 8294.7
1983Q1 8151.6 1983Q2 10992.7 1983Q3 10671.4 1983Q4 10643.2
1984Q1 10215.1 1984Q2 10795.5 1984Q3 14144.4 1984Q4 11623.1
1985Q1 14445.3 1985Q2 13925.2 1985Q3 16729.3 1985Q4 16125.3
1986Q1 15232.6 1986Q2 16272.2 1986Q3 16816.7 1986Q4 17040.0
1987Q1 17967.8 1987Q2 14727.2 1987Q3 18797.3 1987Q4 18258.0
1988Q1 20041.5 1988Q2 20181.0 1988Q3 20061.7 1988Q4 21670.1
1989Q1 21844.3 1989Q2 23524.1 1989Q3 22000.6 1989Q4 24166.7
;
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';
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 Using Macro Invocations
title3 'Revised';
title4 'Selected Values Shown';
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 Using Macro Invocations for Selected Values
|
|
Chapter Contents |
Previous |
Next |
 Top |
Copyright © 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.