How to Forecast Income Statement in Excel

Building an income statement forecast in Excel can seem daunting, but it's one of the most powerful tools for planning your business's future. This guide breaks down the entire process, demonstrating how to project your revenue, costs, and ultimate profitability. You'll learn how to transform your historical data into an actionable financial roadmap, step-by-step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is an Income Statement Forecast?

An income statement, or Profit and Loss (P&L) statement, shows your company’s financial performance over a specific period - a month, a quarter, or a year. A forecast is simply a projection of what that statement will look like in the future. It's not a guess, it's an educated estimate based on historical data, business assumptions, and market trends.

Creating one helps you:

• Set realistic financial goals and budgets.
• Identify potential cash flow issues before they happen.
• Make smarter decisions about hiring, spending, and expansion.
• Present a compelling financial story to investors or lenders.

Step 1: Gather Your Historical Data

Your forecast's accuracy depends heavily on the quality of your historical data. You can't predict where you're going without knowing where you've been. You’ll need to pull at least 12-24 months of data from your accounting software (like QuickBooks or Xero), spreadsheets, or whatever you use to track finances.

Collect the following line items for each month:

• Revenue: Your total sales income.
• Cost of Goods Sold (COGS): The direct costs of producing your goods or services. For an e-commerce store, this is the cost of the products you sell. For a service business, it might be direct labor costs.
• Operating Expenses (OpEx): All other costs related to running the business, which can be broken down further:
• Interest and Taxes

Organizing this data is your foundation. Once you have it, you can move on to setting up your Excel model.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Structure Your Excel Spreadsheet

A clean, organized spreadsheet is your best friend. A standard layout places time periods across the columns and financial line items down the rows.

Setting Up the Framework

1. Open a new Excel sheet. In Row 1, list out the time periods. Start with your historical months and then add your future, forecasted months. For example, your columns might be Jan '23, Feb '23, ... Dec '23, Jan '24, Feb '24, etc.
2. In Column A, list the income statement line items. This creates your structure. It should look something like this:

• Revenue
• Cost of Goods Sold (COGS)
• Gross Profit
• Operating Expenses
• Sales & Marketing
• Salaries & Benefits
• Rent & Utilities
• General & Administrative
• Total Operating Expenses
• Operating Income (EBITDA)
• Depreciation & Amortization
• Interest Expense
• Pre-Tax Income
• Taxes
• Net Income

1. Populate the historical data for the past months. This section of your spreadsheet is complete once it matches your actual P&L statements.

You now have a template with past performance filled in, ready for forecasting the future columns.

Step 3: Forecasting Revenue

Forecasting revenue is both an art and a science, and it’s the most important driver of your entire model. There are several ways to approach it. We'll cover two popular methods.

Method 1: Simple Growth Rate

This is the most straightforward approach. You assume your revenue will grow by a certain percentage each month or year.

1. Calculate Historical Growth: Look at your past year-over-year or month-over-month growth rate. Be realistic about whether you can sustain that rate.
2. Define Your Assumption: Let's say you plan for a conservative 5% month-over-month growth for the next quarter.
3. Apply the Formula: If your revenue in December '23 was $50,000, your formula for January '24 would be:

=B2*(1+0.05)

