Any assistance in working through this Financial Statement Excel Project would be greatly appreciated! I wasn't sure which would be easier to follow, so I've labeled & included the necessary info. on both excel (1-4) and word (A &B) screenshots. In the excel screenshots, I left some tips & pointers highlighted in light red and orange. Thank you in advance & let me know if there are any questions. ***Follow images from top to bottom and left to right***

A B C D E F 2020 Ending Balances 2 DEBITS CREDITS 3 Cash 17,100 4 Marketable Securities 2,000 5 Accounts Rec. 14,000 Allowance for Bad Debt 2,000 7 Inventory 16,000 8 Prepaid Insurance 5,000 9 Land 30,000 10 Building 150,000 11 Accumulated Dep. - Building 45,000 12 Equipment 100,000 13 Accumulated Dep. - Equipment 20,000 14 Accounts Payable 9,000 15 Salaries Payable 16 Unearned Revenue 2,100 17 Interest Payable 18 Income Taxes Payable 3,000 19 Note Payable 20 Bonds 100,000 21 Common Stock 50,000 22 Additional Pd-in-Capital 80,000 23 Retained Earnings 23,000 24 334,100 334,100 25 26 27 28 29 20B D G H M O Q R U Debits Credits Debits Credits Sales for 2021 are $400,000. All sales are on credit $58,000 in cash is borrowed on 9/30 by issuing a Note Payable w/ 8% annual interest. 1. Accounts Rec. 400000 9. Cash 58000 Book the debt & then book Sales 400000 Note Payable 58000 adj. entry for the interest. Gross Margin ratio is 40%. Interest Expense ??? Interest for 3mo must be accrued. 6 2. Cost of Goods Sold ??? Interest Pay. ??? Inventory ??? Bonds were sold at face value last December & pay interest on 12/31/21 8 $300,000 of the accounts receivable is paid by EOY (remaining stays on balance sheet.) 10. Bond Interest Expense ??? Bond Int. Exp. is accrued & 9 3. i Cash 300000 Cash ??? paid for full year. 10 Accounts Rec. 300000 10,000 additional shares of stock were sold for $3 a share. 11 $15,000 of A/R is written off during the year 11. Cash 30000 12 3.ii. Allowance for Bad Debts 15000 Common Stock ??? 13 Accounts Rec. 15000 Paid in Capital ??? 14 5% of Accounts Receivable (after write-off & collections) is uncollectible - balance sheet approach Insurance costing $18,000 was purchased on 6/1/21 (same time as old; the new policy was 12 months). 15 3.iii. Bad Debt Expense ??? 12. Prepaid Insurance 18000 Show full 12mo of insurance used, but will 16 Allowance for Bad Debts ??? Cash 18000 still be unused insurance on balance sheet; prepaid. Prepaid begin bal. used up & 17 Inventory purchases is $230,000, all on credit Insurance Expense ??? needs expensed, plus 7mo from new 18 4.i Inventory 230000 Prepaid Insurance ??? policy 19 Accounts Payable 230000 On 12/31 shares of stock are repurchased from market @ $2.80 PPS. 20 All accounts payable is from inventory purchases & all but $12,000 is paid by EOY. 13. Treasury Stock ??? 21 4.ii. Accounts Payable ??? Cash ??? 22 Cash ??? Tax rate is 20%. Income taxes for current year are payable during the ist 2 months of next year. 23 Additional equipment purchased 4/121 for $20,000 cash. All new equipment has 5yr life, no salvage, & depreciated using straight line method. 14. Income Taxes Payable ??? Inc. Tax Pay. from 2021 bal. sheet. & 24 5 Equipment ??? Cash ??? needs paid this yr. 25 Cash ??? Depreciate new equipment for Income Tax Expense ??? Can't calculate inc. tax expense until this yr's pretax profit is calc. on income stmnt. 26 Depreciation Expense ??? 9 months & existing for full yr. Income Taxes Payable ??? 27 Accum. Deprec. ??? Dividends of $3,200 were paid. 28 The building depreciates at $5,000 per year 15 Dividend ??? 29 6. Depreciation Exp. - Build. ??? Cash ??? 30 Accum. Deprec. ??? The unearned revenue has been earned during the yr - classified as other revenue. 31 1/2 the marketable securities were sold for $1,200. The FMV & cost of other 1/2 are the same. 16. Unearned Revenue ??? Cash already booked, just recoding 32 7. Cash 1200 Revenue ??? the revenue being earned. 33 Mkt. Securities 1000 34 Gair ??? T-ACCOUNTS 35 Salaries are $2,200 per month & one-half month will be owed on 12/31/21 when payday falls. AR Accounts Payable 36 8. Salaries Expense ??? 14000 9000 37 Cash ??? 400000 230000 38 Salaries Payable ??? 300000 ?? ? 39 15000 12000 40 99000 41W23 X V fx B D G H See below: You do not have to organize your work in this way..however, I need to calculate the ending balance of each account. This means that you have to produce an Adjusted Trial Balance, however you do not have to calculate the ending balances using the below method. I only added this in order to provide you with ar example of how you could calculate the ending balances...you could also use T accounts for each account or a transaction analysis approach for each account. AWN 31-Dec-20 2021 Entries and Adjustments 31-Dec-21 This is an adjusted trial balance 5 DEBITS CREDITS DEBITS CREDITS DEBITS CREDITS because you will have to add the 6 Cash 17,100 17,100 revenue and expenses you had to book based on the additional Marketable Securities 2,000 information. Accounts Rec. 14,000 400,000 9 Allowance for Bad Debt 2,000 This is 1st entry - see the 10 Inventory 16,000 credit for sales b 11 Prepaid Insurance 5,000 12 Land 30,000 13 Building 150,000 14 Accumulated Dep. - Building 45,000 15 Equipment 100,000 16 Accumulated Dep. - Equipment 20,000 Accounts Payable 9,000 18 Salaries Payable Unearned Revenue 2,100 Interest Payable Income Taxes Payable 3,000 Note Payable Bonds 100,000 Common Stock 50,000 Additional Pd-in-Capital 80,000 Retained Earnings 23,000 Begin bal. in retained earnings: new revenue & expenses. 334,100 334,100 Additional Accounts: Sales 400,000 30 ***The "2021 Entries & Have to add the revenue and Adjustments" are based on expenses under the retained the additional journal entries earnings account. Every account on the journal and and adjusting entries from adjusting entry sheet needs to be on the previous sheet. * ** the adjusted trial balance. Therefore, if you used an account for an entry that is not listed in the accounts above, you must include it here, in the additional accounts section. This is the only way your adjusted trial balance will balance. 40Prepare Using Excel. Parameters 8: Data 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 (monyjournol entries have been booked to get you started, however none of the entries have been posted). You can add Tacct 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 update 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. *" Note: 5) Statement of Cash Flow: You need to add back depreciation and analyze the working capital accounts (current assets and current liabilities); also, if there are any gains and losses, their effect must be removed from the operating section. Can all be solved on one sheet or individual sheets. Total Equity $153,000 Prepare Using Excel. Parameters & Data 1. Prepare a Multi-step Income Statement for the year ended 2021. This statement should be flexibly Total Liabilities & Equity $267,100 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 - Additional Information (for all entries; please see the posted Excel spreadsheet): none of the journal entries have been posted to the ledger (many journal entries have been booked to get 1. Sales for 2021 are $400,000. All sales are on credit you started, however none of the entries have been posted). You can add T-acct transaction analysis to 2. Gross Margin ratio is 40 percent calculate ending balances, however this is not required. 3. Accounts Receivable: 3. Prepare a Statement of Retained Earnings for the year ended 2021. i. $300,000 of the accounts receivable is paid by the end of the year (the remaining balance 4 Prepare a Balance Sheet dated Dec. 31, 2021 - Again a flexible design is required so any changes will remains on the balance sheet) automatically update the balance sheet - use formulas. ii. $15,000 of A/R is written off during the year. 5. Prepare a Statement of Cash Flows using the indirect method for the year ended 2021. The ending iii. 5% of A/R (after write-off and collections) is considered to be uncollectible. cash as shown on the statement of cash flows will be the same as the cash reported on the Balance Sheet 4. Inventory: 1. Inventory purchases are $230,000, all on credit Your Name, Inc. 1i. All accounts payable is from inventory purchases; all but $12,000 of inventory purchased is paid by the end of the year. Balance Sheet 5. Additional equipment is purchased on 4/1/21 for $20,000 cash. All equipment when new, including the 12/31/2020 new purchase, has/had a 5-year life, no salvage value, and is depreciated using the straight-line method. Current Assets 6. The building depreciates at $5,000 per year. Cash $17,100 7. Half of the marketable securities were sold for $1,200. The FMV and cost of the other half of the Marketable Securities (Short-term) 2,000 securities are the same at year-end, so an adjustment to FMV at year-end is not required. Accounts Receivable 4 000 8. Salaries are $2,200 per month (12 months of salaries expense must be booked). It is expected that one- Allowance for Bad Debt (2,000) half month will be owed on 12/31/21 because of when payday falls (therefore, 11.5 months of salaries Inventory 16,00 have been paid and 1/2 month is still owed to the employees at year end). Prepaid Insurance 5,00 $58,000 in cash is borrowed on 9/30/21 by issuing a Note Payable. Interest is 8% per year. Total Current Assets $52,100 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. Property, Plant, and Equipment 12. Insurance costing $18,000 was purchased on 6/1/21 (the same time in which the old policy expired. The Land $30,000 new policy was for 12 months). Building 150,000 13. On Dec. 31, 2021, 1000 shares of stock are repurchased from the market at $2.80/share (treasury stock). Accumulated Dep. - Building (45,000) 14. The tax rate is 20 percent. Income taxes for the current year are due and therefore paid during the first Equipment 100,000 two months of the next year (you will have to complete an entry to pay the 2020 taxes, however the Accumulated Dep. - Equipment (20,000) 2021 taxes will not be paid until the end of January 2022) Total PPE $215,000 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 Total Assets $267,100 income stmt.) Current Liabilities Required Labeled Sheets (all statements should be for 2021): Accounts Payable $9,000 1. Data Sheet for Additional Data Unearned Revenue 2,10 2. Entries: Basic and Adjusting (you do not have to show closing entries, however, keep in mind all Income Taxes Payable 3.000 temporary accounts are closed to retained earnings) Total Current Liabilities $14,100 3. Adjusted Trial Balance for 2021 (includes the posted amounts of all entries and adjusting entries) 4. Multi-step Income Statement Long-term Liabilities 5. Retained Earnings Statement 0%, due in $100,000 6. Classified Balance Sheet 7. Cash Flow Statement Equity 8. Post-Close Trial Balance for 2021 Common Stock $ 50,000 (100,000 authorized, 50,000 issued) The Post-Close Trial Balance for 2020 is provided below (based on the above balance sheet). This can be Additional Pd.-in Capital 80,000 used as a starting point or you can use the above Balance Sheet; keep in mind all debits and credits Retained Earnings 23,00 ALWAYS equal AND Assets = Liabilities + Equity:Your Name, Inc. Post Close Trial Balance 31-Dec-20 DEBITS CREDITS Cash 17,100 Marketable Securities 2,000 Accounts Rec. 14,000 Allowance for Bad Debt 2,000 Inventory 16,000 Prepaid Insurance 5,000 Land 30,000 Building 150,000 Accumulated Dep. - Building 45,000 Equipment 100,000 Accumulated Dep. - Equipment 20,000 Accounts Payable 9,000 Salaries Payable Unearned Revenue 2,100 Interest Payable Income Taxes Payable 3,000 Note Payable Bonds 100,000 Common Stock 50,000 Additional Pd-in-Capital 80,000 Retained Earnings 23,000 334,100 334,100