Using SAS to Perform Discounted Cash Flow Analysis. Alan Knight - The Royal Bank of Scotland

Introduction Discounted Cash Flow (DCF) analysis is a technl.que for evaluating the financial implications of any plan which involves expenditure. The DCF technique is most useful for comparing alternative plans on a common basis, especially when the plans vary in terms of acquisition policy. For example, the plan may be to install a new piece of computer equipment and it is necessary to determine if this equipment should be purchased, rented or leased. A simple analysis of this problem would involve summing the rental payments until they equal or exceed the purchase price. At this point there is an apparent break even and, if the computer is to be kept after this point, then purchase would be preferable. However, this approach ignores a large number of factors which may influence the decision. Not the least of these factors being the financial profile of the Plan, that is, the purchase option may involve expenditure which is in excess of the budget. In such a case, no matter how quickly purchase breaks even over rental, it is not an option which can be considered. The DCF technique "itself is basically a simple one but is made difficult by the complexity of most plans which are important enough to be subjected to such a rigorous analysis. For example, in the case of the computer acquisition mentioned above, there may be many associated pieces of equipment, various rental schemes and software requirements. In addition, there are tax considerations, inflation, maintenance charges,- interest rates and so on to be taken into account.' In particular, spending money implies a consequent loss of revenue from that money, a factor which is far more significant for a single large expenditure than for smaller monthly rental payments. These and other considerations made it worthwhile to write a DCF' analysis program which would produce tabular and graphical output, driven by input from a series of menu screens. The system was particularly designed for evaluation of hardware and software plans and therefore contains some features which are not generally applicable. However, there are facilities for performing a DCF analysis to meet most requirements. Facilities Available The program considers each option to be evaluated as a "cycle", each such cycle being described by a "RUNPARMS" statement and one or more detail statements. The detail statements may be "PURCHASE", "RENTAL" or "SOFTWARE". Each statement describes the information for the evaluation to be performed on a particular item. Additionally, the first "RUNPARMS" statement specifies common parameters for the whole analysis. For example the start date for the analysis and for inflation rate calculations. \

•

" 90

The parameters available on each statement type are described below, in order to give some idea of the facilities available from the program. RUNPARMS Statement ORGDATE=MMMYY

eg OCT82

This parameter specifies the start date for the anlysis and is assumed to be the start of a financial year. The end date of the analysis is assumed to be five years after the start, although certain calculations will consider a number of years after the end date, due to factors such as the delay in receiving tax allowances. INFLDATE=MMMYY

eg APR81

The date at which inflation is assumed to start, if specified this must precede ORGDATE CORPTAX=NN

eg 52

Percentage tax rate for corporation tax CAPTALLC = NN

eg 52,

Percentage capital allowance rate TAXDELAY=nn

eg

2

DELAY, in years, before tax allowances are received. DISRATE=NN

eg 14

Discount rate i.e. the rate at which money is discounted to its net present value. Effectively, the interest not earned because of spending money on equipment. RUNID=£12.

eg PURCHASE OPT

»~

t

Description of this cycle TEXT1=£40.

eg COMPARISON OF PURCHASE AND RENT

TEXT2=£40.

eg FOR IBM 3081K24 CPUs

Description of the analysis as a whole - these parameters are valid on only the first RUNPARMS statement. PURCHASE STATEMENT This statement is used to describe items which are to be purchased, the available parameters are:

91

DESC=E20.

eg IBM 3083K24

A narrative description of the item ~Y=nnnn

eg 2

The number of items of this type to be purchased. COST=nnnnnnnn

eg 1725424

The unit cost of the item, in unspecified units of currency. Note that the total cost of this purchase is calculated by the program by multiplying QTY by COST. START=MMMYY

eg OCT84

The installation date for the equipment END=MMMYY The expected removal date of the equipment MAINT=nnnnn

eg 1095

The monthly maintenance charge associated with the purchased item RESALE=nnnnnn

eg 20000

The estimated resale value of the equipment at the END date. WARRANTY=nn

eg 6

The manufacturer's warranty period, in month, after which maintenance charges will commence. FREQ

= nn

eg 4

The number of maintenance payments per annum, monthly payments would be represented by specifying FREQ=12 INFL=NN

eg 10

The annual inflation rate to be applied to the maintenance charge. Negative values are allowed, to indicate that maintenance rates are expected to tall. RENTAL Statement The DESC, QTY, START, END and INFL are exactly as for PURCHASE. The COST parameter gives the unit rental cost for each instalment. FREQ specifies the number of instalments per year. No other parameters are valid:

92

