Process analytical technology : spectroscopic tools and implemented strategies for the chemical and pharmaceutical industries

311 Pages·2010·27.43 MB·English

by  Katherine A Bakeev

Practical Financial Modelling
(cid:84)(cid:104)(cid:105)(cid:115)(cid:32)(cid:112)(cid:97)(cid:103)(cid:101)(cid:32)(cid:105)(cid:110)(cid:116)(cid:101)(cid:110)(cid:116)(cid:105)(cid:111)(cid:110)(cid:97)(cid:108)(cid:108)(cid:121)(cid:32)(cid:108)(cid:101)(cid:102)(cid:116)(cid:32)(cid:98)(cid:108)(cid:97)(cid:110)(cid:107)
Practical Financial Modelling
A Guide to Current Practice
2nd Edition
Jonathan Swan
AMSTERDAM•BOSTON•HEIDELBERG•LONDON
NEWYORK•OXFORD•PARIS•SANDIEGO
SANFRANCISCO•SINGAPORE•SYDNEY•TOKYO
CIMAPublishingisanimprintofElsevier
CIMAPublishingisanimprintofElsevier
LinacreHouse,JordanHill,OxfordOX28DP
30CorporateDrive,Suite400,Burlington,MA01803,USA
Firstedition2005
Copyright©2008,ElsevierLtd.Allrightsreserved
Nopartofthispublicationmaybereproduced,storedinaretrievalsystem
ortransmittedinanyformorbyanymeanselectronic,mechanical,photocopying,
recordingorotherwisewithoutthepriorwrittenpermissionofthepublisher
PermissionsmaybesoughtdirectlyfromElsevier’sScience&TechnologyRights
DepartmentinOxford,UK:phone[phone];fax[phone];
e-mail:[e-mail]tyourrequestonlineby
visitingtheElsevierwebsiteathttp://elsevier.com/locate/permissions,andselecting
ObtainingpermissiontouseElseviermaterial
Notice
Noresponsibilityisassumedbythepublisherforanyinjuryand/ordamagetopersons
orpropertyasamatterofproductsliability,negligenceorotherwise,orfromanyuse
oroperationofanymethods,products,instructionsorideascontainedinthematerial
herein.Becauseofrapidadvancesinthemedicalsciences,inparticular,independent
verificationofdiagnosesanddrugdosagesshouldbemade
BritishLibraryCataloguinginPublicationData
AcataloguerecordforthisbookisavailablefromtheBritishLibrary
LibraryofCongressCataloguinginPublicationData
AcatalogrecordforthistitleisavailablefromtheLibraryofCongress
ISBN–13:978-0-7506-8647-1
ForinformationonallCIMAPublishingpublications
visitourwebsiteathttp://www.cimapublishing.elsevier.com
PrintedandboundinSlovenia
08 09 10 11 10 9 8 7 6 5 4 3 2 1
Working together to grow
libraries in developing countries
www.elsevier.com | www.bookaid.org | www.sabre.org
To Rebecca, Jack and Jeremy, for whom modelling
is something to do with clay.
(cid:84)(cid:104)(cid:105)(cid:115)(cid:32)(cid:112)(cid:97)(cid:103)(cid:101)(cid:32)(cid:105)(cid:110)(cid:116)(cid:101)(cid:110)(cid:116)(cid:105)(cid:111)(cid:110)(cid:97)(cid:108)(cid:108)(cid:121)(cid:32)(cid:108)(cid:101)(cid:102)(cid:116)(cid:32)(cid:98)(cid:108)(cid:97)(cid:110)(cid:107)
Contents
Preface to the First Edition xi
Preface to the Second Edition xiii
About the Author xv
Introduction xvii
1 Model Structure 1
Introduction 1
Risk assessment and risk controls 2
The model developer 4
Two approaches 5
Purpose 6
Structure 7
Workbook structure 10
Inputs 11
Workings 12
Outputs 14
Variations 16
Documentation 18
Reporting 21
Reports 26
Model development 27
Navigation 32
Exercises 37
2 Quality Control 39
Introduction 39
Taxonomies of error 40
Audit tools 43
Error values 47
Audit sheet 51
Structural checks 53
viii Contents
Arithmetical checks 66
Financial checks 68
Model map 73
Excel 2007 workbook tests 75
Third party model audit tools 76
Exercises 76
3 Mainly Formulas 79
Introduction 79
Range names 80
Define names 87
Additional name functionality 98
BODMAS or PEMDAS 112
Timing 112
Changing time periods 119
Circularities and iteration 123
Coercion 135
Array formulas 136
R1C1 139
Exercises 140
4 Mainly Functions 143
Introduction 143
Logical 144
Revolving credit and the cash cascade 154
Lookup 157
Financial 169
Dates 172
Other useful functions 174
Exercises 179
5 Model Use 181
Introduction 181
Risk controls 182
Grouping and outlining 182
Data inputs 185
Conditional formatting 191
Custom formatting 198
Protection 205
Contents ix
Charting 209
Exercises 211
6 Sensitivity Analysis and Scenarios 213
Introduction 213
Best practice 214
Goal Seek 215
Data tables 216
Scenarios 219
Solver 231
Risk 231
Monte Carlo simulation 233
Exercises 234
7 Automation 237
Introduction 237
Macro security settings 238
Recorded macros 239
Iteration macro 240
Assigning macros 244
Written macros 247
Branching macros 249
Quarterly/annual macro 251
Error handling 252
User-defined functions 253
Deleting macros 256
Exercises 257
Appendix 1: Keyboard Shortcuts 259
Appendix 2: Good Modelling Practice 263
Appendix 3: Case Study 269
Index 271