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 2022 using a perpetual inventory system. They have provided you with the following information: i. A list of the monthly transactions ii. The opening balances as at January 1, 2022 iii. Subsidiary ledger information for A/R and A/P iv. 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, 2021: 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, 2021: 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 Suggested Sales Price Quantity Cost A001 Each $75.00 210 $25.00 A002 Each 59.00 1,400 15.00 A003 Each 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, 2022 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 2022. All entries must be dated with the correct date and document numbers. 1-Jan Issued cheque no. 682, for January office rent paid to Cressey Ltd. (invoice no. 780) $3,000 plus GST (debit Rent Expense). 2-Jan 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. 3-Jan 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%). 4-Jan Received net amount of cash on account from Rain Gear $5,782, (cheque no. 60) within discount period. 4-Jan Issued cheque no. 683 to pay salaries of $1,580 to Jean Paul. 5-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. 5-Jan 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). 5-Jan 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%). 6-Jan Collected $175 interest revenue from Vancity Credit Union, deposit code no. 95. 7-Jan 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). 8-Jan 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. 11-Jan Issued cheque no. 686 in payment of account for Suncrest Supply. 12-Jan Received a cheque from MEC (cheque no. 650) in full payment of their accounts receivable from January 2nd.

18-Jan Issued cheque no. 687 to pay salary expense to Jean Paul of $2,100. 22-Jan Purchased furniture on credit terms of 3/15 n/60 from Beaver Corp. $1,500 plus GST (5%) & PST (7%), invoice no. 175. 23-Jan 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. 24-Jan Received 25% the account receivable due from invoice #500 from The Bay which totaled $10,770 (cheque no 62). 25-Jan Issued cheque no. 689 to pay utilities bill to B.C. Hydro invoice number 78, $374 plus GST and PST. 25-Jan Purchased 50 units of A001 for $1,250 plus GST and paid by issuing cheque no. 688 to Marlow Ltd. (purchase invoice no. 25). 26-Jan 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). 30-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. 30-Jan Granted a sales allowance of $1,750 to The Bay and issued a memo no. SR 4030. Ignore GST. 31-Jan 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). 31-Jan 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). 31-Jan 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, 2022 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 - Courier & Postage - Amortization Expense (Furn & Equip) - Income Taxes - Insurance - Interest & Bank Charges - Office supplies expense - Property Taxes - Miscellaneous - Rent - Repair & Maintenance - Telephone - Travel & Entertainment - Utilities - Cleaning Supplies expense - 175,115.00 175,115.00