Accounting And Spreadsheets: Concepts And Techniques

Naming cells included in spreadsheet

Naming cells included in spreadsheet

Within the spreadsheet the cells are provided names so that the users can easily refer to it. Further, it enables the users to input formulas easily and make the alterations in convenient way, if required (Brief & Peasnell, 2013). The following example will show the revenues and expenses in the spreadsheet.

Negative numbers in the excel sheet can be shown through red colour, putting minus sign in front of the number or showing the number in bracket (Carraher & Van Auken, 2013). Though there is no specific rule regarding the way in which the negative number is to be presented, it can be shown in bracket to make it visible for the users and present it differently from the positive numbers.

Example that shows the negative numbers in bracket is presented below –

Differentiating report area and data area

The data area and the report area in the excel spreadsheet must be differentiated to enable the uses to take decisions based on that (Bebbington, Unerman & O’Dwyer, 2014). The data area represents raw data whereas the report area presents the processed data.  It enables the users to extract both unprocessed as well as processed data from the sale excel sheet.

Normal view –

Formula view –

IF function

YouTube reference for IF function – https://www.youtube.com/watch?v=mr1EbBMksio

IF function is the logical test under spreadsheet and is based on some specific conditions. If the condition is satisfied then it returns TRUE value, else returns the FALSE value (Golyagina & Valuckas, 2016).

Normal view –

Particulars	Amount	Result
Sales revenue	$ 1,00,000.00
Less: Expenses
Salary and wages	$    35,000.00
Interest expenses	$    15,000.00
Depreciation	$    10,000.00
Total expenses	$    60,000.00
Net profit before tax	$    40,000.00
Less: Tax expenses	$    12,000.00
Net profit after tax	$    28,000.00	Profit

Particulars	Amount	Result
Sales revenue	100000
Less: Expenses
Salary and wages	35000
Interest expenses	15000
Depreciation	10000
Total expenses	=SUM(C7:C9)
Net profit before tax	=C5-C10
Less: Tax expenses	=C11*30%
Net profit after tax	=C11-C12	=IF(B13>0,”Profit”,”Loss”)

Perpetual inventory versus periodic inventory

Perpetual inventory system analyses the inventories and record it in the books on continuous basis and thereby the updates are made automatically (Bragg, 2013). On the contrary the periodic inventory system measures the COGS as well as the inventories on occasional basis. The foot video used periodic inventory system (Cho?odowicz & Or?owski, 2015).

Example for perpetual inventory – inventories in the warehouse are measured or counted whenever there is issue of stock or receipt of stock.

Example of the periodic inventory –

COGS = Opening inventory + Purchases – Closing inventory

If, Opening balance on 01-01-2017 = $ 10,000

Purchases made = $ 5,000

Closing balance on 31-12-2017 = $ 13,000

Then, COGS = $ 10,000 + $ 5,000 – $ 13,000 = $ 2,000

Worksheet and Financial report

Introduction – spreadsheet can be used by the user for accumulating large numbers of data in the single sheet and keep a track of that continuously. Further, the user can use various calculations using those data and make the alteration, inclusion and changes as per requirement. The spreadsheets are used by the business houses to properly maintain the data in single place.

Differentiating report area and data area

Discussion –

Various advantages and disadvantages associated with spreadsheet are as follows –

Advantages – spreadsheet offers wide range of advantages especially for the business users. It offers the tools that help in visualize and analyse the data in accessible manner. If the user have the data associated to finance, statistics and numerical the spreadsheet can help to make calculations, preparing graph or chart using those data. Further, it makes data input, deletion and alteration very easy and simple (Myrelid & Olhager, 2015).

Disadvantages – the main issue associated with spreadsheet that the user needs special knowledge of spreadsheet to use it smoothly. Further, generally the spreadsheet for a particular computer system is used by a particular individual. Therefore, different persons working on same project may prepare the same report in their personal computer. It leads to wastage of time and efforts.

Conclusion – from the above analysis it is concluded that the ability of spreadsheet to input various data, calculation and formula is its main advantage. Therefore, despite of some disadvantages, if the spreadsheet is used by a person who has proper knowledge of spreadsheet it can be a n useful tool to the business.

Assumption inventory flow

Requirement 1

Requirement 2

Gross profit

Bank reconciliation

Normal view

Formula view

With changed data –

 Normal view –

Journal entry for accounts receivable

Particulars	Debit	Credit
Sales on credit –
Accounts receivable	$ 20,000.00
To sales		$ 20,000.00
Cash collection-
Cash a/c	$ 12,000.00
Allowance for doubtful debts	$   8,000.00
Accounts receivable		$ 20,000.00
Income statement	$   8,000.00
To bad debts		$   8,000.00

