Description
Hello Can anybody help me with this excel sheet and document?
Unformatted Attachment Preview
Purchase answer to see full attachment
Explanation & Answer
Here is the answer. Thanks
Answer of Question No: 01
Date
Dec.02
Dec.03
Dec.04
Dec.05
Dec.14
Accounts
Debit
6000
Purchase
Accounts Payable
Credit
6000
Freight Charge
Cash
150
150
Accounts Receivable
Sales
10000
Cost of Goods Sold
Merchandise Inventory
7000
Accounts Payable
Purchase Return
1000
Cash
Discount Allowed
Accounts Receivable
9800
200
10000
7000
1000
10000
Answer of Question No: 02
1) Revenue = $2925000
Working
Total Revenue = (650000*4.50) = $2925000
2) Inventory Table
Date
Start
Buy
Buy
Buy
1/1/2015
1/10/2015
1/18/2015
1/23/2015
Tons
Purchased
n/a
200000
200000
200000
$
Paid/ton
n/a
1.50
2.00
2.50
Total
tons of
Purchased Inventory
n/a
300000
300000
500000
400000
700000
500000
900000
$ Amount
$ Value
Average
inventory
300000
600000
1000000
1500000
Per Ton
1.00
1.20
1.43
1.67
1
3) COGS using FIFO = $900000
Working
COGS Under FIFO = [(300000*1.00)+(200000*1.50)+(150000*2.00)] = $900000
4) REMAINING INVENTORY AMOUNT using FIFO = $600000
Working
Remaining Inventory under FIFO = Total Cost of Goods Available For Sale – COGS
= (1500000 - 900000)
= $600000
5) COGS using LIFO = $1250000
Working
COGS Under LIFO = [(200000*2.50)+(200000*2.00)+(200000*1.50) + (50000*1.00)] =
$1250000
6) REMAINING INVENTORY AMOUNT Using LIFO = $250000
Working
Remaining Inventory under LIFO = Total Cost of Goods Available For Sale – COGS
= (1500000 - 1250000)
= $250000
7) COGS using Weighted Average = $1083333.33
Working
COGS under Weighted Average = [(1500000/900000)*650000] = $1083333.33
8) REMAINING INVENTORY AMOUNT using Weighted average = $416666.67
Working
Remaining Inventory under Weighted average = Total Cost of Goods Available For Sale –
COGS
= (1500000 - 1083333.33)
= $416666.67
2
Answer of Question No: 03
Requirement A:
Chisolm Co.
Bank Reconciliation
December 31, 2010
9000 Chisholm accounting balance
Bank statement balance
Add:
Deposit of December 31
9675
Add:
1392 Error (Check 437)
Less:
Outstanding Check
72
Less:
(1125) NFS Check (Including Fee)
Service Charge
Adjusted bank balance
9267 Adjusted book balance
(455)
(25)
9267
Requirement B:
Date
(a)
No Entry is required.
(b)
No Entry is required.
(c)
Cash
Rent Expense
72
Accounts receivable - Wayne Johnson
Cash
455
Miscellaneous expenses
Cash
25
(d)
(e)
(f)
Accounts
Debit
Credit
72
455
25
No Entry is required.
3
NVCC Accounting Inventory and Depreciation analysis
Instructions: Use the numbers below for each PART (A, B etc) and Step (1,2 etc). Add numbers into the cells and answer
any questions asked in each of the "call out" boxes.
NUMBERS PROVIDED (See Instructions and use for Part A Income Statement)
Sales price
Quantity (Units) sold in 2014
$700.00
12,000
Units carried over from 2013
Price per unit of inventory carried over
Units purchased for each monthly purchase
Purchase price of Jan 1 units
Purchase price for Jan 15 units
Purchase price for Feb 1 units
Purchase price for Feb 15 units
Purchase price for Mar 1 units
Purchase price for Mar 15 units
Purchase price for Apr 1 units
Purchase price for Apr 15 units
Purchase price for May 1 units
Purchase price for May 15 units
Purchase price for Jun 1 units
Purchase price for Jun 15 units
Purchase price for July 1 units
Purchase price for July 15 units
Purchase price for Aug 1 units
Purchase price for Aug 15 units
Purchase price for Sept 1 units
Purchase price for Sept 15 units
Purchase price for Oct 1 units
Purchase price for Oct 15 units
Purchase price for Nov 1 units
Purchase price for Nov 15 units
Purchase price for Dec 1 units
Purchase price for Dec 15 units
4,000
$45.00
500
$110.00
$115.00
$120.00
$125.00
$130.00
$135.00
$140.00
$145.00
$150.00
$155.00
$160.00
$165.00
$170.00
$175.00
$180.00
$185.00
$190.00
$195.00
$200.00
$205.00
$210.00
$215.00
$220.00
$225.00
Revenue for 2014
Step 1
Cost of Existing inventory
Step 2
Step 3
Used to complete the Inventory carryover
and purchases table
For Part A - steps 4, 5, 6 and 7 use the data supplied above.
See Instuctions for Step 8 in Part B Balance Sheet
See Instuctions for Step 9 in Part C Depreciation
See Instuctions for Step 10 in Part D Break Even point analysis
After completing the 10 steps you are done. Email this Excel back to me. Dr. Dantonio
Fall 2015
ers into the cells and answer
Periodic Inventory project
Instructions: Complete this project by using the numbers provided.
Follow the steps (do not alter the 3 Income statements below)
Go to Step 1 - Calculate the revenue (note change on income statement)
For Step 2 - Calculate the cost in Dollars and value of existing Inventory
for the "carried from 2013" inventory.
For Step 3 - Calculate the cost in dollars and value of existing Inventory
for each purchase using the price for that date.
For Step 4 - Enter units sold for each method (they are the same)
For Step 5 - Use the completed Inventory Carryover and Purchases
Table to calculate the COGS using FIFO and LIFO.
For Step 6 - Use the completed Inventory Carryover and Purchases
Table to calculate the average cost and the COGS using AVERAGE.
For Step 7 - Calculate the value of the remaining Inventory.
FIFO
Tech Master Inc
Question : What effect does the Inventory met
Income, EPS, taxes and Rema
Answer: The net income, EPS, taxes and clos
differ across usage of three different approche
LIFO, Weighted Average Method. The closing
under LIFO technique is lowest, and under FIF
income, and EPS of the company are lowest u
under LIFO technique. Tax expense of the com
technique, and lowest under FIFO technique. T
closing Inventory under Weighted Average Me
Step 1:
Multiply the
units by the
Sales price to
get the
revenue for
this cell.
Income Statement
For year ended 31 December 2014
Revenue
$8,400,000.00
Cost of Goods Sold
Gross Margin
$1,360,000.00
$7,040,000.00
Depreciation Expense
$300,000.00
Salaries Expense
$1,200,000.00
Interest Expense
$4,000.00
Advertising Expense
Total Operating Expenses
Earnings before Income tax
Tax
$75.00
$1,504,075.00
$5,535,925.00
$1,937,573.75
Net Income
$3,598,351.25
EPS =
$1.80
Inventory carry
Date
LIFO
Tech Master Inc
Income Statement
Carried From 2013
For year ended 31 December 2014
Revenue
Cost of Goods Sold
Gross Margin
$8,400,000.00
$2,010,000.00
$6,390,000.00
Depreciation Expense
$300,000.00
Salaries Expense
$1,200,000.00
Interest Expense
$4,000.00
Advertising Expense
Total Operating Expenses
$75.00
$1,504,075.00
1-Jan
15-Jan
1-Feb
15-Feb
1-Mar
15-Mar
1-Apr
15-Apr
1-May
15-May
Earnings before Income tax
Tax
$4,885,...