Uncertainty Quantification and Stochastic Modelling with EXCEL

EXCEL^® is a powerful software: a complete exploration of its possibilities cannot be made here. In this chapter, we present some tips that will be useful in the sequel.

As indicated in the Introduction, the reader may find on the net free and commercial add-ins to make Numerical Calculus with EXCEL^®. We cited some of them: MATRIX, XNUMBERS, MATHLAYER^®, the Jensen Library.

EXCEL^® proposes many intrinsic functions to deal with probabilities, but, as previously observed, you may also extend their performance by using add-ins. In the sequel, we recall some basic elements of probability, statistics and the corresponding built-in functions in EXCEL^®.

The formal study of stochastic processes is not in the scope of this book, which focus on the use of EXCEL^® to solve practical problems.

As previously observed, random variables are a comfortable tool to model variability, whenever statistical data is available. Nevertheless, the complete knowledge of their distribution is rarely available, since only samples may be obtained. In some situations, even the variable itself cannot be observed – it is a hidden variable – and only its effects may be observed: variability of the response of the system is observed, but the cause of this variability remains unidentified. To use random variables in models, it is necessary to have information such as its distribution (for instance, its cumulative distribution or its density) and some of its statistical properties (for instance, mean, variance, mode). Classical approaches start by assumptions on the distribution – for instance, assuming that the variable under consideration is Gaussian or a particular transformation of a Gaussian variable. In the classical approaches, severe errors of model are difficulty to correct, even in the Bayesian approaches, since the conjugate distributions are predetermined, so that the choice of the prior distribution seriously constrains the result for the posterior distribution. Statistical Learning approaches are more flexible but request a large amount of data and face difficulties when the cause of the heterogeneity is unobserved. Uncertainty Quantification (UQ) proposes an alternative approach tending to introduce both more flexibility and economic use of data: on the one hand, the connection of the basic variable chosen for the representation (which may be interpreted as a “prior”) and the variable to be represented (which may be interpreted as a “posterior”) is relaxed, so that severe errors may be corrected. In addition, variability generated by hidden variables may be represented by explicit ones. On the other hand, reasonable quantities of data are enough to the determination of the representations.

Solving equations is a basic activity in most fields of knowledge – for instance, Engineering, Management, Economics, … A simple example is furnished by the basic input-output analysis introduced by W. Leontieff in 1936 (Leontieff, 1936, 1937): the economy of a country may be empirically described by a matrix T = (T_ij, 1 ≤ i, j ≤ n) connecting inputs and outputs of the different economic sectors, such as the one presented in Fig. 6.1. Such a matrix synthetizes the empirical data about the connections and interdependencies between the economical fields under consideration. Nowadays, input-output matrices are used for statistical analysis and planning in Economics (Organisation for Economic Co-operation and Development, 2021). For instance, they may be used to furnish estimations of short-term impact of economic changes. The general form of T is shown in Table 6.1.

Differential equations are first of all equations, so that the methods used for equations may be used – at least in principle.

The classic text in Game Theory (GT) is the book by John Von Neumann and Oskar Morgenstern, entitled Theory of Games and Economic Behavior, published in 1944 (Von Neumann & Morgenstern, 1944), what is the culmination of Von Neumann’s works on games since the 1920s (Von Neumann, 1928). Previous works may be found in (Cournot, 1838; Bachelier, 1901; Zermelo, 1912; Borel, 1921).

In Sect. 2.2 (page 52), we introduced the classical mono-objective optimization problem.

Reliability is a quite old idea in many areas of human activity facing uncertainties. For example, Civil Engineering faces such a difficulty since its dawn.