Forecasting the income statement is the first step to building
Rebuild the historicals
To forecast the income statement, you have to understand the historicals. So start by rebuilding the financial statements. This means taking the given values and adding formulas where necessary.
If you want to give it a shot (highly recommended), you can download the values only version and rebuild the financial statements by adding in formulas for all three financial statements.
Assumptions for an income statement are things like growth rates or changes in revenues and expenses based on certain factors and judgements. Each line item can have a related assumption line item.
There are many variations on how to calculation assumptions, but three are pretty common.
|Percent Change||( New - Old ) / Old||Calculate the change from one period to next.
Useful for items like revenue, salary, and rent.
|Percent of Another Line Item||Derivative Line item / Base Line Item(s)||Calculate the assumption by taking the percent of the base assumption. This method can be applied line items like COGS (% of revenue), employee benefits (% of salary), and accrued expenses (% of expenses).|
|Line Item Specific||Days Payable Outstanding (DPO), Days Sales Outstanding (DSO), Days Inventory||These are line item specific and are typical calculations used to indicate key performance indicators as well as forecast.|
We build forward looking assumptions based on historical performance. If COGS was at 70% of revenue for the past three years, it really should not deviate far from the 70%. Looking forward however, the COGS assumption can change, but there should be an explanation for the change – volume discounts, new products, new pricing etc.
I like to include assumptions in a group at the bottom of the related financial statement. In this case, the assumptions related to the income statement are between the end of the income statement and the beginning of the balance sheet. This makes it easier to keep make changes to assumptions without having to jump all over the place.
Financial Modeling Tip: Stick with widely accepted color schemes for modeling – blue for variables, plugs, or assumptions, black for formulas, and red for “beware of changing” such as links to other workbooks.
[iframe title =”Preview” scrolling=”yes” marginheight=”0″ marginwidth=”0″ frameborder=”0″ width=”694px” height=”346px” style=”padding:0;background-color:#fcfcfc;” src=”https://r.office.microsoft.com/r/rlidExcelEmbed?su=-1668697156039599496&Fi=SDE8D798C7188E1A78!121&ak=t%3d0%26s%3d0%26v%3d!AD-gOoKyF938yRs&kip=1&AllowInteractivity=True&ActiveCell=B29″]
Revenue assumption: Average growth over the past three years = 26%, but 26% growth year after year for the next 6 years sounds unrealistic, so I manually entered the growth rates.
COGS assumption: COGS is usually calculated as a % of revenue. The average over the past four years was 69.4%. I used the average for years 5-7, but lowered to 65% for years 8-10, assuming that with increased sales, there would be some volume discount.
Salaries assumption: Because this is a small business with few employees, I just plugged some salary growth assumptions based on when I felt the store could use an extra hand.
Rent/Utilities assumption: It was assumed the rent would increase a small amount after 6 years. Utilities increased only slightly.
Depreciation assumption: Deprecation was kept flat for simplicity sake. Deprecation will be covered in later posts.
Taxes assumption: Taxes are 35% and only taken when the company is profitable. Net Operating Loss will be covered in later posts.
Financial Modeling Tip: After creating all the assumptions, make sure your assumptions are reasonable and defendable. Just because it was true historically, it doesn’t mean it will always be true. Revenue growth is a good example. Revenue growth is usually very high for a young company, but as they mature, revenue growth will slow.
Incorporate your assumptions
To incorporate the forward looking assumptions is simple. The formula is essentially the inverse of the assumption forumla. For example, historical revenue growth was calculated as growth over the previous period and the forward looking forecast would be the inverse – growth over the pervious period.
Test your assumptions
Modify each assumption line item and check to make sure the math works. In most cases, you can keep an eye on the EBITDA and net income lines, if they move in the direction and magnitude as you expected, then most likely things are fine.
Check to make sure your balance sheet is still in balance.
Thanks it for the income statement. Download the working excel model and play around with the assumptions. What would you change?