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]

• Using the Budget Manager in Xero

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.

* 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.

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

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

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

• 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

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)

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

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

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