Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Financial Modelling in MS Excel

Name: Financial Modelling in MS Excel
Rating: 4.4 (47 reviews)

Prepare How to Prepare Financial Models in MS Excel - Beginners to Advanced

Created bySyed Muhammad Ali Shah

Last updated 2/2021

English

What you'll learn

This course is designed for accounting and finance professionals who are keen to learn Financial Modelling in MS Excel.
Learn advanced excel techniques needed by accounting and finance professionals.
Learn how to automate your profit and loss statement
Learn how to automate statement of financial position
Prepare Discounted Cash Flow Models (DCF) using MS Excel
Learn to calculate the project NPV or IRR using financial models.
Business Valuation and Finacial Models
Learn to prepare the forecast profit and loss, forecast statement of financial position and forecast cash flow
Case stidies on prepraing financial models for Profit and Loss Account, Balance Sheet and DCF

Course content

5 sections • 47 lectures • 13h 44m total length

Introduction14:17
Introduction to Excel Ribbon and Menu23:06
Explore the Excel ribbon and menus, including home, insert, and page layout; master formulas, macros, and formatting, and navigate across sheets efficiently.
Revision of Basics - Copy Paste, Cut Paste and Linking the Sheets15:16
Data Sorting and Sub Total Function in Excel10:26
What If Analysis and Conditional Formatting21:59
Locking Cell References in Formulas8:50
Fast scrolling and use of F5 function key5:17
Master fast navigation in financial modelling using double-click to jump to source cells and F5 to return, with linked sheets and unchecked allow editing directly for smooth scrolling.
Using Macros for formatting your work sheet10:45
Tables and Data Filters6:44
Dynamic Naming in Excel6:33
IF and AND Function15:49
Learn to use the if function in Excel to classify pass/fail outcomes and absent students, then tally results with countif, and apply and logic for scholarships.
Cell References5:17
Pivot Tables and Excel11:56
Transform messy sales data into a clear summary by using pivot tables, dragging years, months, and shops into rows, columns, and values. Apply filters and slicers for interactive dashboards.
Formatting tables professionally7:45
Format data as a table in Excel to enable dynamic filtering, sorting, and visuals, then use table totals for counts, minimum, maximum, and averages while expanding as you add data.
D Sum and D Average Function9:45
Finding and removing errors in your sheet3:19
Disable Excel's automatic error checking to stop green indicators from displaying when prices change from 40 to 55, by going to file options, formulas, and unchecking the default setting.
VLook up Function in Excel14:19

Introduction to Forecast Profit and Loss Statement9:33
Learn to build flexible, reusable financial models in Excel with consistent columns, built-in checks, and scenario-based forecasts from historical data for 2020–2022.
Forecasting Revenue and Cost of Sales32:35
Forecasting Fixed and Variable Costs8:43
Structuring the Profit and Loss Statement25:53
Completing the Profit and Loss Statement27:23
Learn to complete a profit and loss statement in Excel by forecasting revenue, depreciation and amortization, taxes, and interest across best, most likely, and worst cases.
Structure of Statement of Financial Position - The Balance Sheet7:02
Assumptions for Statement of Financial Position40:37
Introduction to Cash Flow Statement17:18
Forecast Cash Flow Statement18:52
Completing the Statement of Financial Position16:32
Completing the Statement of Cash Flow15:26
Cash Flow Based Valuation29:10

Introduction to DCF based Financial Model27:44
Fixed Assets and Depreciation Schedule20:32
Depreciation and Tax Benefit15:05
Explore how depreciation and interest create tax benefits that affect cash flows, with practical examples showing two methods to present tax benefits in financial modelling.
Calculating the Operational Cash Flows27:36
Common Issues with Operational Assumptions17:43
Learn to validate operational assumptions by breaking down fixed costs, separating depreciation from production costs, and building a realistic manning plan for 24/7 operations, with questions that reveal hidden details.
Weighted Average Cost of Capital and Beta46:47
Learn levered vs unlevered cash flow in dcf modeling and how wacc, tax shields, and project-specific beta determine cost of capital using capm for equity and gearing concepts.
Calculating Project Beta, Cost of Equity and WACC18:17
Getting the Final Output of DCF Model23:28
Calculating the Internal Rate of Return - IRR13:34
Sensitivity Analysis of the Project24:00
Conduct sensitivity analysis to see how NPV changes with selling price, volume, and costs. Use present value and discount factors to assess feasibility and the impact of initial investment.

Requirements

Basic knowledge of MS Excel and Basics of Accounting and Finance

Description

Overview

Financial modelling is an essential skill for accounting and finance rofessionals. It is very much in demand in the job market and is highly valued by employers.

Our financial modelling training takes you from basics to professional level. This sixteen-hour training is based on practical exercises

The course focuses 40% on honing the participants MS Excel skills and 60% on application of MS Excel in Accounting and Finance

What You Will Learn

1. Learn many of MS Excel's advanced features

2. Become proficient user of Excel within your team

3. Carry out regular tasks faster than ever before

4. Build Profit and Loss, Statement of Financial Position and Cash Flow statements

5. Build valuation models from scratch

6. Build Net Present Value model from scratch

7. Learn how to make neat and professional-looking charts and graphs

Detailed Content

1. Introduction to Excel

2. Useful tips and tools for your work in Excel

3. Keyboard shortcuts in Excel

4. Excel's key functions and functionalities made easy

5. Update! SUMIFS – Exercise

6. Financial functions in Excel

7. Microsoft Excel's Pivot Tables

8. Case study: Building a complete P&L in Excel

9. Introduction to Excel charts

10. Profit and Loss Case Study continued—with great-looking professional charts

11. Financial modeling fundamentals

12. Introduction to Company Valuation and Introduction to Mergers & Acquisitions

13. Learn how to build a Discounted Cash Flow model in Excel

14. Business Valuation: Complete practical exercise

15. Capital Budgeting: The theory

16. Capital Budgeting: A Complete Case study

17. Impact of interest rates and exchange rates on NPV

18. Sensitivity Analysis in Capital Budgeting

Pre-Requisites

1. We expect participants to have a basic understanding of MS Excel. One could measure this by considering someone who has been using MS Excel for more than a year.

2. Basic knowledge of financial accounting.

3. Microsoft Office 2013 or later installed on your computer.

About the Instructor

A qualified accounting and finance professional with over twenty years of extensive experience in diversified industry sectors such as auditing, large scale manufacturing and oil and gas.

Like most accounting and finance professionals, I started my career as finance executive and then over the years rose to the position of CFO in a multinational company in oil and gas industry.

I have also worked as a consultant with the World Bank and European Union on different projects in Middle East, Eastern Europe and CIS countries during 2011 to 2018 as a principal consultant for IFRS and Financial Management.

I am qualified professional with three professional qualifications MBA, ACCA and CIMA UK. I have been teaching IFRS, Financial Reporting, Financial Management and Performance Management for over fifteen years and my focus areas are ACCA and CIMA qualifications.

Who this course is for:

Accounting and Finance professionals as well as accounting and finance students.

Financial Modelling in MS Excel

What you'll learn

Explore related topics

Course content

Introduction17 lectures • 3hr 11min

Capital Budgeting Introduction4 lectures • 1hr 5min

Preparing forecast Profit and Loss, Balance Sheet and Statement of Cash Flow12 lectures • 4hr 9min

Capital Budgeting Introduction - Theory4 lectures • 1hr 25min

Your first DCF based Financial Model10 lectures • 3hr 55min

Requirements

Description

Who this course is for: