You are teaching a graduate class in financial statement analysis and need to devise a test question that demonstrates how FIFO and LIFO can provide different financial ratios. Note: you must develop original work to complete this question.

Develop a spreadsheet-based test question and the answer key that shows the following:

For your purchases:

The beginning inventory plus 12 months of product inventory purchased by unit (units should vary from month to month). Show the ending inventory for the end of the year.

The cost per unit for each month (the cost should vary from month to month.) Show the total annual cost.

For your sales:

The beginning inventory plus 12 months of product inventory sold by unit (units should vary from month to month). Show the ending inventory for the end of the year.

The cost per unit for each month (the cost should vary from month to month.) Show the total annual cost at the end of the year.

Show the following, including the math calculations required to determine the following using FIFO:

Ending inventory units and cost

COGS for the year

Gross margin for the year

Show the following, including the math calculations required to determine the following using LIFO:

Ending inventory units and cost

COGS for the year

Gross margin for the year

Discuss the different results, including pros and cons of each result under LIFO and FIFO.