FMGT $2308$: 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 $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 $1$st 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,2019$:

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,2019$:

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

A$001$ Each $$75\mathrm{.}00210$ $$25\mathrm{.}00$

A$002$ Each $59\mathrm{.}001,40015\mathrm{.}00$

A$003$ Each $39\mathrm{.}005,05812\mathrm{.}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\mathrm{.}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 $2$nd$,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\mathrm{.}0$:$1\mathrm{.}0,$ a quick ratio of $2\mathrm{.}0$:$1\mathrm{.}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 # A$001$ 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 #A$002$ 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 #A$003$ for $$1,950$ plus GST $(5\%)$ and $$42\mathrm{.}00$ freight including GST$(5\%).$ The cost of the merchandise was $$625.$

$5-$Jan Credit sales return memo # CN$102$ was issued to Nordstrom for $40$ units of Inventory