On pages 3 and 4 of this document, you will find the adjusted trial balance and the prior period balance sheet for Be Prepared Inc., a corporation. You will use these reports to complete each of the four phases of the Excel Exam. Each phase is worth 25 points. Please read the instructions carefully as each phase has a different due date. The instructions for each phase are as follows:

Phase #1

Using Be Prepared, Inc.s adjusted trial balance and prior periods balance sheet, prepare the following Financial Reports:

• Multi-Step Income Statement for the month of January 2021 (see page 181 in your textbook for guidance).
• Statement of Retained Earnings for the month of January 2021 (see page 479 in your textbook for guidance). Note: There are no prior period adjustments and all dividends declared and paid are cash dividends in the amount of $25,000.

Phase #2

Using Be Prepared, Inc.s adjusted trial balance and prior periods balance sheet, prepare the following Financial Report in good form using Microsoft Excel:

• Comparative Balance Sheet for the months of December 2020 and January 2021 (See page 616 in your textbook for guidance).

Phase #3

Use the Comparative Balance Sheet you prepared in Phase #2 and the following additional information to prepare the Statement of Cash Flows for the month of January 2021. Use the indirect method to prepare the operating activities section.

• Issued 10,000 new shares of common stock in exchange for a Building. The stock was selling on the market at an average price of $10 per share on the date of sale and the par value of the stock was 50 cents.
• Purchased land with a cost $250,000. A down payment was made in the amount of $100,000 cash and a 10% 5-year note payable was signed for the difference.
• Purchased additional store equipment for $20,000 paying cash.
• The $15,000 notes receivable was related to the sale of merchandise inventory to a credit customer this period. Hint: The increase in notes receivable should be reported as an addition to the operating activities section of the statement of cash flows.
• Issued bonds with a face amount of $375,000 at 97. Hint: The amortization of the bond discount in the amount of $750 should be reported as an addition to the operating activities section.
• Used the cash proceeds from the bond issue to pay off the mortgage payable of $150,000.
• The company repurchased 20,000 shares of its common stock on the open market for $9 per share.
• The company reissued 10,000 of the treasury shares at a price of $18 per share.
• Issued 1,500 shares of preferred stock at $105 per share.
• Paid cash dividends of $25,000 to preferred and common stockholders.

Phase #4

Prepare the following Ratios for January 2021:

1. Current Ratio
2. Acid-Test Ratio
3. Debt Ratio
4. Profit Margin Ratio
5. Gross Margin Ratio

Be Prepared Inc. Balance Sheet December 31, 2020 Credits Debits 423,615.00 $ 25,000.00 $ $ 25,000 7,500 540.00 175,000 150,000 357,500 100.00 15,000.00 7,200.00 8,000.00 500.00 1,600.00 375,000.00 500,000.00 Assets Cash Accounts Receivable Merchandise Inventory Office Supplies Prepaid Insurance Land Buildi Building Accumulated Depreciation, Building Office Equipment Accumulated Depreciation, Office Equipment Store Equipment Accumulated Depreciation, Store Equipment Patents Total Assets Liabilities 74,800 Accounts Payable 40,000 Income Taxes Payable 50,000 Note Payable 1500 1,500 Mortgage Payable 1000 3,000 Total Liabilities 125.000 125,000 400 400,000 Stockholder's Equity (63,000) Preferred Stock 70,000 Common Stock (15,000) Paid in Capital in Excess of par: Preferred 75,000 Paid in Capital in Excess of par: Common (15,000) Retained Earnings 65,800 Total Stockholder's Equity 812,100 Total Liabilities and Stockholder's Equity 52,500 115,000 61,250 200,000 25,850 454,600 72,000.00 70,000.00 17,500.00 S 812,100 95,000.00 15,800.00 65.000.00 30,000.00 12,500.00 5,500.00 325,000.00 375,000.00 10,500.00 Be Prepared Adjusted Trial Balance January 31, 2021 Account ID Account Description 1001 Cash 1021 Accounts Receivable 1022 Allowance for Doubtful Acc 1027 Interest Receivable 1031 Notes Receivable 1051 Merchandise Inventory 1101 Store Supplies 1111 Office Supplies 1125 Prepaid Insurance 1401 Land 1411 Building 1412 Ascum Dep: Building 1431 Office Equipment 1432 Accum Dep: Office Equipm 1451 Store Equipment 14 1452 Accum Derr: Store Equipm 1501 Patents 2007 no 2001 Accounts Payable 2005 Income Taxes Payable 2004 2031 Interest Payable 2101 Notes Payable long term 2301 Bonds Payable 2303 Discount on Bonds Payable 3001 Preferred Stock 3011 Common Stock 2904 3101 Paid in Capital in Excess of par: Prefer 3111 Paid in Capital in Excess of par: Com 3121 Paid in Capital Treasury Stock 3300 Retained Earnings 3351 Treasury Stock 4001 Sales Revenue 4002 Sales Discount 4003 Sales Returns & Allowances 4101 Interest Revenue 5001 Cost of Goods Sold 2010 5010 Insurance Expense - General 5012 Repairs Expense: Office 5015 Depreciation Expense: Building - General 5020 Depreciation Exp: Office Eq 5025 Supplies Expense - Selling 5027 Utilities Expense: Office 5030 Office Salaries Expense 5035 Office Payroll Tax Expense 6005 Depreciation Exp: Store Eq 6010 Amortization Expense: Pat - General 6015 Sales Salaries Expense 5020 Sales Payroll Tax Expense 6022 Utilities Expense: Store 6025 Delivery Expense - General 6030 Repairs Expense: Store 6040 Bad Debts Expense - Selling 7001 Interest Expense 210,000.00 120,000.00 61,250.00 295,000.00 90,000.00 850.00 90,000.00 275,575.00 750.00 5,250.00 50.00 125,500.00 300.00 2,100.00 9,000.00 ro 2,500.00 2,400.00 1,100.00 25,000.00 7,000.00 800.00 800.00 20,000.00 5,600.00 1,200.00 1,500.00 750.00 Activate Windows Go to Settings to activate Windows, 2,000.00 6,500.00 1,906,565.00 5 1,906,565.00 Page 3 of 4 1027 words Focus 100%