Accounting Spreadsheet Design And Functionality

Q1 Naming cells in spreadsheets

One of the main reasons for replacing cell references with names is to describe what that particular cell wants to display. Naming a particular cell allows the users to identify the cell with what calculations it is associated with and the technique is used for the basic convenience of the user. One instance of replacing cell references with names is briefly illustrated as follows:

Particulars	Amount (in $)
EBIT	100,000 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper
Interest	10000
Tax	30000
EAT/PAT	60000

Figure 1: (Chart showing Calculation of EAT/PAT)

Source: (Author Made)

In most cases any negative figures which arises in any calculations are shown in brackets or is highlighted. The main reason for this is to ensure that the user of the excel sheet is aware of the fact that the figure is negative whenever the user see the figures in brackets. The main purpose of putting brackets or highlighting the figure is basic identification and differentiation of the negative figures from the positive ones.

Figure: (Excel Sheet showing negative figure)

Source: (Ahmed, 2015)

The accountant main responsibility is effective calculation and presentation of financial data. In an excel sheet, accountant normally inputs data in another area and for reporting purposes another sheet or area is used. This is mainly done in order to keep the calculations and the reporting part from getting mixed up and moreover for the sake of simplicity of the whole reporting framework. If any of the calculations and reporting area are mixed up than mistakes or miscalculations are bound to happen. A spreadsheet generated instance has been depicted as follows:

Figure 3: (Separate areas of data entry and report in spreadsheet)

Source: (Bogale, 2013)

IF function is one of the most popular functions which is used in excel. IF function provide the users with an opportunity to make comparison among values in an excel worksheet (Liengme, 2015). IF function a technique which helps to check whether a condition is accomplished or not. The function returns a single value if it is true and another value if it is false. An instance of spreadsheet generated IF function is depicted as follows:

Figure 4: (Excel Sheet showing IF function)

Source: (Okeniyi & Okeniyi, 2012)

Perpetual Inventory System	Periodic Inventory System
This system traces every single movement of inventory as and when they arise. The system analyses the book records for keeping track of inventories (Berlemann & Wesselhöft, 2014).	As per this system of inventory the recoding method of inventory involves updating the inventory records at periodic intervals. The system analyses by using physical verification of inventories (Qin et al. 2014).
In this system the inventory and cost of good sold are continuously updated	Under the periodic inventory system, there is no need for continuous updating however both inventory and cost of good sold are updated at the end of the year.
In the perpetual inventory system there is no need for closing entries to be recorded.	In the periodic inventory system as the system updates inventories and cost of good sold therefore the system requires closing entries to be recorded.

Perpetual Inventory System
Date	Particulars	Debit	Credit
Amount (in $)	Amount (in $)
01-01-2017	Purchase Account……Dr	10,000
	To Bank Account		10,000

05-01-2017	Bank Account……….Dr	15,000
	To Sales Account		15,000

Periodic Inventory System
Date	Particulars	Debit	Credit
Amount (in $)	Amount (in $)
01-01-2017	Inventory Account…….Dr	4,000
	To Bank Account		4,000

05-01-2017	Bank Account……Dr	3,900
	To Sales Account		3,900

07-02-2017	Cost of Goods Sold Account……Dr	3,000
	To Inventory Account		3,000

Any individual who is starting off with accounting background will be definitely using excel worksheets for the various calculations. Spreadsheet helps in keeping tracks of various data such as accounts of small as well as large businesses. The owners of small business are new to accounting and may not be able to maintain proper records, spreadsheet helps offering easy solution to such a problem. Spreadsheet helps in commonly using the analysis of money which is spent and income that has been received. Spreadsheet enables an individual to split the amount of cash that business has received and received (Zhu, 2014). Another advantage of using the spreadsheet is that business can link the worksheets together and it is very helpful in generating cash flow of a company. However a disadvantage which arises while using spreadsheets is that such spreadsheets can be manipulated easily and thus there is an increased chance of occurrence of frauds.

Average Method
Date	Purchase	Cost of Sales	Balance Inventory
Unit	Cost per Unit (in $)	Total Amount (in $)	Unit	Cost per Unit (in $)	Total Amount (in $)	Unit	Cost per Unit (in $)	Total Amount (in $)
01-01-2017							80	57	4,560
03-01-2017	10	65	650				80	57	4,560
							10	65	650
							90	58	5,210
05-01-2017	30	70	2,100				90	58	5,210
							30	70	2,100
							120	61	7,310
08-01-2017	70	72	5,040				120	61	7,310
							70	72	5,040
							190	65	12,350
31-01-2017				75	65	4,875	115	65	7,475

FIFO Method
Date	Purchase	Cost of Goods Sold	Balance Inventory
Unit	Cost per Unit (in $)	Total Amount (in $)	Unit	Cost per Unit (in $)	Total Amount (in $)	Unit	Cost per Unit (in $)	Total Amount (in $)
01-01-2017							80	57	4,560
03-01-2017	10	65	650				80	57	4,560
							10	65	650
05-01-2017	30	70	2,100				80	57	4,560
							10	65	650
							30	70	2,100
