The fastest way to learn dynamic 3 statement financial modeling with out using a plug!
Table of Contents
Preface
Who is this book for?
How to use this book
Chapter 1: Projected Financial Statements
1.1 Ingredients Required to Project Financial Statements
1.2 Components of an 3 Statement Financial Model
1.3 Planning & Structure
1.4 Build Order
Chapter 2: Assumptions that Drive the Model
2.1 Historical Financial Statements: Income Statements and Balance Sheets
2.2 Historical Financial Ratios and Performance Drivers
2.3 Assumptions that Drive the Projected Financial Statements
2.3.1 Income Statement Assumptions
2.3.2 Balance Sheet Assumptions
Chapter 3: Forecasting Income Statements
3.1 Revenues
3.2 Expenses
Chapter 4: Forecasting Balance Sheets
4.1 Projecting Working Capital
4.2 Long Term Assets
4.3 Other Assets
4.4 Liabilities
4.5 Shareholders’ Equity
4.6 Golden rule
4.7 Free Cash Flow Projections
4.8 Debt & Dividends schedules
Chapter 5: Forecasting Cash Flow Statements
5.1 Cash Flow from Operations
5.2 Cash Flow from Investing Activities
5.3 Cash Flow from Financing Activities
5.4 Net Cash Flow for the year and Ending Cash Balance
Chapter 6: Presentation & Formatting
6.1 Order & Structure of the Financial Statements
6.2 Formatting Guidelines
6.3 Other Formatting Options
Chapter 7: Additional Features
Appendix: Model
Preface
Who is this book for?
Modeling Projected or Forecasted Financial Statements (without a plug) – Simplified is for MBA, CFA or undergraduate finance students interested in understanding how to forecast or project financial statements into the future in Microsoft Excel or Google Sheets. This book is also helpful for executives and others interested in understanding and modeling financial statements. Itis a wonderful resource for students or professionals interviewing for jobs in the private equity, investment banking or hedge fund industry because it will teach you how to build a basic 3 statement financial model from scratch in about 2 hours.
This book assumes that the reader is familiar with basic accounting concepts. For example, the reader is expected to know the structure of an income statement, balance sheet and cash flow statement, the meaning of the term working capital, etc. The reader is NOT expected to be an expert in Microsoft Excel or Google sheets but has to be reasonably familiar with Microsoft Excel.
This book will teach you how to build a simple 3 statement financial model. Different company’s financial statements may have different account names and lines but the overall structure of a financial statement will be quite similar. Once you can confidently build a simple 3 statement financial model, you can add many bells and whistles to reflect the numerous specifics of any projected 3 statement financial model.Modeling Projected or Forecasted Financial Statements (without a plug) – Simplified is based on Senith Mathews’ experience tutoring financial modeling and building models as a management consultant with Arthur Andersen and Mercer Management Consulting (now Oliver Wyman). Modeling Projected or Forecasted Financial Statements (without a plug) – Simplified narrowly focuses on teaching readers how to build a 3 statement financial model in a spreadsheet. It does not go into the underlying accounting concepts or rules.
How to use this book
Modeling Projected or Forecasted Financial Statements (without a plug) – Simplified is built on our modeling boot camps experience and so it is best you adopt a hands-on boot camp attitude as you read this book to maximize your learning from this book. You should be confident of building a 3 statement financial model on your own in about 2 hours after working through this book. You should read this book at least twice to really understand how to forecast financial statements.
Your first read is meant to give you a quick overview of the entire process. You should follow the model spreadsheet as you progress through the book. Read each section one by one. Once you have read a section, review the corresponding region in the Microsoft Excel model. Change the relevant input assumptions to see how that section changes. Dig into each cell of the Microsoft Excel model row by row so you understand the formula and relationships section by section.
Your second read is when you should open up a blank workbook and build the exact 3 statement financial model yourself. We recommend that you re-read the book from the beginning. As you re-read each section re-create that section in a blank workbook. Begin by setting up the same input assumptions and build the projected financial statement section by section. Check each section of your model and proceed to the next section only after you are sure it reflects the sample model. Your final model should reflect the sample model when you are done. Change the relevant input assumptions in your model to check that the model returns reflect the changes in the book’s model. This should give you the confidence to build a 3 statement financial projection model on your own. Now you are ready to tackle the case studies provided. We recommend you work on the case studies with a friend or a tutor so that you get instant feedback. Good timely feedback boosts your learning process tremendously.
Chapter 1: Projected Financial Statements
Forecasting or projecting financial statements out into the future is an essential skill for an accounting or finance professional. Projected Financial Statements are essentially financial statements for the next few years built on expected or planned performance estimates such as revenue growth, cost of operations, planned investments, etc. Projected Financial Statements are useful in a number of ways including understanding the impact of various operational activities, financial policies or strategies on financial statements. Projecting financial statements also identifies cash requirements, funding needs and assists in communication, planning, etc.
Financial statements include the income statements, balance sheets, cash flow statements and statement of changes in shareholder’s equity. This book teaches you how to forecast or model the three most used financial statements which are income statements, balance sheets and cash flow statements. We skip modeling the statement of changes in shareholder’s equity statement in this book as it is rarely required in financial forecasting.
1.1 Ingredients Required to Project Financial Statements
The key ingredients required to project financial statements are:
- Historical income statements;
- Historical balance sheets;
- A good understanding of the business model; and
- Management plans for the immediate future.
Historical income statements and balance sheets provide a good base on which future performance drivers can be assumed or predicted. A good understanding of the business model will help better estimate future performance drivers. Management plans for the immediate future are very important especially if expansion, new investments or change in strategy or financial policy are expected because these will impact the projected numbers in multiple ways.
Most of the ingredients required to project financial statements can be found in the company’s annual report or Form 10K. The section titled Management Discussion and Analysis (MD&A) in a company’s Form 10K is a good starting point. Information about management plans can be found in industry reports, earnings calls and media reports.
1.2 Components of an 3 Statement Financial Model
A 3 statement financial model must have the following components. This is a basic model. Your financial model could have many more components but it should include the components listed below.
- Performance Drivers (Assumptions)
- Income statements
- Balance sheets
- Cash flow statements
- Supporting schedules
- Debt schedules
- Free cash flow
- Debt and dividend payments
We have omitted some components like a PPE and depreciation schedules, financial ratio analysis, tax schedule, etc. to keep our model simple. You should add them if your situation or case requires it.
1.3 Planning & Structure
You can structure your forecasted financial statements model in many ways. What is important is that you should have all the important components in the model and that the model must be intuitive to follow. An intuitive model is especially important if it is going to be reviewed by others.
You can have a separate tab for each of the above components or you can have the entire model in one single tab. The best way to structure the model will depend on the complexity of the model, the specifics of your situation and personal preferences. If you are modeling a simple 3 statement model with few supporting schedules, a one page model may be sufficient. However, if you want to model many nuances and need many schedules, a model with multiple tabs maybe a better way to proceed. We use a one page model template because we are modeling a simple 3 statement model. You are welcome to structure your model in any way you prefer.
FULL MODEL IMAGE
We have laid out the prior year’s financial statements on the left and the forecasted years or projected financial statements on the right. We have laid out the input assumptions on the top where it is most visible. The 3 financial statements follow the input assumptions. The income statement first, followed by the balance sheet and finally the cash flow statement. The supporting schedules are at the bottom as they are not viewed as often.
We will discuss each component of the model in detail in the next chapters.
1.4 Build Order
While you can build the 3 statement model in any order, we recommend that you build the 3 statement model in a sequence that makes sense. Here is the sequence we recommend:
- Gather historical income statements and balance sheets
- Compute historical performance ratios
- Forecast performance drivers/assumptions
- Build out projected income statements structure
- Build out projected balance sheets structure
- Forecast free cash flow to the firm
- Build the debt schedules
- Build the debt & dividend payment schedule
- Forecast cash flow statement
We jump right into building a 3 statement financial model in the next chapter with this foundation in place. Please download the model using the link provided in the next chapter because you are now going to start building the model.
Chapter 2: Assumptions that Drive the Model
We will now start building a simple 3 statement financial model in Microsoft Excel. Please download the sample model using this Microsoft link or Google spreadsheet here. Please follow the model spreadsheet as you progress through the book. Read each section one by one. Once you have read a section, review the corresponding region in the Microsoft Excel model. Change the relevant input assumptions to see how that section changes and what input it has in the final output figures. Dig into each cell of the Microsoft Excel model row by row so you understand the formula and relationships section by section. Make notes if required. During your second review, please open up a blank workbook and build the exact 3 statement model yourself from scratch. This will help you spot errors quickly because your output should be the same as the one in our model and book. Once you have replicated this financial model, you will be ready to pick a company that you would like to study and model its projected financial statements following these steps.
2.1 Historical Financial Statements: Income Statements and Balance Sheets
The very first step in projecting financial statements is to compile the historical financial statements of the company or organization. We use the income statement and balance sheet, to understand the business. We need at least the last 3 years financial statements to get a basic understanding of the business. It would be great to have 5 to 10 years of historical financial statements to get a better understanding of the business. The income statement and balance sheet will tell us what the business model is like, what are the major items of expenditure and their relative sizes, etc.
We compile (export) the historical income statement and balance sheet in to Microsoft Excel. For our model, we have used the last 3 years of historical financial data to understand the business. Please note that we indicate the historical figures in green font color and assumptions that drive the projected financial statements in blue font color to easily distinguish them.
2.2 Historical Financial Ratios and Performance Drivers
Once we have the prior year’s income statement and balance sheet in Microsoft Excel, we compute select operating and financial ratios to understand the business model. This is the foundation on which we will make assumptions that drive the forecasted financial statements.
On the income statement we first study historical year over year sales or revenue growth. The historical trend in sales or revenue growth assumption is, arguably, the single most important assumption of the forecasted financial statements model because almost all the other numbers in a projected model is driven of the sales figures for a year. Therefore it is very important to understand the historical trend and the factors that drive future revenue growth to make a good estimate because this assumption drives the forecasted financial statement.
Sales or revenue growth estimate for the forecasted years is not only driven by the historical trend but by multiple factors including management plans, product life cycle stage, industry developments, etc. Management plans could include announcements on geographical expansion or new product development, new capacity investments, etc.
The operating expenses are studied to understand what drives the operating expenses. For a simple financial model, the operating expenses can be considered as a percentage of sales. Expenses can also be a function of head count, number of locations, number of customers, etc.
The historical balance sheet is similarly analyzed to understand what drives the various operational items such as receivables, inventory and payables, the long term assets and other investments.
2.3 Assumptions that Drive the Projected Financial Statements
The most important inputs in a financial model are the various assumptions made regarding future performance. These assumptions drive the future operating and financial figures and therefore the three financial statements. The accuracy of any model’s output will depend on the quality of your input assumptions. The garbage in, garbage out (‘GIGO’) concept in computer science applies here. If your input assumptions are poor, the output will also be poor. The assumptions that drive the next few years’ performance are best derived from the prior year’s income statements and adjusted for new information, policy changes, etc.
It is best to have all the inputs and assumptions laid out in one clearly indicated section. All the input variables must be clearly indicated by using blue for the font color. We will address this in more detail in the formatting chapter. It is highly recommended to add comments in the cells or footnotes elsewhere so the source of the information is documented where applicable.
We categorize the input assumptions into two sections: 1) income statement assumptions and 2) balance sheet assumptions. These sections are discussed in detail below.
2.3.1 Income Statement Assumptions
Revenue Growth Assumption: The first and the most important assumption, is the assumption on revenue growth over the next few years. The revenue growth assumption should be based on a mix of factors. These factors include recent historical growth of the firm, industry expectations and specific plans or strategies of the buyer group. The drivers of revenue will be different for different firms and industries. Some firms have only one line of business whereas some firms have multiple lines of businesses with different growth rates. Your financial model must reflect the different sources of revenue of the appropriate firm.
The revenue growth rate can be constant over the entire forecast period or can be varied year by year based on the buyer’s plans. Management’s plans could include introduction of new products or services or elimination of divisions, etc. For example, if the firm is planning to introduce a slew of new products in the second year, the growth will be higher in the second year as compared with the first year and if they plan to drop a service line in the fourth year revenue growth will be slower that year.
Since we are building a simple financial model, we assume that the target firm has only one line of business. We assume that the revenues grow at the rate of 8% over the previous year in the first year (Cell G4). We also assume that the firm will have the same revenue growth rate for the entire forecast period (Cell H4 to K4). You can have different growth rates for each year in your model.
Operating Expenses: We estimate the various costs of operating the business after the revenue growth assumptions are made. Costs are broadly classified into cost of goods and services (CoGS), sales and general administration expenses (SG&A), research and development costs (R&D), depreciation and amortization expenses (D&A), interest expenses and taxes. Each of these costs is driven by different factors in different industries. A good financial model takes into account and models each cost as a function of its cost drivers.
We have assumed for simplicity that all our operating expenses are driven by the size of our revenues and so our operating costs are modeled as a percentage of revenues. The exceptions are 1) interest expense which is computed in the debt schedules based on the average debt outstanding and assumed interest rates and 2) income tax expense which is a function of the earnings before taxes and the tax rate. We have laid out these assumptions in rows 5 to 10. Please note that these expenses as a percentage of revenues assumption (blue font color) are often the average of the historical figures (green font color) or very close to it.
You now have almost all the assumptions to model the income statement if you have the assumptions for revenue growth and operating expenses in place. We now move on to the assumptions required to build the projected balance sheets.
2.3.2 Balance Sheet Assumptions
Working Capital Assumptions: The cash required to run the business on a day to day basis is referred to as its working capital. A business may not get paid for its products and services when an order is placed. But the business has to pay cash to buy inventory and has to pay expenses such rents, wages, etc. to provide its products and services after which it gets paid cash. Therefore cash is required on a day to day basis for the smooth functioning of a business and this cash is referred to as its working capital. Working capital is estimated to be equal to the business’ current assets minus current liabilities. Current assets include assets like inventory and accounts receivables that are converted into cash in a few weeks or months. Current liabilities are liabilities like accounts payable which needs to be paid in 12 months or sooner.
We need to estimate the quantity of current assets and liabilities carried in the balance sheet to arrive at the working capital required to run the business. Current assets like inventory and accounts receivables and current liabilities like accounts payable are a function of the size of the operations of the firm. (Larger the revenues, larger the amount of inventory, accounts receivables and accounts payable). We therefore link accounts receivables to the revenues in terms of the days outstanding. We link inventory and accounts payable to the cost of goods sold in terms of the days outstanding. We make assumptions on days sales outstanding (DSO), days inventory outstanding (DIO), days payable outstanding (DPO) to estimate the working capital required to run the business. We therefore add these variables to the operating assumptions section in rows 11-13. We have also assumed that the number of days in a year is 360 in cell G19.
The days sales outstanding indicates how many days of sales is yet to be collected as cash from customers. The days inventory outstanding indicates how many days of worth of costs is unfinished and in inventory at the end of the year. The days payable outstanding indicates how many days worth of costs is yet to be paid to vendors and suppliers. These assumptions are driven by management plans and financial policies. Historical figures (green font color) also provide an estimate of the figures as a guide to make these assumptions.
Property Plant and Equipment and Depreciation: Every business will need to invest in maintaining and upgrading its capital equipment. These investments are referred to as capital expenses (CAPEX). We need to account for the capital expenses required to maintain and grow the business in our financial projections. Capital expenses can be estimated based on specific plans or investments that will be undertaken by the firm. Capital expenses may or may not be even and may occur in cycles depending on the firm/industry. Capital expenses are driven by different factors in different companies/industries including revenue growth, capacity installed, geographies/locations planned (bottling plants), number of employees planned (services), new stores planned (retail), etc. Capital expense can also be estimated as a percentage of revenue or cost of goods sold to keep the model simple. We chose revenue as the key driver for capital expenses and estimate CAPEX as a percentage of revenues in row 14 in this simple model.
Depreciation and amortization also can be modeled in a variety of ways. We can have a separate supporting schedule to compute depreciation using any one of the many depreciation and amortization methods for different blocks of assets (straight line, double declining balance, MACRS, etc.) In this simple financial model, we decided to model depreciation too as a percentage of revenues and have laid out the depreciation assumption in row 8.
Other Assumptions: In order to forecast financial statements, we need to make a few other assumptions. These assumptions include 1) debt repaid each year 2) dividends paid out and 3) minimum operating cash balance required.
The debt repaid each year assumption could be a fixed number, a percentage of cash flows or a percentage of debt. This plays an important part in the debt schedule and the free cash flow schedule. This assumption could be a static number if adequate cash flows are expected or one based on the amount of free cash flow available to service debt. We will discuss this in more detail when we discuss the debt schedules. We have assumed a fixed debt repayment of $2 million annually in this simple model in row 16.
The dividends paid out is usually a factor of the net income and available cash flow. We have assumed that the firm will payout 50% of its net income each year if it has positive cash flows. You can also model a minimum dividend payout if the company has preferred shares and is obligated to payout a preferred dividend annually.
The minimum operating cash balance required is cash required for the business to operate smoothly during the year. This assumption is a function of its working capital and business model. We have assumed that the business will require $5 million to operate smoothly during the year in cell G18.
You will now have all the assumptions required to forecast your financial statements. We will see how these assumptions are used to forecast the income statement, balance sheets and cash flow statements in the next few chapters. We will start to build out the projected income statement structure in the next chapter.
Chapter 3: Forecasting Income Statements
The income statements reflect the operating performance of the firm for the period under study. It drives the changes in the balance sheet. Therefore, we start with forecasting the income statements when building the 3 statement financial model.
Projecting an income statement is relatively straight forward once you have the income statement structure and assumptions that drive the next few years’ performance. The assumptions that drive the next few years’ performance, made in the previous chapter, were based on the prior year’s income statements figures and our expectations of the future performance.
3.1 Revenues
We start by projecting revenues of the firm for the forecast period. As indicated earlier, the assumption on revenue growth over the next few years is a very important assumption and drives the entire financial model. The revenue growth assumption should be based on a mix of factors. These factors include recent historical growth of the firm, industry expectations and specific management plans and strategies. The drivers of revenue will be different for different firms and industries. Some firms have only one line of business whereas some firms have multiple lines of businesses with different growth rates. Your financial model must reflect the different sources of revenue of the appropriate firm.
We assumed that revenues grow at 8% per year for the 5 year forecast period (cell G4 to K4). We therefore grow the most recent year’s revenue (cell E23) by the growth rate assumption for the first year (G4) to arrive at the revenue for the first year in cell G23. Each subsequent year’s revenue is arrived at by growing the previous year’s revenue by the corresponding growth rate. If we had multiple streams of revenue, we would start with the most recent year’s revenues and grow each stream at the assumed growth rates and add them up to get the total revenues of the firm.
3.2 Expenses
Four types of operating expenses were estimated as a percentage of revenues. These are the cost of goods and services (CoGS), sales and general administration expenses (SG&A), research and development costs (R&D) and depreciation and amortization expenses (D&A). We multiply each expense item’s percentage estimate with the appropriate year’s revenue to arrive at the operating expense for each projected year.
We follow the income statement structure by first subtracting CoGS from revenues to arrive at the gross profits in row 25. We then subtract SG&A and R&D from the gross margin to arrive at the earnings before interest, tax, depreciation and amortization (EBITDA) in row 28. From here we subtract D&A to arrive at earnings before interest and tax (EBIT) in row 30.
We need to estimate interest expense for each year before we subtract it from the EBIT to arrive at the earnings before tax (EBT). Only then can we estimate the tax expense and arrive at the net income. The interest expense, however, is a function of the debt outstanding and so we need to build out the debt schedules to arrive at the interest expense. Since we have not built the debt schedules as yet, we leave the projected interest expenses in cells G31 to K31 empty as a place holder for interest expenses and continue to build out the rest of the income statement.
We apply the tax rate (assumption made in row 10) to the earnings before tax (EBT) to arrive at the income tax expense in row 33. We then deduct the income taxes expense from the EBT to arrive at the net income for each year in row 34. The formulas used in each row are highlighted below. Please look at this in tandem with the model or images provided in the appendix.
We will now move on to projecting the balance sheets in the next chapter.
Chapter 4: Forecasting Balance Sheets
Balance sheets reflect the values of the assets and liabilities as on a specific date. We take different approaches to forecasting the current and non-current items in a balance sheet. We link the current assets and current liabilities or the working capital figures with the size of the operations because the size of the operations determine the quantum of current assets and current liabilities required. Other assets and liabilities or long term assets and liabilities are forecasted based on changes driven by operational performance and management plans.
4.1 Projecting Working Capital
Working capital figures in a balance sheet such as like accounts receivable, inventories and accounts payables will depend on the size of the operations. We therefore linked accounts receivables to the revenues in terms of the days outstanding. We linked inventory and accounts payable to the cost of goods sold in terms of the days outstanding in the assumptions section. So we use days receivables outstanding, days inventory outstanding, days payable outstanding assumptions (from rows 11-13) to estimate each type of current asset and current liability to arrive at the working capital required to run the business. We estimate the components of working capital as follows:
Accounts Receivable: We project accounts receivables’ figures in row 39. We start with our assumption on the number of days sales outstanding (DSO). This is also referred to as days receivables outstanding and indicates how many days of sales is yet to be collected as cash from customers. This assumption is made in row 11 and is based on historical trends and management policy. We divide the annual revenue by the number of days in the year (G19) to gives us the average daily sales. We then multiply the average daily sales by the number of days sales outstanding (our assumption from row 11) to get the accounts receivable outstanding at the end of the year in row 39.
Inventory: We project inventory figures in row 40. To arrive at the inventory levels at the end of each year, we start with our assumption on the number of days inventory outstanding (DIO). The days inventory outstanding indicates how many days of worth of costs is unfinished and in inventory at the end of the year. So we divide the CoGS by the number of days in the year which gives us the average daily CoGS used. We then multiply the average daily CoGS by our assumption on the days inventory outstanding (row 12) to get the inventory held at the end of each year in row 40.
Accounts Payable: We project accounts payables’ figures in row 51. Here we start with our assumption on the number of days payables outstanding (DPO). The days payable outstanding indicates how many days worth of costs is yet to be paid to vendors and suppliers. We divide the CoGS by the number of days in the year which gives us a proxy for the average daily purchases. We multiply the average daily purchases by our assumption on the number of days payables outstanding to get the accounts payable outstanding at the end of the year in row 51.
We assume that the current assets consist of accounts receivable and inventory only. We also assume that the current liabilities consist of just accounts payable only to keep our financial model simple. If you have other current assets or current liabilities, please feel free to add them.
4.2 Long Term Assets
Long term assets like property, plant, equipment, goodwill, etc. form a large component of the balance sheet in most businesses. The values of these long term assets will be a continuation of the most recent values of these assets. Therefore we start with the most recent historical balance sheet figures and adjust it up or down based on the changes brought about by the company during the year.
Property, Plant and Equipment (PPE): PPE is a long term asset in most balance sheets. It is listed as net PPE on the balance sheet or broken down into its components: gross PPE, accumulated depreciation and net PPE. We show the three components of PPE in the balance sheet in rows 43 to 45.
We made assumptions about the amount of capital expenses required each year in the assumptions section. We chose revenue as the key driver for capital expenses and so a percentage of revenues assumption was made in the assumptions section (row 14). We multiply the percentage of revenues estimate with the corresponding year’s revenue to arrive at the capital expense for the current year. We simply add the capital expense for the current year to the prior years gross property, plant and equipment figure to arrive at the gross property, plant and equipment for the current year in row 43.
Similarly, we made assumptions about the depreciation expenses for each year in the assumptions section. We chose revenue as the key driver for depreciation expenses and so a percentage of revenues assumption was made in the assumptions section (row 8). We multiply the percentage of revenues estimate with the corresponding year’s revenue to arrive at the depreciation expense for the current year. The depreciation expense for that year is already computed in the income statement in row 29. We simply add the depreciation expense for that year to the prior years accumulated depreciation figure to arrive at the accumulated depreciation expenses for the current year in row 44.
The net property, plant and equipment figure in row 45 is the difference between the gross property, plant and equipment figure and the accumulated depreciation figure computed in the above two paragraphs (rows 43 and 44 in the model). We have assumed that there has been no sale of PPE in this simple model. If we need to factor in disposals or sales, it is best done in a separate schedule and then transferred into the balance sheet.
4.3 Other Assets
Other Assets include assets like patents, goodwill, non cash assets like investments, securities, bonds, etc. Assets like patents, copyrights and goodwill can be computed in the same manner as the PPE described above with the term amortization used instead of depreciation. The amortization schedule will be different and based on the nature of the asset. Non cash assets like investments, securities, bonds, etc. are reflected based on purchase or sale assumptions made. In our simple model we assume that there is no sale or purchase and so flat line (or hold steady) other assets at the most recent historical figure (row 47).
We add up all the assets to arrive at total assets.
4.4 Liabilities
The liabilities section, in most balance sheets, is broken down into current liabilities and long term liabilities. Current liabilities consists of liabilities such as notes payables or accounts payables that is due in the next 12 months. We have discussed this in the working capital section. Long term liabilities are liabilities that are payable beyond 12 months.
4.4.1 Debt Schedules
Long term liabilities comprise of different types of debt including bank debt, bonds, etc. Each source of debt will have differing terms and conditions. Terms that differ include interest rates, repayment terms (steady fixed repayment, interest only with a bullet payment, at will, etc.), collateral required (unsecured, secured), operating conditions to be met (target ratio levels and approvals for sales of assets), etc. If we have different types of debt, we should model each type of debt separately. We have assumed that there is only one kind of debt in our simple financial model and have modeled the debt schedule in rows 110 to 116.
We start with the opening balance of the debt. This will be found in the most recent historical balance sheet of the company. Below the opening balance in row 112 we add the debt added or new debt taken (row 113) and the debt repaid during the year (row 114). Both new debt taken and debt repaid during the year are discussed in the cash flow section and so we leave two blank rows as a place holder here. We get the ending balance of debt in row 115 by adding the debt added row and subtracting the debt repaid row to the beginning balance.
We then compute the interest expenses for the year by multiplying the assumed interest rate for that type of debt with the debt balance. Debt could be repaid during the year or at the end of the year and the assumption we use must be reflected in the model. If we assume that debt is repaid at the end of the year, we multiply the beginning balance with the interest rate to arrive at the interest expenses for the year. If we assume that the debt may be repaid over the year, like we do in this financial model, we multiply the interest rate with the average of the beginning balance and ending balance to arrive at the interest expenses for the year. We have made the effective interest rate assumption in row 9. We multiply the effective interest rate with the average of opening and ending balance to get the interest expense for the year. This interest expense is taken to the income statement in row 31 to complete the projected income statement. If you have multiple types of debt, the interest expense of all the different types of debt is added up and taken to the income statement in row 31 to complete the projected income statement.
4.5 Shareholders’ Equity
The shareholders equity section comprises of common stock, additional paid in capital and retained earnings. We will need a separate schedule if we have changes in common stock and additional paid in capital during the year. This schedule will be tied to the free cash flow schedule to reflect cash coming in or going out as new issues or stock buy backs, etc. We assume that there are no changes in common stock and additional paid in capital in this simple model and so we leave the common stock and additional paid in capital at the most recent historical figures (row 58).
The retained earnings, however, will be impacted by the operational performance and dividend payments. We add the net income to and subtract the dividends paid from the retained earnings of the prior year in row 59. At this stage we do not have the dividends paid computed and so leave G59 to K59 as blank cells. We will complete this after we compute the debt and dividend payments schedule.
The sum of common stock, additional paid in capital and retained earnings becomes the total shareholders equity total in row 60.
4.6 Golden rule
We complete the liabilities side of the balance sheet by adding up the total liabilities in row 56 and total shareholders’ equity in row 60 and place it in row 62with the title total liabilities and shareholders’ equity. At this point you are ready to check if the basic balance sheet equation or the golden rule in accounting holds true for your financial forecasts. The golden rule in accounting states that total assets must always equal total liabilities and shareholders’ equity (when the model is correct and complete).
Total Assets (row 48) = Total Liabilities & Shareholders’ Equity (row 62)
We rearrange the equation as follows:
Total Assets (row 48) – Total Liabilities & Shareholders’ Equity (row 62)= 0
We put in this equation in row 63 but remember that at this point it will not balance or your total assets will not be equal to your total liabilities plus shareholders’ equity. This is because you still have many rows that are yet to be filled up. We usually put this ‘alert’ in a row below the total liabilities plus shareholders’ equity row with the title balance check or an equivalent term.
4.7 Free Cash Flow Projections
We are ready to compute free cash flows once we have income statement, the working capital figures, debt schedules and the capital expenses estimates. We will use the following formula to computing free cash flows:
Free cash flows = Net income + depreciation and amortization – investment in working capital – investment in capital expenditure
This formula arrives at the free cash flow from the operations of the business by first adding depreciation and amortization expenses to net income because depreciation and amortization is a non-cash item deducted from revenues in the income statements. The formula then subtracts the cash required to run the business (investment in working capital and capital expenses) but not already deducted to arrive at the net income in the income statement.
Net income and depreciation have been computed in the operating performance section in rows 34 and 29 respectively.
Investments in working capital: To arrive at the next component of the free cash flow formula – the investments required in working capital each year, we first need to compute the working capital required in a business each year. The working capital required to support the operations is estimated in row 15 by subtracting the current liabilities from the current assets.
Working Capital = Current Assets – Current Liabilities
We assume that the current assets consist of accounts receivable and inventory and current liabilities consist of accounts payable to keep our financial model simple. Please note that the cash required to support a growing business is NOT the working capital required each year. The cash required to support the growing business is ONLY the increase in working capital required each year. We compute the cash investment required for working capital in row 98 as follows:
Investments in Working Capital = Working Capital (t) – Working Capital (t-1)
This would also mean that we are able to extract cash if there is a decrease in working capital required. Such situations would be depicted with a negative figure in row 98.
Capital expenditure: Capital expenditure required each year as a percentage of revenues was estimated in row 14. We therefore multiply the estimated percentage required for CAPEX with the revenues each year in row 23 to arrive at the cash required for capital expenses in row 99.
To finally arrive at the free cash flow from the operations of the business in row 100, we add depreciation and amortization expenses to net income and subtract investments in working capital and capital expenses.
Free cash flows = Net income + depreciation and amortization – investment in working capital – investment in capital expenditure
This free cash flow in row 100 is the free cash flow generated by the business after meeting its operating and capital needs.
4.8 Debt & Dividends schedules
What should the firm do if it has excess cash or free cash flow after meeting its operating and capital needs? The firm can either use this excess cash to pay back debt and/or pay dividends to its shareholders.
We now arrive at the tricky part of modeling a 3 statement model without a plug figure.
We layout how much excess cash the firm has and if it can or should pay back debt and/or pay dividends to its shareholders in the debt and dividends schedule. We start computing the the cash available at the end of the first year before debt and dividends are paid in cell G104 by adding the ending cash balance from the most recent prior year (cell E38) to this year’s free cash flow (cell G100). This is the cash available to run operations for the next year, pay down debt and/or pay dividends.
At this point, we check if we have the minimum cash balance required to run the business and pay back mandatory debt payments. We have assumed that we need $5 million to run the business in cell G18. We also assumed that we need to repay $2 million of debt each year in row 16. We assume we will borrow money using our revolver loan facility if we do not have the minimum required cash balance and the cash required to pay down debt each year. This is implemented in Microsoft Excel using the MAX() function in row 105. We ask Microsoft Excel to compute the maximum of zero and the minimum cash balance required to run the business (cell G18) plus (and) pay back mandatory debt payments (row 16) minus the ending cash balance (row 104). This formula gives us a positive number if minimum cash balance and mandatory debt payments are more than the ending cash balance indicating that we have to borrow more cash. If the ending cash balance is more than the minimum cash balance required, we will get a negative number which is less than a zero and therefore the Max() function will choose the zero indicating that we do not need to borrow more cash.
If we have more cash than the required minimum balance, we can use the excess to repay debt or pay dividends. The debt repaid can be modeled based on the amount of cash available and the target debt level but in this simple model we made an assumption that the debt terms require a repayment of $2 million each year in row 16. We therefore link debt repaid in row 106 to the debt assumption in row 16.
Any cash flow remaining after repaying debt can be used to pay dividends. We have made an assumption that if the firm has profits and positive cash flows to repay mandatory debt repayments, then 50% of net income is paid out as dividends. We model this using the IF and AND functions in Microsoft Excel. We use the AND function inside the IF statement to make certain that we have profits and free cash flows to meet mandatory debt payments and minimum cash balance. The AND function will return TRUE only if the firm has profits and free cash flows to meet mandatory debt payments and minimum cash balance. If the AND function returns TRUE for the logic component, the IF statement multiplies the net income in row 34 with the dividend-net income percentage assumption in row 17 to compute the dividends paid in the current year. If the AND function returns a FALSE for the logic component, we get a zero dividend payment.
Both the new debt drawn and the debt repaid are now carried into the debt schedule to complete the debt schedule and the liabilities section of the balance sheet.
The ending cash balance is arrived at by adding the new debt drawn (row 105) to the ending free cash flow before debt and/or dividends (row 104) and subtracting any debt repayment (row 106) and dividends paid (row 107). This ending cash balance in row 108 is the input for the cash balance in the balance sheet in row 38.
Chapter 5: Forecasting Cash Flow Statements
The third financial statement is the cash flow statement. The cash flow statement is very important to understand how a business is generating cash and what it is doing with its cash. You have all the ingredients required to prepare the forecasted cash flow statements if you have forecasted the income statements and balance sheets. The cash flow statement essentially classifies the cash flows of the business into three distant activities: operating activities, investing activities and financing activities. We indicate a cash inflow as a positive number and a cash outflow as a negative number in a cash flow statement.
5.1 Cash Flow from Operations
The operating activities comprise of the core revenue generating activities of the firm. The revenue generating activities of each firm varies but includes the cash generated from buying and selling its products and services. Cash flow from operating activities does not including cash used in buying or selling long term assets (classified as investing activities) and cash flow from providers of equity or debt (classified as financing activities).
We use the formula below to generate the cash flow from operations:
Net income
+Depreciation and amortization
– Increases in current assets
+ Increases in current liabilities
= Cash flows from operations
The net income and depreciation and amortization figures are available in the income statement in rows 34 and 29. We copy them to the cash flow statement in rows 68 and 69. The current assets in the operations section of the cash flow statement include accounts receivables and inventory and EXCLUDES cash. The current liabilities in this simple model includes only notes payables. The forecasted current assets and current liabilities figures are available in the balance sheets. Please note that the formula does not include current assets and liabilities by the INCREASES current assets and liabilities. We therefore need to subtract the prior year’s figures from the current year’s figures for each item to arrive at the increase in each current asset and current liabilities. We list these out in rows 71 to 73 in our model.
Finally the cash flow from operations figure is arrived at in row 74 by adding depreciation and amortization to the net income and subtracting the increases in current assets and adding back the increases in current liabilities. We reflect a negative number in the increases in current assets or current liabilities figures if there has been a decrease in current assets and/or current liabilities. The net cash flow from operations is a positive number if the operations have produced cash and a negative if it has consumed cash.
5.2 Cash Flow from Investing Activities
Transactions involving the buying or selling of long term assets and investment securities are classified under the investing activities section. The cash flow from investing activities can have the following components.
Sale of PPE
– Investments in PPE (CAPEX)
– Investments in other assets
+ Sale of other assets
= Cash flow from investment activities
Investment in capital assets is already computed in our free cash flow schedule in row 99 using the assumption on annual capital expenditure. We carry this over into the cash flow from investing activities section as a negative number because cash outflows are indicated by a negative number.
We have place holders for other common transactions that fall under the category of investing activities in rows 77 to 79. We have not assumed any sale of PPE and activities in other assets in this model and so they do not reflect any cash flows. Please note that cash outflows such as in the case of investments in PPE or purchase of other assets are indicated with negative values and inflows such as sale of PPE and other assets are indicated with positive values. We add up rows 76 to 79 to get the net cash flow from investing activities. The net cash flow from investing activities is a positive number if the firm has sold more assets than it has purchased and vice versa.
5.3 Cash Flow from Financing Activities
Transactions with the providers of capital, both debt and equity, fall in the financing activities section of the cash flow statement. Transactions with equity shareholders include new issues of stock, stock buybacks, and dividend payments. Transactions with lenders include issue of new debt or debt principle repayments.
Issue of equity
– Equity retirement or treasury buybacks
– Dividends paid
+New debt / borrowings
-Retirement or repayment of debt
Cash flow from financing activities
We have assumed that we do not have any new issues of equity or purchase of treasury stock in this simple model but have left a placeholder row for these transactions. We have already compiled all the other inputs required to model the cash flow from financing activities in the supporting schedules. The payment of dividend is computed in the debt and dividend payments schedule (row 107) and the retirement of debt is an assumption made in row 16 and discussed earlier.
Please note that cash outflows such as the payment of dividends or share buybacks or debt repayments will have negative values and inflows such as issue of stock or raising new debt are indicated with positive values. We add up rows 83 to 87 to get the net cash flow from financing activities. The net cash flow from financing activities is a positive number if the firm has taken more money from its providers of capital than it has returned to the providers of capital and vice versa.
5.4 Net Cash Flow for the year and Ending Cash Balance
We add up the cash flows from operating activities, investing activities and financing activities to arrive at the net cash flows for the year. This is then added to the cash balance at the beginning of the year to arrive at the ending cash balance.
Cash flow from financing activities
+Cash flow from financing activities
+Cash flow from financing activities
=Net cash flow for the year
+Beginning cash balance
=Ending cash balance
A good way to cross check if you have prepared the statement of cash flows correctly is to make sure the ending cash balance in row 92 matches the cash position in the balance sheet in row 38.
Congratulations! If you have got this far, you have just built a 3 statement financial model. This maybe a simple model but it has all the working components of a full model. You can add any number of additional features relevant to your situation as supporting schedules to this model to make it as complex as you require. We will look at a few additional features that you could consider adding to this basic the model in chapter 7.
Chapter 6: Presentation & Formatting
People do judge a book by its cover. A well-structured, formatted and presented model will communicate accuracy, thoroughness and professionalism. In this section, we discuss the formatting guidelines you should consider when building a financial model.
Professional financial models have some rules unlike other types of models such as inventory tracking models, expense analysis models, scheduling models, etc. These are not really rules but more convention, etiquette or tradition. Following convention, etiquette and tradition makes it easier for a knowledgeable reader to follow the model faster. A professional-looking model will impress the reader more than an equally good but unprofessional-looking model. So pay attention to the way you format your model especially if you intend to share it with others or are being evaluated by anyone else.
6.1 Order & Structure of the Financial Statements
Differences exist between U.S. Generally Accepted Accounting Principles (US GAAP) and International Financial Reporting Standards (IFRS). The order and structure of the financial statements must match the regulations that apply to your situation as far as possible. The names and terms of various line items and components of the model also vary across geographies.
The focus of this book is on modeling and forecasting financial statements and so we do not go into the regulatory requirements of the presentation of financial statements.
6.2 Formatting Guidelines
Here are a few guidelines you should adhere to:
Use blue as the font color for input cells: This will enable your reader to quickly identify the input cells and distinguish them from the computed cells.
Use black as the font color for computed cells and text: All the computed cells must be in black ink so the reader knows that these are not be altered. Black is also used as the font color for text because it is not an assumption or input to be altered.
Use green as the font color for information from other files/workbooks: All cells with data from external sources must be in green font so the reader knows that this data has come from external sources.
Set up ‘Print Areas’: Your reader may want to print out the model. Set up ‘Print Areas’ from the ‘Page Layout’ ribbon in the model so your readers can print out the model neatly if the need arises.
Specify units & currency measures: Define the metrics, units and currency as appropriate at the beginning of the model and on every page. It is also common to indicate the currency symbol on the first and last rows of each section but it is best avoided in every cell to improve readability.
Colors: Different firms or groups within larger companies choose different formatting colors for headings, tabs, etc. either by chance or to match corporate colors. You can choose the preferred colors if you know your readers’ preference. Please ensure that the numbers are easy to read. We chose a light grey to increase the readability of the model.
Protect the sheet & lock cells: Lock the entire worksheet except the assumption input cells in the input section. This allows the reader to make changes in the input & assumption sections only and avoids unintentional changes happening in the LBO model.
6.3 Other Formatting Options
The above guidelines will give your LBO model a professional look and feel. There are many additional options that you can choose from based on your preference. We reiterate here that different corporates, firms and institutions have preferred formatting guidelines and so if you are interviewing or building a model for a specific target audience, use their preferred formatting guidelines and colors. Formatting options that you can choose include:
- Font style: Theme, bold, italic, size, etc.
- Conditional formatting
- Borders
- Totals
- Number of decimals
- Alignment
- Percentages
- Dates
Whatever you choose, use it consistently throughout the model. Keep in mind the golden rule: Formatting must make the model easy to read, understand and follow.
Chapter 7: Additional Financial Model Features
We promised you that we will teach you how to build a simple 3 statement financial model for two reasons: 1) once you can confidently build a simple model, you can add any additional features your situation warrants and 2) in many situations, like in an interview setting, you have limited time to build a model and only a simple model is possible in limited time situations.
There are many more features you can add to a 3 statement financial model if you have time. Here is a list of possible features or sections you can add to your 3 statement financial model if required.
Property, Plant & Equipment & Depreciation Schedules
We have made a simplifying assumption that the capital expenditure and depreciation are going a percentage of revenues. We could build out one or more schedules to compute the capital expenditure and depreciation for different blocks of assets. Capital expenditure can be based on other factors such as geographic expansion, age of machines, capacity utilization, projected growth, etc. Depreciation schedules can be built on MACRS depreciation schedules or other methods like the double declining methods in multiple asset blocks if appropriate.
Time Periods/Fractional years
We have assumed that we are modeling the financial statements for whole years. You can model performance by month, quarter or half years if a more detailed view is required. Your years also need not start with a 12 month period. It could be more or less if your situation requires.
Debt Schedules
We have assumed that there is only one kind of debt. Businesses can have multiple types of debt with different interest rates, repayment schedules and debt covenants. Each kind of debt can be modeled individually.
Preferred Dividends
Different types of equity can have different dividend terms. Each type of equity dividend can be modeled individually. We have made an assumption that whenever the firm has profits and has sufficient cash to pay dividends, 50% of net income is paid out as dividends.
Deferred Taxes, Tax Assets and Tax Liabilities
We have assumed a tax rate of 40% on pre-tax income in this simple model. You can model taxes separately featuring deferred taxes, tax assets, tax liabilities, loss carry forward, federal and state taxes, etc. if you have access to the appropriate details.
Investments
Companies could have investments securities or in other firms including subsidiaries. These have to be modeled separately if your situation requires it.
Statement of changes in equity
This book does not teach you how to build the statement of changes in equity because it is not frequently required. This statement maybe required if you have different categories of owners and significant changes in equity and/or ownership is planned.
Competitor Analysis
We have not done a competitor analysis because this book focuses on teaching the mechanics of a model. Competitor analysis will help you make better assumptions and estimates of projected performance.
Ratio analysis
You can model a performance measurement schedule with detailed ratios to reflect both operational and financial ratios.
Appendix: The Three Statement Financial Model
Download the live model using this Microsoft link or Google spreadsheet here.
Detailed Table of Contents
Preface
Who is this book for?
How to use this book
Chapter 1: Projected Financial Statements
1.1 Ingredients Required to Project Financial Statements
1.2 Components of an 3 Statement Financial Model
1.3 Planning & Structure
1.4 Build Order
Chapter 2: Assumptions that Drive the Model
2.1 Historical Financial Statements: Income Statements and Balance Sheets
2.2 Historical Financial Ratios and Performance Drivers
2.3 Assumptions that Drive the Projected Financial Statements
2.3.1 Income Statement Assumptions
2.3.2 Balance Sheet Assumptions
Chapter 3: Forecasting Income Statements
3.1 Revenues
3.2 Expenses
Chapter 4: Forecasting Balance Sheets
4.1 Projecting Working Capital
4.2 Long Term Assets
4.3 Other Assets
4.4 Liabilities
4.5 Shareholders’ Equity
4.6 Golden rule
4.7 Free Cash Flow Projections
4.8 Debt & Dividends schedules
Chapter 5: Forecasting Cash Flow Statements
5.1 Cash Flow from Operations
5.2 Cash Flow from Investing Activities
5.3 Cash Flow from Financing Activities
5.4 Net Cash Flow for the year and Ending Cash Balance
Chapter 6: Presentation & Formatting
6.1 Order & Structure of the Financial Statements
6.2 Formatting Guidelines
6.3 Other Formatting Options