Better Bottles, Inc. uses a periodic inventory system and has provided its current inventory information. The…
The following solution is suggested to handle the subject “Better Bottles, Inc. uses a periodic inventory system and has provided its current inventory information. The…“. Let’s keep an eye on the content below!
Question “Better Bottles, Inc. uses a periodic inventory system and has provided its current inventory information. The…”
Better Bottles, Inc. uses a periodic inventory system and has provided its current inventory information. The Controller has as you to prepare a comparison of the Ending Inventory and Cost of Goods Sold totals for three different inventory costing methods: Periodic FIFO, LIFO, and Weighted-Average. Use the information included in the Excel Simulation and the Excel functions described below to complete the task. • Cell Reference: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below, if in a blank cell, “=B5” was entered, the formula would output the result from cell B5, or 27 in this example. Basic Math functions: Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys: + (plus sign to add), – (minus sign to subtract), * (asterisk sign to multiply), and / (forward slash to divide). From the Excel Simulation below, if in a blank cell “=B4+B5” was entered, the formula would add the values from those cells and output the result, or 47 in this example. If using the other math symbols the result would output an appropriate answer for its function. SUM function: Allows you to refer to multiple cells and adds all the values. You can add individual cell references or ranges to utilize this function. From the Excel Simulation below, if in a blank cell “=SUM(C4,C5,C6)” was entered, the formula would output the result of adding those three separate cells, or $72.00 in this example. Similarly, if in a blank cell “=SUM(C4:06)” was entered, the formula would output the same result of adding those cells, except they are expressed as a range in the formula, and the result would be $72.00 in this example.
1 Better Bottles, Inc., uses a periodic inventory system and has the following information available: # of Units $ 27 Description 7 Beginning Inventory 5 Jan. 15 Purchase 5 Jan. 20 Purchase 7 Goods Available for Sale 3 Less: January Sales Ending Inventory Cost per Unit 20 $ 20.00 22.00 33 30.00 80 36 44 Total Cost 400.00 594.00 990.00 1,984.00 2 Required: 3 1) Calculate both the Ending Inventory and Cost of Goods Sold using Periodic FIFO. FIFO Ending Inventory # of Units Cost per Unit Total Cost Description 7 Jan. 20 Purchase 8 Jan. 15 Purchase
UN PL FIFO Cost of Goods Sold # of Units Cost per Unit Total Cost Description 3 Beginning Inventory 4 Jan. 15 Purchase UI 36 7 2) Calculate both the Ending Inventory and Cost of Goods Sold using Periodic LIFO. mm LIFO Ending Inventory # of Units Cost per Unit Total Cost Description 1 Beginning Inventory 2 Jan. 15 Purchase m 44 USUI LIFO Cost of Goods Sold # of Units Cost per Unit Total Cost Description 7 Jan. 20 Purchase 3 Jan. 15 Purchase 36
– 3) Using Periodic Weighted Average, first calculate the cost per unit using the formula below. – Next, apply that same cost per unit to calculate both the Ending Inventory and Cost of Goods Sold. Weighted Average Cost = Cost of Goods Available for Sale Number of Units Available for Sale per unit Weighted Average Ending Inventory # of Units Cost per Unit Total Cost Weighted Average Cost of Goods Sold # of Units Cost per Unit Total Cost
6 4) Use the given information and your calculated numbers to complete the Cost of Goods Sold 7 Equation below for all three inventory methods. (All numbers should be positive.) FIFO LIFO Wtd. Avg. 9 Beginning Inventory 0 Add: Purchases 1 Goods Available for Sale 2 Less: Ending Inventory 3 Cost of Goods Sold sw N
Answer
Excel formula
FIFO
- FIFO stands to be First In First Out.
- This method of inventory valuation records the oldest inventory items as being sold first in the books. This means that the end inventory will always reflect the latest purchase cost.
LIFO
- LIFO stands for Last in First Out.
- This method of inventory valuation records the most recent inventory items as being sold first in the books. This means that the ending inventory is valued at the lowest purchase price.
WEIGHTED VERGE
- This method values the end inventory at the average weighted cost per unit.
- Average weighted cost per unit = Inventory available for purchase / Cost of inventory = $1,984/80 = $24.80/unit
- Inventory available for sale = Starting inventory + net purchases
33 $ 30.00 $ 11 $ 22.00 $ 44 $ Total Cost 990.00 242.00 1,232.00 16 Description 17 Jan. 20 Purchase 18 Jan. 15 Purchase 19 20 21 22 Description 23 Beginning inventory 24 Jan. 15 Purchase FIFO COST OF GOODS SOLD # of Units Cost per Unit 20 $ 20.00 16 $ 22.00 36 $ $ $ Total Cost 400.00 352.00 752.00
2) LIFO Description 32 January 15 Purchase 33 LIFO-ENDING INVENTORY #of Units Price per Unit 20 $ 20.00 $24 S 22.00 $ 44 $ Total cost 400.00 528.00 928.00 35 Description 37 January 20 Purchase 38 Jan. 20 39 LIFO COST of GOODS SOLD #of Units Price per Unit 33 $ 30.00 20.00 Total Cost 990.00 66.00 1 $ $ $ S 36
3) WEIGHTED MEDIAN COST Weighted average cost = Cost of Goods for Sale Number of Units for Sale $ 24.80 per Unit Weighted Average Ending Stock # of Units Total Cost 44 $ 24.80 $1,091.20 Weighted average Cost of Goods Sold # Of Units Total Cost 36 $ 24.80 $892.80
V 59, Beginning Inventory 60 Buy: Purchases 61 Goods for Sale 62 Lower: Ending Inventory 63 Price of Goods Sold $ $$ $ $ FIFO 4010.00 $ 1,584.00 $1 1,984.00 $2 1,232.00 $7752.00 $ 752.00 LIFO 400.00 $ 1,584.00 $4 1,984.00 $3 1,984.00 $6 1,232.00 $8 1,232.00 $752.00 $ 752.00 $552.00 $ LIFO 400.00 $500 $ 1,584.00 $ 1984.00 Avg. 400.00 1,584.00 1,984.00 1,091.20 892.80 64
Description # of Units Total Cost 4 January 15th, Purchase 6 Jan. 20, Purchase 7 Jan. 20th, Goods for Sale 8 January Sales 9 January Inventory 400 594 990 1984 10
13 1) FIFO 16 Description Price per Unit FIFO EXTENDING INVENTORY # =+B6=+B19–B17 17/01/20 Purchase =+C6=+C5 Total cost =+B17*C17=+B18*C18 =SUM (D17:D18). 44 Description Price per Unit 23 Starting inventory 24 Jan. 25 Purchase 25 FIFO COST of Goods SOLD # =+B4=+B25-B2336 =+C4=+B23 =+B24*C23 =+B24*C24*C24
27 2) LIFO 28 29 31 31 32 January 15 Buy Description Price per Unit LIFO EXTENDING INVENTORY # =+B4=+B33–B31 =+C4=+C5 Total cost =+B31*C31 ++B32*C32 =SUM (D31:D32). 44 Description Description Price per Unit LIFO COST of Goods SOLD # =+B6 =+B39–B37 36 37 Jan. 20 38 Jan. 20 Order =+C6
41 42
V 59, Beginning Inventory 50 Add: Purchases of 61 Goods for Sale 62 Lower: Ending Inventory 53 Price of Goods Sold FIFO =+D4 =+SUM (D5,06) =SUM (B59:360). =+D19 LIFO =+D4=+SUM (5,D6) =SUM (30 C59:C60) =+D33 =+C61–C62 Wtd. Avg. =+D4 =+SUM(D5,D6) =SUM(D59:060) =+D51 =+D61-D62 =+B61-162
Conclusion
Above is the solution for “Better Bottles, Inc. uses a periodic inventory system and has provided its current inventory information. The…“. We hope that you find a good answer and gain the knowledge about this topic of business.