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.