You have recently accepted the role of management accounting team leader at a large Australian manufacturing firm, High Power International. High Power International specializes in the production of electric motor vehicles. The firm’s 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

Manufacturing Cost Schedule and Income Statement

High Power International’s utility vehicle line is produced at the firm’s Melbourne pant. This plant uses a traditional manufacturing cost flow accounting system. You have been tasked with the job of finalizing 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 utility 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 utility 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 firm’s 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.