The Excel file associated with this exercise includes a sheet $($Inventory Data Sheet$)$ with information regarding Lain Company's inventory

including unit cost, cost of disposal, unit selling price, and quantity on hand. A second sheet $($Inventory Obsolescence Sheet$)$ includes

information about inventory mark downs on the selling price due to obsolescence concerns. In this exercise, we calculate the value of

inventory at the lower of cost or net realizable value. In doing so$,$ we first update sales data with the latest inventory obsolescence

information using a useful Excel function: VLOOKUP.

Required

Download the following Excel file: Lain Company

In the Data$-$Inventory Obsolescence worksheet, use VLOOKUP to pull in the original selling price from the Data$-$Inventory worksheet.

Hint: Use the Item number in the first column as the lookup value, the table in the Inventory Data Sheet for the source to pull from, the

column titled Selling price per unit for the source data. This must be an exact match.

List the formula that is currently in cell D$4$ of your Inventory Obsolescence sheet.

Calculate the updated selling price, considering the mark$-$down percentage in the Updated Selling Price per Unit column.

What is the updated selling price for Item No$.1823555?$

Note: Enter the answer in dollar and cents, rounded to the nearest penny.

$$

Using the VLOOKUP function, add the updated selling price from the Data$-$Inventory Obsolescence worksheet to the Updated Selling

Price per Unit column in the Data$-$Inventory worksheet.

Eliminate the errors in the cells in the Updated Selling Price per Unit column on the Data$-$Inventory worksheet by replacing the error

with the original selling price per unit using the IFERROR function. Hint: Enclose the formula used in part $5$ within the IFERRROR function:

replace the error sign with the original selling price per unit.

List the formula that is currently in cell G$5$ of your Inventory Data sheet.

For each item, enter the net realizable value in the Net Realizable Value per Unit column.

Using an IF statement, calculate the lower of cost per unit or net realizable value per unit in the Lower of Cost or NRV per Unit column.

Hint: If the cost per unit is less than the net realizable value per unit, show the cost per unit value in the cell; otherwise, show the net

realizable value per unit in the cell.