Learn Financial Modeling from Scratch - Part1

Confidently & quickly create your own financial models. Quickly check even complicated models prepared by others!
4.3 (2 ratings)
Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
4 students enrolled
$50
Take This Course
  • Lectures 29
  • Length 3.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 4/2016 English

Course Description

About the Course:

Financial Modeling requires great skills in Practical
Finance, MS-Excel and Business / Industry analysis. This course is
one-step-solution which precisely covers all these areas and trains you
to build spreadsheet based projections from a blank worksheet.

So, if you are an entrepreneur preparing a business plan
for your potential investors or just a graduate dreaming to work as an
analyst at an Investment Bank, this is the program for you!

This entire Curriculum is divided into 3 different Courses

Learn Financial Modeling from Scratch - Part 1 - Current Course

Learn Financial Modeling from Scratch - Part 2

Learn Financial Modeling from Scratch - Part 3

This Course is Part - 1 of the three part series of the entire Course.

Training Materials:

Learning materials include

  1. Financial Modeling Case Study,
  2. Value-added Session Videos,
  3. Financial Model versions at each section-end,
  4. In-video prompters on Best Practices,
  5. Quick tips on Finance and MS Excel,
  6. Guide on Keyboard Short-cuts for better efficiency,
  7. Handbook on Basics of Excel and Basics of Finance (Coming Soon!)

Course Facilitator (Faculty):

The course is facilitated by Ashutosh Zawar, who is a
rank-holder Chartered Accountant and a Bachelor of Commerce from
University of Pune, India. He is a Co-Founder & Lead Trainer @
Academy of Financial Training. He has over 9 years of experience across
Investment Banking, Project Finance, Consulting and Training.

Ashutosh has over 1,000 hours of professional training experience.
He looks after the e-learning, multimedia content management at Academy
of Financial Training (AFT). He has been instrumental in launching
AFT's new training programs and innovating curriculum across topics such
as Advanced Financial Modeling, Excel, Banking, Corporate Finance.

Before co-founding AFT, he worked with the Project Finance
team at IDFC, a leading financial services company in the Infrastructure
domain and the Investment Banking team at Morgan Stanley. he has worked
on sectors such as healthcare, education, tourism, telecom and energy.
His work profile included operational and financial modeling, business
appraisal, valuation analysis, drafting of terms and commercial
negotiations.

Duration:

The entire 3 part course takes about 8-10 hours of video-based learning and 20-30 hours of simultaneous self-practice on MS Excel.

Structure of the Course:

  1. The course guides candidates to start with a blank Excel spreadsheet and goes on to build full-fledged forecasts of Financial statements i.e. Balance Sheet, Income Statement and Cash flow Statement
  2. A real-life case-study of a manufacturing company has been considered for this program
  3. It also includes important calculation schedules like Capital Expenditure, Depreciation, Working Capital, Loan or Debt, Interest, Income Tax, Cash balancing mechanism.

Why take this Course?

  1. Hundreds of classroom editions of the program already executed and ongoing for leading banks, manufacturing companies and b-schools.
  2. Course content designed, recorded and edited by professionals with rich work experience in Investment Banking, Equity Research, Project Finance and Business Consulting domains at world-class banks.
  3. Focus on simplicity, accuracy and efficiency (Building quickly)
  4. Best practices and common errors to avoid in Financial Modeling covered
  5. Live support from expert trainers during and after the programs


What are the requirements?

  • Basic knowledge of MS Excel
  • Basic knowledge of Finance

What am I going to get from this course?

  • By the end of the three part program, you will be able build a financial model from a blank Excel worksheet.
  • This includes analysing historical financial statements, noting important trends, preparing specialised calculation schedules, forecasting the financial statements

What is the target audience?

  • Analysts in Investment Banking, Equity research, Institutional Equities, Private Equity
  • Professionals aspiring to be analysts in Investment Banking, Equity Research, Private Equity
  • Corporate Finance executives
  • Financial Analysts
  • Entrepreneurs and Businessmen

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction to Financial Modeling
05:46

Greetings from Academy of Financial Training (AFT)!!!.

