Access the essential membership for Modern Managers
You have recently been asked to prepare a sales budget, and you have no idea where to start. Working through the following example will help you get to grips with the basics before you go on to apply your skills to your own sales budget.
Task
- Read the ‘Scenario’ and ‘Information’ below.
- Work through the ‘Approach’ below to create a sales budget.
Scenario
You are the manager of a department that makes and sells a single product. You have been asked to prepare the sales budget for your department. To do this, you should complete the following steps:
- Establish your assumptions
- Identify your variable costs
- Identify the contribution margin
- Identify your fixed costs
- Pull together the numbers in your budget format
- (You may also want to do this additional step.) Model any effects on changing your assumptions or numbers
Information
The current selling price of the product is £110.
Manufacturing costs per unit of product are:
- Labor - £22
- Materials - £37
- Variable overheads - £11
Your department's expenses consist of production overheads of £1,435,000 per annum and sales and other administration costs of £775,000.
You must base your budget on:
- selling 70,000 units of product
- at this 70,000 units level, you expect to be using 70% of your available production capacity
Your colleagues would also like to know how the following will change the budget:
- achieving sales volumes of 75% of the planned level of 70,000
- increasing sales volume to 85% of capacity
Approach
Work through the following steps to see how your budget develops:
Establish Your Assumptions
You need to base your budget on the following assumptions:
- selling 70,000 units of product
- at this 70,000 units level, you expect to be using 70% of your available production capacity
Identify Your Variable Costs
Making each unit of product involves £70 of variable costs (£22+£37+£11).
Identify the Contribution Margin
Deducting this from the selling price tells you that the sale of each unit of product will result in a contribution to fixed costs of £40 (£110 – £70). The term ‘contribution’ is used to refer to the amount of income from the sale of a product or service that is available after deducting the cost of all those items that vary with production. In your case, labor, materials and some overheads all vary in direct proportion to production levels, and therefore the contribution to fixed costs is what is left after these have been deducted.
Identify Your Fixed Costs
The fixed costs of your department consist of production overheads of £1,435,000 per annum and sales and other administration costs of £775,000.
Pull Together the Numbers in Your Budget Format
First, calculate the data you will need:
Prices and Costs Per Unit
Sales price
£100
Labor cost
£22
Materials cost
£37
Variable overhead cost
£11
The table separates the basic input data from the results. It helps to ensure that you do not accidentally overwrite or otherwise corrupt an item of input data and allows you to see all of your input and output in one place.
You can now go on to create the budget. Using the format below, data items are arranged logically, with similar items grouped together. The flexibility of the spreadsheet is useful here in that rows and columns may be inserted and deleted as necessary to allow the original grouping to be maintained when new data items come along.
Key input cells are annotated to remind you of what they contain and how they are calculated. This can be an invaluable device when, some months after the initial build of a model, you come back to it wondering what a particular piece of data or calculation is.
Fixed Costs (Per Annum)
Production overheads
£1,435,000
Administration and selling overheads
£775,000
Model Any Effects on Changing Your Assumptions or Numbers
Calculate the data you will need to model the changes:
Prices and Costs Per Unit
Sales price
£110
Labor cost
£22
Material cost
£37
Variable overhead cost
£11
Fixed Costs (Per Annum)
Production overheads
£1,435,000
Administration and selling overheads
£775,000
Other Data
Budgeted sales volume
70,000
Production capacity utilization at
70%
Lower sales %
75%
Lower sales volume
52,500
Upper sales %
85%
Upper sales volume
85,000
Then go on to model the changes. By laying them out column by column (with the lowest unit sales on the left-hand side and the highest unit sales on the right), it is easy to compare the figures and the effects:
Sales Budget
Unit sales
52,500
70,000
85,000
Sales income
£5,775,000
£7,700,000
£9,350,000
Labor
£1,155,000
£1,540,000
£1,870,000
Materials
£1,942,500
£2,590,000
£3,145,000
Variable overheads
£577,500
£770,000
£935,000
Variable Costs
£3,675,000
£4,900,000
£5,950,000
Budgeted Contribution
£2,100,000
£2,800,000
£3,400,000
Production overheads
£1,435,000
£1,435,000
£1,435,000
Sales and administration overheads
£775,000
£775,000
£775,000
Fixed Costs
£2,210,000
£2,210,000
£2,210,000
Budgeted profit/(loss)
(£110,000)
£590,000
£1,190,000
Conclusion
The model contains almost all the basic elements necessary to allow you to model the financial performance of your own department. It could easily be adapted, for example, to:
- Increase the number of products. You could extend the columns on the existing worksheet or use additional worksheets for each product.
- Increase the number of cost items, by inserting rows in either the fixed or variable cost elements.
- Include more detailed analysis of costs and revenues. The components of fixed production costs could be built up on a separate worksheet for example, and then linked to the summary model.
