***** PLEASE USE AN EXCEL SHEET AND SHOW FORMULAS****

AC312- Comprehensive Financial Accounting Project This project should be completed using Excel (with formulas and linked data). The parameters of the project are below: 1. Prepare a Multi-step Income Statement for the year ended 2021. This statement should be flexibly designed (formulas) with common-sized percentages (vertical analysis) to the right of the dollars. 2. Show journal entries, adjusting entries and closing entries for the below additional information - none of the journal entries have been posted to the ledger (many journal entries have been booked to get you started, however none of the entries have been posted). You can add T-acct transaction analysis to calculate ending balances, however this is not required. 3. Prepare a Statement of Retained Earnings for the year ended 2021. 4. Prepare a Balance Sheet dated Dec. 31, 2021 - Again a flexible design is required so any changes will automatically ate the balance sheet - use formulas. 5. Prepare a Statement of Cash Flows using the indirect method for the year ended 2021. The ending cash as shown on the statement of cash flows will be the same as the cash reported on the Balance Sheet. Your Name, Inc. Balance Sheet 12/31/2020 Current Assets Cash $17,100 Marketable Securities (Short-term) 2,000 Accounts Receivable 14,000 Allowance for Bad Debt 12,000) Inventory 16,000 Prepaid Insurance 5.000 Total Current Assets $52,100 Property, Plant, and Equipment Land $30,000 Building 150,000 Accumulated Dep. - Building (45,000) Equipment 100,000 Accumulated Dep. - Equipment__(20,000) Total PPE $215,000 Total Assets $267,100 Current Liabilities Accounts Payable Unearned Revenue Income Taxes Payable Total Current Liabilities Long-term Liabilities Bonds, 10%, due in 2025 $9,000 2,100 3.000 $14.100 $100,000 Equity Equity Common Stock $ 50,000 (100,000 authorized, 50,000 issued) Additional Pd.-in Capital 80,000 Retained Earnings 23,000 Total Equity $153,000 Total Liabilities & Equity $267,100 Additional Information (for all entries, please see the posted Excel spreadsheet): 1. Sales for 2021 are $400,000. All sales are on credit. 2. Gross Margin ratio is 40 percent 3. Accounts Receivable: i. $300,000 of the accounts receivable is paid by the end of the year (the remaining balance remains on the balance sheet). ii. $15,000 of A/R is written off during the year. iii. 5% of A/R (after write-off and collections) is considered to be uncollectible. 4. Inventory: i. Inventory purchases are $230,000, all on credit. ii. All accounts payable is from inventory purchases; all but $12,000 of inventory purchased is paid by the end of the year. 5. Additional equipment is purchased on 4/1/21 for $20,000 cash. All equipment when new, including the new purchase, has/had a 5-year life, no salvage value and is depreciated using the straight-line method. 6. The building depreciates at $5,000 per year. 7. Half of the marketable securities were sold for $1,200. The FMV and cost of the other half of the securities are the same at year-end, so an adjustment to FMV at year-end is not required. 8. Salaries are $2,200 per month (12 months of salaries expense must be booked). It is expected that one- half month will be owed on 12/31/21 because of when payday falls (therefore, 11.5 months of salaries have been paid and % month is still owed to the employees at year end). 9. $58,000 in cash is borrowed on 9/30/21 by issuing a Note Payable. Interest is 8% per year. 10. The bonds were sold at face value last December and pay interest on Dec. 31, 2021. 11. 10.000 additional shares of stock were sold for $3 a share. 12. Insurance costing $18,000 was purchased on 6/1/21 (the same time in which the old policy expired. The new policy was for 12 months). 13. On Dec 31, 2021, 1000 shares of stock are repurchased from the market at $2.80/share (treasury stock). 14. The tax rate is 20 percent. Income taxes for the current year are due and therefore paid during the first two months of the next year (you will have to complete an entry to pay the 2020 taxes, however the 2021 taxes will not be paid until the end of January 2022). 15. Dividends of $3,200 were paid during 2021. 16. The unearned revenue has been earned during the year (classified as other revenue on the multi-step income stmt.). Required Labeled Sheets (all statements should be for 2021): 1. Data Sheet for Additional Data 2. Entries: Basic and Adjusting (you do not have to show closing entries, however, keep in mind all temporary accounts are closed to retained earnings) 3. Adjusted Trial Balance for 2021 (includes the posted amounts of all entries and adjusting entries) 4. Multi-step Income Statement 5. Retained Earnings Statement 6. Classified Balance Sheet 7. Cash Flow Statement 8. Post-Close Trial Balance for 2021 The Post-Close Trial Balance for 2020 is provided below (based on the above balance sheet). This can be used as a starting point or you can use the above Balance Sheet; keep in mind all debits and credits ALWAYS equal AND Assets = Liabilities + Equity: Your Name, Inc. Post Close Trial Balance 31-Dec-20 DEBITS CREDITS Cash 17,100 2,000 14,000 Marketable Securities Accounts Rec. Allowance for Bad Debt Inventory Prepaid Insurance 2,000 16,000 5,000 Land 30,000 150,000 45,000 100,000 Building Accumulated Dep. - Building Equipment Accumulated Dep. - Equipment Accounts Payable Salaries Payable 20,000 9,000 Unearned Revenue 2.100 Your Name, Inc. Post Close Trial Balance 31-Dec-20 CREDITS DEBITS 17,100 2,000 14,000 2,000 16,000 5,000 30,000 150,000 45,000 100,000 Cash Marketable Securities Accounts Rec. Allowance for Bad Debt Inventory Prepaid Insurance Land Building Accumulated Dep. - Building Equipment Accumulated Dep. - Equipment Accounts Payable Salaries Payable Unearned Revenue Interest Payable Income Taxes Payable Note Payable Bonds Common Stock Additional Pd-in-Capital Retained Earnings - 20,000 9,000 2,100 3,000 100,000 50,000 80,000 23,000 334,100 334,100 2020 Ending Balances Cash Marketable Securities Accounts Rec. Allowance for Bad Debt Inventory Prepaid Insurance Land Building Accumulated Dep. - Building Equipment Accumulated Dep. - Equipment Accounts Payable Salaries Payable Unearned Revenue Interest Payable Income Taxes Payable Note Payable Bonds Common Stock Additional Pd-in-Capital Retained Earnings DEBITS CREDITS 17,100 2,000 14,000 2,000 16,000 5,000 30,000 150,000 45,000 100,000 20,000 9,000 2,100 3,000 100,000 50,000 80,000 23,000 334,100 334,100 1. Debits Credits Sales for 2021 are $400,000. All sales are on credit. Accounts Rec. 400000 Sales 400000 . Gross Margin ratio is 40 percent. Cost of Goods Sold ??? Inventory ??? 2. - $300,000 of the accounts receivable is paid by the end of the year (the remaining balance remains on the balance sheet). 3. i. Cash 300000 Accounts Rec. 300000 - $15,000 of A/R is written off during the year. 3.ii. Allowance for Bad Debts 15000 Accounts Rec. 15000 - 5% of Accounts Receivable (after write-off and collections) is considered to be uncollectible. 3.iii. Bad Debt Expense ??? AR Allowance for Bad Debts ??? 14000 400000 Inventory purchases is $230,000, all on credit. 300000 4.i. Inventory 230000 15000 Accounts Payable 230000 99000 Accounts Payable 9000 230000 . All accounts payable is from inventory purchases; all but $12,000 of inventory purchased is paid by the end of the year. 4.ii. Accounts Payable ??? ??? Cash ??? 12000 5. - Additional equipment is purchased on 4/1/21 for $20,000 cash. All equipment when new, including the new purchase, has/had a five year life, no salvage value, and is depreciated using the straight-line method. Equipment ??? Cash Depreciation Expense ??? Accum. Deprec. ??? ??? The building depreciates at $5,000 per year. 6. Depreciation Exp. - Build. ??? Accum. Deprec. ??? Half of the marketable securities were sold for $1,200. The FMV and cost of the other half are the same. 7. Cash 1200 Mkt. Securities 1000 Gain ??? Salaries are $2,200 per month. It is expected that one-half month will be owed on 12/31/21 because of when payday falls. 8. Salaries Expense ??? Cash ??? Salaries Payable ??? $58,000 in cash is borrowed on 9/30 by issuing a Note Payable. Interest is 8% per year. 9. Cash 58000 Note Payable 58000 Interest Expense ??? Interest Pay. ??? - The bonds were sold at face value last December and pay interest on Dec. 31, 2021. 10. Bond Interest Expense ??? Cash ??? - 10,000 additional shares of stock were sold for $3 a share. 11. Cash 30000 Common Stock ??? Paid in Capital ??? - Insurance costing $18,000 was purchased on 6/1/21 (the same time in which the old policy; the new policy was for 12 months). 12. Prepaid Insurance 18000 Cash 18000 Insurance Expense ??? Prepaid Insurance ??? - On Dec. 31, shares of stock are repurchased from the market at $2.80/share. 13. Treasury Stock ??? Cash ??? The tax rate is 20 percent. Income taxes for the current year are payable during the first two months of the next year. 11. Cash Common Stock Paid in Capital 30000 ??? ??? Insurance costing $18,000 was purchased on 6/1/21 (the same time in which the old policy; the new policy was for 12 months). 12. Prepaid Insurance 18000 Cash 18000 Insurance Expense ??? Prepaid Insurance ??? . On Dec. 31, shares of stock are repurchased from the market at $2.80/share. 13. Treasury Stock ??? Cash ??? - The tax rate is 20 percent. Income taxes for the current year are payable during the first two months of the next year. 14. Income Taxes Payable ??? Cash ??? Income Tax Expense ??? Income Taxes Payable ??? - Dividends of $3,200 were paid. 15. Dividends ??? Cash ??? - The unearned revenue has been earned during the year (classified as other revenue). 16. Unearned Revenue ??? Revenue ??? Your Name, Inc. Post Close Trial Balance Your Name, Inc. Adjusted Trial Balance 2021 Entries and Adjustments Debits Credits 31-Dec-20 31-Dec-21 DEBITS CREDITS 17.100 400,000 Cash Marketable Securities Accounts Rec. Allowance for Bad Debt Inventory Prepaid Insurance Land Building Accumulated Dep. - Building Equipment Accumulated Dep. - Equipment Accounts Payable Salaries Payable Unearned Revenue Interest Payable Income Taxes Payable Note Payable Bonds Common Stock Additional Pd-in-Capital Retained Earnings DEBITS CREDITS 17,100 2,000 14,000 2,000 16,000 5,000 30,000 150,000 45,000 100,000 20,000 9,000 2,100 3,000 100,000 50,000 80,000 23,000 334,100 334,100 Additional Accounts: Sales 400,000 Prepare a Multi-step Income Statement (chapter 4) Prepare a Retained Earnings Statement (chapters 3, 4, and 5) 1 Prepare a Balance Sheet (chapter 5) 2 3 min 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28