Financial Modeling in Excel

What is Financial Modeling in Excel?

Financial modelling 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. Such financial models are used in DCF Valuations, Mergers and Acquisitions, Private Equity, Project Finance and more.

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. This goes beyond the usual gibberish and explores practical Financial Modeling as used by Investment BankersInvestment Bankers Investment 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, I will take the example of Colgate Palmolive (2016 – 2020) and will prepare a fully integrated financial model from scratch.

This guide is over 5000 words+ and took me three weeks to complete. Save this page for future reference and don’t forget to share it :-)

Financial Modeling in Excel Training – Read me First

Step 1 – Download the Colgate Financial Model Template.

Download Colgate Financial Model Template

Learn Step by Step Financial Modeling in Excel

Step 2 – Please note you will get two templates – 1) Unsolved Colgate Palmolive Financial Model 2) Solved Colgate Palmolive Financial Model

Step 3- You will be working 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, then do have a 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

How to Build a Financial Model in Excel?

Let us look at how a financial model is built from scratch. This detailed financial modeling guide will provide you with 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 Statement, Balance Sheet, 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. I can understand that this may not be clear now; however, you will realize that 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 Statement The 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 Sheet A 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 depreciationDepreciation Depreciation 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 Equity Shareholder’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 schedule, etc.
The different schedules are linked to the core statements upon their completion.
In this financial modeling guide, we 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 Financial Modeling Guide is to prepare the Historicals.

Download Colgate’s 10K Reports

“Financial models are prepared in excel, and the first steps start with knowing how the industry has been doing in the past years. Understanding the past can provide us with valuable insights related to the future of the company. Therefore the first step is to download all the financials of the company 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 statements data is available. However, for the purpose of Financial Modeling in excel, the recommended dataset is to have the last 5 years of financial statements. Please download the last 3 years of the annual reportAnnual Report An 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.
Many times, these tasks seem too boring and 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 that you are required to do only once for each company and also, populating the historicals helps an analyst understand the trends and financial statementsFinancial Statements Financial 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, download the data and populate the data (even if you feel that this is 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 our Part 1 of the series – Ratio AnalysisRatio Analysis Ratio 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. It should always be included as part of any financial analysis. Here, percentages are computed in relation to net sales, which are considered to be 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 Excel Percentage 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 powers of purchasing. In effect, this analysis indexes the reports and compares the evolution of these over time. As with the vertical analysisVertical Analysis Vertical 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 will surface that need to be investigated and complemented with other financial analysis techniques. The focus is to look for symptoms of problems that can be diagnosed using additional methods.

Let us look at the Horizontal analysis of Colgate.

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. 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. This 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 modeling of fixed and variable costs in multi-product companies and takes into account price vs volume movements.
Unit Volume, Change in Volume, Average Price, and Change in Price: This method is appropriate for businesses that have 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 dynamics 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, or 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 the purchase of additional capacity, for example, or to determine 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, service, and add-ons, etc.). Examples include classic razor-blade businesses and businesses like computers where sales of service, software, and upgrades are essential. 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, revenue per employeeRevenue Per Employee Revenue 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 the basis of stores (old stores plus new stores in each year) and revenue per store.
Occupancy-factor-based: This approach is applicable to airlines, hotels, movie theatres, and other businesses with low marginal costs.

Projecting Colgate Revenues

Let us now look at Colgate 10K 2020 report. We note that in the income statement, Colgate has not provided segmental information; however, as a piece of additional information, Colgate has provided some 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 on the basis of this available data. We will use the sales growth approach across segments to derive the forecasts. Please see the below picture. 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. Total Net sales are the sum 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 really the minimum acceptable in most cases, and permits only partial analysis of operating leverageAnalysis Of Operating Leverage Operating 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 separate schedule: This approach is the minimum necessary for sensitivity analysisSensitivity Analysis Sensitivity 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. Let us have a relook at the vertical analysis –

Since we have already forecasted Sales, all the other costs are some margins of this 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. We can look at forecasting the margins on this basis.
Likewise, Selling, General & Administrative ExpensesAdministrative Expenses Selling, 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.

For calculating the provision for taxes, we use the Effective Tax Rate assumption.

Also, note that we do not complete the “Interest Expense (Income)” row as we will have a relook the Income Statement at a later stage.
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 has already been 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 are to be followed for 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 Capital The 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 Equivalents Cash 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 usually assumed 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 the project the CapexCapex Capex 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 project the Depreciation and Assets figures.

