When people think of budgeting, they think of income and expenses. That is an operating budget. This operating budget template will walk you through how to make one for your small business.
The other types of budgeting are capital budgeting (the estimating of profitability of projects) and financial budgeting (the forecasting of cash flows).
More specifically an operating budget estimates every line item on an income statement. Sales, materials, labor, overhead (cost of goods sold (COGS)), and SG&A (selling, general, and administrative) costs. The end result is a pro forma (expected) income statement. The pro forma balance sheet and cash flow statement are created with the financial budget.
An operating budget starts with a sales forecast and logically works through all of the expenses necessary to achieve that level of sales. Demand drives everything.
If you need help with forecasting, use this spreadsheet template I created.
This operating budget template is designed to work for a manufacturer. A manufacturer has a more complicated value-added process than a service company or non-profit. However, the same principles apply to those types of organizations. A little tweaking is all that’s needed to make this work.
So I don’t have to write it out a million times:
Nothing can replace your experience when looking toward the future. As the manager of a small business, you know its ins and outs best. Everything you enter into this workbook deserves your quantification and qualification. What’s that mean???
Quantification means that you use past amounts to forecast a future amount. For example, if an expense averaged $100 a month for the past five years – mathematical formulas are going to project that expense to be around $100 a month for the coming year.
Qualification is applying your knowledge to the quantification amounts. Say you know that the previously mentioned $100 a month expense is going to increase to $200 a month because of greater demand. That’s valuable knowledge which is priceless in the budgeting process. Be sure to apply that where you can.
Quantification has to do with numbers.
Qualification has to do with know-how.
Looking for more spreadsheet templates?
Complete the form below and click Submit.
Upon email confirmation, the workbook will open in a new tab.
Not exactly an optimistic way to start off budgeting for next year. Nevertheless, if sales are made under any sort of credit terms, no matter how strict, some customers will not pay what they owe. People are people after all…we might as well plan around reality. Enter an appropriate default percentage here.
The effects of this amount can be seen in the Uncollectible portion row.
Next, enter the first month of the forecasted period. The rest of the months will populate automatically. The workbook is set up to forecast for twelve months. You’ll notice that there are an additional two months added on to the sales quantity budget. That’s there for the other budgets that are yet to be completed.
Now, we’ll look at the expected quantity (units) of sales for each item you sell. The example workbook only has room for five items (in the Items column). That’s to conserve space. You probably sell more items than that, and that’s good. The same principles will apply whether you sell five items or 5,000.
Anyhow, for every item you sell you’ll enter an expected sales quantity, by month, for all of the periods specified.
Note that the Total only reflects the sum of the first twelve months. The extra two months on the end will be used as a reference for other worksheets.
Once you’re confident in the sales quantities, enter an average Sales Price for each item.
If prices are expected to change throughout the year, enter one average expected sales price. This workbook is complicated enough, we’ve got to simplify things where we can.
That’s the last piece of information needed on this worksheet. Based on what you entered for quantity sales and Percentage of sales assumed to be uncollectible, the rest will populate. What you’re left with is a Net sales amount. The top line for your operating budget.
Now, we’ll move on to the costs that had to be incurred to make those sales.
Most manufacturing companies don’t manufacture on a just-in-time basis. That would be ideal if it were practical. But, the reality is – some inventory will have to be carried to quickly meet unanticipated demand. This percentage specifies how much of the next month’s sales you plan to have in inventory at month-end.
That’s the only variable entered on this worksheet. Everything else is calculated.
For each item, this will pull the sales quantity from the Sales Budget.
This is the quantity of each item you plan to carry into the next month. This is calculated by simply taking the % of next month’s sales in ending inventory × the next month’s Budgeted unit sales.
This is the (negative) amount of inventory you carried into the current month.
Why is the amount negative? That’ll be made clear in a moment.
You’ll notice that this amount is the inverse of last month’s Plus ending inventory.
The Budgeted unit sales for the month + Plus ending inventory – (Less) beginning inventory tells you how many units of each item you should plan on manufacturing for a given month.
Hopefully, the reasoning is pretty straightforward. You have to manufacture enough to cover what you’re going to sell and what you need to carry over in inventory to next month. But, since you’re bringing over a certain number of units from last month – you can subtract that from what you need to manufacture.
Note that the Total column only sums the Units to be produced for the next twelve months. The extra month included at the end (as a reference for the Direct Materials Budget) is not included in the sum.
Now that you know how many finished goods you’re going to have to produce, it’s time to figure how many direct materials you’re going to have to buy.
For each Item list the Direct Material (name), Quantity (needed to make one finished good), and Direct Material Cost/Unit.
What if your material costs change throughout the year? If so, you’ll have to enter one average estimated cost for the whole year. This is your “standard cost” and it’s what you’ll judge your material expenses against for the next year.
This will calculate automatically by taking Quantity × Direct material Cost/Unit. Total material cost is also shown to reflect the total value of materials in each bill of material.
The Direct Materials Budget is similar to the Production Budget.
This will play a part in determining how many Units to be purchased you’ll need for a given month. Everything else is calculated automatically in this worksheet.
This data pulls from the Units to be produced (Production Budget) and multiplies that times the Quantity (Bill(s) of Material).
DM units needed for production + Plus ending inventory – (Less) beginning inventory. This tells you how many units of each direct material you’d need to purchase in a given month to meet your production and inventory goals. Keep in mind that a negligible amount of lead time (the time it takes for your supplier to get a direct material delivered) is assumed. If your direct materials have a long lead time, you’ll want to carry a greater % of next month’s production in ending inventory.
Pulls from the standard cost specified in the Direct Material Cost/Unit (Bill(s) of Materials) field. This amount is then multiplied by the Units to be purchased to give you a Total amount for each direct material within each bill of materials.
Some of the same direct materials might be used across several different bills of material. This table summarizes the Direct Materials Budget Detail in a somewhat easier to understand format at the bottom of the worksheet. Here you can see the totals for each direct material by month along with a Grand Total for the year.
In a similar fashion to what you did on the Bill(s) of Materials worksheet, you’ll need to specify how much direct labor is required to build each finished good.
Direct labor is measured in man-hours. A man-hour may or may not be the same as a regular hour. It all depends on the size of the crew. For example – say it takes 3 people 1.5 hours to manufacture a particular item. This item would require 4.5 man-hours (3 × 1.5). Another example – say it takes 2 people 15 minutes to manufacture another item. This item would require .5 man-hours (2 × .25).
For each item, you manufacture – enter the expected average man-hours in the Hours field. Also, enter the expected average hourly rate in the Direct Labor Rate/Hour field. Depending on the skills and experience needed to manufacture a particular item, different items might have different hourly rates.
Like you did on the Bill(s) of Materials worksheet, enter an appropriate single rate for the whole year. This will be your direct labor “standard rate” for that item.
With these two pieces of information entered, everything else in the worksheet will calculate automatically.
Hours × Units to be produced (Production Budget). This is the total man-hours you expect to be spent on that finished good for a given month.
DL cost/hr simply uses the same rate as entered above in the Direct Labor Rate/Hour field.
Total DL cost for Item_ is then calculated for each month. As a Total for all twelve months for each item.
Finally, the Total DL Hours and Grand total direct labor cost are calculated.
An Average DL rate/hr, for all items you manufacture, is also included for reference.
Along with direct materials and direct labor – overhead is the final piece of inventory valuation. What costs to include in overhead and what constitutes fixed or variable overhead is very subjective. The subject could warrant a post unto itself.
Here’s a good post about what costs to include in overhead.
Once you’ve determined what costs to include in overhead and divided them into variable (rise and fall with production) overhead and fixed (stay the same no matter the level of production) overhead then these amounts can be entered in their respective fields: Total budgeted variable overhead and Total budgeted fixed overhead.
Since overhead, by its very nature, consists of indirect costs – how it gets divvied up among the various manufactured items is somewhat more complicated than with direct costs like materials and labor. How it is done in this example is not the only way to do it. However, in the interest of keeping things relatively simple – all overhead will be applied on the basis of direct labor hours.
Total budgeted variable overhead ÷ Total expected direct labor hours (Total DL Hours (Direct Labor Budget)) = VOH rate/DL hour. This rate will be added to every direct labor hour to account for the cost of variable overhead.
Fixed overhead will get treated slightly differently than variable overhead. Total budgeted fixed overhead gets spread evenly among all items and all months. So, low-volume items will get a bigger proportion of fixed overhead and high-volume items a smaller proportion. In this example, there are five items manufactured and twelve months in the year. This gives us a denominator of 60 (5 × 12), and a Fixed overhead amount per item per month of $18,863, in the example.
DL hours needed for production (Direct Labor Budget) × VOH rate/DL hour = Allocated VOH by item by month. This is then added to the Allocated FOH (aka Fixed overhead amount per item per month). The monthly Total allocated OH for Item_ is totaled for the year. That amount is then divided by DL hours needed for production to give an Allocated OH/DL hour.
Allocated OH/DL hour × DL hours/unit (Hours (Direct Labor Budget))
As you can see, the Allocated OH/unit is different for each item. This is due to the Allocated FOH being spread across more or less DL hours needed for production.
At the very bottom, you’ll see the Overhead Budget Summary which takes all of the previous information and reformats it to show the overhead incurred by every item every month. It also gives an Average OH/DL hour for company to let you know which items incur above average and below-average overhead rates.
Nothing to enter on this worksheet! With direct materials, direct labor, and overhead information entered, everything you need is already accounted for.
This is equal to the Plus ending inventory (Production Budget) for the last month of the budget period for each item.
The Cost of direct materials pulls from Total material cost (Bills(s) of Materials).
Cost of direct labor pulls from Direct Labor Cost/Unit (Direct Labor Budget).
Cost of overhead pulls from Allocated OH/unit (Overhead Budget).
Each of these costs is per unit and they are summed to give you a “standard” cost for each finished item. That Total cost is then multiplied by the Ending inventory amount to give you an expected year-end inventory value by item and a Total ending FG inventory value for all finished goods.
Another worksheet with nothing to enter!
Here, you’ll see each Item_ you sell along with the Quantity ((Less) beginning inventory (Production Budget)) and the Value (Total cost (Ending FG Inventory Budget)) you expect to start the year off with. The Extended Value is simply Quantity × Value.
The Total value of your beginning inventory is also summed.
Again, you’ll see each item you manufacture. Quantity (Units to be produced (Production Budget)) is the number of units you expect to manufacture over the coming year. Value is the same across the entire workbook. Extended Value is also calculated across the entire workbook (Quantity × Value).
Cost of goods manufactured is the total value of all the items you plan to manufacture this year.
Total Begining FG inventory + Cost of goods manufactured.
Same principle as above. Quantity (Ending inventory (Ending FG Inventory Budget)) is negative because these items won’t be included in Cost of goods sold, the plan is to hold them in inventory and they’ll be next year’s Begining FG inventory.
Cost of goods available for sale – Total Ending FG inventory.
This worksheet will include every other expense included in the income statement. In this example, the expenses are consolidated by Department.
This is a judgment call. Not every expense in a particular department is going to be fixed or variable. Think about the department as a whole.
For instance, departments like Finance/Accounting, Research & Development, and Customer Service might be expected to have the same level of expenses if sales are a little higher or lower than expected.
Conversely, departments like Sales/Marketing and Shipping will probably have expenses closely tied to the level of sales. Sales go up, their expenses go up. Sales go down, their expenses go down.
Fill in the expected costs for each Department for each month. A Total will be calculated for each Department and each month.
Do not include taxes and interest in these amounts. Taxes and interest will be entered separately on the Pro Forma Income Statement (Executive Summary).
Fortunately, most of the work has now been done on the Pro Forma Income Statement. Only a few pieces of information need to be entered.
Enter the effective tax rate for your business’s Operating profit (EBIT) minus the effects of interest.
Net Sales (Net Sales (Sales Budget)) – Cost of goods sold (Cost of goods sold (Cost Of Goods Sold Budget)).
Gross profit – Operating expenses (Total (SG&A Budget(s))).
Interest income is the amount of interest earned on interest-bearing investments. This might simply be interest from your bank’s checking account. Or, if you’re a little more sophisticated, you might park your excess cash in Treasury securities to earn a little extra interest.
Interest expense is the interest you pay on short and long-term loans taken out to finance operations.
Tax is the amount owed to federal, local, and state governments. It is calculated as follows: Tax rate (%) × [Operating profit (EBIT) + Interest income – Interest expense].
The bottom line!
The amount of money you get to keep after all the expenses needed to reach that level of sales is deducted.
Operating profit (EBIT) + Interest income – Interest expense – Tax.
Ratios compare two or more aspects of your business and are meant to provide some perspective on your business’ health and allow you to compare to other businesses.
Net profit ÷ Net Sales.
Profit margin tells you the percentage of sales you carry to the bottom line. Obviously, a higher Net profit is better, as long as it doesn’t come at the expense of long-term results.
Gross profit ÷ Net sales.
The Gross margin is the percentage of sales you carry to Gross profit. Gross profit typically has less room for improvement than Profit margin. Raising prices is the quickest and easiest way to increase Gross margin. High-margin businesses are more desirable than low-margin ones.
Operating profit (EBIT) ÷ Interest expense. This ratio gauges an organization’s ability to meet its debt obligations.
Operating profit (EBIT) ÷ [Operating profit (EBIT) – Interest expense]. This is the degree to which borrowing will help or hurt your Net profit. Tax notwithstanding.
For example, say your Degree of financial leverage is 1.02. This means that any percentage change in Operating profit (EBIT) will be amplified by 2% (taken times 1.02). A 10% increase in Operating profit (EBIT) would equal a 10.2% increase in Net profit. Conversely, a 10% decrease in Operating profit (EBIT) would equal a 10.2% decrease in Net profit.
The effects of leverage amplify both gains and losses.
Learn more about the Degree of financial leverage here.
Alright…this one’s a more little complicated, so stick with me. The Degree of operating leverage, more or less, measures the same thing as the Degree of financial leverage. Except, in this case, the fixed cost isn’t interest – it’s all fixed costs (except interest).
At it’s simplest, Degree of operating leverage = contribution margin ÷ (contribution margin – fixed costs).
Contribution margin = sales – variable costs. This is the amount that gets “contributed” toward paying off fixed costs. Once fixed costs are paid off, it’s the amount that gets “contributed” toward profit. When I post my Breakeven Analysis worksheet, this will all make more sense.
I’ll leave it at this – the higher this number the higher the change in Operating profit (EBIT) due to a change in Net sales. For instance, if the Degree of operating leverage is 2.47, then a 10% increase or decrease in Net sales is going to result in a 24.7% change in Operating profit (EBIT) ±(2.47 × 10%).
Learn more about the Degree of operating leverage here.
This chart provides a summary of all the information you entered into the SFB – Operating Budget Workbook.
It’s a visualization of each month’s sales, COGS, and SG&A expenses. The gap between the columns (costs) and line (sales) represents your company’s profit.
Join the conversation on Twitter!