# Free Financial Modeling Training Course

Financial Modeling training courses are all around the web and there has been lot written about learning Financial Modeling, however, most of the financial modeling courses 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 Course, I will take an example of Colgate Palmolive and will prepare a full integrated financial model from scratch.

This Financial Modeling Course Tutorial 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

if you want to learn Financial Modeling professional, then have a look at this 50+ hours of Financial Modeling Course Videos

## Financial Modeling Training – Read me First

Step 1 – Download Colgate Financial Model Template. You will be using this template for the tutorial

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

I have made an easy to navigate table of contents for you to do this Financial Modeling Course.

## What is Financial Modeling?

Wikipedia defines “Financial Modeling” as follows –

Financial modeling is the task of building an abstract representation (a model) of a real world financial situation. This is a mathematical model designed to represent (a simplified version of) the performance of a financial asset or portfolio of a business, project, or any other investment. Financial modeling is a general term that means different things to different users; the reference usually relates either to accounting and corporate finance applications, or to quantitative finance applications.

In simple terms, financial modeling means forecasting the future of the company or an asset by way of an Excel Model that is easy to understand and perform scenario analysis. In the context of our discussion here, we will discuss Financial Modeling with respect to the forecasting of the future financials of the company. This free financial modeling training course will help you forecast the financial statements of the company i.e. Income Statement, Balance Sheet and Cash Flows. The excel model is also known as an Integrated Financial Statements Model.

## How to build a financial model?

Now that we know what Financial Modeling is, let us look at how a financial model is build from scratch. This detailed financial modeling course will provide you with a step by step guide to create a financial model. The primary approach taken in this financial modeling course is Modular. 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 course, we will build a step by step integrated financial model of Colgate Palmolive from scratch.

## #1 – Financial Modeling – Project the Historicals

##### Step 1A – Download Colgate’s 10K Reports

“Financial models are prepared in

##### 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, the recommended dataset is to have last 5 years of financial statements. Please download the last 3 years of annual report and populate the historical.
• Many a times, this tasks seems too boring and tedious as it may take 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.

## # 2 -Ratio Analysis

A key to learning Financial Modeling 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 indepth 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, 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”
• 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 is 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. Let us look at the Horizontal analysis of Colgate

##### 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
##### 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
##### 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.
##### Step 2D – Operating Profitability Ratios of Colgate

Profitability ratios measure a company’s ability to generate earnings relative to sales, assets and equity

##### Key Highlights – Profitability Ratios of Colgate

As we can see from the above table, Colgate has an 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 company’s will be able to pay its short and long term obligtations (debt). We calculate leverage ratios that focu 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 – Project the Income Statement

The very first step in the Income Statement is to model 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 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 expansion would require new investments.)
• Product Availability and Pricing
• Revenue 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 is 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

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, 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 other 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 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- Working Capital Schedule

Now that we have completed the Income statement, the next step is to look at the Working Capital Schedule – Below are the steps that are to be followed for Working Capital Schedule

##### 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 4F – Calculate the changes in Working Capital
• Arrive at Cash Flows based on individual line items
• Ensure signs are accurate!

## #5 – Depreciation Schedule

With the completion of the working capital schedule, the next step 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 detailed breakup of the Assets. They have rather clubbed all assets into Land, Building, Machinery and other equipments
• 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 Equipments”. We will have to estimate the useful life for other Equipments

Colgate 2013 – 10K, Page 55

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

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) Manfacturing Equipment & Machinery and 2) other Equipments as shown below.

###### Other Equipments

Total Depreciation of Colgate = Depreciation (Building Improvements) + Depreciation (Machinery & Equipments) + Depreciation (other equipments) 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

## #6 – Amortization Schedule

The next step in this Financial Modeling Course 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 is 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

## #7 – Other Long Term Schedule

The next step 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 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 – Completing the Income Statement

• Before we move any further, 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

At this stage, assume that the future number of basic and diluted shares will remain the same as they were in 2013.

##### Step 8B – Calculate Basic and Diluted earnings per share

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

## #9 – Financial Modelling – Shareholder’s Equity Schedule

The next step in this Financial Modeling Training Course 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 have 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

##### 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

www.reuters.com

• 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

Colgate 2013 – 10K, Page 53

Also, note that the stock options have contractual terms of six years and vest over three years. Colgate 2013 – 10K, Page 69

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

Putting these numbers in our options data below, we note that the option proceeds are \$1.014 billion

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

