You have recently accepted the role of management accounting team leader at a large Australian manufacturing firm, High Power International. High Power International specialises in the production of electric motor vehicles. The firms product range includes SUVs, utilities, sedans and sports cars. The firm has a number of production plants across Australia and exports products across the world. Whilst production is based in Australia, the firm is dependent on a number of international suppliers for some components. To ensure that the firm has remained innovative and cost-competitive, the firm has had to make significant investments in product and production innovations.

Task

Question 1 (10 Marks)

Manufacturing Cost Schedule and Income Statement

High Power Internationals utility vehicle line is produced at the firms Melbourne pant. This plant uses a traditional manufacturing cost flow accounting system. You have been tasked with the job of finalising the accounts for the utility vehicle line for the year ended 30 June 2021. The following account details have been provided:

Revenue	$234,500,000
Interest earned	$2,458,000
Other income	$58,000
Employee benefits	$39,700,000
Premise, lease and utilities expenses	$26,540,000
Advertising and marketing expenses	$15,400,000
Goodwill and other intangibles amortisation and impairment	$5,450,000
Finance costs	$2,760,000
Depreciation of manufacturing plant and equipment	$18,500,000
Manufacturing equipment maintenance	$875,000
Other indirect factory costs	$2,300,000
Product research and development costs	$10,350,000
Legal and accounting fees	$578,000
Costs of delivering to customers	$7,900,000
Income tax expense	$19,500,000
Raw material purchases	$37,450,000

The employee benefits and premise, lease and utilities expenses have been allocated between office and administration and production activities:

	Office and administration	Production
		Process workers	Factory supervisors	Other support staff
Employee benefits	7%	85%	6%	2%
Premise, lease and utilities expenses	13%	87%

The opening and closing inventory balances for the split system air conditioning line were as follows:

Inventory Account	1 July 2020	30 June 2021
Work-in-process
Materials	$1,085,711	$1,354,862
Direct labour	$1,259,852	$1,525,358
Manufacturing overhead	$1,366,575	$1,630,780
Raw material inventory	$3,199,087	$3,417,739
Finished goods inventory	$2,563,270	$2,639,990

Required:

Using Excel, from the information provided prepare the following financial reports for the year ended 30 June 2021:

• Schedule of Cost of Goods Manufactured,
• Schedule of Cost of Goods Sold, and
• after tax income statement for utility vehicle line.

Your financial reports must be accompanied by a covering letter (single page) which provides an overview of the nature of the reports and the key highlights (relevant comparative charts/figures developed through Excel should be included) regarding the firms performance.

This question has been designed to develop and test your capacity to design and work with spreadsheet applications which are a critical tool in all forms of accounting practice, but particularly management accounting. Marks are awarded for the simplicity and effectiveness of spreadsheet design as well as the accuracy of your answer. Your worksheet must have a data input section and a result/report section.