Download Understanding Spreadsheets for Economic Analysis in Veterinary Science and more Slides Veterinary in PDF only on Docsity!
Building aspreadsheet model
R.S. Morris^1 ), C.W. Rougoor^2 ) &R.B.M. Huirne^2 ) 1)Department of Veterinary ClinicalSciences, Massey University, PalmerstonNorth, New Zealand 2)Departmentof Farm Management, WageningenAgricultural University, Wageningen, the Netherlands
Objectives Bytheend ofthischapter thereader should beable:
- tounderstand thebasic principles of spreadsheets
- todesign simplespreadsheet models
- toincluderiskfeatures into spreadsheet models
18.1 Introduction When economic analysis first gained acceptance as adecision-making aid in veterinary science,everyanalysishadtobeconductedcompletelybyhand-frequently involvinghours of calculations and double-checking. Since then electronic spreadsheets have made the task relatively simple even the first time, and even easier to doif the same analysis must bere-runwithnewdata. Because repeated financial calculations are very commonly used in business, the spreadsheet caught on like wildfire over adecade ago,and became amajor factor in the growth of personal computers. Spreadsheets can bejust asuseful in veterinary work asin other fields, and this chapter explains the application of thetechnique at various levelsof sophistication. They offer away for thenovice tofirst develop asimplepartial budget,but they offer theexpert apowerful shortcuttoconducting complex modelling and analyses.
18.2 Structure of spreadsheets
18.2.1 Cells Anelectronicspreadsheet consistsofatableofindividual 'cells',mcolumnswidebynrows deep. Columns areusually designated by letters and rowsby numbers. Acell is therefore uniquely designatedbyitscolumnletterandrownumber,suchascellB12,whichisthecell attheintersection ofthesecondcolumn and 12throw.Thewidthofcolumnsmaybevaried to suit theparticularneeds. Cells can be allocated toparticular uses.Common uses include text fields for labels of various kinds, data fields which expect the user to enter values when the spreadsheet is
Chapter 18
ran, andcalculationfields,whichcontaintheresultsofcalculationsbasedonthedatawhich areentered.Theuniquefeature of spreadsheets which makesthemsovaluable istheability toattach acalculation formula toacell, sothat every time anew value isentered in adata entry field which affects acalculated field, the calculation is carried out again either on request or(usually) automatically, sothat newvalues appearinallrelevant cellsassoon as anumber anywhere inthe spreadsheet ischanged. Theformula for acellcanbeviewedandeditedatwill,makingiteasytocheckitsaccuracy. Once the formula is checked and permanently stored as part of a particular named spreadsheet, itcan berelied ontorepeatthecalculation accurately asoften as wished. This feature differs from adatabase management program, which allows manipulation of data entered, but usually requires aformula tobe applied to agroup of cells by specific decision of the user, and does not allow automatic formulae tobe attached to single cells withcross-references toothercells.
18.2.2 Advanced calculation procedures The formulae incellscan include all of the standard arithmetic operations,but in addition avarietyofmoreadvancedoperationswhichallowthespreadsheettoactasafull economic analysis system. For example, the discounting procedure for cost-benefit analysis is available asastandard operation whichcanbecalledbyitsnameandapplied toaseriesof annualcost orbenefitfigurestomake cost-benefit analysiseasy. Sequences ofcalculationswhichmustbecarriedoutrepeatedly for aparticular application can be automated by means of arecorded set of steps called a macro. This can be run to conduct morecomplex analyses than canbe achieved by simply editing data fields in the spreadsheet. Modern macro languages allow almost full programmability of the spreadsheet,permittingittodothingsnotenvisagedbytheoriginaldesigneroftheprogram.
18.2.3 Linking to other functions Almost all spreadsheets now have databases linked to them, and powerful graphics components, which allow themtotakedatain aspreadsheet andturn itimmediately intoa graph of the user's choosing, with many additional features available tocreate impressive graphical displays of results of spreadsheet analyses.Because the graphing isintegral to the spreadsheet, graphs can be easily updated when newdata are entered. Some also have presentation creation modules, which allow computer-operated slide shows tobe created directly from the spreadsheet information and directly entered text, to give public presentations offindings. Because spreadsheets are a major component of total software sales, many smaller companies have developed creative add-on modules for other companies' spreadsheets, to carry out functions not available within the main spreadsheet. Some of these enhance spreadsheet function, suchastheprogram @RISK,whichextendstheanalytical capacityof the spreadsheet in ways to be described below (see section 18.5).Others add totally new functions, evenasextremeasmakingthespreadsheet doubleasawordprocessingprogram, something for which it was never designed. Soif you have aspecial need and the feature
Chapter 18
wanttomaintain aspreadsheet filecontainingsummariesofthedataaccurateuptothelatest records entered.
18.3 Choice of program Fashions change in spreadsheets, and the program Visicalc which started the whole trend hasdisappeared from thescene.Formuchofthe 1980sthedominantprogram inthemarket was Lotus 1-2-3, because it added new features and simplified the way of working with the spreadsheet,butithaslost itsdominance.Ofthelargenumberof spreadsheet programs developed since Visicalc, three Window programs now dominate the market: Excel (Microsoft), Lotus 1-2-3 (Lotus/IBM) and QuatroPro (Novell).Most active spreadsheet users work in Windows because of the ability to handle larger spreadsheets, to display superior graphics,print toanyprinter andtouseOLE.The spreadsheet templates supplied with thisbook will run in all of these.Eachprogram has itsown file format, but each can read someof thecompeting formats aswell.
18.4 Formulating asimple economic analysis
18.4.1 Partial budgeting asan example The most common form of economic analysis used at farm level will be apartial budget. Using asanexampleabudgeted analysisofparasite control insheepwhichwas originally prepared by hand (Anderson etal., 1976),thenetbenefit of thecontrol program using one treatment strategy canbe laid out asshown inFigure 18.1.The analysis for this paper had taken afull weekofwork,butcouldnowbedoneinaspreadsheet inafraction ofthetime, and withgreater accuracy. In the form of apartial budget theanalysis fits easily into aspreadsheet format. Column A willbeused forrowdescriptions,andthefirst rowortwoineach oftheothercolumns will be used for column headings. In this simple form column Bwill be used for the data and calculations. Cells canbe designated to receive the raw data for the analysis,with blank cells to separate each group of related items from the others.It isimportant tomake the layout easy toread and interpret. There areplenty of rows and columns touse,as long as youkeepeverythingyouneedtoworkonatonetimeonasingle screen. An important basic rule is to have each data item entered in exactly the form it was collected, and have theprogram do any pre-processing to get the figures into the right form for analysis.Forexample,putinwoolweight andpriceperkg,and havetheprogram calculate fleece value.Similarly, if you must adjust the figures totake account of deaths during theyear, design the spreadsheet to acceptfiguresfor the number of deaths,and use this to adjust other parts of the analysis. This saves considerable time, frustration and mistakes-especially whenyou aretrying todoananalysis infront of a farmer. Cells aredesignated toreceive calculated values derived from thevarious raw data items, and formulae are entered into these cells to produce the result automatically. Variables used inacalculation areidentified bythe cell inwhich they canbe found, while constants are entered asnumbers.Mathematical operations are designated in theusual way seen on
Building aspreadsheet model
the right-hand side of amathematical equation, using the representation of each operator required bytheparticular spreadsheet.
Figure 18.1 Simple spreadsheet: Benefit of 'critical' parasite control strategy over 'no treatment'
ColumnA
1.Additional returns
Additionalfleecewool
Capital valueof surviving sheep incritical treatment group
Increased value of crutchings
Total
2. Returns foregone
Capital value of surviving sheepinnotreatment group
Wool salvaged from dead sheep
Total
3. Extra costs
Extra anthelmintic and labour
4.Reduced costs
None relevant
Net return
Return oninvested funds
ColumnB
Calculation formula
B4+B7+B
B16+B
(B11+B28)-(B20+B24)
B30x 100/B
Buildingaspreadsheet model
with different setsof assumptions.Theleading spreadsheets offer systems for combininga group of spreadsheets aspages in an electronic notebook, sothat they can be worked on together veryeasily.
18.5 Spreadsheetmodelswith riskconsiderations Computer models have been used extensively toanalyse disease control problems. Often these models are written in acomputer language such asTurbo Pascal, and knowledge of such alanguage is aprerequisite for writing theprogram. It ispossible now to construct moderately complex models purely within electronic spreadsheets, thus allowing people with very limited programming ability toproduce models which are dynamic, ie, they represent changes in a system through time, in an iterative fashion. Some spreadsheet programs have special enhancements to assist in such advanced uses. Spreadsheet modellingisanexcellent startingpointindevelopingamodel,sinceafunctional versioncan be developed quickly, although if the model is to be used extensively it may be best transferred toaprogramming languageoncethefull designhasbeenworkedout,mainly for gainsin speed andease ofuse. The complicating factor in most mathematical models arises from chance or stochastic elements. The @RISK computer package is available asan add-in for Lotus 1-2-3,Excel and Symphony and brings advanced modelling and risk analysis to these worksheets (Palisade, 1992). In general, the technique in an @RISK analysis encompasses the following four steps,of whichthe first threecanbe supported. 1.Developing amodel-bydefining aproblemorsituation intheformat ofthe spreadsheet package you areusing. 2.Identifying risk - setting upthe risky variables inthe worksheet sothat their possible values canbe specified withprobability distributions, and identifying the risky outcome variables intheworksheet results tobe analysed.
- Analysingthemodelwithsimulationusingrandomnumbers-todeterminetherangeand probabilities of allpossible outcomesfor theresultsof the worksheet. 4.Making adecision -based onthe resultsprovided andpersonal preferences.
Probability distribution plays animportant role in any analysis which incorporates risk. Aprobability distribution is amathematical device for presenting the quantified risk for a variable.Therearemany forms andtypesofprobability distributions available in ©RISK, eachof whichdescribesarangeofpossiblevaluesandtheirlikelihoodofoccurrence.There is awide variety of distribution types ranging from uniform and triangular distributions to morecomplex forms such asgammaandWeibull (Figure 18.2). In @RISK, all distribution types use asetof arguments tospecify arange of actual values and distribution ofprobabilities,ascanbeseen inFigure 18.2.Thenormal distribution, for example, uses amean and standard deviation asitsarguments.Themean defines thevalue around which thebell curve willbe centred and the standard deviation defines the spread of values around the mean. Over thirty types of distributions are available in @RISK for describing distributions for uncertain values intheworksheets.
Chapter 18
0.8-f @NORMAL(0,1) 0.6-
0.
0.
0.
@TRIANG(2,3,7)
@UNIFORM(0.2,0.6)
@DISCRETEf11,1,12,2,14,5,15,1.5,16,0.5,5) 0.5 -t-
0.3 -
0.1 -
_L
0 1 2 3 4 5 6 7 11 12 13 14 15 16 17 If
l.o-
@GAMMA(1,1)
\ '" @GAMMA(2,1)
\ / @GAMMA(3,1)
A/~^\ .-'
@WEIBULL(1,1)
_\ r _ @WEIBULL(2,1)
*-ƒ \ *
Figure 18.2Example probability distribution graphs (normal, uniform, triangular, discrete, gamma and Weibull)
In @RISK, uncertain variables and cell values are entered as probability distribution ©functions, for example: @TRIANG(A3/2.01,A4,A5), when you are using Lotus 1-2-3. This example is atriangular function with the minimum (actual value incell A3 divided by 2.01),mostlikely (actual valueincellA4)andmaximum (actualvalueincellA5) value as arguments respectively. These ©functions can be placed in the worksheet cells and
Chapter
Within blocks dedicated todata entry or calculation, subblocks should be laid out which contain closely related material which can be handled as aunit. For example, it may be necessary to add up rows 12to 30in column G. Instead of having torefer toeach cell in the sequence by name ineach formula aswould be necessary if they were spread around, theycanbehandled asaninclusiveblock SUM(G12..G30)or similar. Intermediate calculations should be stored in columns separated from the user-accessible ones sufficiently, sothattheuserisnever awareoftheirexistenceinnormal circumstances. This allows for ease of use but also allows the expert to check calculations easily. Annotation features in aspreadsheet or an add-in program allow explanatory notes to be attachedtocellsofaspreadsheet,mainlyasanaidememoirtothelogiconwhichacomplex cell formula isbased. This feature isextremely useful, since otherwise aformula that was quite clear six months agobecomes incomprehensible when reviewed, and may then be modified in away whichundermines the structure ofthe spreadsheet. If any procedure must be carried out repeatedly in working with aspreadsheet, it is most efficient toprogramitinadvancebywritingamacro,asmallprogramwhichmostcomputer users can prepare. This carries out a specific set of steps within the spreadsheet, which mayvaryfrom something assimpleaschanging afont inasinglestep,torunningacomplex analysisby hitting onekey. The spreadsheets supplied withthisbook all work inaccordance withthese principles as much as possible, and use techniques such as macros. Not all features of each single spreadsheet program could be fully used, however, because the cases were designed such that they run inall 3programs involved (ie,Excel,Quattro andLotus).
18.7 Usingthespreadsheet Normally awell-designed template will take the user in asequence of automated jumps throughallthedataentrystages,andafterthelastitemhasbeenentereditwillautomatically carry outitsrecalculation andpositionthecursoratthefirstresultscreen.Recalculationcan however either be made automatic or be made to await a user instruction. One small problem with automated spreadsheets isthat anerrorwhichisrecognized after adataentry point ispast cannot becorrected without repeating therun. For a very polished spreadsheet, this can be overcome by copying each data item to a check screenjustbefore calculationcommences,wheretheuserisgiven achancetoreview the items entered and to loop back to correct any specific errors. After confirming the accuracy of thevalues,calculation will commence. Onemajoruseofaspreadsheetistocomparetheeffect ofsomepotentialimprovementwith a 'base' analysis representing the status quo. In simple analyses this must be done by printing theresult screen for thebase analysisandthen running thealternative and printing thattoallow acomparison ofthetwo.Anadvanced alternativeistoallowtheresult screens to show two sets offigures,onefor thebase analysis andone for the alternative.The base analysis caneitherbe repeated each time acomparison isdone,orbe processed separately and merely stored visually in the result screens toremind the user of the baseline values against whichthe alternative shouldbecompared.Printedcopieswillcontain bothvalues.
Building aspreadsheet model
An important part of an economic appraisal is sensitivity analysis, where the most influential biological and price variables in the analysis are each varied to test how susceptible the predicted financial return is todifferences between estimated and actual itemsintheanalysis,andvariationwhichcanbeexpected overtimeinmajor variablessuch as product price.The limits within which sensitivity analysis isconducted are amatter of choice to fairly represent the field situation. With regard to prices, thelong-run range of lowest to highest may be taken (perhaps adjusted to current year equivalence), or else fixed percentage variation maybetaken (say 10and20%above andbelowcurrentvalues). With regard tobiologicalvariation inmeasured variables such asgrowth rateorpregnancy rate, sensitivity analysis may either be taken at 1and 2 standard deviations from the measured sample mean, oragain be allowed tovary by apercentage of the mean value.It is good practice toconsider the mean orexpected value and two levels of variation above andbelow themean,thusrequiring fivecalculations intotal. Spreadsheets really come into their own in sensitivity analysis, because what would otherwise havebeen atediousprocessofrepeatingtheentirecalculation becomes asimple matter of changing oneormorevariables andviewing theresult. Iteven becomes possible to adjust twovariables at once (sayproduct price and growth rate response) toproduce a bivariate response surface, something which is far too cumbersome by hand because 25 evaluations are required for asingle sensitivity analysis. If desired, the 25 results can be stored and then graphed ina3-dimensionalrepresentation of aresponse surface within the same program.
18.8 Examples of the useof spreadsheets in practice There are numerous spreadsheet templates of veterinary economic calculations available, eitherthroughdistribution from thedeveloperorthroughpublication oftheprocedure ina scientificjournal.Examplesatthebasiclevelinclude Quekera/. (1986), andGulbenkian & Viegas(1988).Dijkhuizen etal. (1986)provideamuchmoreextensiveanalysissystemusing a spreadsheet to analyse sow replacement economics, embodying most of the principles outlined. Carpenter (1988a,b) demonstrates the use of a spreadsheet in epidemiological modelling.Numerousotherveterinary examplesof spreadsheets existinboth epidemiology andeconomics. Inpractice some very complicated simulation models with many interacting demands and serviceshavebeenused.Wholesectionsofanorganizationhavebeensimulated.Thelimitsare onlythecapacityofthecomputerandthetimetakentoworkandtesttheprogram.Muchofthe workhasbeenfacilitatedbydevisingspecialcomputersimulationlanguagessuchas @RISK. Quantitative analysistechniqueshavegained agreat dealofpopularity withdecision makers andanalystsinrecentyears.Unfortunately, manypeoplehavemistakenlyassumed thatthese techniques aremagicblackboxesthatunequivocally arriveatthecorrect answerordecision. Notechnique,includingthoseusedby @RISK,canmakethatclaim.Thesetechniques are toolsthat canbeusedtohelpmakedecisionsandarrive atsolutions.Like any tool,theycan beusedtogoodadvantagebyskilledpractitioners,but shouldneverbeusedasareplacement forpersonaljudgment.