08-01-2017	70	72	5,040				80	57	4,560
							10	65	650
							30	70	2,100
							70	72	5,040
31-10-2016				75	57	4,275	5	57	285
							10	65	650
							30	70	2,100
							70	72	5,040
31-10-2016				75		4,275	115		8,075

LIFO Method

Date	Purchase	Cost of Goods Sold	Balance Inventory
Unit	Cost per Unit (in $)	Total Amount (in $)	Unit	Cost per Unit (in $)	Total Amount (in $)	Unit	Cost per Unit (in $)	Total Amount (in $)
01-01-2017							80	57	4,560
03-01-2017	10	65	650				80	57	4,560
							10	65	650
05-01-2017	30	70	2,100				80	57	4,560
							10	65	650
							30	70	2,100
08-01-2017	70	72	5,040				80	57	4,560
							10	65	650
							30	70	2,100
							70	72	5,040
31-10-2016				120	70	8,400	80	57	4,560
				70	72	5,040	10	65	650
							(90)	70	(6,300)

Bank Reconciliation
31-07-2017
Account Description	General Cash	Prepared By
GL Account Number		Reviewed By
Bank Account Number
Balance Per Bank Statement			24,009.00

Add:	EFT Rent Received	700
	EFT Insurnace payment	400
	Deposits in Transit	4567.00
	NFS Cheque from customers	2300.00
	Bank Service charges	78.00
	Note Receivables	2345.00
Subtract:
	Checks Issued Not Cleared	1789.00
	Checks Issued Not Cleared	650.00
	Checks Issued Not Cleared	320.00
			(2,759.00)
Other:
			A
	Book error cheque	78.00
			78.00
Balance Per Bank Ledger			31,018.00

Bank Reconciliation
31-07-2017

Account Description	General Cash	Prepared By
GL Account Number		Reviewed By
Bank Account Number

Balance Per Bank Statement			25,000.00
Add:	EFT Rent Received	750
	EFT Insurnace payment	500
	Deposits in Transit	6587.00
	NFS Cheque from customers	2500.00
	Bank Service charges	90.00
	Note Receivables	3545.00
			13,222.00
Subtract:
	Checks Issued Not Cleared	2100.00
	Checks Issued Not Cleared	700.00
	Checks Issued Not Cleared	300.00
			(3,100.00)
Other:
			B
	Book error cheque	95.00
			95.00
Balance Per Bank Ledger			35,217.00

Bank Reconciliation
42947

Account Description	General Cash	Prepared By
GL Account Number		Reviewed By
Bank Account Number

Balance Per Bank Statement			24009
Add:	EFT Rent Received	700
	EFT Insurnace payment	400
	Deposits in Transit	4567
	NFS Cheque from customers	2300
	Bank Service charges	78
	Note Receivables	2345
			=SUM(OFFSET(J19,-1,-1,MATCH(“Add:”,E$1:E$104,0)-ROW()+1))
Subtract:
	Checks Issued Not Cleared	1789
	Checks Issued Not Cleared	650
	Checks Issued Not Cleared	320
			=-SUM(OFFSET(J24,-1,-1,MATCH(“Subtract:”,E$1:E$104,0)-ROW()+1))
Other:
			A
	Book error cheque	78
			=SUM(OFFSET(J28,-1,-1,MATCH(“Other:”,E$1:E$104,0)-ROW()+1))
Balance Per Bank Ledger			=SUM(OFFSET(J30,-1,,MATCH(“Balance Per Bank Statement”,E$1:E$104,0)-ROW()))

Bank Reconciliation
31-7-2017

Account Description	General Cash	Prepared By
GL Account Number		Reviewed By
Bank Account Number
Balance Per Bank Statement			25000
Add:	EFT Rent Received	750
	EFT Insurnace payment	500
	Deposits in Transit	6587
	NFS Cheque from customers	2500
	Bank Service charges	90
	Note Receivables	3545
			=SUM(OFFSET(R19,-1,-1,MATCH(“Add:”,M$1:M$104,0)-ROW()+1))
Subtract:
	Checks Issued Not Cleared	2100
	Checks Issued Not Cleared	700
	Checks Issued Not Cleared	300
			=-SUM(OFFSET(R24,-1,-1,MATCH(“Subtract:”,M$1:M$104,0)-ROW()+1))
Other:
			A
	Book error cheque	95
			=SUM(OFFSET(R28,-1,-1,MATCH(“Other:”,M$1:M$104,0)-ROW()+1))

Balance Per Bank Ledger			=SUM(OFFSET(R30,-1,,MATCH(“Balance Per Bank Statement”,M$1:M$104,0)-ROW()))

Journal Entries

Date	Particulars	Amount ($)	Amount ($)
01-01-2018	Accounts Receivables A/c …………….Dr	50000
	To Sales A/c		50000
	(Being goods sold in credit)
05-01-2018	Cash A/c……………..Dr	50000
	To Accounts Receivables		50000
	(Being the amount owing from credit sales received)
10-01-2018	Allowance for Doubtful Accounts A/c…………Dr	10000
	To Accounts Receivable A/c		10000
	(Being written off accounts receivables)
