Requirements:

$1)$Using the data below, calculate the requested values.

$2)$Use appropriate cell referencing and formulas. Do not type in any numbers $($i$.$e$.,$no "hard coding" data$).$

Tips:

$1)$The tables below have been provided to guide your calculations. For the more complex perpetual inventory calculations, I have filled in the first row to get you started. These tables follow a similar format as the Excel example problem I did for class.

$2)$Remember that in a table, only the first line of data and totals should use $ signs. Otherwise use comma format. If you get a yellow cell after entering data, be sure to look for the "tip" provided by the tool in the row at the top of your spreadsheet. Usually this is a cell formatting issue.

$3)$All dollar values should be taken to TWO decimal places.

$4)$For #s $8$and $9,$it is efficient to use the $=$SUMPRODUCT formula when calculating the current balance in inventory after each additional sale or purchase $.$

Grading: $10$possible points

This spreadsheet contains macros which will provide real time feedback on EACH cell that you enter in the gray shaded areas. There are $110$cells which get graded, and your score on this assignment will be calculated based upon the $\%$of the $110$cells that are completed correctly.

Zhang Company manufactures many products for use in the airline industry. Presented below is information concerning one of its products, the Orbit. Use this information

to calculate the nine items below.

Date Transaction Quantity Price$/$cost

$1/1/2024$Beginning Inventory $1600$$ $31\mathrm{.}00$

$3/6/2024$Sale $100050\mathrm{.}00$

$6/26/2024$Purchase $350033\mathrm{.}00$

$8/2/2024$Purchase $200034\mathrm{.}00$

$10/31/2024$Sale $400052\mathrm{.}00$

Total Units Available$*$

Total Units Sold

Total Units in Ending Inventory

$*$This is the same as Goods Available for Sale $($GAFS$)$in units

$1)$Calculate Goods Available For Sale $($GAFS$)$in dollars.

Date Units Unit Cost Extended Cost

$1/1/20241600$$ $31\mathrm{.}00$$ $49,600\mathrm{.}00$

$6/26/2024350033\mathrm{.}00115,500\mathrm{.}00$

$8/2/2024200034\mathrm{.}0068,000\mathrm{.}00$

$7100$ $ $233,100\mathrm{.}00$

$2)$Calculate FIFO Cost of Goods Sold under a Periodic Inventory System

$3)$Calculate FIFO Ending Inventory under a Periodic Inventory System

$($HINT: You can do this directly using GAFS and COGS$)$

Units Sold Unit Cost Extended Cost

$1600$$ $31\mathrm{.}00$$ $49,600\mathrm{.}00$

FIFO Ending Inventory

$33\mathrm{.}00$

FIFO COGS

$4)$Calculate LIFO Cost of Goods Sold under a Periodic Inventory System

$5)$Calculate LIFO Ending Inventory under a Periodic Inventory System

$($Hint: You can do this directly using GAFS and COGS$)$

Units Sold Unit Cost Extended Cost

$2000$$ $34\mathrm{.}00$$ $68,000\mathrm{.}00$

LIFO Ending Inventory

$300033\mathrm{.}0099,000\mathrm{.}00$

LIFO COGS $ $167,000\mathrm{.}00$

$6)$Calculate Weighted Average Ending Inventory and COGS under a Periodic Inventory System.

Weighted Average Cost$/$Unit

Units in Ending Inventory

Weighted Average Ending Inventory

Weighted Average COGS

$7)$What are the following under a Perpetual Inventory System?

FIFO COGS

FIFO Ending Inventory

$8)$ Calculate LIFO Cost of Goods Sold & Ending Inventory under a Perpetual Inventory System $($HINT: The textbook example $7\mathrm{.}8$ on page $7-16$ demonstrates how to do this$)$

Purchased Sold Balance in Inventory

Date No$.$ of units Unit cost No$.$ of units Unit cost No$.$ of units Unit cost Amount

$1/1/20241600$ $ $31\mathrm{.}001600$ $ $31\mathrm{.}00$ $ $49,600\mathrm{.}00$

$3/6/20241000$

$6/26/2024350033\mathrm{.}00$

$8/2/2024200034\mathrm{.}00$

$10/31/2024$

LIFO Ending Inventory $-$ Perpetual System

LIFO COGS $-$ Perpetual System

$9)$ Calculate Moving Average Cost of Goods Sold & Ending Inventory under a Perpetual Inventory System $($HINT: The textbook example $7\mathrm{.}10$ on page $7-19$ demonstrates how to do this$)$

Purchased Sold Balance in Inventory

Date No$.$ of units Unit cost No$.$ of units Unit cost No$.$ of units Unit cost Amount Moving Average Unit Cost

$1/1/20241600$ $ $31\mathrm{.}001600$ $ $31\mathrm{.}00$ $ $49,600\mathrm{.}00$ $ $31\mathrm{.}00$

$3/6/20241000$

$6/26/2024350033\mathrm{.}00$

$8/2/2024200034\mathrm{.}00$

$10/31/2024$

Moving Average Ending Inventory

Moving Average COGS

I need help filling in the blanks, and help doing the formulas because I cannot use any hard coding numbers, I have to use excel formulas.