Learn Financial Modeling from Scratch - Part1

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.

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.

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

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

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.