### I: Foundations of Modelling with Excel

Introduction to Financial Modelling
Principles of Excel as a Modelling Tool
Common Modeling Structures (I)
Further Operations and Shortcuts
Excel Functions (I): Information and Numerical Aggregation
Excel Functions (II): Conditionality, Aggregations and Arrays
Common Modelling Structures (II)
Excel Functions (II): Lookups and Referencing
Model Planning and Best Practices
CUTZ or ... intrducton to rest of program....and clisoing remarks
1 of 2

# 1.2 Simple Example I: Estimating Ocean Water Volume

Let us consider the issue of how to estimate the amount of water in the world’s oceans. This could not only be of general interest, but also may be needed when assessing the heat-retaining capacity of the water, or other aspects related to atmospheric physics and climate behaviour.

## Core Model Elements

The following description is broken into the stages of concept, implementation, and conclusions.

#### Concept: Variables and Relationships

From a conceptual point of view, a simple model for the volume of water could be:

• Assume that the earth is a perfect sphere (with a fixed radius or diameter).
• Multiply the surface area of the sphere by the percentage of the surface that is covered in ocean, to give the surface are of the ocean.
• Multiply this area by the average depth of the ocean to give the volume.

#### Implementation: Data and Calculations

The table in the image below shows the assumptions, step-by-step calculations and the result. (The assumption values are the items with blue font and grey background, the calculations are in cell with black font, and the final result shown in the green-shaded cells, with the last cell showing the result in million cubic kilometers (i.e. the estimated volume is approximately 1323 million cubic kilometers of ocean water): #### Results and Conclusions

The core result of this model is that the estimated volume is approximately 1323 million cubic kilometers of ocean water. From the model as presented, it is difficult to draw further relevant conclusions, since we have not explained the context or need for this specific estimate sufficiently (e.g. in terms of decisions or its impact on climate models or measures to control or limit the effects of a changing climate).

However, in general, one can see that models (and modelling processes) can provide a framework to:

• Make estimates that are grounded in logic and use the available data.
• Work in a structured way, which exposes the assumptions used.
• Identify areas of uncertainty or lack of knowledge.
• Identify areas where a better understanding or better data could be used to enhance the accuracy of the results.
• Potentially generate insights or highlight issues that one may not have otherwise considered.

It is clear and intuitive that this figure is unlikely to be exact for several reasons, discussed in the following.

#### Uncertainty in Input Values

There may be some uncertainty in the input values. Whilst the diameter of the earth and the proportion covered by ocean water may be known quite precisely, these values may not be exact. In particular, the average ocean depth is likely to be most uncertain: First, at the time of writing, the oceans having not been fully mapped (notably in parts of the southern hemisphere). Second, the calculation of the average is not straightforward: Each ocean is quite different in size and general form (the Pacific Ocean having an average of over 4km, whilst the Arctic Ocean is around 1km on average), and each has a sub-surface structure which is not level.

The following image shows the range of impact for the total estimated volume, as the assumption values are varied across a range of various published estimates for each parameter (that were obtained through simple internet research at the time of writing some of which may be more credible than others!): #### Model Mapping or Intrinsic Assumptions

When building any model, assumptions are made that are of an inherent or structural nature, and which are therefore intrinsic to the model.

For example, in the model being considered, one key assumption is that the world is spherical. Another assumption is that the ocean volume contains only water, as well as that ocean volume is constant over time (the volume could vary in accordance with the time of day as well as the seasons, due to temperature differences), and so on.

In principle, such issues can be dealt with by building more robust and detailed models, at least providing that the issues are identified and can be understood or that relevant data can be found or collected. For example, one could adapt the current model so that the surface area uses a formula for non-spherical shapes (e.g. ellipsoids), and so on: Whether this is worth doing will depend on the complexity and benefit of doing so.

#### “Unknown Unknowns”

The model mapping and parameter values may be incorrect for reasons that we are simply not aware of, and essentially can do little about (other than study the situation in more detail and to improve one’s overall understanding of it). It could be that the model does not contain variables that it should, simply because we are not aware of them.

Scroll to Top
error: Alert: Content is protected !!