Please shows formulas for excel and show work

1. Pro-forma Statements

The 2020 Income Statement and Balance Sheet for Longview Products, Inc. (LPI) are shown below

For 20210, the following information is likely:

0. 1. Projected sales are $6,500,000
   2. Cost of goods sold in 2019 includes $1,150,000 in fixed costs
   3. Operating expense in 2019 includes 400,000 in fixed costs
   4. Interest expense will increase to $120,000
   5. LPI will pay cash dividends of 40% of net profits after taxes
   6. Marketable securities will increase to $500,000; notes payable will decrease to $50,000; while long-term debt and common stock will remain unchanged
   7. Cash, inventories, accounts receivable, and accounts payable will change in direct response to the change in sales
   8. Manufacturing equipment costing $600,000 will be purchased during the year
   9. Total depreciation expense for the year will be $250,000
   10. A 21% tax rate will apply
1. Prepare a pro forma income statement for the year ending December 31, 2021 using the fixed cost data given to improve the accuracy of the percent-of-sales method
2. Prepare a pro-forma balance sheet as of December 31, 2021, using the information given and the judgmental approach. Include a reconciliation of the retained earnings account.
3. Analyze these statements and discuss the resulting external financing needed, if any.

Longview Products, Inc. Income Statement For the Year Ended December 31, 2020 Sales revenue Less: Cost of Goods Sold Gross Profits Less: Operating Expenses Operating profits (EBIT) Less: Interest Expense Net profits before taxes Less: Taxes @ 21% Net profits after taxes Less: Cash dividends To retained earnings $ 6,000,000 $ 3,300,000 $ 2,700,000 $ 1,000,000 $ 1,700,000 $ 95,000 $ 1,605,000 $ 337,050 $ 1,267,950 $ 300,000 $ 967,950 Physical Effort Easy Task Cut grass Edge sidewalk Pull weeds Prune bushes, etc. Rake leaves Piece Rate Incentive per Person $4 $1 $6 $5 $5 Easy Time to Complete per Person .4 hr 1 hr .5 hr .5 hr .5 Charge to Customer $30 $ 5 $40 $30 $25 Very Hard Hard Hard Longview Products, Inc. Income Statement For the Year Ended December 31, 2020 Sales revenue Less: Cost of Goods Sold Gross Profits Less: Operating Expenses Operating profits (EBIT) Less: Interest Expense Net profits before taxes Less: Taxes @ 21% Net profits after taxes Less: Cash dividends To retained earnings $ 6,000,000 $ 3,300,000 $ 2,700,000 $ 1,000,000 $ 1,700,000 $ 95,000 $ 1,605,000 $ 337,050 $ 1,267,950 $ 300,000 $ 967,950 Physical Effort Easy Task Cut grass Edge sidewalk Pull weeds Prune bushes, etc. Rake leaves Piece Rate Incentive per Person $4 $1 $6 $5 $5 Easy Time to Complete per Person .4 hr 1 hr .5 hr .5 hr .5 Charge to Customer $30 $ 5 $40 $30 $25 Very Hard Hard Hard