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. 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 Objectives for Week 4

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: =B5*C5

9. Medium and Small Florals Use Auto fill and check the cell entries Cell entries are: =B6*C6 =B7*C7

10. 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 Pot Plants: Profits

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: =B15*B16 + C15*C16 + D15*D16 + E15*E16

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. 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 Arithmetic Operations

18. 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 Operator Precedence

19. The cell contents are: A2: 3.2 A3: 4.8 A4: 1.5 A5: 4.8 Suppose that the formula in cell A6 is: =(A2+A3)^2 +3*A4 –A5/2 Calculate the value in cell A6. Example

20. Calculation Example =(A2+A3)^2 + 3*A4 – A5/2 Brackets: • (A2 + A3) is (3.2+4.8) = 8.0 Indices: • (A2+A3)^2 is 8.0^2 = 64 Division: • A5/2 is 4.8/2 =2.4 Multiplication: • 3*A4 is 3 x 1.5 =4.5 Addition: • (A2+A3)^2 + 3*A4 is 64 + 4.5 =68.5 Subtraction • (A2+A3)^2 + 3*A4 – 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 =(F4+G4)*D4 =(E4-D4)*(H4-G4) =10*E4+5*(F4+G4+H4) =(F4+G4)^4/H4^2

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 means that we always want that part of the formula to use that particular cell. For example: Multiply A5 by 3: $A$5 * 3 Sum all the values in a block Sum($A$1:$C$2) Absolute Addressing 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. Copying Cell Address K9 K9Q9 R13 K14 Relative Addressing 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. We can relax part of the absolute address We can use this to: Fix a Column: $B3 + 10 Fix a Row: A$5 + 20 Mixed Addressing 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. It is possible to label a particular cell with a name, which can then be used in functions and formulae. Using Range Names This cell D1 can be labelled as “depreciation” Click in this box here to change the D1 todepreciation, then press the return key

30. 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 Using Range Names 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: =depreciation = D4 * B5 =D4-C5

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 C$4 • Also we want to add B5 to all the cells in row 5, so we fix the B and use $B5 = C$4 + $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 = H$4 + $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.