Learn Financial Modeling from Scratch - Part 2

Confidently & quickly create your own financial models. Quickly check even complicated models prepared by others!
3.5 (1 rating) 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.
2 students enrolled
$50
Take This Course
  • Lectures 27
  • 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 

You are viewing Learn Financial Modeling from Scratch - Part 2

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 this 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.
  • The course also seeks to enhance your skills and speed in working with MS Excel.

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
  • Entrepreneurs and Businessmen
  • Business Analysts

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
03:53

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

Thank you for choosing AFT for learning Financial Modeling.

This Course is a follow up on the Learn Financial Modeling from Scratch - Part 1 Course. Please make sure to check out Part 1 of the Course before you begin with Learn Financial Modeling from Scratch Part 2 , & Part 3 of this Course. Please ignore if already subscribed

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. Tools such as Table of Contents, Lecture Description, Notes for review, etc.
  2. 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
  3. 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

Section 2: Populating the Dashboard
05:38

Continuing from Learn Financial Modeling from Scratch - Part 1 we proceed with 2nd part of this Course

This session populates certain sections of our Financial Modeling
dashboard. These include input of assumptions regarding Trading
Statistics (Stock Market data), Capital Expenditure and Depreciation
from the Case Study (PDF) into our Financial Model - Dashboard sheet (MS
Excel File).

The session introduces the concept of Maintenance or Routine Capex and how it is different from Expansionary Capex.

The session also talks about Financial Modeling Best Practice -
"Maintain separate colour coding for Inputs vis-a-vis Outputs."

06:10

This session populates additional sections of our Financial Modeling
dashboard. These include input of assumptions regarding Loans, Interest
Rates, Income Tax Rate etc from the Case Study (PDF) into our Financial
Model - Dashboard sheet (MS Excel File).

The session introduces the concept of Working Capital Finance Limit
i.e. Why banks fund only a part of the entire Working Capital
requirement.

It also includes entering assumptions related to Company's expansion strategy i.e. Assembly Line in China.

The session adheres to the Financial Modeling Best Practice - "Maintain separate colour coding for Inputs vis-a-vis Outputs."

Section 3: Building up the Revenue Forecasts
10:00

In this session, we analyze Top-Down Historical Trends in global
watches industry, the organized industry and our Company's market share.
We calculate the y-o-y growth that the industry has been witnessing,
the share of organised players as % of overall industry and finally
market share of TWC, Inc.

The session introduces the concept of Organized and Unogranized
industry. This is a critical factor that drives availability of data for
financial projections for companies operating in underdeveloped or
developing economies.

The session has some very useful tips for Quick Modeling i.e. Use of 'Text-to-Columns' and 'Paste Special'

The session also talks about dealing with outlying values when analyzing historical trends.

11:31

In this session, We calculate the forecasts for size of overall,
global watch industry. These are based on historical industry size and
annual growth that it is likely to witness.

The growth calculation considers 3 possible scenarios i.e. Worst Case,
Base Case and Best Case. We have used the CHOOSE function to give
impact to these scenarios. The dashboard is modified accordingly.

Additionally, the session introduces an important concept i.e. Top-Down and Bottom-up ways of forecasting revenues.

Financial Modeling about Use of Cell Naming, maintaining Consistent Formulas across a Row are covered here.

Quick Excel tips such as 'Use of Ampersand (&) to create Dynamic
Text Descriptions', 'Use of Data validation with CHOOSE function' are
also included here.

05:06

In this session, we build the forecasts for organised industry size.
This is based on the share of organised players as a % overall industry.

The % share of organised players considers 3 possible scenarios i.e.
Worst Case, Base Case, Best Case. We have used the CHOOSE function to
give impact to these scenarios. Relevant Index_Values and inputs are
included in Dashboard

Quick tips such as limiting the number of Switches (choices) in the model is introduced here.

05:05

In this session, we complete the Final Step i.e. The Company's Revenue
forecasts based on its market share. The market share is calculated
historically as a % of organised industry. The forecasts for market
share are made keeping in view the targeted market share over the next
decade.

