How to Do Marketing Forecast in Excel

Trying to budget for marketing without a forecast is like driving cross-country without a map - you might get somewhere, but probably not where you intended. A good forecast gives you direction, helps you allocate resources effectively, and sets clear goals for your team. This guide will walk you through building a practical marketing forecast in Excel, breaking down the process into simple, manageable steps.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First, Why Build a Marketing Forecast?

While it might seem like just another spreadsheet exercise, a marketing forecast is a powerful strategic tool. It helps you:

• Allocate Your Budget Wisely: Justify your marketing spend by connecting it directly to business outcomes like leads and revenue.
• Set Realistic Goals: Move from guessing what you can achieve to setting data-backed targets for traffic, conversions, and growth.
• Identify Potential Issues: Spot potential budget shortfalls or campaign performance drops before they become major problems.
• Measure Performance: Track your actual results against your forecast to see what's working and where you need to adjust your strategy.

In short, it’s about moving from reactive to proactive marketing. Instead of just reporting on what happened last month, you’re actively planning for what will happen next month.

Step 1: Gather Your Historical Data

Your forecast's accuracy depends entirely on the quality of your historical data. You need a solid baseline of past performance to project future results. The goal is to collect at least 6-12 months of consistent data.

The manual part of this process is often the most time-consuming. You’ll need to log into multiple platforms, export CSV files, and clean them up so the date ranges and formats align. It's a bit of a chore, but it's essential.

Key Metrics to Collect:

• Ad Spend: Total budget spent per channel (e.g., Google Ads, Facebook Ads).
• Impressions: How many times your ads were shown.
• Clicks: How many people clicked your ads or links.
• Website Traffic (Sessions or Users): Found in Google Analytics.
• Leads or Engagements: Form fills, newsletter sign-ups, demo requests. Usually tracked in GA, your CRM, or email platform.
• Conversions (Sales): Number of actual purchases or closed deals. Found in your e-commerce platform (Shopify) or CRM (Salesforce, HubSpot).
• Revenue: Total revenue generated from marketing efforts.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Key Rates and Averages to Calculate:

Once you have your raw data, calculate these averages for each month. These "efficiency" metrics are the bedrock of your forecast model.

• Click-Through Rate (CTR): (Clicks / Impressions) * 100
• Cost Per Click (CPC): Ad Spend / Clicks
• Conversion Rate (CVR): (Conversions / Clicks) or (Conversions / Sessions) You might have different CVRs for leads vs. sales.
• Cost Per Lead (CPL): Ad Spend / Leads
• Cost Per Acquisition (CPA): Ad Spend / Conversions

Gather this information in one clean table in Excel. A simple layout with months in the first column and your metrics in the subsequent columns works perfectly.

Step 2: Structure Your Excel Forecast Template

Organization is everything. A messy spreadsheet will only lead to confusion and errors. A good practice is to use two separate tabs: one for your assumptions and one for your forecast model.

Tab 1: Assumptions

This tab holds all the key rates and variables you plan to use in your forecast. By keeping them separate, you can easily change an assumption (like your expected conversion rate) and see it update across your entire model without having to dig through formulas. This makes scenario planning much easier.

Your "Assumptions" tab should list things like:

• Projected monthly ad spend (or a planned % increase)
• Average CPC
• Average CVR (from clicks to leads)
• Average Lead-to-Sale Conversion Rate
• Average Order Value (AOV)

Tab 2: Forecast Model

This is where the magic happens. Your model will use your historical data and the variables from your "Assumptions" tab to project future performance. Structure it with a timeline running down the rows and your funnel metrics running across the columns.

A typical structure looks like this:

• Column A: Month (Jan, Feb, Mar, etc.)
• Column B: Ad Spend
• Column C: Clicks
• Column D: CPC (Cost per Click)
• Column E: Leads
• Column F: CPL (Cost per Lead)
• Column G: Conversions (Sales)
• Column H: CVR (Conversion Rate)
• Column I: CPA (Cost per Acquisition)
• Column J: Revenue
• Column K: ROAS (Return on Ad Spend)

Populate the first 6-12 rows with your historical data. The rows below that are where you'll start building your forecast formulas.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Build Your Forecast Using Excel Formulas

Now it's time to project the future. There are a few ways to do this in Excel, ranging from simple to more complex. We'll start with the most straightforward and essential methods.

