A What-If Market Analysis

/*--------------------------------------------------------------

                    SAS Sample Library

        Name: ctaex06.sas
 Description: Example program from SAS/ETS User's Guide,
              The COMPUTAB Procedure
       Title: A What-If Market Analysis
     Product: SAS/ETS Software
        Keys: programmable tabular reports
        PROC: COMPUTAB
       Notes:

--------------------------------------------------------------*/


title 'A What-If Market Analysis in PROC COMPUTAB';

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
;

/* 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;

/* 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;

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;

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;