PREPARE BUDGETS

Assessment Submission details:

Please include following details on the top of your assessment:

Your Name

Your Student Id

Your Trainer’s name

Assessment Due Date

Actual Submission Date

Please Note: Any changes in the assessment due date must be approved by your trainer.

This assessment must be in Microsoft word format. Following settings should be made for this assignment to keep consistency among all the assessments:

Body text

Page setup

Font: Times New Roman

Font size: 12 point

Line spacing: Double 

Text style: Normal

Top: 2.54 cm 

Bottom: 2.54 cm

Left: 3.17 cm

Right: 3.17 cm

Header: 1.25 cm

Footer: 1.25 cm

Do not forget to attach the Cover Sheet at the front of the assessment.

Make sure you have signed the Cover sheet to declare this is your own work.

You can e-mail this assessment to your trainer’s e-mail address with following details: In ‘subject’ mention your ‘student Id – Your name’.

Achieving Competence:

To be deemed competent in this assessment you must:

Correctly address all the assessment requirements as described in this task

Correctly address all the submission instructions

Successfully complete the Assessment Questions

Submit assessment on or before the due date with an assessment cover sheet


Performance objective:

In this task, you are required to respond to a range of prompt questions that examine your understanding of key legislative and financial managementrequirements for a case study organisation.This assessment also requires you to review available financial information and establish a budget for the organisation.

Assessment description:

This assessment requires you to determine the requirements to undertake budgeting, financial forecasting and reporting requirements for an organisation. You will also need to review the case study provided and prepare a budget (in electronic spreadsheet format) and budget notes for distribution and implementation in the organisation.

Assessment Questions:

Read the case study.

Analyse the case study information and respond to the questions in the space provided.

Analyse the case study information (including business plan summary and previous financial data) and complete the following.

Develop a sales budget, profit budget, cash flow budget and debtor ageing summaryusing electronic spreadsheets (as separate worksheets) making sure each budget is divided into quarterly periods and that you use previous financial data to determine allocations for resources.

Ensure each budget you prepare complies with the organisational and policies and procedures as provided.

Develop budget notes which include:

identification of reasons for previous profits and losses

your comment on the effectiveness of existing financial management approaches

all assumptions and basis that have been made or used to form budgets

any relevant notes regarding implementation and monitoring of budget expenditure.

Communicate information regarding the budget and answer a series of prompt questions in written or oral form as agreed with your assessor.

Specifications

You must provide:

a completed annual budget in a single spread sheet with a separate sheet for each budget component

budget notes and question answers in a written format.

Your assessor will be looking for:

evidence that you understand the required legislative requirements

evidence that you can identify suitable software for financial management

evidence you have reviewed the case study information provided by submitting an appropriate budget with budget notes

evidence that you have clearly communicated information regarding the budget and correctly responded to a series of questions.

Case study

You have recently been appointed as the business manager of Houzit Pty Ltd having been a store manager for the past three years. Houzit Pty Ltd is a 15 store retail chain located in Brisbane. Houzit is the leading homewares retailer, catering to the growing need for furnishing new and renovated dwellings in the greater Brisbane area.

The assortment on offer of bathroom fittings, bedroom fittings, mirrors and decorative items together with the recently added lighting fixtures has positioned Houzit as a leader in homewares retailing in Australia. Houzit has grown over the past five years from a single store to the current chain. Houzit prides itself on superior after sales service which has been a key reason for the continued growth in sales and corresponding profit increases. Today Houzit employs over 150 staff.

Houzit Pty Ltd is a proprietary limited company (ACN 34 765 234 02) registered with the Australian Securities and Investment Commission. The registered address is with Houzit’s solicitors (Langs Lawyers, 535 Queen Street, Brisbane, QLD 4000) and the principle place of business is 505 Boundary Street Spring Hill Brisbane QLD 4000.

Computer software requirement

The current accounting information system has not adequately provided sufficient analysis of revenue and expenditure and has made it difficult to make informed estimates of future profits. Estimates have relied on the ‘gut feel’ of the experienced traders on the board and of the senior managers. The board sees the need to apply more analysis to past results that they believe could be done with the introduction of state-of-the-art computer software.

Houzit Pty Ltd wants to upgrade their existing accounting systemwhich will manage the company accounts more efficiently in the long run. They request that the new system you recommend to them to be compliant with all legislative and statutory requirements for small to medium businesses.

