Spreadsheets in Finance and Forecasting

1
Spreadsheets in Finance and Forecasting

• Week 4
• Using Formulae

2
Working with Formulae

• In previous weeks we have seen that we can work
  with cell formulae to calculate totals, averages
  and other summary values, and can keep running
  totals of transactions.
• This week we explore this further, and look in
  depth at the processes behind formulae

3
Objectives for Week 4

• After working through the materials for this week
  you will be able to
• Work confidently with spreadsheet formulae
• Understand and work with operator precedence
• Use absolute and relative addresses and range
  names

4
Following the Slides
Action Point!

• When you see this
• You will need to open the spreadsheets referred
  to in the slides
• Switch between the slides and the spreadsheet to
  follow the examples

5
Flower Shop Example
Action Point!

• The next few examples use Flower Sales.xls
• This is a simple spreadsheet which carries out a
  number of calculations of sales and profits

6
Floral Arrangements

• The Florist sells three different sizes of flower
  arrangement
• large, medium, small
• The first task is to calculate the total sales
  income for each type.

7
Large Florals Sales
How can we work out the total sales here?
8
Large Florals Sales
Enter the cell formula B5C5
9
Medium and Small Florals
Use Auto fill and check the cell entries
Cell entries are B6C6 B7C7
10
Pot Plants Profits

• There are three types of pot plants
• Chrysanthemums, Violets, Cyclamen
• The task here is to calculate, for each of these
  the total profit for each type of plant

11
The Flower Shop Spreadsheet
How can we work out the Total Profit here?
12
The Flower Shop Spreadsheet
Cell Contents (C10- B10)D10
Now use auto fill to complete these cells
13
Special Events

• From their stock of flowers and shrubs, the
  company hires out materials and celebration
  banners for special events such as weddings,
  birthdays and anniversaries.
• The third task is to calculate the total hire
  charges for the special event below

14
The Flower Shop Spreadsheet
What is the formula for working out the Total
Hire Charges for the Special Event?
15
The Flower Shop Spreadsheet
Formula B15B16 C15C16 D15D16 E15E16
16
Operations

• In the previous example we saw calculations being
  carried out on cell addresses using a formula
• Such formulae rely on mathematical conventions

17
Arithmetic Operations

• Excel calculates the results by using the rules
  of arithmetic
• The table right shows the arithmetic operations
  that can be used and the symbols that represent
  them

Operator Performs Example
Add A1 B1
- Subtract A1 - B1
Multiply A1 B1
/ Divide A1 / B1
Power A13
18
Operator Precedence
B Calculate within the brackets
I Calculate Indices and powers
D Divide
M Multiply
A Add
S Subtract

• In the flower shop examples we used operations
  and bracketing
• To use this consistently, we need to clarify the
  order in which calculations are to be done.
• Operator precedence uses the BIDMAS rule

19
Example

• The cell contents are
• A2 3.2
• A3 4.8
• A4 1.5
• A5 4.8

• Suppose that the formula in cell A6 is
• (A2A3)2 3A4 A5/2
• Calculate the value in cell A6.

20
Calculation Example

• (A2A3)2 3A4 A5/2
• Brackets
• (A2 A3) is (3.24.8) 8.0
• Indices
• (A2A3)2 is 8.02 64
• Division
• A5/2 is 4.8/2 2.4
• Multiplication
• 3A4 is 3 x 1.5 4.5
• Addition
• (A2A3)2 3A4 is 64 4.5 68.5
• Subtraction
• (A2A3)2 3A4 A5/2 is 68.5 2.4 66.1

21
Calculations Example
Action Point!

• The spreadsheet calculations.xls is a simple
  spreadsheet which will give you practice at
  constructing formulae

22
Calculations Spreadsheet
This spreadsheet asks you to calculate a number
of different values by using the cell references
23
Solutions
(F4G4)D4
(E4-D4)(H4-G4)
10E45(F4G4H4)
(F4G4)4/H42
24
What happens when you copy and paste formulae?

• In the next few slides we look at how the cell
  addresses change when they are copied into
  different locations

25
Cell Referencing

• A cell may be referenced in one of four ways
• An Absolute Address
• A Relative Address
• A Mixed Address
• Range Name

26
Absolute Addressing

