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 |
|
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.