PROC COMPUTAB: Cash Flows

Example 9.7 Cash Flows

The COMPUTAB procedure can be used to model cash flows from one time period to the next. The RETAIN statement is useful for enabling a row or column to contribute one of its values to its successor. Financial functions such as IRR (internal rate of return) and NPV (net present value) can be used on PROC COMPUTAB table values to provide a more comprehensive report. The following statements produce Output 9.7.1:

data cashflow;
   input date date9. netinc depr borrow invest tax div adv ;
datalines;
30MAR1982 65 42 32 126 43 51 41
30JUN1982 68 47 32 144 45 54 46
30SEP1982 70 49 30 148 46 55 47
30DEC1982 73 49 30 148 48 55 47
;

title1 'Blue Sky Endeavors';
title2 'Financial Summary';
title4 '(Dollar Figures in Thousands)';

proc computab data=cashflow;

   cols qtr1 qtr2 qtr3 qtr4 / 'Quarter' f=7.1;
   col  qtr1 / 'One';
   col  qtr2 / 'Two';
   col  qtr3 / 'Three';
   col  qtr4 / 'Four';
   row  begcash / 'Beginning Cash';
   row  netinc  / 'Income' '   Net income';
   row  depr    / 'Depreciation';
   row  borrow;
   row  subtot1 / 'Subtotal';
   row  invest  / 'Expenditures' '   Investment';
   row  tax     / 'Taxes';
   row  div     / 'Dividend';
   row  adv     / 'Advertising';
   row  subtot2 / 'Subtotal';
   row  cashflow/  skip;
   row  irret   / 'Internal Rate' 'of Return' zero=' ';
   rows depr borrow subtot1 tax div adv subtot2 / +3;

   retain cashin -5;
   _col_ = qtr( date );

   rowblock:
      subtot1 = netinc + depr + borrow;
      subtot2 = tax + div + adv;
      begcash = cashin;
      cashflow = begcash + subtot1 - subtot2;
      irret = cashflow;
      cashin = cashflow;

   colblock:
      if begcash then cashin = qtr1;
      if irret then do;
         temp = irr( 4, cashin, qtr1, qtr2, qtr3, qtr4 );
         qtr1 = temp;
         qtr2 = 0; qtr3 = 0; qtr4 = 0;
         end;
run;

Output 9.7.1 Report That Uses a RETAIN Statement and the IRR Financial Function

Blue Sky Endeavors

Financial Summary

(Dollar Figures in Thousands)

                                     Quarter    Quarter    Quarter    Quarter                   
                                         One        Two      Three       Four                   
                  Beginning Cash        -5.0       -1.0        1.0        2.0                   
                  Income                                                                        
                     Net income         65.0       68.0       70.0       73.0                   
                     Depreciation       42.0       47.0       49.0       49.0                   
                     BORROW             32.0       32.0       30.0       30.0                   
                     Subtotal          139.0      147.0      149.0      152.0                   
                  Expenditures                                                                  
                     Investment        126.0      144.0      148.0      148.0                   
                     Taxes              43.0       45.0       46.0       48.0                   
                     Dividend           51.0       54.0       55.0       55.0                   
                     Advertising        41.0       46.0       47.0       47.0                   
                     Subtotal          135.0      145.0      148.0      150.0                   
                  CASHFLOW              -1.0        1.0        2.0        4.0                   
                                                                                                
                  Internal Rate                                                                 
                  of Return             20.9

The COMPUTAB Procedure

Example 9.7 Cash Flows