Question 1

Manufacturing Statement and Profit and Loss

Your worksheet must have a data input section and a result/report section.

Jupiter Cereal Manufacturing Co is a wholly owned subsidiary of Jupiter Australasia Ltd. Jupiter manufacture a range of breakfast cereals for the Australian and export markets. The company utilises a traditional manufacturing cost flow inventory and accounting system.

Trading data for Jupiter Cereal Manufacturing Co for the 2020 financial year was as follows:

Account:	$
Purchases of Raw Materials & Ingredients	9,726,000
Direct Labour	2,543,000
Indirect Labour (including salaried supervisors)	1,250,000
Direct Manufacturing Overhead (including depreciation)	2,924,500
Other Manufacturing Overhead	1,158,500
Factory heat, light and power	1,265,500
Administration Salaries and Costs	975,500
Freight Inwards	726,500
Freight Outwards	834,500
Sales Revenue	32,344,500
Accounting & Audit costs	502,000
Interest & other charges	800,500
Sales & Marketing Expenses	1,492,500

On June 30^th 2020 selected account balances of Jupiter Cereal Manufacturing Co were as follows:

Account:	June 30 2020	June 30 2019
Cash & Receivables	3,632,500	5,976,000
Plant & Equipment (at cost)	4,550,000	5,865,000
Land & Buildings (at cost)	9,500,000	8,500,000
Accounts Payable	1,947,500	1,113,000
Raw Material Inventory	587,500	426,000
Finished Goods Inventory	651,000	235,000
Work in Process (WIP):
Raw Materials	292,000	202,500
Direct Labour	24,500	16,000
Manufacturing Overhead	227,500	155,500
Total WIP	544,000	374,000

Jupiter Cereal Manufacturing Co is incorporated and operates in Australia and pays tax at the Australian corporate rate of 30%. There are no adjustments for accruals or prepayments required.

Required: Using Excel prepare a Schedule of Cost of Goods Manufactured, Schedule of Cost of Goods Sold, and an income statement for the Jupiter Cereal Manufacturing Co from the information provided. (20 marks)

Important Note: Your Excel model should include a data input section and appropriate formulae. WIP calculations should be presented as disaggregated amounts within each of the three cost categories: Materials, Labour and Overhead. Marks will be awarded on the basis of the quantitative accuracy of the report and also the spreadsheet design and appropriate use of Excel formulae.