Detailed Accounting Systems & Processes Assignment Solution - Finance

This document provides a comprehensive solution to an accounting systems and processes assignment. It covers various aspects of accounting, including calculating gross margin, analyzing operating profit, and understanding data organization within spreadsheets. The assignment delves into the use of IF functions, periodic inventory systems, and the identification of errors in corporate spreadsheets, along with recommendations for improvement. It includes detailed inventory ledgers using average, FIFO, and LIFO methods, along with calculations for gross profit. Furthermore, the assignment addresses bank reconciliation, journal entries, and the estimation of bad debts using both direct write-off and allowance methods. It also explores the evaluation of a firm's financial position, the handling of dishonored notes receivable, and a case study of Wesfarmers Limited, including financial ratio analysis and insights from its annual report. The solution provides both normal and formula views of data, as well as revised calculations, offering a complete and detailed analysis of the accounting concepts covered.

Running head: ACCOUNTING SYSTEMS & PROCESSES
Accounting Systems & Processes
Name of the Student
Course Code

1ACCOUNTING SYSTEMS & PROCESSES
Question 1
Renaming Cell Name

2ACCOUNTING SYSTEMS & PROCESSES
Here, Cell D9 is considered as Gross margin column.
Gross margin= Net sales – Cost of goods sold

3ACCOUNTING SYSTEMS & PROCESSES
Question 2
Negative Value
Here, Operating profit figure is showing negative number.

4ACCOUNTING SYSTEMS & PROCESSES
Question 3
In the above data sheet, the calculation is being divided in two parts. One part represents
the data and another one represents the report. The entire list of data is being stored in the above
sheet in the information part. However, the calculation and estimation is being done by utilizing
various formulas in the report section (Shanock et al., 2014). Hence, the relative aspects are
being determined by only changing the information and it is being reported in the exceed
expectation sheet.
For instance, information section is being referred to the place in the spreadsheet where
the data related to costs and deals is being calculated and stored. However, the net salary can be
computed from the given information just by expanding one section (Shanock et al., 2014). This
is due to the reason that, it have to be executed with the help of the recipe, which will help to
determine the net wage effectively. The following screen shots will show the entire calculations.

5ACCOUNTING SYSTEMS & PROCESSES
Step 1

6ACCOUNTING SYSTEMS & PROCESSES
Step 2

7ACCOUNTING SYSTEMS & PROCESSES
Question 4
IF Function

8ACCOUNTING SYSTEMS & PROCESSES
The YouTube video being discussed earlier in this report related to the work of IF is
showing the capability and competency of the system. The particular video also shows that, a
simulated situation which is being made in the information section is being demonstrated by IF
regarding its relevancy. Thus, the particular site interface is proving that just with the help of the
cursor, the whole capacity can be clarified.
Question 5
Periodic systems
The most effective stock framework is the periodic inventory system. This is due to the
reason that, at the end of every timeframe, this framework updates the stock (Cholodowicz &
Orlowski, 2015). Moreover, in the case of this framework, no extra effort is required in recording
the daily stock and cost of the goods being sold. It also helps in determining the stock on an
occasional basis. In addition, at the end of every book keeping period, this framework also stores
the diary sections.

9ACCOUNTING SYSTEMS & PROCESSES
Example

10ACCOUNTING SYSTEMS & PROCESSES
Question 6
Part A:

11ACCOUNTING SYSTEMS & PROCESSES
Part B:
Normal View

12ACCOUNTING SYSTEMS & PROCESSES
Formula View

13ACCOUNTING SYSTEMS & PROCESSES
Part C:
Normal View

14ACCOUNTING SYSTEMS & PROCESSES
Formula View

15ACCOUNTING SYSTEMS & PROCESSES
Changes in six data in 10 Column Sheet
Normal View

16ACCOUNTING SYSTEMS & PROCESSES
Formula View

17ACCOUNTING SYSTEMS & PROCESSES
Part D:
To,
Report to Senior Manager,
In the execution of the corporate spreadsheet, there are various errors or mistakes being
occurred. This includes production sensible errors and avoiding the naturally occurring aspects.
In addition, some other errors such as avoidance of duplication of every cell for the purpose of
calculation and separation of executed formulas and information cells to evade the confusion of
any type are also occurred. In addition, sparing of the exceed expectation sheet is required due to
the reason that, it will enhance the effectiveness of the execution. Moreover, without the option
of sparing, the calculations will become useless. Thus, to prevent all these confusion and
complexities, it is being recommended that an extra tab should be opened for any extra
examinations. Moreover, the range name should be cancelled cross checking should be in
initiated in order to prevent the wrong and error calculations.
Thus, it is been recommended that, an expertise person should be appointed in order to
prevent the identified errors by cross checking and twofold checking the exceed expectation
sheet. In the due course of using the corporate spreadsheet, various errors such as cell
referencing, calculation mechanism, hardcoded numerical and hidden sheets or sections are
being identified (Dzuranin & Slater, 2014). In addition, there are various other mistakes that
happen in addition to the above errors are the requirement of checking the equation, merging the
cell with the formulas, style of writing, inadequate providence of remarks and legitimate pointing
the given diagrams and figures.
Regards

