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 Adiustrients 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. 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 quests 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. 5 Account 6 Number Account 9 101 10 105 11 115 12 120 13 125 14 140 15 150 76 155 17 165 Assets Cash Accounts Receivable Supplies Prepaid Rent Prepaid Insurance Van Furniture Accumulated Depreciation Security Deposit 20 203 21 208 22 210 Liabilities Accounts Payable Deferred Room Rental Revenue Notes Payable 225 310 26 390 Stockholders' Equity Common Stock Retained Earnings 29 501 30 505 Revenue Room Rental Revenue Administrative Fee Revenue 33 605 34 610 35 615 38 620 37 625 38 640 39 645 40 650 1655 42 660 43 665 44 670 45 675 46 685 47 690 48 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 Uties Wage Expense Linens and Uniforms 50 700 Dividends Income Summary 23 Instructions Accounts Transaction 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, 2018 Received $2200 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 My 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 - $876.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 $580, 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 Hill 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 Debit Date May 1 Accounts Receivable Explanation PR Account No. 105 Debit Credit Balance 975.00 Debit Date May 1 Account No. 115 Debit Credit Balance 325.00 Date May Explanation PR Debit 1 Prepaid Rent Explanation PR Date May Debit Account No. 120 Debit Credit Balance 12,000.00 1 Prepaid Insurance PR Account No. 125 Debit Credit Balance 1,200.00 Explanation Debit Date May 1 Prepaid Insurance Account No. 125 Debit Credit Balance 1.200.00 Date May Explanation PR Debit 1 Explanation PR Debit Account No 140 Debit Credit Balance 35.000.00 Date May Explanation PR Debit Date May Account No. 150 Debit Credit Balance 3.000.00 Accumulated Depreciation Account No. 155 Credit Credit Balance Date Explanation PR Debit Security Deposit PR Debit Account No. 165 Debit Credit Balance 3.000.00 Date May Explanation 1 Accounts Payable Account No. 201 Credit Credit Balance 875.00 Explanation Debit Date May Deferred Room Rental Revenue Explanation PR Date May Debit Account No. 203 Credit Credit Balance 1.125.00 1 Notes Payablo Account No. 210 Notes Payable Account No. 210 Credit Credit Balance 17,754.16 Date Explanation PR Debit may 1 Common Stock Account No. 310 Credit Credit Balance 40.000.00 Explanation PR Date May Debit 1 Retained Earnings Account Ne Credit Balance Date Explanation Debit Credit Debit Date May Explanation 1 Account No 501 Credit Credit Balance 71,766 97 Administrative Fee Revenue Account No. 505 Credit Credit Balance Date Explanation Debit Advertising Explanation Credit Date May Debi Balance 1.560.00 1 Depreciation Expense Account No. 610 Debe Date Explanation Debit Credit Balance Account No. 615 Debit Explanation PR Account No. 615 Debit Credit Balance 1.000.00 Debit to Explanation Account No. 620 Debit Credit Balance 4. 18000 PR Debit aning and Maintenance Expense te Account No. 625 Debit Credit Balance 2.650.00 Explanation PR Debit prest expense Account No. 640 Debit Credit Balance 996.13 Explanation PR Debit surance expense Account No. 645 to Explanation PR Debit Balance Debit Credit polies expense Account No. 650 Debit Credit Balance Explanation PR Debit Account No. 655 Explanation Debe Credit Balance 1 275.00 stage expense Account No. 660 Debit Credit Balance 225.00 to Explanation PR Debit 1 Postage expense Account No. GEO Deble Credit Balance Date Explanation PR Debit May 1 Licenses and fees Explanation PR Account No. 665 Debet Credit Balance 100000 Debit Date May 1 Date Account No. 670 Debe C redit Balance 335000 Explanation PR Debi Date Account No. 675 Deble Credit Balance 21 000 00 Explanation PR Debit Account No. 685 Debit Credit Balance 231000 Explanation Date May PR Debit 1 Wage Expense Date Account No. 690 Det Credit Balance 200000 Explanation R Debit May Linens and Uniforms Date Account No. 695 Deble Credit Balance 325 00 Explanation PR Debit Dividends Account No. 700 Date Explanation PR Debit Balance Debit Credit Dividend: Account No. 700 Debit Credit Balance Date Explanation PR Debit Income Summary Account No. 750 Date Explanation PR Debit Credit Balance Preston Hill Bed & Breakfast Trial Balance For the Year Ended May 31, 2019 Unadi usted Trial Balance Debit Credit Austing Entries Debt Credit Adjusted Trial Balance income Statement Debit Credit Debt Credit B alance Sheet Debit Credit Closing Adjustments Post-Closing Trial Balance Debt Credit Debit Credit Accounts Receivable 120 925 140 150 155 165 203 208 210 310 390 501 505 Prepaid Rent Prepaid Ingurence Van Furniture Accumulated Depreciation Security Deposits Accounts Payable Deferred Room Rental Revenue Notes Payable Common Stock Retained Earrings Room Rental Revenue Administrative Fee Revenue Advertising Depreciation Expense Dues Expense Food Expense Cleaning and Maintenance Expense Interest Expense Insurance Expense Printing Expense 610 615 620 625 640 G4S 655 670 685 20 695 700 750 Telephone Rent Expense USD Wage en Linens and Unifoms Dividends Income Summary Totals Preston Hill Bed & Breakfast Income Statement 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 Adiustrients 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. 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 quests 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. 5 Account 6 Number Account 9 101 10 105 11 115 12 120 13 125 14 140 15 150 76 155 17 165 Assets Cash Accounts Receivable Supplies Prepaid Rent Prepaid Insurance Van Furniture Accumulated Depreciation Security Deposit 20 203 21 208 22 210 Liabilities Accounts Payable Deferred Room Rental Revenue Notes Payable 225 310 26 390 Stockholders' Equity Common Stock Retained Earnings 29 501 30 505 Revenue Room Rental Revenue Administrative Fee Revenue 33 605 34 610 35 615 38 620 37 625 38 640 39 645 40 650 1655 42 660 43 665 44 670 45 675 46 685 47 690 48 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 Uties Wage Expense Linens and Uniforms 50 700 Dividends Income Summary 23 Instructions Accounts Transaction 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, 2018 Received $2200 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 My 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 - $876.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 $580, 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 Hill 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 Debit Date May 1 Accounts Receivable Explanation PR Account No. 105 Debit Credit Balance 975.00 Debit Date May 1 Account No. 115 Debit Credit Balance 325.00 Date May Explanation PR Debit 1 Prepaid Rent Explanation PR Date May Debit Account No. 120 Debit Credit Balance 12,000.00 1 Prepaid Insurance PR Account No. 125 Debit Credit Balance 1,200.00 Explanation Debit Date May 1 Prepaid Insurance Account No. 125 Debit Credit Balance 1.200.00 Date May Explanation PR Debit 1 Explanation PR Debit Account No 140 Debit Credit Balance 35.000.00 Date May Explanation PR Debit Date May Account No. 150 Debit Credit Balance 3.000.00 Accumulated Depreciation Account No. 155 Credit Credit Balance Date Explanation PR Debit Security Deposit PR Debit Account No. 165 Debit Credit Balance 3.000.00 Date May Explanation 1 Accounts Payable Account No. 201 Credit Credit Balance 875.00 Explanation Debit Date May Deferred Room Rental Revenue Explanation PR Date May Debit Account No. 203 Credit Credit Balance 1.125.00 1 Notes Payablo Account No. 210 Notes Payable Account No. 210 Credit Credit Balance 17,754.16 Date Explanation PR Debit may 1 Common Stock Account No. 310 Credit Credit Balance 40.000.00 Explanation PR Date May Debit 1 Retained Earnings Account Ne Credit Balance Date Explanation Debit Credit Debit Date May Explanation 1 Account No 501 Credit Credit Balance 71,766 97 Administrative Fee Revenue Account No. 505 Credit Credit Balance Date Explanation Debit Advertising Explanation Credit Date May Debi Balance 1.560.00 1 Depreciation Expense Account No. 610 Debe Date Explanation Debit Credit Balance Account No. 615 Debit Explanation PR Account No. 615 Debit Credit Balance 1.000.00 Debit to Explanation Account No. 620 Debit Credit Balance 4. 18000 PR Debit aning and Maintenance Expense te Account No. 625 Debit Credit Balance 2.650.00 Explanation PR Debit prest expense Account No. 640 Debit Credit Balance 996.13 Explanation PR Debit surance expense Account No. 645 to Explanation PR Debit Balance Debit Credit polies expense Account No. 650 Debit Credit Balance Explanation PR Debit Account No. 655 Explanation Debe Credit Balance 1 275.00 stage expense Account No. 660 Debit Credit Balance 225.00 to Explanation PR Debit 1 Postage expense Account No. GEO Deble Credit Balance Date Explanation PR Debit May 1 Licenses and fees Explanation PR Account No. 665 Debet Credit Balance 100000 Debit Date May 1 Date Account No. 670 Debe C redit Balance 335000 Explanation PR Debi Date Account No. 675 Deble Credit Balance 21 000 00 Explanation PR Debit Account No. 685 Debit Credit Balance 231000 Explanation Date May PR Debit 1 Wage Expense Date Account No. 690 Det Credit Balance 200000 Explanation R Debit May Linens and Uniforms Date Account No. 695 Deble Credit Balance 325 00 Explanation PR Debit Dividends Account No. 700 Date Explanation PR Debit Balance Debit Credit Dividend: Account No. 700 Debit Credit Balance Date Explanation PR Debit Income Summary Account No. 750 Date Explanation PR Debit Credit Balance Preston Hill Bed & Breakfast Trial Balance For the Year Ended May 31, 2019 Unadi usted Trial Balance Debit Credit Austing Entries Debt Credit Adjusted Trial Balance income Statement Debit Credit Debt Credit B alance Sheet Debit Credit Closing Adjustments Post-Closing Trial Balance Debt Credit Debit Credit Accounts Receivable 120 925 140 150 155 165 203 208 210 310 390 501 505 Prepaid Rent Prepaid Ingurence Van Furniture Accumulated Depreciation Security Deposits Accounts Payable Deferred Room Rental Revenue Notes Payable Common Stock Retained Earrings Room Rental Revenue Administrative Fee Revenue Advertising Depreciation Expense Dues Expense Food Expense Cleaning and Maintenance Expense Interest Expense Insurance Expense Printing Expense 610 615 620 625 640 G4S 655 670 685 20 695 700 750 Telephone Rent Expense USD Wage en Linens and Unifoms Dividends Income Summary Totals Preston Hill Bed & Breakfast Income Statement Preston Hill Bed & Breakfast Balance Sheet Assets Liabilities and Stockholders' Equity