Estimating the bad debts

Mainly 2 methods are there for calculating and estimating the bad debts. 1^st method is known as the direct write-off method that uses the actual amount of debt that is uncollectable. Using this amount and dividing it by account receivable can measure the actual bad debt percentage. Another method for estimating bad debt is the allowance method (Otley & Emmanuel, 2013). Under the allowance method the amount that is required to report in the financial report of the company is computed. The amount is calculated from the amount of services provided or goods sold on credit. As per the allowance method the company shall pass another entry for adjusting the amount at the end of the period and the expected loss.

Example for direct write-off method

Example of allowance method

Computers and the accounts receivable

Online shopping site like eBay is gaining popularity over the past few years. The reason is mainly it allows the users to shop from any corner of the world using their computer and internet. The online shopping is convenient for most of the shoppers as it saves the time, energy and transportation cost by avoiding the movement for one place to another for shopping. However, the shoppers can do the shopping online through using their computer. Therefore, computer also plays the same important role in online shopping. It makes the shoppers life easier and simple as the required products can be chosen from the list of products and therefore, the online shopping sites like eBay is a well known shopping site today (Drury, 2013).

IF function

Dishonour of notes receivable

Scenario –

Journal entries –

Work integrated assessment

From the establishment in the year 1914, Wesfarmers has grown to become the largest listed companies in Australia. It employs more than 530,000 employees approximately and has diverse business that includes supermarkets, hotels, fertilizers, liquor, energy, industrial products, safety products, and coal (Sustainability.wesfarmers.com.au 2018).

Discussion –

Issues related to sustainability – the company considered the below mentioned issues as material in their sustainability report for the year ended 2017 –

• Providing positive contribution to communities in which they carrying on their operations.
• Maintaining safe workplace for their employees
• Commitment of the company maintaining a respectful relation with the employees and suppliers (Sustainability.wesfarmers.com.au 2018).

The net income of the company increased from $ 407 million to $ 2873 million over the years from 2016 to 2017. Further, the income from operation of the company increased from $ 3365 million to $ 4226 million and the company was able to reduce the liabilities from $ 17,834 to $ 16,174 over the years from 2016 to 2017. Further, the EPS of the company has significantly increased from 36.2 cents to 254.7 cents. Moreover the company is regular in payment of dividend and the dividend paid by the company for the year ended 2017 was $ 1.20 per share (Sustainability.wesfarmers.com.au 2018).

Working capital ratio –

Particulars	Unit
Current Assets	$9,667
Current Liabilities	$10,417
Working Capital Ratio [Current Assets/Current Liabilities]	0.93

Working capital ratio near about 1 signifying that the working capital ratio can be increased to more than 1 if the company pays off some of its short term obligations

After tax profit of the company for last 5 years are as follows –

Year	Amount ($ m)
2017	2,873
2016	2,353
2015	2,440
2014	2,689
2013	2,261

Bar graph –

Pie chart –

Conclusion and recommendation – looking at the above analysis for Wesfarmers Ltd it has been found that the company’s performance for the year 2017 as well as previous year is stable and strong. Further, the company is regular in paying dividend and creating return on shareholder’s investment. Therefore, Wesfarmers stock shall be included in the investment portfolio.

References

Bebbington, J., Unerman, J., & O’Dwyer, B. (2014). Sustainability accounting and accountability. Routledge.

Bragg, S. M. (2013). Inventory management. AccountingTools.

Brief, R. P., & Peasnell, K. V. (2013). Clean surplus: A link between accounting and finance. Routledge.

Carraher, S., & Van Auken, H. (2013). The use of financial statements for decision making by small firms. Journal of Small Business & Entrepreneurship, 26(3), 323-336.

Cho?odowicz, E., & Or?owski, P. (2015). A periodic inventory control system with adaptive reference stock level for long supply delay. Measurement Automation Monitoring, 61.

Drury, C. M. (2013). Management and cost accounting. Springer

Golyagina, A., & Valuckas, D. (2016). Representation of knowledge on some management accounting techniques in textbooks. Accounting Education, 25(5), 479-501.

Myrelid, A., & Olhager, J. (2015). Applying modern accounting techniques in complex manufacturing. Industrial Management & Data Systems, 115(3), 402-418.

Otley, D., & Emmanuel, K. M. C. (2013). Readings in accounting for management control. Springer.

Sustainability.wesfarmers.com.au. (2018). [online] Available at: https://sustainability.wesfarmers.com.au/media/2222/2017-wesfarmers-sustainability-full-report.pdf [Accessed 29 Apr. 2018].