West Coast Umbrellas is an umbrella store that sells high quality umbrellas and has been struggling...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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
Expert Answer:
Answer rating: 100% (QA)
Record Monthly Transactions in Excel Set up an Excel worksheet with columns for different accounts e... View the full answer
Related Book For
Intermediate Accounting principles and analysis
ISBN: 978-0471737933
2nd Edition
Authors: Terry d. Warfield, jerry j. weygandt, Donald e. kieso
Posted Date:
Students also viewed these accounting questions
-
We will be working with a company called Global Bike Inc., (GBI). Information regarding GBI follows. Company History Global Bike Inc. has a pragmatic design philosophy that comes from its deep roots...
-
West Coast Lifestyle is an apparel store that sells high-quality umbrellas and rain jackets and has been struggling with their monthly accounting. The owners know very little about accounting and...
-
As a security architect for a medium-sized firm, briefly describe how would you approach the following case problems: 1. Sending encrypted email to third parties. 2. Encrypting sensitive data inside...
-
Which of the following individuals qualify for the earned income credit? a. Thomas is single, 21 years old, with no qualifying children. His income consists of $9,000 in wages. b. Shannon, who is 27...
-
Compute a simple aggregate price index. Use 1990 as the base period. Price 1990 $0.287 Quantity 2016 Item Aluminum (cents per pound) Natural gas (1,000 cu. ft.) Petroleum (barrel) Platinum (troy...
-
Make a preliminary design for a solar-driven Rankine refrigeration machine to provide the temperature environment required below the surface of a \(20 \times 40 \mathrm{~m}\) iceskating rink that is...
-
Xander Manufacturing Company manufactures blue rugs, using wool and dye as direct materials. One rug is budgeted to use 36 skeins of wool at a cost of $ 2 per skein and 0.8 gallons of dye at a cost...
-
A diver leaves a 3-m board on a trajectory that takes her 2.2m above the board and then into the water 2.6m horizontally from the end of the board. a) At what speed did she leave the board? Express...
-
Reds Furniture Manufacturing produces a line of tables and chairs from specialty hardwoods. It makes three different styles of chairs, and each chair takes about the same amount of direct labor time...
-
Which of the following is NOT describing assurance services? Multiple Choice O nonfinancial information as well as traditional financial statements. relevance as well as the reliability of...
-
Find the value of \(a\) such that the following two matrices have the same determinant. \[\left[\begin{array}{ccc}1 & 3 & 0 \\-2 & a & 0 \\0 & 0 & 4\end{array} ight],\left[\begin{array}{ccc}4 & 0 & 0...
-
Determine whether the following vectors are linearly independent. 2 5 LO +2 3
-
Classic LEGO plastic bricks have been fixtures in homes around the world for more than 70 years. Just 15 years ago, The LEGO Group (TLG) was near bankruptcy, spiraling downward and losing money at a...
-
BAw Perform the indicated operations, if defined, for the following vectors and matrices. -2 1 -3 1 1 A = 1 -3 2 1 32 B = V= W = 0 0 4 5
-
The Berg Balance Scale (BBS), named after its developer Katherine Berg, is a popular clinical test of a person's static and dynamic balance abilities. It is scored on a scale of 0 to 56; the higher...
-
Construct a short-term financial plan for Springfield Snowboards based on its expansion opportunity described in the "Positive Cash Flow Shocks" part of Section 20.1. Base the plan on the following...
-
Interview managers at three companies in your area about their use of ERP. How have their experiences been similar? What accounts for the similarities and differences?
-
Zoop Inc. incurred a net operating loss of $500,000 in 2008. Combined income for 2006 and 2007 was $400,000. The tax rate for all years is 40%. Zoop elects the carryback option. Prepare the journal...
-
In what way is the Securities and Exchange Commission concerned about and supportive of accounting principles and standards?
-
Presented below are two independent situations. 1. On January 1, 2008, Paul Simon Company issued $200,000 of 9%, 10-year bonds at par. Interest is payable quarterly on April 1, July 1, October 1, and...
-
A container of an organic liquid a $25^{\circ} \mathrm{C}$ has a mass of $1100 \mathrm{~kg}$ and a volume of $0.94 \mathrm{~m}^{3}$. Find the liquid's weight, its density, and its specific gravity.
-
A vertical, cylindrical tank with a diameter of $10 \mathrm{~m}$ and a depth of $4 \mathrm{~m}$ is filled to the top with water at $25^{\circ} \mathrm{C}$. If the water is heated to $60^{\circ}...
-
The system in Figure P1.6 is at $20^{\circ} \mathrm{C}$. If atmospheric pressure is $101.3 \mathrm{kPa}$ and the absolute pressure at the bottom of the tank is $264.1 \mathrm{kPa}$, what is the...
Study smarter with the SolutionInn App