How to Forecast 5 year Income Statement

Let's take the assumption as : 

• Forecast period: 5 years
• YoY Revenue Growth: 10%
• COGS % Revenue: starting at 77%, assume declining margin to 76%
• SG&A % Revenue: starting at 16.5%, assume declining margin to 16%
• Depreciation: $787 million for 2022 and then held constant at 1.5% of revenue in each subsequent year
• Amortization: $82 million for 2022 and then held constant at $100 million in each subsequent year
• Interest exp: $25 million for 2022 and held constant over projection period
• Tax rate: 20%

This exercise will be really simple and I'm writing this blog for my own reference as a well defined Financial Model summary helps a lot to comeback and understand the formulas and step. So that you can identify mistakes in the new forecast model you are making. 

I hope it helps you all in the same way it is helping me.

Based on the assumptions we will make a Income Statement Driver portion. 

Revenue growth rate is already given at 10% so we will take that for constant for each year. 

COGS is given as 77% for the first year and that declined to 76% at the end. So in between we will take the average formula to calculate the COGS as % revenue. The formula for second year or first forecast year will be =+Average(Actual year, Second Forecast year) i.e. =+Average (77%,76%) and then stretch it for next three years as we already know the last year. 

SG&A : Do the same as COGS

Amortization and Interest Expenses are already given in the assumption. 

REVENUE : As we already know the revenue of the actual year and forecast the other year with 10% increase. {=+51761+(51761*10%)}

Less COGS : for the first actual year we will look into the Income statement and subtract Depreciation from Cost of Sales. And for the rest of the year we will mutiply Revenue with the COGS percentage as caulculated in the Income Statement Driver. 

Gross Profit : is Revenue minus COGS

Margin % is Gross Profit divided by Revenue 

Then Sales, General & Administration  Expenses (excl. Amortization) will be for actaul years SG&A minus Amortization and for the forecasting years mutiply Revenue with the SG&A percentage as caulculated in the Income Statement Driver. 

EBITDA is Gross Profit minus SG&A

Margin % is EBITDA divided by Revenue 

Depreciation is given for the actual year in the Annual Report and for the forecasting years mutiply Revenue with the Depreciation percentage as caulculated in the Income Statement Driver. 

Ammortization is given for the actual year in the Annual Report and for the forecasting years we have assumed it as $100 Mn.

EBIT is EBITDA minus (Depreciation + Amortization)

Margin % is EBIT divided by Revenue 

Interest Income is assumed to be $25 Mn for all the years 

Pre-Tax is EBIT minus Interest Income 

Tax is Pre Tax multiplied by 20%

Then Net Income is Pre Tax minus Tax.