Create the adjusted trial balance and the financial statements for a merchandising company for the fiscal year ending December 31, 2020. You will make up all numbers included in the adjusted trial balance, and ultimately the financial statements, except where a required balance is indicated, as detailed below. The following must be reflected in those statements: General information: . it was the company's first year of operations; . the company is organized as a corporation; the company has both cumulative preferred and common stock issued; . on December 31, the company still held all treasury stock that was repurchased during the year; . the company declared dividends during December in an amount large enough for common stockholders to receive a dividend (Statement of Stockholders' Equity must identify amount declared for preferred stockholders and amount declared for common stockholders); Required account balances: the cash account has an ending balance of $30,000; the company recognized bad debt expense on December 31 in the amount of $13,000 (allowance for doubtful accounts method is used- not the direct method); the company issued $135,000 8-year, 5% bonds on July 1; the bonds pay interest semiannually on January 1 and July 1, and the effective interest rate method is used to amortize the bonds; the market rate of interest was 6% on the day of issuance (Use CH14 - Table 2 and CH14 - Table 4. posted on D2L with the chapter 14 materials, when present value tables are required.); on December 1, the company invested in one debt security investment, which it still held on December 31 and is classified as an available-for-sale security - the investment was (a) purchased for $90,000; (b) has an annual interest rate of 6%; (c) pays interest every February 28 and (d) has increased in value since its purchase (round any required balances to the nearest whole number); Additional information pertaining to account balances: use only whole numbers throughout the project - no cents or place holders for cents; the company is a merchandiser with an ending inventory on December 31; the company purchased only one long-term, depreciable asset during the year and depreciation was recognized - in addition, the asset was still in service on December 31 and will be in service during 2021; . the company has at least four current liabilities; . the bond described in the "Required account balances" section above is the only interest-bearing liability that the corporation has ever entered into; . the company had invested in only one non-influential equity investment, which it still held on December 31 - the investment had decreased in value since its purchase and management will sell it in April of 2021; and the income statement SHOULD NOT INCLUDE the earnings per share calculation, because the number of shares outstanding during the fiscal year did not remain constant throughout (which would require a weighted-average calculation, a concept not covered in ACTG 2125). Worksheet 1 in your Excel document - labeled "Narrative": Part A: Narrative (5 points) Provide the following, per the formatting instructions provided in the "Excel Formatting Project" video: Company Name: Product: Preferred Stock Par: Authorized: Issued: Outstanding: Percentage: Common Stock Par: Authorized: Issued: Outstanding: Worksheet 2 in your Excel document - labeled "ATB": Part B: Adjusted Trial Balance (25 points) Employ the formatting instructions provided in the "Excel Formatting-Project" video. The accuracy of the required" numbers will be assessed here and again in the financial statements. On the ATB worksheet, employing the instructions provided in the "Additional Calculations on the ATB" video, complete the following tasks: 1. List the following labels in a column in the following order: Total Assets Total Liabilities Total Equity (not including the income statement accounts) Net Income 2. In the next column to the right, use a function to calculate the total for each of those respective items listed in part 1 immediately above). 3. In the cell immediately below the total for "net income", use a function to calculate the following: Total Assets minus Total Liabilities minus Total Equity minus Net Income (using the totals you calculated in step 2 immediately above). NOTE: You should arrive at a total of zero. Create the adjusted trial balance and the financial statements for a merchandising company for the fiscal year ending December 31, 2020. You will make up all numbers included in the adjusted trial balance, and ultimately the financial statements, except where a required balance is indicated, as detailed below. The following must be reflected in those statements: General information: . it was the company's first year of operations; . the company is organized as a corporation; the company has both cumulative preferred and common stock issued; . on December 31, the company still held all treasury stock that was repurchased during the year; . the company declared dividends during December in an amount large enough for common stockholders to receive a dividend (Statement of Stockholders' Equity must identify amount declared for preferred stockholders and amount declared for common stockholders); Required account balances: the cash account has an ending balance of $30,000; the company recognized bad debt expense on December 31 in the amount of $13,000 (allowance for doubtful accounts method is used- not the direct method); the company issued $135,000 8-year, 5% bonds on July 1; the bonds pay interest semiannually on January 1 and July 1, and the effective interest rate method is used to amortize the bonds; the market rate of interest was 6% on the day of issuance (Use CH14 - Table 2 and CH14 - Table 4. posted on D2L with the chapter 14 materials, when present value tables are required.); on December 1, the company invested in one debt security investment, which it still held on December 31 and is classified as an available-for-sale security - the investment was (a) purchased for $90,000; (b) has an annual interest rate of 6%; (c) pays interest every February 28 and (d) has increased in value since its purchase (round any required balances to the nearest whole number); Additional information pertaining to account balances: use only whole numbers throughout the project - no cents or place holders for cents; the company is a merchandiser with an ending inventory on December 31; the company purchased only one long-term, depreciable asset during the year and depreciation was recognized - in addition, the asset was still in service on December 31 and will be in service during 2021; . the company has at least four current liabilities; . the bond described in the "Required account balances" section above is the only interest-bearing liability that the corporation has ever entered into; . the company had invested in only one non-influential equity investment, which it still held on December 31 - the investment had decreased in value since its purchase and management will sell it in April of 2021; and the income statement SHOULD NOT INCLUDE the earnings per share calculation, because the number of shares outstanding during the fiscal year did not remain constant throughout (which would require a weighted-average calculation, a concept not covered in ACTG 2125). Worksheet 1 in your Excel document - labeled "Narrative": Part A: Narrative (5 points) Provide the following, per the formatting instructions provided in the "Excel Formatting Project" video: Company Name: Product: Preferred Stock Par: Authorized: Issued: Outstanding: Percentage: Common Stock Par: Authorized: Issued: Outstanding: Worksheet 2 in your Excel document - labeled "ATB": Part B: Adjusted Trial Balance (25 points) Employ the formatting instructions provided in the "Excel Formatting-Project" video. The accuracy of the required" numbers will be assessed here and again in the financial statements. On the ATB worksheet, employing the instructions provided in the "Additional Calculations on the ATB" video, complete the following tasks: 1. List the following labels in a column in the following order: Total Assets Total Liabilities Total Equity (not including the income statement accounts) Net Income 2. In the next column to the right, use a function to calculate the total for each of those respective items listed in part 1 immediately above). 3. In the cell immediately below the total for "net income", use a function to calculate the following: Total Assets minus Total Liabilities minus Total Equity minus Net Income (using the totals you calculated in step 2 immediately above). NOTE: You should arrive at a total of zero