Comprehensive Report: Spreadsheet Tools and Applications in Accounting

This report provides a detailed analysis of spreadsheet applications within the field of accounting. It begins with an overview of basic spreadsheet functionalities, including cell referencing and number formatting, and then moves on to demonstrate the use of formulas in accounting calculations. The report covers topics such as inventory systems (periodic vs. perpetual), bad debt estimation (sales percentage and receivables percentage methods), and the handling of accounts receivable. It also includes a case study on Wesfarmers, analyzing its financial statements, dividend policies, and working capital ratios to assess its investment viability. The report uses spreadsheet examples and numerical illustrations to explain concepts and includes journal entries for various financial transactions. Furthermore, it discusses the advantages of spreadsheets in accounting, such as their flexibility in data analysis and reporting, while also acknowledging their limitations, such as the reliance on data accuracy. Overall, the report aims to provide a comprehensive understanding of how spreadsheets are utilized as an essential tool in financial accounting.

1) In Ms-excel there is not necessary to make cell referencing. One can make document
without using cell referencing and put formulas with the default cell reference use by the
excel i.e. column name then row name. Formulas by using default cell referencing, look
like A4-F6 or G7*K2 etc. However, one can make cell referencing by own and ensure
more effective presentation of excel document. Formulas after cell referencing look like
liabilities- assets = equity. Such type of formulas ensures better understanding hence cell
referencing used in making excel document.
Spreadsheet example

2) Negative numbers can be presented in brackets by converting number formatting from
general to custom. Accountants display negative numbers in brackets because it
represents a better presentation and removes misleading which may inure due to hyphens
or a simple sign of dash.
Spreadsheet example

3) Accountants use spreadsheets for making accounting calculations. Every calculation has
some know facts and some facts which derived from the known facts. Accounts separate
data area from the report area because this helps them in separating the know facts and
facts which derived from the known facts. In addition to this such type of presentation
enhance the quality of presentation.
Spreadsheet Example

4) “IF” is a logical function given by the MS-excel. “IF” gives results as per the satisfaction
of function. One more extension is given by MS-excel regarding this function is “IF then
Else” This faction provide results as per satisfaction and non-satisfaction of function.
Spreadsheet Example

5) Inventory can be recorded by the organization by using any of two inventory systems.
Periodic inventory system inventory, Inventory account does not update by the
organization regularly. In place of inventory account Sales and purchases accounts
updated by the organization regularly and at the end of period inventory account updated
and closing stock calculated (Stevenson & Hojati, 2007).
This inventory system cannot apply where real time inventory system is followed (Perpetual vs.
Periodic Inventory Systems - Whiteboard Wednesday, 2013). This system increases the frauds
because those can only be deducted at the end of the period. However, this system is economical
and needs a lower cost of implementation.
For example, if 20 units of goods purchased by the merchandiser then such purchase under
periodic inventory system recorded in the purchase account and not in the inventory account.

6) Hand written solution
Make it yourself by coping spreadsheet solution
Spreadsheet Example

Formula view

Business Report
Introduction
In every business data recording for the all financial transactions are done with accounting.
Accounting includes numbers and calculations for a large data. Hence mostly every person
performing the task of accounting believes on the spreadsheet. This report is written for
assessing spreadsheets as a tool in accounting.
Spreadsheet tool and its advantages
Spredsheet tool is useful in rcording, calculating anf formatting accounting reocrds and data.
This is a flexible application which presents a report from the data by using various calculative
and logical formulas. These aretools useful in data recording as well as data analysing. There are
some specific uses of spredsheets in accounting function,
1. Spreadsheets are able to provide various permutations and combinations for making an
analysis because it contains a large verity of formulas (Cooper).
2. Spreadsheets are having the ability to accumulate large files. Financial accounting files
are always having large data hence spreadsheet become a most suitable tool for recording
financial data.
3. Financial analysis also becomes easier in this tool because this tool having the ability to
connect various data sheets to each other by formulas (Smith).
4. Spreadsheets can make budgets, projected reports as well as a periodical subset.
5. Spreadsheet data is easy to share, understand and making comparisons.
However, there is an only disadvantage of spreadsheets i.e. results of report file of
spreadsheet totally depends on the data of spreadsheet, if data files foes wrong whole results
will go wrong.
Conclusion
Hence above discussion concludes that spreadsheet is the best tool for accounting. Use of this
tool provides appropriate results when it used with the consciousness. This is most
economical simple and flexible tool for accounting.

7) Spreadsheet solution