Thank you for choosing AFT for learning Financial Modeling.

Our first video gives you a brief introduction to AFT's 'Learn Financial Modeling From Scratch' Program. Importantly,
it guides you to important tools & features of the program that you
can use to get a seamless learning experience of the Program.

It includes,

  1. Guide on appropriate screen resolution settings for optimum view
  2. Tools such as Table of Contents, Lecture Description, Notes for review, etc.
  3. Video Support Features like Keyboard Shortcuts Prompters at Top of the Screen, Pop-ups for Important in Concepts in Business Finance, Best Practices in Financial Modeling and Most Efficient way of using MS-Excel on Right hand side of the Screen
  4. Collaterals / Supplementary Materials for Downloads - like Important / Frequently used Keyboard Shortcuts (PDF) file, Financial Model (MS-Excel) file of the Instructor after each Section for your review and Financial Model Case Background (PDF) file

Wish you All the best & Happy Modeling!!!!

Team AFT

02:37

This video gives you the overview & explanation on the how the
final output of the Program, i.e. a completed financial model will look
like.

It helps you note

(a) the different worksheets of the completed model,

(b) colour coding of worksheets to highlight / differentiate the varied
sections of the final model... i.e. (i) Summary & Inputs to the
model, (ii) Special Calculations for the model (iii) Output of the model
in the form of Financial Statements, and

(c) brief explanation on the contents and purpose of each of the sections.

06:49

This session gives an overview of the entire program and describes what
is a financial model. It explains what could be the varied business
objectives for which spreadsheet-based financial models are used.
Various business decisions could be made based on the analysis in a
Financial model.

We also understand the requisite skills and knowledge that are
important for building a robust financial model. We answer questions
like: Who could be the potential users of the financial models? What
are the key attributes or characteristics a financial model should
possess for it to be accurate and robust while being effective at the
same time?

06:17

In this session, we conclude the introduction part by understanding
the key steps to building a financial model, classified as follows:

  1. Steps to be considered BEFORE building a model: like planning the objective, defining the output and related inputs (based on availability of information), and possible scenarios to be built. We also briefly look at steps to be considered AFTER building the model.
  2. Steps to be considered WHILE building a model: like template of the worksheets, input for historical financials and calculation of its trends, building assumptions for revenue & cost, asset & liability projections, special calculations on depreciation, tax, loan schedules, etc. and finally balancing the model and debugging.
Introduction to Financial Modeling
5 questions
Section 2: Keyboard Shortcuts
Easy Navigation in Excel
07:20
Editing based Keyboard Shortcuts
09:33
ALT based Keyboard shortcuts
04:44
Quick Formating using Keyboard Shortcuts
06:17
Formatting Dates in MS Excel
02:06
Tracing & Auditing in Excel
10:28
Section 3: Best Practices in Financial Modeling
10:12

This session elaborates on the next three attributes – ‘ACCURACY’,
‘ROBUSTNESS’ & ‘FLEXIBILITY’ for building a financial model.

Knowing the importance of constant debugging, where to use ‘Data
Validation’ MS-Excel Tool, dealing with Circular Calculations in the
model, and data protection, building flexibility by using ‘CHOOSE’
function in MS-Excel.

With this we conclude the key attributes / characteristics of a Robust Financial Model.

07:29

This session introduces the best practices in building a financial
model. It elaborates on the first attribute – “SIMPLICITY” necessary for
building an IDEAL financial model. We understand the importance of
keeping the model as simple as possible depending on the end-use, time
& information available to build the model. Keep formulae simple and
short as much as possible so as to avoid complexity and confusion for
the user / reader. Avoiding complicated “NESTED” formulae and functions,
unnecessary or extra detailing of calculations and information
presentation.

06:11

This session elaborates on the next three attributes – ‘ACCURACY’,
‘ROBUSTNESS’ & ‘FLEXIBILITY’ for building a financial model.

Knowing the importance of constant debugging, where to use ‘Data
Validation’ MS-Excel Tool, dealing with Circular Calculations in the
model, and data protection, building flexibility by using ‘CHOOSE’
function in MS-Excel.

