Learn Financial Modeling from Scratch - Part 3

Confidently & quickly create your own financial models. Quickly check even complicated models prepared by others!
0.0 (0 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.
2 students enrolled
$50
Take This Course
  • Lectures 22
  • Length 2 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 3

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.

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 & Part 2 of the Course. Please make sure to check out Part 1 & Part 2 of the Course before you begin with Learn Financial Modeling from Scratch Part 3. 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: Forecasting Calculations - Taking Stock and Completing Pending Elements
03:42

In this session, we discuss the simplistic way of completing the tax
calculations i.e. Effective Tax Rate. We introduce the concept of
Effective Tax Rate i.e. Actual Tax Paid / Profit Before Tax

This method represents a crude and simplistic alternative to creating a
Specialized Tax Calculation which has adjustments for Tax
Depreciation, Carry-forward and Set-off of tax losses, Minimum Alternate
Tax etc.

  • We also talk about a Best Practice that allows to quickly navigate and audit the model later. When linking cells from other sheets, link them first and then link cells from the active sheet. This orders allows to quickly check precedents using CTRL + [
03:54

Here, we go through the 3 Financial Statements and note the line
items yet to be forecasted. We also discuss how each of them will be
calculated / forecasted.

In the Income Statement, the items include Interest Expense and
Interest Income. These are driven by respective loan calculations and
Short Term Investment schedules.

In the Balance Sheet, Assets side, the items include Cash & Cash
Equivalents, Short Term & Long Term Investments, Goodwill and
Intangibles etc. While on Liabilities side, the items include Working
Capital Finance, Term Loan, Promoter Loans, Equity and Retained earning
projections.

In the Cash Flow statement, the pending calculations include calculation of the Closing Cash balance.

In absence of information on some line items, they are assumed to be equal to previous year.

04:34

In this Session, we link some of the line items in the Balance Sheet and complete their forecasts.

These include Term Loan, Deferred Tax liabilities, Capital Lease,
Common Stock and Retained Earnings. On the Assets side, it includes Long
Term Investments, Goodwill & Acquired intangibles and Other Assets.

We create a detailed calculation below the PAT Line inside the Income
Statement. This is to arrive at closing balance of Retained Earnings
after netting off dividends and appropriation of retained earnings
(transfer) to special reserves.

  • In this session, we have used the keyboard shortcut for Fill to Right (CTRL + R) heavily. For certain line items, where neither information is available, nor specialized calculations can be made, we have assumed them to be equal to previous year.
Worksheet
Article
Section 3: Specialized Calculations - Cash Balancing Mechanism I
05:02

In this Session, we prepare the schedules for Promoter Loans and Short
Term Investments. These include arriving at Closing balance based on
Opening balance, Additions and repayments (disposals).

We complete the calculations of interest expense, or interest income
based on the above schedules. We link the respective closing balances in
Balance Sheet.

We continue to adhere to the Financial Modeling Best Practices i.e.
"Always highlight calculations that are inconsistent from the other
columns in a row", and "Highlight WIP calculation cells / rows in the
model."

09:20

In this Session, we complete the specialized calculation for Working Capital Finance and Interest thereon.

We understand in detail the concept of Working Capital and funding for
the same. We then build the Working Capital funding schedule. We compare
the opening balance with maximum possible funding from the bank. We
decide to take additional funding or repay surplus funding based on the
same.

The session ends with total interest calculation and linking of the same into the Income Statement.

We have a useful discussion on how availing additional working capital
funding may lead to a negative arbitrage situation which has a negative
impact on the Return on Equity (i.e. Borrowing at a higher cost and
investing in a low-return instrument). We deal with such a situation in
our subsequent sessions.

Quiz on Specialized Calculations - Cash Balancing Mechanism I
5 questions
Section 4: Specialized Calculations - Cash Balancing Mechanism II
04:02

In this Session, we calculate the Closing Cash Balance in our Cash Flow statement and link it into the Balance Sheet.

At the end, we have a balanced Balance Sheet, where
Total of Assets is equal to the Total of Liabilities & Equity. This
is an important Error check. However, it is not exhaustive. There may
still be some errors of principle in the model such as the model allows
negative cash to exist, or total depreciation exceeds the cost of assets
etc.

We simulate a negative cash situation by deliberately creating a loss
situation in the model. We'll fix the negative cash loophole in our next
few sessions.

04:37

In this Session, we understand in detail the Negative and Surplus Cash
situations. We learn how to reflect them on the appropriate side inside
the Balance Sheet using an IF (or MIN) formula.

In real life, the surplus cash is invested by a prudent businessman.
Whereas negative cash has to be funded e.g. bank overdraft, cash credit,
revolver facility, sponsor loans etc. We may either earn Interest
Income on surplus cash invested in short term investments, or we may
have to pay interest expense on such loans.

Actual impact of these situations is considered in the Model through our subsequent sessions.

03:11

In this Session, we take the first steps in our entire Cash Balancing Mechanism.

From the overall cash available, we reduce the cash flow which will be
required to be invested in the form of Minimum cash balance. Like
increase in any other asset, It has to be reduced as it sucks-out and
blocks the cash from the system.

After this, we provide for the two possible situations i.e. What if the cash flow post this stage is negative or positive?

10:12

In this session, we complete one part of the Cash Balancing Mechanism i.e. How to raise funds in a negative cash situation?

Here, we discuss the order in which funds shall be raised. First, we
sell the Short Term Investments. If the proceeds from sale of Short Term
Investments is not sufficient, then we have to resort to the promoters
to fund the balance deficit.

We calculate the relevant amounts (disposal of ST Investments,
additions to Promoter loans) and link them into the respective
schedules.

This calculation creates a circular loop in our model. The session
has a detailed explanation on how both Short Term Investments and
Promoter Loans lead to circularity.

We have some Quick Excel Tips which allow to create shorter formulas.
The use of MIN or MAX functions as alternative to IF or NESTED IF
functions definitely saves some space.

07:51
In this session, we complete the remaining part of the Cash Balancing
Mechanism i.e. How to deal with a surplus cash situation? How to utilize
it?

Here, we discuss the order in which funds shall be utilized. First, we
repay the high-cost promoter loans to the maximum extent possible. If
there is still any surplus cash left, then we invest it in Short Term
Instruments.

We calculate the relevant amounts (repayment of Promoter loans and
additions to ST Investments) and link them into the respective
schedules. We also link the Minimum Cash balance into the Balance Sheet.

The target is to forecast a balanced Balance Sheet with Total of Assets
= Total of Liabilities and Equity. The session ends with the summary of
entire Cash balancing mechanism.

The signs are extremely important here. The repayment of Promoter
loans, additions to ST investments result in a cash outflow and have to
be shown accordingly in the Cash Flow Statement.

Quiz on Specialized Calculations - Cash Balancing Mechanism II
6 questions
Section 5: Addressing Circularity Issues
09:15

In this Session, we understand in detail, why circular calculations
are sometimes an issue? Sometimes entire model can break down if a small
error impacts the circular calculation. In this session, we also
understand how to recover from such a break-down.

We simulate the situation by creating an artificial error in our
model. We trace and correct the source-cell with the error. Even after
correcting the error, the model fails to recover by itself. We take the
following steps to reboot the model

  1. Identify cells which caused circularity,
  2. Replace the formulas here with hard-coded values or zeroes. This allows circularity to restart and errors are eliminated
  3. Once the circularity starts functioning, replace the hard-coded values by original formulas (It helps to keep such cells highlighted and keep the original formulas pasted on the side.)

This process of recovering the model in case of break-down is lengthy and tedious. It
is a classic case where you have to make a choice between accuracy with
precision (circularity gives precise results) and robustness
(circularity also breaks down the model sometimes)

To build a robust model, in our next session, we introduce a switch
that gives us the best of both i.e. Precision and Robustness.

When tracing the errors, we have used the keyboard shortcuts for
tracing precedents (Trace Precedents with arrows - ALT >>>
M>>> P) frequently.

08:48

In this session, we create a Circularity Switch in the Dashboard using CHOOSE function.

For this, we have identify the cells which cause circularity right at
the time of building the model. These are the same cells which
break-down the model and have to be replaced with hard-coded values to
reboot the model.

We have to use the CHOOSE function in all these cells such that
depending upon the value of this Switch i.e. 1 or 2, the contents of
such cells shuffles between the original circular formula or zero
values.

Later, when the model breaks down for any reason, after correcting the
error, you just need to flip the switch from 1 to 2 and back to 1. And,
your model has now recovered and back-on-track!

This ONE step at the time of building, makes the model highly Robust and can save lot of time later.

When tracing the errors, we have used the keyboard shortcuts for
tracing precedents (Trace Precedents with arrows - ALT >>>
M>>> P) frequently.

Section 6: Forecasting of Items Below the PAT Line
05:00

In this session, we forecast the dividends for our projection period.
We evaluate historical trends such as Dividend Payout ratio, dividends
as a % of face value of common stock. We understand what a payout ratio
is and how it impacts the dividends.

Based on the historical trends, we forecast the dividends and link the same in the Income Statement & Balance Sheet.

Section 7: Specialized Calculations - Income Tax
10:05

In this session, we build a separate calculation schedule for Taxable
Profits (As per the Income Tax Act). So far in our model, we had
calculated Income Tax simplistically using the Effective Tax Rate
method. We’ll replace it with a more detailed calculation in the next
few sessions.

Taxable income is different from Profit before Tax (Book Profit or
Accounting Profit). It includes adjustments like carry forward &
set-off of losses, depreciation as per Income Tax, Minimum Alternate Tax
etc. Globally, Governments use tax policies to guide spending in the
economy, meeting fiscal targets, channelising investments in desired
sectors.

We set-up the Taxable Profits Calculation. Starting with PBT, we add
the book depreciation and reduce the Tax Depreciation. We also create a
calculation for carry-forward and set-off of losses. The set-off
calculation is important, with proper signs and MIN function
facilitating comparison

We conclude this session by calculating the Taxable Profits post set-off and Corporate Income Tax based on the same.

04:44

In this session, we start building the Minimum Alternate Tax (MAT) calculations schedule.

We introduce the concept of MAT and explain the rationale for adopting
MAT in various economies. MAT is calculated by applying a Tax Rate
(lower than the regular Corporate Income Tax Rate) on the Book Profits.
We also understand the concept of MAT credit and how MAT paid in one
year can be utilized later to save tax by adjusting them against the
regular Corporate Income Tax payable in the next few years.

We conclude the session with calculation of MAT based on book profits.

03:03

In this session, we build the Minimum Alternate Tax (MAT) credits sub-schedule.

We introduce the concept of MAT Credits and how they can be utilized
later to save tax payments, by adjusting them against the regular
Corporate Income Tax payable in the next few years.

The MAT credits schedule includes additions, utilization and expiry
calculations. Expiry is calculated based on unutilized credits. MAT,
once paid, is not allowed to be carried forward forever. It has to be
utilized within specified number of years. If unutilized, it is liable
to be expired.

We build this schedule and will be forecasting each of the line items in the coming sessions.

04:06

In this session, we calculate the additions to MAT Credits and utilization of the same.

In any year, if MAT payable is more than the Corporate Income Tax, the additions to MAT credits are made. They are equal to excess of MAT over Corporate Income Tax.

MAT credits are utilized when Corporate Income Tax is more than
MAT in any year. This is subject to availability of MAT credits from
last few years i.e. Company should have paid MAT in last/previous few
years.

For calculating utilization, we compare the opening balance of MAT
credits for that year, with excess of Corporate Tax over MAT. Using, MIN
function, lower of the two is calculated as MAT credits utilized.

13:04

In this session, we conclude the MAT credit sub-schedule with
calculations for MAT expiry. Every year, the old additions are
calculated (i.e. Additions to MAT credits made earlier than last 5 years). OFFSET function is used for tracking the same.

If such old additions are not fully utilized over last 5 years, then
the unutilized portion is liable to be expired. We use the combination
of SUM and OFFSET functions for this calculation.

After completion of MAT schedule, we calculate the Actual tax payable.
This is equal to excess of Corporate Income Tax over MAT utilization as
per above. This is, however, subject to at least MAT being paid every
year.

We also calculate the deferred tax liability and link it across all the
financial statements i.e. Income Statement, Balance Sheet and the Cash
Flow Statement. We ensure that our Balance Sheet balances at the end.

Specialized Calculations - Income Tax
6 questions
Section 8: Special Adjustments
07:16

In this session, we introduce and explain the concept of Negative Arbitrage.

In our existing financial model, we are using the surplus cash to repay
the high-cost sponsor loan (borrowed at 15%). If there is additional
surplus post this, we use to invest in short term instruments which
fetch us a yield at 4%. However, we have this surplus cash situation as
we are borrowing maximum possible finance to fund our working capital at
12%. Indirectly, we are borrowing at 12% and investing the surplus cash
to earn yield at 4%. Such an imprudent situation is generally not
possible in real-life situation.

Hence, in this session, we adjust our schedule for utilization of
surplus cash. We prioritize the repayment of Working capital loan over
Short Term investments.

In a real-life situation, the decision to invest is made based on the
return profiles of various investment opportunities. The return profile
is compared with the cost of borrowing and limits available.

Thus, by including this adjustment, we bring our model closer to real-life situation and practice of prudence.

Section 9: Conclusion
05:26

This is the final session with an overview of finished version of the
model. We highlight all important sections of the final model by colour
coding the 'TABS' of the different worksheets appropriately as follows:

  1. 'Dashboard' and 'Historicals' sheets which contain the Inputs to our model in BLUE colour
  2. 'Trends', 'Revenue Trends', 'Calc' and 'Tax' sheets which represent model Calculations in GREEN colour
  3. 'P&L', 'Balance Sheet' and 'Cash Flow' - the three financial statements of the model representing the Output in GREY colour

Further, we also explore how additional analyses can be performed on
this version. E.g. 'Financial Ratios', 'Error Checks' for efficient
debugging, finally 'Dynamic charts' to present the output.

With that we conclude AFT's 'Learn Financial Modeling From Scratch' Program.

Once again, thank you for choosing AFT. We hope to continue the sharing of knowledge with more programs.

Signing off!


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