SOFTWARE Statement The parameters available are exactly the same as for RENTAL. Input and Output The program was originally written to accept these input parameters as punched cards or card images, in batch mode. An SPF/TSO front end has subsequently been produced so that input is gathered from structured menu screens, making it easier for the casual or non-specialist user of the system. Output may be directed to hardcopy devices or back to the originating TSO screen. Output from the program consists of two graphical and six tabular reports, examples of which are attached. The two graphical reports show, respectively, comparative anual gross expenditures for the alternative plans (cycles) and the associated cumulative DCF profiles. It is usually a simple matter to determine which of the rival options most suits the objectives set merely by looking at these two graphs. The non-graphical reports provide the actual financial analyses and input data on which the graphs are based. These reports are:

*

Run summary and error log

* *

Comparative gross Expenditure Report Comparative Discounted Cash Flow Report

*

DCF Analyses - Results

*

Detailed Expenditure Report

*

Input Data Listing

,"-..' /'

Use of SAS SAS was found to have many useful features which considerably eased the way to producing the DCF Program. The ETS component of SAS provided the Net Present Value (NPV) function for performing the actual discounting of cash flows. Although useful, this function is not essential and could have been replaced by a few lines of SAS code. However, many other features of SAS which, if not essential, were extremely useful, included:

*

Array handling

*

Graphical output

*

Data handling - especially data processing and time period calculations.

/

93

i ~

'.'

.'

The code for the DCF Program is included in the SUCCESS library and may therefore be examined, modified and used by anyone who would find it of assistance. Conclusions The DCF analysis system now in use at The Royal Bank of Scotland DP Division has provided an easy to use and objective procedure for evaluating the financial implications of alternative expenditure plans. These plans may cover computer hardware and software or, with a little ingenuity, may relate to any form of expenditure. The structure of the system has made it simple and quick to answer "what if" type questions, since iterations through the evaluation process require only the specification of the input parameters. Although the system is specifically intended for comparing rival plans, an absolute analysis of any single plan is also possible. In this case some care must be taken in selecting such input parameters as the inflation and discount rates, the applicability of tax allowances and so on. However, the system has proved extremely useful and is certainly an enormous improvement on the facilities previously available, namely a pencil and paper plus guesswork.

A J KNIGHT Technical Adviser Data Processing Division The Royal Bank of Scotland plc

94

i

1~T&G lrORKLOAD COMPARED TO CURRENT PLAN

CP1J PLANS INCL1JDING

I

I !I

I I

I I I

I

I i··

I F~

ID !~

I .:":.

! I::· i

!...

lX

,. }:~.

,~..'

;: ~

!(It.

I F' IF

i fj

II D ...

iT.I.

! U I I=(

I I:· • •.••

!'f,.'

1 1",

I)

i (:)

1;

i U

~:

~

fJi

I

i··

II:SfJ

~ lI f fi l F ! f i Ii' '

1

95

(-;P1J PLA'Nrs IN;-CLTIDINC .

_,

,._.

._.

1~7l L" i~lnJ 1,

'_'

"'OMDHREf' III _J TiJ' \ rflRRwN1' ..

C~,

4,200,

.i..

f

"_0'

"

!.LIJ.'

lVOR·K"~LO~D ..._ \_ ._ ~-l

prl.dB.l' nr

.i.

000~

4, 00(-) ,0(-)0 .

::; .,600., 000 .

e

u

M U

L.

i~l

T r ij F

::;,200} 0(-)0

::; , 000,

(-)(-),~)

iD

Ie IF

F'

~.~

Cl