Method 1: Using Averages and Growth Rates

This is the simplest approach and a great starting point. Your forecast is based on calculated averages from your past performance and any planned changes, like a budget increase.

Let's walk through an example for a Google Ads campaign:

1. Forecast Ad Spend: This is an input you control. Let’s say you plan to spend $5,000 next month. Enter that in the 'Ad Spend' column.
2. Forecast Clicks: Pull your average CPC from your "Assumptions" tab (let's say it's $2.50). The formula for Clicks would be: (Ad Spend / Average CPC) For our example: =5000 / 2.5 results in 2,000 clicks.
3. Forecast Leads: Use your average click-to-lead conversion rate from your "Assumptions" tab (e.g., 4%). The formula is: =Clicks * Conversion Rate For our example: =2000 * 0.04 results in 80 leads.
4. Forecast Revenue: To go a step further, you can use your average lead-to-sale rate (e.g., 10%) and average order value (e.g., $500). Revenue = (Leads * Lead-to-Sale Rate) * Average Order Value For our example: (80 * 0.10) * 500 = $4,000 in revenue.
5. Calculate ROAS: Finally, determine your return. =(Revenue / Ad Spend) For our example: =4000 / 5000 which is 0.8x ROAS. Maybe it's time to optimize that campaign!

You can drag these formulas down for future months, adjusting your ad spend as planned, to build out a full 6- or 12-month forecast.

Method 2: Using Excel's FORECAST.LINEAR Function

If you want a more statistically sound projection based on historical trends, the FORECAST.LINEAR function is your best friend. It predicts a future value along a linear trend line based on existing historical values.

The syntax for the formula is:

=FORECAST.LINEAR(target_date, known_y_values, known_x_values)

• target_date: The future date you want to predict (e.g., the cell containing "January 2025").
• known_y_values: The range of your historical numeric data you want to forecast (e.g., the range of your last 12 months of website traffic).
• known_x_values: The corresponding range of historical dates.

This is extremely useful for forecasting top-of-funnel metrics like organic search traffic, which isn't directly tied to ad spend but follows its own trend. Lock the ranges for your known values ($Y$2:$Y$13, $X$2:$X$13) so you can easily drag the formula down to forecast subsequent months.

Step 4: Visualize Your Forecast

Numbers in a spreadsheet are useful, but charts make the story easy to understand. Visualizing your data helps you quickly spot trends, compare actuals to your forecast, and present your plan to stakeholders.

Select your data table (including historical and forecasted months) and create a simple line chart in Excel.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Recommended Charts:

• Forecast vs. Actuals: Create a line chart with two series: one for your forecasted leads and one for your actual leads, which you'll fill in each month. This clearly shows whether you are on track.
• Monthly Channel Spend: A stacked column chart is perfect for showing how your total budget is allocated across different channels each month.
• Key Metrics Trendline: A line chart showing the trend of a key metric like CPL or ROAS over time can highlight your marketing efficiency.

Step 5: Review and Refine Your Forecast

A marketing forecast is not a "set it and forget it" document. It’s a living tool that you should update regularly - at least once a month.

At the end of each month, replace your forecasted numbers for that month with the actual performance data. How did you do against your plan? This is where true learning happens.

• If you beat your forecast: Why? Did a specific campaign outperform? Did your conversion rate increase? Understand what drove the success and see if you can replicate it. Update future months based on this new information.
• If you missed your forecast: Why? Did CPCs go up? Did a landing page underperform? Dig into the data, identify the cause, and adjust your strategy and forecast accordingly.

Final Thoughts

Building a marketing forecast in Excel is about creating a clear blueprint for success. By gathering your historical data, structuring a clean model, and using simple formulas, you can turn a daunting task into a manageable process that provides immense strategic value. The key is to start simple, stay organized, and refine your forecast as you learn what truly drives your growth.

Of course, the most demanding part of forecasting is manually pulling data from tools like Google Analytics, Shopify, and various ad platforms every month. This process can be slow and pulls you away from actual analysis. At Graphed , we automate all of that by connecting directly to your marketing and sales data sources. We turn hours of tedious data collecting and report building into a 30-second conversation, giving you live dashboards so your forecast is always up-to-date without ever exporting another CSV.