please finish spreadsheet
ABC Company Unadjusted Trial Balance June 30, 20XX Cash Accounts Receivable Supplies Inventory Prepaid Insurance Prepaid Rent Prepaid Expenses Equipment Accummulated Depreciation Equipment Accounts Payable Salaries Payable Unearned Revenue Capital Revenue Depreciation Expense Equipment Salaries Expense Insurance Expense Rent Expense Advertising Expense Supplies Expense Utilities Expense Travel Expense Debits $25,000 10,000 15,000 Credits 10,000 2,500 75,000 125,500 1,200 24,000 5,500 22,000 $6,500 7,800 330,000 15,000 12,000 3,000 75,000 225,000 330,000 The numbers on this Unadjusted Trial Balance need recorded as the starting balances in the General Ledger. To those balances the General Journal entries that were made to record the necessary Adjusting Entries (based on the transactions provided on the "Transaction" tab) need added. The adjusted balance for each account needs recorded in the Adjusted Trial Balance. $1,000 of Prepaid Insurance was for insurance for the month of June. A physical count of Supplies Inventory shows that the Supplies Inventory amount at the end of June is $11,000 Annual Depreciation on Equipment is $7,500. The company determined that $2,000 of the revenue recorded as Unearned Revenue was earned as of the end of June. A customer paid $1,500 deposit on account during June that was recorded as Revenue. Wages Payable at the end of June were determined to be $6,200 Rent for July in the amount of $2,000 had been paid in June. Advertising Fee for ads in July papers of $500 was recorded as Advertising Expense in June. Record these necessary adjustments in the General Journal on the next tab. GENERAL JOURNAL Date 30Jun 30Jun Accounts Page 1 Debit Insurance expense prepaid insurance paid June ins. $1,000 Supply Inventory Expense Supply Inventory Credit $4,000 $1,000 $4,000 CASH Date June 1 Description Balance forward Debit Credit ACCOUNTS RECEIVABLE Date Description June 1 Balance forward Debit SUPPLIES INVENTORY Date Description June 1 Balance forward Debit PREPAID INSURANCE Date Description June 1 Balance forward Debit PREPAID RENT Date Description June 1 Balance forward Debit PREPAID EXPENSES Date Description June 1 Debit EQUIPMENT Date June 1 Debit Description - Balance 25,000 - - Balance 10,000 Credit - Credit - Balance - Credit - - Balance - Credit - - Balance - Credit - - Balance - Credit - - Balance - ACCUMULATED DEPRECIATION EQUIPMENT Date Description Debit Credit June 1 - - ACCOUNTS PAYABLE Date Description June 1 Debit - SALARIES PAYABLE Date Description June 1 Debit UNEARNED REVENUE Date Description June 1 Debit CAPITAL Date June 1 Description Debit REVENUE Date June 1 Description - Balance Credit - Balance Credit - Balance Credit - Balance - Credit - - Balance - Credit - - Balance - DEPRECIATION EXPENSE EQUIPMENT Date Description Debit June 1 - Credit SALARIES EXPENSE Date Description June 1 Debit Credit INSURANCE EXPENSE Date Description June 1 Debit RENT EXPENSE Date Description June 1 Debit ADVERTISING EXPENSE Date Description June 1 Debit SUPPLIES EXPENSE Date Description June 1 Debit UTILITIES EXPENSE Date Description June 1 Debit TRAVEL EXPENSE Date Description June 1 Debit - Balance - - - Balance - Credit - - Balance - Credit - - Balance - Credit - - Balance - Credit - - Balance - Credit - - Balance - Credit - - - - Debit - - Balance - - ABC Company Adjusted Trial Balance June 30, 20XX Debits Credits Cash Accounts Receivable Supplies Inventory Prepaid Insurance Prepaid Rent Prepaid Expenses Equipment Accummulated Depreciation - Equipment Accounts Payable Salaries Payable Unearned Revenue Capital Revenue Depreciation Expense - Equipment Salaries Expense Insurance Expense Rent Expense Advertising Expense Supplies Expense Utilities Expense Travel Expense - - Calculate the COGS and the Inventory balance for the end of the month 1Jun Purchased merchandise for $5,000 under credit terms of 1/10, n 30, FOB destination, invoice dated June 1. Paid cash for freight charges of $150 directly to the shipper for the June 1 merchandise and reduced the amount of the 5Jun invoice by this amount. Sold merchandise for $3,500 under credit terms of 2/10, n/60, FOB destination, invoice dated June 7. The merchandise 7Jun cost was $2,100. Purchased merchandise for $6,000 under credit terms of 3/10, n/30, FOB shipping point, invoice dated June 11. 11Jun Additional charge of $200 added to bill for shipping charges. (Discounts do not apply to freight charges) 11Jun Paid for the merchandise purchased June 1st. 15Jun Customer returned $500 of merchandise sold on June 7, inventory cost of $300. Sold merchandise for $5, 500 under credit terms of 3/10, n30, FOB destination, invoice dated June 20. The merchandise 20Jun cost was $3,300. 21Jun Paid for merchandise purchased on June 11th. Purchased merchandise for $1,200 under credit terms of 2/10, n/30, FOB shipping point, invoice dated June 27. 27Jun Additional charge of $175 added to bill for shipping charges. Sold merchandise for $1,000 under credit terms of 2/10, n30, FOB shipping point, invoice dated June 29th. The 29Jun merchandise cost was $600. inventory 1Jun 5Jun 7Jun 11Jun 15Jun 20Jun 27Jun 29Jun 7Jun 15Jun 20Jun 29Jun Calculate the Total Inventory Value at Lower Cost or Market Product Hand Soap Cleanser Lotion Toothpaste Toothbrush Perfume Hair Spray Shampoo Hair Dryer Curling Iron Razor Comb Brush grand total Units 100 50 20 75 60 40 35 101 15 23 44 60 62 Market $1.50 $2.00 $3.10 $1.25 $0.95 $45.00 $3.00 $2.50 $30.00 $15.00 $53.00 $0.90 $2.12 Cost Total Inventory at Lower Cost or Market $1.25 $125.00 $2.10 $2.85 $1.30 $0.80 $39.00 $2.50 $2.10 $32.00 $13.75 $52.65 $0.88 $2.10 Date Beginning Balance Fill in the grey boxes below Units Cost 15000 15Jan 5000 10Feb 7500 21Mar 2500 11Apr 3000 24May 5500 29Jun 4000 5Jul 2750 12Aug 4500 8Sep 8000 28Oct 3025 14Nov 2000 20Dec 3000 $19.00 $17.00 $19.25 $18.00 $18.75 $17.85 $19.10 $18.95 $19.00 $17.65 $19.50 $19.75 $19.00 Physical Count December 31 $12,500.00 Number of Units Available for Sale during the year. Total Cost of the Units Available for Sale COGS Ending Inventory FIFO LIFO Weighted Average Grand Total ABC Company Unadjusted Trial Balance June 30, 20XX Cash Accounts Receivable Supplies Inventory Prepaid Insurance Prepaid Rent Prepaid Expenses Equipment Accummulated Depreciation Equipment Accounts Payable Salaries Payable Unearned Revenue Capital Revenue Depreciation Expense Equipment Salaries Expense Insurance Expense Rent Expense Advertising Expense Supplies Expense Utilities Expense Travel Expense Debits $25,000 10,000 15,000 Credits 10,000 2,500 75,000 125,500 1,200 24,000 5,500 22,000 $6,500 7,800 330,000 15,000 12,000 3,000 75,000 225,000 330,000 The numbers on this Unadjusted Trial Balance need recorded as the starting balances in the General Ledger. To those balances the General Journal entries that were made to record the necessary Adjusting Entries (based on the transactions provided on the "Transaction" tab) need added. The adjusted balance for each account needs recorded in the Adjusted Trial Balance. $1,000 of Prepaid Insurance was for insurance for the month of June. A physical count of Supplies Inventory shows that the Supplies Inventory amount at the end of June is $11,000 Annual Depreciation on Equipment is $7,500. The company determined that $2,000 of the revenue recorded as Unearned Revenue was earned as of the end of June. A customer paid $1,500 deposit on account during June that was recorded as Revenue. Wages Payable at the end of June were determined to be $6,200 Rent for July in the amount of $2,000 had been paid in June. Advertising Fee for ads in July papers of $500 was recorded as Advertising Expense in June. Record these necessary adjustments in the General Journal on the next tab. GENERAL JOURNAL Date 30Jun 30Jun Accounts Page 1 Debit Insurance expense prepaid insurance paid June ins. $1,000 Supply Inventory Expense Supply Inventory Credit $4,000 $1,000 $4,000 CASH Date June 1 Description Balance forward Debit Credit ACCOUNTS RECEIVABLE Date Description June 1 Balance forward Debit SUPPLIES INVENTORY Date Description June 1 Balance forward Debit PREPAID INSURANCE Date Description June 1 Balance forward Debit PREPAID RENT Date Description June 1 Balance forward Debit PREPAID EXPENSES Date Description June 1 Debit EQUIPMENT Date June 1 Debit Description - Balance 25,000 - - Balance 10,000 Credit - Credit - Balance - Credit - - Balance - Credit - - Balance - Credit - - Balance - Credit - - Balance - ACCUMULATED DEPRECIATION EQUIPMENT Date Description Debit Credit June 1 - - ACCOUNTS PAYABLE Date Description June 1 Debit - SALARIES PAYABLE Date Description June 1 Debit UNEARNED REVENUE Date Description June 1 Debit CAPITAL Date June 1 Description Debit REVENUE Date June 1 Description - Balance Credit - Balance Credit - Balance Credit - Balance - Credit - - Balance - Credit - - Balance - DEPRECIATION EXPENSE EQUIPMENT Date Description Debit June 1 - Credit SALARIES EXPENSE Date Description June 1 Debit Credit INSURANCE EXPENSE Date Description June 1 Debit RENT EXPENSE Date Description June 1 Debit ADVERTISING EXPENSE Date Description June 1 Debit SUPPLIES EXPENSE Date Description June 1 Debit UTILITIES EXPENSE Date Description June 1 Debit TRAVEL EXPENSE Date Description June 1 Debit - Balance - - - Balance - Credit - - Balance - Credit - - Balance - Credit - - Balance - Credit - - Balance - Credit - - Balance - Credit - - - - Debit - - Balance - - ABC Company Adjusted Trial Balance June 30, 20XX Debits Credits Cash Accounts Receivable Supplies Inventory Prepaid Insurance Prepaid Rent Prepaid Expenses Equipment Accummulated Depreciation - Equipment Accounts Payable Salaries Payable Unearned Revenue Capital Revenue Depreciation Expense - Equipment Salaries Expense Insurance Expense Rent Expense Advertising Expense Supplies Expense Utilities Expense Travel Expense - - Calculate the COGS and the Inventory balance for the end of the month 1Jun Purchased merchandise for $5,000 under credit terms of 1/10, n 30, FOB destination, invoice dated June 1. Paid cash for freight charges of $150 directly to the shipper for the June 1 merchandise and reduced the amount of the 5Jun invoice by this amount. Sold merchandise for $3,500 under credit terms of 2/10, n/60, FOB destination, invoice dated June 7. The merchandise 7Jun cost was $2,100. Purchased merchandise for $6,000 under credit terms of 3/10, n/30, FOB shipping point, invoice dated June 11. 11Jun Additional charge of $200 added to bill for shipping charges. (Discounts do not apply to freight charges) 11Jun Paid for the merchandise purchased June 1st. 15Jun Customer returned $500 of merchandise sold on June 7, inventory cost of $300. Sold merchandise for $5, 500 under credit terms of 3/10, n30, FOB destination, invoice dated June 20. The merchandise 20Jun cost was $3,300. 21Jun Paid for merchandise purchased on June 11th. Purchased merchandise for $1,200 under credit terms of 2/10, n/30, FOB shipping point, invoice dated June 27. 27Jun Additional charge of $175 added to bill for shipping charges. Sold merchandise for $1,000 under credit terms of 2/10, n30, FOB shipping point, invoice dated June 29th. The 29Jun merchandise cost was $600. inventory 1Jun 5Jun 7Jun 11Jun 15Jun 20Jun 27Jun 29Jun 7Jun 15Jun 20Jun 29Jun Calculate the Total Inventory Value at Lower Cost or Market Product Hand Soap Cleanser Lotion Toothpaste Toothbrush Perfume Hair Spray Shampoo Hair Dryer Curling Iron Razor Comb Brush grand total Units 100 50 20 75 60 40 35 101 15 23 44 60 62 Market $1.50 $2.00 $3.10 $1.25 $0.95 $45.00 $3.00 $2.50 $30.00 $15.00 $53.00 $0.90 $2.12 Cost Total Inventory at Lower Cost or Market $1.25 $125.00 $2.10 $2.85 $1.30 $0.80 $39.00 $2.50 $2.10 $32.00 $13.75 $52.65 $0.88 $2.10 Date Beginning Balance Fill in the grey boxes below Units Cost 15000 15Jan 5000 10Feb 7500 21Mar 2500 11Apr 3000 24May 5500 29Jun 4000 5Jul 2750 12Aug 4500 8Sep 8000 28Oct 3025 14Nov 2000 20Dec 3000 $19.00 $17.00 $19.25 $18.00 $18.75 $17.85 $19.10 $18.95 $19.00 $17.65 $19.50 $19.75 $19.00 Physical Count December 31 $12,500.00 Number of Units Available for Sale during the year. Total Cost of the Units Available for Sale COGS Ending Inventory FIFO LIFO Weighted Average Grand Total Income Statement [Name] [Time Period] Financial Statements in U.S. Dollars Revenue Gross Sales Less: Sales Returns and Allowances Net Sales Cost of Goods Sold Beginning Inventory Add: Purchases Inventory Available Less: Ending Inventory Cost of Goods Sold Gross Profit (Loss) Expenses Advertising Bank Charges Depreciation Dues and Subscriptions Insurance Legal and Professional Fees Licenses and Fees Office Expense Payroll Taxes Postage Rent Utilities Vehicle Expenses Wages Total Expenses Net Operating Income Complete the ratios listed based on the following Income Statement and Balance Sheet. ABC Company Income Statement Sales Last Year Quick Ratio $1,000,000 $750,000 $250,000 Cost of Goods Sold Gross Profit Operating Expenses Selling Expenses Administrative Expenses Total Operating Expenses $900,000 $650,000 $250,000 Accounts Receivable Turnover Inventory Turnover $50,000 $85,000 Net Profit on Sales $135,000 $127,000 $115,000 $17,500 $123,000 $17,500 Net Income before Taxes Income Tax Expense $97,500 $34,125 $105,500 $36,925 Net Income after Taxes $63,375 $68,575 Operating Income Interest Expense ABC Company Balance Sheet Gross Profit Margin Return on Assets Return on Equity Working Capital Debt to Equity Times Interest Earned Current Assets Cash Accounts Receivable Inventory $5,500 $21,000 $17,500 $4,950 $18,900 $15,750 Total Current Assets $44,000 $39,600 NonCurrent Assets Machinery & Equipment Furniture & Fixtures Transportation Equipment Accumulated Depreciation $100,000 $15,000 $25,000 $17,500 $90,000 $13,500 $22,500 $15,750 Total NonCurrent Assets $122,500 $110,250 Total Assets $166,500 $149,850 Current Liabilities Accounts Payable Notes Payable Wages Payable $25,000 $15,500 $1,200 $22,500 $13,950 $1,080 Current Liabilities $41,700 $37,530 NonCurrent Liabilities LongTerm Notes $27,000 $24,300 Total Liabilities $68,700 $61,830 Stockholders' Equity Retained Earnings Common Stock $65,000 $32,800 $58,500 $29,520 Total Equity $97,800 $88,020 $166,500 $149,850 Total Liabilities & Equity Current Ratio Create Journal Entries for the Following Petty Cash Transactions Template may need adjusted to do correct entries GENERAL JOURNAL 1Jun Company check written for $500 to establish a petty cash fund. 5Jun Company check written to Petty Cash for $250 to paid to gas. Date Accounts June 1, 2014 10Jun One company check written for all June 10th reimbursements: Paid for postage of $32.75 10Jun Paid for $200 for gasoline 10Jun Paid $52.50 for meals 10Jun Paid $20 for donation 10Jun Paid $7.50 for parking fees 15Jun Company check written to Petty Cash for $300 paid for gasoline 20Jun Company check written to Petty Cash for $400 paid for gasoline 21Jun Company check written to increase Petty Cash fund to $1,000 22Jun One company check written for all June 10th reimbursements: Paid for postage of $25.25 22Jun Paid $600 for gasoline 22Jun Paid $125 for meals 22Jun Paid $35 for office supplies 22Jun Paid $10 for parking fees Petty cash fund counted and found that there was $195.45 in the fund. Adjustment made to bring fund to 22Jun $1,000 30Jun Company decided $1,000 is too large for the Petty Cash fund. The fund is reduced to $750. Page 1 Debit Credit Prepare a Bank Reconciliation based on the following information: On June 30, the company's Cash account has a $20,222 debit balance, but the bank statement shows $25,552 Check #1234 for $2,250, check #1235 for $3,500, and check #1236 for $45 were outstanding on the June bank statement. Check #1237 cleared the bank for $1250 but was recorded in the company's records as $1205. The bank collects some Notes Receivable for the company. $4,500 was collected by the bank that the company had not previously recorded in the books. The bank deducted a 1% fee for this service and deposited the rest of the money in the company's bank account. The bank service charge for the month was $75, that the company had not previously recorded. Payments from two different customers resulted in NSF checks. The one check was for $500 and the other check was for $740. The company was charged a NSF bank fee of $35 for each of the checks. Deposits for June 30th were done after the close of the bank statement. This deposit was for $3,525 Bank Reconciliaton Ending balance per bank statement Add: Deduct: Correct cash balance Ending balance per company records $20,222.00 Add: notes rec 4,500 Deduct: error 1237 fee on notes rec. bank service charge 45 45 75.00 Correct cash balance Complete all journal entries necessary to reflect the adjustment necessary for the Allowance for Uncollectible Accounts based on Percentage of Receivables. Calculate the total balance for the Allowance of Uncollectible Accounts and enter it below. ABC Company has a total amount of Accounts Receivable of $1,500,000 prior to any yearend adjustments The current balance in the Allowance for Uncollectible Accounts is $15,750 The company has determined that the yearend allowance should be 2% of outstanding receivables. Date GENERAL JOURNAL Accounts December 30, 2014 Page 1 Credit Debit bad debt uncollectible accounts The total Allowance of Uncollectible Accounts Balance at the end of the year 14250 14250 30,000 Accounts at the end of the year and fill in the answer below. Age 0 to 30 days 31 to 60 days 61 to 90 days 91 to 120 days over 120 days Amount Outstanding GENERAL JOURNAL Accounts Date December 30, 2014 Probability of Collection $2,000,500.00 98.00% $1,500,750.00 95.00% $750,500.00 90.00% $300,500.00 75.00% $250,000.00 45.00% bad debt The total Allowance of Uncollectible Accounts Balance at the end of the year $1,960,490.00 $1,425,712.50 $675,450.00 $225,375.00 $112,500.00 Page 1 Credit Debit 402755 $40,010.00 $75,037.50 $75,125.00 collectible. The following are the sales for the last 3 months of the year. Make the appropriate journal entries to account for this allowance. October November December Date GENERAL JOURNAL Accounts October 31, 2014 bad debt expence allowance for uncollectable Accounts November 31, bad debt expence 2014 allowance for uncollectable Accounts Dec.31, 2014 bad debt expence allowance for uncollectable Accounts $550,250.00 $430,100.00 $625,750.00 Debit Page 1 Credit 22,010.00 22,010.00 17,204.00 17,204.00 25,030.00 25,030.00 Complete Journal Entries for the following transactions. 2Jun Purchased merchandise for $5,000, invoice dated June 2, credit terms of 2/15, n/60, and FOB shipping point. 2Jun Paid $150 for shipping charges on the June 2 purchase. 3Jun Returned merchandise for damaged merchandise that was invoiced at $500. 10Jun Purchased merchandise for $6,500 under credit terms of 1/10, n30, FOB destination, invoice date June 10th. 17Jun Paid for the purchase of June 2nd merchandise. Purchased merchandise under the following terms: $7, 000, invoice date June 18th, credit terms of 2/10, n/30 18Jun and FOB destination. 19Jun Received an allowance on the June 18th purchase of $ 750. 20Jun Paid for June 10th purchase. 28Jun Paid for June 18th purchase. GENERAL JOURNAL Accounts Date 2Jun Merch. Inventory accounts payable 2Jun Fright in cash 3Jun purch. and allowancess accounts payble 10Jun Merch. Inventory accounts payable 17Jun accounts payable cash purch. Discount 18Jun inventory merch Page 1 Debit Credit $5,000 $5,000 $150 $150 $500 $500 $6,000 $6,000 $5,000 $4,900 $100 $7,000 ABC Company purchases a new machine. The machine costs are $100,000. The company also must pay freight of $500 to get the machine delivered and $1,000 to get the machine installed. The equipment company has agreed to a 5% reduction in price if ABC accepts the machine by the end of June which ABC has agreed to. ABC has decided to depreciate the machine by the straight line method. It determines there is salvage value of $2500. The machine will be depreciated over 10 years. Record the journal entry for the purchase of the equipment for this year, and the journal entry that ABC will record next June for the first year of depreciation on the equipment. Date 6/30/2014 6/30/2014 GENERAL JOURNAL Accounts Equipment cash Depreciation Expence Accumulated Depreciation Debit 96,500.00 Page 1 Credit 96,500.00 9,400.00 9,400.00 ABC Company purchases a new machine for cash. The machine costs are $150,000. The company also must pay freight of $750 to get the machine delivered and $500 to get the machine installed. The equipment company has agreed to a 10% reduction in price if ABC accepts the machine by the end of June which ABC has agreed to. ABC has decided to depreciate the machine by the units of output method. It determines there is salvage value of $2500. The machine will be depreciated over 5 years. The following is the information for units of output. Year 1 Year 2 Year 3 Year 4 Year 5 1250 hours 1500 hours 2000 hours 2500 hours 1750 hours Record the journal entry for the purchase of the equipment for this year, and the journal entry that ABC will record next June for the first year of depreciation on the equipment. GENERAL JOURNAL Accounts Date Debit Page 1 Credit ABC Company purchases a new machine for cash. The machine costs are $500,000. The company also must pay freight of $1,500 to get the machine delivered and $2,500 to get the machine installed. The equipment company has agreed to a 7% reduction in price if ABC accepts the machine by the end of June which ABC has agreed to. ABC has decided to depreciate the machine by the double declining method. It determines there is salvage value of $5000. The machine will be depreciated over 10 years. Record the journal entry for the purchase of the equipment for this year, and the journal entry that ABC will record next June for the first year of depreciation on the equipment. GENERAL JOURNAL Accounts Date Debit Page 1 Credit From the following information, create the liabilities side of a balance sheet for ABC Company at December 31, 2XXX At the beginning of the year ABC had $500, 500 in Accounts Payable. During the year they had trade purchases on account of $750,000, and payments on account of $625,000. ABC has estimated their costs for utilities for December as $3,000, but they have not received or recorded the invoice as of the end of the year. As of the end of the year, ABC had $2,500 in customer deposits for work not yet completed. ABC has a $1,000,000 interest only loan (classified by the company as longterm debt). The interest for December is not due until January 1. Interest on the note is based on a 5% annual rate. As of the end of the year ABC had $7,500 in wages that employees had earned but had not been paid for. ABC has a bank loan for $500,000. On this note $100,000 will be due within one year. December 1st ABC purchased equipment by securing a shortterm loan for $25,000 due in six months. The loan has an annual interest rate of 6%. No payment was made during December. 500,500+750,000=625,500 accounts payable whether the following liabilites should be classified as shortterm or longterm. Notes Payable not due for three years Salaries Payable Accounts Payable Sales Tax Payable Wages Payable Notes Payable the portion of a five year note, due within the next 12 months Notes Payable the portion of a five year note, due after the next 12 months FICA Taxes Payable Unearned Revenue for a job that will ship next month Accrued Expenses for this month's utility bill Interest Payable Notes Payable due in six months
