Answered step by step
Verified Expert Solution
Question
...
1 Approved Answer
FMGT 2 3 0 8 : Excel Project West Coast Umbrellas West Coast Umbrellas is an umbrella store that sells high quality umbrellas and has
FMGT : Excel Project West Coast Umbrellas 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 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 iii. Subsidiary ledger information for AR and AP 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: 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. Type the beginning balances from January st on the worksheet and link these beginning balances to the Taccounts setup in a separate worksheet in Excel. Post and link all the ending balances from the totaled columns from your monthly transaction sheet from above to the Taccounts. Post all applicable transactions to subsidiary AR AP ledgers and create a subsidiary ledger for inventory. The Accounts Receivable AR ending Balances December : a Rain Gear, $; b Nordstrom, $; c The Bay, $; d MEC, $; The Accounts Payable AP ending Balances December : a Solly Supply, $; b BestBuy, $; c Suncrest Supply, $; d Beaver Corp, $; The Inventory Ledger information is as follows: Item Unit Suggested Sales Price Quantity Cost A Each $ $ A Each A Each Once you have posted all the transactions in the Taccounts prepare a worksheet starting with the unadjusted Trial Balance then linking the TAccount balances to the worksheet. 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: $ b Estimated allowance for uncollectible AR of total AR c Inventory on hand: $ d Accrued Interest revenue: $ e Depreciation expense: $ f Prepaid insurance expired: $ g Employee Jean Paul will be paid $ on February nd for his work in January h Revenue Earned from prepaid sales: $ i Telephone expenses for January were $ The invoice has not yet been received. Produce a Classified Balance Sheet and Income Statement from the worksheet again linking with named cells. Produce at least one Bar or Pie chart showing some interesting aspect of the Income Statement or Balance Sheet & 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 : a quick ratio of : and a debt ratio of less than In addition, West Coast Umbrellas also has credit terms of days remember you are preparing monthly statements 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 All entries must be dated with the correct date and document numbers. Jan Issued cheque no for January office rent paid to Cressey Ltdinvoice no $ plus GST debit Rent Expense Jan Issued invoice no for sale on account to MEC. Sold units of Inventory # A for a total sales value of $ plus GST & PST The cost of the merchandise was $ Jan Purchased units of inventory item #A at $ each on credit terms of n from Suncrest Supply invoice no totaling $ plus GST Jan Received net amount of cash on account from Rain Gear $cheque no within discount period. Jan Issued cheque no to pay salaries of $ to Jean Paul. Jan Issued invoice no for a cash sale to Tom Shu Ltd for units of inventory item #A for $ plus GST and $ freight including GST The cost of the merchandise was $ Jan Credit sales return memo # CN was issued to Nordstrom for units of Inventory
FMGT : Excel Project
West Coast Umbrellas
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 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
iii. Subsidiary ledger information for AR and AP
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:
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.
Type the beginning balances from January st on the worksheet and link these beginning balances to the Taccounts setup in a separate worksheet in Excel.
Post and link all the ending balances from the totaled columns from your monthly transaction sheet from above to the Taccounts.
Post all applicable transactions to subsidiary AR AP ledgers and create a subsidiary ledger for inventory.
The Accounts Receivable AR ending Balances December :
a Rain Gear, $; b Nordstrom, $; c The Bay, $; d MEC, $;
The Accounts Payable AP ending Balances December :
a Solly Supply, $; b BestBuy, $; c Suncrest Supply, $; d Beaver Corp, $;
The Inventory Ledger information is as follows:
Item Unit Suggested Sales Price Quantity Cost
A Each $ $
A Each
A Each
Once you have posted all the transactions in the Taccounts prepare a worksheet starting with the unadjusted Trial Balance then linking the TAccount balances to the worksheet.
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: $
b Estimated allowance for uncollectible AR of total AR
c Inventory on hand: $
d Accrued Interest revenue: $
e Depreciation expense: $
f Prepaid insurance expired: $
g Employee Jean Paul will be paid $ on February nd for his work in January
h Revenue Earned from prepaid sales: $
i Telephone expenses for January were $ The invoice has not yet been received.
Produce a Classified Balance Sheet and Income Statement from the worksheet again linking with named cells.
Produce at least one Bar or Pie chart showing some interesting aspect of the Income Statement or Balance Sheet & 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 : a quick ratio of : and a debt ratio of less than In addition, West Coast Umbrellas also has credit terms of days remember you are preparing monthly statements
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 All entries must be dated with the correct date and document numbers.
Jan Issued cheque no for January office rent paid to Cressey Ltdinvoice no $ plus GST debit Rent Expense
Jan Issued invoice no for sale on account to MEC. Sold units of Inventory # A for a total sales value of $ plus GST & PST The cost of the merchandise was $
Jan Purchased units of inventory item #A at $ each on credit terms of n from Suncrest Supply invoice no totaling $ plus GST
Jan Received net amount of cash on account from Rain Gear $cheque no within discount period.
Jan Issued cheque no to pay salaries of $ to Jean Paul.
Jan Issued invoice no for a cash sale to Tom Shu Ltd for units of inventory item #A for $ plus GST and $ freight including GST The cost of the merchandise was $
Jan Credit sales return memo # CN was issued to Nordstrom for units of Inventory
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started