Microsoft Excel Fundamentals for Analysts

Learn the Essential Excel Skills for every Analyst | taught by David Brown
  • 108 Videos
  • 5 Quizzes
  • 2 Surveys
  • 7 Texts
  • 1 PDF
  • 8 Downloads
  • 10.0 hrs

Course Curriculum

$399.00
Advanced Financial Modeler - Premium Package
$199.00
AFM Exam Model Build - A Step by Step Guide

Course description

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.

Course Content

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

Building a Model Template & Print Settings

Structuring your spreadsheet Template or model correctly helps you work efficiently and saves you a lot of editing time down the road. Printing may not be an apparent skill to have in our paperless age, but don't forget that we can print to PDF and we do quite a lot. So wouldn't it be great not to have to spend the whole day re-editing your spreadsheet to get it ready to print?

This section covers the following:

  • A Standard Structure for Modelling
  • How to setup your spreadsheet for printing
  • How to include automated headers and footers
  • Page setup shortcuts

Working with deliberate Circularities in your Models

  • Understanding circularities
  • Dealing with circularities with the Iteration switch
  • Dealing with circularities with a Macro
  • Understanding the risks inherent in each methodology

Please remember to take advantage of the discussion board on the top left of this page to ask the trainer any question.

Enjoy!

Read More

Instructor

David Brown
David Brown
FCA, Microsoft MVP

David Brown is an ATD Master Trainer and Master Instructional Designer. He is also a Microsoft MVP, an International Consultant to the World Bank, a Chartered Accountant and the founder and Managing Partner of dbrownconsulting, a Lagos-based consulting and Analyst training firm. David is also a regular speaker at International conferences and events.

David and his team at dbrownconsulting develop self-service reporting tools to automate the tedious process of cross reporting on LMS Data, HR & Training Data and Other Data silos in organisations, providing great insights for management with minutes without the need for new software.

He is also a passionate facilitator, he has trained thousands in technical courses such as Financial Modelling, Business Intelligence and Analytics with Power BI, Finance for non-Financial Managers and much more. He develops unique methodologies, frameworks and job aids to simplify tedious tasks at work as well as make the facilitation and learning of complex topics fun and sticky.

He was awarded the Microsoft MVP award in 2018 for his tireless support of the technical community where he hosts monthly online meetups in Excel & Power BI and Financial Modeling, Live events and thought leadership in Excel, Power BI and Data Analytics platforms. He also hosts a monthly webinar on Talent Development.

He leads a team of trainers/instructional designers in developing, designing and implementing blended learning courses for Analyst, his work can be viewed on www.OfficeTrainingHub.com and his firm's corporate website www.dbrownconsulting.net.

Contact him on LinkedIn on https://www.linkedin.com/in/davidbrownprofile/


Reviews (7)

Great course

by Vincent Chukujekwe
I am totally pleased and excited about the skills I just learnt from this course.

very good course

by Ezeani Patricia Oluchi
very good course! very useful!

Straight to the Point!

by Funmi Itelu
Great Job! The Courses are Straight to the Point and not difficult to understand.
Read more

Great course

by Vincent Chukujekwe
I am totally pleased and excited about the skills I just learnt from this course.

very good course

by Ezeani Patricia Oluchi
very good course! very useful!

Straight to the Point!

by Funmi Itelu
Great Job! The Courses are Straight to the Point and not difficult to understand.

Mastering Lookup Function

by Bello Chief
this is segment is a bit difficult and confusing, especially, the INDEX, OFFSET functions

Excel Fundamentals

by Halidu Haruna
the average formula which i am familiar with is =Average it can be any row or column that contain viable you want to find the average let's say =averageV4:J8 but i tried it is not working fine but i enjoy your instructors video you are the best.

The course content appears more comprehensive and detailed than the course title implied

by Abidemi Emmanuel
Course text is detailed and much more than what I thought the training will deliver. This is coming from a foundation student cum participant view of this training

by SILAS HARUNA