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
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 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.
Wish you All the best & Happy Modeling!!!!
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
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."
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
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."
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
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.
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.
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.
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
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.
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"
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
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
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.
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
When calculating historical trends, we had calculated the trends for
these line items in 2 ways. Assuming that they are either variable or
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.
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)
In this session, we divide the Balance Sheet forecasts in two parts.
simply copying over the formulas from historical Balance Sheet. (Here,
the Calculation parts include subtotals and grand totals of various
assets and liability groups)
In this session, we complete the forecasts for Working Capital items
based on Forecasted Trend Calculations. These items include
A quick tip about auto-completing the formulas by selecting from a list of suggested function names or defined names.
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
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
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."
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
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"
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
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.
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.
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.
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.
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.
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:
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
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)"
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
We start with PAT line and arrive at the Cash Flow from Operations. In the process, we adjust the
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.
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
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
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
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
Importance of signs (+ or -) when dealing with all the line items in Cash Flow is highlighted all through.
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.