Accounting Spreadsheets, IF Functions, Perpetual And Periodic Inventory Systems, And Business Reports

Question 1

In the below stated computations the reference of the spread sheet is replaced with the names in each of the specific cells in the spreadsheet. The primary objective of these replacement is the to facilitate the ease of convenience and association with the calculations of the numbers in the excel spreadsheet. The below stated tabular representations provides the illustrations of replacement of the cell with reference to the names;

Particulars	Amount (in $)
Sales	125,000 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper
Cost of goods sold	95,000
Gross Income	30,000

The numbers that are negative is generally exemplified in the spreadsheet with the help of brackets in red and the same is particularly executed in the below stated illustrations

Figure 1: Figure representing illustrations of negative numbers

(Source: Williams, 2014)

For the accountants in the spreadsheet a separated bookkeeping or entry of data is required and the reason for this is that it helps in depicting single view of every single row. Consequently, for report a distinct area for report is necessary to help the accountants in preparing the detailed reports along with the required data source (Williams, 2014). The range of data entry is put into use to record the raw data, whereas the portion of report is put into use for converting the record into a meaningful information.

An important assertion in this regard is that a distinct area for record keeping is required to be created so that incorrect bookkeeping of the raw data is recorded in the spreadsheet. Hence, the procedure of creating a parting enables stress-free recording of the raw data together and helps in eliminating the needless information. The below stated spreadsheet illustrations is provided below;

Figure 2: Figure representing separate data entry areas and reports in the spreadsheets

(Source: Williams, 2014)

The “IF” functions helps in providing anticipated intelligence to the program with the help of which the program would be able to take the decision in respect of the developed criteria and determining course of the program. As stated by Deegan (2016), the “IF” function can be defined as the function that assist in examining whether or not the conditions is attained. Most importantly the “IF” functions helps in returning the singular value and satisfies the criteria then it will be true and alternatively if the criteria is not met then the value will be false. An example of spreadsheet is obtained with the help of “IF” functions which is stated below;

Figure 3: Figure illustrating the IF functions

Question 2

(Source: Warren & Jones, 2018)

Perpetual Inventory System	Periodic Inventory System
Under the perpetual inventory system there is constant updates in the general ledger or the inventory journal because inventory related transactions take place (Warren & Jones, 2018).	Under the periodic inventory system, cost of goods sold accounting entry is not recorded during the accounting period till the physical count takes place which is later derived to compute the cost of goods sold.
It is not possible to physically maintain the records for the perpetual inventory system because there could be numerous transactions at the unit level during the accounting period.	Under the periodic inventory system, it enables the users to manually record the accounts for each small inventories.
In the perpetual inventory system there is a constant updates to the cost of goods sold when each unit of the sales is made.	In the periodic inventory system the cost of goods sold is computed in lump sum at the end of the accounting period (Henderson et al., 2015).

Under any form of accounting role an individual can obtain the benefit from using the spreadsheet in day to day task. Spreadsheet helps in easy tracking of payment for small business owners. The owners of small business are new to accounting and may find it difficult in tracking the payments, spreadsheet helps offering easy solution of tracking payments. Spreadsheet helps in commonly using the analysis of money which is spent and income that has been received (Pratt, 2016). Spreadsheet enables an individual to split the amount of cash that business has received and received during the time period and source. Additional advantage of using the spreadsheet is that business can link the worksheets together and it is useful in determining cash flow. However, one of the disadvantage of spreadsheet in accounting is that it is vulnerable to fraud which might result in million dollar losses and also it lacks control.  

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 $)
=’Q2-11-NV’!C7							80	57	=K6*L6
=’Q2-11-NV’!C8	10	65	=E7*F7				80	=L6	=K7*L7
							10	65	=K8*L8
							=SUM(K7:K8)	=M9/K9	=SUM(M7:M8)
=’Q2-11-NV’!C11	30	70	=E10*F10				=K9	=L9	=K10*L10
							=E10	=F10	=K11*L11
							=SUM(K10:K11)	=M12/K12	=SUM(M10:M11)
=’Q2-11-NV’!C14	70	72	=E13*F13				=K12	=L12	=M12
							=E13	=F13	=K14*L14
							=SUM(K13:K14)	=M15/K15	=SUM(M13:M14)
=’Q2-11-NV’!C17				=K15-K16	=L16	=H16*I16	115	=L15	=K16*L16

Normal View

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 Insurance payment	400
	Deposits in Transit	4567.00
	NFS Cheque from customers	2300.00
	Bank Service charges	78.00
	Note Receivables	2345.00
			9,690.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 Insurance 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
