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
Learning materials include
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
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:
Why take this Course?
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.
Wish you All the best & Happy Modeling!!!!
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
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
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.
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 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
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
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
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.
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
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
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.
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?
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
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.
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
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.
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
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.
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.
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
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
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
We conclude this session by calculating the Taxable Profits post set-off and Corporate Income Tax based on the same.
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.
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.
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
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.
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
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.
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.
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:
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.
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.