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. 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.
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.
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., 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., 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. 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..
- 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.
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 100 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. 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 , 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..
- 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. 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..
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. 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., 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. 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.
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. (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.
- 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..
- 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. 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.
- 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.
- 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. 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., 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. 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. .
- 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. 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. and 2) Other Intangibles.
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. (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. 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., 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..
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..
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. = 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..
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. 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. 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. .
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. 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. (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. 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. 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. 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
- Alibaba Financial Model
- Box IPO Financial Model
- Financial Modeling Templates
- Financial Modeling Course
- Coursera Financial Model
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!