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 bull 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 28 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 41 655 -2 660 43 665 44 670 45 675 46 685 47 690 Expenses Advertising Depreciation Expense Dues Food Expense Cleaning and Maintenance Interest Expense Insurance Expense Supplies Expanse Printing expense Postage Licenses and fees Telephone Rent Expense Uties Wage Expense Linens and Uniforms 50 700 Dividends Income Summary 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 18, 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 3-5, 2019 May 21, 2018 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 $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 & 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 bull 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 28 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 41 655 -2 660 43 665 44 670 45 675 46 685 47 690 Expenses Advertising Depreciation Expense Dues Food Expense Cleaning and Maintenance Interest Expense Insurance Expense Supplies Expanse Printing expense Postage Licenses and fees Telephone Rent Expense Uties Wage Expense Linens and Uniforms 50 700 Dividends Income Summary 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 18, 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 3-5, 2019 May 21, 2018 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 $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