Intermediate Accounting 321 Excel Project
O AutoSave OFF THE OU * AC 321 Int I - SU20 - Comprehensive Problem- Hammon(2) Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) v 11 AA 207 ab General Insert v x Delete v V v O. Paste B IUV vAv E $ ~ % 9 Conditional Format Cell X v Ideas Sensitivity Styles Format v Sort & Find & Formatting as Table Filter Select not Print_Area X V fx Comprehensive Project Information: 220 B Dur D E F G H K L M N P Q R oro 1 Comprehensive Project Information: N All work must be completed using this Excel spreadsheet. w All journal extries, financial statements, etc. must be prepared in clean, proper form. Reference your textbook and past courses for examples or templates to model. 4 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." 6 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). 7 Click on the tabs at the bottom of the bottom to familiarize yourself with the project. 8 Check figures are provided for 3 key items to help ensure you are on the correct track. |EXCEL NOTES: 11 *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 12 and navigating the project. 13 * Also be sure to take advantage of Excel by using formulas to calculate/sum groups of numbers. 14 * 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 15 statement misrepresentation, sufficient to result in a failing grade. 16 *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. 17 18 |REQUIREMENTS: 0 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. 23 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. 24 Part 3 Prepare an Adjusted Trial Balance for Gatsby, Inc. using the updated T-Accounts. 25 Adjusted TB check figure: $1,277,600 26 Part 4 Prepare, in good form, financial statements on each specified sheet in the following order: 27 a Multi-Step Income Statement and prepare a bar or pie chart for a visual representation of expenses. 28 b Statement of Stockholders' Equity 29 30 c Classified Balance Sheet Total Assets check figure: $871,950 Project Instructions Unadjusted Trial Balance Adjusting Journal Entries T-Accounts Adjusted Trial Balance Comma Style Statement Statement Shareholders' Equity Balance Sheet + 1028500 50 Sur 3085500 + 100%O AutoSave OFF THE OU * AC 321 Int I - SU20 - Comprehensive Problem- Hammon(2) Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) v 11 AA ab General Insert v Ex Delete v V v O. Paste B I UV MvAv E $ ~ % 9 .0 Conditional Format Cell Format v X v Sort & Find & Ideas Sensitivity Formatting as Table Styles Filter Select inof Print_Area X V fx Comprehensive Project Information: 220 A B D F G H K L M N P Q R Dur 16 use the formal button to add dollar signs, underlining and double underlining when appropriate. DO NUI put dollar signs on every line as it will cost you points. pro 17 18 19 REQUIREMENTS: 20 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. 22 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. 23 24 Part 3 Prepare an Adjusted Trial Balance for Gatsby, Inc. using the updated T-Accounts. Adjusted TB check figure: $1,277,600 26 Part 4 Prepare, in good form, financial statements on each specified sheet in the following order: 27 a Multi-Step Income Statement and prepare a bar or pie chart for a visual representation of expenses. 28 b Statement of Stockholders' Equity 29 30 c Classified Balance Sheet Total Assets check figure: $871,950 31 Part 5 Using the financial statements, calculate the 5 following ratios on the specified sheet and briefly (2 or 3 sentences) interpret the ratios for Gatsby, Inc. 32 33 a Working Capital 34 b Current Ratio 35 c Quick Ratio (Acid Test) 36 d Debt to Equity 37 e Times Interest Earned 38 39 Part 6 Record closing journal entries AND post them to the T-accounts. 40 Part 7 Prepare a Post-Closing Trial Balance using the updated T-accounts. Total debit check figure: $935,950 41 42 43 44 45 46 47 Project Instructions Unadjusted Trial Balance Adjusting Journal Entries T-Accounts Adjusted Trial Balance Income Statement Statement Shareholders' Equity Balance Sheet + 1028500 50 Sum: 3085500 + 100%O AutoSave OFF THE QU * AC 321 Int I - SU20 - Comprehensive Problem- Hammon(2) Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) v 11 AA 207 ab General Insert v x Delete v V v AY O. Paste BIUV DV Av EEE I V $ ~ % 9 .0 Conditional Format Cell Formatting as Table Styles Format v X v Sort & Find & Ideas Sensitivity Filter Select inof L19 X V fx 220 A B C E F G H K M O P Q R S T U V W Dur D L 1 oro Information to prepare adjusting journal entries PREPARE (in proper form) ADJUSTING JOURNAL ENTRIES HERE 2 The following information relates to Gatsby, Inc. as of December 31 of the current year. The company uses the calendar 3 year as its annual reporting period and the Accrual Method of Accounting . Prepaid and unearned items are recorded as 4 assets and liabilities, respectively. Prepare all necessary adjusting journal entries and post to the T-accounts. Account Debit Credit A. The company's weekly payroll is $3,000 and is paid each Friday for a five-day work week. Assume A. December 31st falls on a Monday, but the employees will not be paid their wages until Friday, January 3rd. 8 9 B. Eighteen months earlier, on July 1st the company purchased equipment that cost $160,000. Its useful life B. 10 s predicted to be twenty years, at which time the equipment is expected to have a zero salvage/residual value. 11 Gatsby, Inc. uses the straight-line depreciation method. Deprecation has NOT been recorded for this year. 12 13 C. On September 1st of the current year Gatsby, Inc. was paid $45,000 in advance of future installation of alarm systems in C. 14 new homes. The amount was credited to the Unearned Revenue - Alarms account. Between September 1st 15 and December 31st alarm systems were installed in 1 home, completing that job. 16 17 D On October 1st of the current year the company purchased a 12-month insurance policy for $24,000. The transaction D. 18 was recorded with a debit to the Prepaid Insurance account. Insurance expense has not been recorded for 19 mber nor December. 20 21 E. On December 30 of the current year the company completed an $38,000 job that has not been billed/invoiced E. 22 and therefore has not been recorded. 23 24 F A$150,000 long-term note payable was signed on August 1st of the current year. It is a five-year note with F. 25 a 6% interest rate. Interest expense as not been accrued for this year. 26 27 G. Supplies at the beginning of the current year had a balance of $ 300. Supplies valued at $4,200 were purchased G . 28 thoughout the year. The current balance in the account is $400. 29 30 H. Depreciation on the building is calculated using the straight-line depreciation method. Gatsby estimates depreciation H . 31 on the building over a 25 year period and a zero salvage/residual value. 32 33 34 Project Instructions Unadjusted Trial Balance Adjusting Journal Entries T-Accounts Adjusted Trial Balance Income Statement Statement Shareholders' Equity Balance Sheet + + 100%
