Build a Project Budget Burn Report

Every project sponsor asks the same two questions: how much of the budget have we spent, and are we going to finish within it? "We have spent 52% of the budget" means little on its own — it is good news halfway through the timeline with 60% of the work done, and bad news with 30% done. A burn report puts the three numbers side by side: spend against a time-phased plan, how much work is actually complete, and a forecast of the final cost. The data is usually scattered — hours in a timesheet export, supplier invoices in an accounts payable list, the budget in a planning workbook — and pulling it together every week by hand is exactly the job to automate. This page builds the report with pandas, adds earned-value measures, and writes a workbook with a burn chart. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Three inputs: project_plan.xlsx with each workstream's budget, planned start and end week and the project manager's percent-complete estimate; timesheets.xlsx with hours per person per day and their cost rate; and invoices.xlsx with supplier invoices tagged to a workstream. The project started on 6 July 2026, runs 16 weeks, and the report is at 27 September — the end of project week 11, counting the first week as week 0.

Step 1 — Weekly actuals from hours and invoices

Labour cost is hours times rate; supplier costs come from invoices. Both are bucketed into project weeks and combined:

import pandas as pd

plan = pd.read_excel("project_plan.xlsx").set_index("Workstream")
ts = pd.read_excel("timesheets.xlsx")
inv = pd.read_excel("invoices.xlsx")
start = pd.Timestamp("2026-07-06")
as_of = pd.Timestamp("2026-09-27")

