This project should be completed using Excel (with formulas and linked data) The parameters of the project are below 1 Multi step Income Statement for the year ended 2020 This statement should be flexibly designed (formulas) with common sized percentages (vertical analysis) to the right of the dollars 2 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 for 2020 have been posted) You can add a transaction analysis (not required), however you must complete the entries in the Excel template Statement of Retained Earnings for the year ended 2020 Balance Sheet dated Dec 31, 2020 Again a flexible design is required so any changes will automatically update the balance sheet use formulas Statement of Cash Flows using the indirect method for the year ended 2020 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 2019 Current Assets Cash$17,000 Marketable Securities (Short term)2,000 Accounts Receivable14,000 Allowance for Bad Debt(2,000) Inventory15,000 Prepaid Insurance 5,000 Total Current Assets$51,000 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$266,000 Current Liabilities Accounts Payable$9,000 Unearned Revenue2,000 Income Taxes Payable 3,000 Total Current Liabilities$14,000 Long term Liabilities Bonds, 10 , due in 2024$100,000 Equity Common Stock $ 50,000 (100,000 authorized, 50,000 issued) Additional Pd in Capital80,000 Retained Earnings 22,000 Total Equity$152,000 Total Liabilities Equity$266,000 Additional Information (for all entries please see the posted Excel spreadsheet) Sales for 2020 are $320,000 All sales are on credit Gross Margin ratio is 40 percent Accounts Receivable i $190,000 of the accounts receivable is paid by the end of the year (the remaining balance remains on the balance sheet) ii $4,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 Inventory i Inventory purchases are $180,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 Additional equipment is purchased on 4 1 20 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 The building depreciates at $5,000 per year Half of the marketable securities were sold for $1,200 The FMV and cost of the other half of the securities are the same, so no adjustment to FMV is required 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 20 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) $55,000 in cash is borrowed on 9 30 20 by issuing a Note Payable Interest is 8 per year The bonds were sold at face value last December and pay interest on Dec 31, 2020 10,000 additional shares of stock were sold for $3 a share Insurance costing $18,000 was purchased on 6 1 20 (the same time in which the old policy expired The new policy was for 12 months) On Dec 31, 2020, 1000 shares of stock are repurchased from the market at $2 90 share (treasury stock) 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 make an entry to pay the 2019 taxes, however the 2020 taxes will not be paid until the end of January 2021) Dividends of $3,000 were paid during 2020 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 2020) Data Sheet for Additional Data Entries Basic and Adjusting (you do not have to show closing entries, however, keep in mind all temporary accounts are closed to retained earnings) Adjusted Trial Balance for 2020 (includes the posted amounts of all entries and adjusting entries) Multi step Income Statement Retained Earnings Statement Classified Balance Sheet Cash Flow Statement Post Close Trial Balance for 2020 2019 Ending Balances DEBITS CREDITS Cash 17,000 Marketable Securities 2,000 Accounts Rec 14,000 Allowance for Bad Debt 2,000 Inventory 15,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,000 Interest Payable Income Taxes Payable 3,000 Note Payable Bonds 100,000 Common Stock 50,000 Additional Pd in Capital 80,000 Retained Earnings 22,000 Debits Credits Sales for 2020 are $320,000 All sales are on credit 1 Accounts Rec 310000 Sales 310000 Gross Margin ratio is 40 percent 2 Cost of Goods Sold jQuery22407582587396986131 1596055640580 Inventory $190,000 of the accounts receivable is paid by the end of the year (the remaining balance remains on the balance sheet) 3 i Cash 190000 Accounts Rec 190000 $4,000 of A R is written off during the year 3 ii Allowance for Bad Debts 4000 Accounts Rec 4000 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 310000 Inventory purchases is $180,000, all on credit 190000 4 i Inventory 180000 4000 Accounts Payable 180000 130000 All accounts payable is from inventory purchases all but $12,000 of inventory purchased ispaid by the end of the year Accounts Payable 9000 180000 12000 4 ii Accounts Payable Cash Additional equipment is purchased on 4 1 20 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 5 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 20 because of when payday falls 8 Salaries Expense Cash Salaries Payable $55,000 in cash is borrowed on 9 30 by issuing a Note Payable Interest is 8 per year 9 Cash 55000 Note Payable 55000 Interest Expense Interest Pay The bonds were sold at face value last December and pay interest on Dec 31, 2020 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 20 (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 90 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,000 were paid 15 Dividends Cash The unearned revenue has been earned during the year (classified as other revenue) 16 Unearned Revenue Revenue (The marks are the one's that need to be answered Also needs a multi step income statement, Statement of Retained Earnings, Classified Balance Sheet, Cash Flow Statement, and Post Close Trial Balance Sheet Many issues with calculations, so if you can show them, that would be great Thank you )

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 16, 2024

This project should be completed using Excel (with formulas and linked data).The parameters of the project are below: 1. Multi-step Income Statement for the year

This project should be completed using Excel (with formulas and linked data).The parameters of the project are below:

1.Multi-step Income Statement for the year ended 2020.This statement should be flexibly designed (formulas) with common-sized percentages (vertical analysis) to the right of the dollars.

2.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 for 2020 have been posted).You can add a transaction analysis (not required), however you must complete the entries in the Excel template.

Statement of Retained Earnings for the year ended 2020.
Balance Sheet dated Dec. 31, 2020Again a flexible design is required so any changes will automatically update the balance sheet - use formulas.
Statement of Cash Flows using the indirect method for the year ended 2020.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/2019

Current Assets

Cash$17,000

Marketable Securities (Short-term)2,000

Accounts Receivable14,000

Allowance for Bad Debt(2,000)

Inventory15,000

Prepaid Insurance5,000

Total Current Assets$51,000

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$266,000

Current Liabilities

Accounts Payable$9,000

Unearned Revenue2,000

Income Taxes Payable3,000

Total Current Liabilities$14,000

Long-term Liabilities

Bonds, 10%, due in 2024$100,000

Equity

Common Stock $ 50,000

(100,000 authorized, 50,000 issued)

Additional Pd.-in Capital80,000

Retained Earnings22,000

Total Equity$152,000

Total Liabilities & Equity$266,000

Additional Information (for all entries; please see the posted Excel spreadsheet):

Sales for 2020 are $320,000.All sales are on credit.
Gross Margin ratio is 40 percent
Accounts Receivable:

i.$190,000 of the accounts receivable is paid by the end of the year (the remaining balance remains on the balance sheet).

ii.$4,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.

Inventory:

i.Inventory purchases are $180,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.

Additional equipment is purchased on 4/1/20 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.
The building depreciates at $5,000 per year.
Half of the marketable securities were sold for $1,200. The FMV and cost of the other half of the securities are the same, so no adjustment to FMV is required.
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/20 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).
$55,000 in cash is borrowed on 9/30/20 by issuing a Note Payable. Interest is 8% per year.
The bonds were sold at face value last December and pay interest on Dec. 31, 2020.
10,000 additional shares of stock were sold for $3 a share.
Insurance costing $18,000 was purchased on 6/1/20 (the same time in which the old policy expired.The new policy was for 12 months).
On Dec. 31, 2020, 1000 shares of stock are repurchased from the market at $2.90/share (treasury stock).
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 make an entry to pay the 2019 taxes, however the 2020 taxes will not be paid until the end of January 2021).
Dividends of $3,000 were paid during 2020.
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 2020):

