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