None of Houzit’s products are GST free however the accounting information system records the GST collected as well as the input tax credits earned on the purchases of stock and assets. These amounts are reported and paid in accordance with the business activity statement (BAS) schedule determined by the Australian Tax Office.

They have 100 fulltime and 50 part-time staff, but only 10 of the staff will have or need access to the financial system.Some staff are paid on a salary sacrifice arrangement that attracts fringe benefits tax.The staff with access to the financial system want software that is a single purchase with no ongoing license fees, and a plan to keep using if for the next 3–5 years, while the organisation continues to grow.They are anticipating that within five years they will have over 250 full-time staff, and at least 20 staff will require access to the financial system by then.

The payroll system deducts withholding tax from the employees and remits this along with the firm’s pay as you go (PAYG) instalment each quarter as reported on the firm’s business activity statement. Income tax return for the company and its annual statement is completed by the firm’s accountant. Taxes and fees due are paid by the due dates. Financial records are kept at Houzit’s principle place of business.

Houzit have just upgraded their computers and have five new desktop PCs which will be used by the finance staff.They are current (for 2011) specification machines with i5 CPUs and 4Gb RAM each, and all have Windows 7 Professional and Norton’s 360 installed with the professional version of Microsoft Office Small Business as well.Other staff willuse their machines at various times, so it is important that the software requires a login to access data and that data stored by the software cannot be accessed in any other way.

Corporate details

Jim Schneider, the CEO, has asked you to prepare some financial budgets for the 2011/12 financial year as a preliminary overview of the financial year ahead.He asked you to first prepare a 12 months budget and then break it up over the four quarters. The areas he is particularly interested in seeing is:

Sales budget for 2011/12 by department by quarter.

Profit budget (including detailed expenses) for 2011/12 by quarter.

The cash flow result per quarter of the GST after adjusting the GST collected by the allowable GST tax credits.

The anticipated aged debtor’s summary at the end of each quarter.

The CEO wants to be given all the budgets except for the aged debtors budget which the accountant and accounts receivable clerk can monitor. The CEO produced a summary of the current business plan that covered the budget year to highlight some of the key goals, objectives and strategies he would like incorporated into the budget.

After going through the business plan summary, the CEO gave you the previous year’s financial reports and asked you to speak with the accountant Celina Patel to get some of the figures and detailed expectations for the coming year.

You arrange a meeting with Celina Patel, Houzit’s accountant, and she gives you the following insight into the historical expense relationships and the current statutory compliance liabilities.

Sales and profit budget information

Celina explained that the only budget she monitors on a day-to-day basis is the cash flow budget and the store manager is primarily responsible for the sales budget.

These are the notes you take at the meeting:

The overall sales for 2011/12 target set by the business plan should be apportioned across the quarters in the same % as was achieved in 2010/11.
This was:

Qtr 1

Qtr 2

Qtr 3

Qtr 4

2010/11

3,142,822

3,771,386

4,085,668

4,714,232

15,714,108

Cost of goods sold is the inverse of the gross profit rate determined by the business plan and is determined by the quarterly sales budget.

Accounting fees have been negotiated for the year at a fixed amount of $10,000 to be paid in equal amounts each quarter.

The interest charges on the bank loan are anticipated at a reduced amount of $84,508 due to an agreed repayment of some of the loan principal. This is to be paid in equal amounts each quarter.

Bank charges are expected to be the same as 2011and paid in equal amounts each quarter.

Celina has requested that a new expense (store supplies) be recognised in the new budget that was previously included in with the cleaning expense amounts. Store supplies in the 2009/10 results was $3,500 of the cleaning expense and $3,605 of the 2010/11 result. Cleaning expense will then be lower but identify the real labour costs involved in the cleaning expense.

Depreciation is expected to be the same as 2011and allocated in equal amounts each quarter.

Advertising is to be apportioned to each quarter based on the business plan.

The following expenses are expected to increase by the determined inflation rate in the business plan summary:

Insurance – apportioned in equal amounts each quarter.

Store supplies – is calculated for to each quarter using the same % as determined by the sales for each quarter.

Cleaning – is calculated for each quarter using the same % as determined by the sales for each quarter.

Repairs and maintenance – apportioned in equal amounts each quarter.

