Introduction to Financial Modelling
Principles of Excel as a Modelling Tool
Essential Operations and Short-Cuts
Introduction to Excel Functions
Building Models: Common Structures and Best Practice Principles
Planning Models for Decision Support

2.2 Creating Calculations and Viewing Formulas

Entering Text and Data

Each cell can be empty or can contain one of:

  • A text field.
  • A number.
  • A formula.

Numbers or text can be entered into a cell by selecting it (with the mouse or equivalent tracker pad), typing in a text or numerical field, and pressing the ENTER key.

The image below shows a sheet in which the text fields “Price” and “Volume” have been entered into cells B2 and B3, and the numerical values of 10 and 800 have been entered into the cells C2 and C3. Note that the numbers are entered as genuine numerical fields (i.e. 10 and 80, and not for example as ’10 and ’80, which would be text fields):

 

It is important to note that the content of every cell is separate to that of other cells. That is, although cells B2 and B3 contain text which describes the content of the associated numerical values (in C2 and C3), this association is of a visual nature only. This apparently trivial point becomes important in more complex modelling situations.

Creating Basic Calculations using Arithmetic Operations

The building of a model requires that dependencies between items are expressed using numerical calculations. In many cases, this requires only basic arithmetic operations (which can be implemented from the keyboard):

  • Addition and subtraction use +, – respectively.
  • Multiplication and division use *, / respectively.
  • Raising to a power, using ^.

The symbol < can be used in logic or conditional comparisons to mean “less than” (and <= to mean “less than or equal to”), and with analogous interpretations for the symbols > and >=.

For example, an influence diagram in which Sales Revenue is to be determined from Price per unit and Volume Sold in units could look as in the image:

 

The corresponding Excel model is one in which Sales Revenue is calculated by multiplication of price and volume:

  • In cell B5, enter the text label Sales Revenue.
  • In cell C5, enter the formula =C2*C3.

Some of the points related to the creation of the formula are simple but important:

  • To refer to the cells C2 and C3, one can either use the mouse to point and click (generally recommended) or type the cell reference directly (which is not typically  recommended as it is prone to one making a typing error and the calculation referring to the wrong cell accidently).
  • When a cell is referred to in a formula, it is treated as if it were a value. Thus  (because the = sign indicates that a formula is being created, cell C5 will evaluate to =10*800 i.e. 8000), and result in the worksheet looking like:…

 

Silly Errors to Avoid!

In the early stages of working with Excel, some mistakes are easy to make.

For example, if one had entered C2*C3 into cell C5 (i.e. without the =), then there would be no formula to evaluate, and the result would be the text field as entered:

 

Other mistakes include:

  • Typing the number 8000 into the cell (C5), rather than creating the formula calculates that value. Whilst the entered value is correct, of course it does not result in a model, since there is no dependency relationships expressed. In practical terms, if Price or Volume were changed, the Sales Revenue would remain at 8000, and so would be wrong. In fact, the changing of input values to test their effect on other aspects of the calculations is an important basic check on the integrity of the model (i.e. simple forms of sensitivity analysis are very useful and important).
  • Building a formula that refers to the wrong cells, for example to have typed =C2*C2 into cell C5. This is easy to do by incorrectly pointing the mouse or when typing cell references directly. In some cases (such as this example) the error would be immediately visible if one performed a simple cross check – since the Sales Revenue would show 100 rather than 8000. However, where the calculations are complex or the inputs are of similar order of magnitude to each other, then the presence of such mistakes may not be easy to notice, and may be overlooked. (A disciplined application of sensitivity analysis can also help detect such errors, since one would in principle detect that changing the value in C3 would have no effect on C5), even though changing Volume should clearly affect the Sales Revenue. The use of dependency and precedents tracing is also a useful tool to detect such errors – see later in the course).

Viewing a Formula

The formula that has been created can be viewed in several ways. One way is to select the cell containing the formula (i.e. C5) and to the press the F2 key on the keyboard, which results in:

 

To exit, one can use the ESC key.

Other ways to view formulas are covered in the next section, and include:

  • By using Formulas/Show Formulas menu item (toggle).
  • Looking within the Formula Bar
Scroll to Top
error: Alert: Content is protected !!