Question

210 Excel Inventory Valuation Spreadsheet Fall 19 [Protected View] Microsoft Excel X File Page Layout Formulas Data Review View Home Insert iProtected View This file originated from an Internet location and might be unsafe. Click for more details Enable Editing fr A8 В C D Е F G J K м O р 1 2 Excel Project Pt2: Inventory Valuation Methods The assumptions are provided at the top of the Calculations worksheet. In making the calculations in each step link to the assumptions. All 3 calculations should be make using formulas in the cells. Part 1. Using the information provided on the Calculations tab caloculate the cost of goods and ending inventory using each of the following methods FIFO, LIFO, and weighted average. The calculations should be in the same format that I use in my video sample problem. Provide 4 proper headings and format the numbers properly. Part 2. To check your work you need to make a table calculating the cost of goods sold based on the beginning inventory, purchases, and ending inventory. You need to make these calculations based on your calculations in step 1. Use the formula provided in your text book on 5 page 201. You should subtotal to get the goods available for sale in your tabular form. 6 Part 3. Based on your calculations in part 2 determine the impact on cost of goods sold if the inventory is understated. 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 14 HDirections Calculations 100% O Ready 10:34 AM h C 10/29/2019

Answer 1

Part 1 & Part 2:

Below is the table showing Cost of Goods Sold Calculation and Ending Inventory calculation based on LIFO, FIFO and Weighted Average method:

							LIFO
Date	Activity	Units Acquired	Cost per unit	Total	Units Sold	Price per unit	Units	Price Per unit	Total	Cumulative unit	Cumulative Total
1-Jan	Opening Inventory	300	50	15,000			300	50	15,000	300	15,000
6-Jan	Purchase	200	51	10,200			200	51	10,200	500	25,200
10-Jan	Purchase	300	52	15,600			300	52	15,600	800	40,800
14-Jan	Sales				400	80	-300	52	(15,600)	500	25,200
							-100	51	(5,100)	400	20,100
16-Jan	Purchase	500	52	26,000			500	52	26,000	900	46,100
20-Jan	Purchase	400	53	21,200			400	53	21,200	1300	67,300
31-Jan	Sales				300	80	-300	53	(15,900)	1000	51,400
	Total (A)			88,000						Ending Inventory (B)	51,400
										Cost of Goods Sold (A)-(B)	36,600

							FIFO
Date	Activity	Units Acquired	Cost per unit	Total	Units Sold	Price per unit	Units	Price Per unit	Total	Cumulative unit	Cumulative Total
1-Jan	Opening Inventory	300	50	15,000			300	50	15,000	300	15,000
6-Jan	Purchase	200	51	10,200			200	51	10,200	500	25,200
10-Jan	Purchase	300	52	15,600			300	52	15,600	800	40,800
14-Jan	Sales				400	80	-300	50	(15,000)	500	25,800
							-100	51	(5,100)	400	20,700
16-Jan	Purchase	500	52	26,000			500	52	26,000	900	46,700
20-Jan	Purchase	400	53	21,200			400	53	21,200	1300	67,900
31-Jan	Sales				300	80	-100	51	(5,100)	1200	62,800
							-200	52	(10,400)	1000	52,400
	Total (A)			88,000						Ending Inventory (B)	52,400
										Cost of Goods Sold (A)-(B)	35,600

Weighted Average Cost Method
Date	Activity	Units Acquired	Cost per unit	Total	Units Sold	Price per unit
1-Jan	Opening Inventory	300	50	15,000
6-Jan	Purchase	200	51	10,200
10-Jan	Purchase	300	52	15,600
14-Jan	Sales				400	80
16-Jan	Purchase	500	52	26,000
20-Jan	Purchase	400	53	21,200
31-Jan	Sales				300	80
	Total (A)	1,700	51.76	88,000	700
	Units Sold ( C)	(700)
	Ending Inventory (B) = (A)-( C)	1,000	51.76	51,760
	Cost of Goods Sold (A)-(B)			36,240

Part 3:

If Inventory is understated, it means Cost of Goods Sold has been overstated and accordingly Gross profit becomes understated.