Considering 3 possible scenarios, we forecast that the Company will
either lose, maintain or gain the market share over the next decade. We
have used the CHOOSE function to give effect to these scenarios.
Relevant Index Values (Driver Cells indicating the choice) and inputs
are included in the Dashboard

The session offers important tip about treatment of outlying and extreme one-off values when analyzing historical trends.

Special Focus - The CHOOSE Function in Excel
Preview
06:47
Populating Dashboard & Building Revenue Forecasts
9 questions
Section 4: Forecasting various elements of the Income Statement
02:16

In this Session, we create separate sheets for forecasting the Income
Statement and Balance Sheet. We avoid using the Historicals sheet to
ensure a uni-directional flow of calculations. i.e. Inputs &
Historicals >>>> Trends and Calculations >>>
Outputs i.e. Forecasted Financial Statements

That's why, for historical financial statements, we create new links from historical sheet to the P&L and BS sheet.

We adhere to the Financial Modeling Best Practice - "For calculations,
maintain a uni-directional flow throughout the Financial Model"

12:26

In this session, we forecast the trends and ratios in the Income
Statement, Working Capital and Fixed Assets. We had calculated these
ratios and trends for historical period earlier.

We define 3 important approaches to forecasting the trends. The forecasted trends will be equal to

  1. Average of trends witnessed in the 3 historical years (Fixed Average)
  2. Trend witnessed in the last historical year
  3. Average of trends witnessed in 3 recent-most years (Moving Average)

We use the CHOOSE function along with Data Validation for this. We
create flexibility in the model so that the user can forecasts trends
for various line items using the most appropriate approach for that
item.

The session has quick time-saving tip about the intelligent use of
Absolute referencing ($ signs) to create one formula that could be used
many times in adjacent cells.

03:52

In this session, we understand the rationale for assuming certain
Expense Trends as Variable and not Fixed. These expenses include
Manufacturing Expenses, Selling General & Admin Expenses, Other
operating expenses.

When calculating historical trends, we had calculated the trends for
these line items in 2 ways. Assuming that they are either variable or
fixed.

Going forward, we assume them to be Variable Costs, because if we
assume them to be Fixed costs, the data is available only for 2 years.
Further, there are no visible trends during this short period.

08:48

In this session, we complete the forecasts for all line items in the
Income Statement till EBITDA based on forecasted trends. i.e. We
calculate the Gross Revenue and all operating expenses.

At this stage, in the Income Statement, only items which are pending
to be forecasted include Depreciation, Interest and Income Tax in our
subsequent sessions. We'll create specialized calculations for these in
our subsequent sessions.

We introduce some quick ways to build formulas and keeping them
shorter. Especially, focus on the use of F5 key (Go To function in MS
Excel) to evaluate and edit the formulas.

We also complete calculations part of the forecasted Income Statement,
by simply copying over the formulas from historical Income Statement.
(Here, the Calculation parts include formulas for calculating various
milestones such as EBIT, PBT and PAT)

Forecasting elements of Income Statement
5 questions
Section 5: Forecasting various elements of the Balance Sheet
03:16

In this session, we divide the Balance Sheet forecasts in two parts.

  1. Those tobe forecasted usingthe Historical Trends
  2. Those to be forecasted using spcialized calculations
We also complete calculations part of the forecasted Balance Sheet, by

simply copying over the formulas from historical Balance Sheet. (Here,
the Calculation parts include subtotals and grand totals of various
assets and liability groups)

04:50

In this session, we complete the forecasts for Working Capital items
based on Forecasted Trend Calculations. These items include

  1. Current Assets: Inventory, Account Receivables, Loans & Advances.
  2. Current Liabilities: Account Payables and Provisions

A quick tip about auto-completing the formulas by selecting from a list of suggested function names or defined names.

Balance Sheet Related
5 questions
Section 6: Specialized Calculations - Capital Expenditure & Depreciation
03:33

This Session discusses the flow of calculations for forecasting
depreciation. Over the next few sessions we'll forecast depreciation
using two methods 1. Written Down Value (WDV) Method 2. Straight Line
Method (SLM). We'll also calculate depreciation for books and Income Tax
separately.

