Periodic Inventory

Completing this activity will help you learn to:

$$ Determine the value of ending inventory using a periodic system under FIFO and LIFO.

$$ Extract information from a data set using Excel formulas, functions, and$/$or pivot tables.

Scenario:

Company A is the parent company of eight merchandising outlets. Each offers a small number of specialty products unique to its region.

All outlets use a periodic inventory system. Some products are accounted for using the FIFO method; the LIFO method is used for others.

The Data worksheet contains a list of products, each of which is unique.

Each row provides information about one product for one merchandiser, including inventory method, beginning inventory on $1/1($both quantity and unit cost$),$ two purchases during the year $($both quantity and unit cost for each$),$ and number of units in inventory at the end of the year.

Required:

Open spreadsheet

Complete three columns on the Data worksheet: Ending inventory value FIFO, Ending inventory value LIFO, and Ending inventory value.

R$1.$ Cell L$2$: Enter a formula with nested IF functions that results in the dollar value of the product's ending inventory under FIFO using the data in row $2.$

fill in the blank $1$ of $1$$

R$2.$ Cell M$2$: Enter a formula with nested IF functions that results in the dollar value of the product's ending inventory under LIFO using the data in row $2.$

fill in the blank $1$ of $1$$

R$3.$ Cell N$2$: Enter an IF function that returns the correct amount from either cell L$2$ or M$2$ based on the method listed in cell D$2.$

fill in the blank $1$ of $1$$

R$4.$ Copy down the entries in cells L$2,$ M$2,$ and N$2$ to all other rows with data.

Steps to create a pivot table:

S$1.$ Select a cell in the data on the Data worksheet.

S$2.$ Select Insert $/$ PivotTable. The data range will automatically be inserted. A $$Create PivotTable$$ window should appear.

S$3.$ In the window, under "Choose where you want the PivotTable report to be placed", select $$New Worksheet$.$ A new worksheet should appear with the PivotTable.

S$4.$ A PivotTable Field List should pop up$.$ Drag fields into rows, columns, filters, and values areas as needed. $($If this gray work box does not appear, right click in the pivot table and select "Show Field List".$)$

Questions:

Use the information on the Data worksheet and pivot tables, formulas, and$/$or functions to answer the following questions.

Q$1.$ How many units did Havelock Company, Kumara Corporation, and Methven, LLC sell, all combined?

fill in the blank $1$ of $1$

Q$2.$ What is the amount of cost of goods sold for Product $128?$

fill in the blank $1$ of $1$$

Q$3.$ What is the amount of difference in ending inventory value if Products $7,50,67,88,$ and $110$ had been accounted for under FIFO rather than LIFO?

fill in the blank $1$ of $1$$

Q$4.$ What is the amount of difference in ending inventory value of all of Ohura, Inc.$\text{'}$s products if all had been accounted for under LIFO and none had been accounted for under FIFO?

fill in the blank $1$ of $1$$

Q$5.$ If a company's ending inventory value is higher under FIFO when compared to LIFO, it may be because

prices are increasing over time and under LIFO the higher$-$priced units remain in stock.

the number of units increases with each purchase.

prices are decreasing over time and under LIFO the lower$-$price units remain in stock.

prices are increasing over time and under LIFO the lower$-$priced units remain in stock.