Comprehensive Project Information: All work must be completed using this Excel spreadsheet. All joumal extries, financial statements, etc. must be prepared in clean, proper form. Reference your textbook and past courses for examples or templates to model Use the current year for dating purposes. This project is valued at 120 points. The grading rubric is available on the last sheet labeled "Grading Rubric." Information on using Excel can be found in the Excel Notes section below. Please be aware, points are allocated for the use of Excel (see rubric). Click on the tabs at the bottom of the bottom to familiarize yourself with the project Check figures are provided for 3 key items to help ensure you are on the correct track EXCEL NOTES: *Use Excel to link all of your journal entries and financial statements. This will be great practice and it reduces the likelihood of errors. There is a video available in Blackboard to guide you on using Excel and navigating the project. * Also be sure to take advantage of Excel by using formulas to calculate/sum groups of numbers. DO NOT force any cells to match check figures given. Any adjustments in the T-Accounts or financial statements not supported by legitimate adjusting or closing entries will be considered financial statement misrepresentation, sufficient to result in a failing grade. *Use the format button to add dollar signs, underlining and double underlining when appropriate. DO NOT put dollar signs on every line as it will cost you points. REQUIREMENTS: Part 1 Post the Unadjusted Trial Balance totals to the T-Accounts on the sheet labeled "T-Accounts." These numbers will represent your beginning balances. Part 2 Record the adjusting journal entries required for items AND post them to the T-accounts. Add or extend lines on any T-Accounts if necessary. Adjusted to check figure: $1,277,600 Part 3 Prepare an Adjusted Trial Balance for Catsby, Inc. using the updated T-Accounts. Part 4 Prepare, in good form, financial statements on each specified sheet in the following order: a Multi-Step Income Statement and prepare a bar or ple chart for a visual representation of expenses. b Statement of Stockholders' Equity Classified Balance Sheet Total Assets check firure: Project Instructions Unadjusted Trial Balance Adjusting Journal Entries T-Accounts 5871.950 Adjusted Trial Balance Comma Style Statement Average: 1028500 Count: 50 Statement SI Sum 3085500 -6 B D H use ve To Dutton and underwen appropria. UNUI put was on every nes WISE you points. 7 23 $1,277,600 27 8 -8 .9 REQUIREMENTS: o Part 1 Post the Unadusted Trial Balance totals to the T-Accounts on the sheet labeled "T-Accounts." These numbers will represent your beginning balances. 2 Part 2 Record the adjusting journal entries required for items AND post them to the T-accounts. Add or extend lines on any T-Accounts if necessary. 4 Part 3 Prepare an Adjusted Trial Balance for Gatsby, Inc. using the updated T-Accounts Adjusted to check lipure: 6 Part 4 Prepare, in good form, financial statements on each specified sheet in the following order: a Multi-Step Income Statement and prepare a bar or pie chart for a visual representation of expenses. b Statement of Stockholders' Equity Classified Balance Shtet Total Assets check figure: $871,950 1 Part 5 Using the financial statements, calculate the following ratios on the specified sheet and briefly (2 or 3 sentences) interpret the ratios for Gatsby, Inc. 12 Working Capital b Current Ratio 5 Quick Ratio (Acid Test) 36 d Debt to Equity e Times Interest Earned 18 19 Part 6 Record closing journal entries AND post them to the T-accounts. 10 Part 7 Prepare a Post-Closing Trial Balance using the updated T-accounts. Total debit check figure: 11 12 $935,950 AutoSave OFF Home Insert Draw Page Layout Formulas Data RE Calibri (Body) v 11 = A X V Paste BIU a.A v 08 fx 4500 D E Credit 40,000 A B 1 Gatsby, Inc. 2 Unadjusted Trial Balance 3 12/31/XX 4 Debit 5 Cash 121,550 Accounts Receivable 236,000 7 Prepaid Insurance 24,000 8 Supplies 4,500 9 Inventory 85,000 10 Land 75,000 11 Building 200,000 12 Accumlated Depreciation-Building 13 Equipment 160,000 14 Accumlated Depreciation-Equipment 15 Accounts Payable 16 Interest Payable 17 Wages Payable 18 Unearned Revenue - Alarm Systems 19 Long-Term Bank Note Payable 20 Common Stock 21 Retained Earnings 22 Dividends 7,000 23 Revenue 24 Cost of the Goods Sold 132,500 25 Depreciation Expense 0 26 Insurance Expense 15,000 27 Interest Expense 0 28 Rent Expense 24,000 29 Supplies Expense 0 30 Utilities Expense 9,700 31 Wage Expense 125,000 32 Totals $1.219,250 33 34 35 36 37 8,000 160,500 0 0 45,000 150,000 310,000 124,750 381,000 $1,219,250 Project Instructions Unadjusted Trial Balance Adju: K M N PREPARE (in proper form) ADJUSTING JOURNAL ENTRIES HERE H Information to prepare adjusting journal entries The following information relates to Gatsby, Inc. as of December 31 of the current year. The company uses the calendar year as its annual reporting period and the Accrual Method of Accounting. Prepaid and unearned items are recorded as assets and liabilities, respectively. Prepare all necessary adjusting journal entries and post to the accounts. Account Debit Credit A A The company's weekly payroll is $3,000 and is paid each Friday for a five-day work week. Assume December 31st falls on a Monday, but the employees will not be paid their wages until Friday, January 3rd. B. B Eighteen months earlier, on July 1st the company purchased equipment that cost $160,000. Its useful life is predicted to be twenty years, at which time the equipment is expected to have a zero salvage/residual value. Gatsby, Inc. uses the straight-line depreciation method. Deprecation has NOT been recorded for this year. c. On September 1st of the current year Gatsby, Inc. was paid $45,000 in advance of future installation of alarm systems in 3 new homes. The amount was credited to the Unearned Revenue - Alarms account. Between September 1st and December 31st alarm systems were installed in 1 home, completing that job. D. D On October 1st of the current year the company purchased a 12-month insurance policy for $24,000. The transaction was recorded with a debit to the Prepaid Insurance account. Insurance expense has not been recorded for November nor December L On December 30 of the current year the company completed an $38,000 job that has not been billed/invoiced and therefore has not been recorded. F. F. A$150,000 long-term note payable was signed on August 1st of the current year. It is a five year note with a 6% interest rate. Interest expense as not been accrued for this year. Supplies at the beginning of the current year had a balance of 300. Supplies valued at $4,200 were purchased thoughout the year. The current balance in the account is $400. G. G. H. H. Depreciation on the building is calculated using the straight-line depreciation method. Gatsby estimates depreciation on the building over a 25 year period and a zero salvage/residual value. Cash Accounts Receivable Prepaid Insurance Supplies Inventory Land Building Accumlated Depreciation- Building Equipment Accumlated Depreciation- Equipment Accounts Payable Interest Payable Wages Payable Unearned Revenue - Alarm Systems Long-Term Bank Note Payable Common Stock Retained Earnings Dividends Revenue Cost of the Goods Sold Depreciation Expense Insurance Expense Interest Expense Rent Expense Supplies Expense Utilities Expense Wage Expense Totals Gatsby, Inc. Unadjusted Trial Balance 12/31/XX Debit Credit 121,550 236,000 24,000 4,500 85,000 75,000 200,000 40,000 160,000 8,000 160,500 0 0 45,000 150,000 310,000 124,750 7,000 381,000 132,500 0 15,000 0 24,000 0 9,700 125,000 $1,219,250 $1,219,250 PREPARE (in proper form) ADJUSTING JOURNAL ENTRIES HERE A Account Debit Credit Wages Expense 1,200 Wages Payable Account 1.200 Depreciation on equipment Account 16,000 Accumlated Deperation on equipment Account 16.000 B. Information to prepare adjusting journal entries The following information relates to Gatsby, Inc. as of December 31 of the current year. The company uses year as its annual reporting period and the Accrual Method of Accounting. Prepaid and uneared items are assets and abilities, respectively. Prepare all necessary adjusting journal entries and post to the T- accounts. The company's weekly payroll is $3,000 and is paid each Friday for a five-day work December 31st falls on a Monday, but the employees will not be paid their wages until Friday January 3rd Eighteen months earlier, on July 1st the company purchased equipment that cost is predicted to be twenty years, at which time the equipment is expected to have a zero Gatsby, Inc. uses the straight-line depreciation method. Deprecation has NOT been recorded for this year On September 1st of the current year Gatsby, Inc. was paid $45,000 in advance of tuturo installation of alarm 3 new homes. The amount was credited to the Unearned Revenue - Alarms account. Between and December 31st alarm systems were installed in 1 home, completing that job On October 1st of the current year the company purchased a 12-month insurance policy for $24,000. The transaction was recorded with a debit to the Prepaid Insurance account, Insurance expense has not been November nor December on December 30 of the current year the company completed an $38,000 job that has not been billed/invoiced B c. 30,000 Unearned Revenue Account Revenue Account 30.000 D D. 3,000 Insurance Expense Account Prepaid Insurance Account 3.000 E. Accounts Receivable Account 38,000 Accumlated Depercation on equipment Account 16,000 recorded for this year. C. C. 30,000 Uneamed Revenue Account Revenue Account 30,000 D. D. 3,000 is predicted to be twenty years, at which time the equipment is expected to have a zero Gatsby, Inc. uses the straight-line depreciation method. Deprecation has NOT been On September 1st of the current year Gatsby, Inc. was paid $45,000 in advance of future installation of alarm 3 new homes. The amount was credited to the Unearned Revenue - Alarms account. Between September 1st and December 31st alarm systems were installed in 1 home, completing that job. On October 1st of the current year the company purchased a 12-month insurance policy for was recorded with a debit to the Prepaid Insurance account. Insurance expense has not been recorded for November nor December On December 30 of the current year the company completed an $38,000 job that has not been billed/Invoiced and therefore has not been recorded. A $150.000 long-term note payable was signed on August 1st of the current year. It is a five-year a 6% interest rate. Interest expense as not been acorved for this year Supplies at the beginning of the current your had a a balanced ce of $ 300. Supplies valued at $4,200 were purchased thoughout the year. The current balance in the account is Insurance Expense Account Prepaid Insurance Account 3,000 E. E. 38,000 Accounts Receivable Account Revenue Account 38.000 F F 4,500 Intrest Expense Account Intrest Payable Account 4,500 a G 4,300 Supplies expense account Supplies Account 4.300 $400. H. H. 10,000 Depreciation on the building is calculated using the straight-line depreciation method. Gatsby estimates on the building over a 25 year period and a zero salvage/residual value. Deprecation on Buildings Account Accumuzated Depreciation on Buildings Account 10,000