Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

image text in transcribed
image text in transcribed
image text in transcribed
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: Wed Avg Sheet 2: FIFO Sheet 3: LIFO 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 cost per unit Mar 5 Purchase 6,000 units $16 cost per unit Apr 17 Sale 7,000 units e $38 selling price per unit Sept 7 Purchase 8,000 units @ $17 cost per unit Nov 11 Sale 6,000 units @ $38 selling price per unit $14 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 Date 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: of units Unit Cost Total Cost Beginning inventory ? Purchase 2 Purchase 2 Goods available for sale Units sold Units in Ending Inventory ? ? ? ? ? ? ? ? 7 2 2 2 7 Copy this schedule showing Goods Available for sale and ending inventory and paste it into Sheet 2 (FIFO) and Sheet 3 (LIFO) at cell Al. Copy this schedule showing Goods Available for sale and ending inventory and paste it into Sheet 2 (FIFO) and Sheet 3 (LIFO) at cell Al. 2) Below the schedule, compute the weighted average cost per unit. Use formulas in your computation You must label the amount as Weighted Average Cost per unit." 3) Below the weighted average cost per unit, compute the cost of ending inventory using the units in ending inventory calculated in Instruction 1) . the weighted average cost per unit calculated in instruction 2 Use formulas in your computation. Label the amount "Ending Inventory 4) Below the computation of the cost of ending inventory, calculate cost of goods sold Label the amount Cost of Goods Sold. 5) Prepare a multiple step Income Statement in good form, using columns and starting in Row 1. Start with the heading Put the titles in Column and the amounts in Column . You must use an Excel formula to compute Sales Revenue. See the information on page 1 about the sales on Apr 17 & Nov 11 Use a formula with a cell reference to the cost of goods sold amount that you calculated in Instruction 4) Assume Operating Expenses are $162.500 and there are no other income or expenses. Copy the income statement and paste it into columns and starting on row 1 on Sheet 2 (FIFO) and Sheet 3 (LIFO). You will update the cost of Goods Sold amount when you work on those sheets. 7) Prepare a partial balance sheet, showing the Current Assets section, using columns Mand N, starting on Row 1. Start with the heading Use column M to enter the titles, and use column to enter the amounts Assume Cash = 98,900, Accounts Receivable = 25,700 Supplies = 2,520 Use a formula with a cell reference to the balance of Merchandise inventory that you calculated in instruction 3) Label and compute Total current assets Copy the income balance sheet and paste it into columns Mand N starting in row 1. on Sheet 2 (FIFO) and Sheet 3 (LIFO). You will update the Merchandise inventory amount when you work on those sheets Sheet 2 - FIFO: You will calculate ending inventory using a periodic system and the FIFO 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, Wid Avg 1) Below the schedule of Goods available for sale and unitsin ending inventory, compute the cost of ending inventory using FIFO. Use formulas in your computation. Label the amount "Ending Inventory 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)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The ASQ Certified Food Safety And Quality Auditor

Authors: Steven Wilson

4th Edition

1951058186, 978-1951058180

More Books

Students also viewed these Accounting questions