For some reason i cannot get the adjusting entries right to make the unadjusted trail balance && The adjusted trial balance to equal out!!

The following ADJUSTED trial balance was prepared for fiscal year end 2018 for Nelson Company. Nelson Company Adjusted Trial Balance 31-Dec-18 Credit Debit $3,000 2,500 5,000 2,000 42,900 $15,250 6,991 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 101,284 2,600 1,200 36 1,525 32,000 1,200 13,000 1,000 9000 155,525 155,525 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: 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. 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 3rd and 4th columns of the adjusting entries worksheet B 20 FastForward Work Shoot For Month Endod December 31, 2015 0 C. 300 6.200 No. Account 7 101 Cash B 106 Accounts receivablo 0126 Supplies 10 128 Prepaid 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 Dividonds 19 403 Consulting rovonuo 20 21 406 Rontal rovenue 22 612 Depreciation expense-Equip. 23 622 Salarios axponso 24 637 Insurance exponso 25 640 Pont expenso 26 652 Supplies exponso 27 600 Utos expenso 2 Totals 29 Not incomo 30 Totals Unadjusted Adjusted Income Trial Balance Adjustments Trial Balance Statement Balance Shoot Dr. Cr. Dr. Cr. Dr. Cr. Dr. Cr. Dr. 4,275 4,275 4 275 0 (11,800 1,800 1.800 9,720 (6)1.050 8,670 8.670 2.400 (a) 100 2,300 2.300 26,000 26,000 26,000 (0) 300 300 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 7,850 101.800 300 300 300 (c) 300 300 300 1,400 (0) 210 1.610 1.610 (a) 100 100 100 1,0001 1.000 1.000 (01.050 1.050 1,050 305 305 305 45,300 45200 375 378154768547005430538500 81 3785 8.150 816043.320.243.320 32 OD Le courts from the ledper,nduse those accounts (ded in dark green necessary to make accounong monts. Encer mounts available from leder ACCO Column toals must be equal Combine wted Extend revenue Extend alle ul balance amounts and expense aby, equity with the RS amounts to the and dividends to put the true come sitement amounts to uul balance mounts colum these com Colum totalmente equal Prst "Totalrow for income Sb Newcome (1) statement com differ by the extended to the crede wou of recome or et lost (debt colum Encer two new lines for the () et income or loss och Encer at Mount and se letters to cross-reference debe and crede aderent Column total mult be equal (1) come or low debidores Como mu be equal rent com differ by the amour de come or het kort edended to the credit bicol 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