West Coast Umbrellas is an umbrella store that sells high quality umbrellas and has been struggling with their monthly accounting. The owners know very little about accounting and have hired you to prepare the financial statements for the month ended January 2024 using a perpetual inventory system. They have provided you with the following information: i. ii. iii. iv. A list of the monthly transactions The opening balances as at January 1, 2024 Subsidiary ledger information for A/R and A/P Additional information to make adjusting entries In addition to preparing the financial statements, the owners also want to use your Excel workbook for the ongoing accounting system. In order to do this properly, you will have to complete the following steps: 1. Record all the necessary transactions for January in the Excel workbook. The accounts should be setup along the top as column titles and a row should be used for each transaction. Setup account debit and credit columns where necessary and total all columns. 2. Type the beginning balances from January 1st on the worksheet and link these beginning balances to the T- accounts setup in a separate worksheet in Excel. 3. Post and link all the ending balances from the totaled columns from your monthly transaction sheet from (1) above to the T-accounts. 4. Post all applicable transactions to subsidiary A/R, A/P ledgers and create a subsidiary ledger for inventory. The Accounts Receivable (AR) ending Balances December 31, 2023: a) Rain Gear, $5,900; b) Nordstrom, $13,590; c) The Bay, $10,770; d) MEC, $0; The Accounts Payable (AP) ending Balances December 31, 2023: a) Solly Supply, $5,600; b) BestBuy, $7,200; c) Suncrest Supply, $0; d) Beaver Corp, $0; The Inventory Ledger information is as follows: Item Unit A001 Each A002 Each A003 Each Suggested Sales Price Quantity Cost $75.00 210 $25.00 59.00 1,400 15.00 39.00 5,058 12.50 5. Once you have posted all the transactions in the T-accounts prepare a worksheet starting with the unadjusted Trial Balance then linking the T-Account balances to the worksheet. 6. Once the unadjusted trial balance is completed enter the following adjusting entries on the worksheet and produce an adjusted trial balance (remember to reference your adjusting entries). a. Office supplies on hand: $1,130 b. Estimated allowance for uncollectible A/R - 1.5% of total A/R c. Inventory on hand: $75,300 d. Accrued Interest revenue: $150 e. Depreciation expense: $750 f. Prepaid insurance expired: $200 g. Employee Jean Paul will be paid $2,500 on February 2nd, 2024 for his work in January h. Revenue Earned (from prepaid sales): $750 i. Telephone expenses for January were $240. The invoice has not yet been received. 7. Produce a Classified Balance Sheet and Income Statement from the worksheet again linking with named cells. 8. Produce at least one Bar or Pie chart showing some interesting aspect of the Income Statement or Balance Sheet & 5 ratios analyzing the financial results for the company. Please note, both your chart and ratios need comments (remember that an important element of financial analysis is the qualitative aspect). The bank loan agreement signed by West Coast Umbrellas has specific covenants that must be met or the loan can be recalled and these include maintaining a minimum current ratio of 4.0:1.0, a quick ratio of 2.0:1.0, and a debt ratio of less than 50%. In addition, West Coast Umbrellas also has credit terms of 30 days (remember you are preparing monthly statements). 9. Use other Excel features to complete this workbook including some of the following: comments, colouring and clear formatting, functions - if statements, formulas, date stamp, inserting objects or any other Excel feature to make it an Interesting report. The following is a list of the transactions for January 2024. All entries must be dated with the correct date and document numbers. 1-Jan 2-Jan 3-Jan 4-Jan Issued cheque no. 682, for January office rent paid to Cressey Ltd. (invoice no. 780) $3,000 plus GST (debit Rent Expense). Issued invoice no. 501 for sale on account to MEC. Sold 45 units of Inventory # A001 for a total sales value of $3,375 plus GST (5%)& PST (7%). The cost of the merchandise was $1,125. Purchased 200 units of inventory item #A002 at $15 each on credit terms of 1/15, n/60 from Suncrest Supply (invoice no. 655) totaling $3,000 plus GST (5%). Received net amount of cash on account from Rain Gear $5,782, (cheque no. 60) within discount period. (hint: invoice total provided above in subledger information) 4-Jan Issued cheque no. 683 to pay salaries of $1,580 to Jean Paul. 5-Jan 5-Jan 5-Jan 6-Jan 7-Jan 8-Jan 11-Jan Issued invoice no. 502 for a cash sale to Tom Shu Ltd. for 50 units of inventory item #A003 for $1,950 plus GST (5%) and $42.00 freight including GST (5%). The cost of the merchandise was $625. Credit sales return memo # CN102 was issued to Nordstrom for 40 units of Inventory item #A002 for $2,360 plus GST (5%) & PST (7%) (COGS $625.00). Issued cheque no. 684, for invoice no.770, a purchase of office supplies from one time vendor Milter Ltd, for $480 plus GST (5%) & PST(7%). Collected $175 interest revenue from Vancity Credit Union, deposit code no. 95. Issued invoice no. 504 for sale on account to The Bay of 275 units of inventory code #A003 with a total retail value $10,725 plus GST (5%) & PST(7%) (COGS $3,437.50). Issued cheque no. 685 to pay Solly Supply $2,600 of the amount owed at Dec 31st. This payment occurred after the end of the discount period. Issued cheque no. 686 in payment of account for Suncrest Supply. (hint: discount on invoice total = 11-Jan Issued cheque no. 686 in payment of account for Suncrest Supply. (hint: discount on invoice total = $30 do not consider taxes.) 12-Jan 18-Jan 22-Jan 23-Jan 24-Jan 25-Jan 25-Jan 26-Jan 30-Jan Received a cheque from MEC (cheque no. 650) in full payment of their accounts receivable from January 2nd. Issued cheque no. 687 to pay salary expense to Jean Paul of $2,100. Purchased furniture on credit terms of 3/15 n/60 from Beaver Corp. $1,500 plus GST (5%) & PST (7%), invoice no. 175. Sold 1,200 units of Inv #A003 to Rain Gear. Issued invoice no. 505 for a total retail sale of $46,800 plus GST (COGS $15,000). PST in this case is exempted as Rain Gear resells these items. The terms are 2/10, net 30. Received 25% the account receivable due from invoice #500 from The Bay which totaled $10,770 (cheque no 62). Issued cheque no. 689 to pay utilities bill to B.C. Hydro invoice number 78, $374 plus GST and PST. Purchased 50 units of A001 for $1,250 plus GST and paid by issuing cheque no. 688 to Marlow Ltd. (purchase invoice no. 25). Purchased cleaning supplies on credit terms 2/10 n/30, invoice no. 96 from Solly Supply $180 plus GST and PST. (Debit to cleaning supplies expense account). Returned 25 units of damaged inventory of item A001 to Marlow Ltd. This was from the purchase 30-Jan 30-Jan 31-Jan 31-Jan 31-Jan Returned 25 units of damaged inventory of item A001 to Marlow Ltd. This was from the purchase made on Jan 25th. PR 25 credit memo was issued by the vendor along with the $625 plus GST cash. Granted a sales allowance of $1,750 to The Bay and issued a memo no. SR 4030. Ignore GST. Purchased 200 units of inventory items A003 for $12.50 each on credit terms 1/15, n/30 from Suncrest Supply for $2,500 plus GST total (invoice no. 657). Issued cheque no. 690 in payment of legal fees of $550 plus GST (5%) & PST (7%) and advertising of $350 plus GST to Barstone and Lister LLP (invoice no. 773). Paid interest and principle owing on the bank loan with cheque no. 691 to Vancity Credit Union. Total payment was $7,000 January interest due was $875, all interest payable was paid and the balance went to repay the principle on the bank loan. The Trial balance as of January 1, 2024 is as follows: Chequing Bank Account 14,830.00 Investments Accounts Receivable 30,260.00 Allowance for Doubtful Accounts Interest Receivable Prepaid Insurance 1,200.00 Office Supplies on hand 1,000.00 - Inventory Umbrellas 89,475.00 Office Furniture & Equipment 38,350.00 Accum. Amort. - Furn. & Equip. 11,650.00 Accounts Payable 12,800.00 Wages & Salaries Payable Bank Loan Current portion - Corporate Taxes Payable PST Payable GST Charged on Sales GST Paid on Purchases Interest Payable 1,250.00 Unearned Revenue (Prepaid sales) 1,000.00 Bank Loan 65,000.00 Mortgage Payable Loans from Shareholders Common Shares 40,000.00 Retained Earnings - beginning 43,415.00 Sales Sales Returns Sales Discounts Freight Revenue Interest Revenue Miscellaneous Revenue Cost of Goods Sold Freight Expense Wages & Salaries Accounting & Legal Advertising & Promotions Bad Debts Mortgage Payable Loans from Shareholders Common Shares Retained Earnings - beginning Sales Sales Returns Sales Discounts Freight Revenue Interest Revenue Miscellaneous Revenue Cost of Goods Sold Freight Expense Wages & Salaries Accounting & Legal Advertising & Promotions Bad Debts Courier & Postage Amortization Expense (Furn & Equip) Income Taxes Insurance Interest & Bank Charges Office supplies expense Property Taxes Miscellaneous Rent 40,000.00 43,415.00 Repair & Maintenance Telephone Travel & Entertainment Utilities Cleaning Supplies expense 175,115.00 175,115.00