How do I create trial balance from this informations given to me?

Palisade Creek Co. is a consulting and merchandising business that uses a perpetual inventory system. Prepare an adjusted trial balance, income statement, retained earnings statement, and classified balance sheet for the quarter ended June 30 (one set of financial statements). ALL OF YOUR WORK SHOULD BE COMPLETED IN MICROSOFT EXCEL. On a separate worksheet, prepare the following ratios: return on assets, debt ratio, current ratio, gross margin ratio, inventory turnover, accounts receivable turnover, total asset turnover, and debt to equity Use Appendix 3B to model your answer. The only difference is your financial statements CAN NOT be in worksheet form. Your worksheet should end with an adjusted trial balance for June 30. The post-closing trial balance for Palisade Creek as March 31, 2020 is as follows: 546,406 233,900 85,000 652,400 7,250 9,550 11,400 597,800 Cash Accounts Receivable Allowance for Doubtful Accounts Inventory Prepaid Insurance Prepaid Rent Store Supplies Equipment Accumulated Depreciation- Equipment Accounts Payable Salaries Payable Unearned Revenue Bonds Payable Discount on Bonds Payable Common Stock Retained Earnings Totals 85,000 296,600 98,400 50,000 500,000 37,194 100,000 880,900 2.067,600 2,067,600 The financial statements for Palisade Creek for the quarter ended March 31, 2020, are as follows: Palisades Creek Income Statement For the quarter ended March 31, 2020 Sales 4,984,000 Cost of Goods Sold 2,823,000 Gross Profit 2,161,000 Operating Expenses Salaries Expense 1,046,900 A201 Capstone Project Instructions and Grading F2020. Page 1 Advertising Expense Miscellaneous Expense Rent Expense Insurance Expense Total operating expenses Net Income 281,000 12,600 382,100 7,800 1.730,400 430,600 Palisades Creek Statement of Retained Earnings For the quarter ended March 31, 2020 Retained Earnings, January 1, 2020 Add: Net income Less: Dividends Retained Earnings, March 31, 2020 585,300 430,600 135,000 880,900 Palisades Creek Balance Sheet March 31, 2020 Liabilities and Stockholders' Equity Current Liabilities 546,406 Accounts payable Salaries payable 148,900 Unearned Revenue 233,900 85,000 296,600 98,400 50,000 Assets Current Assets Cash Accounts Receivable Allowance for Doubtful Accounts Inventory Prepaid Insurance Prepaid Rent Store Supplies Total Current Assets 445,000 500,000 37,194 462,806 100,000 652,400 Total Current Liabilities 7,250 Bonds Payable 9,550 Discount on Bonds Payable 11,400 Stockholders' Equity 1,375,906 Common Stock, $20 par value; 1,000,000 shares authorized; 19,850 shares issued and outstanding Retained Earnings Total Stockholders' Equity 512,800 880,900 980,900 Property, Plant and Equipment Equipment Less: Accumulated depreciation 597,800 85,000 Total Assets 1,888,706 1,888,706 Total Liabilities and Stockholder's Equity A201 Capstone Proiect Instructions and Grading F2020. Page 2 During April, the first month of the quarter, the following transactions were completed: April 1. Issued 15,000 shares of $20 par common stock at $30, receiving cash April 1. Issued 4,000 shares of $80 par preferred 5% stock at $100, receiving cash April 1. Received $4,500 cash from clients as an advance payment for consulting services to be provided during the quarter April 1. Paid salaries that were accrued in the previous quarter April 5. Received cash from clients on account, $2,450 April 9. Paid cash for a newspaper advertisement, $11,000 April 13. Paid Office Station Company for supplies purchased on account last month, $640 April 15. Billed clients for consulting services completed during the first two weeks of the month, $9,180 April 16. The company received $8,360 cash for consulting services performed April 19. Sold merchandise on account to Buttons Co. terms 2/10, n/30, FOB shipping point, $33,450 The cost of goods sold was $17,500 April 20. Purchased supplies on account from Office Station Company, $735 April 21. Billed clients for consulting services completed during the previous two weeks, $4,820 April 21. Sold merchandise on account to Halstad Co. terms 2/10, n/30, FOB shipping point, 22,300 The cost of goods sold was $15,000 April 23. Sold merchandise for cash, $54,000. The cost of goods sold was $32,000 April 25. The company received $7,900 cash for consulting services performed April 27. Received cash from clients on account, $9,520 April 28. Paid employee salaries, $45,000 April 30. Paid utility bills for April, $1,070, the company uses the Miscellaneous Expense account to record utilities April 30. Received $3,300 cash for consulting services performed April 30. Bill clients for consulting services completed during the last few days of the month, $2,650 April 30. Paid dividends $10,500 At the end of April, the following adjustment data was assembled. Analyze and prepare ONLY the adjusting journal entries related to items A-E: A. Insurance expired during April is $275 B. Supplies on hand as of April 30 are $10,715 C. Accrued employee salaries on April 30 is $45,000 D. Rent expired during April is $2,500 E. The balance in the Unearned Consulting Revenue account on April 30 is $13,210 During May, the second month of the quarter, the following transactions were completed: May 1. Paid rent (use rent expense and cash) for May, $5,000 May 1. Sold merchandise to Ryanair Co., terms 2/10, n/30, FOB Destination, $75,000. Ryanair is a new customer and signed an 8% note due in 30 days. The cost of goods sold was $37,550 May 1. Paid salaries that were accrued in the previous month May 1. Sold Equipment for $80,000 cash. The Equipment had cost $150,000 and was 50% depreciated at the time of the sale. All depreciation was already up to date at the time of the sale. May 3. Purchased merchandise on account from Martin Co., terms 2/10, n/30, FOB shipping point, $36,000 May 4. Paid Fedex $600 cash for delivering inventory on May 3 May 6. Sold merchandise on account to Korman Co., terms 2/10, n/30, FOB shipping point, $68,500. The cost of the goods sold was $41,000 May 7. Received $22,300 cash from Halstad Co. on account. The merchandise was purchased last month on April 21 May 8. Korman Co. returned merchandise from sale of May 6. The invoice amount of the returned merchandise was $13,500 and the cost of the returned merchandise was $8,000 May 11. Sold merchandise on account to Gee Co. terms 2/10, n/30, FOB shipping point, 43,776 The cost of goods sold was $16,250 May 13. Paid for merchandise purchased on May 3 May 15. Paid advertising expense for last half of May, $11,000. May 16. Received cash from sale of May 6 May 19. Received $33,450 from Buttons Co. for merchandise purchased on April 19th May 20. Sold merchandise on account to Crescent Co., terms 1/10, n/30, FOB shipping point, $110,000. The cost of goods sold was $70,000 May 21. Received $42,900 cash from Gee Co. from the sale on May 11. May 21. Purchased merchandise on account from Osterman Co., terms 1/10, n/30, FOB destination, $88,000 May 24. Returned damaged merchandise from the May 21, receiving a credit memo from the seller for $5,000 May 26. Refunded cash on sales made for cash, $7,500. The cost of the merchandise returned was $4,800 May 28. Paid salaries of $85,000 May 29. Paid Office Station Company for supplies for cash, $2,400. May 30. Sold merchandise on account to Turner Co., terms 2/10, n/30, FOB shipping point, $78,750. The cost of the goods sold was $47,000. May 30. Received cash from sale of May 20 May 30. Paid for purchase of May 21, less return of May 24. May 31. Paid utility bills for May, $1,070, the company uses the Miscellaneous Expense account to record utilities At the end of May, the following adjustment data was assembled. Analyze and prepare ONLY the adjusting journal entries related to items A-F: A. Inventory on hand at May 31 $497,950 B. Insurance expired during the month $275 C. Supplies on hand at May 31 $10,000 D. Accrued salaries on May 31 $45,000 E. Rent expired during the month $2,500 F. The balance in the Unearned Consulting Revenue account on April 30 is $10,000 During June, the last month of the quarter, the following transactions were completed: June 1. Purchased a Building by issuing a $600,000, 5 year, 9% note to First National Bank June 1. Received cash from sale of May 30 June 1. Paid accrued salaries from last month June 2. Ryanair dishonored the note issued by the company on May 1 June 3. Received cash from clients as an advance payment for consulting services to be provided next month, $45,500 June 5. Received cash from consulting clients for services performed, $12,450 June 9. Paid cash for a newspaper advertisement, $11,000 June 10. Sold merchandise on account to Tricep Co., terms 2/10, n/30, FOB shipping point, $78,750. The cost of the goods sold was $46,000 June 11. Purchased $31,300 of merchandise on account from Oakley Co., terms 1/10 n/30, FOB destination June 13. Paid Office Station Co. for supplies purchased on account on April 20, $735 June 15. Billed clients for consulting services completed during the first two weeks of the month, $9,180 June 15. Paid salaries of $85,000 June 15. Received cash for consulting services performed, $18,360 June 20. Purchased supplies on account, $735 June 24. Ryanair filed for bankruptcy as a result their account was written off. (The allowance method is used in accounting for uncollectible receivables) June 25. Received cash for consulting services performed, $7,900 June 25. Sold merchandise on account to Crescent Co., terms 1/10, n/30, FOB destination, $115,000. The cost of goods sold was $70,000 June 30. Paid utility bills for June, $1,070, the company uses the Miscellaneous Expense account to record utilities June 30. Paid dividends for the quarter ended June 30, $10,500. June 30. Paid the invoice of June 11 after the discount period had passed. June 30. The following information relates the company's end of month bank reconciliation. . Balance according to the bank statement at June 30, $1,419,426 . Balance according to the ledger at June 31, $1,350,536 . Checks outstanding at June 31, $99,840 Deposit in transit, not recorded by bank, $29,500 . Bank debit memo for service charges, $750 A check for $12,700 in payment of utilities expense was incorrectly recorded in the general ledger as $12,000 Based on the bank reconciliation prepared, journalize the entry or entries to be made. Use the Miscellaneous Expense account to record bank service charges and utilities expense At the end of June, the following adjustment data was assembled. Analyze and prepare ONLY the adjusting journal entries related to items A-G: A. Record interest for the following bonds that were issued on January 1, 2020 and appear on the included post- closing trial balance. The $500,000, 5% bonds were issued at a market rate of 6% with interest payable semiannually on June 30 and December 31. B. Ten (10) percent of the $441,510 Accounts Receivable balance are expected to be uncollectible. The Allowance for Doubtful Accounts as of June 30 has a normal balance of $9,500 C. Inventory on hand at May 31, $410,000 D.Prepaid insurance expired during the last month of the quarter, $275 E. Office supplies used during the quarter, $3,920 F. A product warranty was granted beginning June 1 on a new product the company sold in June. The warranty covers a one year period. The estimated cost is 4% of sales, which totaled $193,750 in June. G.Rent expired during the month, $2,500 H.Depreciation is computed as follows: Assets Cost Salvage Value $15,000 Acquisition Date June 1, 2020 Building $600,000 Useful Life in Depreciation Years Method 10 Double Declining Balance Straight line Equipment $419,500 $26,000 5 January 1, 2018 Palisade Creek Co. is a consulting and merchandising business that uses a perpetual inventory system. Prepare an adjusted trial balance, income statement, retained earnings statement, and classified balance sheet for the quarter ended June 30 (one set of financial statements). ALL OF YOUR WORK SHOULD BE COMPLETED IN MICROSOFT EXCEL. On a separate worksheet, prepare the following ratios: return on assets, debt ratio, current ratio, gross margin ratio, inventory turnover, accounts receivable turnover, total asset turnover, and debt to equity Use Appendix 3B to model your answer. The only difference is your financial statements CAN NOT be in worksheet form. Your worksheet should end with an adjusted trial balance for June 30. The post-closing trial balance for Palisade Creek as March 31, 2020 is as follows: 546,406 233,900 85,000 652,400 7,250 9,550 11,400 597,800 Cash Accounts Receivable Allowance for Doubtful Accounts Inventory Prepaid Insurance Prepaid Rent Store Supplies Equipment Accumulated Depreciation- Equipment Accounts Payable Salaries Payable Unearned Revenue Bonds Payable Discount on Bonds Payable Common Stock Retained Earnings Totals 85,000 296,600 98,400 50,000 500,000 37,194 100,000 880,900 2.067,600 2,067,600 The financial statements for Palisade Creek for the quarter ended March 31, 2020, are as follows: Palisades Creek Income Statement For the quarter ended March 31, 2020 Sales 4,984,000 Cost of Goods Sold 2,823,000 Gross Profit 2,161,000 Operating Expenses Salaries Expense 1,046,900 A201 Capstone Project Instructions and Grading F2020. Page 1 Advertising Expense Miscellaneous Expense Rent Expense Insurance Expense Total operating expenses Net Income 281,000 12,600 382,100 7,800 1.730,400 430,600 Palisades Creek Statement of Retained Earnings For the quarter ended March 31, 2020 Retained Earnings, January 1, 2020 Add: Net income Less: Dividends Retained Earnings, March 31, 2020 585,300 430,600 135,000 880,900 Palisades Creek Balance Sheet March 31, 2020 Liabilities and Stockholders' Equity Current Liabilities 546,406 Accounts payable Salaries payable 148,900 Unearned Revenue 233,900 85,000 296,600 98,400 50,000 Assets Current Assets Cash Accounts Receivable Allowance for Doubtful Accounts Inventory Prepaid Insurance Prepaid Rent Store Supplies Total Current Assets 445,000 500,000 37,194 462,806 100,000 652,400 Total Current Liabilities 7,250 Bonds Payable 9,550 Discount on Bonds Payable 11,400 Stockholders' Equity 1,375,906 Common Stock, $20 par value; 1,000,000 shares authorized; 19,850 shares issued and outstanding Retained Earnings Total Stockholders' Equity 512,800 880,900 980,900 Property, Plant and Equipment Equipment Less: Accumulated depreciation 597,800 85,000 Total Assets 1,888,706 1,888,706 Total Liabilities and Stockholder's Equity A201 Capstone Proiect Instructions and Grading F2020. Page 2 During April, the first month of the quarter, the following transactions were completed: April 1. Issued 15,000 shares of $20 par common stock at $30, receiving cash April 1. Issued 4,000 shares of $80 par preferred 5% stock at $100, receiving cash April 1. Received $4,500 cash from clients as an advance payment for consulting services to be provided during the quarter April 1. Paid salaries that were accrued in the previous quarter April 5. Received cash from clients on account, $2,450 April 9. Paid cash for a newspaper advertisement, $11,000 April 13. Paid Office Station Company for supplies purchased on account last month, $640 April 15. Billed clients for consulting services completed during the first two weeks of the month, $9,180 April 16. The company received $8,360 cash for consulting services performed April 19. Sold merchandise on account to Buttons Co. terms 2/10, n/30, FOB shipping point, $33,450 The cost of goods sold was $17,500 April 20. Purchased supplies on account from Office Station Company, $735 April 21. Billed clients for consulting services completed during the previous two weeks, $4,820 April 21. Sold merchandise on account to Halstad Co. terms 2/10, n/30, FOB shipping point, 22,300 The cost of goods sold was $15,000 April 23. Sold merchandise for cash, $54,000. The cost of goods sold was $32,000 April 25. The company received $7,900 cash for consulting services performed April 27. Received cash from clients on account, $9,520 April 28. Paid employee salaries, $45,000 April 30. Paid utility bills for April, $1,070, the company uses the Miscellaneous Expense account to record utilities April 30. Received $3,300 cash for consulting services performed April 30. Bill clients for consulting services completed during the last few days of the month, $2,650 April 30. Paid dividends $10,500 At the end of April, the following adjustment data was assembled. Analyze and prepare ONLY the adjusting journal entries related to items A-E: A. Insurance expired during April is $275 B. Supplies on hand as of April 30 are $10,715 C. Accrued employee salaries on April 30 is $45,000 D. Rent expired during April is $2,500 E. The balance in the Unearned Consulting Revenue account on April 30 is $13,210 During May, the second month of the quarter, the following transactions were completed: May 1. Paid rent (use rent expense and cash) for May, $5,000 May 1. Sold merchandise to Ryanair Co., terms 2/10, n/30, FOB Destination, $75,000. Ryanair is a new customer and signed an 8% note due in 30 days. The cost of goods sold was $37,550 May 1. Paid salaries that were accrued in the previous month May 1. Sold Equipment for $80,000 cash. The Equipment had cost $150,000 and was 50% depreciated at the time of the sale. All depreciation was already up to date at the time of the sale. May 3. Purchased merchandise on account from Martin Co., terms 2/10, n/30, FOB shipping point, $36,000 May 4. Paid Fedex $600 cash for delivering inventory on May 3 May 6. Sold merchandise on account to Korman Co., terms 2/10, n/30, FOB shipping point, $68,500. The cost of the goods sold was $41,000 May 7. Received $22,300 cash from Halstad Co. on account. The merchandise was purchased last month on April 21 May 8. Korman Co. returned merchandise from sale of May 6. The invoice amount of the returned merchandise was $13,500 and the cost of the returned merchandise was $8,000 May 11. Sold merchandise on account to Gee Co. terms 2/10, n/30, FOB shipping point, 43,776 The cost of goods sold was $16,250 May 13. Paid for merchandise purchased on May 3 May 15. Paid advertising expense for last half of May, $11,000. May 16. Received cash from sale of May 6 May 19. Received $33,450 from Buttons Co. for merchandise purchased on April 19th May 20. Sold merchandise on account to Crescent Co., terms 1/10, n/30, FOB shipping point, $110,000. The cost of goods sold was $70,000 May 21. Received $42,900 cash from Gee Co. from the sale on May 11. May 21. Purchased merchandise on account from Osterman Co., terms 1/10, n/30, FOB destination, $88,000 May 24. Returned damaged merchandise from the May 21, receiving a credit memo from the seller for $5,000 May 26. Refunded cash on sales made for cash, $7,500. The cost of the merchandise returned was $4,800 May 28. Paid salaries of $85,000 May 29. Paid Office Station Company for supplies for cash, $2,400. May 30. Sold merchandise on account to Turner Co., terms 2/10, n/30, FOB shipping point, $78,750. The cost of the goods sold was $47,000. May 30. Received cash from sale of May 20 May 30. Paid for purchase of May 21, less return of May 24. May 31. Paid utility bills for May, $1,070, the company uses the Miscellaneous Expense account to record utilities At the end of May, the following adjustment data was assembled. Analyze and prepare ONLY the adjusting journal entries related to items A-F: A. Inventory on hand at May 31 $497,950 B. Insurance expired during the month $275 C. Supplies on hand at May 31 $10,000 D. Accrued salaries on May 31 $45,000 E. Rent expired during the month $2,500 F. The balance in the Unearned Consulting Revenue account on April 30 is $10,000 During June, the last month of the quarter, the following transactions were completed: June 1. Purchased a Building by issuing a $600,000, 5 year, 9% note to First National Bank June 1. Received cash from sale of May 30 June 1. Paid accrued salaries from last month June 2. Ryanair dishonored the note issued by the company on May 1 June 3. Received cash from clients as an advance payment for consulting services to be provided next month, $45,500 June 5. Received cash from consulting clients for services performed, $12,450 June 9. Paid cash for a newspaper advertisement, $11,000 June 10. Sold merchandise on account to Tricep Co., terms 2/10, n/30, FOB shipping point, $78,750. The cost of the goods sold was $46,000 June 11. Purchased $31,300 of merchandise on account from Oakley Co., terms 1/10 n/30, FOB destination June 13. Paid Office Station Co. for supplies purchased on account on April 20, $735 June 15. Billed clients for consulting services completed during the first two weeks of the month, $9,180 June 15. Paid salaries of $85,000 June 15. Received cash for consulting services performed, $18,360 June 20. Purchased supplies on account, $735 June 24. Ryanair filed for bankruptcy as a result their account was written off. (The allowance method is used in accounting for uncollectible receivables) June 25. Received cash for consulting services performed, $7,900 June 25. Sold merchandise on account to Crescent Co., terms 1/10, n/30, FOB destination, $115,000. The cost of goods sold was $70,000 June 30. Paid utility bills for June, $1,070, the company uses the Miscellaneous Expense account to record utilities June 30. Paid dividends for the quarter ended June 30, $10,500. June 30. Paid the invoice of June 11 after the discount period had passed. June 30. The following information relates the company's end of month bank reconciliation. . Balance according to the bank statement at June 30, $1,419,426 . Balance according to the ledger at June 31, $1,350,536 . Checks outstanding at June 31, $99,840 Deposit in transit, not recorded by bank, $29,500 . Bank debit memo for service charges, $750 A check for $12,700 in payment of utilities expense was incorrectly recorded in the general ledger as $12,000 Based on the bank reconciliation prepared, journalize the entry or entries to be made. Use the Miscellaneous Expense account to record bank service charges and utilities expense At the end of June, the following adjustment data was assembled. Analyze and prepare ONLY the adjusting journal entries related to items A-G: A. Record interest for the following bonds that were issued on January 1, 2020 and appear on the included post- closing trial balance. The $500,000, 5% bonds were issued at a market rate of 6% with interest payable semiannually on June 30 and December 31. B. Ten (10) percent of the $441,510 Accounts Receivable balance are expected to be uncollectible. The Allowance for Doubtful Accounts as of June 30 has a normal balance of $9,500 C. Inventory on hand at May 31, $410,000 D.Prepaid insurance expired during the last month of the quarter, $275 E. Office supplies used during the quarter, $3,920 F. A product warranty was granted beginning June 1 on a new product the company sold in June. The warranty covers a one year period. The estimated cost is 4% of sales, which totaled $193,750 in June. G.Rent expired during the month, $2,500 H.Depreciation is computed as follows: Assets Cost Salvage Value $15,000 Acquisition Date June 1, 2020 Building $600,000 Useful Life in Depreciation Years Method 10 Double Declining Balance Straight line Equipment $419,500 $26,000 5 January 1, 2018