Question
HELP PLEASE! You must use formulas for ALL calculation (addition, subtraction, multiplication and division). You should adjust column widths as necessary to display all information
HELP PLEASE!
- You must use formulas for ALL calculation (addition, subtraction, multiplication and division).
- 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: LIFO
Use the following information for Trubisky Company for the year ended Dec. 31, 2019 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
Sept 7 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 | # of units | Unit Cost | Total Cost | |
? | Beginning Inventory | ? | ? | ? |
? | Purchase | ? | ? | ? |
? | Purchase | ? | ? | ? |
Goods available for sale | ? | ? | ||
Units sold | ? | |||
Units in Ending Inventory | ? |
Copy this schedule showing Goods Available for sale and ending inventory and paste it into Sheet 2 (FIFO) and Sheet 3 (LIFO) at cell A1.
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 I and J, starting in Row 1.
- Start with the heading.
- Put the titles in Column I and the amounts in Column J.
- You must use Excel to compute Sales Revenue.
- 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 I and J, 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 L and M., starting on Row 1.
- Start with the heading.
- Use column L to enter the titles, and use column M 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 L and M, 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, 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).
*
Check figures:
Weighted average: Net income = 123,500; Total current assets = 207,120
FIFO: Net income = 128,500; Total current assets = 212,120
LIFO: Net income = 115,500; Total current assets = 199,120
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started