• Absolute addressing means that we always want
  that part of the formula to use that particular
  cell.
• For example
• Multiply A5 by 3
• A5 3
• Sum all the values in a block
• Sum(A1C2)

When copied, this formula will always refer to
cell A5
When copied, this formula will always refer to
the block of cells A1, B1, C1, A2, B2, C2
27
Relative Addressing

• Copying Cell
• Address K9
• K9 Q9
• R13
• K14

When copied a relative address will change,
depending upon where it is put
Copy it along, and the column will change
Copy it diagonally and both row and column will
change
Copy it down and the row will change
28
Mixed Addressing

• We can relax part of the absolute address
• We can use this to
• Fix a Column
• B3 10
• Fix a Row
• A5 20

When copied this formula will always refer to a
cell in column B
When copied this formula will always refer to
cell in row 5
29
Using Range Names

• It is possible to label a particular cell with a
  name, which can then be used in functions and
  formulae.

This cell D1 can be labelled as depreciation
Click in this box here to change the D1 to
depreciation, then press the return key
30
Using Range Names

• Using the label we have created, we can use this
  as an absolute cell reference it will not change
  if we copy or paste using it in a formula

This cell can now be referred to as
depreciation
In this cell, we type in depreciation, and
press the return key
31
Exploring Copy and Paste

• In the next few examples we will carry out some
  simple financial calculations
• Each time we will enter some formulae, then copy
  and paste these formulae to carry out the
  calculations in later cells

32
Depreciation Example
Action Point!

• The spreadsheet depreciation.xls looks at the
  following problem
• Suppose you paid 5000 for a car at the end of
  1999 what would the car be worth in 2010?

33
The Depreciation Spreadsheet
In our example, the car will lose 10 of its
current value year on year
What we need to do is to put formulae in the
cells, so that we end up with our answer here!
34
The Depreciation Spreadsheet
The first thing to do is to re-label cell D1 as
depreciation
Cells have been labelled with post-its Read
these to find out what to put in the cells
35
Depreciation Solution
The first line of the solution is
D4-C5
depreciation
D4 B5
36
Depreciation Full Solution
When all the cell calculations are pasted down,
the answer appears here
37
Auditing Formulae

• Sometimes a formula does not quite give you the
  answer that you wanted.
• In this case you can use the auditing tools to
  check where the answer has originated

38
The Auditing Tools
Action Point!
Click on Tools then Auditing and Auditing Toolbar
39
Auditing a Spreadsheet
The first and third icons on the toolbar are
Trace precedents and Trace Dependents
40
Tracing Precedents
Click on cell B5 . Now click Trace Precedents
on the toolbar
The blue arrow shows that cell B5 is taking
information from cell D1
41
Tracing Dependents
When you click on Trace Dependents, the arrow
leads to cell C5 This is because C5 uses
information from B5
42
More Relationships
By clicking on the Trace Dependents button
repeatedly, you can track how the information
passes from cell to cell
43
Further Challenge

• To extend your understanding of formulae, the
  next part of this presentation looks at copying
  and pasting across rows and down columns
• It uses both relative and absolute addressing

44
Throwing Dice
Action Point!

• The Dice.xls Spreadsheet sets you this challenge
• Two fair dice are thrown. Create a table to show
  all possible sums of the two values

45
Throwing Dice

• In order to get you to think about relative and
  absolute cell addressing,
• You are only allowed to write one formula, and
  that must be in the top left cell
• All the others must be copied and pasted from
  this initial cell.

46
Throwing Dice- Solution

• We needed to add cells B4 and C5
• However, we want to add C4 to all the cells in
  the C column, so we fix the 4, and use C4
• Also we want to add B5 to all the cells in row
  5, so we fix the B and use B5

C4 B5
47
Examining the Solution

• Copying and pasting that initial cell gives the
  following
• If you examine the final cell you will see how
  the formula has been modified

H4 B10
48
Savings and Loans
Action Point!

• As a final example, look at savings and loans.xls
• This spreadsheet calculates interest on savings,
  loan repayments and mortgages.
• You will need to work out the formulae

49
Follow-Up work

• Portfolio Task 2 now takes you through a scenario
  in which you create portfolios of shares to
  maximise your return on investment.