##### Step 9G- Dividends: Forecast the Dividends
• Forecast estimated dividends using
• Pay out ratio
• Fixed dividend outgo
• Per share payout
• From the 10K reports we extract all past information on dividends
• With the informatio 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.

## #10 – Shares Outstanding Schedule

The next step in this online financial modeling 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, 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.

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

##### 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 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 11C = Complete the cash flow statements

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

##### Step 11D – 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

## #12- Financial Modeling – Debt and Interest Schedule

The next step in this Online Financial Modeling Course is to complete the Debt and Interest Schedule. Summary of the Debt and Interest – Schedule

##### Step 12A – Set up a Debt Schedule
• Reference the Cash Flow Available for Financing
• Reference all equity sources and uses of cash
##### 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

Also provided in additional information on Debt is the committed long term debt repayments. Colgate 2013 – 10K, Page 36

##### Step 12C – 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

##### 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

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

##### 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 12J – Link Interest Expense and Interest Income to Income Statement

Perform the Balance Sheet check : Total Assets = Liabilities + Shareholder’s Equity

##### Step 12K – Audit the Balance Sheet

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

## Recommended Financial Modeling Course

Hope you enjoyed the Free Financial Modeling Course. If you wish to learn Financial Modeling through our expert video lectures, you may also look at our Investment Banking Training Course. 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
(26 Courses)
• Part 2 – Advanced Investment Banking Modeling Training
(20 Courses)
• Part 3 – Investment Banking Add-ons
(13 Courses)
• Part 4 – Investment Banking Foundation Courses
(23 Courses)
• Part 5 – Soft Skills for Investment Bankers
(17 Courses)

## What next?

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

### Comments

1. By HanSwe on

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

2. By Sachin on

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.

• By Dheeraj Vaidya on

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

3. By Pratik Biyani on

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

• By Dheeraj Vaidya on

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

4. By Phil Murphy on

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

• By Dheeraj Vaidya on

Hi Phil,

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

Best,
Dheeraj

• By Dheeraj Vaidya on

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

5. By Tejal on

Hey! Thanks for this amazing tutorial.

Can you help with the analysis of banking sector?

• By Dheeraj Vaidya on

HI Tejal,

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

Best,
Dheeraj

6. By Pooja Agarwal on

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

• By Dheeraj Vaidya on

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

• By Dheeraj Vaidya on

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

7. By Adit on

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?

• By Dheeraj Vaidya on

Hi Adit,

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

Thanks,
Dheeraj

• By Adit on

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

• By Dheeraj Vaidya on

Hi Adit,

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

Thanks,
Dheeraj

8. By Adit on

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?

9. By Pa Line on

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?

• By Dheeraj Vaidya 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

10. By Kshitiz Sanjeev Kumar on

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.

• By Dheeraj Vaidya on

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

11. By Rohan Vaswani on

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%?

• By Dheeraj Vaidya on

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

12. By Santanu kar on

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

• By Dheeraj Vaidya on

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

13. By Thomas on

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.

• By Dheeraj Vaidya on

Hey Thomas,

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

thanks,
Dheeraj

14. By Mythreyi velury on

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.

• By Dheeraj Vaidya on

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

• By Nikita Rachel on

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

15. By Hetal on

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.

• By Dheeraj Vaidya on

Hello Hetal,

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

thanks,
Dheeraj

16. By Navin on

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.

• By Dheeraj Vaidya on

Hey Navin,

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

17. By RAJESH PATIL on

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

18. By Ayush on

Hi Dheeraj,

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

• By Dheeraj Vaidya on

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

thanks,
Dheeraj

19. By Arshad Ali on

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

• By Justin on

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.

20. By Tanupreet chadha on

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.

• By Dheeraj on

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

• By Tanupreet chadha on

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.

• By Dheeraj on

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

• By Jan on

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

• By Dheeraj Vaidya on

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

• By Tanupreet chadha on

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!

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

• By Dheeraj on

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

22. By Sridevi on

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.

• By Dheeraj on

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

23. By Shreshtha Gupta on

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

Many thanks,
Shreshtha

• By Dheeraj on

Hi Shrehtha,

Many thanks!

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

Best,
Dheeraj

24. By Jaydev on

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

• By Dheeraj on

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

• By Helen on

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

• By Dheeraj on

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

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

Thanks,
Dheeraj

25. By Lydia on

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

• By Dheeraj on

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