Rent – apportioned in equal amounts each quarter.

Telephone – is calculated for to each quarter using the same % as determined by the sales for each quarter.

Electricity – is calculated for to each quarter using the same % as determined by the sales for each quarter.

Fringe benefits tax is expected to be the same as 2011and paid in equal amounts each quarter.

Wages and salaries are calculated for each quarter using the same % as determined by the sales for each quarter.

The statutory requirements are:

superannuation is 9% of wages and salaries for each quarter

payroll tax is 4.75% of wages and salaries for each quarter

workers compensation is 2% of wages and salaries for each quarter

company tax is 30% of net profit before tax for each quarter.

Houzit Pty Ltd

For 12 months ended

Profit & Loss Actuals

2007/08

2008/09

2009/10

2010/11

Revenue

Sales

12,474,336

13,472,315

14,550,100

15,714,108

Cost Of Goods Sold

6,860,901

7,409,773

8,002,555

8,799,900

Gross Profit

5,613,465

6,062,542

6,547,545

6,914,208

Expenses

Accounting Fees

5,500

6,500

8,500

9,000

Interest Expense

45,000

65,000

96,508

90,508

Bank Charges

1,200

1,300

1,580

1,600

Depreciation

170,000

170,000

170,000

170,000

Insurance

12,500

12,500

12,500

12,875

Store Supplies

Advertising

50,000

100,000

280,000

280,000

Cleaning

12,560

15,652

18,700

19,261

Repairs & Maintenance

40,250

52,600

60,000

61,800

Rent

2,465,000

2,465,000

2,465,000

2,538,950

Telephone

9,862

12,523

14,000

14,420

Electricity Expense

22,500

23,658

25,000

25,750

Luxury Car Tax

12,400

Fringe Benefits Tax

26,000

26,000

26,000

28,000

Superannuation

148,500

160,737

166,500

171,495

Wages & Salaries

1,649,998

1,785,965

1,850,000

1,905,500

Payroll Tax

78,375

84,833

87,875

90,511

Workers’ Compensation

33,000

35,719

37,000

38,110

Total Expenses

4,770,245

5,017,987

5,331,563

5,457,780

Net Profit (Before Tax)

843,220

1,044,554

1,215,982

1,456,428

Income Tax

252,966

313,366

364,795

436,928

Net Profit

590,254

731,188

851,188

1,019,499

Houzit Pty Ltd

Statement of Financial Position

As at 30 June

2009/10

2010/11

Assets

Current Assets

Cash On Hand

50,000

55,000

Cheque Account

144,842

160,314

Deposits Paid

950,000

950,000

Trade Debtors

850,000

975,000

Merchandise Inventory

1,530,000

1,430,000

Total Current Assets

Fixed Assets

Motor Vehicles At Cost

500,000

500,000

Motor Vehicles AccumDep

( 100,000 )

( 125,000 )

Furniture & Fixtures At Cost

1,950,000

2,250,000

Furniture & Fixtures AccumDep

( 650,000 )

( 770,000 )

Office Equip At Cost

400,000

400,000

Office Equip AccumDep

( 90,000 )

( 115,000 )

Total Fixed Assets

2,010,000

2,140,000

Total Assets

5,534,842

5,710,314

Liabilities

Current Liabilities

MasterCard

17,800

14,860

Trade Creditors

780,000

679,000

GST Collected

1,455,010

1,571,411

GST Paid

( 943,125 )

( 987,626 )

Superannuation Payable

100,000

120,000

Luxury Car Tax Payable

20,920

income Tax Payable

364,795

436,928

PAYG Withholding Payable

65,000

44,872

Total Current Liabilities

1,860,400

1,879,445

Long-Term Liabilities

Bank Loans

1,608,459

1,508,459

Total Liabilities

3,468,859

3,387,904

Equity

Owner/Shareholder’s Equity

500,000

500,000

Retained Earnings

850,000

1,565,982

Dividends Paid

( 500,000 )

( 1,200,000 )

Current Year Earnings

1,215,982

1,456,428

Total Equity

2,065,982

2,322,410

Internal auditor

Carl Kerns is one of the directors of the board. Carl said that as a board member they are given the profit and cash flow budgets. He was appointed by the board to conduct an internal audit of operations to look for weaknesses in the internal control system.His report uncovered the following processes that he believed needed to be strengthened.

