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.
To complete Assignment 1,please downloadACC3
06 - Assignment 1
Excel File.
Part I:
- Using the data provided by your professor, create an 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 YELLOW.
- Using the data from your balance sheet and income statement, conduct the requested ratio analyses.
- You will submit one Excel workbook for Part I
Part II
- Write a two to three (2-3) page memo in which you summarize the firm’s financial position, based on the information provided in Part I.
- The memo will be submitted electronically as a Microsoft Word document with SWS formatting.
Balance Sheet Accounts payable and accrued expensesJune 30, 20XB80,085 Accounts payable and accrued expensesDecember 31, 20XA85,979 Accumulated other comprehensive incomeJune 30, 20XB266 Accumulated other comprehensive incomeDecember 31, 20XA32 Additional paid-in capitalJune 30, 20XB1,304,170 Additional paid-in capitalDecember 31, 20XA1,306,653 Cash and cash equivalentsJune 30, 20XB$ 352,387 Cash and cash equivalentsDecember 31, 20XA$ 311,732 Common stock, par value $0.01June 30, 20XB219 Common stock, par value $0.01December 31, 20XA217 Contract liabilitiesJune 30, 20XB40,826 Contract liabilitiesDecember 31, 20XA38,733 Deferred income tax liabilitiesJune 30, 20XB70,298 Deferred income tax liabilitiesDecember 31, 20XA59,358 GoodwillJune 30, 20XB732,799 GoodwillDecember 31, 20XA732,540 Income taxes payableJune 30, 20XB6,144 Income taxes payableDecember 31, 20XA419 Intangible assets, netJune 30, 20XB314,511 Intangible assets, netDecember 31, 20XA328,344 Lease liabilities, currentJune 30, 20XB26,462 Lease liabilities, currentDecember 31, 20XA Lease liabilities, non-currentJune 30, 20XB90,501 Lease liabilities, non-currentDecember 31, 20XA Marketable securities, currentJune 30, 20XB36,486 Marketable securities, currentDecember 31, 20XA37,121 Marketable securities, non-currentJune 30, 20XB31,866 Marketable securities, non-currentDecember 31, 20XA37,678 Other assetsJune 30, 20XB19,052 Other assetsDecember 31, 20XA19,429 Other current assetsJune 30, 20XB16,874 Other current assetsDecember 31, 20XA15,814 Other long-term liabilitiesJune 30, 20XB37,636 Other long-term liabilitiesDecember 31, 20XA51,316 Property and equipment, netJune 30, 20XB119,040 Property and equipment, netDecember 31, 20XA122,677 Retained earningsJune 30, 20XB118,783 Retained earningsDecember 31, 20XA118,322 Right-of-use lease assetsJune 30, 20XB101,533 Right-of-use lease assetsDecember 31, 20XA Receivable, netJune 30, 20XB50,842 Receivable, netDecember 31, 20XA55,694 Other current assetsDecember 31, 20XA Total current assetsDecember 31, 20XA Total assetsDecember 31, 20XA Total current liabilitiesDecember 31, 20XA Total liabilitiesDecember 31, 20XA Total stockholders’ equityDecember 31, 20XA Total liabilities and stockholders’ equityDecember 31, 20XA Other current assetsJune 30, 20XB Total current assetsJune 30, 20XB Total assetsJune 30, 20XB Total current liabilitiesJune 30, 20XB Total liabilitiesJune 30, 20XB Total stockholders’ equityJune 30, 20XB Total liabilities and stockholders’ equityJune 30, 20XB Income Statement Amortization of intangible assetsJune 30, 20XB15,417 Amortization of intangible assetsDecember 31, 20XA0 General and administrationJune 30, 20XB64,139 General and administrationDecember 31, 20XA31,342 Income before income taxesJune 30, 20XB Income before income taxesDecember 31, 20XA Income from operationsJune 30, 20XB Income from operationsDecember 31, 20XA Net incomeJune 30, 20XB Net incomeDecember 31, 20XA Operational CostsDecember 31, 20XA68,452 Operational CostsJune 30, 20XB134,050 Other costsJune 30, 20XB7,179 Other costsDecember 31, 20XA5,347 Other incomeJune 30, 20XB3,327 Other incomeDecember 31, 20XA289 Provision for income taxes (20%)June 30, 20XB Provision for income taxes (20%)December 31, 20XA RevenuesJune 30, 20XB$ 246,508 RevenuesDecember 31, 20XA$ 116,469 Total costs and expensesJune 30, 20XB Total costs and expensesDecember 31, 20XA