How to Build an Income Statement in a Financial Model
After preparing the skeleton of an income statement as such, it can then be integrated into a proper financial model to forecast future performance.
First, input historical data for any available time periods into the income statement template in Excel. Format historical data input using a specific format in order to be able to differentiate between hard-coded data and calculated data. As a reminder, a common method of formatting such data is to color any hard-coded input in blue while coloring calculated data or linking data in black.
Doing so enables the user and reader to know where changes in inputs can be made and which cells contain formulae and, as such, should not be changed or tampered with. Regardless of the formatting method chosen, however, remember to maintain consistent usage in order to avoid confusion.
Next, analyze the trend in the available historical data to create drivers and assumptions for future forecasting. For example, analyze the trend in sales to forecast sales growth, analyzing the COGS as a percentage of sales to forecast future COGS. Learn more about forecasting methods.
Finally, using the drivers and assumptions prepared in the previous step, forecast future values for all the line items within the income statement. Forecast specific line items, and use these to calculate subtotals. For example, for future gross profit, it is better to forecast COGS and revenue and subtract them from each other, rather than to forecast future gross profit directly.