Question 1 (10 Marks) Manufacturing Cost Schedule and Income Statement

Whisk Brothers' oak barrel production plant is based in Hobart. This plant uses a traditional manufacturing cost flow accounting system. You have been tasked with the job of finalising the accounts for the production plant for the year ended 30 June 2022. The following account details have been provided:

Revenue	$6,526,000
Other income	$60,240
Employee benefits	$803,200
Distribution expense (freight out)	$130,520
Selling expenses	$602,400
Marketing expenses	$351,400
General and administration expenses	$301,200
Share based payments	$200,800
Depreciation and amortisation	$702,800
Finance costs	$251,000
Premise, lease and utilities expenses	$301,200
Production equipment maintenance	$80,320
Income tax expense	$602,400
Raw material purchases	$1,204,800

The employee benefits, depreciation and amortisation, and premise, lease and utilities expenses have been allocated as follows:

	Administration and sales	Production
Process workers	Factory supervisors	Other support staff
Employee benefits	5%	80%	10%	5%
Depreciation and amortisation	8%	92%
Premise, lease and utilities expenses	14%	86%

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

Inventory Account	1 July 2021	30 June 2022
Work-in-process
Materials	$33,000	$41,000
Direct labour	$38,000	$46,000
Manufacturing overhead	$41,000	$48,000
Raw material inventory	$96,000	$102,000
Finished goods inventory	$76,000	$79,000

Required:

Using Excel, prepare the following financial reports for the year ended 30 June 2022 for the oak barrel production plant:

• 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 department'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 worksheetmusthave a data input section and a result/report section.