How do you calculate a burden rate?

Divide the overhead budget by the budget direct labour hours. 120,000 of overhead over 6,000 hours is a 20.00 burden rate; with a 25.00 direct wage, wage plus burden is 45.00 an hour, the Burden Rate calculator's own example.

What goes in the overhead budget?

Indirect production cost: supervision and indirect labour, equipment depreciation, energy, consumables, maintenance and the department's share of building costs. Direct wages are not overhead; the workbook takes them as the wage rate. Materials and selling and administrative costs stay out.

Why calculate the rate by department?

Because departments carry very different overhead per hour. In the example the paint line's energy, chemicals and depreciation give it 45.39 an hour against 4.80 in assembly. A plant-wide rate of 16.79 would undercost painted parts and overcost assembled ones.

What is under-applied overhead?

Overhead actually spent minus the overhead the rate applied (hours worked x rate). When fewer hours are worked than budgeted, the rate applies too little. In July the example plant works 3,980 hours: 3,980 x 16.79 = 66,842 applied against 77,600 spent, 10,758 under-applied.

What does the status mean?

Applied overhead within 2% of actual is On target, within 5% Near target; beyond that, under-applied is Below target and over-applied Above target. Several months Below target mean the rate is too low for the hours you really work.

Does the workbook use macros?

No. It is a plain .xlsx with ordinary formulas, tables and native charts. The Verify sheet re-checks the formulas against a fixed test budget and shows Self-check: PASS.

Manufacturing Cost and Quoting

Burden Rate Excel Template

Burden rate = overhead budget ÷ budget direct labour hours. List your overhead budget by department and each department's hours and wage, and see the burden rate and wage plus burden for every department and the plant, then check each month whether the rate applied the overhead you really spent.

Free, and a plain .xlsx with no macros to enable. A CSV with live formulas and a printed how-to guide come with it.

Overview

This burden rate template is a working Excel workbook for cost accountants, plant controllers and estimators. It builds the overhead burden rate from your own overhead budget lines, by department, then checks the rate every month against the overhead you really spent. It ships with a year's budget for five departments of a fictional fabricator and ten months of actuals, so every tile and chart is filled in when you open it.

In the example 963,000 of overhead over 57,340 budget direct labour hours gives a plant-wide burden rate of 16.79 an hour. At an hours-weighted wage of 27.11, wage plus burden is 43.90 and burden is 62% of the wage. The paint shop carries 45.39 of overhead per hour, almost twice its wage, while assembly carries 4.80.

The monthly check shows why a rate needs watching: most months apply within 2% of the overhead spent, but June to August, with a summer shutdown and slow orders, work fewer hours and under-apply 6,340 to 10,758 a month, 28,034 under for the year to date. The download is a plain .xlsx with no macros. A CSV with live formulas is also included for other tools; the monthly check, charts, dropdowns and self-check are in the .xlsx.

What this template includes

Overhead table, one row per indirect cost line with its department and annual budget, and a Check column
Departments table: budget direct labour hours and wage rate in; overhead budget, burden rate, wage plus burden and burden as a share of the wage out, with plant-wide figures under it
Monthly table: direct labour hours worked and actual overhead; overhead applied at the plant-wide rate, over or under applied, variance and a status in words
Dashboard with four tiles (plant-wide burden rate, wage plus burden, burden share of the wage, over or under applied to date), burden rate by department, and applied against actual overhead by month
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference and the Burden Rate calculator (Self-check: PASS)
CSV with live formulas for the Departments table; the monthly check, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it when you set next year's overhead rates, to see which departments carry the most overhead per hour, and each month to catch an under-applied rate before the year-end surprise.

How to use it

On Setup, type your company, the budget period and currency, and list your departments.
On Overhead, list each indirect cost line for the period with its department and annual budget.
On Departments, type each department's budget direct labour hours and direct wage rate.
Each month, type the direct labour hours worked and the actual overhead on Monthly.
Open the Dashboard: burden rates by department and plant-wide, and applied against actual overhead by month.

Frequently Asked Questions

How do you calculate a burden rate?: Divide the overhead budget by the budget direct labour hours. 120,000 of overhead over 6,000 hours is a 20.00 burden rate; with a 25.00 direct wage, wage plus burden is 45.00 an hour, the Burden Rate calculator's own example.
What goes in the overhead budget?: Indirect production cost: supervision and indirect labour, equipment depreciation, energy, consumables, maintenance and the department's share of building costs. Direct wages are not overhead; the workbook takes them as the wage rate. Materials and selling and administrative costs stay out.
Why calculate the rate by department?: Because departments carry very different overhead per hour. In the example the paint line's energy, chemicals and depreciation give it 45.39 an hour against 4.80 in assembly. A plant-wide rate of 16.79 would undercost painted parts and overcost assembled ones.
What is under-applied overhead?: Overhead actually spent minus the overhead the rate applied (hours worked x rate). When fewer hours are worked than budgeted, the rate applies too little. In July the example plant works 3,980 hours: 3,980 x 16.79 = 66,842 applied against 77,600 spent, 10,758 under-applied.
What does the status mean?: Applied overhead within 2% of actual is On target, within 5% Near target; beyond that, under-applied is Below target and over-applied Above target. Several months Below target mean the rate is too low for the hours you really work.
Does the workbook use macros?: No. It is a plain .xlsx with ordinary formulas, tables and native charts. The Verify sheet re-checks the formulas against a fixed test budget and shows Self-check: PASS.