How to Forecast Financial Statements Using the Percent of Sales Approach
The percent of sales approach is commonly used as a quick way to forecast financial statements. Specifically, it is used for income statements, as balance sheets and cash flow statements are not directly related to percent of sales. This article will walk you through the creation of an income statement forecast based on the percent of sales approach using MS Excel.
Other People Are Reading
Instructions
-
-
1
Open a spreadsheet with your historical financial statements. We will be using the income statement as it is the only one with line items that can be directly tied to a percent of sales. The balance sheet will be affected through retained earnings and the cash flow statement primarily through receivables and payables (working capital), but in ancillary ways.
-
2
Create a column called "Percent of Sales" to the right of your most recent annual (or rolling 12 months) income statement numbers. That is, you will want to take each line item in the income statement, from Revenue, to Operating Expenses, to Taxes and Interest as a percent of sales.
-
-
3
Create a formula in the first cell next to Revenue or Sales. The formula should be the "Adjacent Cell" dived by Revenue (or Sales). In this case, it should be Revenue divided by Revenue or 100 percent. Be sure to set your formula up to always pull from Revenue as the denominator.
-
4
Copy this formula down through the income statement. Do a quick sanity check to make sure things add up.
-
5
Create another column to the right of the percent of sales column. Call this "Projection Year 1." Based on the past 3 years, estimate the amount you will sell in "Project Year 1."
-
6
Create a formula under this sales forecast that multiplies the "Projection Year 1" sales by the percent of sales in the adjacent column. If costs of goods sold represent 50 percent of your sales, the forumula should be .5 multipled by Projection Year 1 sales.
-
7
Repeat this step for Projection Year 2 and 3. Be careful as some line items, like interest income, may have nothing to do with sales.
-
1
Tips & Warnings
This is not to be construed as investment or legal advice.
Resources
More Like This
Comments
-
Financial Assumptions This chapter presents the financial assumptions used in EPA Base Case v.4.10. The first section. gives a summary of each of the key financial parameters. The remainder of the chapter presents. an in-depth explanation of the theoretical underpinnings and methods used to develop the two. most important financial parameters – the discount rate and capital charge rate.
-
yolandanichole
Nov 02, 2009
Good approach to forecasting.