31-01-2018	Accounts Receivable A/c……………………Dr	10000
	To Allowance for Doubtful Accounts A/c		10000
	(Being reinstatement of amount written off)
31-01-2018	Cash A/c…………………………….Dr	10000
	To Accounts Receivables A/c		10000
	(Being the received of full amount owing)

Q2 Negative numbers

There are two methods of estimating bad debts which are given below:

Direct write off Method: In this method the amount is directly written off from the sales revenue when the amount of credit sales is deemed to be uncollectable.

Date	Particulars	L/F	Amount ($)	Amount ($)
31/1/2018	Bad Debt Expense Account…….Dr		4000
	To Accounts Receivable Account			4000
	(Being writing-off of the debtor’s account)

Allowance Method: In this method the amount which cannot be collected is recorded as an estimated bad debt is recorded in the same year of sales. . The bad debts that are written off is debited to the allowance account rather than debiting bad debt expenditure expense account.

In recent times the importance of online retailing in business has become crucial for business. most of the business nowadays have online websites in order to access digital markets. For example E-bay is a proper example of online retail shopping. The online shopping websites has a variety of good on offer and this is widely used by business as well as consumers for selling and purchasing respectively. The use of computers for accessing E-bay websites has become essential because it helps in keeping records products that have greater growth for potential.

Date	Details	Amount (in $)
01-01-2017	Credit Sale to Alex	12,000
05-01-2017	Receipt of Partial Payment	4,000
	Promise received outstanding to 5/1/2017
08-01-2017	Dishonour of past promise due to no payment
23-01-2017	Receipt of Partial Payment	4,000

Debit	Accounts Receivable Account	Credit
Date	Particulars	Amount	Date	Particulars	Amount
01-01-2017	Sales	12,000	01-01-2017	Cash	4,000
05-01-2017	Notes Receivable	8,000	05-01-2017	Notes Receivables	8,000
			23-01-2017	Cash	4,000
	Total	20,000		Total	16,000
Debit	Notes Receivable Account	Credit
Date	Particulars	Amount	Date	Particulars	Amount
05-01-2017	Accounts Receivable	4,000	05-01-2017	Accounts Receivable	–
	Total	9,500		Total	9,500

Balance scorecard can be defined as the metric of assessing the performance that is used in strategic administration procedure for locating and improving the different forms of integral functions along with the results obtained from the process. The management of Qantas needs to introduce an effective balanced scorecard so that the investors of the company are benefitted by such practices. Balanced scorecard introduction in the business will greatly improve the financial performance of the company and help the company to achieve the long term goals of the company.

Figure 5: (Figure showing Remuneration of the director of Qantas)

Source: (Investor.qantas.com, 2018)

The figure shows that the directors of the company earn a basic pay and also a bonus. The basic pay is more or less unchanged but the bonus has been reduced by the company. The figure shows that the company has an incentive plan.

Current Assets:	2016	2017
Assets Classified as held for Sales	12	17
Inventory	351	336
Trade Receivables	784	795
Cash and Cash Equivalents	1,775	1,980
Other financial Assets	100	229
Other Assets	97	101

Figure 6: (Chart showing Non currents Assets of Qantas)

Source: (Author Made)

Figure 7: ((Chart showing Pie Chart of Qantas)

Source: (Author Made)

As per the analysis performed above, the share of the majority of the current assets has increased and therefore it is probable that the investors should invest in the company. The company has significant amount of liquid cash in the current year which shows the liquidity of the business, The organization’s financial statement shows better liquidity position of the business (Investor.qantas.com, 2018). The result states that the company has maintain the advantage over the local and the global competitors that has been underpinned by the completion of the three year of transformation program. The international operations of the firm have experienced higher degree of capacity growth and also witnessed an improvement in the EBIT of $327 million.

Reference

Berlemann, M., & Wesselhöft, J. E. (2014). Estimating aggregate capital stocks using the perpetual inventory method. Review of economics, 65(1), 1-34.

Kaplan, R. S. (2012). The balanced scorecard: comments on balanced scorecard commentaries. Journal of Accounting & Organizational Change, 8(4), 539-545.

Liengme, B. (2015). A Guide to Microsoft Excel 2013 for Scientists and Engineers. Academic Press.

Okeniyi, J. O., & Okeniyi, E. T. (2012). Implementation of Kolmogorov–Smirnov P-value computation in Visual Basic®: implication for Microsoft Excel® library function. Journal of Statistical Computation and Simulation, 82(12), 1727-1741.

Qantas | 2017 Annual Report. (2018). Investor.qantas.com. Retrieved 15 January 2018, from https://investor.qantas.com/annual-report-2017/

Qin, L., Miao, L., Ruan, Q., & Zhang, Y. (2014). A local search method for periodic inventory routing problem. Expert Systems with Applications, 41(2), 765-778.

Zhu, J. (2014). Quantitative models for performance evaluation and benchmarking: data envelopment analysis with spreadsheets (Vol. 213). Springer.

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Accounting Spreadsheet Design And Functionality ”

Get high-quality paper

NEW! AI matching with writer