Budget vs Actual Variance Report (Month, Year-to-Date, Forecast and Commentary)

About the Budget vs Actual Variance Report (Month, Year-to-Date, Forecast and Commentary)

Every month someone has to compare the actuals with the budget, find the lines that moved and write why. This report does the comparing for you, on your own device: choose your accounting system’s budget vs actual export (or a budget file and a trial balance), and it works out each line’s variance, variance % and effect on profit for the month, the year to date and the full-year forecast, totals them by account type and by department, and flags every line over the limits you set.

Type your commentary beside each flagged line. The management variance pack then has a cover page with a waterfall chart from the budgeted profit to the actual profit, the summary by account type, the flagged lines with your commentary, the departments and every line, as a PDF or an Excel workbook.

Without a pass the report is a free preview: the total variance, every count and the first flagged lines (up to 10) with their figures. The summaries, the waterfall chart, the other lines and the PDF, Excel and CSV files unlock with a Premium pass. Nothing is uploaded.

Common uses

• A finance team preparing the monthly management accounts and board pack.
• A business owner checking which costs ran over budget this month, and why.
• Department heads explaining their lines before the month-end review.
• An accountant reviewing a client’s budget against its trial balance.

How the variances are worked out

• Variance = actual − budget, for each line and each period.
• Variance % = variance ÷ the budget (shown as “no budget” when the budget is zero).
• Favourable or unfavourable: an income line is favourable when the actual is above the budget; a cost line when it is below.
• Effect on profit = the variance for income lines, and the variance with its sign turned for costs. The effects add up exactly to the change in net profit, which is what the waterfall shows step by step.
• Full-year forecast: the actual to date plus the budget for the rest of the year (the full-year budget less the budget to date), the run rate (actual to date ÷ the months elapsed × 12), or the forecast column of your file.
• Flags: a line is flagged when its variance reaches both the percentage and the amount you set, on the period you choose; a line budgeted at zero is flagged on the amount alone.

Comparing actual performance with planned results and looking into the significant differences is a basic management control in the GAO’s internal control standards.

Reading an export

• Account types come from the type column of your file (Revenue, Other income, Cost of sales, Operating expenses, Other expenses), else from the section heading a line sits under (“Income”, “Cost of Goods Sold”, “Expenses”, “Less Operating Expenses”, “Other Income”, as accounting exports group their lines), else from the account name; anything else counts as an operating expense.
• Subtotal and result lines (“Total revenue”, “Gross profit”, “Net operating income”, “Net income”) are left out so nothing is counted twice, and so are headings without amounts. Balance-sheet lines (bank, receivables, payables, loans, equity: by their type, their heading or, under no heading, their name) are left out too, as they are not part of profit.
• Amounts in brackets or with a trailing minus are negative, a dash is zero, and either decimal mark is read. A trial balance with separate debit and credit columns gives each actual as the debit less the credit. Income written as negative numbers (credits) is turned over by itself, file by file; the checkbox in Columns changes it.
• With two files, lines are matched on the account code, else the account name, and the department; a line only in the actuals file shows with a budget of zero, or is left out when you tick “Leave out lines that are not in the budget”.

Writing commentary that answers the question

Good variance commentary says what happened, why, and whether it will continue: “Autumn campaign brought forward from November: timing, the full-year spend is unchanged” tells the reader that the year-to-date variance will reverse; “Price increase from our main supplier: permanent, about 4% on cost of goods for the rest of the year” tells them the forecast must move. A favourable variance needs a reason too: a cost that has not happened yet is not a saving.

Limitations

• It compares what your files say; it does not check them against your books or produce the budget.
• One sheet a file (the first sheet with a table), one header row, up to 5,000 lines.
• Account types from names are a best guess: a type column, or section headings such as Income and Expenses, give the right signs; choose the type column in Columns when the file has one.
• The commentary you type stays on this page and in the files you make; it is not kept when you leave the page.
• For management review, not an audited statement or professional advice.

Privacy

On this device; its files download once

Your files are read in your browser and never uploaded or stored; the commentary you type stays on this page.

How to check that nothing is uploaded