For calculation of depreciation, forecasting the capital expenditure
(Capex) is necessary. Capex is of two types: 1. Expansionary Capex 2.
Routine or Maintenance Capex.

When forecasting the Expansionary Capex, especially construction of
assets which take long time, the capitalization of interest costs has to
be considered.

  • The session talks about creating a switch using the CHOOSE function. This is to give user a choice between the two methods of depreciation i.e. WDV or SLM.
09:34

In this session, we forecast the maintenance and expansionary capex.

We make the relevant modifications in the dashboard. This also includes
naming the cells that contain capex related assumptions.

The session discusses advantages and disadvantages of cell naming. We
highlight a faster way of naming cells by copying cell names from the
Left column. The session also discusses the practice of creating shorter
names and hence, shorter formulas.

Financial Modeling Best Practice: "Use cell-names judiciously. Overuse of names may lead to the model losing its flexibility."

11:18

In this session, we forecast the depreciation calculated using the WDV
method. The prescribed rates of depreciation are different for Income
Tax and Companies Act. Hence the Book Depreciation calculation has to be
separated from a Tax Depreciation calculation.

We prepare detailed calculations schedules including Gross Asset Value
and Accumulated Depreciation based on which Depreciation for the year is
calculated.

The session uses F5 Key (Go to) heavily to check and change cell
references. It also discusses the process of editing and changing
predefined cell names.

Financial Modeling Best Practices adhered to:"Maintain unidirectional
flow of calculations" "Highlight calculations that are inconsistent
compared to other columns across the row"

11:03

Depreciation using the Straight Line Method (SLM) can be tricky when
new additions to fixed assets are made every year. The calculation is
simpler when you have one-time asset additions in your projections
period.

Here we create a simpler step-by-step solution to calculate SLM
depreciation in a situation where asset additions are happening every
year. Further, we also need to create a built-in Error Check in our
formula, that stops the total accumulated depreciation from exceeding
total cost of assets.

Our objective is to create one formula that can be used across multiple
rows and column and doing away with the need to change the references
as we go to a new row, with similar calculation. We focus on building
such formula in the next session.

11:37

In this session, we create a complete SLM Depreciation schedule, which
has one flexible formula that addresses all possible Error-Checks.

1. The Total Accumulated Depreciation should not exceed the Cost of Assets
2 The depreciation should be provided only from that year, in which asset has been added, not before.

To create a single, flexible formula that can be universally used in
our schedule, we have to convert the horizontal capex line into a
vertical one. We use the TRANSPOSE function for this purpose.

TRANSPOSE is an array function. Arrays provide shorter alternatives to
long formulas.The session also discusses how to input and recognize
array calculations in Excel.

The session also talks about use of MIN and MAX as alternative to IF or NESTED IF calculations to create simpler formulas.

10:37

In this session, we create a Single-row Alternative Formula for
calculating SLM Depreciation. This is a much simpler formula when
compared to the detailed schedule with 10-12 rows that we created
earlier using TRANSPOSE.

We use the OFFSET function along with SUM for the same. The OFFSET
function, when used intelligently, allows us tremendous flexibility to
select any range with adjacent cells in a worksheet. Further operations
can then be performed on such selected range.

05:48

In this session, we complete the first major specialized calculation
i.e. Depreciation (For Books). We create a choice for the user to select
either WDV or SLM as the method for providing for Depreciation
annually. We use the CHOOSE function for the same.

Relevant modifications are made in the Dashboard

We link the Annual Depreciation forecasts into the Income Statement. We
also link the Gross Asset Values, Accumulated Depreciation forecasts
into the Balance Sheet - Asset Side.

Specialized Calculations - Capital Expenditure & Depreciation
14 questions
Section 7: Specialized Calculations - Term Loan & Working Capital Finance
12:54

In this session, we create another Specialized calculation i.e. the
Term Loan Schedule. It includes forecasting the Term Loan disbursement,
repayment, closing balances and also the related Interest Expense.