With this we conclude the key attributes / characteristics of a Robust Financial Model

07:23
    In this session, we focus on the DO’s and DON’T’s for building a financial model DO’s: - Using keyboard shortcuts for improving efficiency - Use of template for consistency in presentation format, data alignment, etc. - International best practices for representing inflows and outflows and other calculations DON’T’s: - Manual inputs in formulas or functions - Hiding rows / columns - Duplicating calcs for same item in different sheets - Overusing circular calculations and counter-flows
08:04

In this session, we conclude the best practices for financial modeling.

We look at the list of COMMON ERRORS to be avoided while building the
model… including linking errors, absolute / mixed referencing errors,
circularities, etc.

We look at the list of CONCEPTUAL or FUNDAMENTAL ERRORS… such as
‘Negative Cash’ Balance, ‘Gross’ vs. ‘Net’ calculations, ‘Accumulated
Depreciation’ exceeding the ‘Original Cost’ of Fixed Assets, etc.

Quiz on Best Practices in Financial Modeling
8 questions
Section 4: Walking Through the Case-Study
05:57


In this session, we walk through the case and also understand the
type of information that needs to be analyzed when preparing the
financial model of a company. This includes the Financial Information (3 financial statements with notes and schedules) and Non-financial Information (E.g.
Information about business model, Industry information, management
strategy, etc.). We walk through the case to understand both these
sections in detail. In this one, we focus on the Financials section.

We note an important convention followed in statutory filings – When
providing annual figures, the earlier historical year is presented on
the right-hand side, while recent-most historical year is on the left. –
We reverse this order when preparing the financial models i.e. We move
from left to right in the order of chronology.

Please note that our Case-study is a compilation of data from
real-life reports and filings. It is adapted further for using in a
classroom situation. In a real-life modeling situation, you may have to
wade through various reports and compile relevant important information.

09:18

In this session, we walk through the Non-Financial information section
of the Case-study on Time Machine Watch Co. Inc. We walk through various
sub-sections such as Business Description, Commentary on Financials for
last few years, About the industry including the growth rates,
Management outlook, Trading statistics (Stock price info) etc.

The session goes on to explain how the information differs for a listed
company vs. an unlisted company. Also, the difference between organized
and unorganized sectors, which is a common feature of industries in
emerging economies.

We also walk through the sub-section on Capital Expenditure and
Funding. We discuss the ways of funding a completely new (greenfield)
project and an existing project. We also explain, what is equity
infusion, and how retained earnings (historical profits retained in the
business) form part of the funding sources for existing projects.

Finally, we speak about the Deliverables (Output). It
is important to understand and define the outputs. This may allow us to
save a lot of time and cut-down and excessive details.

Walking Through the Case-Study
5 questions
Section 5: Creating Template for the Financial Model
10:37

In this session, we start with building a ‘STANDARD TEMPLATE’ worksheet
for our financial model which shall be used when we want to insert a
new worksheet for our model.

Key tasks that we learn in this session are:

    ·View Management: (i) Removing ‘Gridlines’, (ii) Adjusting column widths in a way that is appropriate for information alignment purposes for presenting the text and numbers of the financial statements and calculations, (iii) Hiding the unwanted columns (to the right) and rows (to the bottom) to increase efficiency in navigation and working with information. ·Presentation Best Practice: Colour Coding (i) Virtual Page Borders (ii) Title Rows (iii) Appropriate Columns: For better comprehension and separating historical years from projection years ·Plugging in the Company Name, Historical & Projection Years by use of formulae & appropriate ‘Date’ Formatting, etc.

This completes our exercise of creating a Re-Usable Template for the purpose of our Model

04:17

In this session, we understand the concept of ‘DASHBOARD’ to start
with. Further, we learn HOW to create a Dashboard and understand WHY its
format is different than TEMPLATE, which we had created in our previous
session.

A ‘Dashboard’ is used for plugging in ‘ONE TIME’ Assumptions for the model, like:

  1. General Inputs: Company Name, Currency for the Model, Denomination used (for financial figures), Last Balance Sheet Date for Historical financials, etc.
  2. Other Group of Inputs & Assumptions related to Calculations for Revenue, Costs, Capex, Depreciation, Loan, Tax, etc.