Changes due to change in data
Above results show that in the position of increasing price FIFO method shows higher profits
and LIFO method shows lower profits. On the other hand, in the position of decreasing price
LIFO method shows higher profits and FIFO method shows lower profits.
Why such changes?
 In case of increasing prices, inventory purchased first having lower prices and under
FIFO method inventory purchased first will sell first. Therefore inventory having lower
prices will sell and COGS will become lesser and in turn, gross profit becomes higher.
 In case of increasing prices, inventory purchased last having higher prices and under
LIFO method inventory purchased last will sell first. Therefore inventory having higher
prices will sell and cost of goods sold will become higher and in turn, gross profit
becomes lower.
 In case of decreasing prices, inventory purchased first having higher prices and under
FIFO method inventory purchased first will sell first. Therefore inventory having higher
prices will sell and cost of goods sold will become higher and in turn, gross profit
becomes lower.
 In case of decreasing prices, inventory purchased last having lower prices and under
LIFO method inventory purchased last will sell first. Therefore inventory having lower
prices will sell and COGS will become lesser and in turn, gross profit becomes higher.

8) Hand written answer
Please make yourself by copying spreadsheet solution
Spreadsheet Solution

9) Resource
X Ltd. makes a sale on credit of $4000 to Mr. ABC on 1 April, $2500 paid by Mr. ABC on 5
April and rest recorded by company as bad debts on 10 April. On 15 April Mr. ABC paid $750
for his total dues.
Date Description Debit Credit
1 April Accounts receivable $4000
Sales $4000
(Sale made on credit)
5 April Bank $2500
Accounts receivable $2500
(Part payment received from accounts receivable) $1500
10 April Bad debts expenses $1500
Accounts receivable
(Remaining unpaid amount recorded as bad debts)
15 April Bank $750
Accounts receivable $750
(Amount received from receivable)
15 April Accounts receivable $750
Bad debts recovery $750
(Bad debts recovery recorded)

10) Bad debts estimation can be done by two ways i.e.
 Seles’s percentage method: In this, estimation done on the basis of pre-estimated
proportion of credit revenue made by the organization during the period. This is income
statement approach for estimating the number of bad debts expenses.
Example:
Credit sales $50,000,000
Estimate regarding bad debt expense 2%
Bad debts expenses $1,000,000
 Receivable’s percentage method: In this, estimation done on the basis of pre-estimated
proportion of accounts receivable at the end of the period (McNichols & Wilson, 1988).
This is balance sheet approach for estimating the number of bad debts expenses.
Example:
Accounts receivable’s balance $5,000,000
Estimate regarding bad debt expense 5%
Bad debts expenses $250,000

11) Mostly organizations make revenue by selling their products on accounts and later on
receive payment from accounts receivable. Accounts receivable arise when organizations
start to make credit sales. This item presented on the assets side of balance sheet under
the head of current assets.
This item is useful in making various analyses which defines the financial position of the
company. Some of them are,
1. Receivable turnover ratio: This ratio how efficiently organization deals with its
receivables. Higher receivable turnover ratio reflects the efficient management of
receivables of the company and lowers bad debts.
2. Current ratio: current ratio is a ratio between the short term assets and liabilities of the
organization. Accounts receivable plays important role in this calculation and conclude
regarding the short term financial position of the company.
3. In addition to these two accounts receivable helps in calculating credit period given by
the company to sale on the account. Higher credit period results in higher bad debts.
Hence above discussion concludes that accounts receivable plays an important role in making
analyzing regarding the financial position of the company.
Numerical example: Following is the data of two companies which majorly based on the
accounts receivable, evaluate which company seems to be better than other
Particulars Company A Company B
Current ratio 2:1 1.5:1
Receivable turnover ratio 2 times 1.5 times
Credit period 30 days 45 days
Assets turnover ratio 5 times 3 times
Comparison of above majors concludes that company A is better then company B because
company A is having batter short term position, higher receivable efficiency, lower credit period
and efficient use of assets.

12) Resource scenario: An account sale made by Mr. X to Mr. A for $1,000 on 1 April. For
making repayment Mr. A issued a note for 2 months on 1 April. Mr. X notarize the note
on 1 April for $50. On the date of maturity note dishonored by Mr. A thereafter on 5
June, Mr. A made full payment.
Journal entries
Date Description Debit Credit
1 April Accounts receivable $1000
Sales $1000
(Sale made on credit)
1 April Notes receivable $1000
Accounts receivable $1000
(Note received from accounts receivable)
1 April Bank $950
Interest expenses $50
Notes receivable $1000
(Discounting of note receivable)
30 May Accounts receivable $1000
Bank $1000
(Note receivable dishonored)
5 June Bank $1000
Accounts receivable $1000
(Amount received from accounts receivable)

