Microsoft Excel Fundamentals for Analysts
This is a highly intensive course covering the fundamentals of Microsoft Excel required by every Analyst. These skills are central to becoming more productive completing typical day to day task required for your work. This course also gives you the Excel skills required for more advanced analysis and reporting tasks such as financial modeling and reporting and analytics with power pivot.
The course is delivered by David Brown, a Microsoft MVP with over 20 Years Experience working on Excel, Business Intelligence, Financial Modelling and training projects. David Heads, dbrownconsulting, an accredited training provider of the Financial Modeling Institute. This course was specifically designed to deliver the Excel foundations content required for the Level 1 Examinations of the institute for the Advanced Financial Modeler (AFM) designation.
Working with Text & Dates & Time
Yes, Excel is a numbers tool, but it is actually an excellent tool for analysing and reporting text, dates and time based reporting. Knowing these skills will go a long way in helping you understand why Excel works the way it does. Topics include:
- Master how Excel treats Dates & using Date Functions
- Master how excel calculates time
- Master these text functions: CONCATENATE, CONCAT, TEXTJOIN, TEXT
Using Formatting Styles in your Models
Formatting is another little understood tool in Excel. Do you know about Excel's formatting syntax and how to use it to create formatting styles? Styles allow you to effectively automate the formatting of cells in your spreadsheet reports and models. All those yellow coloured cells can be changed in an instant without clicking on a single one of them. Topics covered include:
- Learn the secrets of formatting in Excel
- Learn Why Styles are the way to format in Excel
- Learn the core formatting styles used by financial Modellers
Math & Rounding Functions in Excel
I bet that you have only ever used the function ROUND, and maybe ROUNDUP and ROUNDDOWN! Do you know about FLOOR or CEILING and how to use them? What about Bankers rounding? Topics covered include:
- Basic Math Functions in Excel
- Basic & Custom Rounding Functions in Excel
- How to calculate Banker’s Rounding
Mastering Lookup & Reference Functions
I believe there is no analyst in this world that can survive without knowing VLOOKUP. But not many know VLOOKUP needs help from the MATCH function to be efficient. Also, after a while you will learn that the king of lookup isn't really VLOOKUP but INDEX. Learn this and much more in this section.
- Learn CHOOSE, VLOOKUP, HLOOKUP, OFFSET, INDEX, MATCH, INDIRECT functions in Excel
- Case study application of these functions in Models
Time Value of Money Calculations in Excel
Lets be honest, money is at the center of all the work that an analyst does (even non profits need the stuff!!). So it makes sense to understand how money works in the context of time and how to do these calculations effectively in Excel. $10 today does not have the same value as $10 a year from now. We use a planning for your pension as the case study to learn these concepts and how to apply them in Excel.
- Real vs Nominal cash flows
- How to build an Inflation Index for your model
- Understanding a TVM question with Pen and Paper
- Mastering Excels TVM Functions
- Building and Sensitizing a Retirement Planning Model
- Common Errors to avoid in TVM Calculations
Managing Scenarios in your model
Once you have built a really cool spreadsheet reporting tool or model that does what it is supposed to do, the next thing is to start asking "What if" questions. What if interest rates go up, what if they go down, what if we cannot meet our obligations etc etc. Many people end up saving different versions of their models for each scenario they are trying to assess. This is so inefficient. Excel has its built in scenario manager which is good, but what is even cooler is building a scenario manager you have 100% control over.
This topic is broken down into three chapters, one covering how Excel manages Scenario and two showing techniques you can use to build and manage scenarios yourself. Chapters include
- Using Excel's Scenario Manager Tool
- Managing Scenarios with the Data Validation Tool
- Managing Scenarios with Form Controls
Sensitivity Analysis in models
Building scenarios is all great and a real must know for most analysts. But there is a saying that a picture is worth a thousand words. I will modify this to "A Dashboard is worth a thousand sheets", wouldn't be cool to see all those wonderful scenario results in a single sheet all calculated and reported together? That is precisely what sensitivity analysis does, it presents multiple result (multiple scenarios from different sets of inputs) in a single table. This is a fantastic tool for decision making.
This topic is broken down into two sections.
- Introduction to Sensitivity Analysis
- Sensitivity Analysis with Data Validation & Form Controls
Working with Logical Operators, Functions and Techniques
Albert Einstein said "Logic will get you from A to B but Imagination will take you everywhere". When building models, you need your imagination to think through all the possible effects of a set of transactions. It could be as simple as knowing that if we don't have cash to run the business then we need to look for the cheapest means of financing to plug this gap. As modellers, you need to use Logical operators, functions and techniques to automate these possibilities into your model. MASKS, FLAGS and COUNTERS are a super techniques which rely on smartly built logical formulas some of which use Logical functions like IF, AND, OR. This section cover this and so much more:
- Logical Operators in Excel
- Using IF, IFS, AND, OR, XOR, SWITCH logical functions
- Practical use of MASKS and FLAGS to build a loan approval model
- Other use cases in Financial Models
Please remember to take advantage of the discussion board on the top left of this page to ask the trainer any question.