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 BankersInvestment banking is a specialized banking stream that facilitates the business entities, government and other organizations in generating capital through debts and equity, reorganization, mergers and acquisition, etc.read more and Research Analysts.

In this Free Financial Modeling Excel Guide, 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.

Please note the following –

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.

Colgate 10K - Download

Create the Historical Financial Statements Worksheet

Colgate Income Statement with historical populated

Colgate Historical Income Statement

Colgate Balance Sheet Historical Data

Colgate Historical Balance Sheet

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 AnalysisRatio analysis is the quantitative interpretation of the company's financial performance. It provides valuable information about the organization's profitability, solvency, operational efficiency and liquidity positions as represented by the financial statements.read more

Vertical Analysis of Colgate

On the income statement, the vertical analysis is a universal tool for measuring the firm’s relative performance from year to year in terms of cost and profitability. 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.

Colgate Ratio Analysis - Vertical Analysis

Horizontal Analysis of Colgate

Horizontal analysis is a technique used to evaluate trends over time by calculating percentage increases excel 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 AnalysisVertical analysis is a kind of financial statement analysis wherein each item in the financial statement is shown in percentage of the base figure. The formula is: (Statement line item / Total base figure) X 100read more methodology, issues 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.

Liquidity Ratios of Colgate

Colgate Liquidity Ratios

Also, have a look at this detailed article on Cash Conversion CycleCash Conversion CycleThe Cash Conversion Cycle (CCC) is a ratio analysis measure to evaluate the number of days or time a company converts its inventory and other inputs into cash. It considers the days inventory outstanding, days sales outstanding and days payable outstanding for computation.read more.

Operating Profitability Ratios of Colgate

Profitability ratiosProfitability RatiosProfitability ratios help in evaluating the ability of a company to generate income against the expenses. These ratios represent the financial viability of the company in various terms.read more a company’s ability to generate earnings relative to sales, assets, and equity

Colgate Profitability Ratios

Risk Analysis of Colgate

Through Risk AnalysisRisk AnalysisRisk analysis refers to the process of identifying, measuring, and mitigating the uncertainties involved in a project, investment, or business. There are two types of risk analysis - quantitative and qualitative risk analysis.read more, we try to gauge whether the companies will be able to pay their short and long-term obligations (debt). We calculate leverage ratiosLeverage RatiosDebt-to-equity, debt-to-capital, debt-to-assets, and debt-to-EBITDA are examples of leverage ratios that are used to determine how much debt a company has taken out against its assets or equity.read more that focus on the sufficiency of assets or generation from assets. Rates that are looked at are

Colgate Financial Risk Ratios

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 EmployeeRevenue Per Employee is the ratio of total revenue over total number of employees in a particular accounting period. It gives an idea about how the business performed.read more (often based on billable hours). More detailed models will include seniority and other factors affecting pricing.
  • Store, facility, or Square footage based: Retail companies are often modeled based on 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

Colgate Segment Information

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.

Colgate - Revenue Projections

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.

Colgate - Revenue Projections - complete

Costs Projections

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 –

Colgate Cost Financial Model - Part 1

Colgate Cost Financial Model - Part 2

Using the above margins, we can find the actual values by back calculations.

Colgate Cost Financial Model - Part 3

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

Colgate Cost Financial Model - Part 4

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 

Colgate - Workng Capital - Part 1a

Reference the Balance Sheet Data related to working capital

Colgate - Workng Capital - Part 1b

Calculate the Turnover Ratios

  • Calculate historical ratios and percentages
  • Use the ending or average balance
  • Both are acceptable as long as consistency is maintained

Colgate - Workng Capital - Part 3

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

Colgate - Workng Capital - Part 4

Project the future working capital balances

Colgate - Workng Capital - Part 5

Calculate the changes in Working Capital

  • Arrive at Cash Flows based on individual line items
  • Ensure signs are accurate!

Colgate - Workng Capital - Part 6

Link up the Working Capital Forecasts to the Balance Sheet

Colgate - Working Capital - Part 7

Link Working Capital Items to the Cash Flow Statement Colgate - Working Capital - Part 8

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 CapexCapexCapex or Capital Expenditure is the expense of the company's total purchases of assets during a given period determined by adding the net increase in factory, property, equipment, and depreciation expense during a fiscal year.read more of Colgate and project the Depreciation and Assets figures.

Colgate 10K - Depreciation

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)

Colgate 10K - Depreciation in Cash flow statements

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

Financial Modeling Depreciation Schedule Excel - Part 1

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.

Financial Modeling Depreciation Schedule Excel - Part 2

Reference Past Information and Calculate Net PP&E

Financial Modeling Depreciation Schedule Excel - Part 3

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 10K - Depreciation Policy

Colgate 2020 – 10K, Page 79

Below is the breakup of 2012 and 2013 Property, Plant, and Equipment Details

Colgate 10K - Depreciation Breakup

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

Financial Modeling Depreciation Schedule Excel - Part 4

Estimate the Depreciation of Assets

Financial Modeling Depreciation Schedule Excel - Part 5

Total Depreciation of BuildingDepreciation Of BuildingDepreciation of building refers to reducing the recorded cost of a building until the value of the structure either becomes zero or reaches its salvage value. In addition, it helps to map the revenue in the form of lease rental generated during the corresponding expenses.read more Improvement =  depreciation from the Building Improvements Asset already listed on the balance Sheet + depreciation from the future Building improvements

Financial Modeling Depreciation Schedule Excel - Part 6

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)

Financial Modeling Depreciation Schedule Excel - Part 7

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

Financial Modeling Depreciation Schedule Excel - Part 8

Link the Net PP&E to the Balance Sheet

Financial Modeling Depreciation Schedule Excel - Part 9

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) GoodwillGoodwillIn accounting, goodwill is an intangible asset that is generated when one company purchases another company for a price that is greater than the sum of the company's net identifiable assets at the time of acquisition. It is determined by subtracting the fair value of the company's net identifiable assets from the total purchase price.read more and 2) Other Intangibles.

Forecasting Goodwill

Amortization - Goodwill from Colgate 10K

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.

Amortization Schedule - Linking Goodwill a

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

Other Intangible - from Colgate 10K

Colgate 2020 – 10K, Page 88

Amortization - Other Intangible Assets - 1

Calculate Ending Net Intangibles

Amortization - Other Intangible Assets - 2

Ending net intangibles are linked to the “Other Intangible Assets.”

Amortization - Other Intangible Assets - 3

Link Depreciation and Amortization to Cash Flow Statements

Amortization - Other Intangible Assets - 4

Link Capex & Addition to Intangibles to Cash flow statements

Amortization - Other Intangible Assets - 5

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 TaxesDeferred income tax is a balance sheet item that can be either a liability or an asset since it is a difference in income recognition between the firm's accounting records and the tax law, resulting in the company's income tax due being different than the total tax expense reported.read more (liability and assets), Other investments, and other liabilities.

Reference the historical data from the Balance Sheet

Also, calculate the changes in these items.

Other Long Term Financial Modeling - Part 1

Forecast the Long Term Assets and Liabilities

Other Long Term Financial Modeling - Part 2

Reference Other Long Term Items to the Balance Sheet

Other Long Term Financial Modeling - Part 3

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.

Other Long Term Financial Modeling - Part 4

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.

Completing the Income Statement - Part 1

Calculate Basic and Diluted earnings per share.

With this, we are ready to move to our next schedule i.e., Shareholder’s Equity Schedule.

Completing the Income Statement - Part 2

Step 9 – Financial Modelling – Shareholder’s Equity Schedule

The next step in this Financial Modeling in Excel Training is to look at the Shareholder’s Equity Schedule. The primary objective of this schedule is to project equity-related items like Shareholder’s Equity, Dividends, Share buybackShare BuybackShare buyback refers to the repurchase of the company’s own outstanding shares from the open market using the accumulated funds of the company to decrease the outstanding shares in the company’s balance sheet. This is done either to increase the value of the existing shares or to prevent various shareholders from controlling the company.read more, Option Proceeds, etc.

Shareholders Equity Schedule - Part 1

Colgate’s 10K report provides us with the details of common stock and treasury stock activities in the past years, as shown below.

Colgate Shares Repurchased 10K

Colgate 10K 2020 – Page 97

Share Repurchase: Populate the historical numbers 

Also, have a look at Accelerated Share RepurchaseAccelerated Share RepurchaseAccelerated share repurchase (buyback) is a strategy adopted by a publicly-traded company to acquire its outstanding shares in the market from the clients in large blocks via an investment bank.read more.

Shareholders Equity Schedule - Part 2

Share Repurchase: Calculate the PE multiple (EPS multiple)

Shareholders Equity Schedule - Part 3

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 Share repurchase plan

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

Colgate Valuation relative to industry

  • 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.

Shareholders Equity Schedule - Part 4

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 Historical Stock Options Exercised

Colgate 2020 – 10K, Page 97

Colgate Stock Option Proceeds

Colgate 2020 – 10K, Page 76

Also, note that the stock optionsStock OptionsStock options are derivative instruments that give the holder the right to buy or sell any stock at a predetermined price regardless of the prevailing market prices. It typically consists of four components: the strike price, the expiry date, the lot size, and the share premium.read more have contractual terms of eight years and vest over three years.

Colgate Stock Option Contractual Term

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 Stock Option Strike Price

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.

Shareholders Equity Schedule - Part 5

Stock Options: Forecast Restricted Stock Unit Data

In addition to the stock options, there are Restricted Stock UnitsRestricted Stock UnitsRestricted Stock Units or RSU can be defined as stock-based compensation that is issued as company’s stock to an employee. The company establishes vesting requirements based on the performance of an individual and the length of the employment.read more given to the employees and are awarded and vested at the end of each of the three year performance period.

Colgate Restricted Stock Units

Colgate 2020 – 10K, Page 99

Populating this data in the Restricted Stock Units dataset

Shareholders Equity Schedule - Part 6

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 MethodTreasury Stock Method is an accounting approach assuming that the options & stock warrants are exercised at the beginning of the year (or date of issue, if later) & proceeds from the exercise of these options & warrants are used to repurchase shares in the market. read more.

Dividends: Forecast the Dividends

  • Forecast estimated dividends using the Dividend Payout Ratio.
  • Fixed dividend outgo Per-share payout
  • From the 10K reports, we extract all past information on dividends.
  • With the information 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 –

Shareholders Equity Schedule - Part 7

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.

Shareholders Equity Schedule - Part 8

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.

Shareholders Equity Schedule - Part 9a

Link Ending Shareholder’s Equity to the Balance Sheet 