31-7-2017
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()))

The two different method of determining the bad debts are the direct write off method and the allowance method.

Direct write off method:

In the direct write off method when it is understood that the particular account would become as uncollectable then the loss will be charged to the “Bad Debt Expenditure Account” (Marshall, 2016). For example, an organization has considered that a debtor’s balance of $2500 would become uncollectible on 31 January 2018 and the journal entry is provided below;

Under the allowance method, the expenditure arising out of the bad debt is stated in the comprehensive income statement as the operating expense. The bad debts that are written off is debited to the allowance account rather than debiting bad debt expenditure expense account.

In the modern era of technology computers have become an essential element in online retailing. For example, E-bay has used the potential of computers as the vehicle for promoting the goods and bagging the sales. More specifically it is argued that E-bay with the correct mixture of technology and organizational culture would be able to make appropriate offering of product by contemplating the retail market (Hoskin et al., 2014). Use of computers for E-bay have become essential because it helps in keeping records products that have greater growth for potential.

Example Scenario

T-Accounts

According to Marshall (2016), balance scorecard can be defined as the metric of assessing the performance that is used a strategic administration procedure for locating and improving the different forms of integral functions along with the results obtained. Hence, it is obligatory in offering quantities result because the managers usually interpret the financial information for making efficient decision making. With the effective of balance scorecard, Qantas has managed to make sure that the net benefit for its shareholders. Therefore, the use of balance score card helps in attaining the long term objectives of the Qantas and assist in improving the financial performance of the firm during the long run.

Question 3

An extract from the Qantas 2017 report have been taken from the remuneration report of the company;

Figure 4: Figure representing Remuneration Report of Qantas

(Source: Investor.qantas.com, 2018)

The total amount of remuneration for the year ended 2016 and 2017 for Qantas stood $21,649 and $20,460 respectively. With the appointment of the Gareth Evans Qantas as the CEO of the company has helped in increasing the marketing strategies of the company. Additionally, on analysing the financial report of Qantas it is understood that Qantas has been delivering stellar revenue growth and the company is becoming into the profitable unit. Additionally, non-current assets for the Qantas has been selected and the figures are presented in the following form with the help of charts and data entry;

Particulars	2016	2017
Non-Current Assets:
Receivables	134	123
Other financial assets	46	43
Investments accounted for under the equity method	197	214
Property, plant and equipment	11,670	12,253
Intangible assets	909	1025
Deferred tax assets	39	–
Other	252	444
Total Non-Current Assets	13,247	14,102

Figure 5: Figure representing the Non-Current Assets of Qantas

(Source: As Created by Author)

Figure 6: Chart representing the Non-Current Assets of Qantas

(Source: As Created by Author)

As per the analysis performed above, the share of the non-current assets has increased and it is advised that the investors should make an investment in the shares of Qantas. Additionally, the organization has reported a better liquidity position as understood from the figures reported by the firm (Investor.qantas.com, 2018). Qantas has also reported the underlying profit before tax of $1,401 million during the year 2016-17 and the company rates the profit as the second highest performance over the last 97 years’ history operations. 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. In addition to this, the international operations of the firm have experienced higher degree of capacity growth and also witnessed an improvement in the EBIT of $327 million.

It is noticed that Qantas sells air miles to the supermarkets and other retail partners. Each unit of the Qantas sells generates profit for the company since its cost less to the company to redeem these points and selling them. Qantas has also witnessed increasing growth in the tourist numbers and rise in revenue from the loyalty business. The airline industry has of late been very attractive and the analyst have predicted their $4.30 price target on the shares of Qantas. This kind of price target reveals that there is a potential of increase in 34% of the current share price. With the price of oil remaining low it its believed that investors would benefit from boom in the international tourism. Overall, an assertion can be bought forward by stating that Qantas is a good investment prospect at the present price for the investors. 

Reference List:

Deegan, C. (2016). Financial accounting. McGraw-Hill Education Australia.

Henderson, S., Peirson, G., Herbohn, K., & Howieson, B. (2015). Issues in financial accounting. Pearson Higher Education AU.

Hoskin, R. E., Fizzell, M. R., & Cherry, D. C. (2014). Financial Accounting: a user perspective. Wiley Global Education.

Marshall, D. (2016). Accounting: What the numbers mean. McGraw-Hill Higher Education.

Pratt, J. (2016). Financial accounting in an economic context. John Wiley & Sons.

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

Warren, C. S., & Jones, J. (2018). Corporate financial accounting. Cengage Learning.

Williams, J. (2014). Financial accounting. McGraw-Hill Higher Education.