18ACCOUNTING SYSTEMS & PROCESSES
Question 7
Normal View
Inventory Ledger (Average Method):
Purchase Cost of Goods Sold Balance Inventory
Date Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount
01-Oct 60 $57 $3,420
03-Oct 10.00 $65 $650 60 $57 $3,420
10 $65 $650
70 $58 $4,070
12-Oct 30 $70 $2,100 70 $58 $4,070
30 $70 $2,100
100 $62 $6,170
18-Oct 70 $72 $5,040 100 $62 $6,170
70 $72 $5,040
170 $66 $11,210
31-Oct 55 $66 $3,627 115 $66 $7,583
Inventory Ledger (FIFO Method):
Purchase Cost of Goods Sold Balance Inventory
Date Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount
01-Oct 60 $57 $3,420
03-Oct 10 $65 $650 60 $57 $3,420
10 $65 $650
12-Oct 30 $70 $2,100 60 $57 $3,420
10 $65 $650
30 $70 $2,100
18-Oct 70 $72 $5,040 60 $57 $3,420
10 $65 $650
30 $70 $2,100
70 $72 $5,040
31-Oct 55 $57 $3,135 5 $57 $285
10 $65 $650
30 $70 $2,100
70 $72 $5,040

19ACCOUNTING SYSTEMS & PROCESSES
31-Oct 55 $3,135 115 $8,075
Inventory Ledger (LIFO Method):
Purchase Cost of Goods Sold Balance Inventory
Date Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount
01-Oct 60 $57 $3,420
03-Oct 10 $65 $650 60 $57 $3,420
10 $65 $650
12-Oct 30 $70 $2,100 60 $57 $3,420
10 $65 $650
30 $70 $2,100
18-Oct 70 $72 $5,040 60 $57 $3,420
10 $65 $650
30 $70 $2,100
70 $72 $5,040
31-Oct 55 $72 $3,960 60 $57 $3,420
10 $65 $650
30 $70 $2,100
15 $72 $1,080
31-Oct 55 $3,960 115 $7,250
Calculation workings:
Calculation of Gross Profit
Particulars Average Cost FIFO LIFO
Sales Revenue $25,000 $25,000 $25,000
Less: Cost of Goods Sold $3,627 $3,135 $3,960
GROSS PROFIT $21,373 $21,865 $21,040
Calculation of Gross Profit
Particulars Average Cost FIFO LIFO
Beginning Inventory $3,420 $3,420 $3,420
Net Purchases $7,790 $7,790 $7,790
Cost of Goods Available $11,210 $11,210 $11,210

20ACCOUNTING SYSTEMS & PROCESSES
Ending Inventory $7,583 $8,075 $7,250
Cost of Goods Sold $3,627 $3,135 $3,960

21ACCOUNTING SYSTEMS & PROCESSES
Formula View

22ACCOUNTING SYSTEMS & PROCESSES
Revised Data Calculation
Normal View
Inventory Ledger (Average Method)
Purchase Cost of Goods Sold Balance Inventory
Date Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount
01-Oct 60 $110 $6,600
03-Oct 10.00 $100 $1,000 60 $110 $6,600
10 $68 $680
70 $104 $7,280
12-Oct 30 $85 $2,550 70 $104 $7,280
30 $85 $2,550
100 $98 $9,830
18-Oct 70 $80 $5,600 100 $98 $9,830
70 $80 $5,600
170 $91 $15,430
31-Oct 55 $91 $4,992 115 $91 $10,438
Inventory Ledger (FIFO Method):
Purchase Cost of Goods Sold Balance Inventory
Date Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount
01-Oct 60 $110 $6,600
03-Oct 10 $100 $1,000 60 $110 $6,600
10 $100 $1,000
12-Oct 30 $85 $2,550 60 $110 $6,600
10 $100 $1,000
30 $85 $2,550
18-Oct 70 $80 $5,600 60 $110 $6,600
10 $100 $1,000
30 $85 $2,550
70 $80 $5,600
31-Oct 55 $110 $6,050 5 $110 $550
10 $100 $1,000
30 $85 $2,550
70 $80 $5,600
31-Oct 55 $6,050 115 $9,700

