Graphed LogoGraphed
Login

How to Present Causal Forecasting in Excel

Cody Schneider•

Predicting future sales or user acquisition can feel like guesswork, but it doesn't have to be. Causal forecasting helps you move beyond simple trend lines by connecting your results to the specific, measurable factors that actually drive them - like ad spend, email campaigns, or website traffic. This guide will walk you through exactly how to build, visualize, and present a causal forecast using the tool you probably already have open: Microsoft Excel.

What Exactly Is Causal Forecasting?

Most people are familiar with time-series forecasting, where you predict the future based solely on past data. For example, "our sales in January are usually 10% higher than December, so we can expect a similar lift this year." It's simple, but it ignores the why.

Causal forecasting, on the other hand, is all about identifying cause-and-effect relationships. It assumes that the metric you want to predict (the effect) is driven by one or more other variables (the causes). This changes the question from "What will sales be?" to "If we spend $5,000 on ads and get 10,000 website visitors, what will our sales be?"

For example:

  • A time-series forecast predicts next month's sales based on sales from the previous 12 months.

  • A causal forecast predicts next month’s sales based on your planned marketing budget, the number of sales reps on staff, and seasonal traffic trends.

The goal is to understand the levers you can pull. By building a causal model, you can play out different scenarios to see how your decisions are likely to impact future results.

Step 1: Gather and Structure Your Data in Excel

The success of any forecast depends on clean, well-organized data. Before you touch any formulas, you need to consolidate your historical data into a simple table in Excel. This is often the most time-consuming part, but getting it right is essential.

Collect Your Variables

First, identify the metric you want to predict. This is your dependent variable. It's the effect you're trying to understand. Examples include:

  • Monthly Sales ($)

  • Weekly Website Signups

  • Daily Leads Generated

Next, brainstorm all the potential factors that might influence your dependent variable. These are your independent variables - the potential causes. Try to use metrics you have direct control over or can reliably predict. Examples include:

  • Facebook Ad Spend

  • Google Ads Spend

  • Number of Emails Sent

  • Website Traffic (Sessions)

  • Number of Sales Demos Booked

  • Seasonal factors (e.g., using a 1 for 'Holiday Month' and 0 for 'Non-Holiday Month')

Organize Your Spreadsheet

Create a single table in your Excel sheet. The structure should follow these rules:

  1. Rows represent time periods. Each row should be a consistent unit of time, like a day, week, or month.

  2. The first column is your dependent variable. List your historical outcomes here (e.g., 'Weekly Sales').

  3. Subsequent columns are your independent variables. Each potential 'cause' gets its own column beside the dependent variable (e.g., 'Ad Spend', 'Website Sessions'). Make sure the data in each row aligns with the correct time period.

Your table should look something like this:

Pro Tip: Ensure you have a good amount of historical data. For a weekly forecast, having at least 52 rows (one year of data) is a great starting point.

Step 2: Start Simple with Linear Regression

To begin, let’s isolate just one independent variable to see its relationship with your dependent variable. This is called a simple linear regression.

Let's say we want to predict 'Widget Sales' based on 'Facebook Ad Spend'. Before using any formulas, create a scatter plot to see if a relationship exists. Highlight your two columns of data, go to Insert > Charts > Scatter. If a clear pattern emerges (e.g., as ad spend goes up, so do sales), you're on the right track.

Excel's FORECAST.LINEAR function is the quickest way to create a single-variable forecast.

The formula is:

  • x: The future value of your independent variable. (e.g., your planned ad spend for next week).

  • known_y's: Your range of historical dependent variable data (e.g., past sales).

  • known_x's: Your range of historical independent variable data (e.g., past ad spend).

For example, if your historical sales data is in cells B2:B20 and ad spend is in C2:C20, and you want to predict sales for a planned ad spend of $1,200 (in cell C21), the formula would be:

This is useful for quick, back-of-the-napkin calculations, but its power is limited. Business outcomes are rarely driven by a single factor, which is why you'll need multiple regression.

Step 3: Build a Powerful Model with Multiple Regression

Multiple regression allows you to model a relationship using two or more independent variables. This is where you can start building a far more realistic forecast that reflects your business.

To do this, we need Excel’s powerful but often-hidden Analysis ToolPak.

Enable the Analysis ToolPak Add-in

If you haven't enabled it before, here’s how:

  1. Go to File > Options.

  2. Click on Add-ins in the left menu.

  3. At the bottom, where it says "Manage," make sure Excel Add-ins is selected and click Go...

  4. In the pop-up box, check the Analysis ToolPak checkbox and click OK.

