How to Create a Budget Forecast in Excel

Creating a budget forecast in Excel turns scary financial unknowns into a clear roadmap for your business. It’s not about predicting the future with 100% accuracy, but about using what you know today to make smarter decisions tomorrow. This guide will walk you through setting up a practical forecast, from gathering your data to using simple Excel functions to project your future revenue, expenses, and profitability.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly is a Budget Forecast?

While often used interchangeably, budgets and forecasts serve different purposes. Think of it this way:

• A budget is your plan. It’s a goal-setting document that outlines the revenue you expect to generate and the expenses you intend to incur over a specific period. It’s built on assumptions and strategic objectives.
• A forecast is your prediction. It uses historical data and recent business trends to estimate what your finances will actually look like. It’s a living document that gets updated as new information comes in.

A forecast gives you a reality check on your budget. It helps you see if you're on track to hit your goals, anticipate cash flow shortages before they happen, and make proactive adjustments to your strategy.

Step 1: Gather Your Historical Data

A forecast is only as reliable as the data it’s built on. Before you open Excel, you need to collect at least 12-24 months of historical financial data. The more data you have, the more accurately you can identify trends and seasonality.

Look for these key documents:

• Profit & Loss (P&L) Statements: Your P&L, or income statement, is the most crucial document. It breaks down your revenues, cost of goods sold (COGS), and operating expenses by month.
• Sales Reports: Dig into specific revenue streams. Are you selling different products or services? Which ones are growing? Data from your CRM (like Salesforce or HubSpot) or e-commerce platform (like Shopify) is perfect for this.
• Expense Reports: Get a detailed list of where your money goes. Categorize expenses into two buckets:

Pulling this data is often the most time-consuming part, as it may be scattered across your accounting software, CRM, and various spreadsheets. Be patient and organized, it will pay off.

Step 2: Build Your Forecasting Template in Excel

With your historical data ready, it’s time to structure your Excel workbook. Keeping it clean and organized from the start will save you headaches later.

Graphed

Still Building Reports Manually?

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

Watch the Demo

1. Create Separate Tabs for an Organized Workflow

Open a new Excel workbook and create three separate sheets:

• Historical Data: A tab to paste all your raw P&L data.
• Assumptions: A tab to document key assumptions driving your forecast. For example, "Assumes a 5% monthly growth rate based on Q4 performance" or "Marketing ad spend will be 15% of total revenue."
• Forecast Model: This is where you’ll build the forecast itself.

2. Structure Your "Forecast Model" Tab

Your forecast model should look like a standard P&L statement.

1. In Column A, list your financial categories. Start with major headings and then add sub-items.
2. In Row 1, set up your time periods. Start with past months (e.g., cell B1: Jan-23) so you can compare historicals, then continue with future months for your forecast period (e.g., Jan-24, Feb-24, etc.). Add a "Total" column at the end to sum the year’s figures.

Your basic structure should now look something like a simple grid with categories down the side and months across the top.

Step 3: Choose Your Forecasting Methods in Excel

Now for the fun part: using Excel's built-in tools to project future numbers. You don't need to be a statistician to do this. Here are three common and effective methods, ranging from simple to more advanced.

Method 1: Moving Averages for Stable Trends

The moving average method smooths out short-term fluctuations to help you identify the underlying trend. It's great for businesses with relatively stable growth that isn't prone to extreme seasonality.

How it works: You calculate the average of the last few periods (e.g., 3 months) to predict the next period.

Example:

Let's say your sales for Q1 were:

• January: $10,000
• February: $11,500
• March: $12,000

To forecast April's sales using a 3-month moving average, you'd find the average of Jan, Feb, and Mar. In Excel, if these values were in cells B2, C2, and D2, your formula to forecast April (in cell E2) would be:

=AVERAGE(B2:D2)

This would give you a forecast of $11,167 for April.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 2: The Percent of Sales Method for Variable Costs

This method works perfectly for forecasting expenses that directly correlate with your sales volume, such as COGS, transaction fees, or commissions.

How it works: You determine the historical relationship of an expense to your total revenue and apply that percentage to your forecasted revenue.

Example:

After reviewing your historical data, you find that your Cost of Goods Sold has consistently been about 40% of your total revenue month after month.

If you've already forecasted your revenue for a future month to be $20,000 (in cell B2), your formula to forecast COGS for that month (in cell B5) would be:

=B2 * 0.40

Your forecasted COGS would be $8,000. You would list the "40%" assumption on your 'Assumptions' tab.

Method 3: The FORECAST.LINEAR Function for Growth Trends

When you have a visible upward or downward trend in your data, Excel's FORECAST.LINEAR function is an incredibly powerful tool. It performs linear regression analysis, which basically draws a "best-fit" line through your historical data and extends that line into the future to make predictions.

How it works: The function looks at ranges of historical dates and values to predict a future value for a specific future date.

The formula structure is: =FORECAST.LINEAR(target_date, known_values, known_dates)

• target_date: The future date you want to predict (e.g., the cell containing 1/1/2024).
• known_values: The range of your historical sales figures (e.g., $A$2:$L$2).
• known_dates: The range of your historical dates (e.g., $A$1:$L$1).

Using absolute references (with a $ sign) for the historical ranges allows you to drag the formula across your forecast period without the ranges shifting.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Putting It All Together in Your Model

Now, combine these methods to fill out your forecast sheet.

1. Project Revenue First: Start with your top line. Use FORECAST.LINEAR if you have a consistent growth trend, or a simpler method like taking last year's sales and adding a growth percentage (e.g., =B12 * 1.05 for 5% growth). Be sure to document your growth % assumption!
2. Forecast Variable Expenses: Apply the percent of sales method. For COGS, marketing, commissions, etc., link them to your forecasted revenue for each month.
3. Fill in Fixed Expenses: This is the easy part. For rent, stable salaries, and fixed software costs, you can often just copy the same amount for each month of your forecast period.
4. Calculate Your Totals and Net Profit: Use the SUM function to calculate "Total Operating Expenses." Then, create your final formula for "Net Profit/Loss" by subtracting total costs from your gross profit for each month.

*Pro Tip: As a sanity check, your forecasted P&L should roughly follow the trends and proportions of your historical data. If your forecasted net profit margin suddenly jumps from 10% to 50% without a clear business reason, double-check your formulas.*

Step 5: Visualize Your Forecast with Charts

Staring at a wall of numbers makes it hard to absorb the insights. Visualizing your forecast is the best way to understand the story your data is telling.

Create a few simple charts:

• Line Chart: Revenue vs. Expenses: Select your "Total Revenue," "Total Operating Expenses," and "Net Profit" rows along with your month headers. Go to Insert > Chart > Line Chart. This will immediately show you the relationship between money coming in and money going out over time.
• Column Chart: Forecasted vs. Actuals: Once the year is underway, you can add an "Actual" row to your model each month. A column chart comparing Forecasted Revenue vs. Actual Revenue helps you track your performance and improve future forecasts.

Final Thoughts

Building a budget forecast in Excel equips you with a powerful tool for strategic planning. By using historical data as your guide and simple Excel functions to project what’s ahead, you move from reacting to your finances to actively shaping them. Remember, a forecast is a living document that should be revisited and adjusted as your business evolves.

The biggest hurdle in this process is often the time spent just collecting and organizing data from different platforms - like Shopify for sales, Google Analytics for traffic, and HubSpot for your sales pipeline. We created Graphed to solve this data wrangling problem. Instead of manually exporting CSVs and pasting them into spreadsheets, we connect directly to these sources to give you a unified, real-time view of business performance. It helps you focus less on data preparation and more on the insights that drive your business forward.