Your firm has just lost its accounting system. The only thing you have left is the financial reports from the December 31, 20XA and June 30, 20XB reporting period, but the data is garbled. You have been asked by your Controller to reconstruct the Balance Sheet & Income Statements in Excel for each of these reporting periods as well as performing the following ratio analyses: (a) current ratio ; (b) quick ratio; (c)debt ratio; (d) equity ratio; (f)debt-to-equity ratio ;and (g)profit margin for eachreportingperiod.

• Using the data provided below, create a Excel worksheet that contains the firm's balance sheet and income statement. You will be required to use formulas and/functions to recreate the firm's financial statements, such as the items in BOLD.
• Using the data from your balance sheet and income statement, conduct the requested ratio analyses.

INCOME SHEET:

Amortization of intangible assets June 30, 20XB 15,417

Amortization of intangible assets December 31, 20XA 0

General and administration June 30, 20XB 64,139

General and administration December 31, 20XA 31,342

Income before income taxes June 30, 20XB

Income before income taxes December 31, 20XA

Income from operations June 30, 20XB

Income from operations December 31, 20XA

Net income June 30, 20XB

Net income December 31, 20XA

Operational Costs December 31, 20XA 68,452

Operational Costs June 30, 20XB 134,050

Other costs June 30, 20XB 7,179

Other costs December 31, 20XA 5,347

Other income June 30, 20XB 3,327

Other income December 31, 20XA 289

Provision for income taxes (20%) June 30, 20XB

Provision for income taxes (20%) December 31, 20XA

Revenues June 30, 20XB $ 246,508

Revenues December 31, 20XA $ 116,469

Total costs and expenses June 30, 20XB

Total costs and expenses December 31, 20XA

BALANCE SHEET:

Accounts payable and accrued expenses June 30, 20XB 80,085

Accounts payable and accrued expenses December 31, 20XA 85,979

Accumulated other comprehensive income June 30, 20XB 266

Accumulated other comprehensive income December 31, 20XA 32

Additional paid-in capital June 30, 20XB 1,304,170

Additional paid-in capital December 31, 20XA 1,306,653

Cash and cash equivalents June 30, 20XB $ 352,387

Cash and cash equivalents December 31, 20XA $ 311,732

Common stock, par value $0.01 June 30, 20XB 219

Common stock, par value $0.01 December 31, 20XA 217

Contract liabilities June 30, 20XB 40,826

Contract liabilities December 31, 20XA 38,733

Deferred income tax liabilities June 30, 20XB 70,298

Deferred income tax liabilities December 31, 20XA 59,358

Goodwill June 30, 20XB 732,799

Goodwill December 31, 20XA 732,540

Income taxes payable June 30, 20XB 6,144

Income taxes payable December 31, 20XA 419

Intangible assets, net June 30, 20XB 314,511

Intangible assets, net December 31, 20XA 328,344

Lease liabilities, current June 30, 20XB 26,462

Lease liabilities, current December 31, 20XA

Lease liabilities, non-current June 30, 20XB 90,501

Lease liabilities, non-current December 31, 20XA

Marketable securities, current June 30, 20XB 36,486

Marketable securities, current December 31, 20XA 37,121

Marketable securities, non-current June 30, 20XB 31,866

Marketable securities, non-current December 31, 20XA 37,678

Other assets June 30, 20XB 19,052

Other assets December 31, 20XA 19,429

Other current assets June 30, 20XB 16,874

Other current assets December 31, 20XA 15,814

Other long-term liabilities June 30, 20XB 37,636

Other long-term liabilities December 31, 20XA 51,316

Property and equipment, net June 30, 20XB 119,040

Property and equipment, net December 31, 20XA 122,677

Retained earnings June 30, 20XB 118,783

Retained earnings December 31, 20XA 118,322

Right-of-use lease assets June 30, 20XB 101,533

Right-of-use lease assets December 31, 20XA

Receivable, net June 30, 20XB 50,842

Receivable, net December 31, 20XA 55,694

Other current assets December 31, 20XA

Total current assets December 31, 20XA

Total assets December 31, 20XA

Total current liabilities December 31, 20XA

Total liabilities December 31, 20XA

Total stockholders' equity December 31, 20XA

Total liabilities and stockholders' equity December 31, 20XA

Other current assets June 30, 20XB

Total current assets June 30, 20XB

Total assets June 30, 20XB

Total current liabilities June 30, 20XB

Total liabilities June 30, 20XB

Total stockholders' equity June 30, 20XB

Total liabilities and stockholders' equity June 30, 20XB