Accounting Assignment: Spreadsheet Applications and Inventory Analysis

This accounting assignment solution delves into various aspects of spreadsheet applications in accounting. It begins by explaining naming cell references and their utility in simplifying calculations, followed by an explanation of how negative numbers are represented in spreadsheets, particularly using brackets. The document then highlights the advantages of separating data entry and report areas in spreadsheet accounting, emphasizing the ease of calculation and data modification. The IF function is explained with examples. The solution further differentiates between periodic and perpetual inventory systems, providing a detailed comparison with examples. It then discusses the advantages and disadvantages of using spreadsheets for accounting, including automation, accuracy, and the potential for data loss. The document then proceeds to inventory costing methods like Average Cost, FIFO (First-In, First-Out), and LIFO (Last-In, First-Out), providing detailed calculations and examples. Finally, the solution addresses bank reconciliations, illustrating the process of adjusting bank and book balances to arrive at the reconciled balance. The assignment covers a wide range of accounting concepts, providing a comprehensive overview of spreadsheet applications in the field.

RUNNING HEAD: ACCOUNTING
AccOUNTING

ACCOUNTING 1
Table of Contents
1..................................................................................................................................................................2
2..................................................................................................................................................................2
3..................................................................................................................................................................3
4..................................................................................................................................................................6
5..................................................................................................................................................................9
6.................................................................................................................................................................11
7.................................................................................................................................................................12
8.................................................................................................................................................................16
9.................................................................................................................................................................18
10...............................................................................................................................................................19
11...............................................................................................................................................................21
12...............................................................................................................................................................21
13...............................................................................................................................................................23

ACCOUNTING 2
1.
Naming cell reference means naming the cell range in the workbook. This function in
spreadsheet is used to make the calculation easy and simpler. Below example showing
calculation of net profit using function ‘Naming cell reference’.
Example:
Calculation of Net Profit:
Normal View:
Sales
($)
Expenses
($)
Net Profit
($)
125000 25400 99600
Formula View:
Sales ($) Expenses ($) Net Profit ($)
125000 25400 =Sales-Expenses
2.
There are multiple ways of applying negative numbers in workbook such as putting minus sign
before the numbers, apply red color to the numbers or applying parentheses. Most common
practice followed by the enterprises is to put bracket for denoting negative numbers in
accounting. Using of brackets highlights credit entries or expenses for accounting purpose.
Further numbers in brackets also easily identifiable to the readers that the number is in negative
form.
Example:

ACCOUNTING 3
Particulars Amount ($)
Sales $25,000.00
Manufacturing
costs ($12,600.00)
Gross return $12,400.00
Overhead costs ($16,600.00)
Net Profit ($4,200.00)
3.
There are multiple reasons for designing spreadsheet accounting report with the completely
seperate report area and data entry area because accounting in spreadsheet shows clear picture
about the company’s financials to the users of the financial statement. Another reason can be said
that spreadsheet contains formulae’s and function bars which helps in making calculations easy
and time saving. In addition to this, numbers can be deleted or changed from the accounting
report without changing the whole calculations and data very smoothly.
After the preparation of financials whole calculations must be combined with the data entry area.
Hence, this will make the assessment of financials very simple to the stockholders.
Example:
Normal View:
Ahuja Ltd
Trading Account
For the year ended 30 April, 2017
Particulars Amount ($) Particulars Amount ($)
Opening Inventory: Closing Stock:
Raw Material 55000 Raw Material 19000
Work in Progress 15000 Work in Progress 9800
Purchases:

ACCOUNTING 4
Raw Material 16000 COGS 105600
Direct labour 14200
Direct Expenses:
Depreciation 9200
Factory Insurance 6500
Manufacturing overhead:
Manufacturing
Expense 9600
Factory Salary 8900
TOTAL 134400 TOTAL 134400
Ahuja Ltd
Income Statement
For the year ended 30 April, 2017
Particulars Amount ($) Total ($)
Revenues:
Sales 195000
Other Income 0
Total Income (A) 195000
Expenses:
COGS 95600
Salary 26500
General expenses 14000
Advertisement 6500
Light and Power 4500
Rate and Taxes 6600
Total operating expenses (B) 153700
Net Income (A-B) 41300
Formula View:
Ahuja Ltd
Trading Account
For the year ended 30 April, 2017
Particulars Amount ($) =+B4 Amount ($)