Dashboard shall also define the level of flexibility that you want
the user to have when changing various inputs and assumptions.

Creating Template for the Financial Model
5 questions
Section 6: Entering (Input) Historical Financials in the Model
10:56

In this session, we create an analysis-friendly layout of Assets
side of the historical Balance Sheet in our Financial Model. For this
source the information from PDF Case study and paste it in our model (MS
Excel File).

Copying from PDF: Modeling often deal with sourcing
data from various files and analyze them in Excel. Annual reports are
typically available as PDF files. Converting columnar information (e.g.
Financial statements) from PDF into a more analysis-friendly format in
Excel could be tricky, as there is no direct way of separating the
information in various columns. There are various options

  • - Copying the selected information as a Table (available only subscribed versions of Adobe Acrobat)
  • - Copying the entire information and use Text-to-columns tool in Excel (Extremely time-consuming)
  • - Intelligent copying of information (copy-pasting one-column at a time)

Excel Tips & Best Practices: We deal with all the
errors commonly associated with data import in Excel. We format the
Asset side properly. We follow the best practices such as maintaining
separate font-colour for inputs and outputs. We use SUBTOTAL function to
calculates totals of various sub-categories and categories.

10:13

In this session, we create an analysis-friendly layout of Liabilities
& Equity side of the historical Balance Sheet in our Financial
Model. For this source the information from PDF Case study and paste it
in our model (MS Excel File).

We use the Intelligent copying of information (copy-pasting
one-column at a time) alternative this time. This can be preferred over
using Text-to-column, as the latter is inefficient and extremely time
consuming in case of Financial information.

Excel Tips & Best Practices: We deal with all the
errors commonly associated with data import in Excel. We format the
Liabilities & Equity side properly. We follow the best practices
such as maintaining separate font-colour for inputs and outputs. We use
SUBTOTAL function to calculates totals of various sub-categories and
categories. We also highlight the use of TAB key to auto-complete
function names and defined cell-names in MS-Excel.

06:41

The filed Income Statement is simplistic. When analyzing the historical
Income Statement for our model, more layers are required to be added
e.g. EBITDA, EBIT etc. In this session, we create an analysis-friendly
layout of the historical Income Statement in our Financial Model. For
this source the information from PDF Case study and paste it in our
model (MS Excel File).

We also introduce and explain the calculation of EBITDA, EBIT in
this session.We use the Intelligent copying of information (copy-pasting
one-column at a time) alternative this time. This can be preferred over
using Text-to-column, as the latter is inefficient and extremely time
consuming in case of Financial information.

Excel Tips & Best Practices: We
deal with all the errors commonly associated with data import in Excel.
We format the Income Statement properly. We follow the best practices
such as maintaining separate font-colour for inputs and outputs. We use
SUBTOTAL function to calculates totals of various sub-categories and
categories.

05:56

In this session, we browse through the Notes & Schedules section
in the Case-study and add more details to our historical Balance Sheet
and Income Statement in our model.

Notes to Accounts and Schedules are important part of financial
statements. They must be read and analyzed thoroughly to add details to
the financial model. In the model, we are now ready with detailed
versions of Income Statement and Balance Sheet which appear different
compared to the filed versions.

We also define and complete the calculations such as EBIT, PBT, PAT and related margins.

Excel Tips & Best Practices: We follow the best
practices such as maintaining separate font-colour for inputs and
outputs. We use the Absolute referencing ($ signs) intelligently to
create one formula for margins, that can be used for various other
margin calculations. We also review the advantages of using the SUBTOTAL
function to calculates totals of various sub-categories and why should
it be preferred over SUM in such a situation.

Entering (Input) Historical Financials in the Model
4 questions
Section 7: Calculating & Analysing Historical Trends
02:17

This session introduces 3 situations that you will encounter when
building Financial Models / Projections. The session guides about
approaches to be followed in each of the situations.

1. Projections for an existing entity with operating history

