Financial modelling formulas

Here you’ll find our curated exhibition of the Excel functions most useful in financial modelling. It’s not all the formulas that Excel has to offer (when you’ve got a spreadsheet open, pressing the insert function keyboard shortcut “Shift” “F3” will reveal that there are hundreds). But below you’ve got a list of functions that delegates on our courses ask about regularly – so we know that what we’ve got is a list of the formulas people working in financial modelling find most useful and most helpful. If you’re on top of these Excel functions then you’re well on your way to becoming a financial modelling expert!

Excel financial modelling: ‘must have’ functions

Here is a list of Excel functions that you must be confident you have mastered if you want a successful financial modelling career. The good news is that we have kept our list of Excel ‘must haves’ extremely short! There’s an awful lot you can achieve in Excel with just a few basics, but you definitely need to be confident that you can use for example “If” and “Vlookup” (Vlookups are one of the tricker Excel functions to use):

    1. Excel If functions: constructing error checks in financial modelling; mastering If functions by creating a balanced scorecard traffic light system with an embedded/ nested If function
    2. Using Excel’s referencing, Vlookup and other lookup functions to get data where you want it in financial modelling
    3. Excel goal seek: iterating with a model and conducting sensitivity analysis, answering questions such as “How far could revenues fall before a key financial target is no longer met?”.

The great news is the list of must have functions is not long – the Excel survival tool kit does not have to be huge!

Excel modelling ‘nice to haves’

Along with the ‘must haves’ here is a list of other useful Excel functions likely to help you solve particular financial modelling problems:

    4. Using conditional formatting to highlight key errors in a financial model
    5. Using Excel’s Choose function to get data into the right place when modelling, structuring scenarios and setting your model up so that it can instantly switch between scenarios
    6. Automating financial modelling scenarios with combo/ drop-down boxes
    7. Naming cells to help you navigate around a financial model
    8. Outputting financial modelling results using Excel’s scenario manager
    9. Modelling with “timing flags“: building flexibility into your model
    10. Applying Excel “custom formats” to get numbers displaying exactly the way you want them to
    11. Other alternatives aside from Vlookup for getting modelling data where you want it: Excel’s Index function, combined with Match and Count
    12. Using Excel TRUE/ FALSE checks as a quicker shorter alternative to If functions
    13. Using Excel data validation to ‘force’ model users to enter particular values
    14. Password protecting whole spreadsheets or areas of Excel workbooks (again, forcing model users to only enter values in particular cells)
    15. Using Excel’s Round function to reduce apparent spreadsheet errors
    16. Using Excel’s Iserror and Iferror functions to identify and reduce modelling errors
    17. Sorting data with Excel’s Sumif, Sumifs and Sumproduct functions
    18. Performing the same sort of analysis, but condensing and shortcutting function length using Excel’s array formulas
    19. Using data tables as part of scenario and sensitivity analysis in Excel modelling
    20. How Excel’s date functions can be helpful in financial modelling
    21. Pitfalls in the use of escalation factors
    22. 3D referencing and its use in modelling
    23. Excel’s watch window and camera functions – how they can be used to monitor results in a financial model
    24. Excel macros
    25. Financial modelling with deliberate circular references
    26. Using Max and Min functions – an example modelling the swing between cash and a short term credit facility
    27. Tax losses: a further example of financial modelling with Excel’s Max and Min functions
    28. Using pivot tables to sort and categorise Excel data
    29. Dealing with Excel’s edit links settings.

What’s this Excel financial modelling course material doing here? For free!

You’re looking at a free online Excel financial modelling course extract covering Excel functions.

This financial modelling course material is lodged here because it has proved useful to training delegates, either before or after a program.  Unless specifically requested by a client, they are not necessarily all covered on Financial Training Associates’ taught Excel modelling courses – so this material is here for our delegates’ future reference.