26. By Michael Pang on

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.

• By Dheeraj on

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

27. By jorge on

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

• By Dheeraj on

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

28. By Tinafaus on

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

• By Dheeraj on

Hi Tinafaus,

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

Thanks,
Dheeraj

• By harry g on

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

• By Dheeraj on

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

• By Dheeraj Vaidya on

Hi Fabian,

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

Thanks,
Dheeraj

29. By Ali Ikhlaq on

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 ?

• By Dheeraj on

Hi Ali,

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

Thanks,
Dheeraj

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

• By Dheeraj on

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

31. By Dusan on

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

• By Dheeraj on

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

32. By abhi on

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.

33. By abhi on

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

34. By abhi on

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

35. By ItsMe on

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.

36. By Sevda on

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.

37. By Raj on

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

• By Dheeraj on

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

38. By Pratik Biyani on

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

39. By Milan on

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

40. By Kristein M on

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.

41. By Saira Thomas on

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.

42. By Vivian Dsouza on

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.

43. By Jyoti Sahani on

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.

44. By Mitesh Agarwal on

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.

• By Dheeraj on

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

Cheers,
Dheeraj

45. By Zoe on

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!

• By Dheeraj on

Hello Zoe,

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

Thanks,
Dheeraj

46. By Visakha Rajpal on

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

47. By Joseph Dominic on

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

48. By Shreenath Iyer on

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.

49. By Sushree Sawant on

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.

50. By Jacque on

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!

51. By Tess on

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

• By Stacey Zafiroff on

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

• By Dheeraj on

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

• By Dheeraj on

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

• By Pooja on

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?

52. By Moh on

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

• By Dheeraj on

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

53. By Junbeom Park on

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

54. By Tuan Anh on

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

• By Dheeraj on

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

55. By Taca on

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

• By Dheeraj on

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

56. By Bett on

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

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

• By Dheeraj on

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

58. By Vijay on

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.

• By Dheeraj on

Hi Vijay,

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

Thanks,
Dheeraj

59. By Prashant on

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

• By Dheeraj on

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

60. 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!

• By Dheeraj on

Hey Dan,

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

Thanks,
Dheeraj

61. By Oleg on

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

• By Dheeraj on

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)

62. By Jai on

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.

• By Dheeraj on

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

63. By malik jawad on

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

• By Dheeraj on

Hi Malik,

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

Thanks,
Dheeraj

64. By Jorge Pierantozzi on

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

• By Dheeraj on

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)

65. By Jorge Pierantozzi on

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

66. By Alaa on

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

• By Dheeraj on

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

• By Dheeraj on

Hey Anshul,

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

All the best!
Dheeraj

67. By Cedric Jirsell on

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…

• By Dheeraj on

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

68. By Renee on

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!

• By Dheeraj on

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

69. By Him on

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

• By Dheeraj on

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

• By malik jawad on

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

• By Dheeraj on

Hello malik,

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

Best,
Dheeraj

70. By Mark W on

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.

• By Dheeraj on

Hello Mark,

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

Best,
Dheeraj

71. By Francis on

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

• By Dheeraj on

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

72. By Manish on

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…

• By Dheeraj on

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

73. By Adam Chan on

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!

74. By Raj Bhagat on

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

75. By Emmanuel on

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.

76. By David on

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.

• By Dheeraj on

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

77. By ferdinand on

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

78. By John Munene Nyagah on

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.

79. By Naman Khanna on

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

80. By Siva Kumar on

Dear Dheeraj,

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

81. By Priti on

Hi Dheeraj,

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

82. By Sulaiman on

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

83. By Nao on

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?

84. By Mike on

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

• By Naman Khanna on

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

85. By Neetesh Dohare on

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.

• By Dheeraj on

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

86. By Carson on

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?

• By Dheeraj on

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.

87. By Zach on

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.

88. By COSTAS N HADJIGAVRIEL on

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

89. By Mario on

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

90. By Komal Malhotra on

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 !!

91. By Santosh on

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

• By Mario on

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

• By Dheeraj on

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

92. By Anna on

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

93. By Sudeep on

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

• By Dheeraj on

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

94. By Stephan on

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.

95. By Nidhi on

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.

• By Dheeraj on

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

Best,
Dheeraj

96. By Rachael on

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?

• By Dheeraj on

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

97. By Neeraj on

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

98. By Nick T on

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

99. By Nick T on

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

Back to top ▴