source – Colgate 10K 2020 Page – 72

Depreciation and Amortization is not provided as a separate line item; however, it is included in the cost of sales
In such cases, please have a look at the Cash flow statements where you will find the Depreciation and Amortization Expense. Also, note that the below figures are 1) Depreciation 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

In order to forecast Capital expenditure, there are various approaches. One common practice is to look at the Press Releases, Management Projections, MD&A to understand the company’s view on future capital expenditure
If the company has provided guidance on future capital expenditure, then we can take those numbers directly.
However, if the Capex numbers are not directly available, then 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 restatementsRestatements A 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

We note that Colgate has not explicitly provided a detailed breakup of the Assets. They have instead clubbed all assets into Land, Building, Machinery, and other equipment
Also, useful lives for machinery and equipment is provided in range. In this case, we will have to do some guesswork to come to the average useful life left for the assets
Also, guidance for useful life is not provided for “Other Equipment.” We will have to estimate the useful life for 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 Land The 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.
For calculating the depreciation from building improvements listed on the asset, we use the simple Straight Line Method of depreciationStraight Line Method Of Depreciation Straight 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.
For calculating future depreciation, we first transpose the Capex using the TRANSPOSE Function in ExcelTRANSPOSE Function In Excel The 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 Building Depreciation 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 Building Improvements Asset already listed on the balance Sheet + depreciation from the future Building improvements

The above process for estimating depreciation is used to 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 out 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 of the years

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. We have two broad categories to consider here – 1) GoodwillGoodwill In 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 the Goodwill for future years.
However, Goodwill is subject to impairment tests annually, which are performed by the company itself. Analysts are in no position to conduct such tests and prepare estimates of impairments.
Most analysts don’t project goodwill; they just keep this constant, which we will also do in our case.

Forecasting Other Intangible Assets

As noted in Colgate’s 10K Report, the majority 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 expense.
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. This 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 Taxes Deferred 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 Sheet Assets 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 have kept the long term assets and liabilities constant, then 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 go back and relook at the Income Statement
Populate the historical basic weighted average shares and diluted 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.

With this, we are ready to move to our next schedule i.e., 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 Buyback Share 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 stock 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 we can see from 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 of Repurchased should be referenced from the cash flow statementsCash Flow Statements A 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 Repurchase Accelerated 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 repurchase historically. This is calculated as the Amount Repurchased / Number of shares.
Calculate the PE multiplePE Multiple The 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 made any official announcement of how many shares they intend to buyback. 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

To find the number of shares repurchased, we need to assume the Share Repurchase Amount. Based on the historical repurchase amount, I have taken this number like $1,500 million for all the future years.
To find the number of shares repurchased, we need the projected implied share price of the potential buyback.
Actual share price = assumed PE multiplex EPS.
Future buys back PE multiple can be assumed 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, I 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

From the summary of common stock and shareholder’s equity, we know the number of options exercised 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 Price Exercise 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 Options Stock 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.

Putting these numbers in our options data below, we note that the option proceeds are $504 million in 2021. I have assumed that 7 million options are exercised each year.

Stock Options: Forecast Restricted Stock Unit Data

In addition to the stock options, there are Restricted Stock UnitsRestricted Stock Units Restricted 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 given to the employees and are awarded and vested at the end of each of the three year performance period.

Colgate 2020 – 10K, Page 99

Populating this data in the Restricted Stock Units dataset

The restricted stock units are projected to be (8.65/3.0 years) i.e. 2.88 million going forward.

Also, have a look at the Treasury Stock MethodTreasury Stock Method Treasury 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 of dividends paid, we can find out the Dividend payout ratio = Total Dividends Paid / Net Income.
I 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%. 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 both 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 paid, 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 Oustanding Schedule. Summary of Shares Outstanding Schedule

Basic Shares – actual and average
Capture past effects of options and convertibles as appropriate
Diluted Shares – 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 Outstanding Outstanding 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: This schedule is commonly integrated with the Equity Schedule

Input the historical numbers from the 10K report

Shares issuedShares Issued Shares 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 Shares Weighted 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 & 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 for us to update the same in the Income Statement.
Link up forecasted diluted weighted average shares outstandingWeighted Average Shares Outstanding Weighted 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 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

It is important for us to fully completed 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 Financing Cash 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 & 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 each year.

Skip Long Term Debt Issuance/ Repayments, Cash available for Revolving Credit Facility and Revolver section for now

From Colgate’s 10K report; we note the available details on 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 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 implied interest rate. Multiply the average long term debt with 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 Debt Current 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