Shareholders Equity Schedule - Part 10

Link Dividends, Share repurchase & Options proceeds to CF

Shareholders Equity Schedule - Part 11

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

Input the historical numbers from the 10K report 

shares outstanding schedule - Part 1

Link share issuances & repurchases from the Share Equity Schedule.

Basic Shares (Ending) = Basic Shares (Beginning) + Share Issuances – Shares Repurchased.

shares outstanding schedule - Part 2

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.

shares outstanding schedule - Part 3

Link Basic & diluted weighted shares to Income Statement

shares outstanding schedule - Part 4

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
Colgate - Cash Flow from Operations

Calculate Cash Flow for Financing Activities

Colgate - Cash Flow for Financing Activities

Also, check out Cash Flow from Financing

Find net increase (decrease) in Cash & Cash Equivalents

Colgate - Calculate Net Change in Cash & Cash Equivalents

Complete the cash flow statements

Find the year-end cash & cash equivalents at the end of the year.

Colgate - Year end Cash

Link the cash & cash equivalents to the Balance Sheet.

cash flow statement - linking to the BS

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

Financial Modeling debt schedule - part 1

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

Financial Modeling debt schedule - part 2

From Colgate’s 10K report; we note the available details on Revolved Credit Facility

Colgate - Line of Credit

Colgate 2020 – 10K, Page 49

Also provided in additional information on debt is the committed long term debt repayments.

Colgate - Long Term Debt Obligations

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.

Financial Modeling debt schedule - part 3a

Link the long term debt repayments

Financial Modeling debt schedule - part 4

Calculate the discretionary borrowings/paydowns.

Using the cash sweep formula, as shown below, calculate the discretionary borrowings / paydown.

Financial Modeling debt schedule - part 5

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

Financial Modeling debt schedule - part 6

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

Financial Modeling debt schedule - part 7

Assume the interest rate on Long term debt based on implied interest rate. Multiply the average long term debt with the assumed interest rate

Financial Modeling debt schedule - part 8

Calculate Total Interest Expense = average balance of debt x interest rate

Find the Total Interest Expense = Interest (Revolving Credit Facility) + Interest (Long Term Debt)

Financial Modeling debt schedule - part 9

Link debt & Revolver drawdowns to Cash Flows 

Financial Modeling debt schedule - part 10

Reference Current and Long Term to Balance Sheet

Financial Modeling debt schedule - part 11

  • Link the Revolving Credit Facility, Long Term Debt, and Current Portion of Long Term Debt to the Balance Sheet

Financial Modeling debt schedule - part 12

Link Noncontrolling Interest from Income Statement

Financial Modeling debt schedule - part 13

Calculate the Interest Income using the average cash balance

Financial Modeling debt schedule - part 14

Link Interest Expense and Interest Income to Income Statement 

Financial Modeling debt schedule - part 15

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

Financial Modeling debt schedule - part 16

Recommended Articles

What next?

If you learned something new or enjoyed this Excel-based Financial Modeling, please leave a comment below. Let me know what you think. Many thanks, and take care. Happy Learning!

Reader Interactions