23ACCOUNTING SYSTEMS & PROCESSES
Inventory Ledger (LIFO Method)
Purchase Cost of Goods Sold Balance Inventory
Date Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount Unit
Cost per
Unit
Total
Amount
01-Oct 60 $110 $6,600
03-Oct 10 $100 $1,000 60 $110 $6,600
10 $100 $1,000
12-Oct 30 $85 $2,550 60 $110 $6,600
10 $100 $1,000
30 $85 $2,550
18-Oct 70 $80 $5,600 60 $110 $6,600
10 $100 $1,000
30 $85 $2,550
70 $80 $5,600
31-Oct 55 $80 $4,400 60 $110 $6,600
10 $100 $1,000
30 $85 $2,550
15 $80 $1,200
31-Oct 55 $4,400 115 $11,350
Calculation workings:
Calculation of Gross Profit
Particulars Average Cost FIFO LIFO
Sales Revenue $25,000 $25,000 $25,000
Less: Cost of Goods Sold $4,992 $6,050 $4,400
GROSS PROFIT $20,008 $18,950 $20,600
Calculation of Gross Profit
Particulars Average Cost FIFO LIFO
Beginning Inventory $6,600 $6,600 $6,600
Net Purchases $9,150 $9,150 $9,150
Cost of Goods Available $15,750 $15,750 $15,750
Ending Inventory $10,438 $9,700 $11,350
Cost of Goods Sold $5,312 $6,050 $4,400

24ACCOUNTING SYSTEMS & PROCESSES

25ACCOUNTING SYSTEMS & PROCESSES
Formula View

26ACCOUNTING SYSTEMS & PROCESSES
Question 8
Normal View
Bank Reconciliation Statement
As on 30th April
Dated Particulars Amount
30/4 Bank Balance as per Pass Book $19,670
Add:
Deposit in Transit $1,543
EFT Insurance Payment $300
Book Error Cheque 1419 $340
NSF Cheque from Customer $1,700
Bank Service Charge $40 $3,923
$23,593
Less:
Outstanding Cheques 2462
EFT Rent Receipt 600
Note Receivable $1,500
$4,562
30/04 Cash Account Balance as of 30th April $19,031
Formula View
Date Particulars Amount
30/4 Bank Balance as per Pass Book 19670
Add:
Deposit in Transit 1543
EFT Insurance Payment 300
Book Error Cheque 1419 340
NSF Cheque from Customer 1700
Bank Service Charge 40 =SUM(D8:D12)
=E6+E12
Less:
Outstanding Cheques =1532+700+230
EFT Rent Receipt 600
Note Receivable 1500
=SUM(D15:D18)
30/04 =IF(E20>0,"Cash Account Balance as of 30th April","Bank Overdraft Balance as of 30th April") =E13-E18
Bank Reconcilaition Statement
As on 30th April

27ACCOUNTING SYSTEMS & PROCESSES
Changes in data (Revised calculation)
Normal View
Formula View

28ACCOUNTING SYSTEMS & PROCESSES
Question 9
Journal Entries Accounts Receivable Entries
Particulars Debit Credit
Party A/c $ 1,200.00
Sales A/c $ 1,200.00
Bank A/c $ 4,300.00
Creditor’s A/c $ 4,300.00
Bad debt A/c $ 5,400.00
Debtor’s A/c $ 5,400.00
Allowance for doubtful A/c $ 6,900.00
accounts receivable A/c $ 6,900.00
Bank A/c $ 7,200.00
Creditor’s A/c $ 7,200.00

29ACCOUNTING SYSTEMS & PROCESSES
Question 10
Estimation of Bad debts
1. Direct write-off method
In the Books of…
Journal Entries
Dated Particulars Dr. Cr.
Amount Amount
05-10-2017 Bad Debts Expenses A/c. $12,000
Accounts Receivable A/c. $12,000
30/6/2017 Profit & Loss A/c. $12,000
Bad Debts Expenses A/c. $12,000
07-05-2017 Cash A/c. $12,000
Bad Debt Recoveree A/c. $12,000
2. Allowance method
In the Books of…
Journal Entries
Dr. Cr.
Date Particulars Amount Amount
10/5/2017 Bad Debts Expenses A/c. $12,000
Provision for Doubtful Debts A/c. $12,000
10/5/2017 Provision for Doubtful Debts A/c. $12,000
Accounts Receivable A/c. $12,000
30/6/2017 Profit & Loss A/c. $12,000
Bad Debts Expenses A/c. $12,000

