How To Create a Budget vs. Actual Report in Excel (Plus 7 Charts You Can Use)

A budget vs. actual report helps you catch issues early before they affect your company's financial performance.

It shows where you're spending more or less than planned, and what’s driving the gap. But you need to visualize it in a way that helps non-finance stakeholders quickly understand what changed, why it happened, and what to do next.

In this guide, you'll learn how to create budget vs. actual reports in Excel, which charts work best for different types of variance analysis, and practical tips for building better reports.

How To Create a Basic Budget vs. Actual Report in Excel

A good budget vs. actual report in Excel needs these three things—structured data, calculated variances, and a chart that makes your variances impossible to miss.

Here’s how to build one in six steps.

Step 1: Start With a Template (Or Build Your Own Structure)

If you don't want to build everything from scratch, start with a pre-built template that already includes the structure, calculations, and visuals you need.

You can download Vena's free budget vs. actual Excel template and use it as-is, customize it for your chart of accounts, or reference it while building your own from scratch.

This template includes:

• Pre-formatted data structure with budget and actual sections
• Built-in variance calculations (dollar and percentage)
• Dropdowns to switch between months and scenarios
• Charts that update when you change your selections

To customize the template, adjust colors, fonts, and cell styles through Excel's Page Layout and Home tabs to match your company's branding or reporting standards. Or if you prefer full control over your report structure, you can build your own from scratch.

Step 2: Map Your Budget And Actuals

Most organizations pull actuals from an ERP or accounting system like NetSuite, SAP, or QuickBooks, while budgeting often still happens in Excel or a separate planning tool.

To build a reliable budget vs. actual report, you need your budget and actuals to match at the same level of detail. If you're using Vena's template, here's how to map both datasets into it. And if you're building from scratch, the same logic applies.

Step 2.1: Export Your Actuals

Pull your actuals from your ERP or accounting system by account and time period.

Since budgets are usually set as monthly totals by account, your actuals need to be summarized the same way—otherwise you're comparing individual transactions to monthly budget targets, and you’ll end up with reporting errors.

If your export gives you a long list of transactions, use a PivotTable to group them into monthly totals by account before you paste anything into your report.

Step 2.2: Bring In Your Budget Data

Next, pull your budget data from your planning file (or budgeting system). Since budgets are usually laid out by month, paste those values into the Budget section of the Data sheet.

The template includes:

• Account column (left): Where your chart of accounts goes
• Actual columns: Paste your monthly actuals here (columns D-O in the template)
• Budget columns: Paste your monthly budget figures here (columns R-AC in the template)
• What If columns: Optional scenario planning columns

Simply paste your exported actuals into the Actual section and your budget values into the Budget section.

The Variance sheet will automatically calculate the differences.

Step 3: Choose Your Reporting Timeframe

The right timeframe depends on who you’re reporting to:

• Monthly: Best for operational teams who need to make quick decisions. For instance, your VP of Operations needs to know this month's variance to decide whether to freeze hiring, delay purchases, or reallocate budget.
• Year-to-date (YTD): Best for executive reporting, like when your CFO needs quick answers to questions like, “Are we still on track to hit our annual plan, or do we need to revise the forecast?” YTD is also useful for smoothing out one-time expenses. For example, if Marketing spent 30% over budget in January due to a conference sponsorship but came in 20% under in February and March, monthly reporting can look erratic—but YTD shows they're actually 2% under budget through Q1.
• Full year: Best for annual planning and board reporting. It keeps the focus on the overall trajectory instead of month-to-month noise. For instance, your CEO and board need the full-year view to see whether budget overruns are one-off timing issues or signs the company will miss its annual plan.

If you’re using Vena's template, you can use the dropdowns in the Variance sheet to switch between months and scenarios, and the variances recalculate automatically.

Step 4: Calculate and Interpret Your Variances

Once your budget and actuals are in place, calculate your variances using these formulas:

Variance ($) = Actual – Budget
Variance (%) = (Actual – Budget) / Budget

If you're using Vena's template, these calculations are already built in—the Variance sheet automatically calculates dollar and percentage variances for each account and time period.

Step 5: Analyze Your Results

Once you’ve calculated variances, the next step is figuring out which ones actually need action. Look for patterns like these:

