Pls show all workings, do in excel, and do all parts,

P4-20 Integrative: Pro forma statements Red Queen Restaurants wishes to prepare finan- cial plans. Use the financial statements and the other information provided below to prepare the financial plans. The following financial data are also available: (1) The firm has estimated that its sales for 2020 will be $900,000. (2) The firm expects to pay $35,000 in cash dividends in 2020. (3) The firm wishes to maintain a minimum cash balance of $30,000. (4) Accounts receivable represent approximately 18% of annual sales. (5) The firm's ending inventory will change directly with changes in sales in 2020. (6) A new machine costing $42,000 will be purchased in 2020. Total depreciation for 2020 will be $17,000. (7) Accounts payable will change directly in response to changes in sales in 2020. (8) Taxes payable will equal one-fourth of the tax liability on the pro forma income statement, (9) Marketable securities, other current liabilities, long-term debt, and common stock will remain unchanged. a. Prepare a pro forma income statement for the year ended December 31, 2020, using the percent-of-sales method b. Prepare a pro forma balance sheet dated December 31, 2020, using the judgmen- tal approach. Analyze these statements, and discuss the resulting external financing required. c. Red Queen Restaurants Income Statement for the Year Ended December 31, 2019 $800,000 Sales revenue 600,000 $200,000 Less: Cost of goods sold Gross profits Less: Operating expenses Net profits before taxes 100,000 $100,000 21,000 $ 79,000 Less: Taxes (rate = 21%) Net profits after taxes Less: Cash dividends 20,000 $ 59,000 To retained earnings Red Queen Restaurants Balance Sheet December 31, 2019 Assets Liabilities and stockholders' equity Cash $ 32,000 Accounts payable Taxes payable $100,000 Marketable securities 18,000 20,000 Accounts receivable 150,000 Other current liabilities 5,000 Inventories 100,000 Total current liabilities $125,000 200,000 $325,000 Total current assets $300,000 Long-term debt Net fixed assets 350,000 Total liabilities Total assets $650,000 Common stock 150,000 Retained earnings 175,000 Total liabilities and stockholders' equity $650,000 P4-20 Integrative: Pro forma statements Red Queen Restaurants wishes to prepare finan- cial plans. Use the financial statements and the other information provided below to prepare the financial plans. The following financial data are also available: (1) The firm has estimated that its sales for 2020 will be $900,000. (2) The firm expects to pay $35,000 in cash dividends in 2020. (3) The firm wishes to maintain a minimum cash balance of $30,000. (4) Accounts receivable represent approximately 18% of annual sales. (5) The firm's ending inventory will change directly with changes in sales in 2020. (6) A new machine costing $42,000 will be purchased in 2020. Total depreciation for 2020 will be $17,000. (7) Accounts payable will change directly in response to changes in sales in 2020. (8) Taxes payable will equal one-fourth of the tax liability on the pro forma income statement, (9) Marketable securities, other current liabilities, long-term debt, and common stock will remain unchanged. a. Prepare a pro forma income statement for the year ended December 31, 2020, using the percent-of-sales method b. Prepare a pro forma balance sheet dated December 31, 2020, using the judgmen- tal approach. Analyze these statements, and discuss the resulting external financing required. c. Red Queen Restaurants Income Statement for the Year Ended December 31, 2019 $800,000 Sales revenue 600,000 $200,000 Less: Cost of goods sold Gross profits Less: Operating expenses Net profits before taxes 100,000 $100,000 21,000 $ 79,000 Less: Taxes (rate = 21%) Net profits after taxes Less: Cash dividends 20,000 $ 59,000 To retained earnings Red Queen Restaurants Balance Sheet December 31, 2019 Assets Liabilities and stockholders' equity Cash $ 32,000 Accounts payable Taxes payable $100,000 Marketable securities 18,000 20,000 Accounts receivable 150,000 Other current liabilities 5,000 Inventories 100,000 Total current liabilities $125,000 200,000 $325,000 Total current assets $300,000 Long-term debt Net fixed assets 350,000 Total liabilities Total assets $650,000 Common stock 150,000 Retained earnings 175,000 Total liabilities and stockholders' equity $650,000