Question

1. Child’s Play provides snack packs to the children who participate in programming. The organization started the current fiscal year with 175 snack packs, which had been purchased in the previous fiscal year for $1.50 each. Three months into the current fiscal year, all 175 snack packs had been used, so the organization purchased 200 more snack packs at a price of $1.75 each. Six months later, there were only 45 snack packs left, so the organization paid $450 for 225 additional snack packs. On the last day of the current fiscal year, there were 40 snack packs remaining. Calculate each of the following in Excel, showing both your solutions and your formulas.

a) Calculate the supplies expense using LIFO.

b) Calculate the supplies expense using FIFO.

c) Calculate the inventory balance using LIFO.

d) Calculate the inventory balance using FIFO.

Answer 1

a.

month	no of snack purchased	price	no of snack pack used	cost per snack pack	expense
0	175	$1.50	175.00	1.5	$262.50
3	200	$1.75	155.00	$1.75	$271.25
9	225	2	230.00		$458.75
12					$992.50

expense = number of packs used * cost per snack pack

as per data given, in the end we have only 40 snack pack left, so as per LIFO all snack pack purchased in 225 pack lot has been used and 5 pack of 200 snack pack lot is been used.

supplies expense using LIFO = $992.50

b.

month	no of snack purchased	price	no of snack pack used	cost per snack pack	expense
0	175	$1.50	175.00	1.5	$262.50
3	200	$1.75	155.00	$1.75	$271.25
9	225	2	230.00		$448.75
12					$982.50

expense = number of packs used * cost per snack pack

as per data given, in the end we have only 40 snack pack left, so as per FIFO all snack pack purchased in 45 pack previous lot has been used and 185 pack of last lot is been used.

supplies expense using FIFO = $982.50

C.

MONTH	USED	OPENING BALANCE	PRICE	PURCHASE	PRICE	CLOSING BALANCE	INVENTORY BALANCE
0		175	$1.50	0.00	0	$175.00	262.5
3	175	0	$0.00	200.00	$1.75	$200.00	350
9	155	45	1.75	225.00	2	$270.00	528.75
12	230	40	1.75	0	0	$40.00	$70.00

D.

MONTH	USED	OPENING BALANCE	PRICE	PURCHASE	PRICE	CLOSING BALANCE	INVENTORY BALANCE
0		175	$1.50	0.00	0	$175.00	262.5
3	175	0	$0.00	200.00	$1.75	$200.00	350
9	155	45	1.75	225.00	2	$270.00	528.75
12	230	40	2	0	0	$40.00	$80.00