30ACCOUNTING SYSTEMS & PROCESSES
5/7/2017 Accounts Receivable A/c. $12,000
Provision for Doubtful Debts A/c. $12,000
5/7/2017 Cash A/c. $12,000
Accounts Receivable A/c. $12,000
.

31ACCOUNTING SYSTEMS & PROCESSES
Question 11
Evaluation of firm’s financial position
It is important for the speculators to gather the relevant and necessary information and
data regarding the monetary and financial position of the organization before opting for
investment in that particular organization (Osadchy & Akhmetshin, 2015). Various tools are
being available for the speculators to determine the effectiveness of the organizations. Some of
the tools are income statement, balance sheet and cash stream articulation (Du, Stevens &
McEnroe, 2015). In this case, the declaration of the income statement demonstrates the cash
inflows. The accounting report signifies the monetary position or status of the organization and
the salary explanation denotes the costs and the amount payable by the organization (Heinrichs et
al., 2013).

32ACCOUNTING SYSTEMS & PROCESSES
Question 12
Dishonour of a Notes Receivable
Particulars Amount (Dr.) Amount (Cr.)
Party A/c $ 4,500.00
Sales A/c $ 4,500.00
Notes Receivable A/c $ 3,200.00
Accounts receivable A/c $ 3,200.00
Receivable A/c $ 2,300.00
Bank A/c $ 2,300.00
Bank A/c $ 1,200.00
Receivable A/c $ 1,200.00

33ACCOUNTING SYSTEMS & PROCESSES
Question 13
Company Wesfarmers Limited
In this case, the organization being discussed is named Wesfarmers Limited. It is an
Australian organization catering to the retail market and being inducted in the Australian stock
exchange. They operate in the expanded zone such as retail, office enhancement and chemicals.
Data and information is being gathered from the annual report of this organization. Upon
evaluating, it is been seen that this organization uses remote money amounts to $15 million and
signifies it as support save (Wesfarmers.com.au, 2017). In addition, it is also been seen that, this
organization is going for ultimate disaster by adding further $78 million (Wesfarmers.com.au,
2017).
As of 2016, this organization has paid of $2600 million of profit (Wesfarmers.com.au,
2017).
Return on Equity
Net Income $407
Shareholder’s Equity $22949
Return on Equity 0.018
Thus, from the annual report of this organization, it can be concluded that, this
organization is garnering only $0.018 of value that is negligible in the long run. By the utilization
of weighted normal numbers for the conventional offer, Wesfarmers Limited has profit per share
at 36.2% (Wesfarmers.com.au, 2017).

34ACCOUNTING SYSTEMS & PROCESSES
However, in the case of the occurrence of any unwarranted issue in their administrative
management, the misquoted accounts in their budget will never be unveiled. Moreover, they
have not even offered any compulsion to any stakeholders regarding complying with the AASB
nominated structure (Wesfarmers.com.au, 2017). However, in the case of the emergence of
corporate governance in this organization, then they will adhere to the recommendations being
stated in the third version of ASX corporate governance.

35ACCOUNTING SYSTEMS & PROCESSES
Working capital ratio
Net profit after tax
Net profit after tax
Particulars
Year Amount ($m)
2016 $ 2,353.00
2015 $ 2,240.00
2014 $ 2,689.00
2013 $ 2,261.00
2012 $ 2,126.00
Thus, having evaluated the monetary position of Wesfarmers Limited, it can be suggested
to Vikram that, he can invest of $50,000 in this organization. This is due to the reason that the
organization is having stability in the market and their profit is being showing positive trend in

36ACCOUNTING SYSTEMS & PROCESSES
the last few years. Accordingly, it is being projected that this organization will make profit in the
coming years also.

37ACCOUNTING SYSTEMS & PROCESSES
Reference
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.
Deegan, C. (2012). Australian financial accounting. McGraw-Hill Education Australia.
Drury, C. M. (2013). Management and cost accounting. Springer.
Du, N., Stevens, K. & McEnroe, J., (2015). The effects of comprehensive income on investors’
judgments: An investigation of one-statement vs. two-statement presentation formats.
Accounting Research Journal, 28(3), pp.284-299.
Dzuranin, A.C. & Slater, R.D., (2014). Business Risks All Identified? If You're Using a Spreadsheet,
Think Again. Journal of Corporate Accounting & Finance, 25(3), pp.25-30.
Wesfarmers.com.au. (2017). Home. Wesfarmers.com.au. Retrieved 17 September 2017, from
http://wesfarmers.com.au