POI Pty. Ltd. purchases comic books from publishers and resells them to customers. The company has a perpetual inventory system and assigns the cost of goods sold on a FIFO basis.
The company has the following records for the financial year 2016/17:
All figures exclude GST.
 

Inventory on hand 1 July 2016	1200 units @ $20 per unit
Purchases made during the financial year:
10/7/16	600 units @ $15 per unit
12/9/16	200 units @ $23 per unit
22/12/16	1400 units @ $16 per unit
3/2/17	400 units @ $14 per unit
5/5/17	100 units @ $22 per unit
Sales made during the financial year:
15/7/16	1000 units sold
20/10/16	900 units sold
1/1/17	1400 units sold
3/4/17	200 units sold
Stocktake on 30 June 2017	20 units damages and written off

Required:
Read the pdf document titled "Hints when using Excel" and use the Excel spreadsheet titled "POI Pty Ltd Stock Ledger Card Template" (both of which can be found in the LMS), to calculate the following:
You are required to upload your completed Excel Spreadsheet titled "POI Pty Ltd Stock Ledger Card Template" when you upload this assignment.
 

3a. Calculate: (refer to your completed Excel spreadsheet).

Cost of Goods Sold using perpetual FIFO method	$
Closing inventory using perpetual FIFO method	$

3b. Calculate: (refer to your completed Excel spreadsheet).

Average inventory (simplified) = (Opening inventory + Closing inventory) / 2  (show your workings):

= 

3c. Calculate: the inventory turnover ratio.

Inventory Turnover Ratio = COGS / Average Inventory (show your workings):

= 

 

3d. Set up a new Microsoft Excel (or equivalent spreadsheet program) file titled "Inventory Calculation Schedule" 

Include a Title for your spreadsheet, and headings Month, Opening Inventory, Closing Inventory, Cost of Goods Sold, and Turnover Ratio across the top of your spreadsheet.

Enter the closing inventory and cost of goods sold data into your spreadsheet from the following table. The opening inventory for July is $24000.

Complete the table by entering the formulae for the calculation of average inventory (0 decimal places) and inventory turnover rate (2 decimal places).

 

Month	Closing Inventory	Cost of Goods Sold
Jul	6120	61680
Aug	8835	55670
Sept	16500	51270
Oct	12300	45620
Nov	7250	38974
Dec	8420	52378
Jan	10670	57683
Feb	11432	55234
Mar	9982	53489
Apr	8269	58925
May	9346	60334
Jun	10457	61259

 

Q3e The following data is provided in respect of the inventory of ABC Co at 30 June 

ABC Co - Inventory Schedule 30 June xx
Item	At Cost	At NRV	Inventory Value
001	$2,000	$2,200
002	8,800	7,900
003	1,000	1,200
004	2,000	3,000
005	6,600	5,000
006	9.500	10,000

 

Applying the lower of cost and net realisable value rule, determine the value of inventory for ABC Co for year ended 30 June