We modify our dashboard to include the relevant assumptions and name
these cells later. The session goes on to explain Term Loan-specific
terms like Tenure, Moratorium period, Repayment period.

The amount of Term Loan is based on the funding percentage viz-a-viz the capex for assembly line, as agreed with bankers.

The repayment amount has to be forecasted based on repayment period and
type of repayment. We have to build-in a check that total repayment
doesn't exceed the Loan amount. This formula is similar to the SLM
Depreciation formula that we have created earlier

Finally, we calculate the Interest Expense on the Term Loan.

The session has some Quick Excel Tips about editing cell names etc. It
also adheres to 'Simplicity' where calculations are broken down in 2-3
steps to help the user understand the flow.

08:49

In this session, we understand the concept of Capitalization of Interest Cost and create related calculations.

First of all, we segregate the calculation schedule for New Term Loan
from that for Total Loan. We calculate the interest on the New Term Loan
separately. Of this interest, we decide which part has to be
capitalized and which part has to be expenses out i.e. to be taken to
Income Statement as an expense.

Following are some of the questions which are answered in this session:

  • What are circular calculations?
  • How Capitalization of interest leads to a circular calculation in our model?
  • How to enable/disable circular calculations in Excel?
  • How to trace cells that have circular calculations (that are part of the circular loop)?
  • How do circular calculations lead to higher precision?

The session talks about a Best Practice: Keep a check on the number of
circular calculations in your model. Lot of loops may make the model
slow. Sometimes they may also break-down the entire model in face of
small errors.

08:48

In this session, we forecast the Net Working Capital (NWC) requirement
or gap first. Based on this, we calculate the Working Capital Financing
Limit available from the bank.

The session calculates various elements of NWC gap i.e. Current Assets (Minimum Cash, Inventory, Accounts Receivable) Less: Current Liabilities (Accounts Payable and Provisions)

We explain the concept of Minimum Cash and how it forms part of the NWC gap.

We adhere to the Financial Modeling Best Practice: "Maintain
uni-directional flow of calculations i.e. Inputs & Historicals
>>>> Trends and Specialized Calculations >>>>
Output (Financial Statements)"

Specialized Calculations - Term Loan & Working Capital Finance
6 questions
Section 8: Forecasting Various Elements of the Cash Flow Statement
06:38

In this session, we start building Forecasted Cash Flow Statement. We
prepare the layout i.e. Cash From Operating, Investing and Financing
Activities. We also complete the Cash Flow from Operating Activities
(CFOA) section.

We start with PAT line and arrive at the Cash Flow from Operations. In the process, we adjust the

  • Non-Cash line items (E.g. Depreciation),
  • Non-Operating line items (E.g. Interest expense and Income), and also
  • Changes Working Capital.

The impact of all these adjustments on the Cash Flow is explained in a detailed manner.

Cash Flow statement is based on the other 2 Financial Statements.
Hence, after the lay-out is completed, we link the relevant line items
from the Balance Sheet and Income Statement. It is extremely important
to pay attention to signs when building the cash flow and identify cash
inflows & outflows properly.

Changes in Assets and Liabilities and how do they impact the Cash flow is also covered in detail.

03:58

In this Session, we complete the layout and linking for Cash Flow from Investing Activities.

All the line items i.e. Capital Expenditure, Proceeds from Sale of
Fixed Assets, changes in Long Term Investments, Interest Income are
considered here.

We have not considered Short Term Investments at this stage, as we plan
to include them in our Cash Balancing mechanism at the end of the Cash
Flow Statement.

04:57

In this Session, we complete the layout and linking for Cash Flow from Financing Activities.

All the line items i.e. Term Loan drawal, Repayment, New Equity issued,
Dividends Paid, changes in Working Capital Finance and Interest
Expense.

We have not considered Promoter Loans at this stage, as we plan to
include them in our Cash Balancing mechanism at the end of the Cash Flow
Statement.

Importance of signs (+ or -) when dealing with all the line items in Cash Flow is highlighted all through.

Quiz on Forecasting Various Elements of the Cash Flow Statement
5 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