2. Projections for a new project or start-up with no operating history.
Though, sometimes, such projects can be benchmarked against other
similar projects or ventures

3. Projections for a new business model. These are pioneering ventures with no direct competition.

05:04

In this session, we create a sheet for analysis of historical trends
(from the template). The trends primarily include those related to
Income Statement and Working Capital.

In real-life financial modeling, the availability of information is very different in the following two situations:

  1. Where you are working closely with the management (as their advisor) to prepare business plan-cum-financial model for a company. In this case, trend analysis and assumptions based on the same could be extremely detailed.
  2. However, in a situation where you are working independently, based on publicly available information, the trends analysis could be restricted due to constraints in information availability. Many assumptions may have to be made.

After answering the 'WHY' question in our last session, we explain
which trends and ratios are to be included for our analysis. The Income
Statement related trends include several expense line items as a
percentage of revenues or sales. We try to find-out if these line-items
can be categorized as fixed, variable or semi-variable.

05:44

In this session, we calculate various Income Statement Trends from the historical financial statements, mostly the Expense Trends.
We understand the difference between Gross and Net Revenue. We
understand why expenses and margins are considered as a % of Net Revenue
(and not Gross Revenue).

We calculate historical revenue growth, understand if there are any
outlying values. We also calculate historical trends in VAT, Material
consumed, Manufacturing expenses, SG&A, Royalty, Other expenses.

We stress on two financial modeling best practices here:

  1. Always segregate the input cells from calculations and output. You can use separate colour-coding.
  2. Use Absolute Referencing ($ signs) intelligently to create ONE formula that can be easily reused in adjacent cells (rows and columns) across a range.
04:37

In this session, we classify each expense (cost) line item into Fixed costs, Variable costs and / or Semi-variable cost.

We understand that some of the line items could be purely variable,
some could be semi-variable. We evaluate if Manufacturing expenses,
SG&A, Other Operating expenses could be semi-variable. In such a
situation, we analyse if the year-on-year (y-o-y) growth in these
expenses demonstrates any clear & visible trends.

10:03

In this session, we calculate historical working capital trends /
ratios such as Inventory Holding (days), Receivables Credit Period
(days), Payables Credit Period (days), Provisions as a % of Expenses
excl. Materials.

We also understand various elements of these calculations
(specifically denominators) such as Cost of Goods Sold, Gross Sales,
Materials Consumed, Expenses excluding Materials etc.

We make relevant modifications in the dashboard. Judicious use of Cell
Naming is recommended here. We also use Custom Cell formatting for
display of some of these trends effectively. For all of the above
calculations, we discuss as to which of the following methods to be used
for items like inventory, receivables, payables etc.

  1. Closing inventory
  2. Average of opening or closing annual inventory
  3. Average of opening or closing monthly inventory

This decision is primarily driven by availability of information and level of precision required.

03:44

In this session, we calculate the Fixed Assets and Capital Expenditure (Capex) related trends.

We calculate the historical Fixed Assets Turnover Ratio, Capex as a %
of Net Revenues ratio. The latter could be used to forecast the Capex in
situations where capex-specific information availability is
constrained. We use Custom Cell Formatting feature in Excel display the
calculated ratios in a specific manner.

In this session, we also learn how to approximate the historical capex in case it has not been specifically provided.

After this session, we go on to define various assumptions and constants as a part of our dashboard. The
historical trends calculated in this entire Section, along with the
dashboard assumptions, are important drivers of various calculations in
our Financial Model.

Calculating & Analysing Ratios
15 questions

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Academy of Financial Training AFT -, Premium Institute focused on Advanced Finance Programs

Academy of Financial Training is engaged in providing niche financial trainings to companies, educational bodies and individuals. We have been in operation since ~4 years and have trained 5,000+ candidates and have a range of marquee MNC/corporate clients including companies like GE, Hewlett-Packard, Genpact, Canon, Deutsche Bank, IL&FS, Essar Steel, amongst others. Our strength has been the robust financial training content based on years of research backed by effective delivery provided by trainers with rich work experience in the domain.

Ready to start learning?
Take This Course