ACCOUNTING 5
Opening Inventory: Closing Stock:
Raw Material 55000 Raw Material 19000
Work in Progress 15000 Work in Progress 9800
Purchases:
Raw Material 16000 COGS
=E17-
(E6+E7)
Direct labour 14200
Direct Expenses:
Depreciation 9200
Factory Insurance 6500
Manufacturing overhead:
Manufacturing Expense 9600
Factory Salary 8900
TOTAL
=SUM(C5:C16
) TOTAL =C17
=B1
Income Statement
=B3
=+B4 Amount ($) Total ($)
Revenues:
Sales 195000
Other Income 0
Total Income (A) =SUM(C26:C27)
Expenses:
=D9 95600
Salary 26500
General expenses 14000
Advertisement 6500
Light and Power 4500
Rate and Taxes 6600
Total operating expenses (B) =SUM(C30:C35)
Net Income (A-B) =D28-D36

ACCOUNTING 6
4.
IF Function in workbook is most popular used function in excel. It is used in number of ways for
the purpose of testing a situation and return with a value if the situation is fulfilled.
The syntax is:
SYNTAX =IF(logical_test, [value_if_true], [value_if_false]
Considering example (Q 3) we are calculating net profit/loss using ‘IF’ function:
No Profit or Loss:
Ahuja Ltd
Trading Account
For the year ended 30 April, 2017
Particulars Amount ($) Particulars Amount ($)
Opening Inventory: Closing Stock:
Raw Material 55000 Raw Material 19000
Work in Progress 15000 Work in Progress 9800
Purchases:
Raw Material 16000 COGS 105600
Direct labour 14200
Direct Expenses:
Depreciation 9200
Factory Insurance 6500
Manufacturing overhead:
Manufacturing Expense 9600
Factory Salary 8900
TOTAL 134400 TOTAL 134400
Ahuja Ltd
Income Statement
For the year ended 30 April, 2017
Particulars Amount ($) Total

ACCOUNTING 7
Revenues:
Sales 187100
Other Income 0
Total Income (A) 187100
Expenses:
COGS 105600
Salary 41000
General expenses 17000
Advertisement 7000
Light and Power 12000
Rate and Taxes 4500
Total operating expenses (B) 187100
Net Income (A-B) no profit no loss0
Profit:
Ahuja Ltd
Trading Account
For the year ended 30 April, 2017
Particulars Amount ($) Particulars Amount ($)
Opening Inventory: Closing Stock:
Raw Material 55000 Raw Material 19000
Work in Progress 15000 Work in Progress 9800
Purchases:
Raw Material 16000 COGS 105600
Direct labour 14200
Direct Expenses:
Depreciation 9200
Factory Insurance 6500
Manufacturing overhead:
Manufacturing Expense 9600
Factory Salary 8900
TOTAL 134400 TOTAL 134400
Ahuja Ltd
Income Statement

ACCOUNTING 8
For the year ended 30 April, 2017
Particulars Amount ($) Total ($)
Revenues:
Sales 195000
Other Income 0
Total Income (A) 195000
Expenses:
COGS 95600
Salary 26500
General expenses 14000
Advertisement 6500
Light and Power 4500
Rate and Taxes 6600
Total operating expenses (B) 153700
Net Income (A-B) Profit/41300
Loss:
Ahuja Ltd
Trading Account
For the year ended 30 April, 2017
Particulars Amount ($) Particulars Amount ($)
Opening Inventory: Closing Stock:
Raw Material 55000 Raw Material 19000
Work in Progress 15000 Work in Progress 9800
Purchases:
Raw Material 16000 COGS 105600
Direct labour 14200

ACCOUNTING 9
Direct Expenses:
Depreciation 9200
Factory Insurance 6500
Manufacturing overhead:
Manufacturing Expense 9600
Factory Salary 8900
TOTAL 134400 TOTAL 134400
Ahuja Ltd
Income Statement
For the year ended 30 April, 2017
Particulars Amount ($) Total
Revenues:
Sales 200000
Other Income 0
Total Income (A) 200000
Expenses:
COGS 115000
Salary 65000
General expenses 22000
Advertisement 12000
Light and Power 16000
Rate and Taxes 6500
Total operating expenses (B) 236500
Net Income (A-B) loss/-36500
5.
Perpetual and periodic inventory systems are the two different methods for recording of stocks in
books of accounts. The difference is as follow:
Periodic Inventory System Recording of transactions are not updated

ACCOUNTING 10
every time of sale or purchase. In other words,
it is updated at the end of the reporting period
via closing entry.
Perpetual Inventory System In this system, transactions are updated every
time of sale or purchase.
Example:
Date Particulars Amount ($)
01-01-2018
Opening
Stock
(100 units at
$ 20 each) 2000
16-01-2018 Purchase
(60 units at $
20 each) 1200
22-01-2018 Issue
(110 units at
$ 30 each) 3300
31-01-2018 Closing Stock
(220 units at
$ 20 each) 4400
Periodic inventory system:
Particulars Amount ($) Particulars Amount ($)
Opening
Stock 2000 Sales 3300
Purchase 1200
Closing
Stock 4400
Gross profit 4500
Total 7700 Total 7700
Perpetual inventory system:
Date
Partic
ulars
Purchase Issue Closing Balance
No of
units
Unit
cost
Total
cost
No of
units
Unit
cost
Total
cost
No of
units
Unit
cost
Total
cost
2011
01-01-
2018
Openi
ng
Stock 100 20 2000
16-01-
2018
Purch
ase
60 20 1200 100 20 3200

ACCOUNTING 11
60 20
22-01-
2018 Issue 110 30 3300 50 10 500
6.
Summary
The spreadsheet can be used as a tool of accounting because in this application program the data
is placed in rows and columns. The rows and columns is called as cell range. The numbers are
organized in workbook and it also represents numbers in graphs or in pie chart form. Microsoft
excel is the most common used application in today’s time.
This report includes advantages and disadvantages of spreadsheets. Further the worksheet can
also be used by the accounting expert to allocate the costs to different process of the corporation.
Context
Advantages of spreadsheets:
 Using of spreadsheet is very simple which is the biggest advantage.
 Inserting comments attaching to the cell range is also very helpful because there is no
need to write notes.
 Calculations in spreadsheets are very easy with the help of inserting formulae’s and using
of function keys. This feature will also bring accurate results without any error.
 Linking of worksheets together is also an advantage because many data can be
interlinked with each other.
 Automation of data is another feature which can be used for accounting purposes.
 Separation of costs such as rent or taxes and can identify the amount spent on monthly
basis by making a slab.

ACCOUNTING 12
 Accuracy is an another feature of spreadsheet through which exact report is prepared.
 Many external data such as sales data, purchase data, banking data etc. is imported for
accounting purpose from various sources.
Disadvantages of spreadsheets:
 Many times lakhs of lines in different tabs is stored which increases the storage
requirements.
 There are also chances that the data can be lost or corrupted or trashed and thus the whole
data will be unavailable.
 Unskilled labor can also operate spreadsheet because no special skills are required.
 Duplication or medication of data can be easily made without having control is also a
disadvantage of using a spreadsheet.
Decision
Using of spreadsheet for the purpose of accounting increases productivity with the using of
functions and formulae’s and accurate results will be achieved without error which are valuable
to the firm and also to the stakeholders of the firm.
7.
Statement of units:
Date Purchase Issue
Closing
Balance
No of units No of units No of units
Oct-01 60
Oct-03 10 70
Oct-12 30 100
Oct-18 70 170

ACCOUNTING 13
Oct-31 115
October $ 25000
Closing Inventory at the end of the October were 115.
Average Cost Method
Date
Partic
ulars
Purchase Issue Closing Balance
No of
units
Unit
cost
Total
costs
No of
units
Unit
cost
Total
costs
No of
units
Unit
cost
Total
costs
Oct-
01
Openi
ng
Balan
ce 60 57
$
3,420.00
Oct-
03
Purch
ase 10 65
$
650.00 60 57 $
4,070.00
10 65
Oct-
12
Purch
ase 30 70
$
2,100.
00 60 57 $
6,170.00
10 65
30 70
Oct-
18
Purch
ase 70 72
$
5,040.
00 60 57 $
11,210.0
0
10 65
30 70
70 72
Octo
ber Issue 379 65.94
$
25,000.
00
Oct-
31
Closin
g
Balanc
e 115 65.94
$
7,583.24
Average
unit Cost
$
65.94

ACCOUNTING 14
Cost of Closing Inventory as per Average Cost method were $ 7583.24.
Cost of Goods Sold = Opening Inventory + Purchases during the month – Closing Inventory
COGS = $3420 + $7790 - $7583.24
COGS = $ 3626.76.
First in First Out (FIFO)
Date
Partic
ulars
Purchase Issue Closing Balance
No of
units
Unit
cost
Total
costs
No of
units
Unit
cost
Total
costs
No of
units
Unit
cost
Total
costs
Oct-
01
Openi
ng
Balanc
e 60 57 3420
Oct-
03
Purch
ase 10 65 650 60 57 4070
10 65
Oct-
12
Purch
ase 30 70 2100 60 57 6170
10 65
30 70
Oct-
18
Purch
ase 70 72 5040 60 57
1121010 65
30 70
70 72
Octo
ber Issue 55
454.5
5
$
25000
Oct-
31
Closing
Balanc
e
5 57
$
8,075.00
10 65
30 70
70 72

ACCOUNTING 15
Cost of Closing Inventory as per FIFO method were $ 8075.
Cost of Goods Sold = Opening Inventory + Purchases during the month – Closing Inventory
COGS = $3420 + $7790 - $8075
COGS = $3135.
Last in First Out (LIFO)
Date
Partic
ulars
Purchase Issue Closing Balance
No of
units
Unit
cost
Total
costs
No of
units
Unit
cost
Total
costs
No of
units
Unit
cost
Total
costs
Oct-
01
Openi
ng
Balanc
e 60 57 3420
Oct-
03
Purch
ase 10 65 650 60 57 4070
10 65
Oct-
12
Purch
ase 30 70 2100 60 57 6170
10 65
30 70
Oct-
18
Purch
ase 70 72 5040 60 57
1121010 65
30 70
70 72
Octo
ber Issue 55
454.5
5 25000
60 57
$
7,250.00Oct-
31
Closing
Balanc
e
10 65
30 70
15 72

ACCOUNTING 16
Cost of Closing Inventory as per FIFO method were $ 7250.
Cost of Goods Sold = Opening Inventory + Purchases during the month – Closing Inventory
COGS = $3420 + $7790 - $7250
COGS = $3920.
8.
Normal View:
Mickey
Data
Particulars $
Bank balance on 31 july 21453
Adjustments:
EFT Rent receipt -600
EFT Insurance payment 300
NSF Cheque from
customer -2400
Note receivable -2650
Bank error Cheque 1419 456
Bank service charges 65
Deposit in transit -2345
outstanding cheque
Cheque no. 1420 1678
Cheque no. 1421 760
Cheque no. 1422 340
Cash account balance as
of 31 july 17057
Formula View:
Mickey
Data
Particulars $

ACCOUNTING 17
Bank balance on 31 july 21453
Adjustments:
EFT Rent receipt -600
EFT Insurance payment 300
NSF Cheque from customer -2400
Note receivable -2650
Bank error Cheque 1419 456
Bank service charges 65
Deposit in transit -2345
outstanding cheque
Cheque no. 1420 1678
Cheque no. 1421 760
Cheque no. 1422 340
Cash account balance as of 31 july =SUM(C6:C18)
Changes
Normal View:
Mickey
Data
Particulars $
Bank balance on 31 july 21453
Adjustments:
EFT Rent receipt -300
EFT Insurance payment 1400
NSF Cheque from
customer -2700
Note receivable -2050
Bank error Cheque 1419 -44
Bank service charges 65
Deposit in transit -3545
outstanding cheque
Cheque no. 1420 2278
Cheque no. 1421 460
Cheque no. 1422 40
Cash account balance as
of 31 july 17057

ACCOUNTING 18
Formula View:
=B3
Data
Particulars $
Bank balance on 31 july 21453
Adjustments:
EFT Rent receipt =-600+300
EFT Insurance payment =300+1100
NSF Cheque from customer =-2400-300
Note receivable =-2650+600
Bank error Cheque 1419 =456-500
Bank service charges 65
Deposit in transit =-2345-1200
outstanding cheque
Cheque no. 1420 =1678+600
Cheque no. 1421 =760-300
Cheque no. 1422 =340-300
Cash account balance as of 31 july =SUM(C26:C38)
9.
i. Credit sales entry:
Particulars Dr ($) Cr ($)
Accounts Receivable A/c Dr.
To Credit Sales A/c
90000
90000
ii. The collection of the part of the amount owing entry:
Particulars Dr ($) Cr ($)
Cash A/c Dr.
To Accounts Receivable A/c
78000
78000
iii. The write off of accounts receivable entry:

ACCOUNTING 19
Particulars Dr ($) Cr ($)
Allowance for doubtful amount A/c Dr.
To Accounts Receivable A/c
25000
25000
iv. The reinstatement of an amount written off entry:
Particulars Dr ($) Cr ($)
Accounts Receivable A/c Dr.
To Allowance for doubtful amount A/c
25000
25000
v. The collection in full of the amount owing entry:
Particulars Dr ($) Cr ($)
Cash A/c Dr.
To Accounts Receivable A/c
12000
12000
Accounts receivable Account:
Particulars Amount ($) Particulars Amount ($)
To credit sales 90000 By balance b/d -
To Allowance for
doubtful amount
25000 By cash 78000
To balance c/d - By Allowance for
doubtful amount
25000
By cash 12000
10.
Below are the two approaches for valuing bad debts:
1. Accounts receivable Approach:

ACCOUNTING 20
Bad debt is measured as a % of accounts receivable balance.
Suppose:
Closing balance of Accounts receivable = $ 90,000
It is given that 5% of total Accounts receivable is uncollectible.
Bad Debt amount = $ 90,000 * 5% = $ 4,500
Journal entry:
Particulars Dr ($) Cr ($)
Bad debt Expense Account Dr.
To Allowance for bad debt Account
(Estimate of bad debts)
4,500
4,500
2. Credit sales Approach:
Bad debt is measured as a % of credit sales.
Suppose:
Credit Sales = $ 4,00,000
It is given that 10% of credit sales is uncollectible.
Bad Debt Amount = $ 4,00,000 * 10% = $4,0000
Journal entry:
Particulars Dr ($) Cr ($)
Bad debt Expense Account Dr.
To Allowance for bad debt Account
(Estimate of bad debts)
40,000
40,000

ACCOUNTING 21
11.
The e-business is expanding its reputation day to day in the world. Retailing through online is
recognized and used by every business firms whether large, medium size or small size in today’s
time. The benefit of e-commerce business is that the inventory storage and material procurement
expenditures abridged to the least. The e-business can be expanded only when the management
is well understood about the roots, drive forces behind the retailing through electronic means.
The e-market has restructured and consumers can easily acquire products through online only
with the availability of Personal computers and strong internet connection. Many industries have
started traded through online such as clothing, medicines etc. Online retailing can be business to
business and business to consumer. This is very time saving and sales has also increased with
increase in net profit.
Example can be EBAY. This company is retailing its business through online either from
website (ebay.com) or from application. The buyers are free to use the website. EBAY is the 9th
largest e-commerce public company. Nearly 30 nations use ebay for trading and shopping
purposes.
Thus, online retailing with more and more e-commerce industries will flourish in approaching
centuries.
12.
Narrative:
ADP Ltd. Sells goods to AMQ Ltd. for $ 10,000 (credit sales) payment due in 45 days. Later 45
days of non-payment, ADP ltd accepted the note receivable from the AMQ ltd for $ 10,000. It

ACCOUNTING 22
has been established that, AMQ ltd had postponed the payment of $88,00 and balance amount
$1,200 got dishonor.
Journal:
(1). Recording of credit sales:
Particulars Debit ($) Credit ($)
Notes Receivable A/c Dr.
Credit Sales Revenue A/c
10,000
10,000
(2). Account receivable converted to Notes Receivable:
Particulars Debit ($) Credit ($)
Notes Receivable A/c Dr.
Account Receivable A/c
10,000
10,000
(3). AMQ ltd paid $ 8,800 after the due date:
Particulars Debit ($) Credit ($)
Account Receivable A/c Dr.
Notes Receivable A/c
8,800
8,800
(4). $ 1,200 got dishonor. Journal for the dishonor of the note:
Particulars Debit ($) Credit ($)
Allowance for doubtful amount Dr.
Notes Receivable A/c
1,200
1,200
Note receivable A/c:
Particulars Amount in $ Particulars Amount in $

ACCOUNTING 23
To balance b/d - By Account
receivable
8,800
To Account
receivable
10,000 By Allowance for
doubtful amount
1,200
To credit sales
revenue
10,000 To balance c/d 10,000
Total 20,000 Total 20,000
13.
Refer another DOC File