You must use formulas for ALL calculation (addition, subtraction, multiplication and division). Your amount will be marked wrong if you use a calculator or mental math instead of a formula. . You should adjust column widths as necessary to display all information in the cell. Open Excel and click on 'Blank Workbook. You will use this file to complete the assignment, which will require you to use 3 sheets in the Excel file to perform inventory calculations and prepare financial statements. Step 1: Name the sheets as follows: Sheet 1: Wtd Avg Sheet 2: FIFO Sheet 3: LFO Use the following information for Mercedes Company for the year ended Dec. 31, 2022 to complete Sheets 1-3: Jan 1 Beginning inventory 4,000 units $14 cost per unit Mar 5 Purchase 6,000 units @ $16 cost per unit Apr 17 Sale 7,000 units @ $38 selling price per unit Sept7 Purchase 8,000 units @ $17 cost per unit Nov 11 Sale 6,000 units $38 selling price per unit Sheet 1: Wtd Avg - You will prepare a schedule showing Cost of Goods Available for sale and the units in Ending Inventory, using the above information. You will calculate ending inventory using a periodic system and the weighted average method. You will also prepare an income statement and a partial balance sheet 1) Create a schedule showing Goods available for sale in units and cost and the units in Ending Inventory, based on the following example: Example: Date Unit Cost Total Cost 2 ? 2 # of units ? 2 7 ? Beginning Inventory Purchase Purchase Goods available for sale Units sold Units in Ending Inventory 2 2 ? 2 ? ? 2 Copy this schedule showing Goods Available for sale and ending inventory and paste it into Sheet 2 (FIFO) and Sheet 3 (UFO) at cell Al. Sheet 2 - FIFO: You will calculate ending inventory using a periodic system and the FIFO method. an income statement and a partial balance sheet. 3 of 3 This sheet should have the schedule of Goods available for sale since the instructions required you to copy it here from Sheet 1, Wtd Avg. 1) Below the schedule of Goods available for sale and units in ending inventory, compute the cost of ending inventory using FIFO. Use formulas in your computation. Label the amount Ending Inventory 2) Below the computation of the cost of ending inventory, calculate cost of goods sold, using FIFO. Use formulas in your computation. Label the amount 'Cost of Goods Sold.' 3) Update the income statement using a formula with a cell reference to the cost of goods sold calculated in Instruction 2) 4) Update the partial balance sheet using a formula with a cell reference to the new Merchandise Inventory amount, calculated in Instruction 1). Sheet 3 - LIFO: You will calculate ending inventory using a periodic system and the LIFO method. You will also prepare an income statement and a partial balance sheet. This sheet should have the schedule of Goods available for sale since the instructions required you to copy it here from Sheet 1, Wtd Avg. 1) Below the schedule of Goods available for sale and units in ending inventory, compute the cost of ending inventory using LIFO. Use formulas in your computation. Label the amount 'Ending Inventory 2) Below the computation of the cost of ending inventory, calculate cost of goods sold, using FIFO. Use formulas in your computation. Label the amount 'Cost of Goods Sold. 3) Update the income statement using a formula with a cell reference to the cost of goods sold calculated in Instruction 2) 4) Update the partial balance sheet using a formula with a cell reference to the new Merchandise Inventory amount, calculated in Instruction 1)
