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.
- Step 1 – Colgate’s Financial Model – Historical
- Step 2 – Ratio Analysis of Colgate Palmolive
- Step 3 – Projecting the Income Statement
- Step 4- Working Capital Forecast
- Step 5 – Depreciation Forecast
- Step 6 – Amortization Forecast
- Step 7 – Other Long Term Forecast
- Step 8 – Completing the Income Statement
- Step 9 – Shareholder’s Equity Forecast
- Step 10 – Shares Outstanding Forecast
- Step 11 – Completing the Cash Flow Statements
- Step 12- Debt and Interest Forecast
Please note the following –
- The core statements are the Income StatementIncome StatementThe income statement is one of the company's financial reports that summarizes all of the company's revenues and expenses over time in order to determine the company's profit or loss and measure its business activity over time based on user requirements.read more, Balance SheetBalance SheetA balance sheet is one of the financial statements of a company that presents the shareholders' equity, liabilities, and assets of the company at a specific point in time. It is based on the accounting equation that states that the sum of the total liabilities and the owner's capital equals the total assets of the company.read more, and Cash Flows.
- The different sheets are the depreciation forecast, working capital forecast, intangibles forecast, shareholder’s equityShareholder's EquityShareholder’s equity is the residual interest of the shareholders in the company and is calculated as the difference between Assets and Liabilities. The Shareholders' Equity Statement on the balance sheet details the change in the value of shareholder's equity from the beginning to the end of an accounting period.read more forecast, other long term items forecast, debt forecast schedule, etc.
- The different schedules are linked to the core statements upon their completion.
- In this financial modeling guide, we will build a step by step integrated economic model of Colgate Palmolive from scratch.
Step 1 – Financial Modeling in Excel – Project the Historicals
The first step in Financial Modeling Guide is to prepare the Historicals.
Download Colgate’s 10K Reports
“Financial models are prepared in excel, and the first steps start with knowing how the industry has been doing in the past years. Understanding the past can provide us with valuable insights related to the future of the company. Therefore the first step is to download all the financials of the company and populate the same in an excel sheet. For Colgate Palmolive, you can download the annual reports of Colgate Palmolive from their Investor Relation Section.
Create the Historical Financial Statements Worksheet
- If you download 10K of 2020, you will note that only two years of financial statements data is available. However, for the purpose of Financial Modeling in excel, the recommended dataset is to have the last 5 years of financial statements. Please download the last 3 years of the annual report and populate the historical.
- Many times, these tasks seem too boring and tedious as it may take a lot of time and energy to format and put the excel in the desired format.
- However, one should not forget that this is the work that you are required to do only once for each company and also, populating the historicals helps an analyst understand the trends and financial statementsFinancial StatementsFinancial statements are written reports prepared by a company's management to present the company's financial affairs over a given period (quarter, six monthly or yearly). These statements, which include the Balance Sheet, Income Statement, Cash Flows, and Shareholders Equity Statement, must be prepared in accordance with prescribed and standardized accounting standards to ensure uniformity in reporting at all levels.read more.
- So please do not skip this, download the data and populate the data (even if you feel that this is donkey’s work ;-) )
Colgate Income Statement with historical populated
Colgate Balance Sheet Historical Data
Step 2 – Ratio Analysis
The second step in Financial Modeling in Excel is to perform Ratio Analysis. We covered this in detail in our Part 1 of the series – Ratio AnalysisRatio 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.
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
- Liquidity ratios measure the relationship of the more liquid assetsLiquid AssetsLiquid Assets are the business assets that can be converted into cash within a short period, such as cash, marketable securities, and money market instruments. They are recorded on the asset side of the company's balance sheet.read more of an enterprise (the ones most easily convertible to cash) to current liabilities. The most common liquidity ratios are the current ratio RatioThe current ratio is a liquidity ratio that measures how efficiently a company can repay it' short-term loans within a year. Current ratio = current assets/current liabilities read more, Acid test (or quick asset) ratio Cash RatiosCash RatiosCash Ratio is calculated by dividing the total cash and the cash equivalents of the company by total current liabilities. It indicates how quickly a business can pay off its short term liabilities using the non-current assets.read more.
- Turnover Ratios like Accounts ReceivablesAccounts ReceivablesAccounts receivables refer to the amount due on the customers for the credit sales of the products or services made by the company to them. It appears as a current asset in the corporate balance sheet.read more turnover, Inventory turnover, and Payables Turnover
Also, have a look at this detailed article on Cash Conversion CycleCash Conversion CycleThe Cash Conversion Cycle (CCC) is a ratio analysis measure to evaluate the number of days or time a company converts its inventory and other inputs into cash. It considers the days inventory outstanding, days sales outstanding and days payable outstanding for computation.read more.
Operating Profitability Ratios of Colgate
Profitability ratiosProfitability RatiosProfitability ratios help in evaluating the ability of a company to generate income against the expenses. These ratios represent the financial viability of the company in various terms.read more a company’s ability to generate earnings relative to sales, assets, and equity
Risk Analysis of Colgate
Through Risk AnalysisRisk AnalysisRisk analysis refers to the process of identifying, measuring, and mitigating the uncertainties involved in a project, investment, or business. There are two types of risk analysis - quantitative and qualitative risk analysis.read more, we try to gauge whether the companies will be able to pay their short and long-term obligations (debt). We calculate leverage ratiosLeverage RatiosDebt-to-equity, debt-to-capital, debt-to-assets, and debt-to-EBITDA are examples of leverage ratios that are used to determine how much debt a company has taken out against its assets or equity.read more that focus on the sufficiency of assets or generation from assets. Rates that are looked at are
- Debt to Equity Ratio
- Debt ratio
- Interest Coverage Ratio
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
Source – Colgate 2020 – 10K, Page 119
Since we do not have any further information about the features, we will project the future sales of Colgate on the basis of this available data. We will use the sales growth approach across segments to derive the forecasts. Please see the below picture. We have calculated the year-over-year growth rate for each element.
Now we can assume a sales growth percentage based on the historical trends and project the revenues under each part. Total Net sales are the sum total of the Oral, Personal & Home Care, and Pet Nutrition Segment.
Costs Projections
- Percentage of Revenues: Simple but offers no insight into any leverage (economy of scale or fixed cost burden
- Costs other than depreciation as a percent of revenues and depreciation from a different schedule: This approach is really the minimum acceptable in most cases, and permits only partial analysis of operating leverageAnalysis Of Operating LeverageOperating Leverage is an accounting metric that helps the analyst in analyzing how a company’s operations are related to the company’s revenues. The ratio gives details about how much of a revenue increase will the company have with a specific percentage of sales increase – which puts the predictability of sales into the forefront.read more.
- Variable costs based on revenue or volume, fixed costs based on historical trends, and depreciation from a separate schedule: This approach is the minimum necessary for sensitivity analysis of profitability based on multiple revenue scenarios
Cost Projections for Colgate
For projecting the cost, the vertical analysis done earlier will be helpful. Let us have a relook at the vertical analysis –
- Since we have already forecasted Sales, all the other costs are some margins of this Sales.
- The approach is to take the guidelines from the historical cost and expense margins and then forecast the future margin.
- For example, the Cost of Sales has been in the range of 39.2%-40.6% for the past five years. We can look at forecasting the margins on this basis.
- Likewise, Selling, General & Administrative ExpensesAdministrative ExpensesSelling, general and administrative (SG&A) expense includes all the expenses incurred in the selling of the products of the company whether direct or indirect along with the entire general and the administrative expenses during an accounting period under consideration such as advertisement expenses, sales promotion expenses, marketing salaries, etc.read more have been historically in the range of 33.8%-36.5%. We can assume the future SG&A expense margin on this basis. Likewise, we can go on for another set of expenses.
Using the above margins, we can find the actual values by back calculations.
For calculating the provision for taxes, we use the Effective Tax Rate assumption.
- Also, note that we do not complete the “Interest Expense (Income)” row as we will have a relook the Income Statement at a later stage.
- Interest Expense and Interest Income.Interest Income.Interest Income is the amount of revenue generated by interest-yielding investments like certificates of deposit, savings accounts, or other investments & it is reported in the Company’s income statement. read more
- We have also not calculated Depreciation and Amortization, which has already been included in the Cost of Sales.
- This completes the Income Statement (at least for the time being!)
Step 4- Financial Modeling – Working Capital Schedule
Now that we have completed the Income statement, the fourth step in Financial Modeling is to look at the Working Capital Schedule.
Below are the steps that are to be followed for Working Capital Schedule
Link the Net Sales and Cost of Sales
Reference the Balance Sheet Data related to working capital
- Reference the past data from the balance sheet
- Calculate net working capitalCalculate Net Working CapitalThe change in net working capital of a firm from one accounting period to the next is referred to as the change in net working capital. It is calculated to ensure that the firm maintains sufficient working capital in each accounting period so that there is no shortage of funds or that funds do not sit idle in the future.read more
- Arrive at an increase/ decrease in working capital
- Note that we have not included short term debt and cash and cash equivalents in the working capital. We will deal with debt and cash and cash equivalents separately.
Calculate the Turnover Ratios
- Calculate historical ratios and percentages
- Use the ending or average balance
- Both are acceptable as long as consistency is maintained
Populate the assumptions for future working capital items
- Certain items without a prominent driver are usually assumed at constant amounts
- Ensure assumptions are reasonable and in line with the business
Project the future working capital balances
Calculate the changes in Working Capital
- Arrive at Cash Flows based on individual line items
- Ensure signs are accurate!
Link up the Working Capital Forecasts to the Balance Sheet
Link Working Capital Items to the Cash Flow Statement 
Step 5 – Financial Modeling in Excel – Depreciation Schedule
With the completion of the working capital schedule, the next step in this Financial Modeling is the project the 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.
source – Colgate 10K 2020 Page – 72
- Depreciation and Amortization is not provided as a separate line item; however, it is included in the cost of sales
- In such cases, please have a look at the Cash flow statements where you will find the Depreciation and Amortization Expense. Also, note that the below figures are 1) Depreciation 2) amortization. So what is the depreciation number?
- Ending Balance for PPE = Beginning balance + Capex – Depreciation – Adjustment for Asset Sales (BASE equation)
Link the Net Sales figures in the Depreciation Schedule
- Set up the line items
- Reference Net Sales
- Input past capital expenditures
- Arrive at Capex as a % of Net Sales
Forecast the Capital Expenditure Items
- In order to forecast Capital expenditure, there are various approaches. One common practice is to look at the Press Releases, Management Projections, MD&A to understand the company’s view on future capital expenditure
- If the company has provided guidance on future capital expenditure, then we can take those numbers directly.
- However, if the Capex numbers are not directly available, then we can calculate it crudely using Capex as % of Sales (as done below)
- Use your judgment based on industry knowledge and other reasonable drivers.
Reference Past Information and Calculate Net PP&E
- We will use Ending Balance for PPE = Beginning balance + Capex – Depreciation – Adjustment for Asset Sales (BASE equation)
- It is complicated to reconcile past PP&E due to restatementsRestatementsA restatement is the revision of already issued financial statements of one or more companies to correct errors with material inaccuracy due to non adhering and complying with the GAAP, accounting mistakes, fraud, or clerical errors affecting part of the entire financial statement requiring a completely new audit.read more, asset sales, etc.
- It is therefore recommended not to reconcile the past PPE as it may lead to some confusion.
Depreciation Policy of Colgate
- We note that Colgate has not explicitly provided a detailed breakup of the Assets. They have instead clubbed all assets into Land, Building, Machinery, and other equipment
- Also, useful lives for machinery and equipment is provided in range. In this case, we will have to do some guesswork to come to the average useful life left for the assets
- Also, guidance for useful life is not provided for “Other Equipment.” We will have to estimate the useful life for other Equipment
Colgate 2020 – 10K, Page 79
Below is the breakup of 2012 and 2013 Property, Plant, and Equipment Details
Colgate 2020 – 10K, Page 125
Estimate the breakup of Property Plant and Equipment (PPE)
- First, find the Asset weights of the Current PPE (2020)
- We will assume that these asset weights of 2020 PPE will continue going forward
- We use these asset weights to calculate the breakup of estimated Capital Expenditure
Estimate the Depreciation of Assets
- Please note that we do not calculate depreciation of LandDepreciation Of LandThe land is a company asset with an infinite useful life. As a result, it is not subject to depreciation, unlike other long-term assets such as buildings and furniture, which have a limited useful life and thus require their costs to be allocated to the accounting period.read more as land is not a depreciable asset
- For estimating depreciation from Building improvements, we first make use of the below structure.
- Depreciation here is divided into two parts – 1)depreciation from the Building Improvements Asset already listed on the balance sheet, 2) depreciation from the future Building improvements.
- For calculating the depreciation from building improvements listed on the asset, we use the simple Straight Line Method of depreciationStraight Line Method Of DepreciationStraight Line Depreciation Method is one of the most popular methods of depreciation where the asset uniformly depreciates over its useful life and the cost of the asset is evenly spread over its useful and functional life. read more.
- For calculating future depreciation, we first transpose the Capex using the TRANSPOSE Function in Excel.
- We calculate the depreciation from asset contributions from each year.
- Also, the first-year depreciation is divided by two as we assume the mid-year convention for asset deployment.
Total Depreciation of BuildingDepreciation Of BuildingDepreciation of building refers to reducing the recorded cost of a building until the value of the structure either becomes zero or reaches its salvage value. In addition, it helps to map the revenue in the form of lease rental generated during the corresponding expenses.read more Improvement = depreciation from the Building Improvements Asset already listed on the balance Sheet + depreciation from the future Building improvements
The above process for estimating depreciation is used to calculate the depreciation of 1) Manufacturing Equipment & Machinery and 2) other Equipment as shown below.
Total Depreciation of Colgate = Depreciation (Building Improvements) + Depreciation (Machinery & Equipment) + Depreciation (additional equipment)
Once we have found out the real depreciation figures, we can put that in the BASE equation as shown below
- With this, we get the Ending Net PP&E figures for each of the years
Link the Net PP&E to the Balance Sheet
Step 6 – Amortization Schedule
The sixth step in this Financial Modeling in Excel is to forecast the Amortization. We have two broad categories to consider here – 1) GoodwillGoodwillIn accounting, goodwill is an intangible asset that is generated when one company purchases another company for a price that is greater than the sum of the company's net identifiable assets at the time of acquisition. It is determined by subtracting the fair value of the company's net identifiable assets from the total purchase price.read more and 2) Other Intangibles.
Forecasting Goodwill
Colgate 2020 – 10K, Page 88
- Goodwill comes on the balance sheet when a company acquires another company. It usually is complicated to project the Goodwill for future years.
- However, Goodwill is subject to impairment tests annually, which are performed by the company itself. Analysts are in no position to conduct such tests and prepare estimates of impairments.
- Most analysts don’t project goodwill; they just keep this constant, which we will also do in our case.
Forecasting Other Intangible Assets
- As noted in Colgate’s 10K Report, the majority of the finite life intangible is related to the Sanex acquisition
- “Additions to Intangibles” are also complicated to project
- Colgate’s 10K report provides us with the details of the next five years of amortization expense.
- We will use these estimates in our Financial Model
Colgate 2020 – 10K, Page 88
Calculate Ending Net Intangibles
Ending net intangibles are linked to the “Other Intangible Assets.”
Link Depreciation and Amortization to Cash Flow Statements
Link Capex & Addition to Intangibles to Cash flow statements
Step 7 – Other Long Term Schedule
The next step in this Financial Modeling is to prepare the Other Long Term Schedule. This is when we prepare for the “leftovers” that do not have specific drivers for forecasting. In the case of Colgate, the other Long Term Items (leftovers) were Deferred Income TaxesDeferred Income TaxesDeferred income tax is a balance sheet item that can be either a liability or an asset since it is a difference in income recognition between the firm's accounting records and the tax law, resulting in the company's income tax due being different than the total tax expense reported.read more (liability and assets), Other investments, and other liabilities.
Reference the historical data from the Balance Sheet
Also, calculate the changes in these items.
Forecast the Long Term Assets and Liabilities
- Keep the Long Term items constant for projected years in case of no visible drivers
- Link the forecasted long term items to the Balance SheetItems To The Balance SheetAssets such as cash, inventories, accounts receivable, investments, prepaid expenses, and fixed assets; liabilities such as long-term debt, short-term debt, Accounts payable, and so on are all included in the balance sheet.read more as shown below
Reference Other Long Term Items to the Balance Sheet
Link the long term items to the Cash Flow Statement
Please note that if we have kept the long term assets and liabilities constant, then the change that flows to the cash flow statement would be zero.
Step 8 – Financial Modeling in Excel – Completing the Income Statement
- Before we move any further in this Excel-based Financial Modeling, we will go back and relook at the Income Statement
- Populate the historical basic weighted average shares and diluted weighted average number of shares
- These figures are available in Colgate’s 10K report
Reference the basic and diluted shares
At this stage, assume that the future number of primary and diluted shares will remain the same as in 2020.
Calculate Basic and Diluted earnings per share.
With this, we are ready to move to our next schedule i.e., Shareholder’s Equity Schedule.
Step 9 – Financial Modelling – Shareholder’s Equity Schedule
The next step in this Financial Modeling in Excel Training is to look at the Shareholder’s Equity Schedule. The primary objective of this schedule is to project equity-related items like Shareholder’s Equity, Dividends, Share buybackShare BuybackShare buyback refers to the repurchase of the company’s own outstanding shares from the open market using the accumulated funds of the company to decrease the outstanding shares in the company’s balance sheet. This is done either to increase the value of the existing shares or to prevent various shareholders from controlling the company.read more, Option Proceeds, etc.
Colgate’s 10K report provides us with the details of common stock and treasury stock activities in the past years, as shown below.
Colgate 10K 2020 – Page 97
Share Repurchase: Populate the historical numbers
- Historically, Colgate has repurchased shares, as we can see from the schedule above.
- Populate Colgate’s shares repurchase (millions) in the excel sheet.
- Link the historical diluted EPS from the Income Statement
- The historical Amount of Repurchased should be referenced from the cash flow statementsCash Flow StatementsStatement of Cash flow is a statement in financial accounting which reports the details about the cash generated and the cash outflow of the company during a particular accounting period under consideration from the different activities i.e., operating activities, investing activities and financing activities.read more.
Also, have a look at Accelerated Share RepurchaseAccelerated Share RepurchaseAccelerated share repurchase (buyback) is a strategy adopted by a publicly-traded company to acquire its outstanding shares in the market from the clients in large blocks via an investment bank.read more.
Share Repurchase: Calculate the PE multiple (EPS multiple)
- Calculate the implied average price at which Colgate has done share repurchase historically. This is calculated as the Amount Repurchased / Number of shares.
- Calculate the PE multiplePE MultipleThe price to earnings (PE) ratio measures the relative value of the corporate stocks, i.e., whether it is undervalued or overvalued. It is calculated as the proportion of the current price per share to the earnings per share. read more = Implied Share Price / EPS
Share Repurchase: Finding Colgate’s Share Repurchased
Colgate has not made any official announcement of how many shares they intend to buyback. The only information that their 10K report shares are that they have authorized a buyback of up to 50 million shares.
Colgate 10K 2020 – Page 97
- To find the number of shares repurchased, we need to assume the Share Repurchase Amount. Based on the historical repurchase amount, I have taken this number like $1,500 million for all the future years.
- To find the number of shares repurchased, we need the projected implied share price of the potential buyback.
- Actual share price = assumed PE multiplex EPS.
- Future buys back PE multiple can be assumed based on historical trends. We note that Colgate has repurchased shares at an average PE range of 17x – 25x
- Below is the snapshot from Reuters that helps us validate the PE range for Colgate
- In our case, I have assumed that all future buybacks of Colgate will be at a PE multiple of 25x.
- Using the PE of 25x, we can find the implied price = EPS x 25
- Now that we have found the implied price, we can see the number of shares repurchased = $ amount used for repurchase / implied price.
Stock Options: Populate Historical Data
- From the summary of common stock and shareholder’s equity, we know the number of options exercised each year.
Colgate 2020 – 10K, Page 97
- Besides, we also have the Option Proceeds from the cash flow statements (approx)
- With this, we should be able to find an effective strike priceStrike PriceExercise price or strike price refers to the price at which the underlying stock is purchased or sold by the persons trading in the options of calls & puts available in the derivative trading. Thus, the exercise price is a term used in the derivative market.read more.
Colgate 2020 – 10K, Page 76
Also, note that the stock optionsStock OptionsStock options are derivative instruments that give the holder the right to buy or sell any stock at a predetermined price regardless of the prevailing market prices. It typically consists of four components: the strike price, the expiry date, the lot size, and the share premium.read more have contractual terms of eight years and vest over three years.
Colgate 2020 – 10K, Page 100
With this data, we fill up the Options data as per below. We also note that the weighted average strike price of stock options for 2020 was $72 and the number of options outstanding was 27.541 million
Colgate 2020 – 10K, Page 100
Stock Options: Find the Option Proceeds.
Putting these numbers in our options data below, we note that the option proceeds are $504 million in 2021. I have assumed that 7 million options are exercised each year.
Stock Options: Forecast Restricted Stock Unit Data
In addition to the stock options, there are Restricted Stock UnitsRestricted Stock 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 2020 – 10K, Page 99
Populating this data in the Restricted Stock Units dataset
The restricted stock units are projected to be (8.65/3.0 years) i.e. 2.88 million going forward.
Also, have a look at the Treasury Stock MethodTreasury Stock 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 –
We note that the dividends payout ratio has been broadly in the range of 60%-66%. Let us assume the Dividend payout ratio of 60% in the future years.
- We can also link the projected Net Income from the Income statement.
- Using both the projected Net Income and the dividends payout ratio, we can find the Total Dividends Paid.
Forecast equity account in its entirety
With the forecast of share repurchase, option proceeds, and dividends paid, we are ready to complete the Shareholder’s Equity Schedule. Link all these up to find the Ending Equity Balance for each year, as shown below.
Link Ending Shareholder’s Equity to the Balance Sheet
Link Dividends, Share repurchase & Options proceeds to CF
The next step in this online financial modeling in Excel training is to look at the Shares Oustanding Schedule. Summary of Shares Outstanding Schedule
- Basic Shares – actual and average
- Capture past effects of options and convertibles as appropriate
- Diluted Shares – average
- Reference Shares repurchased and new shares from exercised options
- Calculate forecasted raw percentages (actual)
- Calculate average basic and diluted shares
- Reference projected shares to Income Statement (recall Income Statement Build up!)
- Input historical shares outstandingShares OutstandingOutstanding shares are the stocks available with the company's shareholders at a given point of time after excluding the shares that the entity had repurchased. It is shown as a part of the owner's equity in the liability side of the company's balance sheet.read more information
- Note: This schedule is commonly integrated with the Equity Schedule
Input the historical numbers from the 10K report
- Shares issuedShares IssuedShares Issued refers to the number of shares distributed by a company to its shareholders, who range from the general public and insiders to institutional investors. They are recorded as owner's equity on the Company's balance sheet.read more (actual realization of options) and shares repurchased can be referenced from the Shareholder’s Equity Schedule
- The input weighted an average number of sharesWeighted An Average Number Of SharesWeighted Average Shares Outstanding is a calculation used to estimate the variations in a Company’s outstanding shares during a given period. It is determined by multiplying the outstanding number of shares (consider issuance & buybacks) in a given reporting period with their individual time-weighted portions. read more and the effect of stock options for the historical years.
Link share issuances & repurchases from the Share Equity Schedule.
Basic Shares (Ending) = Basic Shares (Beginning) + Share Issuances – Shares Repurchased.
Find the basic weighted average shares
- We find an average of two years, as shown below.
- Also, add the effect of options & restricted stock units (referenced from the shareholder’s equity schedule) to find the Diluted Weighted Average Shares.
Link Basic & diluted weighted shares to Income Statement
- Now that we have calculated the diluted weighted average shares, it is time for us to update the same in the Income Statement.
- Link up forecasted diluted weighted average shares outstandingWeighted Average Shares OutstandingWeighted Average Shares Outstanding is a calculation used to estimate the variations in a Company’s outstanding shares during a given period. It is determined by multiplying the outstanding number of shares (consider issuance & buybacks) in a given reporting period with their individual time-weighted portions. read more to Income Statement as shown below
With this, we complete the Shares Outstanding Schedule and time to move to our next set of statements.
Step 11 – Completing the Cash Flow Statements
It is important for us to fully completed the cash flow statements before we move to our next and final schedule in this Financial Modeling, i.e., the Debt Schedule. Until this stage, there are only a couple of incomplete things
- Income Statement – interest expense/ income are incomplete at this stage
- Balance Sheet – cash and debt items are incomplete at this stage
Calculate Cash Flow for Financing Activities
Also, check out Cash Flow from Financing
Find net increase (decrease) in Cash & Cash Equivalents
Complete the cash flow statements
Find the year-end cash & cash equivalents at the end of the year.
Link the cash & cash equivalents to the Balance Sheet.
Now we are ready to take care of our last and final schedule, i.e., Debt and Interest Schedule
Step 12 – Financial Modeling in Excel – Debt and Interest Schedule
The next step in this Online Financial Modeling is to complete the Debt and Interest Schedule. Summary of the Debt and Interest – Schedule
Set up a Debt Schedule
- Reference the Cash Flow Available for Financing
- Reference all equity sources and uses of cash
Calculate Cash Flow from Debt Repayment
- Reference the Beginning Cash Balance from the Balance Sheet
- Deduct a minimum cash balance. We have assumed that Colgate would like to keep a minimum of $500 million each year.
Skip Long Term Debt Issuance/ Repayments, Cash available for Revolving Credit Facility and Revolver section for now
From Colgate’s 10K report; we note the available details on Revolved Credit Facility
Colgate 2020 – 10K, Page 49
Also provided in additional information on debt is the committed long term debt repayments.
Colgate 2020 – 10K, Page 50
Calculate the Ending Long Term Debt.
We use the Long Term Debt repayment schedule provided above and calculate the Ending Balance of Long Term Debt Repayments.
Link the long term debt repayments
Calculate the discretionary borrowings/paydowns.
Using the cash sweep formula, as shown below, calculate the discretionary borrowings / paydown.
Calculate Interest Expense from Revolving Credit Facility
- Make a reasonable assumption for an interest rate based on the information provided in the 10K report
- Find the average balance of Revolving Credit Facility and multiply it with the assumed interest rate
Calculate the Interest Expense from the Long Term Debt
Link the historical average balances and interest expenses. Find the implied Interest rate for historical years
Assume the interest rate on Long term debt based on implied interest rate. Multiply the average long term debt with the assumed interest rate
Calculate Total Interest Expense = average balance of debt x interest rate
Find the Total Interest Expense = Interest (Revolving Credit Facility) + Interest (Long Term Debt)
Link debt & Revolver drawdowns to Cash Flows
Reference Current and Long Term to Balance Sheet
- Demarcate the Current Portion of Long Term DebtCurrent Portion Of Long Term DebtCurrent Portion of Long-Term Debt (CPLTD) is payable within the next year from the date of the balance sheet, and are separated from the long-term debt as they are to be paid within next year using the company’s cash flows or by utilizing its current assets.read more and Long Term debt as shown below
- Link the Revolving Credit Facility, Long Term Debt, and Current Portion of Long Term Debt to the Balance Sheet
Link Noncontrolling Interest from Income Statement
Calculate the Interest Income using the average cash balance
Link Interest Expense and Interest Income to Income Statement
Perform the Balance Sheet check: Total Assets = Liabilities + Shareholder’s Equity
Audit the Balance Sheet
If there is any discrepancy, then we need to audit the model and check for any linkage errors
Recommended Articles
- Alibaba Financial Model
- Box IPO Financial Model
- Financial Modeling Templates
- Financial Modeling Course
- Coursera Financial Model
What next?
If you learned something new or enjoyed this Excel-based Financial Modeling, please leave a comment below. Let me know what you think. Many thanks, and take care. Happy Learning!
46.8K subscribers
Thanks sir, excellent work.
Thanks for your kind words!
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.
Thanks for your kind words!
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
Please check your email. I’ve just sent the model.
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?
Please check your email. I’ve just sent the model.
Hello Sir,
There is a problem in downloading colgate financial model. i have tried several times but dint receive the mail.
Please check your email. I’ve just sent the model.
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?
Please check your email. I’ve just sent the model.
Добрый день, Dheeraj.
Спасибо за Ваш замечательный сайт и интересный обучающий материал.
Прошу направить электронные модели по почте, так как ввод электронной почты в форму запроса не привел к отправке файлов.
Заранее спасибо.
Please check your email. I’ve just sent the model.
Hello Sir, please share the model via email, i haven’t received it yet.
Please check your email. I’ve just sent the model.
Hi, Can you please email me the template? I am unable to download it.
Please check your email. I’ve just sent the model.
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
Please check your email. I’ve just sent the model.
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.
Please check your email. I’ve just sent the model.
Thank you Dheeraj for this great tutorial! Can you please send me the templates?
Please check your email. I’ve just sent the model.
Thank you dheeraj for this great turorial!
Can youn please send me the templates?
Please check your email. I’ve just sent the model.
Sir, I am unable to downlaod the model. Kindly email me the same.
Please check your email. I’ve just sent the model.
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
Please check your email. I’ve just sent the model.
Hi,
Could you please email me the Excel templates as they are failing to download?
thanks a lot!
Myrna
Please check your email. I’ve just sent the model.
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.
Please check your email. I’ve just sent the model.
Thanks Dheeraj for sharing this. Could you please mail me the templates?
Please check your email. I’ve just sent the model.
Could you please mail me templates. Thanks in advance!
Please check your email. I’ve just sent the model.
Please email me the excel models as well. Not able to download them. Thanks for the help.
Please check your email. I’ve just sent the model.
Hi,
The content is great. Thank for everything you shared.
Just one think; I can not get the Colgate model
Please check your email. I’ve just sent the model.
Would love to go through this but like all others, don’t have the template.
Please check your email. I’ve just sent the model.
Hello sir, I failed to download the model.
Could you please send me one copy?
Thank you so much for sharing such helpful stuff.
Please check your email. I’ve just sent the model.
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
Please check your email. I’ve just sent the model.
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
Please check your email. I’ve just sent the model.
Hello Dear,
I tried to download the templates for the free model but I was not apply to receive them
Please check your email. I’ve just sent the model.
Hi, great article. But I have not received email of templates, could you please send me it?
Thanks
Please check your email. I’ve just sent the model.
Hi Dheeraj. Thanks so much for the model tutorial. Kindly send me the model as i have signed up my email as well.
Please check your email. I’ve just sent the model.
Hello! Great article, very useful. But I haven’t received the template by e-mail. Could you please forward it ti me?!
Please check your email. I’ve just sent the model.
Not yet received the Colgate Templates, Please email
Please check your email. I’ve just sent the model.
Hi Dheeraj!
I have trouble downloading the file. Can you send me the templates to my email?
I really appreciated it.
Please check your email. I’ve just sent the model.
hi sir,
I haven’t received the template in my mail
Please forward it.
just mailed the model. Please check.
hi Dheeraj,
I have not received your template in my email, could you please check
Thanks
Hitesh
Please check your email. I’ve just sent the model.
Fabulous explanations.
Please do forward if you the model if you can
Many thanks
Please check your email. I’ve just sent the model.
Can you Please forward me the model
Please check your email. I’ve just sent the model.
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.
just mailed the model. Please check.
Hey, great article. Just wondering where i can get the template. Thanks!
just mailed the model. Please check.
Hi Dheeraj.
i haven’t received the model in my email. Kindly send it.
just mailed the model. Please check.
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
Please check your email. I’ve just sent the model.
I have not received email of templates, could you please send me the files?
Regards
Ali
just mailed the model. Please check.
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?
just mailed the model. Please check.
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
just mailed the model. Please check.
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.
just mailed the model. Please check.
Hello!
I have submitted my email address and now waiting for the excel (solved & unsolved) :)
just mailed the model. Please check.
Hi,
I am unable to download the models, haven’t received it despite numerous attempts.
Request you to please share it with me.
Thanks!
just mailed the model. Please check.
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
just mailed the model. Please check.
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?
just mailed the model. Please check.
Dear sir,
Indeed a good article, very informative and best one. I thank you for your efforts for writing this.
thanks Sandeep!
Great article!
Could I have the excel template?
just mailed the model. Please check.
Hi,
Please assist by sending the excel file.
just mailed the model. Please check.
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
just mailed the model. Please check.
sir,
i want to first learn in demo class, so that i can analyze myself that i can able to course or not
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
Hi Raghu, i don’t have such a model.
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.
just mailed the model. Please check.
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
Hi Gafar, unfortunately, i don’t have an actuarial model.
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
just mailed the model. Please check.
Hi.. Could you share me the template for a deep study further? The one with all the ratios and the detailed study ?
just mailed the model. Please check.
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.
just mailed the model. Please check.
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
Hi Johnavia, Thanks. I left JP Morgan in 2007 after falling in love with entrepreneurship and blogging.
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!
just mailed the model. Please check.
Do you have the template for this example?
just mailed the model. Please check.
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!!!
thanks Daniel. Glad you liked it.
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
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
just mailed the model. Please check.
Hi Dheeraj . This is very instructive and largely straightforward to follow . Many thanks
thanks Hezekiah!
Insightful.
Please assist by emailing me the MOdel.
Kind regards,
Akintayo.
Please check your email. I’ve just sent the model.
Excellent article.
thanks Kaushik!
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.
Please check your email. I’ve just sent the model.
Great inisght can you mail me the template
Please check your email. I’ve just sent the model.
Hey Dheeraj Very nice Article, I haven’t got the Financial model in mail please send it again.
Please check your email. I’ve just sent the model.
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
Please check your email. I’ve just sent the model.
Hello,
I signed up but haven’t received the files.
Please check your email. I’ve just sent the model.
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!
Please check your email. I’ve just sent the model.
excellent job!!! please could you share the template with me my email?
Please check your email. I’ve just sent the model.
Excellent! what a great work
thanks Joshua!
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
Please check your email. I’ve just sent the model.
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
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
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!
Please check your email. I’ve just sent the model.
wonderful tutorial.
Could you share me the template for a deep study further?
Thanks. have mailed you the template!
Hi, Dheeraj
I am not able to receive the files. Request to share them
Please check your email. I’ve just sent the model.
I really appreciate the efforts you have put here. Learnt a lot. Can you please share the template with me too?
just mailed the model. Please check.
Hi Dheeraj,
I to found this very informative – thank you!
Would it be possible to get a copy of the template?
just mailed the model. Please check.
Hello,Mr
I’m student in S.korea
Thank you for your effort.
thanks Kim. Glad you liked the financial modeling tutorial.
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?
Please check your email. I’ve just sent the model.
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
Please check your email. I’ve just sent the model.
Hi,
Thanks for sharing this info, will you mind to send me the model.
Please check your email. I’ve just sent the model.
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
Yes, we do have video training course on Financial Modeling. Please do let me know in case of any information you require about this one.
Please how can I get the videos of the financial modeling ?
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
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?
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.
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!
Please check your email. I’ve just sent the model.
wonderful tutorial.
Could you share me the template for a deep study further?
Hi Alex, did you receive the template?
This is amazing!
Could you kindly mail me the template?
just mailed the model. Please check.
i appreciate for all your efforts
can you please send me some SOTP Valuation model
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
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
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
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
Hi Phil,
I am updating this case study.. should be up in 2-3 weeks time.
Best,
Dheeraj
Could you send me the most up to date model (solved and unsolved) please? Thanks!
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
Hey! Thanks for this amazing tutorial.
Can you help with the analysis of banking sector?
HI Tejal,
As of now, I have not yet prepared a banking sector model. Will keep you posted on this.
Best,
Dheeraj
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
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
Hello Dheeraj
I havent received the template in my mail
Please forward it
Hey Kartik, just send you an email on this.
Please check.
Thanks,
Dheeraj
I also have not received the template
just mailed the model. Please check.
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?
Hi Adit,
I have taken this just on the basis of historical growth rates (though i should have investigated further on this).
Thanks,
Dheeraj
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
Hi Adit,
You can send me an email on dheeraj at wallstreetmojo.com
Thanks,
Dheeraj
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?
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
Hey dheeraj!
Thank you so much for your reply! I look forward to your update. it is much appreciated.
sure Adit.
thanks!
Has it been published yet?
Yes Rohit, you can check the Ratio Analysis here
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?
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
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.
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
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%?
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
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
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
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.
Hey Thomas,
An update to this is under progress. Will send you shortly.
thanks,
Dheeraj
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.
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
Hi mythreyi, is that youuuu? Fancy meeting you here. Yay we’re both learning the same thing haha. xP
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.
Hello Hetal,
If not much information about the amortization is provided, we will proceed in the same way as the Depreciation schedule.
thanks,
Dheeraj
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.
Hey Navin,
Unfortunately, i do not have the SAAs valuation model with me.
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….
Thanks Rajesh!
Hi Dheeraj,
Can you please help me finding the templates because I am unable to locate the Template download option.
Hope you received the model. Anyways, I have resend the same.
thanks,
Dheeraj
Hello Dheeraj Sir
I had filled the form already but still not received template. kinldy send me the colpal unsolved template.
Hi Arshad,
I just mailed you the template.
thanks,
Dheeraj
Hi Dheeraj, can you please send me the Colgate model unsolved & solved? Thank you!
I have just resent you the templates in case you didn’t receive.
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.
Please check your mail. I have resent it.
Thanks,
Dheeraj
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.
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
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.
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
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.
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
sure Jan. Please send me the model. Will have a quick look at it.
thanks,
Dheeraj
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!
Ehi Dude, this is amazing.
I dont have received an model yet. Excited to start.
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
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.
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
HI Dheeraj,
This tutorial seems to be really informative. Do you happen to have a video tutorial of the same?
Many thanks,
Shreshtha
Hi Shrehtha,
Many thanks!
Unfortunately, i have not prepared financial modeling video tutorials as of now.
Best,
Dheeraj
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
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
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
Thanks Helen! I am glad you loved this Financial Modeling course.
I have sent you the templates through mail. Please check.
Thanks,
Dheeraj
Do you of a certificate of completion for this training, we need 45 hours of financial management training?
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
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.
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
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
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
Please is there a video for the financial modelling tutorials? I am finding it difficult to understand
Hi Tinafaus,
Unfortunately, i have not yet prepared the video tutorials. Please let me know if you have any questions.
Thanks,
Dheeraj
Thank you very much for sharing. Quite insightful
My Pleasure Kumunga :-)
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
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
Hi Dheeraj,
Could you send me the Ratio Analysis too? Thank you!
Hi Fabian,
I am yet to work on the Ratio Analysis template. Will update you on this.
Thanks,
Dheeraj
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 ?
Hi Ali,
Ofcourse preparing financial models can be wonderful. You can use your models commercially or as a base for financial advise.
Thanks,
Dheeraj
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
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
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
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
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.
Hi Abhishek,
It is due to the stock split announced by Colgate.
thanks,
Dheeraj
Hi,
In leverage buyout models, would the integrated three statement model be as advanced as this example?
Yes Abhishek, they are generally advanced models
I m not able to download. Can you share this to my emailid KARTHICK_govind@ yahoo.co.in
Its wonderful for laymen like me
Hi Karthick, I have emailed you the models.
Thanks,
Dheeraj
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
Sorry, i figured it out.
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.
Sure. Please send the model.
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.
Hi Sevda,
I do not have the these templates.
thanks,
Dheeraj
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
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
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
Hi Pratik,
Will send you the historical financial model with ratio analysis soon.
Thanks,
Dheeraj
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
Sorry for the inconvenience caused Milan. I have sent the models to your email id.
Thanks,
Dheeraj
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.
thanks Kristein! :-)
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.
Thanks Saira, please do let me know incase you have any questions!
Best,
Dheeraj
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.
Thanks Vivian! Do let me know if you have any questions.
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.
Thank you Jyoti!
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.
Thank you Mitesh. The advanced modeling sessions are planned and are definitely coming in 2016.
Cheers,
Dheeraj
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!
Hello Zoe,
I am glad you liked the financial modeling course. Please let me know if you have any questions.
Thanks,
Dheeraj
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
thanks Visakha!
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
thanks Joseph. Please do let me know if you have any questions.
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.
thank you Shreenath :-)
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.
thanks Sushree!
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!
Its my pleasure Jacque!
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
Hello Tess,
Many thanks. I have mailed you the financial modeling templates.
Best,
Dheeraj
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
Hi Sylvia,
I just emailed you both the models.
thanks,
Dheeraj
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
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
Sir I’m CA FInal student. I wish to join this course. can you guide me
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
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?
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
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
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
Hi Junbeom,
I have mailed you model. Let me know if you need anything else.
Thanks,
Dheeraj
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
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
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
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
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
Dheeraj, I have known why. It is simply to arrive at the net movement.
Regards,
thanks Bett!
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
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
this will be very helpful, i keep getting different numbers
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.
Hi Vijay,
Unfortunately, i do not have the manufacturing company financial model.
Thanks,
Dheeraj
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
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
Thats absolutely amazing! This is highly appreciated :)
thanks Martin!
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!
Hey Dan,
I I am working towards writing another blog post completely dedicated to Ratio Analysis. Will update you once its posted.
Thanks,
Dheeraj
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
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)
HI,
your page is having tooo good information for modelling learners
thank you Ramesh!
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.
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
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…..
Hi Malik,
Please email me the problems that you are facing in the financial modeling exercise.
Thanks,
Dheeraj
Hi Dheeraj
Its amazing to go through this. Really great work.
Regards: Pawan
Hi Pawan,
Many thanks :-)
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
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)
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
Try checking the calculations again. the numbers should match.
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
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
Please send me your course details along with commercial over my mail id.
Hey Anshul,
this is a free course. You just need to download the templates and start learning!
All the best!
Dheeraj
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…
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
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!
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
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
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
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…..
Hello malik,
I can surely help you out on this. Please let me know the questions that you have.
Best,
Dheeraj
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.
Hello Mark,
Many thanks! Do let me know if you run into any issue while practicing financial modeling.
Best,
Dheeraj
i need to learn
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
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
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…
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
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!
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
Hello Raj,
Can you please let me know what help are you looking forward to.
thanks,
Dheeraj
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.
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.
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
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
Many thanks Ferdinand!
Thank you Ferdinand!
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.
Thanks John :-)
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
Naman, Thank You so much for this feedback. I feel humbled.
Dear Dheeraj,
Thanks very much for sharing the template. It is an excellent tool to understand the topic financial modelling.
Hi Dheeraj,
The model template is excellent. Can we get new sheet/ addendum wherein we can find actual performance v/s projections..
kapan anda membuka training di Jakarta di tahun 2015 ini. Tolong contack saya atau send a email. I will joint
Thank you for this document.
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?
Hi Dheeraj, Thank you so much for this financial model tutorial.
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
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
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.
Hi Dheeraj,
Could you please help me out at my query.
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
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?
Any chance I can get a copy of the colgate example excel file. I would really appreciate it. Thanks
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.
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.
THIRTY YEARS IN FINANCIAL SERVICES, I CAN HARDLY THINK OF A BETTER TREATMENT OF FINANCIAL MODELING.
EXCELENT.
COSTAS
Hello Costas, thank you for your kind words :-)
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
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 !!
Hi Dheeraj, Its always been rewarding and intellectual feast for me to learn things from your case studies. Kudos! Great Work on Colgate-Palmolive.
Thanks Santosh for your kind words. I am glad you liked the tutorial.
Best,
Dheeraj
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
Thank you dheeraj for such a great insight about financial modeling.awesome work done by you.
Thank you Sandeep for your kind consideration. I hope you liked the Financial Modeling Training Tutorial.
Best,
Dheeraj
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).
Amazing work. Thanks for making it simple. Get back to you if need be.
Thanks for sharing this well explained tutorial. Can you share financial model related to Indian banking sector.
Thanks Sudeep. At this stage i do not have a banking model, however, I do plan to write about it in my coming posts.
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.
Many thanks Stephan :-)
I am glad you liked the tutorial. Hope this proves useful.
Best,
Dheeraj
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.
Thanks Nidhi. I look forward to preparing a banking sector model pretty soon. Will let you know about the same.
Best,
Dheeraj
wow, you made it so easy to understand…many Thanks Dheeraj
My pleasure Manohar
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?
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
Than you so much for sharing
my pleasure Harjot!
Tthis was a great learning i would further like to learn and want to connect with you
Sure Avinash. You can reach me at dheeraj (at) wallstreetmojo(dot) com.
Best,
Dheeraj
Greatly appreciated!
thanks Gates!
Thanks for a clear ,useful framework for the fiancial modeling setting.
Thanks for sharing. Amazing work.
Thanks Nikola for the appreciation :-)
Wow Thanks for this stuff. So kind to share this complex – made it easy financial Modelling tutorial
Thanks Neeraj. Hope you enjoyed this Financial Modeling tutorial.
Many thanks Dheeraj sir for this financial modeling tutorial.
My pleasure Vishesh :-)
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
This is an epic Financial Modeling tutorial. Bookmarked for reference. Thank You. I will followup with more questions as i try the unsolved template.