Need help with this spreadsheet and Word document

User Generated

Snvel

Business Finance

Description

Hello Can anybody help me with this excel sheet and document?

Unformatted Attachment Preview

NORTHERN VIRGINIA COMMUNITY COLLEGE Business and Social Science Division Woodbridge Campus Principles of Accounting I Take home (Chapters 4 - 6) Professor Dr. Mark DAntonio NAME: _____________________ Answer all parts of all questions. appropriate. Show calculations where 1. You keep the accounting records for a small merchandising corporation which operates on a fiscal year that ends on December 31. Using the separate general journal form provided, journalize the selected business transactions given below. You may omit the explanations for your entries. (40 points) 2005 Dec. 2 Purchased $6,000 worth of merchandise on credit from Buy-Right Corp. Their invoice #a210 includes sales terms of 1/10, n/30, FOB shipping point. 3 Paid Express Shipping Company $150 for the delivery of the merchandise you purchased from Buy-Right Corp. on Dec. 2. 4 Sold $10,000 worth of merchandise on credit to Best Supply Co. on Invoice #2256, terms 2/10, n/30, FOB shipping point. The merchandise cost $7,000. (Remember you need to do 2 entries here!) 5 You discovered that some of the merchandise you purchased from Buy-Right Corp. on Dec. 2 was defective and had to be sent back. You returned $1,000 worth of merchandise. 14 Received a check from Best Supply Co. for the merchandise you had sold to them on Dec. 4, less their applicable discount. 1 Note: Print neatly and Skip a line between your entries Date Accounts Debit Credit 2 40 Points (Show work and circle the answer for each question) Angel Inc. currently (on 1/1/2015) has 300,000 tons of gravel in their inventory (the balance sheet shows this is worth $300,000). On 1/10/2015 they make a gravel purchase: 200,000 at $1.50 per ton. On 1/18/2015 they make a gravel purchase: 200,000 at $2.00 per ton. On 1/23/2015 they make a gravel purchase: 200,000 at $2.50 per ton. On January 25th of 2015 they sell 650,000 tons of mulch to George Mason University for $4.50 per ton. 1) Calculate the Revenue = __________ 2) Fill out the inventory table below Date Tons Purchased Start 1/1/2015 n/a $ Paid/ton n/a $ Amount Total tons of $ Value Average Purchased Inventory inventory Per Ton n/a Buy Buy Buy 3) What is the COGS using FIFO = 4) What is the REMAINING INVENTORY AMOUNT using FIFO = 3 5) What is the COGS using LIFO = 6) What is the REMAINING INVENTORY AMOUNT Using LIFO = 7) What is the COGS using Weighted Average = 8) What is the REMAINING INVENTORY AMOUNT using Weighted average = 4 The following information was available to reconcile Chisholm Company's book balance of cash with its bank statement balance as of December 31, 2010: a. After all posting was completed on December 31, the company's Cash account had a $9,675 debit balance, but its bank statement showed a $9,000 balance. b. Check No. 409 for $1,125 was outstanding. c. In comparing the canceled checks returned with the bank statement with the entries in the accounting records, it was found that Check No. 437 for the purchase of office supplies was correctly drawn for $419 but was erroneously entered in the accounting records as though it were for $491. d. A debit memo for $455 listed a $440 NSF check plus a $15 NSF charge. The check had been received from Wayne Johnson as payment on his account and was returned and uncollectible. e. Also enclosed with the statement was a $25 debit memo for bank services. It had not been recorded because previous notification had been received. f. no The December 31 cash receipts, $1,392, were placed in the bank's night depository after banking hours on that date and this amount did not appear on the bank statement. Required: A. Prepare a bank reconciliation for the company as of December 31, 2010. Be sure to label all numbers and clearly indicate whether you are adding or subtracting. (15 points) 5 Bank statement balance Chisolm Co. Bank Reconciliation December 31, 2010 Chisholm accounting balance B. Prepare one (1) general journal entry necessary as a result of the bank reconciliation. You may omit the explanation. (5 points) Date Debit Credit 6
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

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,...


Anonymous
Goes above and beyond expectations!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags