Comprehensive Accounting Assignment: Financial Statement Analysis

This document presents a student's solutions to an accounting assignment, addressing various aspects of financial accounting. The assignment covers topics such as using Excel for financial calculations, including named cells, formatting negative numbers, and creating separate spreadsheets to avoid errors. It also delves into the application of the IF statement in Excel, periodic inventory systems, and the preparation of trading, profit and loss accounts, and balance sheets. The assignment further explores bank reconciliation processes, journal entries for accounts receivable, and methods for estimating bad debts. Additionally, it examines the impact of accounts receivable on a firm's liquidity, the conversion of credit sales to notes receivable, and the analysis of Wesfarmers' 2016 annual report. The student provides detailed explanations, examples, and relevant formulas to support the answers.

Student name:
Student ID number:
Assignment task number: 2
Subject code and name:
THIS IS MY OWN WORK, AND HASN’T BEEN PLAGIARISED with all the sources
been acknowledged

Table of Contents
Q1................................................................................................................................................3
Q2................................................................................................................................................3
Q3................................................................................................................................................3
Q4................................................................................................................................................4
Q5................................................................................................................................................4
Q6................................................................................................................................................5
C..................................................................................................................................................8
Q7................................................................................................................................................8
Q8 Bank Reconciliation..............................................................................................................8
Q9................................................................................................................................................9
Q10..............................................................................................................................................9
Q11............................................................................................................................................10
Q12............................................................................................................................................10
Q13............................................................................................................................................11
Wesfarmers Annual Report 2016..............................................................................................11
References.................................................................................................................................13

Q1.
It is simpler to replace the cell reference with any name so that the work becomes easy, simple
and fast. For this replacement the name box can be utilised. The formula bar's left side is for
name box. The user can simply understand with the name and file becomes simple (Szulewski,
2016). Any cell, table and function can be given a name. These named cells can be simply
updated, reviewed or managed.
Eg: excel attached
Q2
The negative numerals can be marked in red, red brackets, negative symbol etc. The choice can
be made by using the option of "format cells", and there is even a choice of using decimals
(Brennan, 2016). The signs can depend on the appearance needed by the user, content needed by
the user, option to be utilized, ease of use etc. For example, the excel spreadsheet has been
attached.
Q3
An accountant has to create a separate reported spreadsheet for avoiding the "black box effect”
as the person who creates the spreadsheet is aware of its workings and must have the capacity to
change it in the right manner (Alfian, 2013). The data doesn't get manipulated if the different
sheets are used for graphs and data presentation. This way there would be less errors and
improved decisions. Its example has been attached.

Q4
The link for YouTube instance utilized to give details of ‘IF statement’ is
https://www.youtube.com/watch?v=fcROq683zSE ("Excel if function", 2017)
The "IF statement" is one which creates Logic where the result is based on specific criteria and
its happening or not happening. The attached file shows how the statement has been applied to
the column D where it has been seen that all the payments of commissions which are more than
$40,000 have been already paid and those with lesser commission are yet to be paid. This is
helpful for the management to keep the data and set the criteria appropriately.
Salesperson Sales ($) Commission Payment status
Roger 200,000.00$ 20,000.00$ yet to be paid
Alina 352,000.00$ 35,200.00$ yet to be paid
Paul 652,456.00$ 97,868.40$ paid
Diana 256,712.00$ 25,671.20$ yet to be paid
Steve 752,683.00$ 112,902.45$ paid
Q5
The periodic inventory system, just updates the ending inventory balance for the general ledger
when the physical inventory counting is carried out. As the physical inventory cannot be
counted every time and it takes a huge time, some businesses carried out this once in a quarter or
a year. And until that counting happens, the accounting system shows the cost of inventory that
had been there in the last counting. Till then all the purchases are captured in the purchases
account and once counting just got it out this balance his move into inventory account for
matching the cost of ending stock (Research Conference on the Changing Roles of Management
Accounting as a Control System, 2014). For example a seller would keep a record of sales every

Sunday and manages the invoices until those are recorded in the ledger (Starling, 2013). Till then
the balance of the ledger remains same as the past week's balance.
Q6
Trading Account
Particulars Amount ($) Particulars Amount ($)
To opening stock 56,980.00 By Sales 166,000.00
to purchases 89,700.00 less returns -3,430.00
less returns - 7,690.00 less discount - 3,450.00
less discount - 4,015.00 By unearned sales
revenue (earned now)
2,900.00
to gross profit 105,045.00 By closing stock 78,000.00
240,020.00 240,020.00
Profit and Loss Account
Particulars Amount
($)
Particulars Amount ($)
To depreciation for
selling exp
3,850.00 By gross profit 105,045.00
To depreciation for
general exp
3,850.00

To accrued interest 1,400.00
To selling exp 23,700.00
To general exp 14,500.00
To supplies 2,400.00
To accrued salaries 2,300.00
To net profit 53,045.00
105,045.0
0
105,045.00

Supplies Account
Particulars Amount ($) Particulars Amount ($)
To balance b/d 7,560.00 By gen exp 800.00
By selling exp 1,600.00
By balance c/d 5,160.00
7,560.00 7,560.00
Balance sheet
Assets Amount ($) Liabilities Amount ($)
Cash 12,450.00 Capital account 142,675.00
building 14,500.00 Net profit 53,045.00
accounts receivables 17,650.00 less drawings - 4,590.00
Furniture 23,780.00 Notes payable 34,000.00
Accrued interest 1,400.00 Unearned sales revenue 4,750.00
Accrues salaries 2,300.00 Accounts payable 17,400.00
Supplies 5,160.00 Accumulated dep for building 13,600.00
Inventory 78,000.00 Accumulated dep for furniture 2,060.00

C.
The use of spreadsheets is beneficial as the spreadsheets can be simply exchanged with any tools
or systems. These can be attached in the mail or any application. In case any highly skilled
person makes its template or uses formula in the templates then anyone can use these simple.
Q7
The question seventh in the sheet shows both along with the formula view and the same view can
be attained by using simple steps:
 Ctrl+A,
 Ctrl+~ (Goldmeier, n.d.)
Q8 Bank Reconciliation
Bank statement balance as on30th April $19,670
Book balance as on 30th April $19,031
Adjustments:
Rent received by EFT $600
Insurance payment -$300
NSF cheque from customer -$1,700
Notes receivable $1,500
Bank Service charges -$40
Book error for cheque number 1419 -$340
Deposit within transit $1,543
Outstanding cheques -$2,462
Corrected Balances $18,751 $18,751

Q9
a) Accounts receivables A/c Dr $34,000
To Sales $34,000
(For sales on credit)
b) Cash A/c Dr $17,400
To Accounts receivables $17,400
(For collection of part amount owed)
c) Bad debt A/c Dr $16,600
To Accounts Receivables $16,600
(For writing off the accounts receivable)
d) Accounts receivables A/c Dr $16,600
To Bad debt A/c $16,600
(For reinstating an amount written off)
e) Cash A/c Dr $16,600
To Accounts receivables $16,600
(For collection in full of the amount owed)
Q10
Taking an example of a supplier which supplies cosmetics to their different businesses, the
balance for accounts receivable is $10,000. Looking at the previous few ears trend it has been

seen usually the 3% of the receivable score for better. Therefore under the method of sales, if the
income statement has sales for $6000 then the journal entry would be passed for 3% * $6000=
$180
Bad debts a/c dr $180
To allowances for losses $180
As per the income method, it would be evaluated as 3% of $10000 that is $300. For this, the
journal entry would be
Bad debts a/c dr $300
To allowances for losses $300
Q11
The accounts receivable form a part of financial position and these can be used to see how much
cash would be coming up with the business. With the past trends of business can also see how
much collectables would be converted into cash and how much are expected to be bad debts.
Therefore the receivables have a huge effect on the liquidity of the firm. The business uses it
stock to produce the goods and then the same is changed into accounts receivable and finally
cash generated from the same. So it can be said that receivables if it the liquidity of firm and
therefore these affect the financial position also.
Q12
An example of this is when Angels Company sells the supplies to Rogers Ltd for $70,000.
This payment has to made within 45 days however, the contract states that after the two months
there would be notes payables given to Rogers Ltd and that would be of $85,000 with 10%
interest for payment on quarterly basis. Therefore the journal entries would be as below.

Entry for credit sales:
a) Rogers Ltd Dr $70,000
To sales $70,000
b) Accounts receivable A/c Dr $70,000
To sales $70,000
Entry to change to notes receivables
Notes receivable Dr $85,000
To accounts receivable $85,000
Entry for dishonouring of note
Bad debts A/c Dr $93,500
To notes receivable $85,000
To interest receivable $8,500
Entry for next delayed payment
Cash account Dr $93,500
To bad debts account $93,500
Q13
Wesfarmers Annual Report 2016
The report has made for the understanding of accounting and its impact on this Wesfarmers.
After studying the net profit of the business and its financial reports, it is suggested to one of the
friends that it is absolutely okay to purchase of course these are likely to be beneficial

It is a huge group of companies which are dealing into supermarts, liquor, coal, energy products,
improvement goods, convenience stores, safety items, chemicals and fertilisers and the office
supplies.
Looking at the reports of the business its net profit after tax had been $407 million for that year
ending 2016. The same has been shown in the spreadsheet attached along with the 3-D graph.
This is one of the famous companies all of the world and it is also a court of the stock exchange
of Australia. The stakeholders look into its reports to see how to businesses performing and to
protect its profitability in the liquidity. It is also used for assessing the business in comparison to
its competitors.
The financial reports show that $2.87 billion have been earned as net profit after tax in the year
2016, which had been approximately 606% more than the previous year’s $407 millions.
Looking at the graphs it can also be seen that the cash reserves for the business had become
better and its casual margins also improved because The Company was capable of running $4.23
billion through its operations.
The company is performing better because its dividends per share and earnings per share have
been improving yearly. This trend of betterment has made the business capable of sustaining in
such a competitive environment where many businesses have surrendered to losses or to
liquidation. This capacity and its debt capital ratio show how the business has been capable to
survive and is expected to continue growing. Even though the risk of investment is always there,
still after studying the financial reports of this company it is advisable that the investment can be
made. There has been a rise in the working capital due to this business acquiring home-base
along with the retail portfolios.
Working capital ratio =Current assets/ current liabilities