Operating budget template: a budgeted profit and loss with free Excel

What is an operating budget?

An operating budget shows whether the business makes a profit: expected revenue minus all costs, per month or per year. It is a budgeted profit and loss account. Lenders and investors ask for one with a funding application, and in a business plan it forms the financial plan together with the capital budget and the cash flow forecast. It is just as useful in an established business: without a budget there is nothing to hold actual results against.

Want to start straight away? Download the free operating budget Excel template. It is filled with the example from this article and works out gross profit, operating profit and net profit per month.

Operating budget, cash flow forecast and capital budget

The three are easily confused. Each answers a different question.

The main difference is timing. A 60,000 van is one purchase in the capital budget, a payment in the month you pay for it in the cash flow forecast, and 12,000 of depreciation a year in the operating budget over five years. How to build the forecast is covered in how to build a cash flow forecast.

The line items

Seven steps to an operating budget

Step 1: build revenue from the bottom up. Never start with a single total. Split revenue into streams and work each out as price times volume. An installation company might use the number of jobs per month times the average job value, plus fixed income from service contracts. Allow for seasons and holidays.

Step 2: take cost of sales as a percentage. Use the percentage from last year's figures. In the example, materials and subcontracting are 45% of installation revenue. Check the gross margin with the margin calculator.

Step 3: include the full staff cost. Use gross salary plus employer taxes, holiday pay and pension. Ask your payroll provider for your own on-cost percentage, and put one-off payments in the month they are paid.

Step 4: other fixed costs. Rent, vehicles, software, insurance and marketing. Go through last year's bank statements; the costs people forget are almost always small subscriptions that add up.

Step 5: depreciation from the capital budget. Divide the purchase price by the useful life. Land is not depreciated.

Step 6: interest and tax. Interest follows from the funding proposal. Enter your own tax rate; there is no profit tax on a loss.

Step 7: compare with actuals every month. A budget that sits in a drawer is worthless. Put budget and actual side by side each month and explain the large differences. That is the heart of good management reporting.

Example: operating budget of an installation company

A fictional installation company with five people budgets for 2027. These are the annual totals from the Excel template.

Two things stand out. Revenue in July and August is lower because of the summer holidays while fixed costs continue; in those months profit is close to zero. And operating profit is 10.4% of revenue: if revenue comes in 10% lower, profit falls by more than half. The minimum revenue you need follows from the break-even calculator.

Common mistakes

• Including VAT. Amounts in the operating budget exclude VAT. For a VAT-registered business VAT is neither revenue nor cost.
• Treating investments as costs. A 60,000 purchase is not a 60,000 cost but depreciation over its useful life.
• Including loan repayments. Repayments are not a cost; only interest is. Repayments belong in the cash flow forecast.
• Revenue as one number. Without the build-up, nobody, including you, can judge whether the budget is realistic.
• Forgetting the owner's pay. For a sole trader, your own income is not a cost, but profit has to be enough to live on and pay tax.

Budget against actual without manual work

Comparing budget with actual every month takes a lot of copying and pasting in Excel. Connect your accounting, such as Xero, QuickBooks Online or Exact Online, to DigiData and the financial dashboard puts actual results and budget side by side each month. Check ratios from your budget, such as return on equity and solvency, with the free calculators.