Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
Financial forecasting is the process of estimating or predicting how a business will perform in the future. The most common type of financial forecast is an income statement; however, in a complete financial model, all three financial statements are forecasted. In this guide on how to build a financial forecast, we will complete the income statement model from revenue to operating profit or EBIT.
There are inherent tensions in model building between making your model realistic and keeping it simple and robust. The first-principles approach identifies various methods to model revenues with high degrees of detail and precision. For instance, when forecasting revenue for the retail industry, we can forecast the expansion rate and derive income per square meter.
When forecasting revenue for the telecommunications industry, we can predict the market size and use current market share and competitor analysis. When forecasting revenue for any service industries, we can estimate the headcount and use the income for customer trends.
On the other hand, the quick and dirty approach to robust models outlines how you can model revenues in a much more straightforward way, with the benefit that the model will be more simple and easy to use (although less accurate and detailed). With this approach, users predict future growth based on historical figures and trends.
Forecasting Gross Margin and SG&A Expenses
Once we finish forecasting revenues, we next want to forecast gross margin. Gross margin is usually forecast as a percent of revenues. Again, we can use historical figures or trends to forecast future gross margin.
However, it is advised to take a more detailed approach, considering factors such as the cost of input, economies of scale, and learning curve. This second approach will allow your model to be more realistic, but also make it harder to follow.
The next step is to forecast overhead costs: SG&A expenses. Forecasting Selling, General, and Administrative costs are often done as a percentage of revenues. Although these costs are fixed in the short term, they become increasingly variable in the long term.
Therefore, when forecasting over shorter periods (weeks and months), using revenues to predict SG&A may be inappropriate. Some models forecast gross and operating margins to leave SG&A as the balancing figure.
Financial Forecasting Example
Let’s go through an example of financial forecasting together and build the income statement forecast model in Excel. First off, you can see that all the forecast inputs are grouped in the same section, called “Assumptions and Drivers.”
I created separate output section groups for the income statement, balance sheet, and cash flow statement. I also created a “Supporting Schedules” section, where detailed processing calculations for PP&E and equity are broken down in order to make the model easier to follow and audit. In this article, we will only work on the assumptions and the income statement.
All income statement input assumptions from revenues down to EBIT can be found in rows 8-14. All expenses are being forecasted as a percentage of sales. Only the sales forecast is based on growth over the previous year. My inputs are also ordered in the order they appear on the income statement.
Now, let’s move to the “Income Statement” section, where we are going to work on Column D and move downwards. To forecast sales for the first forecast year (in this case 2017), I take the previous year (C42) and grow it by the sales growth assumption in the “Assumptions & Drivers” section. The “SalesGrowthPercent” assumption is located in cell “D8”. Therefore, the formula for the 2017 forecasted revenue is =C42*(1+D8).
I then calculated our Cost of Goods Sold. To calculate the first forecast year’s COGS, we put a minus sign in front of our forecast sales, then multiply by one minus the “GrossMargin” assumption located in cell D9. The formula reads =-D42*(1-D9).
I then sum forecasted sales and COGS to calculate “Gross Profit”, located in cell D44. The formula reads =SUM(D42:D43). A handy shortcut for summing is ALT + =.
Next, I forecast all the expenses in rows 45 to 48 as a percentage of sales. Let’s first start with “Distribution Expenses,” then copy the formula down to “Depreciation.” To calculate, we subtract the forecast sales and multiply by the appropriate assumption, which in this case is Distribution Expense as a Percent of Sales. The formula reads =D$42*D10. Be mindful of the $ sign because we want to make row 42 of cell D42 an absolute reference. I then copy this formula down, using the shortcut CTRL + D or fill down.
Then, over to the right, using the shortcut CTRL + R or fill right.
Finally, I net gross profit off with all the other operating expenses to calculate EBIT, using =SUM(D44:D48).
Thank you for reading this guide to financial forecasting. CFI is a global provider of financial analyst training and career advancement for finance professionals. To learn more and expand your career, explore the additional relevant CFI resources below:
Below is a break down of subject weightings in the FMVA® financial analyst program. As you can see there is a heavy focus on financial modeling, finance, Excel, business valuation, budgeting/forecasting, PowerPoint presentations, accounting and business strategy.
A well rounded financial analyst possesses all of the above skills!
Additional Questions & Answers
CFI is the global institution behind the financial modeling and valuation analyst FMVA® Designation. CFI is on a mission to enable anyone to be a great financial analyst and have a great career path. In order to help you advance your career, CFI has compiled many resources to assist you along the path.
In order to become a great financial analyst, here are some more questions and answers for you to discover:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.