Uncertainty Quantification and Stochastic Modelling with EXCEL

536 Pages·2022·34.777 MB·English

by  Eduardo Souza de Cursi

Springer Texts in Business and Economics
Eduardo Souza de Cursi
Uncertainty
Quantifi cation
and Stochastic
Modelling
with EXCEL
Springer Texts in Business
and Economics
Springer Texts in Business and Economics (STBE) delivers high-quality
instructional content for undergraduates and graduates in all areas of Busi-
ness/Management Science and Economics. The series is comprised of self-
containedbookswithabroadandcomprehensivecoveragethataresuitable
for class as well as for individual self-study. All texts are authored by
established experts in their fields and offer a solid methodological back-
ground,oftenaccompaniedbyproblemsandexercises.
Moreinformationaboutthisseriesathttp://www.springer.com/series/10099
Eduardo Souza de Cursi
Uncertainty
Quantification
and Stochastic
Modelling with EXCEL
Editors
EduardoSouzadeCursi
DepartmentMechanics/CivilEngineering
INSARouenNormandie
Saint-EtienneduRouvray,France
ISSN2192-4333 ISSN2192-4341 (electronic)
SpringerTextsinBusinessandEconomics
ISBN978-3-030-77756-2 ISBN978-3-030-77757-9 (eBook)
https://doi.org/10.1007/978-3-030-77757-9
#TheEditor(s)(ifapplicable)andTheAuthor(s),underexclusivelicensetoSpringerNatureSwitzerlandAG2022
Thisworkissubjecttocopyright.AllrightsaresolelyandexclusivelylicensedbythePublisher,whetherthewholeorpartof
the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting,
reproductiononmicrofilmsorinanyotherphysicalway,andtransmissionorinformationstorageandretrieval,electronic
adaptation,computersoftware,orbysimilarordissimilarmethodologynowknownorhereafterdeveloped.
Theuseofgeneraldescriptivenames,registerednames,trademarks,servicemarks,etc.inthispublicationdoesnotimply,even
in the absence ofa specific statement,that such names are exemptfrom the relevant protective lawsand regulationsand
thereforefreeforgeneraluse.
Thepublisher,theauthorsandtheeditorsaresafetoassumethattheadviceandinformationinthisbookarebelievedtobetrue
andaccurateatthedateofpublication.Neitherthepublishernortheauthorsortheeditorsgiveawarranty,expressedorimplied,
withrespecttothematerialcontainedhereinorforanyerrorsoromissionsthatmayhavebeenmade.Thepublisherremains
neutralwithregardtojurisdictionalclaimsinpublishedmapsandinstitutionalaffiliations.
ThisSpringerimprintispublishedbytheregisteredcompanySpringerNatureSwitzerlandAG
Theregisteredcompanyaddressis:Gewerbestrasse11,6330Cham,Switzerland
Introduction
This book presents a collection of methods of Uncertainty Quantification
(UQ), that is, a collection of methods for the analysis of numerical data,
namelywhenuncertaintyorvariabilityisinvolved.
The general aim of UQ is to characterize the observed variability in a
quantityXbyusingarandomvariableU. Intheidealsituation,theconnec-
tion between X and U is perfectly known and the random variable U has a
known distribution. Unfortunately, such a situation may be unrealistic in
practice and we must also consider situations where this knowledge is
imperfect or even non-existent, for example, situations where U is simply
unknown:variabilityisobservedwithoutpreciseknowledgeofthecause.
UQtriestousealltheavailableinformationabout(X, U)toconstructan
explanationofXbyUintoaformwhichwillbeusefulforuseinnumerical
calculationsinvolvingX.Theinformationmaybe,forinstance,anequation,
anumericalprobleminvolvingboththevariables,orsamples.
The methods of UQ are general and may be applied to a wide range of
situations. They generally belong to the large and well-supplied family of
methods based on functional representations, that is, on expansions of the
unknowns in series of functions – we find these approaches particularly in
Fourier analysis, spectral methods, finite elements, Bayesian optimization,
and quantum algorithms. It is a large family with numerous and very
diversifiedapplications.
Our objective is to present the practical use of UQ techniques under
®
EXCEL . We assume that you are a mean user of this software, that is to
sayyouareabletoperformstandardtaskswithoutdifficulties,butwedonot
®
assumethatyouhaveanexpertknowledgeofEXCEL andVBA.Someof
the worksheets proposed by this book contain VBA code, but we do not
assumethatyouareaVBAprogrammer.Obviously,ifyouareanexpertin
®
EXCEL and VBA, you will find a large amount of improvements in our
codes and worksheets: do not hesitate in making your own enhancements
and,eventually,insharingthem.
® ®
EXCEL is a part of Microsoft Office . It is an application that works
withspreadsheets–whichareakindoftableshavingalargenumberoflines
®
andcolumnsonwhichwemayperformmathematicaloperations.EXCEL
offers also the possibility of object programming using Visual Basic for
v
vi Introduction
®
Applications(VBA).EXCEL offerswidepossibilitiesfordataanalysisand
®
scientificcalculations.TherearemanybooksdevotedtotheuseofEXCEL ,
goingfromthebasicusetotheadvancedprogramming.Youmayalsofinda
®
lot of books concerning specific applications of EXCEL , namely in
Finance, Management, Operations Research. If you are familiar with
EXCEL, you probably know the extent of the possibilities offered by this
software.If,inaddition,youarefamiliarwithVisualBasicforApplications
®
(VBA), then you can appreciate the full range of possibilities of EXCEL .
®
The community of the users of EXCEL and some commercial software
provide you with many useful add-ins, such as linear algebra, numerical
calculus,probability,statistics,andsimulation.
Forinstance,youmayusethefreeadd-inMATRIX,whichprovidessome
usefulmacrosforlinearalgebraandworkswithrecentversionsofversionof
®
EXCEL . There is a free powerful extension of MATRIX, known as
XNUMBERS, which includes many numerical methods – XNUMBERS
®
works with recent versions of EXCEL , but you need to use VBA to
®
effectively use it. Another free add-in is MATHLAYER , which is a
® ®
MATLAB /OCTAVE-like extension of EXCEL , offering the possibility
®
of programming analogously to MATLAB /OCTAVE: programs may be
®
written in separate files or directly in EXCEL worksheets – in addition,
® ® ®
MATHLAYER allowstheuseofMATLAB /OctaveprogramsinEXCEL
Workbooks. A simple-to-use add-in is SDAT, designed for chemistry, but
performinggeneraltaskssuchasregression,differentiation,andstatistics.If
you are mainly interested in operations research, you may use the Jensen
Library,anoldsolverproposingmanyclassicalmethodsforthisfield.These
are a few examples among a lot of resources available for the users of
®
EXCEL –wecannotcitealltheexistingadd-insandweapologizeforthe
non-citedexistingcontributions.
®
Inthisbook,weusetheadd-insproposedbyEXCEL :theSOLVER,the
ANALYSIS TOOLPACK, and VBA, which are standard complements of
®
EXCEL .Thischoiceismadebyreasonsofconcentrationonthepurposeof
thisbook:wedon’twanttolosethereaderintheforestofadd-ins,but–on
thecontrary–wewanttokeepthereaderonthecentraltopicallthetime.Of
®
course,youmayuseyourpreferredadd-ininsteadofthestandardEXCEL
tools.
Inthenextchapter,wedescribesomebasicoperationsthatarenecessary
®
touseallthepowerofEXCEL .
Contents
®
1 SomeTipstoUseEXCEL . . . . . . . . . . . . . . . . . . . . . . 1
1.1 HowtoActivatetheSOLVERandtheANALYSIS
TOOLPACK. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.2 HowtoIncludeaThird-PartAdd-In. . . . . . . . . . . . . . . . 2
1.3 DisablingWarningsontheAdd-Ins. . . . . . . . . . . . . . . . 3
1.4 HowtoActivatetheVBATools. . . . . . . . . . . . . . . . . . . 6
1.5 HowtoInsertaVBAModule. . . . . . . . . . . . . . .. . . . . . 7
1.6 HowtoImportaVBAModule. . . . . . . . . . . . . . . . . . . . 10
1.7 MatrixFormulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.8 FixingVolatileFormulas. . . . . . . . . . . . . . . . . . . . . . . . 11
1.9 UsingAddressesofCells. . . . . . . . . . . . . . . . . . . . . . . . 12
®
1.10 UsingNamesinEXCEL . . . . . . . . . . . . . . . . . . . . . . . 14
1.11 HowtoRuntheSOLVER. . . . . . . . . . . . . . . . . . . . . . . 16
1.12 HowtoIncludeIterativeCalculationsinYour
Workbook. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
1.13 HowtoIncludeaControlinYourWorkbook. . . . . . . . . 25
1.14 HowtoIncludeaChartinYourWorkbook. . . . . . . . . . . 27
1.15 HowtoUseaVarianttoStoreAnythinginaVariable. . . 30
1.16 HowtoUseaCollectiontoStoreAnything
inaVariable. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
1.17 HowtoIncludeaClassinYourWorkbook. . . . . . . . . . . 33
2 SomeUsefulNumericalMethods. . . . . . . . . . . . . . . . . 43
2.1 LinearSystems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
2.1.1 UsingtheInverseMatrix. . . . . . . . . . . . . . . . . . 43
2.1.2 UsingtheSOLVER. . . . . . . . . . . . . . . . . . . . . 45
2.1.3 UsingGauss-JordanPivoting. . . . . . . . . . . . . . . 47
2.1.4 UsingLUDecomposition. . . . . . . . . . . . . . . . . 49
2.1.5 UsingQRDecomposition. . . . . . . . . . . . . . . . . 49
2.1.6 UsingRelaxationIterations. . . . . . . . . . . . . . . . 50
2.2 Optimization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
2.2.1 UnconstrainedOptimizationUsingthe
SOLVERinaWorksheet. . . . . . . . . . . . . . . . . 51
2.2.2 UnconstrainedOptimizationUsingthe
SOLVERinVBA. . . . . . . . . . . . . . . . . . . . . . . 53
vii
viii Contents
2.2.3 ConstrainedOptimizationUsingthe
SOLVERinaWorksheet. . . . . . . . . . . . . . . . . 54
2.2.4 ConstrainedOptimizationUsingtheSOLVER
inVBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
2.2.5 LinearProgrammingUsingtheSOLVER
inaWorksheet. . . . . . . . . . . . . . . . . . . . . . . . . 59
2.2.6 LinearProgrammingUsingtheSOLVER
inVBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
2.3 NonlinearEquations. . . . . . . . . . . . . . . . . . . . . . . . . . . 63
2.3.1 NonlinearEquationsUsingtheSOLVER
inaWorksheet. . . . . . . . . . . . . . . . . . . . . . . . . 63
2.3.2 NonlinearEquationsUsingtheSOLVER
inVBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
2.3.3 NonlinearEquationsUsingNewton-Raphson. . . 65
2.3.4 OverdeterminedLinearSystems. . . . . . . . . . . . 68
2.4 OrdinaryDifferentialEquations. . . . . . . . . . . . . . . . . . . 70
2.4.1 Runge-Kutta’sMethods. . .. . . . . . .. . . . . . .. . 71
2.5 NumericalIntegration. . . . . . . . . . . . . . . . . . . . . . . . . . 76
2.6 MultiobjectiveOptimization. . . . . . . . . . . . . . . . . . . . . . 77
2.7 InterpolationofDiscreteNumericalData. . . . . . . . . . . . 80
2.8 NumericalDerivatives. . . . . . . . . . . . . . . . . . . . . . . . . . 84
®
3 ProbabilitieswithEXCEL . . . . . . . . . . . . . . . . . . . . . . . . . . 89
3.1 Probability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
3.1.1 MassFunctionsandMassDensities. . . . . . . . . . 90
3.1.2 TheCaseofFinitePopulations. . . . . . . . . . . . . 91
®
3.2 CombinatorialProbabilitieswithEXCEL . . . . . . . . . . . 93
3.3 ConditionalProbability,Bayes’Formula,and
Independence. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
3.4 RandomVariables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
3.4.1 StatisticsofaRandomVariable. . . . . . . . . . . . . 98
3.4.2 NumericalEvaluationofStatistics. . . . . . . . . . . 102
3.4.3 ClassicalInequalities. . . . . . . . . . . . . . . . . . . . 104
3.4.4 CharacteristicFunctionandMoments. . . . . . . . 105
3.5 RandomVectorsandPairsofRandomVariables. . . . . . . 106
3.6 DiscreteandContinuousRandomVariables. . . . . . . . . . 112
3.6.1 DiscreteVariables. . . . . . . .. . . . . . . . . . .. . . . 112
3.6.2 ContinuousVariablesHavingaPDF. . . . . . . . . 114
3.7 SequencesofRandomVariables. . . . . . . . . . . . . . . . . . . 119
3.8 Samples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
3.8.1 MaximumLikelihoodEstimators. . . . . . . . . . . . 130
3.8.2 SamplesfromRandomVectors. . . . . . . . . . . . . 132
3.8.3 EmpiricalCDFandEmpiricalPDF. . . . . . . . . . 136
®
3.9 FrequentistProbabilitieswithEXCEL . . . . . . . . . . . . . 140
3.9.1 TestingAdequacytoaDistribution. . . . . . . . . . 141
3.9.2 TestingIndependence. . . . . . . . . . . . . . . . . . . . 144
3.10 GeneratingUniformRandomnumbers. . . . . . . . . . . . . . 147
3.10.1 UsingBuilt-InFunctions. . . . . . . . . . . . . . . . . . 147
Contents ix
3.10.2 UsingtheDataAnalysisTool. . . . . . . . . . . . . . 148
3.10.3 UsingVBA. . . . . . . . . . . . . . . . . . . . . . . . . . . 149
3.11 GeneratingNormalRandomNumbers. . . . . . . . . . . . . . 151
3.11.1 UsingBuilt-InFunctions. . . . . . . . . . . . . . . . . . 151
3.11.2 UsingtheAnalysisToolPack. . . . . . . . . . . . . . . 152
3.11.3 UsingVBA. . . . . . . . . . . . . . . . . . . . . . . . . . . 153
3.12 GeneratingTriangularRandomNumbers. . . . . . . . . . . . 155
3.13 GeneratingRandomNumbersbyInversion. . . . . . . . . . . 157
3.14 GeneratingDiscreteRandomNumbers. . . . . . . . . . . . . . 158
3.14.1 UsingBuilt-InFunctions. . . . . . . . . . . . . . . . . . 158
3.14.2 UsingtheAnalysisToolpack. . . . . . . . . . . . . . . 158
3.14.3 UsingVBA. . . . . . . . . . . . . . . . . . . . . . . . . . . 160
3.15 GeneratingRegularRandomFunctions. . . . . . . . . . . . . . 161
3.16 GeneratingRegularRandomCurves. . . . . . . . . . . . . . . . 165
4 StochasticProcesses. . . . . . . . . . . . . . . . . . . . . . . . . . . 171
4.1 StationarityandErgodicity. . . . . . . . . . . . . . . . . . . . . . . 172
4.2 DeterminationoftheDistributionofaStationary
Process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
4.3 WhiteNoise. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
4.4 MovingAverageProcesses. . . . . . . . . . . . . . . . . . . . . . 181
4.5 AutoregressiveProcesses. . . . . . . . . . . . . . . . . . . . . . . . 188
4.6 ARMAProcesses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
4.7 MarkovProcesses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
4.8 DiffusionProcesses. . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
4.8.1 TimeIntegralandDerivativeofaProcess. . . . . 215
4.8.2 SimulationoftheTimeIntegralofaWhite
Noise. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
4.8.3 BrownianMotion. . . . . . . . . . . . . . . . . . . . . . . 223
4.8.4 RandomWalks. . . . . . . . . . . . . . . . . . . . . . . . . 226
4.8.5 Itoˆ’sIntegrals. . . . . . . . . . . . . . . . . . . . . . . . . . 228
4.8.6 Itoˆ’sCalculus. . . . . . . . . . . . . . . . . . . . . . . . . . 230
4.8.7 NumericalSimulationofStochastic
DifferentialEquations. . . . . . . . . . . . . . . . . . . . 236
5 RepresentationofRandomVariables. . . . . . . . . . . . . . 243
5.1 TheUQApproachfortheRepresentationofRandom
Variables. . .. . . .. . . .. . . .. . .. . . .. . . .. . . .. . . .. . 243
5.2 Collocation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
5.2.1 FindingtheCoefficientsoftheExpansion
inaWorksheet. . . . . . . . . . . . . . . . . . . . . . . . . 254
5.2.2 SolutionUsingVBA. . . . . . . . . . . . . . . . . . . . . 256
5.2.3 SolutionUsinganAdaptedWorkbook. . . . . . . . 259
5.3 VariationalApproximation. . . . . . . . . . . . . . . . . . . . . . . 265
5.3.1 FindingtheCoefficientsoftheExpansion
inaWorksheet. . . . . . . . . . . . . . . . . . . . . . . . . 266
5.3.2 SolutionUsingVBA. . . . . . . . . . . . . . . . . . . . . 268
5.3.3 SolutionUsinganAdaptedWorkbook. . . . . . . . 270