Comments

  1. Ravi Kumar says

    Thanks sir, excellent work.

    • Dheeraj Vaidya says

      Thanks for your kind words!

  2. Patrick Chebos says

    Thank you Dheeraj, this is an excellent piece of work and I have learnt a lot from it. Thank you once again and have a good day.

    • Dheeraj Vaidya says

      Thanks for your kind words!

  3. Lawrence Raphael says

    Hi – thank for this very useful course template, but I wasn’t able to download the Colgate model via the download box?
    Would be grateful if you could assist!
    Many thanks,
    Lawrence R

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  4. Abbey says

    Dheeraj, you are a blessing to those of us getting into financial modeling with this piece. Thank you so much. Can I get the model/templates?

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  5. Pragya Garkemukhu says

    Hello Sir,
    There is a problem in downloading colgate financial model. i have tried several times but dint receive the mail.

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  6. Elnur says

    Thank you Dheeraj for such a great insight about financial modeling. Awesome work done by you.

    I can’t download your all of sample modelling in your blog. Could you email it to me?

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  7. Эльнур says

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

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

    Заранее спасибо.

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  8. Harmeet Singh says

    Hello Sir, please share the model via email, i haven’t received it yet.

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  9. Marcel says

    Hi, Can you please email me the template? I am unable to download it.

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  10. Elnur says

    Hi, I would love to try this Colgate model but I can’t download it. How can I get the templates. I have put my email address in but it doesn’t work.
    Thanks

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  11. Muideen Abubakar says

    Hi Dheeraj, many thanks for this, you have just added a huge value. Meanwhile, it seemed the model failed to deliver to my mail, can you help me out with this please.

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  12. sanaa raoussi says

    Thank you Dheeraj for this great tutorial! Can you please send me the templates?

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  13. sanaa raoussi says

    Thank you dheeraj for this great turorial!
    Can youn please send me the templates?

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  14. Pranjal says

    Sir, I am unable to downlaod the model. Kindly email me the same.

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  15. Aayushi says

    Hi. thank you for the wonderful tutorial. However I’m unable to download any of the excels – solved unsolved models used through the tutorial. Could you please send me all the excels on the email id: aayushikk@gmail.com
    It’ll be very helpful. Thanks a lot again

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  16. Myrna says

    Hi,
    Could you please email me the Excel templates as they are failing to download?
    thanks a lot!
    Myrna

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  17. Olivier says

    Hello, I failed to download the model. I think the link is not available.
    Could you please send me one copy of the1) Unsolved Colgate Palmolive Financial Model, and 2) Solved Colgate Palmolive Financial Model
    Thank you so much.

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  18. Mukul Gupta says

    Thanks Dheeraj for sharing this. Could you please mail me the templates?

    • Dhanashire Rajesh says

      Please check your email. I’ve just sent the model.

  19. Andrew says

    Could you please mail me templates. Thanks in advance!

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

      • Aayushi Kukreja says

        Please email me the excel models as well. Not able to download them. Thanks for the help.

        • Dhanashire Rajesh says

          Please check your email. I’ve just sent the model.

  20. Alex says

    Hi,
    The content is great. Thank for everything you shared.
    Just one think; I can not get the Colgate model

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  21. Nidhi says

    Would love to go through this but like all others, don’t have the template.

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  22. Yimeng SUN says

    Hello sir, I failed to download the model.
    Could you please send me one copy?
    Thank you so much for sharing such helpful stuff.

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  23. Neeraj says

    Hi, I would love to try this Colgate model but I can’t download it. How can I get the templates. I have put my email address in but it doesn’t work.
    Thanks

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  24. Mickey says

    Hey Dheeraj

    Could I have the excel template….

    Also wrote earlier if you have any material on different types of valuation models…..

    Did not hear from you

    Thanks & await to hear from u

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  25. Ahmed-Sherif says

    Hello Dear,

    I tried to download the templates for the free model but I was not apply to receive them

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  26. Dima says

    Hi, great article. But I have not received email of templates, could you please send me it?
    Thanks

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  27. Delom says

    Hi Dheeraj. Thanks so much for the model tutorial. Kindly send me the model as i have signed up my email as well.

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  28. Evgeny says

    Hello! Great article, very useful. But I haven’t received the template by e-mail. Could you please forward it ti me?!

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  29. pv says

    Not yet received the Colgate Templates, Please email

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  30. Ana says

    Hi Dheeraj!
    I have trouble downloading the file. Can you send me the templates to my email?
    I really appreciated it.

    • Rajesh Dhanashire says

      Please check your email. I’ve just sent the model.

  31. fayaz says

    hi sir,
    I haven’t received the template in my mail
    Please forward it.

    • Dheeraj Vaidya says

      just mailed the model. Please check.

      • hitesh Japi says

        hi Dheeraj,

        I have not received your template in my email, could you please check

        Thanks
        Hitesh

        • Rajesh Dhanashire says

          Please check your email. I’ve just sent the model.

    • Noel ferrin says

      Fabulous explanations.

      Please do forward if you the model if you can

      Many thanks

      • Rajesh Dhanashire says

        Please check your email. I’ve just sent the model.

    • Jason Prignoli says

      Can you Please forward me the model

      • Rajesh Dhanashire says

        Please check your email. I’ve just sent the model.

  32. Dana Walker says

    I would love to try this Colgate model but I can’t download it. How can I get the templates. I have put my email address in but it doesn’t work.

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  33. Terencek says

    Hey, great article. Just wondering where i can get the template. Thanks!

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  34. Kato says

    Hi Dheeraj.

    i haven’t received the model in my email. Kindly send it.

    • Dheeraj Vaidya says

      just mailed the model. Please check.

      • Mohammed Sayani says

        Hi Sir
        I would like to practice on the Colgate model but I don’t have the file. Could you please send me the files

        • Rajesh Dhanashire says

          Please check your email. I’ve just sent the model.

  35. Ali says

    I have not received email of templates, could you please send me the files?

    Regards
    Ali

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  36. Ali says

    Thank you for this detailed and simple explanation. I have submitted my email id but did not receive template email yet. could you please help?

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  37. Carlina says

    Hi,
    I’ve tried to registered but was unable to get any email.Can you please send me the 2 template at vinamontinola@gmail.com and also the ratio analysis

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  38. Manideep says

    Hi Dheeraj sir,

    I am not able to get the financial model template even after signing up for download.It would be helpful to have good hand on of the above course.I request you to please look into it.

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  39. Helmi says

    Hello!

    I have submitted my email address and now waiting for the excel (solved & unsolved) :)

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  40. Anish says

    Hi,

    I am unable to download the models, haven’t received it despite numerous attempts.

    Request you to please share it with me.

    Thanks!

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  41. Smriti Jaiswal says

    Hi Dheeraj,
    First of all ,Thank you for the amazing blog.I Have been navigating through a lot of websites and blogposts but none have proved to be remotely helpful as this one!!! Your approach of covering both theoretical as well as practical application makes one ready to hit the ground running!!.I am trying to learn financial modeling and have been unable to download the colgate Palmolive financial model template in excel .Request you to kindly mail me a copy at my email id

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  42. Hemant Sultania says

    Hi Dheeraj, thank you so much for this wonderful explanation and deep dive into the world of Equity Research, could you please send me the templates? I didn’t receive it through the emails?

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  43. Sandeep Kumar Gupta says

    Dear sir,
    Indeed a good article, very informative and best one. I thank you for your efforts for writing this.

    • Dheeraj Vaidya says

      thanks Sandeep!

  44. shao says

    Great article!
    Could I have the excel template?

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  45. Akintayo Alo says

    Hi,

    Please assist by sending the excel file.

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  46. MH says

    Hi Dheeraj, just want to say that this tutorial is one of the best I’ve ever seen. Please do more of these in the future, I’m sure a lot of people are also interested.

    Also, is there any way that I can download the model as I cannot see any link to download it? Same goes for the Box and Alibaba models as well on your other posts.

    Thank you

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  47. bindumadhavi says

    sir,
    i want to first learn in demo class, so that i can analyze myself that i can able to course or not

  48. Raghu says

    Hi Dheeraj,

    Great post. Thanks for the effort.

    Is there any financial model developed for upstream oil and gas industry. It would be great to see something

    • Dheeraj Vaidya says

      Hi Raghu, i don’t have such a model.

  49. William Awuah says

    Hi Dheeraj,
    This is so helpful for the course that I’m currently enrolled in. Can you email me the exact template in PDF?
    My email address is wawuah@yahoo.com.

    Thank you in advance.

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  50. Gafar Adediran says

    Thank you for sharing your great knowledge.
    Please is this the same basis for building Actuarial model.
    I want to be able to develop an actuarial model

    • Dheeraj Vaidya says

      Hi Gafar, unfortunately, i don’t have an actuarial model.

  51. Ayushi says

    Really an amazing article. I have learnt a lot form it. Keep up the wonderful work as it really helped the upcoming professionals in the industry. Amazing job !!!!!

    It would be great if you share the template with me. Thanks

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  52. Yash says

    Hi.. Could you share me the template for a deep study further? The one with all the ratios and the detailed study ?

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  53. Raman says

    MOST AWESOME MATERIAL. I am from Booth MBA and not come across such valuable article. Can you please email the most updated copy

    Thanks much.

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  54. Johnavia Scott-Walton says

    This is amazing. I have been wanting to practice financial modeling since I last took a course in 2003.
    Do you still work with JP Morgan

    • Dheeraj Vaidya says

      Hi Johnavia, Thanks. I left JP Morgan in 2007 after falling in love with entrepreneurship and blogging.

  55. Ruby says

    Hi Dheeraj,

    This site is wonderful, glad I found it. Could you please email me the deep analysis template as the template I downloaded seems missing some parts. Thank you!

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  56. Alexander Santibanez says

    Do you have the template for this example?

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  57. Daniel Merediz Jimenez says

    Sir Dheeraj, I actually appreciate what you are doing for all of us. This blog is amazing, and I’ll keep going learning as much as I could from you. I hope to be a good analyst like you, thank you for all!!!

    • Dheeraj Vaidya says

      thanks Daniel. Glad you liked it.

      • Anurag Agrawal says

        Hi Dheeraj,

        I have enrolled for paid venture capital course. I am unable to access it. Could you please guide me on the next steps ?

        Regards,
        Anurag

  58. ARJUN SEN says

    Hi Sir,

    I have just started using your course on Finacial Modelling through EDUCBA. Can you please send me the various templates used on the courses.

    Thanks and regrads,

    Arjun

    • Dheeraj Vaidya says

      just mailed the model. Please check.

  59. Hezekiah Mulehse says

    Hi Dheeraj . This is very instructive and largely straightforward to follow . Many thanks

    • Dheeraj Vaidya says

      thanks Hezekiah!

  60. Akintayo says

    Insightful.

    Please assist by emailing me the MOdel.

    Kind regards,
    Akintayo.

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  61. Kaushik says

    Excellent article.

    • Dheeraj Vaidya says

      thanks Kaushik!

  62. Sahil Jakhar says

    Hello Dheeraj,
    I loved you written model here. I want practice this same model myself. Can you send the Solved Excel Sheet, so that I can evaluate myself.
    Thanks in advance.

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  63. SHREYANS JAIN says

    Great inisght can you mail me the template

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  64. Abhishek Pareek says

    Hey Dheeraj Very nice Article, I haven’t got the Financial model in mail please send it again.

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  65. Mithlesh Sahani says

    Hello Dheeraj,

    Despite mentioning the e-mail address in the box for a couple of times, i have still not received the unsolved and solved s/sheet for the model. can you please kindly mail me on mithleshsahani@yahoo.co.in

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  66. Michael Pineiro says

    Hello,

    I signed up but haven’t received the files.

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  67. Paresh Upasani says

    Dear Dheeraj,

    Awesome article. I have seen very few such easy worded articles which make difficult topic like this look simple.

    I am unable to download the solved and unsolved excel sheets for finance modelling can you please arrange to send me.

    Many thanks for this wonderful work.

    Thanks!

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  68. Michael Emmanuel says

    excellent job!!! please could you share the template with me my email?

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  69. Joshua says

    Excellent! what a great work

    • Dheeraj Vaidya says

      thanks Joshua!

  70. Satish says

    Hi Dheeraj

    I’m unable to download the Colgate palmolive Historical model through the link you have given. Could you please send me the link?

    Many thanks
    Satish

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  71. Mariya says

    Hi Dheeraj,

    I enjoy your site and appreciate your time and desire to share your knowledge with people like me! I am still reading this article and training particularly.
    I would like to ask you, at the moment this is Colgate case to learn. But if you have to do something similar but for a company which starts now where do you manage to take all the information from. Here we have historical data but when the company is new, in fact we do not have such data?
    I appologize if you have commented a question like this before!
    Thank you!

    Mariya

    • Dheeraj Vaidya says

      thanks Mariya. If the company is new, then it becomes tricky as there is very limited data available. Such models are very simplified and its complexity depends on the amount of information available.

      Best,
      Dheeraj

  72. Michael says

    Hello Dheeraj Sir,

    First of all i would like to thank you for giving us a useful model and some information about Financial modeling.

    But, can you please send me the download link of the Ratio Analysis in this course. Also, “Download the Colgate Palmolive Historical Model here” is not working.

    Thank you so much!

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  73. JY,Kim says

    wonderful tutorial.

    Could you share me the template for a deep study further?

      • shivam dang says

        Hi, Dheeraj
        I am not able to receive the files. Request to share them

        • Dheeraj Vaidya says

          Please check your email. I’ve just sent the model.

      • Jai Gupta says

        I really appreciate the efforts you have put here. Learnt a lot. Can you please share the template with me too?

        • Dheeraj Vaidya says

          just mailed the model. Please check.

      • Jon P says

        Hi Dheeraj,

        I to found this very informative – thank you!

        Would it be possible to get a copy of the template?

        • Dheeraj Vaidya says

          just mailed the model. Please check.

  74. Kim,JY says

    Hello,Mr
    I’m student in S.korea
    Thank you for your effort.

    • Dheeraj Vaidya says

      thanks Kim. Glad you liked the financial modeling tutorial.

  75. sowjanya kariveda says

    hai.. I have downloaded the excel sheets but later what to do I am not getting. Can you help me how to learn from that?? And where the videos will be available?

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  76. Eason says

    Dear Dheeraj,
    I really appreciate your work and knowledge. However, I haven’t received the template yet, Could you please kindly send me the Colgate financial model?

    Best,
    Eason

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  77. Ed says

    Hi,

    Thanks for sharing this info, will you mind to send me the model.

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  78. HanSwe says

    Dear Dheeraj, do you have video training for this financial modelling? I would like to have it if you do.
    I am very interested to learn financial modelling, thank you for your great help.
    Bests regards,
    HanSwe

      • Adebari Oladimeji says

        Please how can I get the videos of the financial modeling ?

        • Dheeraj Vaidya says

          Hi Adebari, unfortunately, i haven’t prepare videos for this Colgate Model. However, you may find a lot of video based tutorials in the pro version here Financial Modeling Course

  79. Sachin says

    Simply amazing and a grand salute to your knowledge. I have a small query. Sometimes the previous figures that are given in an annual report are different from current figures of previous annual report. For eg the ebit figures of 2014-15 (previous figures) in the annual report of year 2015-16 (current year) and those of 2014-15 (current figures) in the annual report of 2014-15 (previous year) are different. In such a case, which figures to use?

    • Dheeraj Vaidya says

      Excellent Question Sachin. I am happy you thought about this :-)

      Only those who have opened the annual report and tried populating the historical figures may not this issue. You should always take the most recent data available for the model. In the case you suggested, you should take all the figures from 2015-16.

  80. Ryan says

    Hi, Thanks for the Tutorial.
    I can’t seem to find the ratios in the templates that I downloaded. Is there a link to another download for them?
    Thank you!

    • Dheeraj Vaidya says

      Please check your email. I’ve just sent the model.

  81. Alex says

    wonderful tutorial.

    Could you share me the template for a deep study further?

    • Dheeraj Vaidya says

      Hi Alex, did you receive the template?

      • Karishma says

        This is amazing!
        Could you kindly mail me the template?

        • Dheeraj Vaidya says

          just mailed the model. Please check.

  82. Amit says

    i appreciate for all your efforts
    can you please send me some SOTP Valuation model

    • Dheeraj Vaidya says

      Hi Amit,

      I don’t have a ready a SOTP valuation model as of now. However, you can refer to SOTP valuation that i took in one of the detailed posts.

      Thanks,
      Dheeraj

  83. Pratik Biyani says

    Hello Sir,

    Great Tutorial. I have one question though!
    So the company I am analysing has never buybacked thus there is no amount for the buyback of shares in the cash flow statement. With this issue, how do I come up with the implied share price (Amount outgo/no. of repurchased shares)and thus the Assumed current year EPS multiple (Implied Share price/Current year EPS). Please help me!

    Thank you

    • Dheeraj Vaidya says

      Hi Pratik,

      If they have never bought back the shares, then there are two scenarios –
      1. did the company announce that they are going to buy back in the future. If no, then you are not required to work on finding the share repurchase.
      2. If the company did announce that they are going to buy back, then you may use the Industry average PE to find the implied share price of the stock.

      Hope this answers your queries.
      Dheeraj

  84. Phil Murphy says

    Hi Dheeraj,
    Many thanks for your great tutorial.
    Do you have the most up to date version yet? it would be very much appreciated if you do
    many thanks

    • Dheeraj Vaidya says

      Hi Phil,

      I am updating this case study.. should be up in 2-3 weeks time.

      Best,
      Dheeraj

  85. Ryan says

    Could you send me the most up to date model (solved and unsolved) please? Thanks!

    • Dheeraj Vaidya says

      Hi Ryan,

      I am working on the updated model to be released soon. Meanwhile, please let me know if you require the dated model?

      Best,
      Dheeraj

  86. Tejal says

    Hey! Thanks for this amazing tutorial.

    Can you help with the analysis of banking sector?

    • Dheeraj Vaidya says

      HI Tejal,

      As of now, I have not yet prepared a banking sector model. Will keep you posted on this.

      Best,
      Dheeraj

  87. Pooja Agarwal says

    Hi Dheeraj,

    Thank you for an amazing model, this truly helps getting hands on experience on this very interesting
    aspect of fundamental analysis. I have a couple of questions though, would be glad if you could answer them:
    1) I believe that for the purpose of projections (and ratios), we should be excluding non-recurring/unusual, non-operating (for EBIT) items, because we may not expect the same to be existent in future. Is this not the general practice? (like in 2015, there is a charge of accounting change 1084 million dollars, which, if not excluded from the projections, may not provide a true expectation for future since YoY growth is not explanatory of business activities)
    2) For ratios, one of the components may be included in an item like ‘other assets’, I think to fill in the historic numbers, these components should be brought in (like for 2010, there are short term investments of 74 million dollars included in other current assets. Which, if ignored doesn’t precisely fill the quick and cash ratios, though in this case impact is not much since its a small number compared to the total current liabilities)
    Many Thanks!
    Pooja

    • Dheeraj Vaidya says

      Hello Pooja,

      Thanks. You have some great points here. My take on those.
      #1 – Non recurring items – you are right on this. We should ideally remove all the non recurring items so that the projections do not include these one time volatilities. I did not do that in the model to keep the model a bit simpler to work with at this stage.
      #2 – You are right on the short term investments thing too :-) It should be included as well. I am working on a full ratio analysis case study. Will try and incorporate your valuable suggestions in that.

      Many thanks,
      Dheeraj

  88. Kartik says

    Hello Dheeraj
    I havent received the template in my mail
    Please forward it

    • Dheeraj Vaidya says

      Hey Kartik, just send you an email on this.
      Please check.
      Thanks,
      Dheeraj

      • matt parent says

        I also have not received the template

        • Dheeraj Vaidya says

          just mailed the model. Please check.

  89. Adit says

    Hi! so some of my questions may seem silly however i am very new to this! Under what assumptions did you come up with the figure of 4% growth in sales?

    • Dheeraj Vaidya says

      Hi Adit,

      I have taken this just on the basis of historical growth rates (though i should have investigated further on this).

      Thanks,
      Dheeraj

      • Adit says

        Hi dheeraj!

        Thanks for that. So you calculated the average historical growth rates and used that as a basis for your projection?

        I would like to ask you a number of questions. Would you be willing to send me an email so that I could respond with my questions? ( only because I do not see any email contact for you on the page)

        Regards
        Adit

        • Dheeraj Vaidya says

          Hi Adit,

          You can send me an email on dheeraj at wallstreetmojo.com

          Thanks,
          Dheeraj

  90. Adit says

    hi dheeraj! Thank you for all your efforts! it is much appreciated. I noticed that in the colgate palmolive model template the ratio analysis bit is absent. Could you please advise?

    • Dheeraj Vaidya says

      Hi Adit,

      I am currently working on updating this case study along with the Ratio Analysis. Should be up and ready in a few weeks time.

      Thakns,
      Dheeraj

      • Adit says

        Hey dheeraj!

        Thank you so much for your reply! I look forward to your update. it is much appreciated.

        • Dheeraj Vaidya says

          sure Adit.

          thanks!

          • Rohit says

            Has it been published yet?

          • Dheeraj Vaidya says

            Yes Rohit, you can check the Ratio Analysis here

  91. Pa Line says

    I can get to the last step fine but the part around the revolver and the transferring the debt info the the BS are causing me serious trouble. Why do the numbers in the tutorial, the numbers calculated by using the formulas, and the numbers in the completed model not match? For example, in the 12H step, the revolver is 40, on the completed model it is 940, and via my model, cell K20 should be 112.3. What is going on?

    • Dheeraj Vaidya says

      Hey Pa,

      Did you take the same assumptions as mine? Also, once the model is completely linked, it goes through a circular reference loop and causes changes to the intial numbers that we may have taken.

      thanks,
      Dheeraj

  92. Kshitiz Sanjeev Kumar says

    Hi Dheeraj,

    Got to know about this blog of yours while searching guidelines of finance modelling over internet.
    I am an amateur in this field though done with my mba. I really want to explore the finance modelling.
    So, could you please help me with this by providing some background and how can i pursue some practice on the same.

    It will be really helpful for me.
    Thanks.

    • Dheeraj Vaidya says

      Hey Kshitiz,

      Financial Modeling is primarily useful for careers in Investment Banking, Equity Research etc. You can think of this as the core of Research Field. You can learn Financial Modeling easily by downloading unsolved sheet and practice as per the given step by step instructions.

      Do let me know in case of any isseus that you are facing.

      thanks,
      Dheeraj

  93. Rohan Vaswani says

    For 3A, revenue projections using growth rates, how do you get the projected growth rates? for example: projected growth rate of revenue in North America is 4.0%. How do you get this 4.0%?

    • Dheeraj Vaidya says

      Hey Rohan,

      I did not use much brains here. I have just taken this from the trend based on historical analysis. In this case, i was focussing more on ensuring that students learn financial modeling and don’t get lost much into the assumptions side.

      thanks,
      Dheeraj

  94. Santanu kar says

    Hi deeraj,

    Hope u r doing well.
    Is financial modelling for capex projects different from that of equity ?
    I am a civil engineer trying to gain knowledge on capex project proposal, financial projections

    Regards,
    Santanu

    • Dheeraj Vaidya says

      Hi Shantanu,

      thanks. Equity research may not contain capex in lot of details as it is just a part of the overall financial model. However, from project finance perspective, Capex will become the most important driver. You should learn Project Finance Modeling.

      Thanks,
      Dheeraj

  95. Thomas says

    The ratios are shown int he tutorial but are not found in the template. Is there a reason for this? Can you send me the updated template please.

    • Dheeraj Vaidya says

      Hey Thomas,

      An update to this is under progress. Will send you shortly.

      thanks,
      Dheeraj

  96. Mythreyi velury says

    Hey Dheeraj

    I love your work and thanks a million for such a generous offering. i am currently working on step 2 and have reached the calculation of operational profitability ratios. My values are close yet far from your values. i request you to share the sheet in which you’ve done the financial ratios analysis. it would be of great help.

    Thanks in advance.

    • Dheeraj Vaidya says

      Hey Mythreyi,

      You should proceed further as Ratio Analysis will not affect much of your financial modeling. An update to this Ratio Analysis is under progress. Will reply to you soon on this.

      Thanks,
      Dheeraj

    • Nikita Rachel says

      Hi mythreyi, is that youuuu? Fancy meeting you here. Yay we’re both learning the same thing haha. xP

  97. Hetal says

    Hi Dheeraj,
    Thank you for this tutorial. Its the best ever tutorial that i have come across.
    I just had one query how do you project amortization ? In this example colgate had given details of next the 5 years of amortization expense. But how do you do it when nothing is mentioned about the future expenses ? Thanks in advance.

    • Dheeraj Vaidya says

      Hello Hetal,

      If not much information about the amortization is provided, we will proceed in the same way as the Depreciation schedule.

      thanks,
      Dheeraj

  98. Navin says

    Thanks Dheeraj for the free financial modelling tutorial. Much appreciated.

    Would you have a example for a SaaS startup valuation. As in initial years they make losses & their working capital mostly comes from borrowings or injection from shareholders.

    • Dheeraj Vaidya says

      Hey Navin,

      Unfortunately, i do not have the SAAs valuation model with me.

  99. RAJESH PATIL says

    Hi Dheeraj,

    It is indeed great info & new simple way of learning.Surely it will help all those who are in this field & currently working in F & A dept.

    Personally thanks for brushing lost knowledge.

    Would like to see more….

    • Dheeraj Vaidya says

      Thanks Rajesh!

  100. Ayush says

    Hi Dheeraj,

    Can you please help me finding the templates because I am unable to locate the Template download option.

    • Dheeraj Vaidya says

      Hope you received the model. Anyways, I have resend the same.

      thanks,
      Dheeraj

  101. Arshad Ali says

    Hello Dheeraj Sir
    I had filled the form already but still not received template. kinldy send me the colpal unsolved template.

    • Dheeraj says

      Hi Arshad,

      I just mailed you the template.

      thanks,
      Dheeraj

      • Eric says

        Hi Dheeraj, can you please send me the Colgate model unsolved & solved? Thank you!

        • Dheeraj Vaidya says

          I have just resent you the templates in case you didn’t receive.

      • Justin says

        Good morning Dheeraj,

        I filled out the form already but have not received the template. Would you be so kind to send me both the solved and unsolved Colgate Palmolive template?

        Thank you in advance.

        • Dheeraj Vaidya says

          Please check your mail. I have resent it.
          Thanks,
          Dheeraj

  102. Tanupreet chadha says

    I’m getting a DIV/0!error while referring the originally held constant diluted weighted average figures to the calculated “share outstanding schedule”, figures for the same. let me know how to fix this, i have done it in the same manner as yours.

    • Dheeraj says

      Hi Tanupreet,

      This is a typical circular reference error. You need to activate “Enable iterative calculations”. In excel 2016, it is present under FILE->Options->Formula.

      Hope this helps,
      Dheeraj

      • Tanupreet chadha says

        It’s been already enabled.Rechecked quite a times but still having the same error. Only the diluted weighted share figures has this problem and not the basic weighted average share figures. i can share the worksheet, if you can please look into it.

        • Dheeraj says

          Hi Tanupreet,

          The sheet that you had sent doesn’t contain any errors. Such errors can be excel /computer specific. You may try again with the following to remove #DIV #Value kind of errors –

          Circular reference comes due to two aspects –
          1. Interest income/interest expense from debt schedule is linked to INcome Statement sheet.
          2. diluted number of shares are linked back to Income Statement.

          For 1 – Try this if this helps – IS SHEET
          a) go to income statement – Copy all range of linked cells L14 to N14 from the debt schedule to let’s say Q14 to S14.
          b) Delete the links L14 to N14 (blank it totally)
          c) Copy Q14 to S14 and Paste it back to L14 to N14.

          if error still persists then try this –

          Go to 2 – Try this if this helps – Shares Outstanding Sheet
          a) Copy all range of linked cells L8 to N8 from the shares outstnading to let’s say Q8 to S8.
          b) Likewise do it for L12 to N12 and copy it to Q12 to S12
          c) Delete the links L8 to N8 & L12 to N12
          d) Copy Q8 to S8 and Paste it back to L8 to N8
          e) Copy Q12 to S12 and Paste it back to L12 to N12

          All the best,
          Dheeraj

          • Tanupreet chadha says

            Thank you dheeraj the solution absolutely worked. But few things are still unanswered. One, the ratio analysis bit from the FM is missing. Secondly, how to make a reasonable assumption for an interest rate based on the information provided in the 10K report.Meaning,do you mean past interest rates from the 10-k, please specify.

          • Jan says

            Hello Dheraaj,

            If above two did not help, could I send you my model and ask you for having a look at it?

            Woild be very helpful, as A do not equal L+S and this circular reference is blockong the calculations.

            Please let me know.

            Thanks,
            Jan

          • Dheeraj Vaidya says

            sure Jan. Please send me the model. Will have a quick look at it.
            thanks,
            Dheeraj

      • Tanupreet chadha says

        Hi Dheeraj,

        Few things: 1)The FM is complete yet incomplete as my balance sheet balances remains unmatched, this could be because my cash and cash equivalent year end balances are not similar to the minimum kept in the debt schedule.
        2) The iteration solution you have provided above hasn’t worked, as it was already enabled.
        3) How have you gauged the interest percentages for both revolving credit and cssh balances.IF 10K, please direct specifically.

        In nutshell, i would like you to please check my worksheet. If yes, i would be mailing it to you.

        Thank you!

  103. AM says

    Ehi Dude, this is amazing.
    I dont have received an model yet. Excited to start.

    • Dheeraj says

      Please check you email for the template. Did you fill the download Colgate model form. Else, please mail me. Will send you the model.
      Cheers,
      Dheeraj

  104. Sridevi says

    Hello Dheeraj Sir,
    Thank you for your kind and generous free course for financial modeling, I am looking for a come back in finance career, it is very much useful in both brushing up basics and in-depth analysis. It will be a great favor to me, if I can get excel sheets e-mailed.
    Thank you.

    • Dheeraj says

      Hi Sridevi,

      Did you signup for the Download Colgate Model at the start of this post. If yes, you should get it automatically. If you have still not recieved, i will email you the models.

      thanks,
      Dheeraj

  105. Shreshtha Gupta says

    HI Dheeraj,
    This tutorial seems to be really informative. Do you happen to have a video tutorial of the same?

    Many thanks,
    Shreshtha

    • Dheeraj says

      Hi Shrehtha,

      Many thanks!

      Unfortunately, i have not prepared financial modeling video tutorials as of now.

      Best,
      Dheeraj

  106. Jaydev says

    Hi Dheeraj ,
    First of all thanks for the detailed model.

    I have doubt related to Depreciation and Capex calculation.
    In 2014 the calculation of CAPEX(Building improvements) is 1.7 and in 2015 it is 3.5 and 1.8 .
    I want to know why is 3.5 taken into calculation.

    Please let me know.

    -Thanks
    Jaydev

    • Dheeraj says

      Hi Jaydev,

      This is a mid year convention. If the capex installation took place on Day 1 of the year, then you must charge full depreciation. However, while estimating, we do not know the day when Capex was installed so we take Mid-Year convention (capex installed at the middle of the year). With this we should charge half the depreciation for the installation year (not the full year).

      Hope this helps,
      Dheeraj

    • Helen says

      Hi Dheeraj,

      I’m so lucky to find this free financial modeling course. Thank you for your amazing work done! It is very detailed and covered almost everything.

      Can you please send me the template? The link seems not working.

      Thousands Thanks again!
      Helen

      • Dheeraj says

        Thanks Helen! I am glad you loved this Financial Modeling course.

        I have sent you the templates through mail. Please check.

        Thanks,
        Dheeraj

  107. Lydia says

    Do you of a certificate of completion for this training, we need 45 hours of financial management training?

    • Dheeraj says

      Hi Lyndia,

      Unfortunately there is no certificate for this Free Financial Modeling Training. I didn’t get your second question about 45 hours of financial management training.

      thanks,
      Dheeraj

  108. Michael Pang says

    How did you get the 4.0% in YoY growth (as well as the other numbers)? in segmental growth? I don’t see how you calculated that and it doesn’t seem to be an average of the past numbers.

    • Dheeraj says

      Hi Michael,

      for the sake of convenience, I have taken this as some number based on the ball park historical numbers. In actual scenarios, we need to more industry research to put the growth numbers.

      Thanks,
      Dheeraj

  109. jorge says

    I completed the exercise and my balance sheet balances where ( A = L + OE) for the first 4 years, but the 5th (last) year it shows an imbalance of 0.0000000000000127

    Would an error like this be acceptable?

    Thank you

    • Dheeraj says

      Hey Jorge,

      This kind of error is completely acceptable. it is ~0 for all practical purposes.
      Great to see that you were able to prepare the full financial model. why don’t you try some other company now?

      Cheers,
      Dheeraj

  110. Tinafaus says

    Please is there a video for the financial modelling tutorials? I am finding it difficult to understand

    • Dheeraj says

      Hi Tinafaus,

      Unfortunately, i have not yet prepared the video tutorials. Please let me know if you have any questions.

      Thanks,
      Dheeraj

  111. Kimunga Kimani says

    Thank you very much for sharing. Quite insightful

    • Dheeraj says

      My Pleasure Kumunga :-)

    • harry g says

      Hi Dheeraj

      Thanks for posting, this is very useful indeed.
      I can’t seem to find the ratio analysis section of the spreadsheet, could you please send it to my email when you get a chance? Or point me towards it on the site.
      Also I was wondering how long it takes you approximately to complete a financial model similar to this one? Just so as I can see if I am at pace :)

      Cheers
      Harry

      • Dheeraj says

        Hello Harry,

        Thanks for your question. I received your email as well. Ratio analysis sheet is a bit dated. I am working towards updating the same. Will send you shortly.
        Assuming that you worked through this Colgate Financial model, any new Financial Model may take anything between 1 day to 10 days. It depends on how robust modeling you are looking at. Normally the first independent financial model is the most challenging and exciting! Thereafter, it will be easier for you to interlink and fine tune other models.

        All the very best with modeling,
        Cheers,
        Dheeraj

        • Fabian Lima says

          Hi Dheeraj,

          Could you send me the Ratio Analysis too? Thank you!

          • Dheeraj Vaidya says

            Hi Fabian,

            I am yet to work on the Ratio Analysis template. Will update you on this.

            Thanks,
            Dheeraj

  112. Ali Ikhlaq says

    Hi Dheeraj,

    I have learn financial modelling back two years and now forgot everything but your course help me to remind everything. Dear i am doing job and working as a Manager accounts in a manufacturing firm.
    I want to ask from you that where online i can made the models for people and sell so i can generate more income in my free time ?

    • Dheeraj says

      Hi Ali,

      Ofcourse preparing financial models can be wonderful. You can use your models commercially or as a base for financial advise.

      Thanks,
      Dheeraj

  113. M says

    Hi Dheeraj,

    First, are the financial modelling for biotechnology sector and pharma sector the same? Is it similar to a financial model done for your colgate example, except you use a pharmaceutical company 10K and/or biotech company 10K filing?

    Is it possible to just purchase module 35 (Financial Modeling – Pharma Sector) and 41 (Financial Modeling – Biotechnology Sector) of the advanced financial modelling courses?

    Thanks,
    M

    • Dheeraj says

      Hi Mark,

      More of less all financial models are prepared in a similar way. All financial models start from the Income Statement and later move to Balance Sheet and Cash Flows. Only the revenue and cost built up statements and assumptions may change a bit depending on your understanding on Pharma/Biotech sectors.

      One exception to this is the bank models (e.g. JPMorgan) where balance sheet is prepared and later on we move to the income statement.

      Thanks,
      Dheeraj

      Thanks,
      Dheeraj

  114. Dusan says

    Hi Dheeraj,

    first thanks for this very helpful tutorial!
    I have one question, why do you divide “Earnings per common share, basic” and “Earnings per common share, diluted” with 2 in your template?
    For example, in original Income Statement downloaded from Colgate site, for these categories in 2007 we have 3.5 and 3.2, and in the template you divide them with 2, so we have 1.675 and 1.6

    • Dheeraj says

      Hi Dusan,

      I have divided this by 2 due to the stock split of Colgate. Since the denominator increased by a factor of 2, the earnings per share should be divided by 2 to get the correct picture.

      Thanks,
      Dheeraj

  115. abhi says

    Hi,
    For the consolidated shares outstanding, i’m unable to figure out how you arrived at the values- which are in the 900million range. As the annual reports show them to be in the range of 400 million.

    • Dheeraj says

      Hi Abhishek,

      It is due to the stock split announced by Colgate.

      thanks,
      Dheeraj

  116. abhi says

    Hi,
    In leverage buyout models, would the integrated three statement model be as advanced as this example?

    • Dheeraj says

      Yes Abhishek, they are generally advanced models

      • Karthick says

        I m not able to download. Can you share this to my emailid KARTHICK_govind@ yahoo.co.in

        Its wonderful for laymen like me

        • Dheeraj says

          Hi Karthick, I have emailed you the models.

          Thanks,
          Dheeraj

  117. abhi says

    Hi,
    In the working capital schedule, how did you calculate the (Increase)/Decrease in WC for 2009 of 429?
    I understand how the 2010, 2011 etc.. values are arrived at. But for 2009, since 2008 data is not shown – do you use some other method?

    Thanks

    • abhi says

      Sorry, i figured it out.

  118. ItsMe says

    Hi Dheeraj,

    Your website is excellent, I have used it many times as a reference for modeling. I am also a member of eduCBA – I have the investment banking bundle.

    I am unable to balance a model – I have a constant difference, which doubles every year. Would you be willing to take a look? Thanks.

    • Dheeraj says

      Sure. Please send the model.

  119. Sevda says

    Hello.

    First of all I’d like to thank you for helping us to sharpen our finance
    skills. I need help. I am looking for “Excel Worksheets and Solutions
    to Exercises to Accompany Financial Modeling, fourth edition, Simon
    Benninga”. If You have it or can help me to find it, I’d be very
    grateful. It is very important for me to find it.

    Thank you in advance and waiting your reply.

    • Dheeraj says

      Hi Sevda,

      I do not have the these templates.

      thanks,
      Dheeraj

  120. Raj says

    Hello sir,

    I am currently working as a QA for payment domain in an IT company. What are your thoughts on pursuing this finance courses for a person like me?. I am even interested in CFA although my interests are not aligned with the job I do. I have a certification from University of Michigan about – introduction to finance. That’s what made me get hooked into this field. I am now planning to do MBA with this finance knowledge as my assets, as I am already half way through your financial modelling course. I am willing to learn to more and get involved in this field. Please help me in understanding what would be the right way to go ahead

    Regards,
    Raj

    • Dheeraj says

      Hello Raj,

      I think CFA is the right thing to start with. At least, do plan to give CFA Level 1 ASAP. In addition, you can do these financial modeling courses to solidify your concepts practically.

      Hope this helps,
      Dheeraj

  121. Pratik Biyani says

    Hello Dheeraj Sir,

    First of all i would like to thank you for all the detailed courses and the time that you have taken out to make them.

    However, can you please send me the download link of the Ratio Analysis in this course. Also, “Download the Colgate Palmolive Historical Model here” is not working.

    Thank you so much!

    My EMAIL ID – pratikbiyani90@gmail.com

    • Dheeraj says

      Hi Pratik,

      Will send you the historical financial model with ratio analysis soon.

      Thanks,
      Dheeraj

  122. Milan says

    Hi Dheeraj,
    I have some problems to download the files. Could you please send it to my mail directly?
    Thanks in advance, best regards,
    Milan

    • Dheeraj says

      Sorry for the inconvenience caused Milan. I have sent the models to your email id.

      Thanks,
      Dheeraj

  123. Kristein M says

    Thanks Sir for this financial model article, it is quite useful & worthy too to know how to make a financial model in an easy go. Thank you for making it easy to understand its fundamentals properly.

    • Dheeraj says

      thanks Kristein! :-)

  124. Saira Thomas says

    Thank you for enlightening us with your great articles on finance. Your articles are truly appreciable; they are easy to understand & grasp. This financial modeling article is very interesting to know the performance of the companies. Thank you for teaching through the mode of an article how to prepare a financial model.

    • Dheeraj says

      Thanks Saira, please do let me know incase you have any questions!

      Best,
      Dheeraj

  125. Vivian Dsouza says

    Simply amazing. Dheeraj thanks a lot for your contribution, extremely helpful. Providing a thorough knowledge on each and every step caught my attention. Thanks again for the contribution, and yes all the new posts on your site are simply awesome as well.

    • Dheeraj says

      Thanks Vivian! Do let me know if you have any questions.

  126. Jyoti Sahani says

    I am glad that i found this free financial modeling course on wallstreetmojo. This is a complete step by step training in simple way. Really helpful for anyone who is naive to financial modeling.

    • Dheeraj says

      Thank you Jyoti!

  127. Mitesh Agarwal says

    The training on preparing financial models that too for FREE is awesome. I really appreciate the way it has been explained step by step and in a concise manner. Looking forward to learn some other advanced modeling lessons as well.

    • Dheeraj says

      Thank you Mitesh. The advanced modeling sessions are planned and are definitely coming in 2016.

      Cheers,
      Dheeraj

  128. Zoe says

    This financial modeling guide is great. All concepts are explained to the point and the explanation is crisp. I am now going to try making one financial model. Thanks for the help!

    • Dheeraj says

      Hello Zoe,

      I am glad you liked the financial modeling course. Please let me know if you have any questions.

      Thanks,
      Dheeraj

  129. Visakha Rajpal says

    I am amazed that this free course has actually covered everything. Specially the way in which each calculation and formula is explained. Use of excel is done very well. Thanks Dheeraj for sharing the content

    • Dheeraj says

      thanks Visakha!

  130. Joseph Dominic says

    This is an amazing course and that to for free. The model is very well explained. Kudos Dheeraj for sharing such a content of true Value

    • Dheeraj says

      thanks Joseph. Please do let me know if you have any questions.

  131. Shreenath Iyer says

    Thank you Dheeraj Sir for your informative article on Financial Modeling. Truly your articles are amazing and helpful. The way you explain the things through your articles is too good. I love your way of explaining the things in the form of examples. I intend to know what does actually financial modeling means your this article has helped me a lot in understanding about financial modeling.

    • Dheeraj says

      thank you Shreenath :-)

  132. Sushree Sawant says

    This post is awesome Sir! This can really help freshers like me understanding financial forecasting. Thanks a lot and keep providing knowledge to us from your vast industry experience.

    • Dheeraj says

      thanks Sushree!

  133. Jacque says

    This was very helpful as I prepare for an interview where I might be asked about financial modeling. I learned so much. Thank you for creating this free course!

    • Dheeraj says

      Its my pleasure Jacque!

  134. Tess says

    Thanks Dheeraj for this tutorial. You made it appear simple. It looks great! I also would like to request for an email of Solved and Unsolved Colgate-Palmolive Financial Model.

    Tess

    • Dheeraj says

      Hello Tess,

      Many thanks. I have mailed you the financial modeling templates.

      Best,
      Dheeraj

  135. sylvia says

    Hi, could you kindly email me the colgate models, both the solved and unsolved. Am unable to access. Thanks. My email is sylvianyakio@yahoo.com

    • Dheeraj says

      Hi Sylvia,

      I just emailed you both the models.

      thanks,
      Dheeraj

      • Stacey Zafiroff says

        Hi Dheeraj– Can you email me both models too? Staceyzafiroff@gmail.com
        Thanks so much. You are a guru! You must analyze the stock market with impeccable precision. What’s your average annual return? I know it must be high!

        Also, could you analyze GoPro next? I’m interested to see what your in depth analysis shows on this volatile, speculative stock. Thanks, Stacey Zafiroff

        • Dheeraj says

          Thank you Stacey for the motivation :-). i have sent you the models to your email id. Though i am not tracking GoPro, I will check and get back to you if i can evaluate this stock.

          Best,
          Dheeraj

  136. Pooja says

    Sir I’m CA FInal student. I wish to join this course. can you guide me

    • Dheeraj says

      Hi Pooja,

      You need to just download the Colgate Model (from the form at the start of this post) and start learning financial modeling.

      Happy Learning!

      Thanks,
      Dheeraj

      • Pooja says

        but i am confused about some other course. I am still thinking either to join CFP or CFA. I am not sure that joining this course with CA will be better for me?

  137. Moh says

    Outstanding work mate. None of my finance courses in B-school taught me financial modelling / analysis better than the masterpiece that you have created.
    I have a couple of questions;
    – Step 9C: How have you used the information “authorised the repurchase of upto 50 Million shares of company’s common stock” into the model? I am not able to understand the link.
    – Step 9D: Where did you get the RSU figures of 1.46, 2.21, and 1.91. I can’t find these figures on the 10-K report
    Step 9F: RSU of 4.539 needs to be recognized over a weighted average period of 2.2 years. So, shouldn’t we be recognizing 4.539/2.2 in 2014, 4.539/2.2 in 2015 as well. Why have we assumed 2.0 in 2015?

    Thanks

    • Dheeraj says

      Hello Moh,

      Thanks for the encouragement and sorry for the late replies. This comment got dumped in the spam comments i received.
      Step 9C – as the company statement said that they are authorized by repurchase update 50 million shares, in the model, we just need to be careful that we do not repurchase more than 50 million shares. If you check 9C, you will note that each year, we are repurchasing around 30 million shares.
      Step 9D – The RSU figures are mentioned in the summary table i provided. its in the 10K. Search for “restricted stock units” and you will be directed to this table.
      Step 9F – good question, why i divided by 2.0 instead of 2.2 years that was accounted for earlier. This is just an assumption that i have used to keep the calculations simple.

      Thanks,
      Dheeraj

  138. Junbeom Park says

    Hi Dheeraj !

    My name is Jun Park

    Many thanks for your useful information, it is a boon for me.

    I work for Utility in Korea and i am deeply involved in nuclear financing

    So I Would like to learn financial modelling step by step

    but i can’t download your all of sample modelling in your blog

    could you email it to me ?

    my email address : jaybee13102082@gmail.com

    thank you,

    Junbeom

    • Dheeraj says

      Hi Junbeom,

      I have mailed you model. Let me know if you need anything else.

      Thanks,
      Dheeraj

  139. Tuan Anh says

    Hi you, thank you so much for your free course!
    I just want to ask you where i can find the Solved of 2CDE, i didn’t see it in IS.
    Best,
    TA

    • Dheeraj says

      Hi Tuan,

      I realized that i didn’t provide those in the solution. I am writing a separate note on this. Will update you on this shortly.

      Thanks,
      Dheeraj

  140. Taca says

    hi Dheeraj,

    I took a finance course 10 years ago, do you think I need to take a refresher course? It yes, can you please recommend something ( either an online tutorial or a book ). I work as a senior accountant so I’m familiar with most of it.

    Thank you,

    Taca

    • Dheeraj says

      Hi Taca,

      Just curious to know what is your objective of taking a finance course? I can guide you further based on your inputs.

      Best,
      Dheeraj

  141. Bett says

    Dheeraj,Thank you very much for taking time to share such insight in financial modeling.Why do you subtract 1 from the base year in the horizontal analysis formula. What do you call that.

    Regards,
    Bett

    • Bett says

      Dheeraj, I have known why. It is simply to arrive at the net movement.

      Regards,

      • Dheeraj says

        thanks Bett!

  142. PM says

    Hello Mr. Dheeraj,

    Thanks for the lovely tutorial.

    My liquidity and solvency ratios are varying a little. I am unable to identify the error. This is Step 2C. For example my inventory turnover from Dec-09 onwards if 5.2, 5.2,5.4, 5.2 and 5.1. However urs is 5.3, 5.2, 5.6, 5.3 and 5.2. The formula used is Cost of sales from IS and inventories from BS.

    I await ur response.

    Regards

    PM

    • Dheeraj says

      Hi Pallavi,

      I am writing a note on Ratio analysis where i am covering Colgate as an example. Will keep you posted on this.

      Thanks,
      Dheeraj

      • jorje says

        this will be very helpful, i keep getting different numbers

  143. Vijay says

    Hi Dhiraj

    Great Work. Could you help with Financial Modelling of a New Mfg. Co. with Loans and Loan and Interest Repayment schedule and Depreciation Schedule.

    • Dheeraj says

      Hi Vijay,

      Unfortunately, i do not have the manufacturing company financial model.

      Thanks,
      Dheeraj

  144. Prashant says

    HI,

    Regarding the circular reference in step 10D.How do we go about getting rid of this circular reference. I am getting “DIV” error.

    Regards,
    PN

    • Dheeraj says

      Hi Prashant,

      This is important. Follow the following steps to remove the DIV error. You cannot get rid of circular reference as it is inbuilt in the core modeling exercise.
      1. select the columns with DIV error and delete it.
      2. Undo the delete

      You should be able to see the corrected output.

      Thanks,
      Dheeraj

  145. Martin says

    Thats absolutely amazing! This is highly appreciated :)

    • Dheeraj says

      thanks Martin!

  146. Dee says

    Hi Dheeraj,

    Thank you so much for publishing this – it is wonderfully written. I was just wondering, would you mind including all the ratio calculations in the solved template for verification purposes? As I couldn’t locate them there. Cheers!

    • Dheeraj says

      Hey Dan,

      I I am working towards writing another blog post completely dedicated to Ratio Analysis. Will update you once its posted.

      Thanks,
      Dheeraj

  147. Oleg says

    Hi Dheeraj,

    Thanks for an amazing opportunity of free practice

    I have a quick question
    In step 4E we have to populate 6 fiels in Working Capital Balances.

    However, there are given only three drivers in Ratios&Assumption Section
    which are
    1. Other current Assets ( % of Net Sales )
    2. Accrued Income Taxes ( % of COGS)
    3.Other accruals ( % of COGS)

    Therefore my question, how to come up with
    a) Receivables
    b)Inventories
    c) Account payable

    Thanks,Oleh

    • Dheeraj says

      Hi Oleg,

      Sorry for this late reply. The comments got dumped in between the spam messages i have been receiving. How do we come up with teh following –
      a) Receivables – here we calculate the receivables turnover in days. We note that the receivable days is between 34 – 39 days. So going forward, we take an assumption that receivables days will be around 35 or so. Based on this input of 35 days for colgate, we back calculate the receivables. Please refer the Colgate excel sheet solutions for further details.
      b) Inventory – here we calculate inventory days and perform the same approach described above.
      c) Payables, we do the same thing, we calcluate the payable days and perform the same approach described in a)

  148. Ramesh says

    HI,
    your page is having tooo good information for modelling learners

    • Dheeraj says

      thank you Ramesh!

  149. Jai says

    Hi,

    You have beautifully explained and demonstrated the flow of the FMCG company model. Thanks for it. However, I wish to value a company which is still in the nascent stage of development, i.e., the company does not have any revenue from its product (its product are in the pipeline stage). then in that case how you value such a company, like any early stage pharmaceutical/biotech company listed in the stock exchange. Could you please build such model?

    Thanks.

    • Dheeraj says

      Hi Jai,

      thank you. Currently i do not have such a model in place. A company that does not have any revenue from its product till date will be valued on the basis of how the growth may look like once they launch the product. Cant detail things much here without knowing the whereabouts of the company.

      Thanks,
      Dheeraj

  150. malik jawad says

    thanks a lot for the financial model training and it was great experiance.
    it really help me a lot but in the end i fail to tie a balance sheet i don’t know where i went wrong.
    please can you help me out?? need further guidence…..

    • Dheeraj says

      Hi Malik,

      Please email me the problems that you are facing in the financial modeling exercise.

      Thanks,
      Dheeraj

  151. Pawan says

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

    Regards: Pawan

    • Dheeraj says

      Hi Pawan,

      Many thanks :-)

  152. Jorge Pierantozzi says

    Hello Teacher Dheeraj!

    Thx a lot for this website!!!

    But I stock in the step 4E. I do not understand how do you project the Receivables, Inventories, Accounts payable and Accrued income taxes. I need some help here.

    Regards,
    Jorge Pierantozzi

    • Dheeraj says

      Sorry for this late reply. The comments got dumped in between the spam messages i have been receiving. How do we come up with teh following –
      a) Receivables – here we calculate the receivables turnover in days. We note that the receivable days is between 34 – 39 days. So going forward, we take an assumption that receivables days will be around 35 or so. Based on this input of 35 days for colgate, we back calculate the receivables. Please refer the Colgate excel sheet solutions for further details.
      b) Inventory – here we calculate inventory days and perform the same approach described above.
      c) Payables, we do the same thing, we calculate the payable days and perform the same approach described in a)

  153. Jorge Pierantozzi says

    Hello Teacher

    I’m quite loving your website, it has helped me alot! I well made!!!

    In other hand, I’m Stock in the 4E step. I do not understand how do you project the Receivables, Inventories, Other current assets, etc. Is with the proponcional os Net sales and Ration Assumptions Drivers??? I’m not reaching the same numbers that you projected.

    Regards,
    Jorge Pierantozzi

    • Dheeraj says

      Try checking the calculations again. the numbers should match.

  154. Alaa says

    Hi Dheeraj

    Thanks for your generosity to the world.

    How will a similar model work for a private firm?

    I an trying to do a DCF for a private firm and am looking for benchmark financial and DCF model for private firms.

    Can you recommend any sources or do you have a reference model that we can refer to?

    Regards
    Alaa

    • Dheeraj says

      Hello Alaa,

      I am sorry i do not have such a model at this stage. However, stay tuned. will update you soon on this.

      Thanks,
      Dheeraj

  155. Anshul Agarwal says

    Please send me your course details along with commercial over my mail id.

    • Dheeraj says

      Hey Anshul,

      this is a free course. You just need to download the templates and start learning!

      All the best!
      Dheeraj

  156. Cedric Jirsell says

    Great tutorial, thanks for the effort put in.

    Some thoughts though, as we do calculate most areas that would be classified as operating expenses, why not link up those once to the Rev Estimation as well. As it is now the only thing determining the Net Income estimation is an arbitrary estimation of COGS and OPEX. I might have missed something in regards to this but would be glad if you could give your thoughts on it, as Earnings would be an important factor to determine share price.

    I’m still working through mine as I have obviously messed something up to my BS isn’t in balance…

    • Dheeraj says

      Hi Cedric,

      You are right. Its just about how you think about certain assumptions. For example, advertising expenses should move with Revenue (can see the direct links) so Revenue linkages makes sense. If you are comfortable with Revenue assumption linkages, you can also take that. I have seen many analysts doing so.

      How is your model coming along?

      Thanks,
      Dheeraj

  157. Renee says

    Hi Dheeraj,

    A lot of thanks for sharing your knowledge! :) As a junior at college, I really appreciate the precious hand-on lecture. I have a question though. How did you calculate “Accounts Payable (days payable)” in working capital chart?

    Thank you very much!

    • Dheeraj says

      Hi Renee,

      a) Receivables – here we calculate the receivables turnover in days. We note that the receivable days is between 34 – 39 days. So going forward, we take an assumption that receivables days will be around 35 or so. Based on this input of 35 days for colgate, we back calculate the receivables. Please refer the Colgate excel sheet solutions for further details.
      b) Inventory – here we calculate inventory days and perform the same approach described above.
      c) Payables, we do the same thing, we calculate the payable days and perform the same approach described in a)

      Thanks,
      Dheeraj

  158. Him says

    Hi Prof Dheeraj,

    Your Tutorial is really nice and I learnt a lot of the mechanisms from it. One question. I have already tried to go through the model and I got stuck during the equity section Step 9C to find out he implied share price. However at that time, my income statement is still not finished as the interest expense statistic is not ready and hence no EPS is derived. If I reverse the step to calculate interest expense first, equity data and cash flow from financing activities needs to be used. How should I solve this problem? Many thanks!!!

    Him

    • Dheeraj says

      Hello Him,

      Thanks for your note. You need to carefully follow each step one by one without skipping any. This also means that you have to follow the same sequence as suggested in this tutorial. It will be great if you could send me your Financial Model – i will have a look at it and see the exact nature of the problem.

      Best,
      Dheeraj

      • malik jawad says

        thanks a lot for the financial model training and it was great experiance.
        it really help me a lot but in the end i fail to tie a balance sheet i don’t know where i went wrong.
        please can you help me out?? need further guidence…..

        • Dheeraj says

          Hello malik,

          I can surely help you out on this. Please let me know the questions that you have.

          Best,
          Dheeraj

  159. Mark W says

    Awesome financial modeling tutorial. I will be working on this provided unsolved Colgate financial model and then hopefully be able to do my own model of another company. Once completed I am going to try and show interviewers what I have accomplished and hopefully land a financial analyst entry level position.

    • Dheeraj says

      Hello Mark,

      Many thanks! Do let me know if you run into any issue while practicing financial modeling.

      Best,
      Dheeraj

  160. أسامة ابراهيم says

    i need to learn

  161. Francis says

    I really appreciate the effort you put in to come up with this very nice tutorial. I have one main issue anytime i look at a financial model, Please i would like to know how to check if the financial model built is reliable in terms of the accuracy of the forecast.
    Thanks

    • Dheeraj says

      Hi Francis,

      Accuracy of the forecast can be done by revisiting assumptions and checking the same with the publicly available resources. For example, in the press releases, if the company management said that they will spend $200million on capex, then in your model these numbers should match. Likewise, in results and conference calls, management do provide their guidance on key numbers like Revenue, profit etc – broadly these should be in line with the estimates provided. Also, you may want to check the consensus figures to check if your forecasts are in sync with other research analysts or are high/low.

      Thanks,
      Dheeraj

  162. Manish says

    Hi Dheeraj,

    Thanks a lot for the knowledge that you are furnishing through this portal.

    Request you to please assist how you forecast the numbers of revenue growth and whats the best forecast method i can use for further forecasting?

    Please help…

    • Dheeraj says

      Hi Manish,

      there is generally no best method to forecast revenues. Each depends on the availability of data and your time. if you are ready to spend a couple of days/weeks on forecasting revenues (like financial analysts), then you may want to go as granular as possible for revenue forecasts. For eg. FMCG companies forecast can be done geography wise, product wise or both. If full scale data is available then you can do forecast on the basis of each product (there may be 500+ products). Though such an approach is time consuming but is often recommended as it more robust.

      Thanks,
      Dheeraj

  163. Adam Chan says

    This is really amazing stuff Dheeraj, you were able to break the financial modeling concepts down into an extremely digestible form for ease of learning, much appreciated! I was just wondering, is there a follow up post on how to conduct a scenario analysis?

    Once again, many thanks for your great work!

  164. Raj Bhagat says

    Hi Dheeraj, Thank you for this tutorial, Dheeraj, i need your guidance pls, I am 32 years old,BCA graduate in job and interested in to be a Equity research analyst. Please guide me.

    Thanks

    • Dheeraj says

      Hello Raj,

      Can you please let me know what help are you looking forward to.

      thanks,
      Dheeraj

  165. Emmanuel says

    quite invaluable stuff. did you leave a link for the videos?? or did i miss something. otherwise I will say well done for making this free as well.

  166. David says

    Dear Dheeraj,

    I desire to learn to be a finance guru ALMOST like you. However problem is i have no understanding and dont know where to start from. I really admire you finance people but i have no knowledge and i am ready to learn and hopefully get a CFA qualification for myself as it is not common here in Ghana.

    Can you advise on what i need to do and learn to at least be able to understand what you have done inside out and get ready to do more complex things? I love to project figures naturally so i trust i will enjoy finance but i need to know how i can start understanding from scratch so i can understand all about finance including NYSE, derivatives, options reading and interpretations. I want a career in finance and set a company later when i have money.

    I hope to hear from you.

    • Dheeraj says

      Hello David,

      Thanks for your kind words! I think getting a CFA charter will be first step towards a career in Finance. I strongly recommend the same considering your enthusiasm for Finance.

      Best,
      Dheeraj

  167. ferdinand says

    Thanks Mr Dheeraj sir, for sharing this knowledge. I need this for my financial self study. Love the way you present all the information And the memes too.

    Ferdinand

    • Dheeraj says

      Many thanks Ferdinand!

    • Dheeraj says

      Thank you Ferdinand!

  168. John Munene Nyagah says

    I am interested in building my finance career experience in Financial Modelling and i have found these materials as a good starting point. I am impressed.

    • Dheeraj says

      Thanks John :-)

  169. Naman Khanna says

    Hi..

    Dheeraj, very deep insight about the subject and the way you have put in here is symphonic.

    This is to supplement you already perfect share of wisdom herein above that a Sub-head Shareholders’ Equity Schedule could be a shade more elaborate.

    Its the best available online material. People who have FM-phobia will overcome there fears by the end of the page.

    Humbled,
    Naman Khanna

    • Dheeraj says

      Naman, Thank You so much for this feedback. I feel humbled.

  170. Siva Kumar says

    Dear Dheeraj,

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

  171. Priti says

    Hi Dheeraj,

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

  172. Sulaiman says

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

  173. M.A.R Shanas says

    Thank you for this document.

  174. Nao says

    Hi Dheeraj,
    Thank you very much for your work.
    I’m afraid this is not an appropriate place but I have a question regarding depreciation schedule in Alibaba IPO model.
    You set the useful term of computer equipment and software as 3years, however you continue to book the depreciation cost after the forth year.
    For example, the capex of computer equipment in 2015 is 4,193. But the total depreciation generated from this capex is 10,482 from Mar-15 to Mar22.
    This end up a negative number of PP&E in Mar-22, -1,813, cell in the same sheet.
    Also I think you are using wrong useful life for all of the three PP&E. Although you set 4 years as the useful time of computer equipment in , you apply 3 years in .
    Could you show me the correct calculation?

  175. Aseef Y says

    Hi Dheeraj, Thank you so much for this financial model tutorial.

  176. Mike says

    Thanks for the great tutorial. I have doubt on the debt schedule. Why do you consider dividends to be paid out before arriving at the cash available for debt service? Doesn’t debt have preference over equity?

    Regards

    • Naman Khanna says

      Dear Mike,

      Dividends are returns for shareholders. Basis the performance of current year dividend is distributed to shareholders. On the other hand cash sweep is essentially a cash surplus after considering all the cash operations for the year. Therefore, the preferential treatment to service debt before equity does not arise here.

      Its just like earmarking every possible cash outflow before deciding upon to repayment.

      Regards,

      Naman Khanna

  177. Neetesh Dohare says

    HI Dheeraj,
    Today I was going through the Colgate Financial Model. I realized one thing, that the way the revenues of Colgate were forecasted is more kind of an approximation. Simply taking AM/GM would limit our forecasting.

    I am thinking of this, please let me know if the following approach will correct or not?

    What I believe is this -> Although the industry & the firm are at mature stage, but I was thinking of another approach. The another approach is to see through the sales-gdp regression analysis. If we find that the p value is 0.05 & both the variables are dependent, one can find out the sales. Even one can do the same thing with the market size as well & then have a look at the Annual reports to see the future plans & adjust the computed sales according to best case & worst case scenarios.

    Is my approach kind of more complicated/incorrect, please let me know.

    Thanks in Advance.

    • Neetesh Dohare says

      Hi Dheeraj,
      Could you please help me out at my query.

      • Dheeraj says

        Hello Neetesh, Thanks for the reminder :-)

        I see a point in taking the approach like you are suggesting. Would call this as a mathematical approach to projecting revenues using regression analysis. Unfortunately, I have not yet seen many models where this technique is primarily used to project revenues. One reason could be availability of the data to make it statistically viable. Additionally, the approach I suggested is too simplistic. A research analyst will do well do dig into the segments, product portfolios and geographies to find the growth rate of sub parts and then add it up. However, it takes lot of time and I avoided presenting those complex forecasts at this stage.

        Hope this helps.

        Best,
        Dheeraj

  178. Carson says

    Hi Dheeraj,

    Thank you very much for putting this tutorial together. It is an amazing guide… the best that I’ve seen.

    I do have a quick question for you though – why is it that circular references are allowed to exist in this model?

    Specifically, I am referring to the circular reference found in the “Basic Weighted Average Shares” and “Diluted Weighted Average Shares” on the Income Statement. In step 8A, we were told to assumed that the future number of basic and diluted shares will remain the same as they were in 2013. However, in step 10D, we linked the basic & diluted weighted shares we calculated on the Shares Outstanding Schedule back to the Income Statement.

    Since we were only able to calculate 10D by assuming 8A, why do we plug the calculated amount to replace the assumed amount?

  179. Brian says

    Any chance I can get a copy of the colgate example excel file. I would really appreciate it. Thanks

    • Dheeraj says

      Hey Brian, you just need to download the excel sheet from the form provided above. I will send you the excel sheets in this doesn’t work for you.

  180. Zach says

    Note: Maybe I’m wrong, but I don’t think you mention to adjust non-controlling interest on the balance sheet, so I ended up with an imbalance for about half an hour until I realized that was the problem.

  181. COSTAS N HADJIGAVRIEL says

    THIRTY YEARS IN FINANCIAL SERVICES, I CAN HARDLY THINK OF A BETTER TREATMENT OF FINANCIAL MODELING.
    EXCELENT.
    COSTAS

    • Dheeraj says

      Hello Costas, thank you for your kind words :-)

  182. Mario says

    Hi Dheeraj, first of all, thank you very much for your work.

    I had a problem downloading the template, despite I´d tried several times (using different email adress) the email didn,t arrive, neither to the Inbox nor to the Junk box. Is it possible to download it in any different way?

    Thank you

  183. Komal Malhotra says

    there is a perfect blend of all the classroom teachings we have had during graduation/post-graduation years to real life examples in terms of their implementation and execution. i look forward to more such courses!
    Keep up the good works !!

  184. Santosh says

    Hi Dheeraj, Its always been rewarding and intellectual feast for me to learn things from your case studies. Kudos! Great Work on Colgate-Palmolive.

    • Dheeraj says

      Thanks Santosh for your kind words. I am glad you liked the tutorial.
      Best,
      Dheeraj

      • Mario says

        Hi Dheeraj, first of all, thank you very much for your work.

        I had a problem downloading the template, despite I´d tried several times (using different email adress) the email didn,t arrive, neither to the Inbox nor to the Junk box. Is it possible to download it in any different way?

        Thank you

  185. sandeep says

    Thank you dheeraj for such a great insight about financial modeling.awesome work done by you.

    • Dheeraj says

      Thank you Sandeep for your kind consideration. I hope you liked the Financial Modeling Training Tutorial.
      Best,
      Dheeraj

  186. Anna says

    Thank you Dheeraj! Can you please answer my email about the Alibaba Group? It was sent about 2 weeks ago. And just one more question…how do you estimate the percentages for the further years? Like the growth rates (4,0%; 1,0%; 1,0%; 10%; etc) at the Segmental Information (Income Statement of Colgate).

  187. Syed Zafarul Hasan says

    Amazing work. Thanks for making it simple. Get back to you if need be.

  188. Sudeep says

    Thanks for sharing this well explained tutorial. Can you share financial model related to Indian banking sector.

    • Dheeraj says

      Thanks Sudeep. At this stage i do not have a banking model, however, I do plan to write about it in my coming posts.

  189. Stephan says

    Thank you very much. I really appreciate your expertise in breaking this down to a very simple methodology. There is no limit to how far I can take this. Thank you again.

    • Dheeraj says

      Many thanks Stephan :-)
      I am glad you liked the tutorial. Hope this proves useful.
      Best,
      Dheeraj

  190. Nidhi says

    Thanks Dheeraj for such a wonderful explanation of financial modelling. Can you share some sector specific template like banking and oil& gas and important ratios that are covered in these sectors.

    • Dheeraj says

      Thanks Nidhi. I look forward to preparing a banking sector model pretty soon. Will let you know about the same.

      Best,
      Dheeraj

  191. Manohar says

    wow, you made it so easy to understand…many Thanks Dheeraj

    • Dheeraj says

      My pleasure Manohar

  192. Rachael says

    Hi Dheeraj, thanks for this awesome tutorial. When i try to open the Financial Model of Colgate, it gives me circular reference. Does the model contain any errors or am i missing something?

    • Dheeraj says

      Hi Rachael, many thanks for the download. This financial model contains circular references as the financial statements (Income statmements, Balance Sheet and Cash Flows) are interlinked. Circular references come when we link the Interest expense from the Debt Schedule to the Income Statement. You can remove the error by going to File->Options->Formulas->Enable iterative calculations.
      Hope this helps,
      Dheeraj

  193. Harjot Singh says

    Than you so much for sharing

    • Dheeraj says

      my pleasure Harjot!

  194. Avinash Sharma says

    Tthis was a great learning i would further like to learn and want to connect with you

    • Dheeraj says

      Sure Avinash. You can reach me at dheeraj (at) wallstreetmojo(dot) com.

      Best,
      Dheeraj

  195. Gates says

    Greatly appreciated!

    • Dheeraj says

      thanks Gates!

    • alex Wang says

      Thanks for a clear ,useful framework for the fiancial modeling setting.

  196. Nikola says

    Thanks for sharing. Amazing work.

    • Dheeraj says

      Thanks Nikola for the appreciation :-)

  197. Neeraj says

    Wow Thanks for this stuff. So kind to share this complex – made it easy financial Modelling tutorial

    • Dheeraj says

      Thanks Neeraj. Hope you enjoyed this Financial Modeling tutorial.

  198. Vishesh N Singhi says

    Many thanks Dheeraj sir for this financial modeling tutorial.

    • Dheeraj says

      My pleasure Vishesh :-)

  199. Nick T says

    This is an epic Financial Modeling Tutorial. Best I have ever read. I have bookmarked this one and will get back to you once i try unsolved templates

  200. Nick T says

    This is an epic Financial Modeling tutorial. Bookmarked for reference. Thank You. I will followup with more questions as i try the unsolved template.