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 |