Skip main navigation

Creating effective budgets using Xero Accounting Software

Learn how to create effective budgets using Xero using 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.
Screenshot of first page of the table sales budget
Screenshot of the second page from the table sales budget

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 units
Note 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

The direct materials budget is made up of five components:
  1. Quantity of Material Needed for Production = (Units to be Produced)(Quantity of Material Budgeted per Unit)
  2. Quantity of Material to be Purchased = Quantity of Material Needed for Production + Desired Ending Material – Beginning Material
    The 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.
  3. 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.
  4. Cost of Material Used = (Quantity needed for Production)(Budgeted Material Prices)
  5. Cash Payments for Direct Material Purchases = Current Period Purchases Paid in Current Period + Prior Period Purchases Paid in Current Period
We get the information we need to determine budgeted cash payments from accounting, (accounts payable). This is usually based on past experience.
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
  1. 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 PBS
    Note: 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
  2. Budgeted cost of direct material purchases for March (22,310)($10) = $223,100
  3. Budgeted cost of direct material used for March = (22,100)($10) = $221,000
  4. Cash payments for direct material = purchases in this example = (see point 3).

Direct labour budget

  1. 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.
  2. 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
  1. Direct labor needed for production for March = (11,050 units to be produced)(.4 hrs) = 4,420 D.L. hrs
  2. Budgeted cost of direct labor used for March. = (4,420 D.L hrs needed)($15 per hr) = $66,300

Budgeted overhead expense

  1. Budgeted Factory Overhead Costs = Budgeted Fixed Overhead + (Budgeted Variable Overhead Rate)(D.L. Hours needed for Production from Direct Labour budget point 1)
  2. Cash Payments for Overhead = Budgeted Factory Overhead Cost – Depreciation and other costs that do not require cash payments
The alternative calculation for factory overhead costs is:
  • 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
  1. 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 cost
    Graphically 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.
  2. Cash Payments = $372,600 – 100,000 = $272,600

Inventory budget

You need the dollar amount for the ending inventory of finished goods to determine the cost of goods sold. You need the dollar amounts for ending direct materials and finished goods for the balance sheet.
  1. Ending direct materials = (desired ending materials) (budgeted prices)
  2. 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.
  3. Ending finished goods = (desired ending finished goods)(budgeted unit cost)
Ending Inventory Budget for March
  1. Ending Direct materials = (Desired Ending materials from 3b)(Budgeted Prices) = (.10)(24,200)($10) = (2,420 sheets of particle board)($10) = $24,200
  2. 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
  3. 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)

You need the cost of goods sold for the income statement. One method of determining budgeted COGS involves accumulating the amounts from the previous sub-budgets as described below.
  1. Budgeted total manufacturing cost = cost of direct material used + cost of direct labour used + total factory overhead costs
  2. 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

  1. Budgeted selling and administrative expenses = budgeted fixed selling and administrative expenses + (budgeted variable rate as a proportion of sales $)(budgeted sales $)
  2. 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

To prepare the budgeted income statement, you combine the relevant amounts from the sales, cost of goods sold, and selling and administrative expense budgets, and then subtract interest, bad debts, and income taxes to obtain budgeted net income.
  1. Budgeted sales $ – budgeted cost of goods sold = budgeted gross profit
  2. Budgeted gross profit – budgeted selling and administrative expenses = operating income
  3. Operating income – interest expense – bad debts expense = net income before taxes
  4. 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

  1. Budgeted cash available = beginning cash balance + budgeted cash collections
  2. Budgeted cash excess or deficiency = budgeted cash available – budgeted cash payments
  3. 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

Screenshot of budgeted balance sheet
You should now understand the types of budgets, approaches to budgeting, and the steps for creating effective budgets.

References:

  1. 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
  2. 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
This article is from the free online

Financial Analysis for Business Performance: Planning, Budgeting, and Forecasting

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education

close