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{.}003/6/2024$ Sale $100050\mathrm{.}006/26/2024$ Purchase $350033\mathrm{.}008/2/2024$ Purchase $200034\mathrm{.}0010/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{.}006/26/2024350033\mathrm{.}00115,500\mathrm{.}008/2/2024200034\mathrm{.}0068,000\mathrm{.}007100$ $ $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{.}003/6/202410006/26/2024350033\mathrm{.}008/2/2024200034\mathrm{.}0010/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{.}003/6/202410006/26/2024350033\mathrm{.}008/2/2024200034\mathrm{.}0010/31/2024$

Moving Average Ending Inventory Moving Average COGS

I need help showing the formula$$s in excel to show how I got my answers. No hard coding numbers.