If there is any discrepancy, then we need to audit the model and check for any linkage errors

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!

Comments

Ravi Kumar says

Thanks sir, excellent work.
- Dheeraj Vaidya says
  
  Thanks for your kind words!
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!
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.
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.
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.
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.
Эльнур says

Добрый день, Dheeraj.

Спасибо за Ваш замечательный сайт и интересный обучающий материал.
Прошу направить электронные модели по почте, так как ввод электронной почты в форму запроса не привел к отправке файлов.

Заранее спасибо.
- Dhanashire Rajesh says
  
  Please check your email. I’ve just sent the model.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
pv says

Not yet received the Colgate Templates, Please email
- Rajesh Dhanashire says
  
  Please check your email. I’ve just sent the model.
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.
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.
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.
Terencek says

Hey, great article. Just wondering where i can get the template. Thanks!
- Dheeraj Vaidya says
  
  just mailed the model. Please check.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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!
shao says

Great article!
Could I have the excel template?
- Dheeraj Vaidya says
  
  just mailed the model. Please check.
Akintayo Alo says

Hi,

Please assist by sending the excel file.
- Dheeraj Vaidya says
  
  just mailed the model. Please check.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
Alexander Santibanez says

Do you have the template for this example?
- Dheeraj Vaidya says
  
  just mailed the model. Please check.
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
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.
Hezekiah Mulehse says

Hi Dheeraj . This is very instructive and largely straightforward to follow . Many thanks
- Dheeraj Vaidya says
  
  thanks Hezekiah!
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.
Kaushik says

Excellent article.
- Dheeraj Vaidya says
  
  thanks Kaushik!
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.
SHREYANS JAIN says

Great inisght can you mail me the template
- Dheeraj Vaidya says
  
  Please check your email. I’ve just sent the model.
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.
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.
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.
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.
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.
Joshua says

Excellent! what a great work
- Dheeraj Vaidya says
  
  thanks Joshua!
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.
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
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.
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.
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.
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.
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.
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.
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
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.
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.
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.
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
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
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
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
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
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
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.
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
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
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
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
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
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
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
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
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
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.
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!
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
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
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!
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
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.
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
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
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
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
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! :-)
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
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.
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!
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
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
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!
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.
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 :-)
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!
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!
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
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
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?
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
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
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
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
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!
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
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
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
Martin says

Thats absolutely amazing! This is highly appreciated :)
- Dheeraj says
  
  thanks Martin!
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
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)
Ramesh says

HI,
your page is having tooo good information for modelling learners
- Dheeraj says
  
  thank you Ramesh!
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
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
Pawan says

Hi Dheeraj
Its amazing to go through this. Really great work.

Regards: Pawan
- Dheeraj says
  
  Hi Pawan,
  
  Many thanks :-)
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)
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.
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
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
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
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
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
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
أسامة ابراهيم says

i need to learn
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
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
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!
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
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.
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
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!
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 :-)
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.
Siva Kumar says

Dear Dheeraj,

Thanks very much for sharing the template. It is an excellent tool to understand the topic financial modelling.
Priti says

Hi Dheeraj,

The model template is excellent. Can we get new sheet/ addendum wherein we can find actual performance v/s projections..
Sulaiman says

kapan anda membuka training di Jakarta di tahun 2015 ini. Tolong contack saya atau send a email. I will joint
M.A.R Shanas says

Thank you for this document.
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?
Aseef Y says

Hi Dheeraj, Thank you so much for this financial model tutorial.
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
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
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?
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.
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.
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 :-)
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
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 !!
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
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
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).
Syed Zafarul Hasan says

Amazing work. Thanks for making it simple. Get back to you if need be.
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.
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
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
Manohar says

wow, you made it so easy to understand…many Thanks Dheeraj
- Dheeraj says
  
  My pleasure Manohar
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
Harjot Singh says

Than you so much for sharing
- Dheeraj says
  
  my pleasure Harjot!
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
Gates says

Greatly appreciated!
- Dheeraj says
  
  thanks Gates!
- alex Wang says
  
  Thanks for a clear ,useful framework for the fiancial modeling setting.
Nikola says

Thanks for sharing. Amazing work.
- Dheeraj says
  
  Thanks Nikola for the appreciation :-)
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.
Vishesh N Singhi says

Many thanks Dheeraj sir for this financial modeling tutorial.
- Dheeraj says
  
  My pleasure Vishesh :-)
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
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.

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST