Financial Modeling Tutorials
- Financial Modeling Basics
- Excel Modeling
- Financial Functions in Excel
- Sensitivity Analysis in Excel
- Sensitivity Analysis
- Capital Budgeting Techniques
- Time Value of Money
- Future Value Formula
- Present Value Factor
- Perpetuity Formula
- Present Value vs Future Value
- Annuity vs Pension
- Present Value of an Annuity
- Doubling Time Formula
- Annuity Formula
- Annuity vs Perpetuity
- Annuity vs Lump Sum
- Deferred Annuity Formula
- Internal Rate of Return (IRR)
- IRR Examples (Internal Rate of Return)
- NPV vs XNPV
- NPV vs IRR
- NPV Formula
- NPV Profile
- NPV Examples
- PV vs NPV
- IRR vs ROI
- Break Even Point
- Payback Period & Discounted Payback Period
- Payback period Formula
- Discounted Payback Period Formula
- Profitability Index
- Cash Burn Rate
- Simple Interest
- Simple Interest vs Compound Interest
- Simple Interest Formula
- CAGR Formula (Compounded Annual Growth Rate)
- Effective Interest Rate
- Loan Amortization Schedule
- Mortgage Formula
- Loan Principal Amount
- Interest Rate Formula
- Rate of Return Formula
- Effective Annual Rate
- Effective Annual Rate Formula (EAR)
- Daily Compound Interest
- Monthly Compound Interest Formula
- Discount Rate vs Interest Rate
- Rule of 72
- Geometric Mean Return
- Real Rate of Return Formula
- Continuous compounding Formula
- Weighted average Formula
- Average Formula
- Average Rate of Return Formula
- Mean Formula
- Mean Examples
- Population Mean Formula
- Weighted Mean Formula
- Harmonic Mean Formula
- Median Formula in Statistics
- Range Formula
- Outlier Formula
- Decile Formula
- Midrange Formula
- Quartile Deviation
- Expected Value Formula
- Exponential Growth Formula
- Margin of Error Formula
- Decrease Percentage Formula
- Percent Error Formula
- Holding Period Return Formula
- Cost Benefit Analysis
- Cost Benefit Analysis Examples
- Cost Volume Profit Analysis
- Opportunity Cost Formula
- Opportunity Cost Examples
- Mortgage APR vs Interest Rate
- Normal Distribution Formula
- Standard Normal Distribution Formula
- Normalization Formula
- Bell Curve
- T Distribution Formula
- Regression Formula
- Regression Analysis Formula
- Multiple Regression Formula
- Correlation Coefficient Formula
- Correlation Formula
- Population Variance Formula
- Covariance Formula
- Coefficient of Variation Formula
- Sample Standard Deviation Formula
- Relative Standard Deviation Formula
- Standard Deviation Formula
- Volatility Formula
- Binomial Distribution Formula
- Quartile Formula
- P Value Formula
- Skewness Formula
- R Squared Formula
- Adjusted R Squared
- Regression vs ANOVA
- Z Test Formula
- F-Test Formula
- Quantitative Research
Financial modeling in Excel is the process of building a financial model to represent a transaction, operation, merger, acquisition, financial information to analyze how a change in one variable can affect the final return so as to make a decision on one or more of the aforementioned financial transactions.
Financial modeling in Excel
Financial Modeling training are all around the web and there has been lot written about learning Financial Modeling, however, most of the financial modeling trainings are exactly the same. This goes beyond the usual gibberish and explore practical Financial Modeling as used by Investment Bankers and Research Analysts.
In this Free Online Financial Modeling Training, I will take an example of Colgate Palmolive and will prepare a full integrated financial model from scratch.
This guide is over 6000 words and took me 3 weeks to complete. Save this page for future reference and don’t forget to share it 🙂
MOST IMPORTANT – Download the Colgate Financial modeling Excel template to follow the instructions
Financial Modeling in Excel Training – Read me First
Step 1 – Download Colgate Financial Model Template. You will be using this template for the tutorial
Download Colgate's Financial Model
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 Financial modeling Training instructions to prepare a fully integrated financial model.
Step 4 – Happy Learning!
Financial Modeling Training – Table of Contents
I have made an easy to navigate table of contents for you to do this Financial Modeling Training.
- How to build a financial model?
- #1 – Colgate’s Financial Model – Historical
- # 2 – Ratio Analysis of Colgate Palmolive
- #3 – Projecting the Income Statement
- #4- Working Capital Schedule
- #5 – Depreciation Schedule
- #6 – Amortization Schedule
- #7 – Other Long Term Schedule
- #8 – Completing the Income Statement
- #9 – Shareholder’s Equity Schedule
- #10 – Shares Outstanding Schedule
- #11 – Completing the Cash Flow Statements
- #12- Debt and Interest Schedule Recommended
- Financial Modeling Course
- Free Financial Models
If you are new to Financial Modeling, then do have a look at this guide on What is Financial Modeling?
How to build a financial model in Excel?
Let us look at how a financial model is built from scratch. This detailed financial modeling training will provide you with a step by step guide to creating a financial model. The primary approach taken in this financial modeling training is Modular. The modular approach essentially means that we build core statements like Income Statement, Balance Sheet and Cash Flows using different modules/schedules. The key focus is to prepare each statement step by step and connect all the supporting schedules to the core statements on completion. I can understand that this may not be clear as of now, however, you will realize that this is very easy as we move forward. You can see below various Financial Modeling Schedules / Modules –
Please note the following –
- The core statements are the Income Statement, Balance Sheet, and Cash Flows.
- The additional schedules are the depreciation schedule, working capital schedule, intangibles schedule, shareholder’s equity schedule, other long term items schedule, debt schedule etc.
- The additional schedules are linked to the core statements upon their completion
- In this financial modeling training, we will build a step by step integrated financial model of Colgate Palmolive from scratch.
#1 – Financial Modeling in Excel Training – Project the Historicals
The first step in Financial Modeling Training is to prepare the Historicals.
Step 1A – Download Colgate’s 10K Reports
“Financial models are prepared in Excel and the first steps starts with knowing how the industry has been doing in the past years. Understanding the past can provide us 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. Once you click on “Annual report”, you will find the window as shown below –
Step 1B – Create the Historical Financial Statements Worksheet
- If you download 10K of 2013, 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 last 5 years of financial statements. Please download the last 3 years of the annual report and populate the historical.
- Many times, this tasks seems 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 changes that were made in the financial statements.
- So please do not skip this, download the data and populate the data (even if you feel that this is donkey’s work 😉 )
If you wish to skip this step, you can directly download Colgate Palmolive Historical Model here.
Colgate Income Statement with historical populated
# 2 -Financial Modeling Training – Ratio Analysis
The second step in Financial Modeling in Excel is to perform Ratio Analysis.
A key to learning Financial Modeling in Excel is to be able to perform fundamental analysis. If fundamental analysis or Ratio Analysis is something new for you, I recommend that you read a bit on the internet. I intend to take an in-depth ratio analysis in one of my upcoming posts, however, here is a quick snapshot of the Colgate Palmolive ratios
IMPORTANT – Please note that I have updated the Ratio Analysis of Colgate in a separate post. Please do have a look at this comprehensive guide to Ratio Analysis.
Step 2A – 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 the different margins in relation to sales.
Vertical Analysis Results
- Gross Profit Margin has increased by 240 basis points from 56.2% in 2007 to 58.6% in 2013. This is primarily due to decreased Cost of Sales
- Operating Profit or EBIT has also shown improved margins thereby increasing from 19.7% in 2007 to 22.4% in 2012 (an increase of 70 basis points). This was due to decreased Selling general and administrative costs. However, note that the EBIT margins reduced in 2013 to 20.4% due to increase “Other expenses”. Also, check out the difference between EBIT vs EBITDA
- Net Profit Margin increased from 12.6% in 2007 to 14.5% in 2012. However, Net Profit Margin in 2013 decreased to 12.9%, primarily due to increased “other expenses”.
- Earnings Per share has steadily increased from FY2007 until FY2012. However, there was a slight dip in the EPS of FY2013
- Also, note that the Depreciation and Amortization are separately provided in the Income Statement. It is included in the Cost of Sales
Step 2B – Horizontal Analysis of Colgate
Horizontal analysis is a technique used to evaluate trends over time by computing percentage increases or decreases relative to a base year. It provides an analytical link between accounts calculated at different dates using currency with different purchasing powers. In effect, this analysis indexes the accounts and compares the evolution of these over time. As with the vertical analysis 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 techniques.
Horizontal Analysis Results
- We see that the Net Sales has increased by 2.0% in 2013.
- Also, note the trend in Cost of Sales, we see that they have not grown in the same proportion has Sales.
- These observations are extremely handy while we do financial modeling in Excel
Step 2C – Liquidity Ratios of Colgate
- Liquidity ratios measure the relationship of the more liquid assets of an enterprise (the ones most easily convertible to cash) to current liabilities. The most common liquidity ratios are: Current ratio Acid test (or quick asset) ratio Cash Ratios
- Turnover Ratios like Accounts Receivables turnover, Inventory turnover, and Payables Turnover
Key Highlights of Liquidity Ratios
- Current Ratio of Colgate is greater than 1.0 for all the years. This implies that current assets are greater than current liabilities and maybe Colgate has sufficient liquidity
- Quick Ratio of Colgate is in the range of 0.6-0.7, this means that Colgates Cash and Marketable securities can pay for as much as 70% of current liabilities. This looks like a reasonable situation to be in for Colgate.
- Cash Collection Cycle has decreased from 43 days in 2009 to 39 days in 2013. This is primarily due to the reduction in receivables collection period.
Also, have a look at this detailed article on Cash Conversion Cycle
Step 2D – Operating Profitability Ratios of Colgate
Key Highlights – Profitability Ratios of Colgate
As we can see from the above table, Colgate has a ROE of closer to 100%, which implies great returns to the Equity holders.
Step 2E – Risk Analysis of Colgate
Through Risk Analysis, we try to gauge whether the companies will be able to pay its short and long term obligations (debt). We calculate leverage ratios that focus on the sufficiency of assets or generation from assets. Ratios that are looked at are
- Debt to Equity Ratio
- Debt ratio
- Interest Coverage Ratio
- Debt to Equity Ratio has steadily increased to a higher level of 2.23x. This signifies increased Financial Leverage and risks in the market
- However, the Interest Coverage Ratio is very high signifying less risk of Interest Payment Default.
#3 – Financial Modeling in Excel – Project the Income Statement
The third step in Financial Modeling Training is to forecast the Income Statement, wherein we will start with modeling the Sales or Revenue items.
Step 3A – Revenues Projections
For most companies, revenues are a fundamental driver of economic performance. A well designed and logical revenue model reflecting accurately the type and amounts of revenue flows is extremely important. There are as many ways to design 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 which have 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: 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 key variable. (For a company with a price-discounting strategy, for example, or a best of breed premium priced niche player) e.g. Luxury car market
- Volume Capacity, Capacity Utilization, and Average Price: These assumptions can be important for businesses where production capacity is important 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 important. Modeling the installed base is key (new additions to the base, attrition in the base, 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 employee (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 2013 report. We note that in the income statement, Colgate has not provided segmental information, however, as an additional information, Colgate has provided some details of segments on Page 87 Source – Colgate 2013 – 10K, Page 86
Since, we do not have any further information about the segments, 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 year-over-year growth rate for each segment. Now we can assume a sales growth percentage based on the historical trends and project the revenues under each segment. Total Net sales are the sum total of Oral, Personal & Home Care, and Pet Nutrition Segment.
Step 3B – 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 separate schedule: This approach is really the minimum acceptable in most cases, and permits only partial analysis of operating leverage.
- 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
- 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, Cost of Sales has been in the range of 41%-42% for the past 5 years. We can look at forecasting the margins on this basis.
- Likewise, Selling, General & Administrative Expenses have been historically in the range of 34%-36%. We can assume future SG&A expense margin on this basis. Likewise, we can go on for another set of expenses.
- Also, note that we do not complete the “Interest Expense (Income)” row as we will have a relook a the Income Statement at a later stage.
- Interest Expense and Interest Income is covered in the Debt Schedule.
- We have also not calculated Depreciation and Amortization which has already been included in the Cost of Sales
- This completes the Income Statement (atleast for the time being!)
#4- Financial Modeling Training – Working Capital Schedule
Now that we have completed the Income statement, the fouth step in Financial Modeling Training is to look at the Working Capital Schedule.
Below are the steps that are to be followed for Working Capital Schedule
Step 4A – Link the Net Sales and Cost of Sales
Step 4B – Reference the Balance Sheet Data related to working capital
- Reference the past data from the balance sheet
- Calculate net working capital
- Arrive at 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.
Step 4C – Calculate the Turnover Ratios
- Calculate historical ratios and percentages
- Use the ending or average balance
- Both are acceptable as long consistency is maintained
Step 4D – Populate the assumptions for future working capital items
- Certain items without an obvious driver are usually assumed at constant amounts
- Ensure assumptions are reasonable and in line with the business
Step 4E – Project the future working capital balances
Step 4F – Calculate the changes in Working Capital
- Arrive at Cash Flows based on individual line items
- Ensure signs are accurate!
Step 4G – Link up the forecasted Working Capital to the Balance Sheet
Step 4H – Link Working Capital to the Cash Flow Statement
#5 – Financial Modeling in Excel – Depreciation Schedule
With the completion of the working capital schedule, the next step in this Financial Modeling Training is the project the capital expenditure requirements of Colgate and project the Depreciation and Assets figures. Colgate 2013 – 10K, Page 49
- 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)
Step 5A – 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
Step 5B – Forecast the Capital Expenditure Items
- In order to forecast the Capital expenditure, there are various approaches. One common approach is to look at the Press Releases, Management Projections, Management Discussion and Analysis sections 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
Step 5C- Reference Past Information
- We will use Ending Balance for PPE = Beginning balance + Capex – Depreciation – Adjustment for Asset Sales (BASE equation)
- It is very difficult to reconcile past Property Plant and Equipment (PPE) data due to restatements, asset sales etc
- It is therefore recommended not to reconcile the past PPE as it may lead to some confusions.
Depreciation Policy of Colgate
- We note that Colgate has not explicitly provided a detailed breakup of the Assets. They have rather 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 2013 – 10K, Page 91
Step 5D – Estimate the breakup of Property Plant and Equipment (PPE)
- First, find the Asset weights of the Current PPE (2013)
- We will assume that these asset weights of 2013 PPE will continue going forward
- We use this asset weights to calculate the breakup of estimated Capital Expenditure
Step 5E – Estimate the Depreciation of Assets
- Please note that we do not calculate depreciation of Land 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 depreciation
- For calculating future depreciation, we first transpose the Capex using the TRANSPOSE function
- We calculate the depreciation from asset contribution from each year
- Also, the first year depreciation is divided by 2 as we assume the mid year convention for asset deployment
Total Depreciation of Building 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 (other equipment) Once we have found out the total depreciation figures, we can put that in the BASE equation as show below
- With this, we get the Ending Net PP&E figures for each of the years
Step 5F – Link the Net PP&E to the Balance Sheet
#6 – Financial Modeling Training – 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) Goodwill and 2) Other Intangibles.
Step 6A – Forecasting Goodwill
Colgate 2013 – 10K, Page 61
- Goodwill comes on the balance sheet when a company acquires another company. It is normally very difficult to project the Goodwill for future years.
- Goodwill is, however, subject to impairment tests annually which are performed by the company itself. Analysts are in no position to perform such tests and prepare estimates of impairments
- Most analyst’s don’t project goodwill, they just keep this as constant and this is what we will also do in our case.
Step 6B – Forecasting Other Intangible Assets
- As noted in Colgate’s 10K Report, majority of the finite life intangible is related to the Sanex acquisition
- “Additions to Intangibles” are also very difficult to project
- Colgate’s 10K report provides us with the details of next 5 years of amortization expense.
- We will use these estimates in our Financial ModelColgate 2013 – 10K, Page 61
Step 6C – Ending net intangibles are linked to the “Other Intangible Assets”
Step 6D – link Depreciation and Amortization to Cash Flow Statements
Step 6E – Link Capex & Addition to Intangibles to Cash flow statements
#7 – Other Long Term Schedule
The next step in this Financial Modeling Training is to prepare the Other Long Term Schedule. This is the schedule that we prepare for the “left overs” that do not have specific drivers for forecasting. In case of Colgate, the other Long Term Items (left overs) were Deferred Income Taxes (liability and assets), Other assets and other liabilities.
Step 7A – Reference the historical data from the Balance Sheet
Also, calculate the changes in these items.
Step 7B – 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 Sheet as shown below
Step 7C – Reference Other Long Term Items to the Balance Sheet
Step 7D – Link the long term items to Cash Flow Statement
Please note that if we have kept the long term assets and liabilities as constant, then the change that flow to the cash flow statement would be zero.
#8 – Financial Modeling in Excel – Completing the Income Statement
- Before we move any further in this Excel based Financial Modeling Training, we will actually 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
Step 8A – Reference the basic and diluted shares
Step 8B – Calculate Basic and Diluted earnings per share
#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 Repurchase, 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 2013 – 10K, Page 68
Step 9A – Share Repurchase: Populate the historical numbers
- Historically, Colgate has bought back shares as we can see the schedule above.
- Populate the Colgate’s shares repurchase (millions) in the excel sheet.
- Link the historical diluted EPS from the Income Statement
- Historical Amount Repurchased should be referenced from the cash flow statements
Also, have a look at Accelerated Share Repurchase
Step 9B – 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 Amount Repurchased / Number of shares
- Calculate the PE multiple = Implied Share Price / EPS
Step 9C – 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 is that they have authorized a buy back of upto 50 million shares. Colgate 2013 – 10K, Page 35
- In order to find the number of shares bought back, we need to assume the Share Repurchase Amount. Based on the historical repurchase amount, I have taken this number as $1,500 million for all the future years.
- In order to find the number of shares repurchased, we need the projected implied share price of the potential buy back.
- Implied share price = assumed PE multiple x EPS
- Future buy back PE multiple can be assume on the basis of historical trends. We note that Colgate has bought back shares at an average PE range of 17x – 25x
- Below is the snapshot from Reuters that helps us validate PE range for Colgate
- In our case, I have assumed that all future buybacks of Colgate will be at a PE multiple of 19x.
- Using the PE of 19x, we can find the implied price = EPS x 19
- Now that we have found the implied price, we can find the number of shares repurchased = $ amount used for repurchase / implied price
Step 9D – Stock Options: Populate Historical Data
- From the summary of common stock and shareholder’s equity, we know the number of options exercised each year.
In addition, we also have the Option Proceeds from the cash flow statements (approx)
- With this, we should be able to find the effective strike price
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 2013 was $42 and the number of options exercisable were 24.151 million Colgate 2013 – 10K, Page 70
Step 9E – Stock Options: Find the Option Proceeds
Step 9F – Stock Options: Forecast Restricted Stock Unit Data
In addition to the stock options, there are Restricted Stock Units given to the employees with the weighted average period of 2.2 years Colgate 2013 – 10K, Page 81
Populating this data in the Options dataset For simplicity sake, we have not projected options issuance (I know this is not the right assumption, however, due to lack of data, I am not taking any more option issuances going forward. We have just taken these as zero as highlighted in the grey area above. Additionally, the restricted stock units are projected to be 2.0 million going forward.
Also, have a look at Treasury Stock Method
Step 9G- Dividends: Forecast the Dividends
- Forecast estimated dividends using 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 Dividends 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 50%-60%. Let us take an assumption of Dividends payout ratio of 55% 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
Step 8H – 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.
Step 9I – Link Ending Shareholder’s Equity to the Balance Sheet
Step 9J – 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 basic shares (actual)
- Calculate average basic and diluted shares
- Reference projected shares to Income Statement (recall Income Statement Build up!)
- Input historical shares outstanding information
- Note: This schedule is commonly integrated with the Equity Schedule
Step 10A – Input the historical numbers from the 10K report
- Shares issued (actual realization of options) and shares repurchased can be referenced from the Shareholder’s Equity Schedule
- Also, the input weighted average number of shares and effect of stock options for the historical years.
Step 10B – Link share issuances & repurchases from Share Equity Schedule.
Step 10C – Find the basic weighted average shares,
- we find the 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.
Step 10D – 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 outstanding to Income Statement as shown below
With this we complete the Shares Oustanding Schedule and time to move to our next set of statements.
#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 Training i.e. the Debt Schedule Until this stage, there are only a couple of things that are incomplete
- Income Statement – interest expense/ income are incomplete at this stage
- Balance Sheet – cash and debt items are incomplete at this stage
Step 11A – Calculate Cash Flow for Financing Activities
Also, check out Cash Flow from Financing
Step 11B – Find net increase (decrease) in Cash & Cash Equivalents
Step 11C = Complete the cash flow statements
Step 11D – Link the cash & cash equivalents to the Balance Sheet.
#12- Financial Modeling in Excel – Debt and Interest Schedule
The next step in this Online Financial Modeling Training is to complete the Debt and Interest Schedule. Summary of the Debt and Interest – Schedule
Step 12A – Set up a Debt Schedule
Step 12B – 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 2013 – 10K, Page 35
Step 12C – Calculate the Ending Long Term Debt
Step 12D – Link the long term debt repayments.
Step 12E -Calculate the discretionary borrowings/paydowns
Using the cash sweep formula as shown below, calculate the discretionary borrowings / paydowns.
Step 12F – Calculate the Interest Expense from the Long Term Debt
- Calculate the average balance for Revolving Credit Facility and Long Term Debt
- Make a reasonable assumption for an interest rate based on the information provided in the 10K report
- Calculate Total Interest Expense = average balance of debt x interest rate
Step 12G – Link Principal debt & Revolver drawdowns to Cash Flows
Step 12H – Reference Current and Long Term to Balance Sheet
- Demarcate the Current Portion of Long Term Debt and Long Term debt as show below
- Link the Revolving Credit Facility, Long Term Debt and Current Portion of Long Term Debt to the Balance Sheet
Step 12I – Calculate the Interest Income using the average cash balance
Step 12J – Link Interest Expense and Interest Income to Income Statement
Step 12K – Audit the Balance Sheet
Recommended Financial Modeling Course
Hope you enjoyed the Free Financial Modeling Training. If you wish to learn Financial Modeling in Excel through our expert video lectures, you may also look at our Investment Banking Training . This is primarily 99 courses Investment Banking training bundle. This course starts from basics and takes you to advanced level of Investment Banking Job. This course is divided into 5 parts –
- Part 1 – Investment Banking Training – Core Courses
- Part 2 – Advanced Investment Banking Modeling Training
- Part 3 – Investment Banking Add-ons
- Part 4 – Investment Banking Foundation Courses
- Part 5 – Soft Skills for Investment Bankers
Financial Models Download
If you learned something new or enjoyed this Excel based Financial Modeling Training, please leave a comment below. Let me know what you think. Many thanks and take care. Happy Learning!