• Recurring variances across multiple months: If the same account is over or under budget for several months in a row, it's usually a structural issue, not a one-time event. For example, if Marketing is 25% over budget in January due to an annual conference sponsorship, that's expected. But if Marketing stays over budget in February and March too, it signals ongoing overspending or a budget assumption that needs updating.
• Related variances that move together: Some variances only make sense when viewed together. If Product COGS is 15% over budget because Product Revenue is 20% over budget, that's not necessarily a problem—it just means costs scaled with higher sales. If margins remain steady, no action is needed.
• Timing-based variances that normalize over time: Some variances happen because of timing, not performance. Paying for an annual software license or conference sponsorship in one month can make that month look over budget even though the full year is still on track. This only becomes an issue if the full-year budget is also at risk.

Also watch out for variances that look positive but are actually red flags.

For example, if Salaries are under budget because two critical roles haven't been filled, it may look like cost control, but it can slow execution and put your roadmap at risk.

Step 6: Choose the Right Chart to Visualize Your Variances

Charts show trends and patterns faster than a table of numbers, and you can use them to visually demonstrate the comparison between your budget and actual spending or revenue. Ultimately, the type you choose depends on what question you're answering.

Here’s how to decide:

• Need to show one month's performance across multiple accounts? Use a bar or column chart to compare budget vs. actual side by side
• Need to show trends over time? Use a line chart to track how actual and budget diverge or converge across months
• Need to show what drove the total variance? Use a waterfall chart to break down which accounts contributed to the overall difference
• Need to show if you're on track at a glance? Use a bullet chart to show actual performance against budget targets with visual zones

In Vena's template, three charts are already built in—a bar chart showing gross margin breakdown, a line chart comparing actual vs. budget over time, and an area chart showing revenue composition by category. If you need additional chart types (like waterfall or bullet charts), you can build them using your variance data.

6 Tips for Better Budget Variance Reports in Excel

Use these tips to create accurate reports stakeholders can trust, easily interpret, and act on:

• Apply conditional formatting so stakeholders can instantly see which variances need attention. Green typically indicates favorable variances like expenses under budget, while red indicates unfavorable variances like revenue under budget.
• Use the same account names and structure in both your budget and actuals. If Marketing is "Marketing Expenses" in your budget but "Mktg Spend" in your ERP, Excel won't match them automatically. Standardize once, then maintain it every month.
• Update your report as soon as books close. Set up your template to minimize manual work so you can turn around reports within days of month-end close.
• Add context with notes or annotations. If Marketing is 25% over budget in January, include a short note explaining why, like “Annual conference sponsorship, expected timing difference.” And if Salaries are under budget, don’t just leave it as a “win.” Add the business impact, like “Two critical roles unfilled, may delay the Q2 roadmap.” This way, you’re answering stakeholder questions before they’re even asked.
• Compare multiple timeframes. Instead of showing monthly variances, show YTD and full-year projections in the same report. This helps stakeholders distinguish between one-time timing issues and systemic problems. A 30% variance in one month might be noise; a 5% variance YTD is a trend.
• Keep your raw actuals and budget data in separate sheets, then reference them in your variance calculations. This keeps your reporting sheet clean and easy to read while maintaining an audit trail. Plus, if someone questions a number, you can drill back to the source.

Make Your Budget vs. Actuals Report More Actionable With Vena

The process outlined in this guide works well for most FP&A teams managing budget vs. actual reporting in Excel. But as your reporting needs grow, you'll likely hit some common limitations.

For example, if you’re pulling actuals across multiple entities, divisions, or systems, consolidating everything into one workbook each month becomes time-consuming and error-prone. And once multiple stakeholders need to input, review, or adjust the report, version control in Excel becomes a real challenge, and this can result in errors, which makes the results harder to trust.

If you're hitting these limitations, you have a few options. You can improve your workflow by standardizing templates, tightening your monthly close process, and using tools like Power Query to automate data imports. This can work well for a while. But if you need more control, collaboration, and scale, budgeting and forecasting software like Vena helps here. You still work in Excel, but with workflow automation, version control, and a single source of truth, so your budget vs. actual reporting stays accurate and actionable as the business grows.

Ready to get started? Download Vena's free budget vs. actual Excel template