While the overall customer base is increasing from year to year, there may be internal control issues relating to how these new customers are secured.

Some discounts that were being given to customers were recorded as a net amount on the invoices and gave no indication of the discount from standard prices.

Some cash registers in the stores were not reconciling the cash in drawer with the register printout.

Not all timesheet overtime amounts were being authorised by the line manager.

Service invoices for some items of equipment were not signed or linked to apurchase order. There was no check that the work had actually been carried out.

Not all assets in the stores had unique codes fixed to the asset.

There were minimal feedback lines of communication from the shop floor to head office, particularly when an error in the budgeting report process was recognised.

Debtor reconciliations were not done monthly and sometimes not at all.

In busy times the cashiers that operated the registers were also asked to do their own reconciliations and banking. Sometimes the cash was held in the store for a day or two.

Job roles were not clearly defined so that responsibilities and liability can be identified.

There was little rostering of duties and cash receipts were not pre-numbered.

Of particular concern to Carl was the directive given by the board to ensure that audit trails were created and maintained. These included:

Signing the timesheets for employees under the authority of a department manager.

Maintenance of a numbered cash receipts book.

Using sequenced cheques as a systematic way of evidencing all monies paid out.

Ensuring proper coding of evidenced transactions against appropriate general ledger account and cost centre.

Ensuring reconciliations between company books and third party bank statements are performed.

GST cash flow budget

Statutory requirements for GST is 10% of the recorded amounts in sales. The only capital purchase planned for the year is the luxury car for the chairman. Those expense payments on which 10% GST was paid include the following:

Cost of goods sold:

accounting fees

insurance

store supplies

advertising

cleaning

repairs and maintenance

rent

telephone

electricityexpense.

The GST amount payable each quarter is the difference between the GST collected from sales and the GST paid – format as per policy and procedures.

CASH FLOW ANALYSIS – GST

2011/12

Qtr 1

Qtr 2

Qtr 3

Qtr 4

GST Collected

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Less GST Paid

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

GST Payable

Calculation

Calculation

Calculation

Calculation

Calculation

Debtors ageing budget

The historical records show that the debtors balance at the end of each quarter is usually about 20% of the quarter’s sales. At any time in the debtor’s balances 1% of the total debtors is overdue 90 days and over, 5% is 60 days overdue, 10% is 30 days overdue and the balance of the total debtors is current. The aged debtors’ budgets are only distributed to the accountant and the accounts receivable clerk.

Houzit Budgeting Policy and Procedures

Budget development process

The standard process for developing budgets will follow the following steps:

Establish the budget objective.

Gather prior period data.

Discuss prior period information and anticipated changes in the budget period with stakeholders.

Research relevant external information.

Incorporate identified trends to determine assumptions and parameters.

Prepare budgets in standard formats.

Submit budgets for approval.

Budget objectives

Houzit prepares budgets to meet various company objectives. Budgets are prepared:

for a specific expansion of the business activities:

business case to be prepared covering a cost-benefit analysis, market research report and summary profit and investment expectations

to outline a specific debt reduction initiative:

company-wide summary of profit expectations, planned debt and equity funding arrangements, CAPEX plans summarised

annually to cover the next financial year:

for the 12 month period from the beginning to the end of the financial year

budget to include four quarter milestones in line with seasonal trends identified from prior year data

initial preparation includes a preliminary overview of the financial year ahead

sales budget for next year to be prepared by department by quarter

profit budget (including detailed expenses) for the next year to be prepared by quarter

cash flow effect of the GST payable per quarter to be prepared (scheduled compliance payment date is the 21st day after the end of the quarter)

To satisfy the statutory requirements relating to the current and short-term solvency of the company:

three monthly rolling forecast of cash flows to be prepared

To qualify the strategic plans for the next 3–5 years planning cycle:

profit and CAPEX budget to be prepared.

Budget variances and schedules

Key performance indicators that should be closely monitored and reported on include variances to:

total sales

gross profit (GP) %

wages andsalaries as a % of total sales

total expenses as a % of total sales

net profit in dollars

net profit as a percentage.

Budget variances will be reported using the standard format provided in this policy and procedures document.

Budget variances must be completed within five working days of quarter end.

Actual results for the month will be provided by the accounting information system.

An analysis of the variance between the actual and the budget must include $ and % variance.