You’ll now find a "Data Analysis" button under the "Data" tab in Excel’s ribbon.

Run a Multiple Regression Analysis

  1. Click the Data Analysis button on the Data tab.

  2. Select Regression from the list and click OK.

  3. Fill out the dialog box:

    • Input Y Range: Select your dependent variable data (e.g., your column of "Weekly Sales"). Include the header label.

    • Input X Range: Select all your independent variable columns together (e.g., "Ad Spend," "Website Sessions," "Emails Sent"). Important: These columns must be adjacent to each other in the spreadsheet.

    • Check the Labels box if you included the headers in your selection.

    • Choose New Worksheet Ply for the Output option to keep your results clean.

    • Click OK.

Excel will instantly generate a new sheet with a "SUMMARY OUTPUT" table. Initially, it may look intimidating, but you only need to focus on a few key numbers to understand and present your model.

Interpreting the Key Metrics for Your Presentation

This output tells you how good your model is and which variables matter. Here are the three most important things to check:

1. R Square

Think of this as a "goodness of fit" score from 0 to 1. It tells you what percentage of the variation in your dependent variable can be explained by your chosen independent variables.

  • What to Look For: An R Square of 0.75 means your model (e.g., using ad spend and website sessions) explains 75% of the fluctuation in your sales. That's pretty strong! A score of 0.20 would be weak. There's no magic number, but generally, anything over 0.60 is considered decent.

2. P-value

The P-value tells you whether an independent variable has a statistically significant impact on your dependent variable.

  • What to Look For: The standard rule is to look for a P-value that is less than 0.05. If the P-value for "Ad Spend" is 0.01, it means ad spend is a significant driver of sales. If the P-value for "Emails Sent" is 0.52, it's likely just noise and doesn't have a real, predictable effect - you should consider removing it from your model and re-running it.

3. Coefficients

This is the fun part. The coefficients tell you how much each independent variable affects your dependent variable, assuming all other variables are held constant.

  • How to Interpret It: If the coefficient for "Ad Spend" is 2.5, it means that for every additional $1 you spend on ads, your model predicts sales will increase by $2.50. This is the "lever" you can use for planning different scenarios. The "Intercept" is your model's baseline - what sales would be if all your variables were zero.

Step 4: Visualize and Present Your Forecast Like a Pro

Never present a spreadsheet full of raw numbers. Your goal is to tell a story and make the forecast easy to understand. Visualizing the data is the best way to do this.

Build a Predictive Model in Your Sheet

Using the coefficients from your regression output, you can now write a single formula in your original data table to calculate a "Predicted" value for each time period.

The formula is:

Add a new column called "Predicted Sales" and paste in your formula, referencing the coefficients and the independent variable values for each row. Now you can directly compare what actually happened ("Actual Sales") with what your model would have predicted ("Predicted Sales").

Plot Actuals vs. Predicted on a Line Chart

The best way to show your model's accuracy is to plot the actual and predicted values on the same chart.

  1. Highlight your time period column, your actual dependent variable column, and your new predicted variable column.

  2. Go to Insert > Charts > Line Chart.

  3. Format your chart with clear labels.

If the two lines track each other closely, you have a solid model that can be trusted. This visual alone is often enough to create buy-in from your stakeholders.

Bring it all Together - Tips for Presenting

  • Focus on the Story, Not the Statistics: Start by explaining the purpose. "We built a model to understand what really drives our sales." Then show the visuals. Save the term "P-value" for the appendix unless you're asked.

  • Talk in Scenarios: Use your model's coefficients to explore business decisions. "Based on the model, if we increase our ad spend by 20% to $6,000 next month while keeping email promotions flat, we can forecast sales of about $42,500."

  • Be Honest About Limitations: No model is perfect. Acknowledge this. "This forecast is based on historical data and assumes market conditions remain stable. It's a strategic guide, not a guarantee." This builds trust and credibility.

Final Thoughts

Building a causal forecast in Excel transforms you from simply reporting on the past to understanding the levers that shape your future. By connecting business outcomes to their root causes and visualizing the potential impact of your decisions, you create a powerful strategic tool that truly guides your business toward growth.

While Excel is a great tool for this, keeping the data updated and connecting all your performance sources manually can be a major challenge. At Graphed , we help you skip the manual export/import process entirely. Just connect your data sources like Google Analytics, Shopify, and your ad platforms in a few clicks, and our AI can instantly analyze cross-channel performance and build real-time dashboards for you. It turns hours of spreadsheet wrangling into a simple conversation, allowing you to focus on the insights instead of the data prep.