Data Sheet for Additional Data
Entries: Basic and Adjusting (you do not have to show closing entries, however, keep in mind all temporary accounts are closed to retained earnings)
Adjusted Trial Balance for 2020 (includes the posted amounts of all entries and adjusting entries)
Multi-step Income Statement
Retained Earnings Statement
Classified Balance Sheet
Cash Flow Statement
Post-Close Trial Balance for 2020

2019 Ending Balances

DEBITS CREDITS

Cash 17,000

Marketable Securities 2,000

Accounts Rec. 14,000

Allowance for Bad Debt 2,000

Inventory 15,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,000

Interest Payable

Income Taxes Payable 3,000

Note Payable

Bonds 100,000

Common Stock 50,000

Additional Pd-in-Capital 80,000

Retained Earnings 22,000

Debits Credits

Sales for 2020 are $320,000.All sales are on credit.

1. Accounts Rec. 310000

Sales 310000

Gross Margin ratio is 40 percent.

2. Cost of Goods Sold jQuery22407582587396986131_1596055640580?

Inventory ???

$190,000 of the accounts receivable is paid by the end of the year (the remaining balance remains on the balance sheet).

3. i. Cash 190000

Accounts Rec. 190000

$4,000 of A/R is written off during the year.

3.ii. Allowance for Bad Debts 4000

Accounts Rec. 4000

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

310000

Inventory purchases is $180,000, all on credit. 190000

4.i. Inventory 180000 4000

Accounts Payable 180000 130000

All accounts payable is from inventory purchases;all but $12,000 of inventory purchased ispaid by the end of the year.

Accounts Payable

9000

180000

???

12000

4.ii. Accounts Payable ???

Cash ???

Additional equipment is purchased on 4/1/20 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.

5. 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/20 because of when payday falls.

8. Salaries Expense ???

Cash ???

Salaries Payable ???

$55,000 in cash is borrowed on 9/30 by issuing a Note Payable. Interest is 8% per year.

9. Cash 55000

Note Payable 55000

Interest Expense ???

Interest Pay. ???

The bonds were sold at face value last December and pay interest on Dec. 31, 2020.

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/20 (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.90/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,000 were paid.

15. Dividends ???

Cash ???

The unearned revenue has been earned during the year (classified as other revenue).

16. Unearned Revenue ???

Revenue ???

(The ? marks are the one's that need to be answered. Also needs a multi-step income statement, Statement of Retained Earnings, Classified Balance Sheet, Cash Flow Statement, and Post Close Trial Balance Sheet. Many issues with calculations, so if you can show them, that would be great! Thank you!)