, (3\:)0 , O(-)(~

iW

II'

2, {)oo ,000 -

':)

.......

') .....

,I

i,l (.)

?

') .....

(~

(.j

t.) {.) (.',

.• , '

(.) ~

,

"

(.) f.) .'.',

J \ ... '\,f

J3 j

1

2,000,000 I 'j

I

.,BOO,.Of-)O ' -- T·-·--·--·-,·-··-·----·---r····------·-·····-···-··--r·.--.-.--...-.....-. -_·····'l·--···-_····--···-r-·····_··-·····-·_····-r·

SEF'83

SEF'84

II

f

LEGEND:

F:l.JNDESC

SEP8S

SFP86

FIN{:lNCI(i!... YE::/~;F' Fi'--.!D ····Cl !F'!:;:!::'NT .... ,... J .... :3 () i:l'-j' Rs·. E i~l F~ L. \'

-B-tl--G. .j

...;----ti-+

SEPB?

Il_. _____._~_.._.~_____~._.~_..__,.____...~_.......~:.·._~:~.~~ r f p.,{ITr ;\ rT'FF nrF u r l'd' . ~. . _~_~:... i~~~~~~.~._~ \

\,

96

SEP88 SEP89

:? . h' [!:::F' ~_ ~.

?;(;:. ·~:. ·::J--.i

~ _.~ ___..____.

___.___ ,_._." .._....._ .. __ .. .• ___ .__ ._........____ .• ..

~1~LL~N1EL

"liN lE5CRIPTIUN -

CA~h

FL~w

~NALYSJ~

-

R~~Q~T

TIMl

A~U

uATE -

CPU i>LAI';S INCLUi.JlllOG W6lO wURKlUAlJ CUMPAh.i::L; Tu CUh.kl.NT PLAN

START DATl (LklOuAlc) - 0('T82 -SI:.P87

END l'ATE

r:1+i::CTlV!: PARAMUEk!> Fur.. CUf,.f'GRATIU~

CAtlTAL

TAX

(LGkPT~X)

ALLO~IAtKE

-

ICAf'TALL-C)

fAX GELAY

~cRCENT

52

PH.CENT

2

L.11S(.uUNT KATE:

t.OTt

~Z

-

14

EkRO~

LU~

1-CLIf,.;;,UH

YEAR~

PERCc-NT

INFLATlON FUf\. kLNTAL, SOfTWARE AND MAINHNANCE lNFLATION IS N~T APPLIeD TO puRCHAS~ COSTS -

••• PKUCCSS PhASE

CYCU,

•••

97

ITEMSSTARl.~

A1 - 4CTSLc _

14 32

UN

01MAk&~

~t~,\!'1''''\'';o.--..~~~~~~~;:~'''''''''~~''''''-'''~!;''!'''-''''''''"';"''3:''"-..,,,(.~~~-'':-~

TYPl

1 b

"oe. 7

9t 4.;1 2L0/t

'1643

LoL 10

C 9t.421 2t.1.. -f c·

L

9tA3 "01,; 'fo

LJ 9c't3 2tl. 1L

(J

'1t."

'::6C It,

1d~3~~9

..'

2607

T01 .. L

2~L"1.

1143" 23115

2229u

lYf'[

,..,ukT hI

~luNTHL

2 3

f'URCH,Sl MAINlENANCE: kENIAL

1713tbt 14241

-350[ 1(,515

4

SOF1~AkE

14~0('

12576

:)

10TAL

I~G3Ul~

1 'is Sol

o

o

~'u~,lrl3

MUt,TH't

'1UN1H~

TOTAL

1 2 3

"5

lYl-'[ PLlRe.HA~E

MAINTENAI\CE KErn AL SOFhlARE lOlAL

o

26429

0,

o

o

FiNA~LIAL Y~AR END"5I:Pb> MONh.0

MLNlh7

HUt,lMb

K(Ji~HI"

TOTAL

o

r

c.

C

c

o

1(;515

o

Ill!:>l!>

lC51~

10:>15

10515

10~15

1051~

1770368 129912'

lJ

o

1C~1~

1257t 2.3C91

1325t 23771

1325t,

1::;;: ~ t 23711

132:>6 23171

13256

13256

13256

159351

23 Til

23771

13256 23771

23771

2059631

o

n-

23771

12'180

o

MoN1t::tS

o - -_-

12980

o

o

2el71 '1706uES

20171

2071

20171

'33151

33151

:--.33151_

, 0 ,

MLJN1H3

I-IONTh;?

n lc.C12 u

r

o

16012

lcCl" CI

221H 3t201

2?1h 362(1

'221H

38201

c

'0

20171

1.;;",

~

G t,

23771

fINANCIAL YEAR ~ONTj;6

O~~_:--o-

12980

1298C _

Co

o

- _ _0 12980 0

20171

33151 ·'3::;151

, 33151

f1NAt,Clt.L

'331~1

MONlhb

(J

0 16012 0

o

o

1601.~·_X!J::a 11'5~~~~!,!!j:%.J"""tl...~"t~"",:",:~~.!"J"~_·~~),""'~~!:T::!'~'),:!:~_~'~-~y.-::p.~~:~":"K!~~~r':-":'r'_~~.~·.~~.?!·t,:r"7n ··~."'··"~,·--;,",-"f:r"7•.,.-:,:p:.-·";.::o.~V":~'·". r,:-;""'~?;--::_-c"-'''~;'_';':~;'':''''-J',H'-'-'t."!'.~(YPR"·,:',:"'.~'r:··::l"'-,,\,,',"'!;?'.""~j""_7_~}""~.n