Spreadsheets in Finance and Forecasting

1
Spreadsheets in Finance and Forecasting

• Week 5
• Using Formulae

This presentation is in Self-Study Format.
Before you begin, open the spreadsheet Formulae
Examples.xls Press F5 to begin.
2
A note on self-study presentations

• A lot of presentations in this module will be
  produced in self-study form.
• The best way to use these is to have the
  presentation full-screen and presenting (ie not
  in edit mode)
• If you have already pressed F5, then you are
  viewing it in that mode already.
• At the foot of the screen are navigation buttons.
  These allow you to go backwards forwards
  through the presentation, and sometimes make
  choices.

3
Following the Slides

• When you see this, You will need to work on the
  worksheets referred to in the slides
• Switch between the slides and the Formula
  Examples spreadsheet to follow the examples. You
  can do this by pressing down the alt key (to the
  left of the spacebar), together with the tab key
  (just above the Caps Lock key) Alt-Tab allows
  you to flip between applications.
• Try this now

Action Point!
4
Objectives for Week 5

• 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

5
Menu

• Introduction to Formulae
• Arithmetic Operations
• Relative Absolute Addressing
• Auditing Formulae
• Challenge Task

• There are 5 different sections.
• Each section asks you to undertake some
  activities.
• You can work through these one at a time.

6
Introduction to formulae
7
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

8
Flower Shop Example
Action Point!

• The next few examples uses Flower Sales
• This is a simple worksheet which carries out a
  number of calculations of sales and profits

9
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.

10
Large Florals Sales
How can we work out the total sales here?
Answer
11
Large Florals Sales
Enter the cell formula B5C5
12
Medium and Small Florals
Use Auto fill and check the cell entries
Cell entries are B6C6 B7C7
13
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

14
The Flower Shop Spreadsheet
How can we work out the Total Profit here?
Answer
15
The Flower Shop Spreadsheet
Cell Contents (C10- B10)D10
Now use auto fill to complete these cells
16
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

17
The Flower Shop Spreadsheet
What is the formula for working out the Total
Hire Charges for the Special Event?
Answer
18
The Flower Shop Spreadsheet
Formula B15B16 C15C16 D15D16 E15E16
19
The Flower Shop Spreadsheet
Now try changing the quantities sold. You should
find that the totals change in alignment with the
new values
End of Section
20
Arithmetical Operations
21
Operations

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

22
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

23
Operator Precedence

• 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

24
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.

Answer
25
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

26
Calculations Example
Action Point!

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

27
Calculations Spreadsheet
This spreadsheet asks you to calculate a number
of different values by using the cell references
Answer
28
Calculations Spreadsheet Solutions
(F4G4)D4
(E4-D4)(H4-G4)
10E45(F4G4H4)
(F4G4)4/H42
29
Calculations Spreadsheet Changing numbers

• Replace the numbers in the boxes with these
  values

Now, a different set of different calculations
are done, and you obtain these results
End of Section
30
Relative Absolute Addressing
31
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

32
Cell Referencing

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

33
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
34
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
35
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
36
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
37
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
38
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

39
Depreciation Example
Action Point!

• The worksheet depreciation 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?

40
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!
41
The Depreciation Spreadsheet
The first thing to do is to label cell D1 as
depreciation
Answer
Cells have been labelled with post-its Read
these to find out what to put in the cells
42
Depreciation Solution
The first line of the solution is
D4-C5
depreciation
D4 B5
43
Depreciation Full Solution
When all the cell calculations are pasted down,
the answer appears here
End of Section
44
Auditing Formulae
45
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

46
The Auditing Tools
Action Point!
Click on Tools then Auditing and Auditing Toolbar
47
Auditing a Spreadsheet
The first and third icons on the toolbar are
Trace precedents and Trace Dependents
48
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
49
Tracing Dependents
When you click on Trace Dependents, the arrow
leads to cell C5 This is because C5 uses
information from B5
50
More Relationships
By clicking on the Trace Dependents button
repeatedly, you can track how the information
passes from cell to cell
End of Section
51
Challenge Task
52
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

53
Throwing Dice
Action Point!

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

54
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.

Hint Begin with a formula which adds together
cells C4 and B5, then experiment!
Answer
55
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
56
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
57
Follow-Up work

• Portfolio Task 4 now takes you through a scenario
  in which you create a stock control spreadsheet
  for the owner of the Cornershop business.
• This task is in two parts. You can begin it this
  week, but you will need material from next weeks
  presentation in order to complete it.

End of Section