This is an example excel promblem and I truly have no idea how to complete. I understand it's long so if you need anything extra just let me know! I dont think you can message through here however? Appreciate the help!!
Dear Newbie, Welcome to Hampton! My name is Julio Antoni, your supervisor. We believe the best way for you to get familiar with what you are going to be doing here is to throw you right in, so we are asking you to start working on the books for us right away. For this project you will complete Hampton's entire accounting cycle. You will be required to record entries in a journal, post to a ledger, complete a trial balance, and prepare financial statements. Transactions will include day to day operations, adjusting entries, dividends and closing entries. All of the work will be completed in the provided Excel workbook (make sure you save the file on to your computer!) Company Information Hampton Accounting Service Inc. is a public corporation that has been in business for 1 year. The company is authorized to issue 100,000 shares of common stock at $1 par value. The company provides accounting services and sells accounting software. To date the company has issued 20,000 shares of common stock, which are still outstanding. The issued shares were sold at $10 per share. A building and furnishings were purchased at the start of the year. The cost of the building was $140,000 and the cost of the furnishings was $30,000. The depreciation will be recorded annually. The estimated useful life of the building is 20 years with a residual value of $10,000. The company uses the straight line method to depreciate the building. The estimated useful life of the furnishings is 5 years with a residual value of $2,500. The company uses the Double Declining Balance method to depreciate furnishings. The current inventory consists of 30 units of software at a cost of $140.00 each. Inventory is costed using LIFO and the perpetual inventory system. The company uses an allowance method to account for uncollectible accounts. It is estimated that 1% of net credit revenue will be uncollectible, adjusted monthly. Employees are paid $2,500 salary twice a month, on the 16 for the first half of the month and on the 2 of the following month for the last half of the month. Ignore income tax calculations. 1. Opening balances. The opening balances for select accounts are provided in the appropriate ledger accounts. 2. Record the following transactions in the journal and post to the ledger: 12/02/xx- Purchased 10 Units of Software on Account for $1,300 12/02/xx-Paid employees Salaries owed 12/02/xx- Provided Accounting Service on Account, $3,200 12/05/xx-Purchased Computer Equipment on Account, $4,800 Note: The equipment has an estimated useful life of 4 years, residual value of $50 and uses the DDB method. 505 inbaditontin mutta Sento Post 12/08/xx- Purchased Supplies for $800 cash 12/10/xx Received $3,000 payment on account 12/11/xx-Sold 15 units of Inventory on Account, $4,500 Note: This transaction requires two journal entries. The first entry should deal with the sale, and the second should deal with the inventory 12/11/xx-Made payment on account, $1,000 12/12/xx- Purchased 20 units of Inventory on Account, $2,400 12/14/xx-Received a bill for Advertising and paid it, $250 12/15/x- Provided Accounting service for Cash, $8,000 12/16/xx- Pald employees salary for Dec. 1-15 12/17/xx. Provided Accounting Service on Account. 56,000 1/19/ Sold 10 min inventor on Arrunt , $3,000 Note: This transaction requires two journal entries. The first entry should deal with the sale, and the second should deal with the inventory 12/22/xx-Received $2,500 payment on Account 12/23/xx-Made payment on Account, $2,500 12/24/xx-Received and paid monthly Utility bills, $850 12/27/xx-Received $2,000 cash in advance for accounting services 12/29/xx- Provided Accounting Service for Cash, $5,000 12/30/xx- Issued 1,000 shares of Common Stock at $15 per share 12/31/xx-Declared dividends of $1.50 to outstanding shareholders as of 12/31/XX. Note: Be sure to debit retained earnings and credit dividends payable. 3. Prepare a trial balance to verify that total debits equal total credits. 4. Record the following adjustments in the journal and in the trial balance. DO NOT post the adjusting entries to the ledger. Instead, post them directly as adjustments to the trial balance. 12/31/xx-Employees salary for Dec. 16-31 12/31/16-Depreciation on Building, use SL method (calculate for entire year) 12/31/xx-Depreciation on Furnishings, use DDB method (calculate for entire year) 12/31/xx-Depreciation on Computer Equipment, use DDB method (calculate for one month) 12/31/xx- Supplies on hand equal $1,625 12/31/xx- Inventory count shows 33 units, some damaged units were disposed of during the month 12/31/xx- $500 of advance payment was earned in December 12/31/xx- Uncollectible Account expense for December 5. Prepare an Adjusted trial balance to verify debits equal credits at the end of the month 6. Prepare the 3 main financial statements for the year ending December 31, 20xx. 1 HAMPTON ACCOUNTING SERVICE IN 2 Chart of Accounts Note: This chart of accounts is complete for every account you will need for this 3 assignment. 4 5 Acct # Description 6 7 100 Cash 8 110 Accounts Receivable 9 115 Allowance for Unc. Accounts 10 120 Supplies 11 130 Inventory 12 150 Building 13 151 Accumulated Depreciation- Building 14 155 Furnishings 15 156 Accumulated Depreciation - Furnishings 16 160 Office Equipment 17 161 Accumulated Depreciation- Off. Equip. 18 200 Accounts Payable 19 210 Unearned Service Revenue 1 E 16 17 1 B A 13 151 Accumulated Depreciation- Building 14 155 Furnishings 15 156 Accumulated Depreciation- Furnishings 160 Office Equipment 161 Accumulated Depreciation- Off. Equip. 18 200 Accounts Payable 19 210 Unearned Service Revenue 20 215 Salary Payable 21 220 Dividends Payable 22 300 Common Stock, $1 par value 23 305 Additional Paid in Capital 24 310 Retained Earnings 25 400 Service Revenue 26 410 Sales Revenue 27 500 Advertising Expense 28 510 Depreciation Expense 29 520 Repairs & Maintenance Expense 30 530 Supplies Expense 31 540 Uncollectible Accounts Expense 32 550 Utilities Expense 33 560 Cost of Goods Sold 34 570 Salary Expense 2 3 3 25 NOTE: Do not proceed until this page has no errors. Errors will be carried through the rest of the assignment, so you will receive a poor score. Hint: It will be much easier to post these to the Ledger if you use the "Filter" function - below is a link to a video showing how to use filters in case you are un- familiar with it. Excel 2013: Filtering Data 5 6 7 Date 8 9 10 11 12 13 Accounts and Explanation Center here) Center here] Credit Post Re Debit n/a n/a enter here) Center here) n/a n/a 15 16 (enter here) (enter here) n/a n/a Center here) n/a B E n/a 18 [enter here] 19 20 [enter here) [enter here] n/a n/a [enter here) [enter here! n/a n/a [enter here] Center here) n/a n/a 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 (enter here) [enter here! n/a n/a Center here) (enter here) n/a n/a [enter here! Center here) n/a n/a [enter here) Center here! n/a B D E 41 [enter here] [enter here] n/a n/a 42 43 44 45 (enter here) [enter here] n/a n/a 46 47 [enter here] [enter here) 48 49 n/a n/a 50 51 enter here] (enter here] n/a n/a 52 53 54 [enter here) [enter here! n/a n/a 55 56 57 58 59 60 [enter here) (enter here) n/a n/a [enter here) [enter here] n/a n/a 61 62 63 lenter here! Tenter here pla A B D E E 65 [enter here] [enter here) n/a n/a 66 67 68 [enter here] [enter here) n/a n/a 69 70 72 73 [enter here] [enter here) Center here) [enter here] [enter here) n/a n/a n/a n/a n/a 74 75 76 The following adjustments SHOULD NOT be posted to the Ledger; instead, they should be posted directly to the Trial Balance 77 78 79 Ad) enter here! (enter here) 80 81 n/a n/a 82 Adj [enter here! (enter here) n/a BE B D E 81 82 Adj 83 enter here) [enter here] n/a n/a 84 85 Adj 86 [enter here) [enter here] n/a n/a 87 88 Adj (enter here] (enter here) n/a n/a 89 90 91 Adj (enter here) [enter here) n/a n/a 92 93 94 Adi [enter here] [enter here) n/a n/a 95 96 97 Adj [enter here! (enter here) n/a n/a 99 99 100 (enter here] enter here) 101 Ad n/a n/a 1 HAMPTON ACCOUNTING SERVICE INC. 2 Income Statement 3 For the Year Ending December 31, 2020 $ $ 4 5 Service Revenue 6 Sales Revenue 7 Net Revenue 8 Cost of Goods Sold 9 Gross Profit 10 Operating Expenses 11 Advertising Expense 12 Depreciation Expense 13 Repairs & Maintenance 14 Supplies Expense 15 Uncollectible Accts Expense 16 Utilities Expense 17 Salary Expense 18 Total Operating Expense 19 Net Income 20 21 $ $ F A B D E 1 HAMPTON ACCOUNTING SERVICE INC. 2 Statement of Retained Earnings 3 For the Year Ending December 31, 2020 4 5 Retained Earnings, January 1 6 Add: Net Income 7 $ Less: Dividends 9 Retained Earnings, December 31 $ 10 11 12 13 $ 8 $ 14 15 16 17 18 HAMPTON ACCOUNTING SERVICE IN Balance Sheet Hint: List the accounts in the Balance Sheet in the same order as in the Chart of Accoun Hint: If the number is going to be modified, it should first appear in the left column (Col C). If it is NOT going to be modified, it should appear in the center column (Column D). T will appear in far right coumn (Column E). 5 December 31, 2020 7 8 Assets 9 Current: 10 $ $ 12 13 14 Cash Accounts Receivable Less: Allowance for Unc. Accounts enter here! Center here) Total Current Assets $ $ $ $ $ $ A B 15 Total Current Assets 16 Long-Term: 17 [enter here] 18 [enter here) 19 [enter here) 20 Center here) 21 [enter here) 22 [enter here) 23 Total Long-term Assets 24 Total Assets 25 26 Liabilities 27 Current: 28 Accounts Payable 29 Center here) 30 [enter here! 31 Center here! 32 Total Current Liabilities 33 Stockholders Equity 24 Center here! 35 Additional Paid in Capital 36 Retained Earnings 37 Total abilities & Stockholders Equity $ $ S $ $ $ $ $ $ $ [enter here) 3 [enter here] 0 [enter here) 1 (enter here) 02 [enter here) 22 Total Long-term Assets 24 Total Assets 25 26 Liabilities 27 Current: 28 Accounts Payable 29 (enter here) 30 (enter here) 31 (enter here) 32 Total Current Liabilities 33 Stockholders Equity Center here! 25 Additional Paid in Capital 36 Retained Earnings 37 Total Liabilities & Stockholders Equity 39 $ Dear Newbie, Welcome to Hampton! My name is Julio Antoni, your supervisor. We believe the best way for you to get familiar with what you are going to be doing here is to throw you right in, so we are asking you to start working on the books for us right away. For this project you will complete Hampton's entire accounting cycle. You will be required to record entries in a journal, post to a ledger, complete a trial balance, and prepare financial statements. Transactions will include day to day operations, adjusting entries, dividends and closing entries. All of the work will be completed in the provided Excel workbook (make sure you save the file on to your computer!) Company Information Hampton Accounting Service Inc. is a public corporation that has been in business for 1 year. The company is authorized to issue 100,000 shares of common stock at $1 par value. The company provides accounting services and sells accounting software. To date the company has issued 20,000 shares of common stock, which are still outstanding. The issued shares were sold at $10 per share. A building and furnishings were purchased at the start of the year. The cost of the building was $140,000 and the cost of the furnishings was $30,000. The depreciation will be recorded annually. The estimated useful life of the building is 20 years with a residual value of $10,000. The company uses the straight line method to depreciate the building. The estimated useful life of the furnishings is 5 years with a residual value of $2,500. The company uses the Double Declining Balance method to depreciate furnishings. The current inventory consists of 30 units of software at a cost of $140.00 each. Inventory is costed using LIFO and the perpetual inventory system. The company uses an allowance method to account for uncollectible accounts. It is estimated that 1% of net credit revenue will be uncollectible, adjusted monthly. Employees are paid $2,500 salary twice a month, on the 16 for the first half of the month and on the 2 of the following month for the last half of the month. Ignore income tax calculations. 1. Opening balances. The opening balances for select accounts are provided in the appropriate ledger accounts. 2. Record the following transactions in the journal and post to the ledger: 12/02/xx- Purchased 10 Units of Software on Account for $1,300 12/02/xx-Paid employees Salaries owed 12/02/xx- Provided Accounting Service on Account, $3,200 12/05/xx-Purchased Computer Equipment on Account, $4,800 Note: The equipment has an estimated useful life of 4 years, residual value of $50 and uses the DDB method. 505 inbaditontin mutta Sento Post 12/08/xx- Purchased Supplies for $800 cash 12/10/xx Received $3,000 payment on account 12/11/xx-Sold 15 units of Inventory on Account, $4,500 Note: This transaction requires two journal entries. The first entry should deal with the sale, and the second should deal with the inventory 12/11/xx-Made payment on account, $1,000 12/12/xx- Purchased 20 units of Inventory on Account, $2,400 12/14/xx-Received a bill for Advertising and paid it, $250 12/15/x- Provided Accounting service for Cash, $8,000 12/16/xx- Pald employees salary for Dec. 1-15 12/17/xx. Provided Accounting Service on Account. 56,000 1/19/ Sold 10 min inventor on Arrunt , $3,000 Note: This transaction requires two journal entries. The first entry should deal with the sale, and the second should deal with the inventory 12/22/xx-Received $2,500 payment on Account 12/23/xx-Made payment on Account, $2,500 12/24/xx-Received and paid monthly Utility bills, $850 12/27/xx-Received $2,000 cash in advance for accounting services 12/29/xx- Provided Accounting Service for Cash, $5,000 12/30/xx- Issued 1,000 shares of Common Stock at $15 per share 12/31/xx-Declared dividends of $1.50 to outstanding shareholders as of 12/31/XX. Note: Be sure to debit retained earnings and credit dividends payable. 3. Prepare a trial balance to verify that total debits equal total credits. 4. Record the following adjustments in the journal and in the trial balance. DO NOT post the adjusting entries to the ledger. Instead, post them directly as adjustments to the trial balance. 12/31/xx-Employees salary for Dec. 16-31 12/31/16-Depreciation on Building, use SL method (calculate for entire year) 12/31/xx-Depreciation on Furnishings, use DDB method (calculate for entire year) 12/31/xx-Depreciation on Computer Equipment, use DDB method (calculate for one month) 12/31/xx- Supplies on hand equal $1,625 12/31/xx- Inventory count shows 33 units, some damaged units were disposed of during the month 12/31/xx- $500 of advance payment was earned in December 12/31/xx- Uncollectible Account expense for December 5. Prepare an Adjusted trial balance to verify debits equal credits at the end of the month 6. Prepare the 3 main financial statements for the year ending December 31, 20xx. 1 HAMPTON ACCOUNTING SERVICE IN 2 Chart of Accounts Note: This chart of accounts is complete for every account you will need for this 3 assignment. 4 5 Acct # Description 6 7 100 Cash 8 110 Accounts Receivable 9 115 Allowance for Unc. Accounts 10 120 Supplies 11 130 Inventory 12 150 Building 13 151 Accumulated Depreciation- Building 14 155 Furnishings 15 156 Accumulated Depreciation - Furnishings 16 160 Office Equipment 17 161 Accumulated Depreciation- Off. Equip. 18 200 Accounts Payable 19 210 Unearned Service Revenue 1 E 16 17 1 B A 13 151 Accumulated Depreciation- Building 14 155 Furnishings 15 156 Accumulated Depreciation- Furnishings 160 Office Equipment 161 Accumulated Depreciation- Off. Equip. 18 200 Accounts Payable 19 210 Unearned Service Revenue 20 215 Salary Payable 21 220 Dividends Payable 22 300 Common Stock, $1 par value 23 305 Additional Paid in Capital 24 310 Retained Earnings 25 400 Service Revenue 26 410 Sales Revenue 27 500 Advertising Expense 28 510 Depreciation Expense 29 520 Repairs & Maintenance Expense 30 530 Supplies Expense 31 540 Uncollectible Accounts Expense 32 550 Utilities Expense 33 560 Cost of Goods Sold 34 570 Salary Expense 2 3 3 25 NOTE: Do not proceed until this page has no errors. Errors will be carried through the rest of the assignment, so you will receive a poor score. Hint: It will be much easier to post these to the Ledger if you use the "Filter" function - below is a link to a video showing how to use filters in case you are un- familiar with it. Excel 2013: Filtering Data 5 6 7 Date 8 9 10 11 12 13 Accounts and Explanation Center here) Center here] Credit Post Re Debit n/a n/a enter here) Center here) n/a n/a 15 16 (enter here) (enter here) n/a n/a Center here) n/a B E n/a 18 [enter here] 19 20 [enter here) [enter here] n/a n/a [enter here) [enter here! n/a n/a [enter here] Center here) n/a n/a 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 (enter here) [enter here! n/a n/a Center here) (enter here) n/a n/a [enter here! Center here) n/a n/a [enter here) Center here! n/a B D E 41 [enter here] [enter here] n/a n/a 42 43 44 45 (enter here) [enter here] n/a n/a 46 47 [enter here] [enter here) 48 49 n/a n/a 50 51 enter here] (enter here] n/a n/a 52 53 54 [enter here) [enter here! n/a n/a 55 56 57 58 59 60 [enter here) (enter here) n/a n/a [enter here) [enter here] n/a n/a 61 62 63 lenter here! Tenter here pla A B D E E 65 [enter here] [enter here) n/a n/a 66 67 68 [enter here] [enter here) n/a n/a 69 70 72 73 [enter here] [enter here) Center here) [enter here] [enter here) n/a n/a n/a n/a n/a 74 75 76 The following adjustments SHOULD NOT be posted to the Ledger; instead, they should be posted directly to the Trial Balance 77 78 79 Ad) enter here! (enter here) 80 81 n/a n/a 82 Adj [enter here! (enter here) n/a BE B D E 81 82 Adj 83 enter here) [enter here] n/a n/a 84 85 Adj 86 [enter here) [enter here] n/a n/a 87 88 Adj (enter here] (enter here) n/a n/a 89 90 91 Adj (enter here) [enter here) n/a n/a 92 93 94 Adi [enter here] [enter here) n/a n/a 95 96 97 Adj [enter here! (enter here) n/a n/a 99 99 100 (enter here] enter here) 101 Ad n/a n/a 1 HAMPTON ACCOUNTING SERVICE INC. 2 Income Statement 3 For the Year Ending December 31, 2020 $ $ 4 5 Service Revenue 6 Sales Revenue 7 Net Revenue 8 Cost of Goods Sold 9 Gross Profit 10 Operating Expenses 11 Advertising Expense 12 Depreciation Expense 13 Repairs & Maintenance 14 Supplies Expense 15 Uncollectible Accts Expense 16 Utilities Expense 17 Salary Expense 18 Total Operating Expense 19 Net Income 20 21 $ $ F A B D E 1 HAMPTON ACCOUNTING SERVICE INC. 2 Statement of Retained Earnings 3 For the Year Ending December 31, 2020 4 5 Retained Earnings, January 1 6 Add: Net Income 7 $ Less: Dividends 9 Retained Earnings, December 31 $ 10 11 12 13 $ 8 $ 14 15 16 17 18 HAMPTON ACCOUNTING SERVICE IN Balance Sheet Hint: List the accounts in the Balance Sheet in the same order as in the Chart of Accoun Hint: If the number is going to be modified, it should first appear in the left column (Col C). If it is NOT going to be modified, it should appear in the center column (Column D). T will appear in far right coumn (Column E). 5 December 31, 2020 7 8 Assets 9 Current: 10 $ $ 12 13 14 Cash Accounts Receivable Less: Allowance for Unc. Accounts enter here! Center here) Total Current Assets $ $ $ $ $ $ A B 15 Total Current Assets 16 Long-Term: 17 [enter here] 18 [enter here) 19 [enter here) 20 Center here) 21 [enter here) 22 [enter here) 23 Total Long-term Assets 24 Total Assets 25 26 Liabilities 27 Current: 28 Accounts Payable 29 Center here) 30 [enter here! 31 Center here! 32 Total Current Liabilities 33 Stockholders Equity 24 Center here! 35 Additional Paid in Capital 36 Retained Earnings 37 Total abilities & Stockholders Equity $ $ S $ $ $ $ $ $ $ [enter here) 3 [enter here] 0 [enter here) 1 (enter here) 02 [enter here) 22 Total Long-term Assets 24 Total Assets 25 26 Liabilities 27 Current: 28 Accounts Payable 29 (enter here) 30 (enter here) 31 (enter here) 32 Total Current Liabilities 33 Stockholders Equity Center here! 25 Additional Paid in Capital 36 Retained Earnings 37 Total Liabilities & Stockholders Equity 39 $
