Creating effective budgets using Xero Accounting Software
Xero is a cloud-based accounting software with all the time-saving tools necessary to enable real-time accounting. It is secure and reliable and provides 24/7 support for users and companies. You’ll learn more about Xero in later activities throughout the course.
Watch the following video to see how Xero creates budgets in Budget Manager: [1]
How can a business create an effective budget?
Let’s look at a step-by-step approach to creating an effective budget.
- Step 1: Identify budget assumptions (such as income and expense assumptions)
- Step 2: Review revenue bottlenecks
- Step 3: Determine available funding
- Step 4: Create a budget package with instructions and actual costs for the last budgeting period
- Step 5: Issue the budget package
- Step 6: Obtain the approved revenue budget. The sales revenue budget is the basis for developing other budgets.
- Step 7: Obtain department budgets
- Step 8: Obtain capital budget requests for fixed-asset purchases.
- Step 9: Update the budget model
- Step 10: Review the budget
- Step 11: Process budget iterations
- Step 12: Obtain approval for the budget and execute
Example
Now let’s look at an example of this process to create a master budget.
The Florio Company produces entertainment centres from a type of pressed wood. Other materials, such as glue and screws, are considered insignificant and are charged to ‘overheads’ as indirect materials. Budgeted or standard quantities allowed per unit along with the budgeted prices and rates are as follows.
Type of Input | Inputs per Output | Cost per Input | Cost per Output |
---|---|---|---|
Direct materials | 2 particle board sheets* | $10 | $20 |
Direct labor | 4 hours | $15 | $6 |
Factory overhead: | |||
Variable | 4 hours | $30 | $12 |
Fixed | 4 hours | $50 | $20 |
Total cost per output | $58 |
* Particle board is purchased in sheets that are ¾’ by 4’by8”
Source: Management Accounting: Concepts, Techniques & Controversial Issues[2]
Overhead rates are based on 4,800 standard direct labour hours per month, or an average monthly production of 12,000 units; i.e. (left(0.4right)timesleft(12,000right) = 4,800text{hours}). Desired closing inventories are 10% of next period’s material requirements for direct material and 5% of next period’s sales for finished goods. Unit sales are budgeted as follows for the first six months of the year.
Month | Unit sales |
---|---|
Jan | 9,000 |
Feb | 10,000 |
Mar | 11,000 |
Apr | 12,000 |
May | 14,000 |
Jun | 14,500 |
The budgeted sales price is $100 per unit. Sales are budgeted as 50% cash and 50% credit sales. Past experience indicates that 80% of the credit sales are collected during the month of sale, 18% are collected in the following month, and 2% are uncollectible. A 1% discount is allowed to customers who pay within the month the sale takes place. This includes cash sales.
Variable selling and administrative expenses are budgeted at 10% of sales dollars. The budget for fixed selling and administrative expenses is $50,000 per month. Cash payments are made for all expenditures during the month, except for depreciation of $100,000 in manufacturing and $25,000 in the sales and administration. The budgeted beginning cash balance for March is $100,000 and the tax rate is 40%. Budgeted income taxes from January and February are $200,000. This amount is to be paid at the end of March, together with the current month’s taxes. A three-month note for $50,000 is to be repaid at the end of March. The interest rate on the note is 12%.
Some additional account balances budgeted for the end of February include:
- land = $5,000,000
- buildings and equipment = $15,000,000
- accumulated depreciation = $6,000,000
- other current liabilities = 0
- long term liabilities = 0
- common stock = $5,000,000
- retained earnings = $8,993,000.
The following illustrations show a partial master budget for March, including parts of the operating budget, a cash budget, and an abbreviated balance sheet. Budgets for February and April can also be prepared with the given data. However, a budget for January would require unit sales for December, and a budget for May would require unit sales for July.
Sales budget
To develop a sales budget, we use the following calculations.
[text{Budgeted sales $} = left(text{budgeted unit sales}right)timesleft(text{budgeted sales prices}right)]
And:
[text{Current period cash collections} = text{current period cash sales} + text{current period credit sales collected in current period} + text{prior period credit sales collected in current period}]
The screenshots below show figures for the table sales budget, these are available as a PDF file at the bottom of the page.
Production budget
To prepare a production budget, consider the desired inventory change as follows.
[text{Units to be produced} = text{budgeted unit sales (from 1)} + text{desired ending finished goods} – text{beginning finished goods}]
We usually base the desired ending inventory on the next period’s sales budget. The production budget for March would be:
Production budget for March Units to be produced for March = Unit sales for March + Desired ending FG – Budgeted Beginning FG = 11,000 + (.05)(12,000) – (.5)(11,000) = 11,050 unitsNote that the desired ending inventory is based on April unit sales and the desired beginning inventory is based on march unit sales.
Direct materials budget
-
Quantity of Material Needed for Production = (Units to be Produced)(Quantity of Material Budgeted per Unit)
-
Quantity of Material to be Purchased = Quantity of Material Needed for Production + Desired Ending Material – Beginning MaterialThe desired ending materials quantity is normally based on the next period’s (month’s) materials needed for production and this amount depends on the third period’s budgeted unit sales. Factors that influence the desired inventory levels include the reliability of the company’s suppliers, as well as ordering and carrying costs.
-
Budgeted Cost of Material Purchases = (Quantity of Material to be Purchased)(Budgeted Material Prices)This amount is needed to determine cash payments. Once the quantity to be purchased has been determined, the cost of purchases can be easily calculated. Budgeted material prices are provided by the purchasing department.
-
Cost of Material Used = (Quantity needed for Production)(Budgeted Material Prices)
-
Cash Payments for Direct Material Purchases = Current Period Purchases Paid in Current Period + Prior Period Purchases Paid in Current Period
Direct materials budget for March 1. Direct Material quantity needed for production for March = (Units to be produced)(Material required per unit)(11,050)(2 sheets per unit) = 22,100 sheets
Direct Material quantity to be purchased for March = Material needed for production + desired ending mat – Budgeted beg mat = 22,100 + (.10)(24,200*) – (.10)(22,100) = 22,310 PBSNote: April units to be produced: 12,000 + (.05)(14,000) – (.05)(12,000) = 12,100 April material needed for production: (12,100)(2)= 24,200 sheets Budgeted cost of direct material purchases for March (22,310)($10) = $223,100 Budgeted cost of direct material used for March = (22,100)($10) = $221,000 Cash payments for direct material = purchases in this example = (see point 3).
Direct labour budget
-
Direct labour (DL) hours needed for production = (units to be produced)(DL hours budgeted per unit)Industrial engineers determine the amount of direct labour time needed per unit of product. Estimates are frequently made by using time-and-motion study. This involves measuring every movement in a task, and assigning a precise amount of time to it. Learning curves provide another quantitative technique that is helpful in establishing labour standards.
-
Budgeted direct labour cost = (DL hours needed for production)(budgeted rates per hour)The budgeted rates per hour for direct labour are provided by the human resource department. The labour (union) contract is often the source of this information. Many types of labour might be required, with different levels of expertise and experience.
Direct labour budget for March
- Direct labor needed for production for March = (11,050 units to be produced)(.4 hrs) = 4,420 D.L. hrs
- Budgeted cost of direct labor used for March. = (4,420 D.L hrs needed)($15 per hr) = $66,300
Budgeted overhead expense
- Budgeted Factory Overhead Costs = Budgeted Fixed Overhead + (Budgeted Variable Overhead Rate)(D.L. Hours needed for Production from Direct Labour budget point 1)
- Cash Payments for Overhead = Budgeted Factory Overhead Cost – Depreciation and other costs that do not require cash payments
- Budgeted factory overhead costs = (Total budgeted overhead rate per hour)
- Unfavourable planned production volume variance or – Favorable planned production volume variance
Budgeted Factory Overhead Costs for March
Budgeted Factory overhead costs:
Variable overhead ($30 per hr)(4,420 hrs from 4a) $132,600 Fixed overhead ($50 per hr)(4,800 denominator hrs given) 240,000 Budgeted factory overhead costs $372,600 Alternative approach Factory overhead at standard (4,420 hrs)($80 per hr) $35,600 Adjust for the unfavorable Production Volume Variance (380 D.L. hrs planned below denominator hrs)($50) 19,000 Budgeted factory overhead costs $372,600 Note that the overhead rates are based on 4,800 hours per month, but the planned hours only 4,420. This causes standard fixed costs to be 19,000 less than budgeted fixed costGraphically the standard amount is below the budget amount. Therefore we must add the 19,000 to standard factory overhead cost to obtain the budgeted amount. Cash Payments = $372,600 – 100,000 = $272,600
Inventory budget
-
Ending direct materials = (desired ending materials) (budgeted prices)
-
Budgeted or standard unit cost = (Quantity of Direct Material (D.M) required per unit)(budgeted prices) + (DL hours required per unit)(budgeted rate) + (total overhead rate)(DL hours required per unit)You can calculate the budgeted or standard unit cost at any time after you’ve obtained the budgeted quantities per unit and input prices. The calculation is placed here because it is needed below.
-
Ending finished goods = (desired ending finished goods)(budgeted unit cost)
Ending Inventory Budget for March
Ending Direct materials = (Desired Ending materials from 3b)(Budgeted Prices) = (.10)(24,200)($10) = (2,420 sheets of particle board)($10) = $24,200 Budgeted or Standard Unit Cost = (Quantity of D.M required per Unit)(Budgeted Prices)(D.L. Hours required per Unit)(Budgeted Rate)(Total Overhead Rate)(D.L. Hours required per Unit) = (2)($10) + (.4)($15) + (.4)($80) = 20 + 6 + 32 = $58 Ending Finished Goods = (Desired Ending Finished Goods from 2) (Budgeted Unit Cost) = (.05)(12,000 April budgeted unit sales)($58) = (600)($58) = 34,800
Cost of goods sold budget (COGS)
-
Budgeted total manufacturing cost = cost of direct material used + cost of direct labour used + total factory overhead costs
-
Budgeted cost of goods sold = budgeted total manufacturing cost + beginning finished goods – ending finished goods
Budgeted Cost of Goods Sold for March
Total manufacturing cost: D.M from 3d + D.L. from 4b + F.O from Budgeted factory overhead costs
221,000 + 66,300 + 372,600$659,900 Add beginning finished goods (.05)(11,000)($58 from Budgeted or standard unit cost) + 31,900 Subtract ending finished goods
(.05)(12,000)($58)– 34,800 Budgeted cost of goods sold $657,000 Alternative approach Standard cost of goods sold:
(11,000 units sold)($58 per unit)= $638,000 Adjustment from standard to budget:
Unfavorable production volume variance from Budgeted factory overhead costs= 19,000 Budgeted cost of goods sold $657,000
Selling and administrative budget
-
Budgeted selling and administrative expenses = budgeted fixed selling and administrative expenses + (budgeted variable rate as a proportion of sales $)(budgeted sales $)
-
Cash payments for selling and administrative expenses = budgeted selling and administrative expenses – depreciation and other costs that do not require cash payments
Selling and Administrative Budget
Variable costs (.10)(1,100,000) $110,000 Fixed costs 50,000 Total $160,000
Budgeted income statement
-
Budgeted sales $ – budgeted cost of goods sold = budgeted gross profit
-
Budgeted gross profit – budgeted selling and administrative expenses = operating income
-
Operating income – interest expense – bad debts expense = net income before taxes
-
Net income before taxes – income taxes = net income after taxes
Budgeted income statement
Sales $1,100,00 Cash discounts 9,900 Net Sales 1,090,100 Less Cost of goods sold 657,000 Gross Profit 433,100 Less Selling & Administrative expenses 160,000 Less Bad debts (1,100,000)(.5)(.02) 11,000 Interest expense (50,000)(.12) / 12 500 Net Income Before Taxes 261,600 Less Income Taxes (.40)(261,600) 104,640 Net Income After Taxes $156,960 Notice that bad debts are 2% of credit sales and credit sales are 50% of total sales. The interest expense is for one month’s interest on the three month note.
Cash budget
- Budgeted cash available = beginning cash balance + budgeted cash collections
- Budgeted cash excess or deficiency = budgeted cash available – budgeted cash payments
- Ending cash balance = cash excess or deficiency + borrowings – repayments including interest
Cash budget
Beginning cash balance $100,000 Collections from Production budget 1,070,100 Cash available 1,170,100 Less payments: Direct material from Direct materials budget 223,100 Direct labor from Direct labour budget 66,300 Factory overhead 372,600 from budgeted overhead expense – 100,000 depreciation 272,600 Selling & administrative 160,000 from selling & administrative budget – 25,000 135,000 Income taxes 200,000 + 104,640 304,640 Excess or deficiency $168,460 Financing: Borrowings 0 Repayments $50,000 + ($50,000)(.12)(3/12) 51,500 Ending Cash balance $116,960
Budgeted balance sheet
You should now understand the types of budgets, approaches to budgeting, and the steps for creating effective budgets.
References:
- Using the budget manager in Xero [Video]. Xero; [date unknown]. Available from: https://tv.xero.com/detail/video/5131036429001/using-the-budget-manager-in-xero
- Martin JR. Chapter 9: The Master Budget or Financial Plan. Management Accounting: Concepts, Techniques & Controversial Issues [Book]. Management And Accounting Web; [date unknown]. Available from: https://w.maaw.info/Chapter9.htm
Financial Analysis for Business Performance: Planning, Budgeting, and Forecasting
Financial Analysis for Business Performance: Planning, Budgeting, and Forecasting
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.
Register to receive updates
-
Create an account to receive our newsletter, course recommendations and promotions.
Register for free