(Assuming B2 contains your Dec '23 revenue). You can then drag this formula across your future months.

When to use this: Good for stable businesses or for a quick, high-level forecast.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: Driver-Based Forecasting

This method is more detailed and generally more accurate because it ties revenue to specific business activities. You forecast the key metrics (or "drivers") that lead to revenue.

For an e-commerce website, the drivers might be:

Revenue = (Website Sessions x Conversion Rate) x Average Order Value (AOV)

To use this method:

1. Create an "Assumptions" Section: At the top of your sheet, list out each driver (e.g., Website Sessions, Conversion Rate, AOV).
2. Forecast Each Driver:

• Website Sessions: Will you be increasing ad spend? Do you expect a seasonal bump? Estimate a growth number (e.g., 10,000 sessions/month).
• Conversion Rate: Are you making any site improvements that might lift this? Keep it stable or project a small increase (e.g., 2.5%).
• AOV: Are you launching higher-priced products? Let's assume it stays at $100.

1. Calculate Revenue with a Formula: In your revenue cell for the first forecasted month (e.g., Jan '24), you would reference your assumption cells:

=(B5*B6*B7)

(Assuming B5, B6, and B7 contain your assumptions for Jan sessions, conversion rate, and AOV).

The beauty of this method is you can play with the drivers. What happens if you run a campaign that boosts traffic by 20%? What if a site redesign increases your conversion rate? You can model these scenarios instantly.

Step 4: Forecasting Expenses

With revenue projected, you can now forecast your costs. The common practice is to separate COGS from other operating expenses.

Forecasting Cost of Goods Sold (COGS)

COGS is usually directly tied to revenue. As sales increase, the cost to produce what you're selling also increases.

1. Calculate Historical COGS as a Percentage of Revenue: For each historical month, calculate COGS / Revenue. You might find it hovers around, say, 40%.
2. Apply the Percentage: In your forecast, you can assume COGS will remain 40% of sales. Your formula for the first forecasted month would be simple:

=C2*0.40

(Assuming C2 is your forecasted revenue for that month).

After forecasting revenue and COGS, you can calculate your Gross Profit: Gross Profit = Revenue - COGS.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Forecasting Operating Expenses (OpEx)

Operating expenses can be broken down into fixed and variable costs.

• Fixed Costs: These stay relatively the same month after month, regardless of sales volume. Examples include rent, base salaries, and software subscriptions. For these, you can often just carry forward the last historical month's value, adjusting for any known changes (e.g., a planned rent increase in March).
• Variable Costs: These change with business activity. A prime example is marketing spend, which might be forecasted as a percentage of revenue (e.g., 10% of monthly sales). Another is a sales commission, which would be a percentage of that month's revenue.

For each Operating Expense line item, apply the logic that fits best. Then, sum them all up into a "Total Operating Expenses" line.

Step 5: Calculate Profitability and Complete the Forecast

You've done the hard work. The rest is just simple subtraction to see your company’s projected profitability.

1. Operating Income (EBITDA): Calculate this by subtracting total operating expenses from your gross profit. This shows the profitability of your core business operations.

=GROSS_PROFIT_CELL - TOTAL_OPEX_CELL

1. Depreciation & Amortization: This is a non-cash expense. You can usually get this figure from your accountant or carry a historical average forward.
2. Interest and Taxes: Forecast interest based on your debt schedules. For taxes, you can apply a simple effective tax rate (e.g., 25%) to your pre-tax income.
3. Net Income: This is your bottom line - what’s left after all expenses are paid.

=(EBITDA_CELL - D&A_CELL - INTEREST_CELL) * (1 - TAX_RATE)

Tips for Maintaining an Accurate Forecast

• Document Your Assumptions: Why did you choose a 5% growth rate? Document it in a text box or a separate tab in Excel. This makes it easier to update the model later.
• Create Scenarios: Copy your model into two new tabs. Create a "Best Case" (optimistic assumptions) and "Worst Case" (pessimistic assumptions). This gives you a range of potential outcomes.
• Update Regularly: A forecast is a living document. At the end of each month, replace your forecasted numbers with actuals and re-forecast the coming months. This "forecast vs. actual" analysis is invaluable.
• Keep it Simple at First: You can always add more detail later. Start with a high-level forecast and refine it over time as you get more comfortable with the process.

Final Thoughts

Building an income statement forecast in Excel gives you incredible foresight into your business's financial health. By using historical data to project future revenue and costs, you can make proactive decisions, set clear goals, and manage your resources more effectively.

While handling this in Excel is great practice, you'll find the most time-consuming part isn't the formulas - it's the constant manual work of gathering and updating data from all your different platforms. To help teams automate that entirely, we built Graphed. We connect directly to your data sources like QuickBooks, Shopify, and Google Analytics to create live, real-time dashboards that update automatically, so you can focus on making decisions instead of wrangling spreadsheets.