The following UNADJUSTED trial balance is prepared at fiscal year-end 2019 for Nelson Company: Nelson Company Unadjusted Trial Balance December 31, 2019 Credit Debit $1,000 12,500 5,800 2,400 42,900 $15,250 10,000 5,000 27,000 2,200 Cash Merchandise Inventory Store Supplies Prepaid Insurance Store Equipment Accumulated Depreciation Accounts Payable Common Stock Retained Earnings Dividends Sales Sales Discounts Sales Returns and Allowances Cost of Goods Sold Depreciation Expense - Store Equipment Salaries Expense Insurance Expense Rent Expense Store Supplies Expense Advertising Expense Totals 111,950 2,000 2,200 38,400 0 35,000 0 15,000 0 9,800 169,200 169,200 Additonal information to prepare financial statements is: Additonal information to prepare financial statements is: 1. Rent expense and salaries expense are equally divided between selling activities and the general and administrative activities. 2. Nelson Company uses a perpetual inventory system. 3. Prepare adjusting journal entries and an adjusted entries worksheet to reflect each of the following: a. Store supplies still available at fiscal year-end amount to $1,750. b. Expired insurance, an administrative expense, for the fiscal year is $1,400. c. Depreciation expense on store equipment, a selling expense, is $1,525 for the fiscal year. d. To estimate shrinkage, a physical count of ending merchandise inventory is taken. It shows $10,900 of inventory is still available at fiscal year-end. 4. Prepare a multiple-step income statement for fiscal years 2019. Use vertical analaysis in creation of this statement. 5. Prepare a statement of retained earnings at December 31, 2019 6. Prepare a classified balance sheet at December 31, 2019. Use horizontal Analysis in creation of this statement. 7. Compute the following ratios for both years: (ROUND to 2 decimal places) a. Current ratio b. Acid-test ratio c. Gross margin ratio d. Net profit margin e. Debt ratio f. Debt to equity ratio g. Working capital Excel Requirements You are to create an Excel workbook with the following five worksheets. Name the worksheets as follows: 1. Adjusting Entry Worksheet 2. Income Statement for boht years with Verticak Analysis 3. Statement of Retained Earnings 4. Balance Sheet for both years with Horizontal Analysis 5. Ratio analysis for both years As an alternative, if you are familiar with the worksheet approach to handling adjusting entries, you may combine Number 1-3 above into a worksheet that includes the unadjusted trial balance, adjusting entries, and the adjusted trial balance. When creating these worksheets, keep the following in mind: 1. Unadjusted and adjusted trial balances - you DO NOT need to retype the data from this word document into the excel worksheet. You can simply copy the table from this document and paste it into the excel worksheet. This will save you considerable time. 2. Adjusting entries - These entries should be recorded in the 34 and 4th columns of the adjusting entries worksheet D) Focus o TI B 19 FastForward Work Shoot For Month Endod Docomber 31, 2015 0 Dr. Cr No. Account 7 101 Cash B 106 Accounts receivablo @ 126 Supplies 10 129 Propaid insurance 11 167 Equipment 12 168 Accumulated depreciation Equip 13 201 Accounts payablo 14209 Salorios payablo 15 236 Unoarned consulting rovonuo 18 307 Common stock 17 318 Rotainod earnings 18 319 Dividends 19 403 Consulting rovonuo 20 21 406 Rontal revenue 22 612 Dopreciation expense-Equip 23 622 Salarios axponso 24 637 Insurance expono 25 640 Pont exponso 26 652 Supplios exponso 27 600 Utos expenso 20 Totals 29 Not incomo 30 Totals bi 32 Unadjusted Adjusted Income Trial Balance Adjustments Trial Balance Statement Balance Shoot Dr. Cr. Dr. Cr. Dr. Cr. Dr. Cr. 4,276 4275 4 275 0 (91,800 1,800 1.800 9,720 (0)1,050 8,670 8.670 2,400 (a) 100 2,300 2.300 26,000 26,000 26,000 (0) 300 300 300 6.200 6.200 6.200 (0) 210 210 210 3,000 (d) 250 2,750 30,000 30.000 30.000 0 0 o 200 200 200 5,800 (d) 250 7,850 7850 (1.800 300 300 300 (c) 300 300 300 1,400 (0) 210 1.610 1,610 (a) 100 100 100 1,000 1.000 1.000 (611.050 1.050 1,050 305 305 305 15,300 452003853X76154768547685480532150 20 EUR 8 37785 8.150 816043.320 143.220 OD Los al accounts from the ledger, include those accounts (ded in dark green necessary to make song usument. Enter amount available from leder Account Column totals must be equal Combine wted Extend revenue Extend alle tral balance amounts and expense laby, equity with the ad amounts to the and dividends to get the used ncome sotment mounts to uul balance amount come these com Colum local met be equal First Touarow for income Nel come statement come after by the extended to the crede our of wore or Netlost (debit colum Incer two new lines for the () et income or loss ) Enter a wounds and se letters to cross-reference debit and crede aderente Column total mult be equal (1) netcome or on debidores dents Co to be come affer by the amount come of the lost edended to the book 3. Note - the 2 areas above are the ONLY places where you will actually type in values in this assignment. The other worksheets (and the remainder of the adjusting entries worksheet) should consist ONLY of formulas (including cell referencing). Anytime you enter a value when a formula should be used, points will be deducted. 4. Complete the adjusting entries worksheet showing and adjusted trial balance, income statement, and balance sheet. See example below. In each of the columns of the adjusted trial balance column (debit and credit columns), I'd like you to create a formula that can be copied down the entire column and create accurate results for any combination of values. DO NOT enter formulas that differ down the column. 5. Income statement - prepare a MULTI-STEP Income statement with selling and administrative expenses reported separately. All values on this statement should be referenced from the adjusted trial balance worksheet. DO NOT TYPE IN THE VALUES. Use cell referencing or a formula. a. For the Net Income/Net Loss line, apply conditional formatting so that both the words Net Income/Net Loss AND the value will appear as red if the firm has a net loss. This is accomplished using conditional formatting (see video). 6. Statement of Retained Earnings - all values should be referenced from the adjust tiral balance and the income statement worksheets. DO NOT TYPE IN THE VALUES. Use cell referencing or a formula. 7. Balance Sheet - prepare a CLASSIFIED BALANCE SHEET. Again, DO NOT type in values Use cell references from the adjusted trial balance and the retained statement. Use cell referencing or a formula. 8. Ratio Analysis Sheet - calculate the required ratios. Use formulas. DO NOT TYPE IN VALUES. Use cell referencing or a formula