www.wallstreetmojo.com
Open in
urlscan Pro
2606:4700:10::6816:c3c
Public Scan
Submitted URL: https://click.convertkit-mail2.com/0vupxr4o69c9hoqgw2pslhwq5zx55/08hwh9hd757w02tl/aHR0cHM6Ly93YWxsc3RyZWV0bW9qby5jb20vZmluYW5jaWFsL...
Effective URL: https://www.wallstreetmojo.com/financial-modeling-in-excel/
Submission: On October 27 via manual from GR — Scanned from DE
Effective URL: https://www.wallstreetmojo.com/financial-modeling-in-excel/
Submission: On October 27 via manual from GR — Scanned from DE
Form analysis 5 forms found in the DOM
GET https://www.wallstreetmojo.com/
<form method="get" action="https://www.wallstreetmojo.com/">
<input type="text" size="16" name="s" placeholder="Search For Tutorials / Courses" value="">
<input type="submit" value="">
</form>GET https://www.wallstreetmojo.com/
<form method="get" action="https://www.wallstreetmojo.com/">
<input type="text" size="16" name="s" placeholder="Search For Tutorials / Courses" value="">
<input type="submit" value="">
</form>GET https://www.wallstreetmojo.com/
<form method="get" action="https://www.wallstreetmojo.com/">
<input type="text" size="16" name="s" placeholder="Search For Tutorials / Courses" value="">
<input type="submit" value="">
</form>Name: wsm_cb —
<form id="wsm_cb" name="wsm_cb" class="search-form zfrm_fc" style="margin-bottom:5px;text-align: center;" autocomplete="off">
<input id="form_name_xtg" name="form_name_xtg" type="hidden" value="Financial Modeling in Excel">
<input id="course_nonce" name="course_nonce" type="hidden" value="034322e6b1">
<input id="course_id" name="course_id" type="hidden" value="1552">
<input class="course_url" name="course_url" type="hidden" value="">
<label class="search-form-label" style="color:#000;font-size: 18px;display: block;font-weight: 400;margin-bottom: 5px;text-align: left;" for="wsm_cb_em"> Email ID <span style="color:red">*</span></label>
<input type="email" name="wsm_cb_em" id="wsm_cb_em" placeholder="Enter Email Address" style="margin-bottom:20px;padding:8px 16px;height: 60px;" autocomplete="off">
<input type="submit" id="wsm_cb_sub" name="wsm_cb_sub" class="wd-button" value="Proceed" style="width: 210px;border:none;background-color:#e55836;text-align: center;">
<input type="hidden" name="pum_form_popup_id" value="451559">
</form>Name: frm_model_emdir —
<form id="frm_model_emdir" name="frm_model_emdir" class="search-form" style="margin-bottom:5px;">
<input id="form_name_clone" name="form_name_clone" type="hidden" value="1552_Colgate Financial Modeling Templates (Solved/Unsolved)">
<label class="search-form-label screen-reader-text" for="freeform">Enter Email Address</label>
<input type="text" name="ws_email_model_emdir" id="ws_email_model_emdir" placeholder="Enter Email Address" style="margin-bottom:10px;padding:8px 16px;" autocomplete="off">
<input type="text" name="mobile" id="mobile" placeholder="Enter your mobile number" class="khal" autocomplete="off">
<input type="text" name="wsm_tknt21" id="wsm_tknt21" class="khal" autocomplete="off">
<input type="submit" value="Download Excel Template" class="wd-button" style="width:100%;border:none;background-color:#e55836;">
<input type="hidden" name="pum_form_popup_id" value="95356">
</form>Text Content
* Skip to primary navigation
* Skip to main content
* Skip to primary sidebar
* Skip to footer
* About
* Contact
* Login
* All Courses
* Free Videos
* Blog
* All Articles
* Financial Modeling Bundle
* About
* Contact
* Login
* All Courses
* Free Videos
* Blog
* All Articles
* Financial Modeling Bundle
Home » Financial Modeling Guides » Financial Modeling in Excel
FINANCIAL MODELING IN EXCEL
Article byKrishna Shenmare
Reviewed byDheeraj Vaidya, CFA, FRM
WHAT IS FINANCIAL MODELING IN EXCEL?
> Financial modeling in Excel refers to tools used for preparing the expected
> financial statements predicting the company’s financial performance in a
> future period using the assumptions and historical performance information.
> One may use such financial models in DCF valuations, mergers and acquisitions,
> private equity, project finance, etc.
Financial modeling in Excel is all around the web. There has been a lot written
about learning financial modeling. However, most of the financial modeling
pieces of training are the same. It goes beyond the usual gibberish and explores
practical financial modeling used by Investment BankersInvestment
BankersInvestment banking is a specialized banking stream that facilitates the
business entities, government and other organizations in generating capital
through debts and equity, reorganization, mergers and acquisition, etc.read
more and Research Analysts.
In this free financial modeling Excel guide, we will take the example of Colgate
Palmolive (2016 – 2020) and prepare a fully integrated financial model from
scratch.
This guide is over 5,000 words and took me three weeks to complete. Therefore,
save this page for future reference, and do not forget to share it.
FINANCIAL MODELING IN EXCEL TRAINING – READ ME FIRST
Step 1 – Download the Colgate Financial Model Template.
You can download this Colgate Financial Modeling Templates (Solved/Unsolved)
here – Colgate Financial Modeling Templates (Solved/Unsolved)
Step 2 – Please note you will get two templates – 1) Unsolved Colgate Palmolive
Financial Model and 2) Solved Colgate Palmolive Financial Model.
Step 3- You will work on the Unsolved Colgate Palmolive Financial Model
Template. Follow the step-by-step instructions to prepare a fully integrated
financial model.
Step 4 – Happy Learning!
If you are new to financial modeling, look at this guide on What is Financial
Modeling?What Is Financial Modeling?Financial modeling refers to the use of
excel-based models to reflect a company's projected financial performance. Such
models represent the financial situation by taking into account risks and future
assumptions, which are critical for making significant decisions in the future,
such as raising capital or valuing a business, and interpreting their
impact.read more
Now you can Master Financial Modeling with Wallstreetmojo’s premium courses at
special prices
BEST FINANCIAL MODELING COURSES BY WALLSTREETMOJO
FINANCIAL MODELING COURSE
* McDonalds Step by Step Modeling from Scratch
* 12+ Hours of Video
* Certificate
Rating 4.91/5
Learn More
FINANCIAL MODELING & VALUATION
* McDonalds Step by Step Modeling from Scratch
* Valuations
* 25+ Hours of Video
* Certificate of Completion
Rating 5/5
Learn More
VALUATION COURSE
* Valuations (DCF, DDM, Comparable Comps)
* 12+ Hours of Video
* Certificate
Rating 4.89/5
Learn More
HOW TO BUILD A FINANCIAL MODEL IN EXCEL?
Let us look at how one can build a financial model from scratch. This detailed
financial modeling guide will provide a step-by-step guide to creating a
financial model. The primary approach taken in this financial modeling guide is
Modular. The modular system essentially means building core statements like
income statements, balance sheets, and cash flows using different
modules/sheets. The key focus is to prepare each statement step by step and
connect all the supporting programs to the core statements on completion. We
understand that this may not be clear now. However, you will realize this is
very easy as we move forward.
* Step 1 – Colgate’s Financial Model – Historical
* Step 2 – Ratio Analysis of Colgate Palmolive
* Step 3 – Projecting the Income Statement
* Step 4- Working Capital Forecast
* Step 5 – Depreciation Forecast
* Step 6 – Amortization Forecast
* Step 7 – Other Long Term Forecast
* Step 8 – Completing the Income Statement
* Step 9 – Shareholder’s Equity Forecast
* Step 10 – Shares Outstanding Forecast
* Step 11 – Completing the Cash Flow Statements
* Step 12- Debt and Interest Forecast
Please note the following –
* The core statements are the Income StatementIncome StatementThe income
statement is one of the company's financial reports that summarizes all of
the company's revenues and expenses over time in order to determine the
company's profit or loss and measure its business activity over time based on
user requirements.read more, Balance SheetBalance SheetA balance sheet is one
of the financial statements of a company that presents the shareholders'
equity, liabilities, and assets of the company at a specific point in time.
It is based on the accounting equation that states that the sum of the total
liabilities and the owner's capital equals the total assets of the
company.read more, and Cash Flows.
* The different sheets are the depreciationDepreciationDepreciation is a
systematic allocation method used to account for the costs of any physical or
tangible asset throughout its useful life. Its value indicates how much of an
asset’s worth has been utilized. Depreciation enables companies to generate
revenue from their assets while only charging a fraction of the cost of the
asset in use each year. read more forecast, working capital forecast,
intangibles forecast, shareholder’s equityShareholder's EquityShareholder’s
equity is the residual interest of the shareholders in the company and is
calculated as the difference between Assets and Liabilities. The
Shareholders' Equity Statement on the balance sheet details the change in the
value of shareholder's equity from the beginning to the end of an accounting
period.read more forecast, other long term items forecast, debt forecast
scheduleDebt Forecast ScheduleA debt schedule is the list of debts that the
business owes, including term loans, debentures, cash credit, etc. Business
organizations prepare this schedule to know the exact amount of the company's
liability to others and manage its cash flows to prevent the financial crisis
and enable better debt management.read more, etc.
* The different schedules are linked to the core statements upon their
completion.
* This financial modeling guide will build a step-by-step integrated economic
model of Colgate Palmolive from scratch.
STEP 1 – FINANCIAL MODELING IN EXCEL – PROJECT THE HISTORICALS
The first step in the financial modeling guide is to prepare the historicals.
DOWNLOAD COLGATE’S 10K REPORTS
One prepares financial models in Excel. The first steps start with knowing how
the industry has been doing recently. Understanding the past can provide
valuable insights into the company’s future. Therefore the first step is to
download all the company’s financials and populate the same in an Excel sheet.
For Colgate Palmolive, you can download the annual reports of Colgate Palmolive
from their Investor Relation Section.
CREATE THE HISTORICAL FINANCIAL STATEMENTS WORKSHEET
* If you download 10K of 2020, you will note that only two years of financial
statement data is available. However, for financial modeling in Excel, the
recommended dataset is to have the last 5 years of financial statements.
Therefore, please download the last 3 years of the annual reportAnnual
ReportAn annual report is a document that a corporation publishes for its
internal and external stakeholders to describe the company's performance,
financial information, and disclosures related to its operations. Over time,
these reports have become legal and regulatory requirements.read more and
populate the historical.
* Often, these tasks seem too tedious as it may take a lot of time and energy
to format and put the excel in the desired format.
* However, one should not forget that this is the work you are required to do
only once for each company. Populating the historicals also helps an analyst
understand the trends and financial statementsFinancial StatementsFinancial
statements are written reports prepared by a company's management to present
the company's financial affairs over a given period (quarter, six monthly or
yearly). These statements, which include the Balance Sheet, Income Statement,
Cash Flows, and Shareholders Equity Statement, must be prepared in accordance
with prescribed and standardized accounting standards to ensure uniformity in
reporting at all levels.read more.
* So, please do not skip this. Instead, download and populate the data (even if
you feel this is the donkey’s work).
Colgate Income Statement with Historical Populated
Colgate Balance Sheet Historical Data
STEP 2 – RATIO ANALYSIS
The second step in financial modeling in Excel is to perform ratio analysis. We
covered this in detail in part 1 of the series – Ratio AnalysisRatio
AnalysisRatio analysis is the quantitative interpretation of the company's
financial performance. It provides valuable information about the organization's
profitability, solvency, operational efficiency and liquidity positions as
represented by the financial statements.read more
VERTICAL ANALYSIS OF COLGATE
On the income statement, the vertical analysis is a universal tool for measuring
the firm’s relative performance from year to year in terms of cost and
profitability. Therefore, it should always be included as part of any financial
analysis. Here, percentages are computed concerning net sales, which is
considered 100%. This vertical analysis effort in the income statement is often
referred to as margin analysis since it yields different margins concerning
sales.
HORIZONTAL ANALYSIS OF COLGATE
Horizontal analysis is a technique used to evaluate trends over time by
calculating percentage increases excelCalculating Percentage Increases
ExcelPercentage increase = (New Value - Old Value)/ Old Value. Instead of
showing the delta as a Value, percentage increase shows how much the value has
changed in terms of percentage increase.read more or decreases relative to a
base year. It provides an analytical link between accounts calculated at
different dates using the currency with varying purchasing powers. In effect,
this analysis indexes the reports and compares these evolved. As with the
vertical analysisVertical AnalysisVertical analysis is a kind of financial
statement analysis wherein each item in the financial statement is shown in
percentage of the base figure. The formula is: (Statement line item / Total base
figure) X 100read more methodology, issues that need to be investigated and
complemented with other financial analysis techniques will surface. The focus is
to look for symptoms of problems that one can diagnose using additional methods.
Let us look at the horizontal analysis of Colgate.
LIQUIDITY RATIOS OF COLGATE
* Liquidity ratios measure the relationship of the more liquid assetsLiquid
AssetsLiquid Assets are the business assets that can be converted into cash
within a short period, such as cash, marketable securities, and money market
instruments. They are recorded on the asset side of the company's balance
sheet.read more of an enterprise (the ones most easily convertible to cash)
to current liabilities. The most common liquidity ratios are the current
ratio RatioThe current ratio is a liquidity ratio that measures how
efficiently a company can repay it' short-term loans within a year. Current
ratio = current assets/current liabilities read more, Acid test (or quick
asset) ratio Cash RatiosCash RatiosCash Ratio is calculated by dividing the
total cash and the cash equivalents of the company by total current
liabilities. It indicates how quickly a business can pay off its short term
liabilities using the non-current assets.read more.
* Turnover Ratios like Accounts ReceivablesAccounts ReceivablesAccounts
receivables is the money owed to a business by clients for which the business
has given services or delivered a product but has not yet collected payment.
They are categorized as current assets on the balance sheet as the payments
expected within a year. read more turnover, inventory turnover, and payables
turnover.
Also, have a look at this detailed article on Cash Conversion CycleCash
Conversion CycleThe Cash Conversion Cycle (CCC) is a ratio analysis measure to
evaluate the number of days or time a company converts its inventory and other
inputs into cash. It considers the days inventory outstanding, days sales
outstanding and days payable outstanding for computation.read more.
OPERATING PROFITABILITY RATIOS OF COLGATE
Profitability ratiosProfitability RatiosProfitability ratios help in evaluating
the ability of a company to generate income against the expenses. These ratios
represent the financial viability of the company in various terms.read more are
a company’s ability to generate earnings relative to sales, assets, and equity.
RISK ANALYSIS OF COLGATE
Through Risk AnalysisRisk AnalysisRisk analysis refers to the process of
identifying, measuring, and mitigating the uncertainties involved in a project,
investment, or business. There are two types of risk analysis - quantitative and
qualitative risk analysis.read more, we try to gauge whether the companies will
be able to pay their short and long-term obligations (debt). We calculate
leverage ratiosLeverage RatiosDebt-to-equity, debt-to-capital, debt-to-assets,
and debt-to-EBITDA are examples of leverage ratios that are used to determine
how much debt a company has taken out against its assets or equity.read more
that focus on the sufficiency of assets or generation from assets. Rates that
looks at are:
* Debt to Equity Ratio
* Debt ratio
* Interest Coverage RatioInterest Coverage RatioThe interest coverage ratio
indicates how many times a company's current earnings before interest and
taxes can be used to pay interest on its outstanding debt. It can be used to
determine a company's liquidity position by evaluating how easily it can pay
interest on its outstanding debt.read more
STEP 3 – FINANCIAL MODELING IN EXCEL – PROJECT THE INCOME STATEMENT
The third step in financial modeling is to forecast the income statement,
wherein we will start with modeling the sales or revenue items.
REVENUES PROJECTIONS
For most companies, revenues are a fundamental driver of economic performance.
Therefore, a well-designed and logical revenue model reflecting the type and
amounts of income flows accurately is extremely important. There are as many
ways to create a revenue schedule as there are businesses. Some common types
include:
* Sales Growth: Sales growth assumption in each period defines the change from
the previous period. It is a simple and commonly used method but offers no
insights into the components or dynamics of growth.
* Inflationary and Volume/ Mix effects: Instead of a simple growth assumption,
a price inflation factor and a volume factor are used. This useful approach
allows the modeling of fixed and variable costs in multi-product companies
and considers price vs. volume movements.
* Unit Volume, Change in Volume, Average Price, and Change in Price: This
method is appropriate for businesses with a simple product mix. It permits
analysis of the impact of several key variables.
* Dollar Market Size and Growth: Market share and change in share – useful for
cases where information is available on market dynamicsMarket DynamicsMarket
Dynamics is defined as the forces of market constituents responsible for the
shift in the demand and supply curve and are therefore accountable for
creating and reducing the demand and supply of a particular product.read more
and where these assumptions are likely to be fundamental to a decision. For
example, the telecom industry.
* Unit Market Size and Growth: This is more detailed than the preceding case
and is useful when pricing in the market is a crucial variable. (For a
company with a price-discounting strategy. For example, a best-of-breed
premium-priced niche player) e.g., the luxury car market
* Volume Capacity, Capacity Utilization Rate, and Average Price: These
assumptions can be important for businesses where production capacity is
essential to the decision. (In purchasing additional capacity, for example,
or determining whether the expansion would require new investments).
* Product Availability and Pricing
* Revenue was driven by investment in capital, marketing, or R&D
* Revenue-based on installed base (continuing sales of parts, disposables,
services, add-ons, etc.). Examples include classic razor-blade businesses and
businesses like computers where sales of service, software, and upgrades are
essential. Again, modeling the installed base is key (new additions to the
floor, attrition in the ground, continuing revenues per customer, etc.).
* Employee based: For example, revenues of professional services firms or
sales-based firms such as brokers. Modeling should focus on net staffing and
revenue per employeeRevenue Per EmployeeRevenue Per Employee is the ratio of
total revenue over total number of employees in a particular accounting
period. It gives an idea about how the business performed.read more (often
based on billable hours). More detailed models will include seniority and
other factors affecting pricing.
* Store, facility, or Square footage based: Retail companies are often modeled
based on stores (old stores plus new stores each year) and revenue per store.
* Occupancy-factor-based: This approach applies to airlines, hotels, movie
theatres, and other businesses with low marginal costs.
Projecting Colgate Revenues
Let us now look at the Colgate 10K 2020 report. First, Colgate has not provided
segmental information in the income statement. However, as additional
information, Colgate has provided details of each segment.
Source – Colgate 2020 – 10K, Page 119
Since we do not have any further information about the features, we will project
the future sales of Colgate based on this available data. We will use the sales
growth approach across segments to derive the forecasts. Please see the picture
below. We have calculated the year-over-year growth rate for each element.
Now, we can assume a sales growth percentage based on the historical trends and
project the revenues under each part. Therefore, total net sales are the total
of the Oral, Personal & Home Care, and Pet Nutrition Segment.
COSTS PROJECTIONS
* Percentage of Revenues: Simple but offers no insight into any leverage
(economy of scale or fixed cost burden.
* Costs other than depreciation as a percent of revenues and depreciation from
a different schedule: This approach is the minimum acceptable in most cases
and permits only partial analysis of operating leverageAnalysis Of Operating
LeverageOperating Leverage is an accounting metric that helps the analyst in
analyzing how a company’s operations are related to the company’s revenues.
The ratio gives details about how much of a revenue increase will the company
have with a specific percentage of sales increase – which puts the
predictability of sales into the forefront.read more.
* Variable costs based on revenue or volume, fixed costs based on historical
trends, and depreciation from a different schedule. This approach is the
minimum necessary for sensitivity analysisSensitivity AnalysisSensitivity
analysis is a type of analysis that is based on what-if analysis, which
examines how independent factors influence the dependent aspect and predicts
the outcome when an analysis is performed under certain conditions.read more
of profitability based on multiple revenue scenarios.
Cost Projections for Colgate
For projecting the cost, the vertical analysis done earlier will be helpful. So,
let us have a relook at the vertical analysis:
* Since we have already forecasted sales, all the other costs are some margins
of these sales.
* The approach is to take the guidelines from the historical cost and expense
margins and then forecast the future margin.
* For example, the cost of sales has been in the range of 39.2%-40.6% for the
past five years. So we can look at forecasting the margins on this basis.
* Likewise, selling, general, and Administrative ExpensesAdministrative
ExpensesSelling, general and administrative (SG&A) expense includes all the
expenses incurred in the selling of the products of the company whether
direct or indirect along with the entire general and the administrative
expenses during an accounting period under consideration such as
advertisement expenses, sales promotion expenses, marketing salaries,
etc.read more have been historically in the range of 33.8%-36.5%. We can
assume the future SG&A expense margin on this basis. Likewise, we can go on
for another set of expenses.
Using the above margins, we can find the actual values by back calculations.
We use the effective tax rate assumption to calculate the provision for taxes.
* Also, note that we do not complete the “Interest Expense (Income)” row as we
will look at the income statement later.
* Interest Expense and Interest Income.Interest Income.Interest Income is the
amount of revenue generated by interest-yielding investments like
certificates of deposit, savings accounts, or other investments & it is
reported in the Company’s income statement. read more
* We have also not calculated depreciation and amortization, which we have
already included in the cost of sales.
* This completes the income statement (at least for the time being!).
STEP 4- FINANCIAL MODELING – WORKING CAPITAL SCHEDULE
Now that we have completed the income statement, the fourth step in financial
modeling is to look at the working capital schedule.
Below are the steps that one must follow for a working capital schedule.
LINK THE NET SALES AND COST OF SALES
REFERENCE THE BALANCE SHEET DATA RELATED TO WORKING CAPITAL
* Reference the past data from the balance sheet.
* Calculate net working capitalCalculate Net Working CapitalThe change in net
working capital of a firm from one accounting period to the next is referred
to as the change in net working capital. It is calculated to ensure that the
firm maintains sufficient working capital in each accounting period so that
there is no shortage of funds or that funds do not sit idle in the
future.read more
* Arrive at an increase/ decrease in working capital
* Note that we have not included short-term debt and cash and cash equivalents
in the working capital. We will deal with debt and cash and cash
equivalentsCash And Cash EquivalentsCash and Cash Equivalents are assets that
are short-term and highly liquid investments that can be readily converted
into cash and have a low risk of price fluctuation. Cash and paper money, US
Treasury bills, undeposited receipts, and Money Market funds are
its examples. They are normally found as a line item on the top of the
balance sheet asset. read more separately.
CALCULATE THE TURNOVER RATIOS
* Calculate historical ratios and percentages
* Use the ending or average balance.
* Both are acceptable as long as consistency is maintained.
POPULATE THE ASSUMPTIONS FOR FUTURE WORKING CAPITAL ITEMS
* Certain items without a prominent driver are assumed usually at constant
amounts.
* Ensure assumptions are reasonable and in line with the business.
PROJECT THE FUTURE WORKING CAPITAL BALANCES
CALCULATE THE CHANGES IN WORKING CAPITAL
* Arrive at cash flows based on individual line items.
* Ensure signs are accurate!
LINK UP THE WORKING CAPITAL FORECASTS TO THE BALANCE SHEET
LINK WORKING CAPITAL ITEMS TO THE CASH FLOW STATEMENT
STEP 5 – FINANCIAL MODELING IN EXCEL – DEPRECIATION SCHEDULE
With the completion of the working capital schedule, the next step in this
financial modeling is to project the CapexCapexCapex or Capital Expenditure is
the expense of the company's total purchases of assets during a given period
determined by adding the net increase in factory, property, equipment, and
depreciation expense during a fiscal year.read more of Colgate and the
depreciation and assets figures.
source – Colgate 10K 2020 Page – 72
* It has not provided depreciation and amortization as separate line items.
However, it is included in the cost of sales.
* In such cases, please look at the cash flow statements, where you will find
the depreciation and amortization expense. Also, note that the below figures
are 1) Depreciation and 2) amortization. So, what is the depreciation number?
* Ending Balance for PPE = Beginning balance + Capex – Depreciation –
Adjustment for Asset Sales (BASE equation).
LINK THE NET SALES FIGURES IN THE DEPRECIATION SCHEDULE
* Set up the line items
* Reference net sales
* Input past capital expenditures
* Arrive at Capex as a % of net sales
FORECAST THE CAPITAL EXPENDITURE ITEMS
* There are various approaches to forecasting capital expenditure. One common
practice is to look at the press releases, management projections, and MD&A
to understand the company’s view on future capital expenditure.
* If the company has guided future capital expenditure, we can take those
numbers directly.
* However, if the Capex numbers are not directly available, we can calculate it
crudely using Capex as % of Sales (as done below).
* Use your judgment based on industry knowledge and other reasonable drivers.
REFERENCE PAST INFORMATION AND CALCULATE NET PP&E
* We will use Ending Balance for PPE = Beginning balance + Capex – Depreciation
– Adjustment for Asset Sales (BASE equation)
* It is complicated to reconcile past PP&E due to restatementsRestatementsA
restatement is the revision of already issued financial statements of one or
more companies to correct errors with material inaccuracy due to non adhering
and complying with the GAAP, accounting mistakes, fraud, or clerical errors
affecting part of the entire financial statement requiring a completely new
audit.read more, asset sales, etc.
* It is therefore recommended not to reconcile the past PPE as it may lead to
some confusion.
Depreciation Policy of Colgate
* Colgate has not explicitly provided a detailed breakup of the Assets.
Instead, they clubbed all assets into the land, building, machinery, and
other equipment.
* Also, useful lives for machinery and equipment are provided in range. In this
case, we will have to do some guesswork to determine the average useful life
left for the assets.
* Also, guidance for useful life is not provided for “Other equipment.”
Therefore, we will have to estimate the useful life of other equipment.
Colgate 2020 – 10K, Page 79
Below is the breakup of 2012 and 2013 Property, Plant, and Equipment Details
Colgate 2020 – 10K, Page 125
ESTIMATE THE BREAKUP OF PROPERTY PLANT AND EQUIPMENT (PPE)
* First, find the Asset weights of the Current PPE (2020)
* We will assume that these asset weights of 2020 PPE will continue going
forward.
* We use these asset weights to calculate the breakup of estimated capital
expenditure.
ESTIMATE THE DEPRECIATION OF ASSETS
* Please note that we do not calculate depreciation of LandDepreciation Of
LandThe land is a company asset with an infinite useful life. As a result, it
is not subject to depreciation, unlike other long-term assets such as
buildings and furniture, which have a limited useful life and thus require
their costs to be allocated to the accounting period.read more as land is not
a depreciable asset.
* For estimating depreciation from Building improvements, we first make use of
the below structure.
* Depreciation here is divided into two parts: 1)Depreciation from the building
improvements asset already listed on the balance sheet, 2) depreciation from
the future Building improvements.
* We use the simple Straight Line Method of depreciationStraight Line Method Of
DepreciationStraight Line Depreciation Method is one of the most popular
methods of depreciation where the asset uniformly depreciates over its useful
life and the cost of the asset is evenly spread over its useful and
functional life. read more to calculate the depreciation from building
improvements listed on the asset.
* For calculating future depreciation, we first transpose the Capex using the
TRANSPOSE Function in ExcelTRANSPOSE Function In ExcelThe TRANSPOSE function
in excel helps rotate (switch) the values from rows to columns and vice
versa. Being a part of the Excel lookup and reference functions, its purpose
is to organize the data in the desired format. To execute the formula, the
exact size of the range to be transposed is selected and the CSE key
(“Control+Shift+Enter”) is pressed. read more.
* We calculate the depreciation from asset contributions from each year.
* Also, the first-year depreciation is divided by two as we assume the mid-year
convention for asset deployment.
Total Depreciation of BuildingDepreciation Of BuildingDepreciation of building
refers to reducing the recorded cost of a building until the value of the
structure either becomes zero or reaches its salvage value. In addition, it
helps to map the revenue in the form of lease rental generated during the
corresponding expenses.read more improvement = depreciation from the asset
already listed on the balance sheet + depreciation from the future building
improvements.
In the above process for estimating depreciation, one may calculate the
depreciation of 1) manufacturing equipment & machinery and 2) other equipment,
as shown below.
Total Depreciation of Colgate = Depreciation (Building Improvements) +
Depreciation (Machinery & Equipment) + Depreciation (additional equipment)
Once we have found the real depreciation figures, we can put that in the BASE
equation as shown below.
* With this, we get the ending net PP&E figures for each year.
LINK THE NET PP&E TO THE BALANCE SHEET
STEP 6 – AMORTIZATION SCHEDULE
The sixth step in this financial modeling in Excel is to forecast the
amortization. Again, we have two broad categories to consider here – 1)
GoodwillGoodwillIn accounting, goodwill is an intangible asset that is generated
when one company purchases another company for a price that is greater than the
sum of the company's net identifiable assets at the time of acquisition. It is
determined by subtracting the fair value of the company's net identifiable
assets from the total purchase price.read more and 2) Other Intangibles.
FORECASTING GOODWILL
Colgate 2020 – 10K, Page 88
* Goodwill comes on the balance sheet when a company acquires another company.
It usually is complicated to project goodwill for future years.
* However, Goodwill is subject to impairment tests annually, which the company
performs. Therefore, analysts are in no position to conduct such tests and
prepare estimates of impairments.
* Most analysts do not project goodwill. They just keep this constant, which we
will do in our case.
FORECASTING OTHER INTANGIBLE ASSETS
* Colgate’s 10K Report notes that most of the finite life intangible is related
to the Sanex acquisition.
* “Additions to Intangibles” are also complicated to project.
* Colgate’s 10K report provides us with the details of the next five years of
amortization expenses.
* We will use these estimates in our financial model.
Colgate 2020 – 10K, Page 88
CALCULATE ENDING NET INTANGIBLES
ENDING NET INTANGIBLES ARE LINKED TO THE “OTHER INTANGIBLE ASSETS.”
LINK DEPRECIATION AND AMORTIZATION TO CASH FLOW STATEMENTS
LINK CAPEX & ADDITION TO INTANGIBLES TO CASH FLOW STATEMENTS
STEP 7 – OTHER LONG TERM SCHEDULE
The next step in this financial modeling is to prepare the other long-term
schedule. It is when we prepare for the “leftovers” that do not have specific
drivers for forecasting. In the case of Colgate, the other long-term items
(leftovers) were Deferred Income TaxesDeferred Income TaxesDeferred income tax
is a balance sheet item that can be either a liability or an asset since it is a
difference in income recognition between the firm's accounting records and the
tax law, resulting in the company's income tax due being different than the
total tax expense reported.read more (liability and assets), other investments,
and other liabilities.
REFERENCE THE HISTORICAL DATA FROM THE BALANCE SHEET
Also, calculate the changes in these items.
FORECAST THE LONG TERM ASSETS AND LIABILITIES
* Keep the long-term items constant for projected years in case of no visible
drivers.
* Link the forecasted long term items to the Balance SheetItems To The Balance
SheetAssets such as cash, inventories, accounts receivable, investments,
prepaid expenses, and fixed assets; liabilities such as long-term debt,
short-term debt, Accounts payable, and so on are all included in the balance
sheet.read more as shown below.
REFERENCE OTHER LONG TERM ITEMS TO THE BALANCE SHEET
LINK THE LONG TERM ITEMS TO THE CASH FLOW STATEMENT
Please note that if we keep the long-term assets and liabilities constant, the
change that flows to the cash flow statement would be zero.
STEP 8 – FINANCIAL MODELING IN EXCEL – COMPLETING THE INCOME STATEMENT
* Before we move any further in this Excel-based financial modeling, we will
review the income statement.
* Populate the historical basic weighted average shares and dilute the weighted
average number of shares
* These figures are available in Colgate’s 10K report.
REFERENCE THE BASIC AND DILUTED SHARES
At this stage, assume that the future number of primary and diluted shares will
remain the same as in 2020.
CALCULATE BASIC AND DILUTED EARNINGS PER SHARE.
We are ready to move to our next shareholder’s equity schedule.
STEP 9 – FINANCIAL MODELLING – SHAREHOLDER’S EQUITY SCHEDULE
The next step in this financial modeling in Excel training is to look at the
shareholder’s equity schedule. The primary objective of this schedule is to
project equity-related items like shareholder’s equity, dividends, Share
buybackShare BuybackShare buyback refers to the repurchase of the company’s own
outstanding shares from the open market using the accumulated funds of the
company to decrease the outstanding shares in the company’s balance sheet. This
is done either to increase the value of the existing shares or to prevent
various shareholders from controlling the company.read more, option proceeds,
etc.
Colgate’s 10K report provides us with the details of common and treasury stock
activities in the past years, as shown below.
Colgate 10K 2020 – Page 97
SHARE REPURCHASE: POPULATE THE HISTORICAL NUMBERS
* Historically, Colgate has repurchased shares, as shown in the schedule above.
* Populate Colgate’s shares repurchase (millions) in the Excel sheet.
* Link the historical diluted EPS from the income statement.
* The historical amount repurchased should be referenced from the cash flow
statementsCash Flow StatementsA Statement of Cash Flow is an accounting
document that tracks the incoming and outgoing cash and cash equivalents from
a business.read more.
Also, have a look at Accelerated Share RepurchaseAccelerated Share
RepurchaseAccelerated share repurchase (buyback) is a strategy adopted by a
publicly-traded company to acquire its outstanding shares in the market from the
clients in large blocks via an investment bank.read more.
SHARE REPURCHASE: CALCULATE THE PE MULTIPLE (EPS MULTIPLE)
* Calculate the implied average price at which Colgate has done share
repurchases historically. One may calculate the Amount repurchased / Number
of shares.
* Calculate the PE multiplePE MultipleThe price to earnings (PE) ratio measures
the relative value of the corporate stocks, i.e., whether it is undervalued
or overvalued. It is calculated as the proportion of the current price per
share to the earnings per share. read more = Implied Share Price / EPS
SHARE REPURCHASE: FINDING COLGATE’S SHARE REPURCHASED
Colgate has not officially announced how many shares they intend to buy back.
The only information that their 10K report shares are that they have authorized
a buyback of up to 50 million shares.
Colgate 10K 2020 – Page 97
* We need to assume the share repurchase amount to find the number of shares
repurchased. Based on the historical repurchase amount, we have taken this
number as $1,500 million for all the future years.
* We need the projected implied share price of the potential buyback to find
the number of shares repurchased.
* Actual share price = assumed PE multiplex EPS.
* One can assume future buyback PE multiple based on historical trends. We note
that Colgate has repurchased shares at an average PE range of 17x – 25x.
* Below is the snapshot from Reuters that helps us validate the PE range for
Colgate.
* In our case, we have assumed that all future buybacks of Colgate will be at a
PE multiple of 25x.
* Using the PE of 25x, we can find the implied price = EPS x 25.
* Now that we have found the implied price, we can see the number of shares
repurchased = $ amount used for repurchase / implied price.
STOCK OPTIONS: POPULATE HISTORICAL DATA
* The common stock and shareholder’s equity summary shows us the number of
options exercises each year.
Colgate 2020 – 10K, Page 97
* Besides, we also have the option proceeds from the cash flow statements
(approx).
* With this, we should be able to find an effective strike priceStrike
PriceExercise price or strike price refers to the price at which the
underlying stock is purchased or sold by the persons trading in the options
of calls & puts available in the derivative trading. Thus, the exercise price
is a term used in the derivative market.read more.
Colgate 2020 – 10K, Page 76
Also, note that the stock optionsStock OptionsStock options are derivative
instruments that give the holder the right to buy or sell any stock at a
predetermined price regardless of the prevailing market prices. It typically
consists of four components: the strike price, the expiry date, the lot size,
and the share premium.read more have contractual terms of eight years and vest
over three years.
Colgate 2020 – 10K, Page 100
With this data, we fill up the options data as per below. We also note that the
weighted average strike price of stock options for 2020 was $72, and the number
of options outstanding was 27.541 million.
Colgate 2020 – 10K, Page 100
STOCK OPTIONS: FIND THE OPTION PROCEEDS.
Our options data below shows that the option proceeds were $504 million in 2021.
We have assumed that 7 million options exercise each year.
STOCK OPTIONS: FORECAST RESTRICTED STOCK UNIT DATA
In addition to the stock options, there are Restricted Stock UnitsRestricted
Stock UnitsRestricted Stock Units or RSU can be defined as stock-based
compensation that is issued as company’s stock to an employee. The company
establishes vesting requirements based on the performance of an individual and
the length of the employment.read more are given to the employees and awarded
and vested at the end of each three-year performance period.
Colgate 2020 – 10K, Page 99
Populating this data in the restricted stock units dataset.
The restricted stock units project to be (8.65/3.0 years), i.e., 2.88 million
going forward.
Also, have a look at the Treasury Stock MethodTreasury Stock MethodTreasury
Stock Method is an accounting approach assuming that the options & stock
warrants are exercised at the beginning of the year (or date of issue, if later)
& proceeds from the exercise of these options & warrants are used to repurchase
shares in the market. read more.
DIVIDENDS: FORECAST THE DIVIDENDS
* Forecast estimated dividends using the Dividend Payout Ratio.
* Fixed dividend outgo per-share payout.
* From the 10K reports, we extract all past information on dividends.
* With the information on dividends paid, we can find the Dividend Payout Ratio
= Total Dividends Paid / Net Income.
* We have calculated the dividends payout ratio of Colgate as seen below:
We note that the dividends payout ratio has been broadly in the range of
60%-66%. Therefore, let us assume the dividend payout ratio of 60% in the future
years.
* We can also link the projected net income from the income statement.
* Using the projected net income and the dividends payout ratio, we can find
the total dividends paid.
FORECAST EQUITY ACCOUNT IN ITS ENTIRETY
With the forecast of share repurchase, option proceeds, and dividends, we are
ready to complete the shareholder’s equity schedule. Link all these up to find
the ending equity balance for each year, as shown below.
LINK ENDING SHAREHOLDER’S EQUITY TO THE BALANCE SHEET
LINK DIVIDENDS, SHARE REPURCHASE & OPTIONS PROCEEDS TO CF
STEP 10 – SHARES OUTSTANDING SCHEDULE
The next step in this online financial modeling in Excel training is to look at
the shares’ outstanding schedule. Summary of shares outstanding schedule:
* Basic Shares – actual and average
* Capture past effects of options and convertibles as appropriate
* Diluted SharesDiluted SharesDiluted shares can be defined as the total number
of shares that the company has at a particular point that can be converted
into the normal share by the holders (convertible bond, convertible preferred
stock, employee stock options). It is done by exercising the right to alter
such shares into ordinary shares.read more – average
* Reference Shares repurchased and new shares from exercised options
* Calculate forecasted raw percentages (actual)
* Calculate average basic and diluted shares
* Reference projected shares to Income Statement (recall Income Statement Build
up!)
* Input historical shares outstandingShares OutstandingOutstanding shares are
the stocks available with the company's shareholders at a given point of time
after excluding the shares that the entity had repurchased. It is shown as a
part of the owner's equity in the liability side of the company's balance
sheet.read more information
* Note: Commonly, this schedule integrates with the equity schedule.
INPUT THE HISTORICAL NUMBERS FROM THE 10K REPORT
* Shares issuedShares IssuedShares Issued refers to the number of shares
distributed by a company to its shareholders, who range from the general
public and insiders to institutional investors. They are recorded as owner's
equity on the Company's balance sheet.read more (actual realization of
options) and shares repurchased can be referenced from the shareholder’s
equity schedule.
* The input weighted an average number of sharesWeighted An Average Number Of
SharesWeighted Average Shares Outstanding is a calculation used to estimate
the variations in a Company’s outstanding shares during a given period. It is
determined by multiplying the outstanding number of shares (consider issuance
& buybacks) in a given reporting period with their individual time-weighted
portions. read more and the effect of stock options for the historical years.
LINK SHARE ISSUANCES & REPURCHASES FROM THE SHARE EQUITY SCHEDULE.
Basic Shares (Ending) = Basic Shares (Beginning) + Share Issuances – Shares
Repurchased.
FIND THE BASIC WEIGHTED AVERAGE SHARES
* We find an average of two years, as shown below.
* Also, add the effect of options and restricted stock units (referenced from
the shareholder’s equity schedule) to find the diluted weighted average
shares.
LINK BASIC & DILUTED WEIGHTED SHARES TO INCOME STATEMENT
* Now that we have calculated the diluted weighted average shares, it is time
to update the same in the income statement.
* Link up forecasted diluted weighted average shares outstandingWeighted
Average Shares OutstandingWeighted Average Shares Outstanding is a
calculation used to estimate the variations in a Company’s outstanding shares
during a given period. It is determined by multiplying the outstanding number
of shares (consider issuance & buybacks) in a given reporting period with
their individual time-weighted portions. read more to the income statement as
shown below
With this, we complete the shares’ outstanding schedule and time to move to our
next set of statements.
STEP 11 – COMPLETING THE CASH FLOW STATEMENTS
We must fully complete the cash flow statements before we move to our next and
final schedule in this financial modeling, i.e., the debt schedule. Until this
stage, there are only a couple of incomplete things.
* Income Statement – interest expense/ income are incomplete at this stage
* Balance Sheet – cash and debt items are incomplete at this stage
CALCULATE CASH FLOW FOR FINANCING ACTIVITIES
Also, check out Cash Flow from FinancingCash Flow From FinancingCash flow from
financing activities refers to inflow and the outflow of cash from the financing
activities like change in capital from securities like equity or preference
shares, issuing debt, debentures or repayment of a debt, payment of dividend or
interest on securities.read more
FIND NET INCREASE (DECREASE) IN CASH & CASH EQUIVALENTS
COMPLETE THE CASH FLOW STATEMENTS
Find the year-end cash and cash equivalents at the end of the year.
LINK THE CASH & CASH EQUIVALENTS TO THE BALANCE SHEET.
Now we are ready to take care of our last and final schedule, i.e., Debt and
Interest Schedule
STEP 12 – FINANCIAL MODELING IN EXCEL – DEBT AND INTEREST SCHEDULE
The next step in this online financial modeling is to complete the debt and
interest schedule. Summary of the Debt and Interest – Schedule.
SET UP A DEBT SCHEDULE
* Reference the cash flow available for financing
* Reference all equity sources and uses of cash
CALCULATE CASH FLOW FROM DEBT REPAYMENT
* Reference the beginning cash balance from the balance sheet.
* Deduct a minimum cash balance. We have assumed that Colgate would like to
keep a minimum of $500 million yearly.
Skip long-term debt issuance/ repayments, cash available for revolving credit
facility, and revolver section.
Colgate’s 10K report notes the available details on the revolved credit
facility.
Colgate 2020 – 10K, Page 49
Also provided in additional information on debt is the committed long-term debt
repayments.
Colgate 2020 – 10K, Page 50
CALCULATE THE ENDING LONG TERM DEBT.
We use the long-term debt repayment schedule provided above and calculate the
ending balance of long-term debt repayments.
LINK THE LONG TERM DEBT REPAYMENTS
CALCULATE THE DISCRETIONARY BORROWINGS/PAYDOWNS.
Using the cash sweep formula, as shown below, calculate the discretionary
borrowings/paydown.
CALCULATE INTEREST EXPENSE FROM REVOLVING CREDIT FACILITY
* Make a reasonable assumption for an interest rate based on the information
provided in the 10K report.
* Find the average balance of the revolving credit facility and multiply it
with the assumed interest rate.
CALCULATE THE INTEREST EXPENSE FROM THE LONG TERM DEBT
Link the historical average balances and interest expenses. Find the implied
interest rate for historical years.
Assume the interest rate on Long term debt based on the implied interest rate.
Then, multiply the average long-term debt by the assumed interest rate.
Calculate Total Interest Expense = average balance of debt x interest rate
Find the Total Interest Expense = Interest (Revolving Credit Facility) +
Interest (Long Term Debt)
LINK DEBT & REVOLVER DRAWDOWNS TO CASH FLOWS
REFERENCE CURRENT AND LONG TERM TO BALANCE SHEET
* Demarcate the Current Portion of Long Term DebtCurrent Portion Of Long Term
DebtCurrent Portion of Long-Term Debt (CPLTD) is payable within the next year
from the date of the balance sheet, and are separated from the long-term debt
as they are to be paid within next year using the company’s cash flows or by
utilizing its current assets.read more and long-term debt as shown below.
* Link the revolving credit facility, long-term debt, and current portion of
long-term debt to the balance sheet.
LINK NONCONTROLLING INTEREST FROM INCOME STATEMENT
CALCULATE THE INTEREST INCOME USING THE AVERAGE CASH BALANCE
LINK INTEREST EXPENSE AND INTEREST INCOME TO INCOME STATEMENT
Perform the Balance Sheet check: Total Assets = Liabilities + Shareholder’s
Equity
AUDIT THE BALANCE SHEET
We need to audit the model and check for linkage errors if there is any
discrepancy.
RECOMMENDED ARTICLES
* Alibaba Financial Model
* Box IPO Financial Model
* Financial Modeling Templates
* Coursera Financial Model
WHAT NEXT?
If you learned something new or enjoyed this Excel-based financial modeling,
please leave a comment below. Let me know what you think. Many thanks, and take
care. Happy Learning!
READER INTERACTIONS
COMMENTS
1. Ravi Kumar says
Thanks sir, excellent work.
* Dheeraj Vaidya says
Thanks for your kind words!
2. Patrick Chebos says
Thank you Dheeraj, this is an excellent piece of work and I have learnt a
lot from it. Thank you once again and have a good day.
* Dheeraj Vaidya says
Thanks for your kind words!
3. Lawrence Raphael says
Hi – thank for this very useful course template, but I wasn’t able to
download the Colgate model via the download box?
Would be grateful if you could assist!
Many thanks,
Lawrence R
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
4. Abbey says
Dheeraj, you are a blessing to those of us getting into financial modeling
with this piece. Thank you so much. Can I get the model/templates?
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
5. Pragya Garkemukhu says
Hello Sir,
There is a problem in downloading colgate financial model. i have tried
several times but dint receive the mail.
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
6. Elnur says
Thank you Dheeraj for such a great insight about financial modeling.
Awesome work done by you.
I can’t download your all of sample modelling in your blog. Could you
email it to me?
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
7. Эльнур says
Добрый день, Dheeraj.
Спасибо за Ваш замечательный сайт и интересный обучающий материал.
Прошу направить электронные модели по почте, так как ввод электронной
почты в форму запроса не привел к отправке файлов.
Заранее спасибо.
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
8. Harmeet Singh says
Hello Sir, please share the model via email, i haven’t received it yet.
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
9. Marcel says
Hi, Can you please email me the template? I am unable to download it.
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
10. Elnur says
Hi, I would love to try this Colgate model but I can’t download it. How
can I get the templates. I have put my email address in but it doesn’t
work.
Thanks
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
11. Muideen Abubakar says
Hi Dheeraj, many thanks for this, you have just added a huge value.
Meanwhile, it seemed the model failed to deliver to my mail, can you help
me out with this please.
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
12. sanaa raoussi says
Thank you Dheeraj for this great tutorial! Can you please send me the
templates?
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
13. sanaa raoussi says
Thank you dheeraj for this great turorial!
Can youn please send me the templates?
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
14. Pranjal says
Sir, I am unable to downlaod the model. Kindly email me the same.
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
15. Aayushi says
Hi. thank you for the wonderful tutorial. However I’m unable to download
any of the excels – solved unsolved models used through the tutorial.
Could you please send me all the excels on the email id:
aayushikk@gmail.com
It’ll be very helpful. Thanks a lot again
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
16. Myrna says
Hi,
Could you please email me the Excel templates as they are failing to
download?
thanks a lot!
Myrna
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
17. Olivier says
Hello, I failed to download the model. I think the link is not available.
Could you please send me one copy of the1) Unsolved Colgate Palmolive
Financial Model, and 2) Solved Colgate Palmolive Financial Model
Thank you so much.
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
18. Mukul Gupta says
Thanks Dheeraj for sharing this. Could you please mail me the templates?
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
19. Andrew says
Could you please mail me templates. Thanks in advance!
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
* Aayushi Kukreja says
Please email me the excel models as well. Not able to download them.
Thanks for the help.
* Dhanashire Rajesh says
Please check your email. I’ve just sent the model.
20. Alex says
Hi,
The content is great. Thank for everything you shared.
Just one think; I can not get the Colgate model
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
21. Nidhi says
Would love to go through this but like all others, don’t have the
template.
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
22. Yimeng SUN says
Hello sir, I failed to download the model.
Could you please send me one copy?
Thank you so much for sharing such helpful stuff.
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
23. Neeraj says
Hi, I would love to try this Colgate model but I can’t download it. How
can I get the templates. I have put my email address in but it doesn’t
work.
Thanks
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
24. Mickey says
Hey Dheeraj
Could I have the excel template….
Also wrote earlier if you have any material on different types of
valuation models…..
Did not hear from you
Thanks & await to hear from u
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
25. Ahmed-Sherif says
Hello Dear,
I tried to download the templates for the free model but I was not apply
to receive them
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
26. Dima says
Hi, great article. But I have not received email of templates, could you
please send me it?
Thanks
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
27. Delom says
Hi Dheeraj. Thanks so much for the model tutorial. Kindly send me the
model as i have signed up my email as well.
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
28. Evgeny says
Hello! Great article, very useful. But I haven’t received the template by
e-mail. Could you please forward it ti me?!
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
29. pv says
Not yet received the Colgate Templates, Please email
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
30. Ana says
Hi Dheeraj!
I have trouble downloading the file. Can you send me the templates to my
email?
I really appreciated it.
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
31. fayaz says
hi sir,
I haven’t received the template in my mail
Please forward it.
* Dheeraj Vaidya says
just mailed the model. Please check.
* hitesh Japi says
hi Dheeraj,
I have not received your template in my email, could you please check
Thanks
Hitesh
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
* Noel ferrin says
Fabulous explanations.
Please do forward if you the model if you can
Many thanks
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
* Jason Prignoli says
Can you Please forward me the model
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
32. Dana Walker says
I would love to try this Colgate model but I can’t download it. How can I
get the templates. I have put my email address in but it doesn’t work.
* Dheeraj Vaidya says
just mailed the model. Please check.
33. Terencek says
Hey, great article. Just wondering where i can get the template. Thanks!
* Dheeraj Vaidya says
just mailed the model. Please check.
34. Kato says
Hi Dheeraj.
i haven’t received the model in my email. Kindly send it.
* Dheeraj Vaidya says
just mailed the model. Please check.
* Mohammed Sayani says
Hi Sir
I would like to practice on the Colgate model but I don’t have the
file. Could you please send me the files
* Rajesh Dhanashire says
Please check your email. I’ve just sent the model.
35. Ali says
I have not received email of templates, could you please send me the
files?
Regards
Ali
* Dheeraj Vaidya says
just mailed the model. Please check.
36. Ali says
Thank you for this detailed and simple explanation. I have submitted my
email id but did not receive template email yet. could you please help?
* Dheeraj Vaidya says
just mailed the model. Please check.
37. Carlina says
Hi,
I’ve tried to registered but was unable to get any email.Can you please
send me the 2 template at vinamontinola@gmail.com and also the ratio
analysis
* Dheeraj Vaidya says
just mailed the model. Please check.
38. Manideep says
Hi Dheeraj sir,
I am not able to get the financial model template even after signing up
for download.It would be helpful to have good hand on of the above
course.I request you to please look into it.
* Dheeraj Vaidya says
just mailed the model. Please check.
39. Helmi says
Hello!
I have submitted my email address and now waiting for the excel (solved &
unsolved) :)
* Dheeraj Vaidya says
just mailed the model. Please check.
40. Anish says
Hi,
I am unable to download the models, haven’t received it despite numerous
attempts.
Request you to please share it with me.
Thanks!
* Dheeraj Vaidya says
just mailed the model. Please check.
41. Smriti Jaiswal says
Hi Dheeraj,
First of all ,Thank you for the amazing blog.I Have been navigating
through a lot of websites and blogposts but none have proved to be
remotely helpful as this one!!! Your approach of covering both theoretical
as well as practical application makes one ready to hit the ground
running!!.I am trying to learn financial modeling and have been unable to
download the colgate Palmolive financial model template in excel .Request
you to kindly mail me a copy at my email id
* Dheeraj Vaidya says
just mailed the model. Please check.
42. Hemant Sultania says
Hi Dheeraj, thank you so much for this wonderful explanation and deep dive
into the world of Equity Research, could you please send me the templates?
I didn’t receive it through the emails?
* Dheeraj Vaidya says
just mailed the model. Please check.
43. Sandeep Kumar Gupta says
Dear sir,
Indeed a good article, very informative and best one. I thank you for your
efforts for writing this.
* Dheeraj Vaidya says
thanks Sandeep!
44. shao says
Great article!
Could I have the excel template?
* Dheeraj Vaidya says
just mailed the model. Please check.
45. Akintayo Alo says
Hi,
Please assist by sending the excel file.
* Dheeraj Vaidya says
just mailed the model. Please check.
46. MH says
Hi Dheeraj, just want to say that this tutorial is one of the best I’ve
ever seen. Please do more of these in the future, I’m sure a lot of people
are also interested.
Also, is there any way that I can download the model as I cannot see any
link to download it? Same goes for the Box and Alibaba models as well on
your other posts.
Thank you
* Dheeraj Vaidya says
just mailed the model. Please check.
47. bindumadhavi says
sir,
i want to first learn in demo class, so that i can analyze myself that i
can able to course or not
48. Raghu says
Hi Dheeraj,
Great post. Thanks for the effort.
Is there any financial model developed for upstream oil and gas industry.
It would be great to see something
* Dheeraj Vaidya says
Hi Raghu, i don’t have such a model.
49. William Awuah says
Hi Dheeraj,
This is so helpful for the course that I’m currently enrolled in. Can you
email me the exact template in PDF?
My email address is wawuah@yahoo.com.
Thank you in advance.
* Dheeraj Vaidya says
just mailed the model. Please check.
50. Gafar Adediran says
Thank you for sharing your great knowledge.
Please is this the same basis for building Actuarial model.
I want to be able to develop an actuarial model
* Dheeraj Vaidya says
Hi Gafar, unfortunately, i don’t have an actuarial model.
51. Ayushi says
Really an amazing article. I have learnt a lot form it. Keep up the
wonderful work as it really helped the upcoming professionals in the
industry. Amazing job !!!!!
It would be great if you share the template with me. Thanks
* Dheeraj Vaidya says
just mailed the model. Please check.
52. Yash says
Hi.. Could you share me the template for a deep study further? The one
with all the ratios and the detailed study ?
* Dheeraj Vaidya says
just mailed the model. Please check.
53. Raman says
MOST AWESOME MATERIAL. I am from Booth MBA and not come across such
valuable article. Can you please email the most updated copy
Thanks much.
* Dheeraj Vaidya says
just mailed the model. Please check.
54. Johnavia Scott-Walton says
This is amazing. I have been wanting to practice financial modeling since
I last took a course in 2003.
Do you still work with JP Morgan
* Dheeraj Vaidya says
Hi Johnavia, Thanks. I left JP Morgan in 2007 after falling in love with
entrepreneurship and blogging.
55. Ruby says
Hi Dheeraj,
This site is wonderful, glad I found it. Could you please email me the
deep analysis template as the template I downloaded seems missing some
parts. Thank you!
* Dheeraj Vaidya says
just mailed the model. Please check.
56. Alexander Santibanez says
Do you have the template for this example?
* Dheeraj Vaidya says
just mailed the model. Please check.
57. Daniel Merediz Jimenez says
Sir Dheeraj, I actually appreciate what you are doing for all of us. This
blog is amazing, and I’ll keep going learning as much as I could from you.
I hope to be a good analyst like you, thank you for all!!!
* Dheeraj Vaidya says
thanks Daniel. Glad you liked it.
* Anurag Agrawal says
Hi Dheeraj,
I have enrolled for paid venture capital course. I am unable to access
it. Could you please guide me on the next steps ?
Regards,
Anurag
58. ARJUN SEN says
Hi Sir,
I have just started using your course on Finacial Modelling through
EDUCBA. Can you please send me the various templates used on the courses.
Thanks and regrads,
Arjun
* Dheeraj Vaidya says
just mailed the model. Please check.
59. Hezekiah Mulehse says
Hi Dheeraj . This is very instructive and largely straightforward to
follow . Many thanks
* Dheeraj Vaidya says
thanks Hezekiah!
60. Akintayo says
Insightful.
Please assist by emailing me the MOdel.
Kind regards,
Akintayo.
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
61. Kaushik says
Excellent article.
* Dheeraj Vaidya says
thanks Kaushik!
62. Sahil Jakhar says
Hello Dheeraj,
I loved you written model here. I want practice this same model myself.
Can you send the Solved Excel Sheet, so that I can evaluate myself.
Thanks in advance.
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
63. SHREYANS JAIN says
Great inisght can you mail me the template
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
64. Abhishek Pareek says
Hey Dheeraj Very nice Article, I haven’t got the Financial model in mail
please send it again.
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
65. Mithlesh Sahani says
Hello Dheeraj,
Despite mentioning the e-mail address in the box for a couple of times, i
have still not received the unsolved and solved s/sheet for the model. can
you please kindly mail me on mithleshsahani@yahoo.co.in
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
66. Michael Pineiro says
Hello,
I signed up but haven’t received the files.
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
67. Paresh Upasani says
Dear Dheeraj,
Awesome article. I have seen very few such easy worded articles which make
difficult topic like this look simple.
I am unable to download the solved and unsolved excel sheets for finance
modelling can you please arrange to send me.
Many thanks for this wonderful work.
Thanks!
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
68. Michael Emmanuel says
excellent job!!! please could you share the template with me my email?
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
69. Joshua says
Excellent! what a great work
* Dheeraj Vaidya says
thanks Joshua!
70. Satish says
Hi Dheeraj
I’m unable to download the Colgate palmolive Historical model through the
link you have given. Could you please send me the link?
Many thanks
Satish
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
71. Mariya says
Hi Dheeraj,
I enjoy your site and appreciate your time and desire to share your
knowledge with people like me! I am still reading this article and
training particularly.
I would like to ask you, at the moment this is Colgate case to learn. But
if you have to do something similar but for a company which starts now
where do you manage to take all the information from. Here we have
historical data but when the company is new, in fact we do not have such
data?
I appologize if you have commented a question like this before!
Thank you!
Mariya
* Dheeraj Vaidya says
thanks Mariya. If the company is new, then it becomes tricky as there is
very limited data available. Such models are very simplified and its
complexity depends on the amount of information available.
Best,
Dheeraj
72. Michael says
Hello Dheeraj Sir,
First of all i would like to thank you for giving us a useful model and
some information about Financial modeling.
But, can you please send me the download link of the Ratio Analysis in
this course. Also, “Download the Colgate Palmolive Historical Model here”
is not working.
Thank you so much!
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
73. JY,Kim says
wonderful tutorial.
Could you share me the template for a deep study further?
* Dheeraj Vaidya says
Thanks. have mailed you the template!
* shivam dang says
Hi, Dheeraj
I am not able to receive the files. Request to share them
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
* Jai Gupta says
I really appreciate the efforts you have put here. Learnt a lot. Can
you please share the template with me too?
* Dheeraj Vaidya says
just mailed the model. Please check.
* Jon P says
Hi Dheeraj,
I to found this very informative – thank you!
Would it be possible to get a copy of the template?
* Dheeraj Vaidya says
just mailed the model. Please check.
74. Kim,JY says
Hello,Mr
I’m student in S.korea
Thank you for your effort.
* Dheeraj Vaidya says
thanks Kim. Glad you liked the financial modeling tutorial.
75. sowjanya kariveda says
hai.. I have downloaded the excel sheets but later what to do I am not
getting. Can you help me how to learn from that?? And where the videos
will be available?
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
76. Eason says
Dear Dheeraj,
I really appreciate your work and knowledge. However, I haven’t received
the template yet, Could you please kindly send me the Colgate financial
model?
Best,
Eason
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
77. Ed says
Hi,
Thanks for sharing this info, will you mind to send me the model.
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
78. HanSwe says
Dear Dheeraj, do you have video training for this financial modelling? I
would like to have it if you do.
I am very interested to learn financial modelling, thank you for your
great help.
Bests regards,
HanSwe
* Dheeraj Vaidya says
Yes, we do have video training course on Financial Modeling. Please do
let me know in case of any information you require about this one.
* Adebari Oladimeji says
Please how can I get the videos of the financial modeling ?
* Dheeraj Vaidya says
Hi Adebari, unfortunately, i haven’t prepare videos for this Colgate
Model. However, you may find a lot of video based tutorials in the
pro version here Financial Modeling Course
79. Sachin says
Simply amazing and a grand salute to your knowledge. I have a small query.
Sometimes the previous figures that are given in an annual report are
different from current figures of previous annual report. For eg the ebit
figures of 2014-15 (previous figures) in the annual report of year 2015-16
(current year) and those of 2014-15 (current figures) in the annual report
of 2014-15 (previous year) are different. In such a case, which figures to
use?
* Dheeraj Vaidya says
Excellent Question Sachin. I am happy you thought about this :-)
Only those who have opened the annual report and tried populating the
historical figures may not this issue. You should always take the most
recent data available for the model. In the case you suggested, you
should take all the figures from 2015-16.
80. Ryan says
Hi, Thanks for the Tutorial.
I can’t seem to find the ratios in the templates that I downloaded. Is
there a link to another download for them?
Thank you!
* Dheeraj Vaidya says
Please check your email. I’ve just sent the model.
81. Alex says
wonderful tutorial.
Could you share me the template for a deep study further?
* Dheeraj Vaidya says
Hi Alex, did you receive the template?
* Karishma says
This is amazing!
Could you kindly mail me the template?
* Dheeraj Vaidya says
just mailed the model. Please check.
82. Amit says
i appreciate for all your efforts
can you please send me some SOTP Valuation model
* Dheeraj Vaidya says
Hi Amit,
I don’t have a ready a SOTP valuation model as of now. However, you can
refer to SOTP valuation that i took in one of the detailed posts.
Thanks,
Dheeraj
83. Pratik Biyani says
Hello Sir,
Great Tutorial. I have one question though!
So the company I am analysing has never buybacked thus there is no amount
for the buyback of shares in the cash flow statement. With this issue, how
do I come up with the implied share price (Amount outgo/no. of repurchased
shares)and thus the Assumed current year EPS multiple (Implied Share
price/Current year EPS). Please help me!
Thank you
* Dheeraj Vaidya says
Hi Pratik,
If they have never bought back the shares, then there are two scenarios
–
1. did the company announce that they are going to buy back in the
future. If no, then you are not required to work on finding the share
repurchase.
2. If the company did announce that they are going to buy back, then you
may use the Industry average PE to find the implied share price of the
stock.
Hope this answers your queries.
Dheeraj
84. Phil Murphy says
Hi Dheeraj,
Many thanks for your great tutorial.
Do you have the most up to date version yet? it would be very much
appreciated if you do
many thanks
* Dheeraj Vaidya says
Hi Phil,
I am updating this case study.. should be up in 2-3 weeks time.
Best,
Dheeraj
85. Ryan says
Could you send me the most up to date model (solved and unsolved) please?
Thanks!
* Dheeraj Vaidya says
Hi Ryan,
I am working on the updated model to be released soon. Meanwhile, please
let me know if you require the dated model?
Best,
Dheeraj
86. Tejal says
Hey! Thanks for this amazing tutorial.
Can you help with the analysis of banking sector?
* Dheeraj Vaidya says
HI Tejal,
As of now, I have not yet prepared a banking sector model. Will keep you
posted on this.
Best,
Dheeraj
87. Pooja Agarwal says
Hi Dheeraj,
Thank you for an amazing model, this truly helps getting hands on
experience on this very interesting
aspect of fundamental analysis. I have a couple of questions though, would
be glad if you could answer them:
1) I believe that for the purpose of projections (and ratios), we should
be excluding non-recurring/unusual, non-operating (for EBIT) items,
because we may not expect the same to be existent in future. Is this not
the general practice? (like in 2015, there is a charge of accounting
change 1084 million dollars, which, if not excluded from the projections,
may not provide a true expectation for future since YoY growth is not
explanatory of business activities)
2) For ratios, one of the components may be included in an item like
‘other assets’, I think to fill in the historic numbers, these components
should be brought in (like for 2010, there are short term investments of
74 million dollars included in other current assets. Which, if ignored
doesn’t precisely fill the quick and cash ratios, though in this case
impact is not much since its a small number compared to the total current
liabilities)
Many Thanks!
Pooja
* Dheeraj Vaidya says
Hello Pooja,
Thanks. You have some great points here. My take on those.
#1 – Non recurring items – you are right on this. We should ideally
remove all the non recurring items so that the projections do not
include these one time volatilities. I did not do that in the model to
keep the model a bit simpler to work with at this stage.
#2 – You are right on the short term investments thing too :-) It should
be included as well. I am working on a full ratio analysis case study.
Will try and incorporate your valuable suggestions in that.
Many thanks,
Dheeraj
88. Kartik says
Hello Dheeraj
I havent received the template in my mail
Please forward it
* Dheeraj Vaidya says
Hey Kartik, just send you an email on this.
Please check.
Thanks,
Dheeraj
* matt parent says
I also have not received the template
* Dheeraj Vaidya says
just mailed the model. Please check.
89. Adit says
Hi! so some of my questions may seem silly however i am very new to this!
Under what assumptions did you come up with the figure of 4% growth in
sales?
* Dheeraj Vaidya says
Hi Adit,
I have taken this just on the basis of historical growth rates (though i
should have investigated further on this).
Thanks,
Dheeraj
* Adit says
Hi dheeraj!
Thanks for that. So you calculated the average historical growth rates
and used that as a basis for your projection?
I would like to ask you a number of questions. Would you be willing to
send me an email so that I could respond with my questions? ( only
because I do not see any email contact for you on the page)
Regards
Adit
* Dheeraj Vaidya says
Hi Adit,
You can send me an email on dheeraj at wallstreetmojo.com
Thanks,
Dheeraj
90. Adit says
hi dheeraj! Thank you for all your efforts! it is much appreciated. I
noticed that in the colgate palmolive model template the ratio analysis
bit is absent. Could you please advise?
* Dheeraj Vaidya says
Hi Adit,
I am currently working on updating this case study along with the Ratio
Analysis. Should be up and ready in a few weeks time.
Thakns,
Dheeraj
* Adit says
Hey dheeraj!
Thank you so much for your reply! I look forward to your update. it is
much appreciated.
* Dheeraj Vaidya says
sure Adit.
thanks!
* Rohit says
Has it been published yet?
* Dheeraj Vaidya says
Yes Rohit, you can check the Ratio Analysis here
91. Pa Line says
I can get to the last step fine but the part around the revolver and the
transferring the debt info the the BS are causing me serious trouble. Why
do the numbers in the tutorial, the numbers calculated by using the
formulas, and the numbers in the completed model not match? For example,
in the 12H step, the revolver is 40, on the completed model it is 940, and
via my model, cell K20 should be 112.3. What is going on?
* Dheeraj Vaidya says
Hey Pa,
Did you take the same assumptions as mine? Also, once the model is
completely linked, it goes through a circular reference loop and causes
changes to the intial numbers that we may have taken.
thanks,
Dheeraj
92. Kshitiz Sanjeev Kumar says
Hi Dheeraj,
Got to know about this blog of yours while searching guidelines of finance
modelling over internet.
I am an amateur in this field though done with my mba. I really want to
explore the finance modelling.
So, could you please help me with this by providing some background and
how can i pursue some practice on the same.
It will be really helpful for me.
Thanks.
* Dheeraj Vaidya says
Hey Kshitiz,
Financial Modeling is primarily useful for careers in Investment
Banking, Equity Research etc. You can think of this as the core of
Research Field. You can learn Financial Modeling easily by downloading
unsolved sheet and practice as per the given step by step instructions.
Do let me know in case of any isseus that you are facing.
thanks,
Dheeraj
93. Rohan Vaswani says
For 3A, revenue projections using growth rates, how do you get the
projected growth rates? for example: projected growth rate of revenue in
North America is 4.0%. How do you get this 4.0%?
* Dheeraj Vaidya says
Hey Rohan,
I did not use much brains here. I have just taken this from the trend
based on historical analysis. In this case, i was focussing more on
ensuring that students learn financial modeling and don’t get lost much
into the assumptions side.
thanks,
Dheeraj
94. Santanu kar says
Hi deeraj,
Hope u r doing well.
Is financial modelling for capex projects different from that of equity ?
I am a civil engineer trying to gain knowledge on capex project proposal,
financial projections
Regards,
Santanu
* Dheeraj Vaidya says
Hi Shantanu,
thanks. Equity research may not contain capex in lot of details as it is
just a part of the overall financial model. However, from project
finance perspective, Capex will become the most important driver. You
should learn Project Finance Modeling.
Thanks,
Dheeraj
95. Thomas says
The ratios are shown int he tutorial but are not found in the template. Is
there a reason for this? Can you send me the updated template please.
* Dheeraj Vaidya says
Hey Thomas,
An update to this is under progress. Will send you shortly.
thanks,
Dheeraj
96. Mythreyi velury says
Hey Dheeraj
I love your work and thanks a million for such a generous offering. i am
currently working on step 2 and have reached the calculation of
operational profitability ratios. My values are close yet far from your
values. i request you to share the sheet in which you’ve done the
financial ratios analysis. it would be of great help.
Thanks in advance.
* Dheeraj Vaidya says
Hey Mythreyi,
You should proceed further as Ratio Analysis will not affect much of
your financial modeling. An update to this Ratio Analysis is under
progress. Will reply to you soon on this.
Thanks,
Dheeraj
* Nikita Rachel says
Hi mythreyi, is that youuuu? Fancy meeting you here. Yay we’re both
learning the same thing haha. xP
97. Hetal says
Hi Dheeraj,
Thank you for this tutorial. Its the best ever tutorial that i have come
across.
I just had one query how do you project amortization ? In this example
colgate had given details of next the 5 years of amortization expense. But
how do you do it when nothing is mentioned about the future expenses ?
Thanks in advance.
* Dheeraj Vaidya says
Hello Hetal,
If not much information about the amortization is provided, we will
proceed in the same way as the Depreciation schedule.
thanks,
Dheeraj
98. Navin says
Thanks Dheeraj for the free financial modelling tutorial. Much
appreciated.
Would you have a example for a SaaS startup valuation. As in initial years
they make losses & their working capital mostly comes from borrowings or
injection from shareholders.
* Dheeraj Vaidya says
Hey Navin,
Unfortunately, i do not have the SAAs valuation model with me.
99. RAJESH PATIL says
Hi Dheeraj,
It is indeed great info & new simple way of learning.Surely it will help
all those who are in this field & currently working in F & A dept.
Personally thanks for brushing lost knowledge.
Would like to see more….
* Dheeraj Vaidya says
Thanks Rajesh!
100. Ayush says
Hi Dheeraj,
Can you please help me finding the templates because I am unable to locate
the Template download option.
* Dheeraj Vaidya says
Hope you received the model. Anyways, I have resend the same.
thanks,
Dheeraj
101. Arshad Ali says
Hello Dheeraj Sir
I had filled the form already but still not received template. kinldy send
me the colpal unsolved template.
* Dheeraj says
Hi Arshad,
I just mailed you the template.
thanks,
Dheeraj
* Eric says
Hi Dheeraj, can you please send me the Colgate model unsolved &
solved? Thank you!
* Dheeraj Vaidya says
I have just resent you the templates in case you didn’t receive.
* Justin says
Good morning Dheeraj,
I filled out the form already but have not received the template.
Would you be so kind to send me both the solved and unsolved Colgate
Palmolive template?
Thank you in advance.
* Dheeraj Vaidya says
Please check your mail. I have resent it.
Thanks,
Dheeraj
102. Tanupreet chadha says
I’m getting a DIV/0!error while referring the originally held constant
diluted weighted average figures to the calculated “share outstanding
schedule”, figures for the same. let me know how to fix this, i have done
it in the same manner as yours.
* Dheeraj says
Hi Tanupreet,
This is a typical circular reference error. You need to activate “Enable
iterative calculations”. In excel 2016, it is present under
FILE->Options->Formula.
Hope this helps,
Dheeraj
* Tanupreet chadha says
It’s been already enabled.Rechecked quite a times but still having the
same error. Only the diluted weighted share figures has this problem
and not the basic weighted average share figures. i can share the
worksheet, if you can please look into it.
* Dheeraj says
Hi Tanupreet,
The sheet that you had sent doesn’t contain any errors. Such errors
can be excel /computer specific. You may try again with the
following to remove #DIV #Value kind of errors –
Circular reference comes due to two aspects –
1. Interest income/interest expense from debt schedule is linked to
INcome Statement sheet.
2. diluted number of shares are linked back to Income Statement.
For 1 – Try this if this helps – IS SHEET
a) go to income statement – Copy all range of linked cells L14 to
N14 from the debt schedule to let’s say Q14 to S14.
b) Delete the links L14 to N14 (blank it totally)
c) Copy Q14 to S14 and Paste it back to L14 to N14.
if error still persists then try this –
Go to 2 – Try this if this helps – Shares Outstanding Sheet
a) Copy all range of linked cells L8 to N8 from the shares
outstnading to let’s say Q8 to S8.
b) Likewise do it for L12 to N12 and copy it to Q12 to S12
c) Delete the links L8 to N8 & L12 to N12
d) Copy Q8 to S8 and Paste it back to L8 to N8
e) Copy Q12 to S12 and Paste it back to L12 to N12
All the best,
Dheeraj
* Tanupreet chadha says
Thank you dheeraj the solution absolutely worked. But few things
are still unanswered. One, the ratio analysis bit from the FM is
missing. Secondly, how to make a reasonable assumption for an
interest rate based on the information provided in the 10K
report.Meaning,do you mean past interest rates from the 10-k,
please specify.
* Jan says
Hello Dheraaj,
If above two did not help, could I send you my model and ask you
for having a look at it?
Woild be very helpful, as A do not equal L+S and this circular
reference is blockong the calculations.
Please let me know.
Thanks,
Jan
* Dheeraj Vaidya says
sure Jan. Please send me the model. Will have a quick look at it.
thanks,
Dheeraj
* Tanupreet chadha says
Hi Dheeraj,
Few things: 1)The FM is complete yet incomplete as my balance sheet
balances remains unmatched, this could be because my cash and cash
equivalent year end balances are not similar to the minimum kept in
the debt schedule.
2) The iteration solution you have provided above hasn’t worked, as it
was already enabled.
3) How have you gauged the interest percentages for both revolving
credit and cssh balances.IF 10K, please direct specifically.
In nutshell, i would like you to please check my worksheet. If yes, i
would be mailing it to you.
Thank you!
103. AM says
Ehi Dude, this is amazing.
I dont have received an model yet. Excited to start.
* Dheeraj says
Please check you email for the template. Did you fill the download
Colgate model form. Else, please mail me. Will send you the model.
Cheers,
Dheeraj
104. Sridevi says
Hello Dheeraj Sir,
Thank you for your kind and generous free course for financial modeling, I
am looking for a come back in finance career, it is very much useful in
both brushing up basics and in-depth analysis. It will be a great favor to
me, if I can get excel sheets e-mailed.
Thank you.
* Dheeraj says
Hi Sridevi,
Did you signup for the Download Colgate Model at the start of this post.
If yes, you should get it automatically. If you have still not recieved,
i will email you the models.
thanks,
Dheeraj
105. Shreshtha Gupta says
HI Dheeraj,
This tutorial seems to be really informative. Do you happen to have a
video tutorial of the same?
Many thanks,
Shreshtha
* Dheeraj says
Hi Shrehtha,
Many thanks!
Unfortunately, i have not prepared financial modeling video tutorials as
of now.
Best,
Dheeraj
106. Jaydev says
Hi Dheeraj ,
First of all thanks for the detailed model.
I have doubt related to Depreciation and Capex calculation.
In 2014 the calculation of CAPEX(Building improvements) is 1.7 and in 2015
it is 3.5 and 1.8 .
I want to know why is 3.5 taken into calculation.
Please let me know.
-Thanks
Jaydev
* Dheeraj says
Hi Jaydev,
This is a mid year convention. If the capex installation took place on
Day 1 of the year, then you must charge full depreciation. However,
while estimating, we do not know the day when Capex was installed so we
take Mid-Year convention (capex installed at the middle of the year).
With this we should charge half the depreciation for the installation
year (not the full year).
Hope this helps,
Dheeraj
* Helen says
Hi Dheeraj,
I’m so lucky to find this free financial modeling course. Thank you for
your amazing work done! It is very detailed and covered almost
everything.
Can you please send me the template? The link seems not working.
Thousands Thanks again!
Helen
* Dheeraj says
Thanks Helen! I am glad you loved this Financial Modeling course.
I have sent you the templates through mail. Please check.
Thanks,
Dheeraj
107. Lydia says
Do you of a certificate of completion for this training, we need 45 hours
of financial management training?
* Dheeraj says
Hi Lyndia,
Unfortunately there is no certificate for this Free Financial Modeling
Training. I didn’t get your second question about 45 hours of financial
management training.
thanks,
Dheeraj
108. Michael Pang says
How did you get the 4.0% in YoY growth (as well as the other numbers)? in
segmental growth? I don’t see how you calculated that and it doesn’t seem
to be an average of the past numbers.
* Dheeraj says
Hi Michael,
for the sake of convenience, I have taken this as some number based on
the ball park historical numbers. In actual scenarios, we need to more
industry research to put the growth numbers.
Thanks,
Dheeraj
109. jorge says
I completed the exercise and my balance sheet balances where ( A = L + OE)
for the first 4 years, but the 5th (last) year it shows an imbalance of
0.0000000000000127
Would an error like this be acceptable?
Thank you
* Dheeraj says
Hey Jorge,
This kind of error is completely acceptable. it is ~0 for all practical
purposes.
Great to see that you were able to prepare the full financial model. why
don’t you try some other company now?
Cheers,
Dheeraj
110. Tinafaus says
Please is there a video for the financial modelling tutorials? I am
finding it difficult to understand
* Dheeraj says
Hi Tinafaus,
Unfortunately, i have not yet prepared the video tutorials. Please let
me know if you have any questions.
Thanks,
Dheeraj
111. Kimunga Kimani says
Thank you very much for sharing. Quite insightful
* Dheeraj says
My Pleasure Kumunga :-)
* harry g says
Hi Dheeraj
Thanks for posting, this is very useful indeed.
I can’t seem to find the ratio analysis section of the spreadsheet,
could you please send it to my email when you get a chance? Or point me
towards it on the site.
Also I was wondering how long it takes you approximately to complete a
financial model similar to this one? Just so as I can see if I am at
pace :)
Cheers
Harry
* Dheeraj says
Hello Harry,
Thanks for your question. I received your email as well. Ratio
analysis sheet is a bit dated. I am working towards updating the same.
Will send you shortly.
Assuming that you worked through this Colgate Financial model, any new
Financial Model may take anything between 1 day to 10 days. It depends
on how robust modeling you are looking at. Normally the first
independent financial model is the most challenging and exciting!
Thereafter, it will be easier for you to interlink and fine tune other
models.
All the very best with modeling,
Cheers,
Dheeraj
* Fabian Lima says
Hi Dheeraj,
Could you send me the Ratio Analysis too? Thank you!
* Dheeraj Vaidya says
Hi Fabian,
I am yet to work on the Ratio Analysis template. Will update you
on this.
Thanks,
Dheeraj
112. Ali Ikhlaq says
Hi Dheeraj,
I have learn financial modelling back two years and now forgot everything
but your course help me to remind everything. Dear i am doing job and
working as a Manager accounts in a manufacturing firm.
I want to ask from you that where online i can made the models for people
and sell so i can generate more income in my free time ?
* Dheeraj says
Hi Ali,
Ofcourse preparing financial models can be wonderful. You can use your
models commercially or as a base for financial advise.
Thanks,
Dheeraj
113. M says
Hi Dheeraj,
First, are the financial modelling for biotechnology sector and pharma
sector the same? Is it similar to a financial model done for your colgate
example, except you use a pharmaceutical company 10K and/or biotech
company 10K filing?
Is it possible to just purchase module 35 (Financial Modeling – Pharma
Sector) and 41 (Financial Modeling – Biotechnology Sector) of the advanced
financial modelling courses?
Thanks,
M
* Dheeraj says
Hi Mark,
More of less all financial models are prepared in a similar way. All
financial models start from the Income Statement and later move to
Balance Sheet and Cash Flows. Only the revenue and cost built up
statements and assumptions may change a bit depending on your
understanding on Pharma/Biotech sectors.
One exception to this is the bank models (e.g. JPMorgan) where balance
sheet is prepared and later on we move to the income statement.
Thanks,
Dheeraj
Thanks,
Dheeraj
114. Dusan says
Hi Dheeraj,
first thanks for this very helpful tutorial!
I have one question, why do you divide “Earnings per common share, basic”
and “Earnings per common share, diluted” with 2 in your template?
For example, in original Income Statement downloaded from Colgate site,
for these categories in 2007 we have 3.5 and 3.2, and in the template you
divide them with 2, so we have 1.675 and 1.6
* Dheeraj says
Hi Dusan,
I have divided this by 2 due to the stock split of Colgate. Since the
denominator increased by a factor of 2, the earnings per share should be
divided by 2 to get the correct picture.
Thanks,
Dheeraj
115. abhi says
Hi,
For the consolidated shares outstanding, i’m unable to figure out how you
arrived at the values- which are in the 900million range. As the annual
reports show them to be in the range of 400 million.
* Dheeraj says
Hi Abhishek,
It is due to the stock split announced by Colgate.
thanks,
Dheeraj
116. abhi says
Hi,
In leverage buyout models, would the integrated three statement model be
as advanced as this example?
* Dheeraj says
Yes Abhishek, they are generally advanced models
* Karthick says
I m not able to download. Can you share this to my emailid
KARTHICK_govind@ yahoo.co.in
Its wonderful for laymen like me
* Dheeraj says
Hi Karthick, I have emailed you the models.
Thanks,
Dheeraj
117. abhi says
Hi,
In the working capital schedule, how did you calculate the
(Increase)/Decrease in WC for 2009 of 429?
I understand how the 2010, 2011 etc.. values are arrived at. But for 2009,
since 2008 data is not shown – do you use some other method?
Thanks
* abhi says
Sorry, i figured it out.
118. ItsMe says
Hi Dheeraj,
Your website is excellent, I have used it many times as a reference for
modeling. I am also a member of eduCBA – I have the investment banking
bundle.
I am unable to balance a model – I have a constant difference, which
doubles every year. Would you be willing to take a look? Thanks.
* Dheeraj says
Sure. Please send the model.
119. Sevda says
Hello.
First of all I’d like to thank you for helping us to sharpen our finance
skills. I need help. I am looking for “Excel Worksheets and Solutions
to Exercises to Accompany Financial Modeling, fourth edition, Simon
Benninga”. If You have it or can help me to find it, I’d be very
grateful. It is very important for me to find it.
Thank you in advance and waiting your reply.
* Dheeraj says
Hi Sevda,
I do not have the these templates.
thanks,
Dheeraj
120. Raj says
Hello sir,
I am currently working as a QA for payment domain in an IT company. What
are your thoughts on pursuing this finance courses for a person like me?.
I am even interested in CFA although my interests are not aligned with the
job I do. I have a certification from University of Michigan about –
introduction to finance. That’s what made me get hooked into this field. I
am now planning to do MBA with this finance knowledge as my assets, as I
am already half way through your financial modelling course. I am willing
to learn to more and get involved in this field. Please help me in
understanding what would be the right way to go ahead
Regards,
Raj
* Dheeraj says
Hello Raj,
I think CFA is the right thing to start with. At least, do plan to give
CFA Level 1 ASAP. In addition, you can do these financial modeling
courses to solidify your concepts practically.
Hope this helps,
Dheeraj
121. Pratik Biyani says
Hello Dheeraj Sir,
First of all i would like to thank you for all the detailed courses and
the time that you have taken out to make them.
However, can you please send me the download link of the Ratio Analysis in
this course. Also, “Download the Colgate Palmolive Historical Model here”
is not working.
Thank you so much!
My EMAIL ID – pratikbiyani90@gmail.com
* Dheeraj says
Hi Pratik,
Will send you the historical financial model with ratio analysis soon.
Thanks,
Dheeraj
122. Milan says
Hi Dheeraj,
I have some problems to download the files. Could you please send it to my
mail directly?
Thanks in advance, best regards,
Milan
* Dheeraj says
Sorry for the inconvenience caused Milan. I have sent the models to your
email id.
Thanks,
Dheeraj
123. Kristein M says
Thanks Sir for this financial model article, it is quite useful & worthy
too to know how to make a financial model in an easy go. Thank you for
making it easy to understand its fundamentals properly.
* Dheeraj says
thanks Kristein! :-)
124. Saira Thomas says
Thank you for enlightening us with your great articles on finance. Your
articles are truly appreciable; they are easy to understand & grasp. This
financial modeling article is very interesting to know the performance of
the companies. Thank you for teaching through the mode of an article how
to prepare a financial model.
* Dheeraj says
Thanks Saira, please do let me know incase you have any questions!
Best,
Dheeraj
125. Vivian Dsouza says
Simply amazing. Dheeraj thanks a lot for your contribution, extremely
helpful. Providing a thorough knowledge on each and every step caught my
attention. Thanks again for the contribution, and yes all the new posts on
your site are simply awesome as well.
* Dheeraj says
Thanks Vivian! Do let me know if you have any questions.
126. Jyoti Sahani says
I am glad that i found this free financial modeling course on
wallstreetmojo. This is a complete step by step training in simple way.
Really helpful for anyone who is naive to financial modeling.
* Dheeraj says
Thank you Jyoti!
127. Mitesh Agarwal says
The training on preparing financial models that too for FREE is awesome. I
really appreciate the way it has been explained step by step and in a
concise manner. Looking forward to learn some other advanced modeling
lessons as well.
* Dheeraj says
Thank you Mitesh. The advanced modeling sessions are planned and are
definitely coming in 2016.
Cheers,
Dheeraj
128. Zoe says
This financial modeling guide is great. All concepts are explained to the
point and the explanation is crisp. I am now going to try making one
financial model. Thanks for the help!
* Dheeraj says
Hello Zoe,
I am glad you liked the financial modeling course. Please let me know if
you have any questions.
Thanks,
Dheeraj
129. Visakha Rajpal says
I am amazed that this free course has actually covered everything.
Specially the way in which each calculation and formula is explained. Use
of excel is done very well. Thanks Dheeraj for sharing the content
* Dheeraj says
thanks Visakha!
130. Joseph Dominic says
This is an amazing course and that to for free. The model is very well
explained. Kudos Dheeraj for sharing such a content of true Value
* Dheeraj says
thanks Joseph. Please do let me know if you have any questions.
131. Shreenath Iyer says
Thank you Dheeraj Sir for your informative article on Financial Modeling.
Truly your articles are amazing and helpful. The way you explain the
things through your articles is too good. I love your way of explaining
the things in the form of examples. I intend to know what does actually
financial modeling means your this article has helped me a lot in
understanding about financial modeling.
* Dheeraj says
thank you Shreenath :-)
132. Sushree Sawant says
This post is awesome Sir! This can really help freshers like me
understanding financial forecasting. Thanks a lot and keep providing
knowledge to us from your vast industry experience.
* Dheeraj says
thanks Sushree!
133. Jacque says
This was very helpful as I prepare for an interview where I might be asked
about financial modeling. I learned so much. Thank you for creating this
free course!
* Dheeraj says
Its my pleasure Jacque!
134. Tess says
Thanks Dheeraj for this tutorial. You made it appear simple. It looks
great! I also would like to request for an email of Solved and Unsolved
Colgate-Palmolive Financial Model.
Tess
* Dheeraj says
Hello Tess,
Many thanks. I have mailed you the financial modeling templates.
Best,
Dheeraj
135. sylvia says
Hi, could you kindly email me the colgate models, both the solved and
unsolved. Am unable to access. Thanks. My email is sylvianyakio@yahoo.com
* Dheeraj says
Hi Sylvia,
I just emailed you both the models.
thanks,
Dheeraj
* Stacey Zafiroff says
Hi Dheeraj– Can you email me both models too? Staceyzafiroff@gmail.com
Thanks so much. You are a guru! You must analyze the stock market with
impeccable precision. What’s your average annual return? I know it
must be high!
Also, could you analyze GoPro next? I’m interested to see what your in
depth analysis shows on this volatile, speculative stock. Thanks,
Stacey Zafiroff
* Dheeraj says
Thank you Stacey for the motivation :-). i have sent you the models
to your email id. Though i am not tracking GoPro, I will check and
get back to you if i can evaluate this stock.
Best,
Dheeraj
136. Pooja says
Sir I’m CA FInal student. I wish to join this course. can you guide me
* Dheeraj says
Hi Pooja,
You need to just download the Colgate Model (from the form at the start
of this post) and start learning financial modeling.
Happy Learning!
Thanks,
Dheeraj
* Pooja says
but i am confused about some other course. I am still thinking either
to join CFP or CFA. I am not sure that joining this course with CA
will be better for me?
137. Moh says
Outstanding work mate. None of my finance courses in B-school taught me
financial modelling / analysis better than the masterpiece that you have
created.
I have a couple of questions;
– Step 9C: How have you used the information “authorised the repurchase of
upto 50 Million shares of company’s common stock” into the model? I am not
able to understand the link.
– Step 9D: Where did you get the RSU figures of 1.46, 2.21, and 1.91. I
can’t find these figures on the 10-K report
Step 9F: RSU of 4.539 needs to be recognized over a weighted average
period of 2.2 years. So, shouldn’t we be recognizing 4.539/2.2 in 2014,
4.539/2.2 in 2015 as well. Why have we assumed 2.0 in 2015?
Thanks
* Dheeraj says
Hello Moh,
Thanks for the encouragement and sorry for the late replies. This
comment got dumped in the spam comments i received.
Step 9C – as the company statement said that they are authorized by
repurchase update 50 million shares, in the model, we just need to be
careful that we do not repurchase more than 50 million shares. If you
check 9C, you will note that each year, we are repurchasing around 30
million shares.
Step 9D – The RSU figures are mentioned in the summary table i provided.
its in the 10K. Search for “restricted stock units” and you will be
directed to this table.
Step 9F – good question, why i divided by 2.0 instead of 2.2 years that
was accounted for earlier. This is just an assumption that i have used
to keep the calculations simple.
Thanks,
Dheeraj
138. Junbeom Park says
Hi Dheeraj !
My name is Jun Park
Many thanks for your useful information, it is a boon for me.
I work for Utility in Korea and i am deeply involved in nuclear financing
So I Would like to learn financial modelling step by step
but i can’t download your all of sample modelling in your blog
could you email it to me ?
my email address : jaybee13102082@gmail.com
thank you,
Junbeom
* Dheeraj says
Hi Junbeom,
I have mailed you model. Let me know if you need anything else.
Thanks,
Dheeraj
139. Tuan Anh says
Hi you, thank you so much for your free course!
I just want to ask you where i can find the Solved of 2CDE, i didn’t see
it in IS.
Best,
TA
* Dheeraj says
Hi Tuan,
I realized that i didn’t provide those in the solution. I am writing a
separate note on this. Will update you on this shortly.
Thanks,
Dheeraj
140. Taca says
hi Dheeraj,
I took a finance course 10 years ago, do you think I need to take a
refresher course? It yes, can you please recommend something ( either an
online tutorial or a book ). I work as a senior accountant so I’m familiar
with most of it.
Thank you,
Taca
* Dheeraj says
Hi Taca,
Just curious to know what is your objective of taking a finance course?
I can guide you further based on your inputs.
Best,
Dheeraj
141. Bett says
Dheeraj,Thank you very much for taking time to share such insight in
financial modeling.Why do you subtract 1 from the base year in the
horizontal analysis formula. What do you call that.
Regards,
Bett
* Bett says
Dheeraj, I have known why. It is simply to arrive at the net movement.
Regards,
* Dheeraj says
thanks Bett!
142. PM says
Hello Mr. Dheeraj,
Thanks for the lovely tutorial.
My liquidity and solvency ratios are varying a little. I am unable to
identify the error. This is Step 2C. For example my inventory turnover
from Dec-09 onwards if 5.2, 5.2,5.4, 5.2 and 5.1. However urs is 5.3, 5.2,
5.6, 5.3 and 5.2. The formula used is Cost of sales from IS and
inventories from BS.
I await ur response.
Regards
PM
* Dheeraj says
Hi Pallavi,
I am writing a note on Ratio analysis where i am covering Colgate as an
example. Will keep you posted on this.
Thanks,
Dheeraj
* jorje says
this will be very helpful, i keep getting different numbers
143. Vijay says
Hi Dhiraj
Great Work. Could you help with Financial Modelling of a New Mfg. Co. with
Loans and Loan and Interest Repayment schedule and Depreciation Schedule.
* Dheeraj says
Hi Vijay,
Unfortunately, i do not have the manufacturing company financial model.
Thanks,
Dheeraj
144. Prashant says
HI,
Regarding the circular reference in step 10D.How do we go about getting
rid of this circular reference. I am getting “DIV” error.
Regards,
PN
* Dheeraj says
Hi Prashant,
This is important. Follow the following steps to remove the DIV error.
You cannot get rid of circular reference as it is inbuilt in the core
modeling exercise.
1. select the columns with DIV error and delete it.
2. Undo the delete
You should be able to see the corrected output.
Thanks,
Dheeraj
145. Martin says
Thats absolutely amazing! This is highly appreciated :)
* Dheeraj says
thanks Martin!
146. Dee says
Hi Dheeraj,
Thank you so much for publishing this – it is wonderfully written. I was
just wondering, would you mind including all the ratio calculations in the
solved template for verification purposes? As I couldn’t locate them
there. Cheers!
* Dheeraj says
Hey Dan,
I I am working towards writing another blog post completely dedicated to
Ratio Analysis. Will update you once its posted.
Thanks,
Dheeraj
147. Oleg says
Hi Dheeraj,
Thanks for an amazing opportunity of free practice
I have a quick question
In step 4E we have to populate 6 fiels in Working Capital Balances.
However, there are given only three drivers in Ratios&Assumption Section
which are
1. Other current Assets ( % of Net Sales )
2. Accrued Income Taxes ( % of COGS)
3.Other accruals ( % of COGS)
Therefore my question, how to come up with
a) Receivables
b)Inventories
c) Account payable
Thanks,Oleh
* Dheeraj says
Hi Oleg,
Sorry for this late reply. The comments got dumped in between the spam
messages i have been receiving. How do we come up with teh following –
a) Receivables – here we calculate the receivables turnover in days. We
note that the receivable days is between 34 – 39 days. So going forward,
we take an assumption that receivables days will be around 35 or so.
Based on this input of 35 days for colgate, we back calculate the
receivables. Please refer the Colgate excel sheet solutions for further
details.
b) Inventory – here we calculate inventory days and perform the same
approach described above.
c) Payables, we do the same thing, we calcluate the payable days and
perform the same approach described in a)
148. Ramesh says
HI,
your page is having tooo good information for modelling learners
* Dheeraj says
thank you Ramesh!
149. Jai says
Hi,
You have beautifully explained and demonstrated the flow of the FMCG
company model. Thanks for it. However, I wish to value a company which is
still in the nascent stage of development, i.e., the company does not have
any revenue from its product (its product are in the pipeline stage). then
in that case how you value such a company, like any early stage
pharmaceutical/biotech company listed in the stock exchange. Could you
please build such model?
Thanks.
* Dheeraj says
Hi Jai,
thank you. Currently i do not have such a model in place. A company that
does not have any revenue from its product till date will be valued on
the basis of how the growth may look like once they launch the product.
Cant detail things much here without knowing the whereabouts of the
company.
Thanks,
Dheeraj
150. malik jawad says
thanks a lot for the financial model training and it was great experiance.
it really help me a lot but in the end i fail to tie a balance sheet i
don’t know where i went wrong.
please can you help me out?? need further guidence…..
* Dheeraj says
Hi Malik,
Please email me the problems that you are facing in the financial
modeling exercise.
Thanks,
Dheeraj
151. Pawan says
Hi Dheeraj
Its amazing to go through this. Really great work.
Regards: Pawan
* Dheeraj says
Hi Pawan,
Many thanks :-)
152. Jorge Pierantozzi says
Hello Teacher Dheeraj!
Thx a lot for this website!!!
But I stock in the step 4E. I do not understand how do you project the
Receivables, Inventories, Accounts payable and Accrued income taxes. I
need some help here.
Regards,
Jorge Pierantozzi
* Dheeraj says
Sorry for this late reply. The comments got dumped in between the spam
messages i have been receiving. How do we come up with teh following –
a) Receivables – here we calculate the receivables turnover in days. We
note that the receivable days is between 34 – 39 days. So going forward,
we take an assumption that receivables days will be around 35 or so.
Based on this input of 35 days for colgate, we back calculate the
receivables. Please refer the Colgate excel sheet solutions for further
details.
b) Inventory – here we calculate inventory days and perform the same
approach described above.
c) Payables, we do the same thing, we calculate the payable days and
perform the same approach described in a)
153. Jorge Pierantozzi says
Hello Teacher
I’m quite loving your website, it has helped me alot! I well made!!!
In other hand, I’m Stock in the 4E step. I do not understand how do you
project the Receivables, Inventories, Other current assets, etc. Is with
the proponcional os Net sales and Ration Assumptions Drivers??? I’m not
reaching the same numbers that you projected.
Regards,
Jorge Pierantozzi
* Dheeraj says
Try checking the calculations again. the numbers should match.
154. Alaa says
Hi Dheeraj
Thanks for your generosity to the world.
How will a similar model work for a private firm?
I an trying to do a DCF for a private firm and am looking for benchmark
financial and DCF model for private firms.
Can you recommend any sources or do you have a reference model that we can
refer to?
Regards
Alaa
* Dheeraj says
Hello Alaa,
I am sorry i do not have such a model at this stage. However, stay
tuned. will update you soon on this.
Thanks,
Dheeraj
155. Anshul Agarwal says
Please send me your course details along with commercial over my mail id.
* Dheeraj says
Hey Anshul,
this is a free course. You just need to download the templates and start
learning!
All the best!
Dheeraj
156. Cedric Jirsell says
Great tutorial, thanks for the effort put in.
Some thoughts though, as we do calculate most areas that would be
classified as operating expenses, why not link up those once to the Rev
Estimation as well. As it is now the only thing determining the Net Income
estimation is an arbitrary estimation of COGS and OPEX. I might have
missed something in regards to this but would be glad if you could give
your thoughts on it, as Earnings would be an important factor to determine
share price.
I’m still working through mine as I have obviously messed something up to
my BS isn’t in balance…
* Dheeraj says
Hi Cedric,
You are right. Its just about how you think about certain assumptions.
For example, advertising expenses should move with Revenue (can see the
direct links) so Revenue linkages makes sense. If you are comfortable
with Revenue assumption linkages, you can also take that. I have seen
many analysts doing so.
How is your model coming along?
Thanks,
Dheeraj
157. Renee says
Hi Dheeraj,
A lot of thanks for sharing your knowledge! :) As a junior at college, I
really appreciate the precious hand-on lecture. I have a question though.
How did you calculate “Accounts Payable (days payable)” in working capital
chart?
Thank you very much!
* Dheeraj says
Hi Renee,
a) Receivables – here we calculate the receivables turnover in days. We
note that the receivable days is between 34 – 39 days. So going forward,
we take an assumption that receivables days will be around 35 or so.
Based on this input of 35 days for colgate, we back calculate the
receivables. Please refer the Colgate excel sheet solutions for further
details.
b) Inventory – here we calculate inventory days and perform the same
approach described above.
c) Payables, we do the same thing, we calculate the payable days and
perform the same approach described in a)
Thanks,
Dheeraj
158. Him says
Hi Prof Dheeraj,
Your Tutorial is really nice and I learnt a lot of the mechanisms from it.
One question. I have already tried to go through the model and I got stuck
during the equity section Step 9C to find out he implied share price.
However at that time, my income statement is still not finished as the
interest expense statistic is not ready and hence no EPS is derived. If I
reverse the step to calculate interest expense first, equity data and cash
flow from financing activities needs to be used. How should I solve this
problem? Many thanks!!!
Him
* Dheeraj says
Hello Him,
Thanks for your note. You need to carefully follow each step one by one
without skipping any. This also means that you have to follow the same
sequence as suggested in this tutorial. It will be great if you could
send me your Financial Model – i will have a look at it and see the
exact nature of the problem.
Best,
Dheeraj
* malik jawad says
thanks a lot for the financial model training and it was great
experiance.
it really help me a lot but in the end i fail to tie a balance sheet i
don’t know where i went wrong.
please can you help me out?? need further guidence…..
* Dheeraj says
Hello malik,
I can surely help you out on this. Please let me know the questions
that you have.
Best,
Dheeraj
159. Mark W says
Awesome financial modeling tutorial. I will be working on this provided
unsolved Colgate financial model and then hopefully be able to do my own
model of another company. Once completed I am going to try and show
interviewers what I have accomplished and hopefully land a financial
analyst entry level position.
* Dheeraj says
Hello Mark,
Many thanks! Do let me know if you run into any issue while practicing
financial modeling.
Best,
Dheeraj
160. أسامة ابراهيم says
i need to learn
161. Francis says
I really appreciate the effort you put in to come up with this very nice
tutorial. I have one main issue anytime i look at a financial model,
Please i would like to know how to check if the financial model built is
reliable in terms of the accuracy of the forecast.
Thanks
* Dheeraj says
Hi Francis,
Accuracy of the forecast can be done by revisiting assumptions and
checking the same with the publicly available resources. For example, in
the press releases, if the company management said that they will spend
$200million on capex, then in your model these numbers should match.
Likewise, in results and conference calls, management do provide their
guidance on key numbers like Revenue, profit etc – broadly these should
be in line with the estimates provided. Also, you may want to check the
consensus figures to check if your forecasts are in sync with other
research analysts or are high/low.
Thanks,
Dheeraj
162. Manish says
Hi Dheeraj,
Thanks a lot for the knowledge that you are furnishing through this
portal.
Request you to please assist how you forecast the numbers of revenue
growth and whats the best forecast method i can use for further
forecasting?
Please help…
* Dheeraj says
Hi Manish,
there is generally no best method to forecast revenues. Each depends on
the availability of data and your time. if you are ready to spend a
couple of days/weeks on forecasting revenues (like financial analysts),
then you may want to go as granular as possible for revenue forecasts.
For eg. FMCG companies forecast can be done geography wise, product wise
or both. If full scale data is available then you can do forecast on the
basis of each product (there may be 500+ products). Though such an
approach is time consuming but is often recommended as it more robust.
Thanks,
Dheeraj
163. Adam Chan says
This is really amazing stuff Dheeraj, you were able to break the financial
modeling concepts down into an extremely digestible form for ease of
learning, much appreciated! I was just wondering, is there a follow up
post on how to conduct a scenario analysis?
Once again, many thanks for your great work!
164. Raj Bhagat says
Hi Dheeraj, Thank you for this tutorial, Dheeraj, i need your guidance
pls, I am 32 years old,BCA graduate in job and interested in to be a
Equity research analyst. Please guide me.
Thanks
* Dheeraj says
Hello Raj,
Can you please let me know what help are you looking forward to.
thanks,
Dheeraj
165. Emmanuel says
quite invaluable stuff. did you leave a link for the videos?? or did i
miss something. otherwise I will say well done for making this free as
well.
166. David says
Dear Dheeraj,
I desire to learn to be a finance guru ALMOST like you. However problem is
i have no understanding and dont know where to start from. I really admire
you finance people but i have no knowledge and i am ready to learn and
hopefully get a CFA qualification for myself as it is not common here in
Ghana.
Can you advise on what i need to do and learn to at least be able to
understand what you have done inside out and get ready to do more complex
things? I love to project figures naturally so i trust i will enjoy
finance but i need to know how i can start understanding from scratch so i
can understand all about finance including NYSE, derivatives, options
reading and interpretations. I want a career in finance and set a company
later when i have money.
I hope to hear from you.
* Dheeraj says
Hello David,
Thanks for your kind words! I think getting a CFA charter will be first
step towards a career in Finance. I strongly recommend the same
considering your enthusiasm for Finance.
Best,
Dheeraj
167. ferdinand says
Thanks Mr Dheeraj sir, for sharing this knowledge. I need this for my
financial self study. Love the way you present all the information And the
memes too.
Ferdinand
* Dheeraj says
Many thanks Ferdinand!
* Dheeraj says
Thank you Ferdinand!
168. John Munene Nyagah says
I am interested in building my finance career experience in Financial
Modelling and i have found these materials as a good starting point. I am
impressed.
* Dheeraj says
Thanks John :-)
169. Naman Khanna says
Hi..
Dheeraj, very deep insight about the subject and the way you have put in
here is symphonic.
This is to supplement you already perfect share of wisdom herein above
that a Sub-head Shareholders’ Equity Schedule could be a shade more
elaborate.
Its the best available online material. People who have FM-phobia will
overcome there fears by the end of the page.
Humbled,
Naman Khanna
* Dheeraj says
Naman, Thank You so much for this feedback. I feel humbled.
170. Siva Kumar says
Dear Dheeraj,
Thanks very much for sharing the template. It is an excellent tool to
understand the topic financial modelling.
171. Priti says
Hi Dheeraj,
The model template is excellent. Can we get new sheet/ addendum wherein we
can find actual performance v/s projections..
172. Sulaiman says
kapan anda membuka training di Jakarta di tahun 2015 ini. Tolong contack
saya atau send a email. I will joint
173. M.A.R Shanas says
Thank you for this document.
174. Nao says
Hi Dheeraj,
Thank you very much for your work.
I’m afraid this is not an appropriate place but I have a question
regarding depreciation schedule in Alibaba IPO model.
You set the useful term of computer equipment and software as 3years,
however you continue to book the depreciation cost after the forth year.
For example, the capex of computer equipment in 2015 is 4,193. But the
total depreciation generated from this capex is 10,482 from Mar-15 to
Mar22.
This end up a negative number of PP&E in Mar-22, -1,813, cell in the same
sheet.
Also I think you are using wrong useful life for all of the three PP&E.
Although you set 4 years as the useful time of computer equipment in , you
apply 3 years in .
Could you show me the correct calculation?
175. Aseef Y says
Hi Dheeraj, Thank you so much for this financial model tutorial.
176. Mike says
Thanks for the great tutorial. I have doubt on the debt schedule. Why do
you consider dividends to be paid out before arriving at the cash
available for debt service? Doesn’t debt have preference over equity?
Regards
* Naman Khanna says
Dear Mike,
Dividends are returns for shareholders. Basis the performance of current
year dividend is distributed to shareholders. On the other hand cash
sweep is essentially a cash surplus after considering all the cash
operations for the year. Therefore, the preferential treatment to
service debt before equity does not arise here.
Its just like earmarking every possible cash outflow before deciding
upon to repayment.
Regards,
Naman Khanna
177. Neetesh Dohare says
HI Dheeraj,
Today I was going through the Colgate Financial Model. I realized one
thing, that the way the revenues of Colgate were forecasted is more kind
of an approximation. Simply taking AM/GM would limit our forecasting.
I am thinking of this, please let me know if the following approach will
correct or not?
What I believe is this -> Although the industry & the firm are at mature
stage, but I was thinking of another approach. The another approach is to
see through the sales-gdp regression analysis. If we find that the p value
is 0.05 & both the variables are dependent, one can find out the sales.
Even one can do the same thing with the market size as well & then have a
look at the Annual reports to see the future plans & adjust the computed
sales according to best case & worst case scenarios.
Is my approach kind of more complicated/incorrect, please let me know.
Thanks in Advance.
* Neetesh Dohare says
Hi Dheeraj,
Could you please help me out at my query.
* Dheeraj says
Hello Neetesh, Thanks for the reminder :-)
I see a point in taking the approach like you are suggesting. Would
call this as a mathematical approach to projecting revenues using
regression analysis. Unfortunately, I have not yet seen many models
where this technique is primarily used to project revenues. One reason
could be availability of the data to make it statistically viable.
Additionally, the approach I suggested is too simplistic. A research
analyst will do well do dig into the segments, product portfolios and
geographies to find the growth rate of sub parts and then add it up.
However, it takes lot of time and I avoided presenting those complex
forecasts at this stage.
Hope this helps.
Best,
Dheeraj
178. Carson says
Hi Dheeraj,
Thank you very much for putting this tutorial together. It is an amazing
guide… the best that I’ve seen.
I do have a quick question for you though – why is it that circular
references are allowed to exist in this model?
Specifically, I am referring to the circular reference found in the “Basic
Weighted Average Shares” and “Diluted Weighted Average Shares” on the
Income Statement. In step 8A, we were told to assumed that the future
number of basic and diluted shares will remain the same as they were in
2013. However, in step 10D, we linked the basic & diluted weighted shares
we calculated on the Shares Outstanding Schedule back to the Income
Statement.
Since we were only able to calculate 10D by assuming 8A, why do we plug
the calculated amount to replace the assumed amount?
179. Brian says
Any chance I can get a copy of the colgate example excel file. I would
really appreciate it. Thanks
* Dheeraj says
Hey Brian, you just need to download the excel sheet from the form
provided above. I will send you the excel sheets in this doesn’t work
for you.
180. Zach says
Note: Maybe I’m wrong, but I don’t think you mention to adjust
non-controlling interest on the balance sheet, so I ended up with an
imbalance for about half an hour until I realized that was the problem.
181. COSTAS N HADJIGAVRIEL says
THIRTY YEARS IN FINANCIAL SERVICES, I CAN HARDLY THINK OF A BETTER
TREATMENT OF FINANCIAL MODELING.
EXCELENT.
COSTAS
* Dheeraj says
Hello Costas, thank you for your kind words :-)
182. Mario says
Hi Dheeraj, first of all, thank you very much for your work.
I had a problem downloading the template, despite I´d tried several times
(using different email adress) the email didn,t arrive, neither to the
Inbox nor to the Junk box. Is it possible to download it in any different
way?
Thank you
183. Komal Malhotra says
there is a perfect blend of all the classroom teachings we have had during
graduation/post-graduation years to real life examples in terms of their
implementation and execution. i look forward to more such courses!
Keep up the good works !!
184. Santosh says
Hi Dheeraj, Its always been rewarding and intellectual feast for me to
learn things from your case studies. Kudos! Great Work on
Colgate-Palmolive.
* Dheeraj says
Thanks Santosh for your kind words. I am glad you liked the tutorial.
Best,
Dheeraj
* Mario says
Hi Dheeraj, first of all, thank you very much for your work.
I had a problem downloading the template, despite I´d tried several
times (using different email adress) the email didn,t arrive, neither
to the Inbox nor to the Junk box. Is it possible to download it in any
different way?
Thank you
185. sandeep says
Thank you dheeraj for such a great insight about financial
modeling.awesome work done by you.
* Dheeraj says
Thank you Sandeep for your kind consideration. I hope you liked the
Financial Modeling Training Tutorial.
Best,
Dheeraj
186. Anna says
Thank you Dheeraj! Can you please answer my email about the Alibaba Group?
It was sent about 2 weeks ago. And just one more question…how do you
estimate the percentages for the further years? Like the growth rates
(4,0%; 1,0%; 1,0%; 10%; etc) at the Segmental Information (Income
Statement of Colgate).
187. Syed Zafarul Hasan says
Amazing work. Thanks for making it simple. Get back to you if need be.
188. Sudeep says
Thanks for sharing this well explained tutorial. Can you share financial
model related to Indian banking sector.
* Dheeraj says
Thanks Sudeep. At this stage i do not have a banking model, however, I
do plan to write about it in my coming posts.
189. Stephan says
Thank you very much. I really appreciate your expertise in breaking this
down to a very simple methodology. There is no limit to how far I can take
this. Thank you again.
* Dheeraj says
Many thanks Stephan :-)
I am glad you liked the tutorial. Hope this proves useful.
Best,
Dheeraj
190. Nidhi says
Thanks Dheeraj for such a wonderful explanation of financial modelling.
Can you share some sector specific template like banking and oil& gas and
important ratios that are covered in these sectors.
* Dheeraj says
Thanks Nidhi. I look forward to preparing a banking sector model pretty
soon. Will let you know about the same.
Best,
Dheeraj
191. Manohar says
wow, you made it so easy to understand…many Thanks Dheeraj
* Dheeraj says
My pleasure Manohar
192. Rachael says
Hi Dheeraj, thanks for this awesome tutorial. When i try to open the
Financial Model of Colgate, it gives me circular reference. Does the model
contain any errors or am i missing something?
* Dheeraj says
Hi Rachael, many thanks for the download. This financial model contains
circular references as the financial statements (Income statmements,
Balance Sheet and Cash Flows) are interlinked. Circular references come
when we link the Interest expense from the Debt Schedule to the Income
Statement. You can remove the error by going to
File->Options->Formulas->Enable iterative calculations.
Hope this helps,
Dheeraj
193. Harjot Singh says
Than you so much for sharing
* Dheeraj says
my pleasure Harjot!
194. Avinash Sharma says
Tthis was a great learning i would further like to learn and want to
connect with you
* Dheeraj says
Sure Avinash. You can reach me at dheeraj (at) wallstreetmojo(dot) com.
Best,
Dheeraj
195. Gates says
Greatly appreciated!
* Dheeraj says
thanks Gates!
* alex Wang says
Thanks for a clear ,useful framework for the fiancial modeling setting.
196. Nikola says
Thanks for sharing. Amazing work.
* Dheeraj says
Thanks Nikola for the appreciation :-)
197. Neeraj says
Wow Thanks for this stuff. So kind to share this complex – made it easy
financial Modelling tutorial
* Dheeraj says
Thanks Neeraj. Hope you enjoyed this Financial Modeling tutorial.
198. Vishesh N Singhi says
Many thanks Dheeraj sir for this financial modeling tutorial.
* Dheeraj says
My pleasure Vishesh :-)
199. Nick T says
This is an epic Financial Modeling Tutorial. Best I have ever read. I have
bookmarked this one and will get back to you once i try unsolved templates
200. Nick T says
This is an epic Financial Modeling tutorial. Bookmarked for reference.
Thank You. I will followup with more questions as i try the unsolved
template.
PRIMARY SIDEBAR
INVESTMENT BANKING RESOURCESLearn the foundation of Investment banking,
financial modeling, valuations and more.
JOIN WALLSTREETMOJO YOUTUBE
68.5K subscribers
FREE EXCEL RESOURCESLearn MS Excel right from scratch. Master excel formulas,
graphs, shortcuts with 3+hrs of Video.
JOIN WALLSTREETMOJO INSTAGRAM
FREE FINANCE MODELING COURSELearn Financial Modeling in Excel with this Step by
Step Guide (Colgate Case Study)
JOIN WALLSTREETMOJO LINKEDIN
FINANCE DICTIONARYLearn & Master Finance & Accounting with 5400+ Step by Step
Guides & Resources
FEATURED GUIDES
FOOTER
COMPANY
* About
* Reviews
* Contact
* Privacy Policy
* Terms of Service
* Cookie Policy
* Advertise with us
RESOURCES
* Blog
* Investment Banking Resources
* Financial Modeling Guides
* Excel Resources
* Accounting Resources
* Financial Statement Analysis
FOLLOW US
* YouTube
* Facebook
* LinkedIn
* Twitter
* Instagram
Copyright © 2023 . CFA Institute Does Not Endorse, Promote, Or Warrant The
Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are
Registered Trademarks Owned By CFA Institute.
Let's Begin!
Email ID *
By signing up, you agree to our Terms of Use and Privacy Policy.
x
Download Colgate Financial Modeling Templates (Solved/Unsolved)
Enter Email Address
X
Offer - Financial Modeling Bundle at 60% OFF | Offer Ends in Enroll Now
X