ts["Cost"] = ts["Hours"] * ts["Rate"]
labour = ts.assign(Week=(ts["Date"] - start).dt.days // 7).groupby(["Week", "Workstream"])["Cost"].sum()
suppliers = inv.assign(Week=(inv["Date"] - start).dt.days // 7).groupby(["Week", "Workstream"])["Amount"].sum()

actual = (pd.concat([labour, suppliers], axis=1).fillna(0).sum(axis=1)
            .unstack("Workstream", fill_value=0).reindex(columns=plan.index, fill_value=0))
actual = actual.reindex(range(int((as_of - start).days // 7) + 1), fill_value=0)
print(actual.round(0).astype(int).tail(4).to_string())
print("spent to date:", round(actual.to_numpy().sum()))

Week numbers are counted from the project start, so week 0 is the first week; this lines actuals up with the plan, which is expressed in project weeks too. reindex fills weeks with no cost with zeros so the cumulative series has no gaps. Combining the two sources with concat(..., axis=1) and summing across keeps labour and supplier costs side by side until the last moment, which is useful if the report later needs to show them separately.

Step 2 — A time-phased budget to compare with

A budget total is not a plan. Spread each workstream's budget evenly across its planned weeks to get the planned value — what should have been spent by each week:

n_weeks = 16
planned = pd.DataFrame(0.0, index=range(n_weeks), columns=plan.index)
for ws, row in plan.iterrows():
    weeks_span = range(int(row["Start week"]), int(row["End week"]) + 1)
    planned.loc[list(weeks_span), ws] = row["Budget"] / len(weeks_span)

cum_plan = planned.sum(axis=1).cumsum()
cum_actual = actual.sum(axis=1).cumsum()
week_now = cum_actual.index[-1]
print(f"week {week_now}: planned to date {cum_plan[week_now]:,.0f}, actual {cum_actual[week_now]:,.0f}, budget {plan['Budget'].sum():,.0f}")

An even spread is the simplest phasing and often good enough; if the plan has front-loaded or back-loaded spend, read the weekly planned figures from the planning workbook instead. By the end of week 11 the plan expected 123,636 of the 160,000 budget to be spent; actual spend is 88,600. On its own that looks like an underspend — but whether it is good or bad depends on how much work was done for it, which is the next step.

Step 3 — Earned value: is the spend buying progress?

Earned value (EV) is the budget of the work actually done: a workstream's budget times its percent complete. Comparing it with actual cost (AC) and planned value (PV) gives two indices:

pv = planned.loc[:week_now].sum()
ac = actual.sum()
ev = plan["Budget"] * plan["% complete"] / 100

ev_table = pd.DataFrame({"Budget": plan["Budget"], "PV": pv, "EV": ev, "AC": ac})
ev_table["CPI"] = (ev_table["EV"] / ev_table["AC"]).round(2)        # cost performance
ev_table["SPI"] = (ev_table["EV"] / ev_table["PV"]).round(2)        # schedule performance
ev_table["EAC"] = (ev_table["Budget"] / ev_table["CPI"]).round(0)    # estimate at completion
ev_table.loc["Project"] = ev_table[["Budget", "PV", "EV", "AC"]].sum().tolist() + [None] * 3
tot = ev_table.loc["Project"]
ev_table.loc["Project", ["CPI", "SPI"]] = [round(tot["EV"] / tot["AC"], 2), round(tot["EV"] / tot["PV"], 2)]
ev_table.loc["Project", "EAC"] = round(tot["Budget"] / ev_table.loc["Project", "CPI"])
print(ev_table.round({"Budget": 0, "PV": 0, "EV": 0, "AC": 0, "EAC": 0}).to_string())

The cost performance index (CPI = EV / AC) says how much work each unit of spend buys: below 1.0 means over cost. The schedule performance index (SPI = EV / PV) compares work done with work planned: below 1.0 means behind schedule. Dividing the budget by the CPI gives a simple estimate at completion — what the project will cost if it keeps spending at the current efficiency. For the project as a whole, CPI is 1.04 — the 88,600 spent has bought 92,500 of planned work, so cost efficiency is fine — but SPI is 0.75: only three quarters of the work planned by now is done. The underspend from Step 2 is really a delay. Workstream by workstream, Design is complete and came in well under budget (CPI 1.39); Build is roughly on cost (CPI 0.95) but behind (SPI 0.67); Test has spent 7,700 for an estimated 10% of its work. Test's forecast of 76,923 shows the limit of budget ÷ CPI: early in a workstream, a few weeks of set-up cost against little measurable progress produce a wildly pessimistic estimate, so treat EAC as meaningful only once a workstream is well under way. Percent complete is the weak link here, since it is an estimate — ask for it per deliverable rather than as a gut feeling for the whole workstream.

Step 4 — Flag workstreams at risk

Turn the indices into a short list the sponsor can act on:

def flag(row):
    issues = []
    if row["CPI"] < 0.95:
        issues.append(f"over cost (CPI {row['CPI']:.2f})")
    if row["SPI"] < 0.90:
        issues.append(f"behind plan (SPI {row['SPI']:.2f})")
    if row["EAC"] > row["Budget"] * 1.05:
        issues.append(f"forecast {row['EAC'] - row['Budget']:+,.0f} vs budget")
    return "; ".join(issues) or "on track"

ev_table["Status"] = ev_table.apply(flag, axis=1)
print(ev_table[["CPI", "SPI", "EAC", "Status"]].to_string())

The thresholds — 5% over cost, 10% behind schedule, a forecast more than 5% over budget — are typical starting points; projects with contingency may tolerate more. Build is flagged as behind plan with a forecast overrun of about 4,700 — its CPI of 0.95 sits exactly on the threshold, so it is not flagged on cost yet; Test is flagged on all three counts for the early-stage reasons above, and the project as a whole is behind plan but not over cost. A workstream far behind schedule but within cost is a different conversation from one that is over cost, which is why the reasons are listed rather than merged into one colour.

Step 5 — Write the report with a burn chart

The workbook has the earned-value table on top and a weekly sheet feeding a line chart of planned versus actual cumulative spend:

from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
from openpyxl.styles import Font
from openpyxl.utils.dataframe import dataframe_to_rows

wb = Workbook()
ws = wb.active
ws.title = "Burn report"
ws["A1"] = f"Project budget burn — week {week_now} ({as_of:%d %b %Y})"
ws["A1"].font = Font(bold=True, size=14)
for r in dataframe_to_rows(ev_table.reset_index().rename(columns={"index": "Workstream"}), index=False, header=True):
    ws.append(r)
for c in ws[2]:
    c.font = Font(bold=True)
for row in ws.iter_rows(min_row=3, max_row=ws.max_row, min_col=2, max_col=5):
    for c in row:
        c.number_format = "#,##0"

weekly = wb.create_sheet("Weekly")
weekly.append(["Week", "Planned (cum.)", "Actual (cum.)"])
for w in range(n_weeks):
    weekly.append([f"W{w}", round(cum_plan[w]), round(cum_actual[w]) if w <= week_now else None])

chart = LineChart()
chart.title = "Cumulative spend vs plan"
chart.y_axis.title = "EUR"
chart.x_axis.title = "Project week"
chart.add_data(Reference(weekly, min_col=2, max_col=3, min_row=1, max_row=n_weeks + 1), titles_from_data=True)
chart.set_categories(Reference(weekly, min_col=1, min_row=2, max_row=n_weeks + 1))
chart.x_axis.delete = False
chart.y_axis.delete = False
chart.width, chart.height = 22, 10
ws.add_chart(chart, "A10")
for col, w in zip("ABCDEFGHI", (14, 11, 11, 11, 11, 7, 7, 11, 46)):
    ws.column_dimensions[col].width = w
wb.save("project_burn_week11.xlsx")
print("written; actual series stops at week", week_now)

Future weeks have no actual value (None), so the actual line stops at the current week while the planned line runs to the end — the gap between them is the picture sponsors remember. x_axis.delete = False and y_axis.delete = False make sure the axes are shown; openpyxl's defaults can leave them hidden in current Excel versions. For charts of this kind in general, see Add a Line Chart to an Excel Report with Python.

Common pitfalls

• Comparing spend with the total budget only. Compare with a time-phased plan and with earned value.
• Gaps in the weekly series. Reindex weeks so cumulative sums have no holes.
• Percent complete as a guess. Base it on deliverables, or the indices mean little.
• Labour without supplier costs (or vice versa). Combine every source of actual cost.
• Hidden chart axes. Set delete = False on openpyxl chart axes.

Conclusion

A project burn report brings together actual cost from timesheets and invoices, a time-phased planned value from the budget, and earned value from percent complete. From these, CPI shows whether spend is buying the expected progress, SPI whether the work is on schedule, and budget divided by CPI forecasts the final cost. Flag workstreams with clear reasons, and chart cumulative spend against plan so the trend is visible at a glance.

Frequently asked questions

How do I calculate budget burn in Python? Sum actual costs per week from timesheets and invoices, take the cumulative sum, and compare it with a cumulative planned budget spread over the project's weeks.

What are CPI and SPI? CPI is earned value divided by actual cost and shows cost efficiency; SPI is earned value divided by planned value and shows schedule progress. Values below 1 mean over cost or behind schedule.

How do I forecast a project's final cost? A common estimate at completion is the budget divided by the cost performance index, which assumes the project continues at its current cost efficiency.

How do I chart planned versus actual spend in Excel with Python? Write weekly cumulative planned and actual figures to a sheet, leave future actuals empty, and add an openpyxl LineChart with both series and the weeks as categories.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports in Excel.
• Consolidate Timesheets from Many Workbooks — where the hours come from.
• Build a Staff Utilisation Report from Timesheets — the same hours from the people angle.
• Build a Budget vs Actual Variance Report — variance for operating budgets.