13) Investment viability in Wesfarmers
Executive summary
This report is written for providing investment advice to a non-accounting professional person.
In making such investment advice report will make analysis regarding Wesfarmers Company.
This analysis will report about the business of company, make comment on the income statement
of company for the year ended in 2016, analysis regarding the dividend policy, earning per share
and return on capital related policies of company, calculate working capital ratio of company and
will provide sum diagrammatic representation regarding net profit of the company.
Business of company
Wesfarmers Company is a very old company, was founded in 1914. This company is one of the
largest companies in the Australia. The company is having diversified business operations. This
company is making revenue by its supermarkets, the business of liquor, departmental stores, and
products of home improvements, industries of fertilizers, energy, and Cole and having a number
of hotels. The company is presently providing employment to 220000 employees (Wesfarmers,
2017).
Income Statement
Company’s revenue for year 2016 was $65981 million and for the year 2015 was $62447million.
Company’s net profit for the year 2016 was $407 million and for the year 2015 was $2440
million. This denotes that there a higher rate of unfavorable fluctuation even after the increase in
revenue during the year. Such fluctuation in profit is because of increasing in an impairment loss
of the company during the year. Higher impairment loss put direct effects on the company’s
profits.
Investor’s return policies
Company is having following data which are essential for the potential investors of the company,
2016 2017
Earnings per share $36.2 $216.7
Total dividend $2272 million $2214 million

Number of shares outstanding 1126 million 1123 million
Dividend per share $2.02 $1.97
Profit attributable to equity
shareholders
$ 407 million $ 2440 million
Equity share capital $ 22949 million $24781 million
Return on equity 1.78% 9.85%
Above data presents that company’s earnings per share and return on equity majors are showing
negative results. This is not influencing data for the potential investor of the company. However,
deep analysis of this data results that only majors directly related to profit showing a negative
trend. Dividend per share is showing increasing trend. Net profits of this company are showing
declining trend only because of impairment loss which cannot be taken as a recurring expense
and except this assertion, other factors are showing increasing operating profit (wesfarmers,
2016).
Ratio
The working capital ratio evaluates a company’s short term capital maintenance by making a
division of current assets by the current liabilities. The moderate working capital ratio shows the
efficient utilization of short term capital of the company. At present this ratio is showing
following figures in Wesfarmers Company,
Particulars 30 June 2016 30 June 2017
Current Asset $ 9694 million $ 9093 million
Current Liabilities $ 10424 million $ 9726 million
Working Capital Ratio 0.93 0.93
This result concludes that company’s short term financial position is not good and company’s
short term obligations are more than short term resources.
Diagrammatic representation for Profit portion in revenue
Revenue 2016 ( $ millions) 2015 ($ millions)
Expenses 64,984 59,100

Finance costs 308 315
Income tax expenses 631 1,004
Profits 407 2,440
2016
Expenses Finance costs Income tax expenses Profits
2015
Expenses Finance costs Income tax expenses Profits
Conclusion
Above discussion concludes that Wesfarmers is not a good option for making the investment
because this company’s trend regarding profitability is not good. The Short term financial
position i.e. the current working ratio of the company is also not showing better position.

Bibliography
Cooper, S. (n.d.). The Advantages & Disadvantages of Spreadsheets. Retrieved September 15,
2017, from Small business chron: http://smallbusiness.chron.com/advantages-disadvantages-
spreadsheets-26551.html
McNichols, M., & Wilson, G. (1988). Evidence of earnings management from the provision for
bad debts. Journal of accounting research, 1-31.
Perpetual vs. Periodic Inventory Systems - Whiteboard Wednesday. (2013, october 9). Retrieved
september 15, 2017, from You tube: https://www.youtube.com/watch?v=UtLd8H_qz7I
Smith, S. (n.d.). The Advantages of Using Spreadsheets. Retrieved from your business:
http://yourbusiness.azcentral.com/advantages-using-spreadsheets-3647.html
Stevenson, W., & Hojati, M. (2007). Operations management. Boston: McGraw-Hill/Irwin.
wesfarmers. (2016). 2016 annual report. Retrieved September 15, 2017, from wesfarmers:
https://www.wesfarmers.com.au/docs/default-source/reports/2016-annual-report.pdf?sfvrsn=4
Wesfarmers. (2017). WHO WE ARE. Retrieved september 15, 2017, from wesfarmers:
http://www.wesfarmers.com.au/who-we-are/who-we-are