Can someone please help me do this accounting project, I am sooo lost! All excel sheets are attached as pictures in order. Thanks so much!! Its due 3/8/20 at midnight est!!
Preston Hill Bed & Breakfast Case Study This case study must be completed in Excel or other spreadsheet program using the formats provided in this workbook. Objectives: 1. Practice the steps involved in the Accounting Cycle 2. Practice electronic spreadsheet skills a. Formulas b. Linking worksheets 3. Practice using data visualization to share results Instructions 1. Using the Chart of Accounts provided, record the transactions in the General Journal. Do not add new accounts. 2. Post the General Journal entries to the General Ledger. 3. Prepare the Unadjusted Trial Balance in the Trial Balance tab 4. Journalize any adjusting entries in the General Journal and post in the General Ledger. Show your calcuations for the adjustments in the Adjustments tab. 5. Prepare the Adjusted Trial Balance in the Trial Balances tab. Also include the Income Statement and Balance Sheet on the Trial Balance Worksheet 6. Prepare IN GOOD FORM the Income Statement, Statement of Shareholders' Equity, and Balance Sheet for for the period of June 1, 2018 - May 31, 2019 in the appropriate tabs. Ignore the effects of income tax. 7. Prepare an appropriate data visualization of financial results (either income statement or balance sheet). 8. Prepare and post the closing entries for the year ended May 31, 2019. 9. Note the following additional information: 1. The business began on June 1, 2018. 2. The period under consideration is the year of June 1, 2018 - May 31, 2019. 3. For the purposes of this assignment, ignore the effects of income tax, property tax, sales tax, and payroll tax. 4. Straight-line depreciation method is used. 5. The supplies on hand May 31, 2019 were $325. 6. The telephone bill for May 2019 for $305 was receive June 2019. 7. The utility bill for May 2019 for $210 was received June 2019. 8. For the purposes of this assignment, consider the note payable to be a current liability 9. The following transactions occurred in the first month of operations and are reflected in the general ledger account balances: June 1, 2018 Jon Edwards and Dan Fogle each invested $20,000 in a corporation to run Preston Hill Bed & Breakfast. Each were issued 2,000 shares of $10 par value common stock June 1, 2018 The partners leased a fully furnished, established, five-bedroom room Bed & Breakfast for 3 years at $3,000 per month. The landlord required a payment of $12,000 for 4 months of rent plus a $3,000 security deposit. June 1, 2018 Purchased a minivan valued at $35,000 with a $10,000 down payment and a 3-year $25,000 note payable with an annual interest rate of 5%. The minivan has a 5-year useful life and no salvage value The monthly payment including principal and interest is $749.27 June 1, 2018 Neither partner can cook but arranged with the restaurant next door (Grace's Diner) to provide breakfast for the guests at $10 per person per day. Grace's Diner will bill the B & B at the end of each month for the meals provided. They must pay Grace's Diner before the 10th of the next month. June 1, 2018 Purchased used deck furniture for $3,000 cash. The furniture has a 6-year useful life and no salvage value June 3, 2018 Ace Computers, a local company, arranged with the B & B to provide rooms for their out of town weekday visitors at a rate of $100 per night. The B & B will bill Ace at the end of the month for rooms used. March 8, 2019 Paid invoice from Insurance company for 4 months of coverage beginning April 1, 2019 for a total of $1,200. April 15, 2019 Purchased suppplies on account from Restaurant Depot in the amount of $875. April 24, 2019 Received reservation deposits as follows: Smith Family - $250 for May 25 - 26, 2019 Harris Family - $500 for May 29 - 30, 2019 Mester Family - $375 for June 3-5, 2019 April 28, 2019 Sent invoice to Ace Computers for the month of April in the amount of $975. Preston Hill Bed & Breakfast Chart of Accounts Account Number Account 101 105 115 120 125 140 150 155 165 Assets Cash Accounts Receivable Supplies Prepaid Rent Prepaid Insurance Van Furniture Accumulated Depreciation Security Deposit 203 208 210 Liabilities Accounts Payable Deferred Room Rental Revenue Notes Payable 310 390 Stockholders' Equity Common Stock Retained Earnings 501 505 Revenue Room Rental Revenue Administrative Fee Revenue 605 610 615 620 640 645 650 655 660 665 670 675 685 690 695 Expenses Advertising Depreciation Expense Dues Food Expense Cleaning and Maintenance Interest Expense Insurance Expense Supplies Expense Printing expense Postage Licenses and fees Telephone Rent Expense Utilities Wage Expense Linens and Uniforms 700 750 Dividends Income Summary Preston Hill Bed & Breakfast Accounting Transactions for the Month of May 2019 Date Description May 1, 2019 Paid May rent in the amount of $3.000. May 2, 2019 Paid $750 for six-months auto insurance policy beginning May 1. May 8, 2019 Received $2,500 in cash and credit card receipts for room rentals for May 1 - 8, 2019. May 9, 2019 Purchased $540 for supplies on account from Jones Supplies Co. May 10, 2019 Paid amount due to Restaurant Depot for supplies purchased April 15, 2019. May 10, 2019 Received payment from Ace Computers for April 2019 billing. May 15, 2019 Paid $900 in wage expense for the period May 1 - May 15, 2019 May 15, 2019 Received $2,200 in cash and credit card receipts for room rentals for May 9 - 15, 2019 May 18, 2019 Paid for cleaning and maintenance in the amount of $425. May 20, 2019 Received reservation deposits as follows: Plourde Family - $250 for May 25-26, 2019 Friedman Family - $200 for May 29 - 30, 2019 Brooks Family - $255 for July 3 - 5, 2019 May 21, 2019 Received $2,050 in cash and credit card receipts for room rentals for May 16 - 21, 2019. May 24, 2019 Paid utilities bill in the amount of $277. May 24, 2019 Paid telephone bill in the amount of $158. May 27, 2019 Refunded Harris Family deposit paid on May 8, 2019 less $50 Administrative Fee. May 30, 2019 Van Payment # 12 - Total $749.27 (Principal = $676.29; Interest = $72.98) May 30, 2019 Paid $1,100 in wage expense for the period May 16 - May 30, 2019. May 30, 2019 Paid credit card in full with the following charges incurred in May 2019: Licenses and Fees - $100; Printing - $275, Dues - $100; Linens and Uniforms - $325, Advertising $560; Postage $125 May 30, 2019 Sent $735 invoice to Ace Computers for rooms rented May 1, 2019 - May 30, 2019. May 30, 2019 Received invoice from Grace's Diner for $380. May 30, 2019 Paid dividends to shareholders in the amount of $800. May 31, 2019 Received $1,850 in cash and credit card receipts for room rentals for May 22 - 31, 2019. Preston Hull Bed and Breakfast General Journal - Month of May 2019 Date Description PR Debit Credit Preston Hill Bed & Breakfast Explanation of Adjustments The Year Ended May 31, 2019 Preston Hill Bed & Breakfast General Ledger Cash Account No. 101 Debit Credit Balance 8,150.00 Explanation PR Date May Debit 1 Accounts Receivable Account No. 105 Debit Credit Balance 975.00 PR Debit Date May Explanation 1 Supplies Account No. 115 Debit Credit Balance 325.00 Explanation PR Debit Date May 1 Prepaid Rent Account No. 120 Debit Credit Balance 12,000.00 Explanation PR Debit Date May 1 Prepaid Insurance Account No. 125 Debit Credit Balance 1,200.00 PR Debit Date May Explanation 1 Van Account No. 140 Debit Credit Balance 35,000.00 Explanation PR Debit Date May 1 Furniture PR Debit Account No. 150 Debit Credit Balance 3,000.00 Date May Explanation 1 Accumulated Depreciation Account No. 155 Credit Credit Balance Date Explanation PR Debit Security Deposit Account No. 165 Debit Credit Balance 3,000.00 PR Debit Date May Explanation 1 Accounts Payable Date May Account No. 203 Credit Credit Balance 875.00 Explanation PR Debit 1 Deferred Room Rental Revenue Account No. 208 Credit Credit Balance 1,125.00 Explanation PR Debit Date May 1 Notes Payable Account No. 210 Credit Credit Balance 17.754.16 PR Debit Date May Explanation 1 Common Stock Account No. 310 Credit Credit Balance 40,000.00 Debit Date May Explanation 1 Retained Earnings Account No. 390 Credit Credit Balance Date Explanation PR Debit Room Rental Revenue Account No. 501 Credit Credit Balance 71,766.97 Explanation PR Debit Date May 1 Administrative Fee Revenue Account No. 505 Credit Credit Balance Date Explanation PR Debit Advertising Account No. 605 Debit Credit Balance 1,560.00 PR Debit Date May Explanation 1 Depreciation Expense Account No. 610 Debit Credit Balance Date Explanation PR Debit Dues Account No. 615 Debit Credit Balance 1,000.00 Explanation PR Debit Date May 1 Food Expense Account No. 620 Debit Credit Balance 4.180.00 PR Debit Date May Explanation 1 $ Cleaning and Maintenance Expense Account No. 625 Debit Credit Balance 2,650.00 PR Debit Date - May Explanation 1 5 Interest expense Account No. 640 Debit Credit Balance 996.13 PR Debit Date 3 May Explanation 1 - Insurance expense Account No. 645 Debit Credit Balance 5 Date Explanation PR Debit B Supplies expense Account No. 650 Debit Credit Balance 5 Date Explanation PR Debit Printing Expense PR Debit Account No. 655 Debit Credit Balance 1,275.00 # Date 5 May Explanation 1 ? Postage expense Account No. 660 Debit Credit Balance 225.00 Explanation PR Debit ! Date 5 May 1 | Licenses and fees Account No. 665 Debit Credit Balance 1,000.00 Explanation PR 3 Date 1 May Debit 1 ) Telephone Account No. 670 Debit Credit Balance 3,350.00 PR Debit 2 Date 3 May Explanation 1 > Rent Account No. 675 Debit Credit Balance 21,000.00 PR Debit | Date ? May Explanation 1 3 Utilities PR Debit Account No. 685 Debit Credit Balance 2,310.00 ) Date | May Explanation 1 107 Wage Expense Account No. 690 Debit Credit Balance 28,000.00 Explanation PR Debit 109 Date 10 May 1 17 Linens and Uniforms 18 Account No. 695 Debit Credit Balance 325.00 PR 19 Date 20 May Debit Explanation 1 26 Dividends Account No. 700 Debit Credit Balance 28 Date Explanation PR Debit 35 Income Summary Account No. 750 37 Date Explanation PR Debit Credit Balance Preston Hill Bed & Breakfast Trial Balance For the Year Ended May 31, 2019 Account Number Account Unadjusted Trial Balance Debit Credit Adjusting Entries Debit Credit Adjusted Trial Balance Income Statement Debit Credit Debit Credit Balance Sheet Debit Credit Closing Adjustments Post-Closing Trial Balance Debit Credit Debit Credit 101 105 115 120 125 140 150 155 165 203 208 210 310 390 501 505 605 610 615 Cash Accounts Receivable Supplies Prepaid Rent Prepaid Insurance Van Furniture Accumulated Depreciation Security Deposits Accounts Payable Deferred Room Rental Revenue Notes Payable Common Stock Retained Earnings Room Rental Revenue Administrative Fee Revenue Advertising Depreciation Expense Dues Expense Food Expense Cleaning and Maintenance Expense Interest Expense Insurance Expense Printing Expense Supplies Postage Licenses and fees Telephone Rent Expense Utilities Wage Expense Linens and Uniforms Dividends Income Summary 620 625 640 645 655 650 660 665 670 675 685 690 695 700 750 Net Income (Loss) Totals Preston Hill Bed & Breakfast Income Statement UE EH Preston Hill Bed & Breakfast Statement of Stockholders' Equity Common Stock Retained Earnings Total Stockholers Equity A B C D E F Preston Hill Bed & Breakfast Balance Sheet Assets Liabilities and Stockholders' Equity Preston Hill Bed & Breakfast Case Study This case study must be completed in Excel or other spreadsheet program using the formats provided in this workbook. Objectives: 1. Practice the steps involved in the Accounting Cycle 2. Practice electronic spreadsheet skills a. Formulas b. Linking worksheets 3. Practice using data visualization to share results Instructions 1. Using the Chart of Accounts provided, record the transactions in the General Journal. Do not add new accounts. 2. Post the General Journal entries to the General Ledger. 3. Prepare the Unadjusted Trial Balance in the Trial Balance tab 4. Journalize any adjusting entries in the General Journal and post in the General Ledger. Show your calcuations for the adjustments in the Adjustments tab. 5. Prepare the Adjusted Trial Balance in the Trial Balances tab. Also include the Income Statement and Balance Sheet on the Trial Balance Worksheet 6. Prepare IN GOOD FORM the Income Statement, Statement of Shareholders' Equity, and Balance Sheet for for the period of June 1, 2018 - May 31, 2019 in the appropriate tabs. Ignore the effects of income tax. 7. Prepare an appropriate data visualization of financial results (either income statement or balance sheet). 8. Prepare and post the closing entries for the year ended May 31, 2019. 9. Note the following additional information: 1. The business began on June 1, 2018. 2. The period under consideration is the year of June 1, 2018 - May 31, 2019. 3. For the purposes of this assignment, ignore the effects of income tax, property tax, sales tax, and payroll tax. 4. Straight-line depreciation method is used. 5. The supplies on hand May 31, 2019 were $325. 6. The telephone bill for May 2019 for $305 was receive June 2019. 7. The utility bill for May 2019 for $210 was received June 2019. 8. For the purposes of this assignment, consider the note payable to be a current liability 9. The following transactions occurred in the first month of operations and are reflected in the general ledger account balances: June 1, 2018 Jon Edwards and Dan Fogle each invested $20,000 in a corporation to run Preston Hill Bed & Breakfast. Each were issued 2,000 shares of $10 par value common stock June 1, 2018 The partners leased a fully furnished, established, five-bedroom room Bed & Breakfast for 3 years at $3,000 per month. The landlord required a payment of $12,000 for 4 months of rent plus a $3,000 security deposit. June 1, 2018 Purchased a minivan valued at $35,000 with a $10,000 down payment and a 3-year $25,000 note payable with an annual interest rate of 5%. The minivan has a 5-year useful life and no salvage value The monthly payment including principal and interest is $749.27 June 1, 2018 Neither partner can cook but arranged with the restaurant next door (Grace's Diner) to provide breakfast for the guests at $10 per person per day. Grace's Diner will bill the B & B at the end of each month for the meals provided. They must pay Grace's Diner before the 10th of the next month. June 1, 2018 Purchased used deck furniture for $3,000 cash. The furniture has a 6-year useful life and no salvage value June 3, 2018 Ace Computers, a local company, arranged with the B & B to provide rooms for their out of town weekday visitors at a rate of $100 per night. The B & B will bill Ace at the end of the month for rooms used. March 8, 2019 Paid invoice from Insurance company for 4 months of coverage beginning April 1, 2019 for a total of $1,200. April 15, 2019 Purchased suppplies on account from Restaurant Depot in the amount of $875. April 24, 2019 Received reservation deposits as follows: Smith Family - $250 for May 25 - 26, 2019 Harris Family - $500 for May 29 - 30, 2019 Mester Family - $375 for June 3-5, 2019 April 28, 2019 Sent invoice to Ace Computers for the month of April in the amount of $975. Preston Hill Bed & Breakfast Chart of Accounts Account Number Account 101 105 115 120 125 140 150 155 165 Assets Cash Accounts Receivable Supplies Prepaid Rent Prepaid Insurance Van Furniture Accumulated Depreciation Security Deposit 203 208 210 Liabilities Accounts Payable Deferred Room Rental Revenue Notes Payable 310 390 Stockholders' Equity Common Stock Retained Earnings 501 505 Revenue Room Rental Revenue Administrative Fee Revenue 605 610 615 620 640 645 650 655 660 665 670 675 685 690 695 Expenses Advertising Depreciation Expense Dues Food Expense Cleaning and Maintenance Interest Expense Insurance Expense Supplies Expense Printing expense Postage Licenses and fees Telephone Rent Expense Utilities Wage Expense Linens and Uniforms 700 750 Dividends Income Summary Preston Hill Bed & Breakfast Accounting Transactions for the Month of May 2019 Date Description May 1, 2019 Paid May rent in the amount of $3.000. May 2, 2019 Paid $750 for six-months auto insurance policy beginning May 1. May 8, 2019 Received $2,500 in cash and credit card receipts for room rentals for May 1 - 8, 2019. May 9, 2019 Purchased $540 for supplies on account from Jones Supplies Co. May 10, 2019 Paid amount due to Restaurant Depot for supplies purchased April 15, 2019. May 10, 2019 Received payment from Ace Computers for April 2019 billing. May 15, 2019 Paid $900 in wage expense for the period May 1 - May 15, 2019 May 15, 2019 Received $2,200 in cash and credit card receipts for room rentals for May 9 - 15, 2019 May 18, 2019 Paid for cleaning and maintenance in the amount of $425. May 20, 2019 Received reservation deposits as follows: Plourde Family - $250 for May 25-26, 2019 Friedman Family - $200 for May 29 - 30, 2019 Brooks Family - $255 for July 3 - 5, 2019 May 21, 2019 Received $2,050 in cash and credit card receipts for room rentals for May 16 - 21, 2019. May 24, 2019 Paid utilities bill in the amount of $277. May 24, 2019 Paid telephone bill in the amount of $158. May 27, 2019 Refunded Harris Family deposit paid on May 8, 2019 less $50 Administrative Fee. May 30, 2019 Van Payment # 12 - Total $749.27 (Principal = $676.29; Interest = $72.98) May 30, 2019 Paid $1,100 in wage expense for the period May 16 - May 30, 2019. May 30, 2019 Paid credit card in full with the following charges incurred in May 2019: Licenses and Fees - $100; Printing - $275, Dues - $100; Linens and Uniforms - $325, Advertising $560; Postage $125 May 30, 2019 Sent $735 invoice to Ace Computers for rooms rented May 1, 2019 - May 30, 2019. May 30, 2019 Received invoice from Grace's Diner for $380. May 30, 2019 Paid dividends to shareholders in the amount of $800. May 31, 2019 Received $1,850 in cash and credit card receipts for room rentals for May 22 - 31, 2019. Preston Hull Bed and Breakfast General Journal - Month of May 2019 Date Description PR Debit Credit Preston Hill Bed & Breakfast Explanation of Adjustments The Year Ended May 31, 2019 Preston Hill Bed & Breakfast General Ledger Cash Account No. 101 Debit Credit Balance 8,150.00 Explanation PR Date May Debit 1 Accounts Receivable Account No. 105 Debit Credit Balance 975.00 PR Debit Date May Explanation 1 Supplies Account No. 115 Debit Credit Balance 325.00 Explanation PR Debit Date May 1 Prepaid Rent Account No. 120 Debit Credit Balance 12,000.00 Explanation PR Debit Date May 1 Prepaid Insurance Account No. 125 Debit Credit Balance 1,200.00 PR Debit Date May Explanation 1 Van Account No. 140 Debit Credit Balance 35,000.00 Explanation PR Debit Date May 1 Furniture PR Debit Account No. 150 Debit Credit Balance 3,000.00 Date May Explanation 1 Accumulated Depreciation Account No. 155 Credit Credit Balance Date Explanation PR Debit Security Deposit Account No. 165 Debit Credit Balance 3,000.00 PR Debit Date May Explanation 1 Accounts Payable Date May Account No. 203 Credit Credit Balance 875.00 Explanation PR Debit 1 Deferred Room Rental Revenue Account No. 208 Credit Credit Balance 1,125.00 Explanation PR Debit Date May 1 Notes Payable Account No. 210 Credit Credit Balance 17.754.16 PR Debit Date May Explanation 1 Common Stock Account No. 310 Credit Credit Balance 40,000.00 Debit Date May Explanation 1 Retained Earnings Account No. 390 Credit Credit Balance Date Explanation PR Debit Room Rental Revenue Account No. 501 Credit Credit Balance 71,766.97 Explanation PR Debit Date May 1 Administrative Fee Revenue Account No. 505 Credit Credit Balance Date Explanation PR Debit Advertising Account No. 605 Debit Credit Balance 1,560.00 PR Debit Date May Explanation 1 Depreciation Expense Account No. 610 Debit Credit Balance Date Explanation PR Debit Dues Account No. 615 Debit Credit Balance 1,000.00 Explanation PR Debit Date May 1 Food Expense Account No. 620 Debit Credit Balance 4.180.00 PR Debit Date May Explanation 1 $ Cleaning and Maintenance Expense Account No. 625 Debit Credit Balance 2,650.00 PR Debit Date - May Explanation 1 5 Interest expense Account No. 640 Debit Credit Balance 996.13 PR Debit Date 3 May Explanation 1 - Insurance expense Account No. 645 Debit Credit Balance 5 Date Explanation PR Debit B Supplies expense Account No. 650 Debit Credit Balance 5 Date Explanation PR Debit Printing Expense PR Debit Account No. 655 Debit Credit Balance 1,275.00 # Date 5 May Explanation 1 ? Postage expense Account No. 660 Debit Credit Balance 225.00 Explanation PR Debit ! Date 5 May 1 | Licenses and fees Account No. 665 Debit Credit Balance 1,000.00 Explanation PR 3 Date 1 May Debit 1 ) Telephone Account No. 670 Debit Credit Balance 3,350.00 PR Debit 2 Date 3 May Explanation 1 > Rent Account No. 675 Debit Credit Balance 21,000.00 PR Debit | Date ? May Explanation 1 3 Utilities PR Debit Account No. 685 Debit Credit Balance 2,310.00 ) Date | May Explanation 1 107 Wage Expense Account No. 690 Debit Credit Balance 28,000.00 Explanation PR Debit 109 Date 10 May 1 17 Linens and Uniforms 18 Account No. 695 Debit Credit Balance 325.00 PR 19 Date 20 May Debit Explanation 1 26 Dividends Account No. 700 Debit Credit Balance 28 Date Explanation PR Debit 35 Income Summary Account No. 750 37 Date Explanation PR Debit Credit Balance Preston Hill Bed & Breakfast Trial Balance For the Year Ended May 31, 2019 Account Number Account Unadjusted Trial Balance Debit Credit Adjusting Entries Debit Credit Adjusted Trial Balance Income Statement Debit Credit Debit Credit Balance Sheet Debit Credit Closing Adjustments Post-Closing Trial Balance Debit Credit Debit Credit 101 105 115 120 125 140 150 155 165 203 208 210 310 390 501 505 605 610 615 Cash Accounts Receivable Supplies Prepaid Rent Prepaid Insurance Van Furniture Accumulated Depreciation Security Deposits Accounts Payable Deferred Room Rental Revenue Notes Payable Common Stock Retained Earnings Room Rental Revenue Administrative Fee Revenue Advertising Depreciation Expense Dues Expense Food Expense Cleaning and Maintenance Expense Interest Expense Insurance Expense Printing Expense Supplies Postage Licenses and fees Telephone Rent Expense Utilities Wage Expense Linens and Uniforms Dividends Income Summary 620 625 640 645 655 650 660 665 670 675 685 690 695 700 750 Net Income (Loss) Totals Preston Hill Bed & Breakfast Income Statement UE EH Preston Hill Bed & Breakfast Statement of Stockholders' Equity Common Stock Retained Earnings Total Stockholers Equity A B C D E F Preston Hill Bed & Breakfast Balance Sheet Assets Liabilities and Stockholders' Equity
