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.