Report with explanations and recommendations to be complete within seven working days of quarter end and be given to the CEO.

Analysis and investigation of variances will include the following priority:

Establish the primary causes for variances to key performance indicators of total sales, gross profit % and net profit $.

Establish reasons for those individual items in the variance report that represent the greatest $ variance.

Establish reasons for those individual items in the variance report that represent the greatest % variance.

Schedules relating to compliance due dates must be prepared and monitored by the accountant. Managers supplying information to the accountant regarding the compliance schedule must submit it at least five working days prior to the due date deadline.

Standard formats

The following formats will be used when preparing Houzit budgets and variance reports.

Sales and profit budgets

PROFIT BUDGET

2011/12

Qtr 1

Qtr 2

Qtr 3

Qtr 4

Revenue

%

%

%

%

Sales

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Cost of Goods Sold

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Gross Profit

Calculation

Calculation

Calculation

Calculation

Calculation

Gross Profit %

Calculation

Calculation

Calculation

Calculation

Calculation

Expenses

Accounting Fees

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Interest Expense

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Bank Charges

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Depreciation

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Insurance

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Store Supplies

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Advertising

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Cleaning

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Repairs & Maintenance

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Rent

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Telephone

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Electricity Expense

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Luxury Car Tax

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Fringe Benefits Tax

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Superannuation

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Wages & Salaries

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Payroll Tax

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Workers’ Compensation

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Total Expenses

Calculation

Calculation

Calculation

Calculation

Calculation

Net Profit (Before Tax)

Calculation

Calculation

Calculation

Calculation

Calculation

Income Tax

Calculation

Calculation

Calculation

Calculation

Calculation

Net Profit

Calculation

Calculation

Calculation

Calculation

Calculation

GST Cash flow budget

CASH FLOW ANALYSIS – GST

2011/12

Qtr 1

Qtr 2

Qtr 3

Qtr 4

GST Collected

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

Less GST Paid

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

GST Payable

Calculation

Calculation

Calculation

Calculation

Calculation

Aged debtors

AGED DEBTORS BUDGET

TOTAL

Qtr 1

Qtr 2

Qtr 3

Qtr 4

Sales

x,xxx

x,xxx

x,xxx

x,xxx

x,xxx

% Debtors Sales

%

%

%

%

Total Debtors

%

Calculation

Calculation

Calculation

Calculation

Current

%

Calculation

Calculation

Calculation

Calculation

30 Days

%

Calculation

Calculation

Calculation

Calculation

60 Days

%

Calculation

Calculation

Calculation

Calculation

90 Days

%

Calculation

Calculation

Calculation

Calculation

Scenario

The CEO of Houzit Pty Ltd, Jim Schnieder explained that he prefers to discuss the budgets with all senior managers prior to their distribution in order to ensure a corporate view of the strategic plans. He then meets with each group separately to answer questions and concerns about their particular area. Eventually the budgets will be printed in hard copy and bound as well distributed as an electronic spreadsheet.

Upon completion of the budgets you meet with Jim to provide an overview of the information contained within the budgets, the budget notes and recommendations regarding the internal controls to prepare him for the meetings with the senior managers.To clarify his understanding of the information, Jim asks you a series of questions (listed below,which you will completewritten or orally as agreed with your assessor).

Prompt questions

Based on the information provided in the case study answer the following questions in the space provided below:

Identify the current statutory requirements for tax compliance and list and calculate the tax liabilities for Houzit Pty Ltd under taxation legislation.

Identify the current compliance requirements and liabilities for this organisation under the Corporations Act 2001.

Review commercially available financial management software to select the most suitable software for Houzit Pty Ltd.

Ensure you diagnose software options by comparing two commercially available software titles against the capabilities of the existing technology for the organisation and against the prioritised requirements, and outline the reasons that lead you to this recommendation.

Explain how you can apply the following principles of accounting in developing the budgets required for this task:

matching principle

account groups

time periods.

Explain and discuss the implications of probity when preparing and revising budgets.

List the critical dates and initiatives that will require or generate resources for Houzit Pty Ltd in the next financial cycle.

6.List the items you would recommend for inclusion in the budgets for Houzit Pty Ltd.

7.List the new or modified internal controls that could improve risk management for Houzit Pty Ltd including the maintenance of audit trails.