Spreadsheet Skills Introduction to Financial Modelling Definition

1. Spreadsheet Skills Introduction to Financial Modelling Definition A model is a program which has been developed to copy the way a system works in real life. It uses mathematical formulas and calculations to predict what is likely to happen based on data recorded about what actually did happen in the past. The reason people use modelling and Financial Modelling in particular is that they want to predict the future. This is done by carefully setting up a model that they think will do this. I say 'think' because sometimes setting up a complicated financial model is as much an art as science!

2. Spreadsheet Skills 2. Spreadsheet modelling Any model needs a set of input values, a way to work on those input values and then provide one or more output values. This is the case no matter if you are doing it on a humble laptop or the world's biggest super-computer. A spreadsheet can deal with a lot of numbers, do some work on them and produce some answers, so spreadsheets are ideal tools to use for straight-forward financial modelling. You tweak an input value and you immediately see the effect it has on the output value(s)

3. Spreadsheet Skills • A typical set of questions you may ask of a spreadsheet financial model would be • "What will happen to my profit if I put up the price by 10%?" • "If I reduce the number of staff by one, how will that affect the end of month profit?" • "How many items do I need to sell before breaking even?" • "what is the interest I will earn if my money goes into that savings account?" • It is a good idea to use a spreadsheet to make business decisions because: • It allows you to keep testing different scenarios until you find one that suits your needs • there is no financial risk to your business or staff • You can see the result immediately rather than having to wait for a month/year

4. Spreadsheet Skills 3. Spreadsheet modelling features Some of the features available within a spreadsheet that make it a good tool for modelling include :- Supports the idea of variables This is essential in order to change input values and alter pre-set numbers in the model such as VAT rate or tax levels. Use of formulas A model needs to be able to do maths on the input values and this is just what formulas can do. Use of functions This is a real time-saver as spreadsheets have hundreds of functions you can use for building up the model - for instance there is a function that can do compound interest calculations. If it did not support functions then you would have to do all the hard calculations yourself by using formulas.

5. Spreadsheet Skills Supports Goal Seek This is a very powerful feature where the goal-seek feature will find the correct input value given what you want the answer to be. It is like running the model backwards in time. For instance, I want to make 20% profit margin overall, I know how many of each item I sell, so what does the mark-up need to be? Provides a wide range of Graphs A model is often concerned with not just getting a single answer, but also needs to show how that answer is reached over time. Graphs are an ideal way of doing this. Dynamic calculation A spreadsheet is very fast on a modern computer, so you can set up a powerful model with thousands of calculations and yet you can expect an answer very quickly.

6. Spreadsheet Skills 4. Characteristics of spreadsheet applications 'Features' explained in the previous page are the fundamental things that make a spreadsheet a good choice for financial modelling 'Characteristics' are the fine details of a particular spreadsheet package that make life easier for you. Here are some useful characteristics of a good spreadsheet package - Can sort data: good for making it easier to find individual data - Absolute and relative referencing: makes setting up formulas easier - Can protect certain cells from change: Handy to make the model more robust - User interface forms : Excellent to load input values into the model - Macro language support: Allows complicated subroutines and functions to be written - Automatic re-calculation : Makes the model respond very quickly - Conditional formatting : Highlight certain numbers of interest such as a profit / loss

7. Spreadsheet Skills - Import / Export support : Easy to load input values in one go and save output values - Searching and filtering : Very good when looking for specific data in the model - Lookup tables : Used extensively in modelling to store values - Pivot tables : Very powerful tool to summarise a huge amount of data into one table - Pivot charts : Very powerful way of seeing the effect of running 'what if' questions - Replication : Easy to create a list of values by simply dragging down in a certain way - Database lookup : Can connect to external databases to get values - Worksheets supported : Very good for splitting up parts of the model - Graphing : Pie-charts, Bar charts etc each good for presenting data in a different way