Projected Income Statement Template Excel – How to Predict an Income Statement Income Statement Gross Income vs. Net Gross Sales Credit Sales Cost of Goods Sold (COGS) Cost of Goods Manufactured (COGM) Gross Profit Operating Expenses SG&A Expenses EBIT (Operating Income) Research and Development (R&D) COGS vs. Expenses Direct and Indirect Expenses Overhead Earnings Before Taxes (EBT) Effective Tax Rate Effective vs. Marginal Tax Rate Depreciation Tax Shield Interest Tax Shield Interest Income Net Income
Profit and loss forecasting is an important part of building a 3-statement model because it drives most of your budgeting and cash flow forecasting. In this guide, we look at general approaches to forecasting major P&L items in the context of integrated 3-statement modeling.
Projected Income Statement Template Excel
Before we begin any predictions, we begin by introducing historical results. The process involves manually entering data from the 10K or press release or using an Excel plug-in through financial data providers such as Factset or Capital IQ to enter historical data directly into Excel.
Free M&a Excel Templates For Download
Some companies report revenue at the segment or product level and details of operations in the footnotes (which are attached to the consolidated statement of financial results). For example, while Apple provides a consolidated “net sales” figure in its income statement, the notes report sales by product (iPhone, iPad, Apple Watch, etc.).
If it is important that the final model includes scenario analysis, for example, what if iPhone unit sales exceed expectations, but the average iPhone selling price is lower than expected? — a detailed analysis of the historical segment is useful for creating a basis for forecasts. If not, just rely on the net sales line on the income statement.
Not all companies evaluate the results of their activities in the same way. Some businesses combine all operating expenses into one line item, while others break them down into several items. If our model is to be used to compare the performance of other companies, classifications should be simple and often require us to make decisions about how to classify items and whether to look for more detailed structure in the financial notes.
For example, notice that Apple’s 2016 income statement includes a line item titled “Other income/(expense), net” for $1,348 million. This line aggregates interest expense, interest income, and other non-operating expenses, as we can see in Apple’s 10K notes:
Quick Monthly Budget Template Excel Free
Because three-statement financial models must predict future interest expense based on debt levels and interest income based on future cash levels, we had to define and use the more detailed breakdown provided in the footnotes.
Companies prepare their historical income statements in accordance with US GAAP or IFRS. That means the income statement won’t include financial metrics like EBITDA and non-GAAP operating income, which ignore certain items like stock-based compensation. As a result, we often have to dig into the notes and other financial statements to get the data we need to present our income statement data in a way that is useful for analysis.
If you compare this to Apple’s actual earnings report (shown above), you’ll notice a few differences. In the model:
After entering historical data into the model, predictions can be made. Before we dive in, let’s establish some predictive realities.
Financial Projections_12 Months Template
While our goal in this article is to provide insight into the mechanism behind effective modeling, a much more important aspect of forecasting is something this guide cannot provide: a deep understanding of the business and industry in question. To forecast a company’s revenue, an analyst must have an understanding of the company’s business model, key customers, target market, competitive position, and sales strategy. Garbage in = garbage out, as the old saying goes.
Most investment banking analysts spend very little time doing the due diligence necessary to arrive at their assumptions. Instead, they rely on stock research and management evaluations to provide “management justifications” and “forecasts” of future performance. Then the analyst ideally builds other cases that should show what would have happened if the street and management cases had not materialized. This is why many people consider investment banking models to be stylish and inconsequential. A buy-side or private equity analyst, on the other hand, will spend much more time understanding the assets they are considering for investment. After all, if they get their assumptions wrong, their bottom line will suffer.
Assumptions are the most important part of getting the model “right”. But a convoluted, error-prone, and unintegrated model will never be a useful tool, despite the large underlying assumptions.
Subscribe to the Premium Package: Learn how to model financial statements, DCF, M&A, LBO and Comps. The same training program is used in the best investment banks.
Trucking Financial Projection Template
The revenue (or sales) forecast is probably the most important forecast in most 3-statement models. Mechanically, there are two common approaches to revenue forecasting:
Approach 1 is simple. In our example, Apple’s revenue growth last year was 9.2%. For example, if the analyst expected the growth rate to continue over the forecast period, revenue would simply grow at that rate.
On the other hand, if the analyst has an idea of changes in prices and volumes by segment, a more comprehensive approach to forecasting is required. In this case, the analyst would make clear assumptions about volume and price for each segment. In this case, instead of directly predicting the consolidated growth rate, the consolidated growth rate is the result of a model based on the accumulation of the price/volume segment.
Make an assumption about the gross profit margin percentage (gross profit/revenue) or the cost margin percentage (COGS/revenue) and enter this amount in COGS dollars. Historical margins help create a benchmark that the analyst can incorporate directly into the forecast period or reflect a thesis that emerges from a particular perspective (which the analyst develops on his own or, more likely, based on stock research).
Free Financial Statement Templates For Small Businesses
Operating expenses include selling, general and administrative expenses, and research and development expenses. All of these costs are driven by revenue growth or the clear expectation of possible margin changes. For example, if last year’s return on general and administrative expenses was 21.4%, the forecast “We do not have theses on general and administrative expenses” for the next year would simply mean a decrease in the previous year’s margin by 21.4%. Of course, if we expect changes, they will usually be reflected in a clear change in margin assumptions.
Depreciation is usually not directly classified on the income statement. Rather, they are embedded in other operating expense categories. However, it is usually necessary to forecast D&A to get an EBITDA forecast. Because D&A costs are a function of historical and expected capital expenditures and acquisitions of intangible assets, they are actually projected on the balance sheet and shown on the income statement after the formation is complete.
Like D&A, stock-based compensation is included in other operating expense categories, but historical amounts can be found clearly in the statement of cash flows. Stock-based compensation is typically expected as a percentage of revenue.
Like depreciation and amortization forecasting, interest expense forecasting is done as part of the balance sheet in the debt schedule and is a function of forecasted debt balances and forecasted interest rates.
Coffee Shop Financial Projections Template
Interest expense is determined based on the company’s debit balances, and interest income is determined based on the company’s cash balances. Analysts calculate percentages in financial models using one of two approaches:
Conceptually, forecasting using average debt is considered more logical because debt balances change over the period. However, debit (or rather revolving debit) is often used as a plug-in to the model, and when average debit is used, it creates a cyclicality in the model. Circularity is problematic in Excel, so analysts often use opening debt balances instead. For more information on circularity, go to the Circularity section of this Financial Modeling Best Practices article.
While revolving debt is usually a constraint on the deficit, cash is a constraint on the surplus, so any excess cash flows predicted by the model naturally lead to an increase in cash balances on the balance sheet. This means that we run into the same circularity problems here as we do when forecasting interest income. Interest income is a function of projected cash balances and the projected interest rate earned on idle funds. We can predict this only after completing the balance sheet and cash flow statement. Similar to interest expense, analysts can calculate interest using either the initial or mid-period approach. Like interest expense, if you project your interest income based on your average cash balance, you will create cyclicality.
In addition to interest income and expenses, companies can show other non-realization income and expenses in the income statement, the nature of which is not directly indicated. These items are usually best forecasted on a straight-line basis (as opposed to operating expenses, which are typically associated with revenue growth).
Try This Financial Projections Template
It is usually enough to simply agree on the tax rate for the last historical year. However, there are times when tax rates historically are not indicative of what a company can reasonably expect to be in the future. Learn more about this in our article on tax rate modeling.
The last element of the profit and loss forecast is the forecast of outstanding shares and earnings per share. We talk about this in our guide to stock forecasts and earnings per share.
We are now sending the requested files to your email address. If you did not receive the email, be sure to check your spam folder before re-requesting the files.
Get instant access to video lectures taught by experts
How To Forecast Income Statement (step By Step)
Projected cash flow statement template excel, projected income statement excel, 5 year projected income statement template excel, budgeted income statement template excel, personal income statement template excel, income statement excel template free, simple income statement template excel, income statement forecast template excel, monthly income statement template excel, restaurant income statement template excel, projected income statement template, income statement template excel
