APPLICATIONS OF SPREADSHEET IN ACCOUNTING

A computerised accounting system is an accounting information system that processes the financial
transactions and events as per Generally Accepted Accounting Principles (GAAP) to produce reports as
per user requirements. Every accounting system, manual or computerised, has two aspects.
FEATURES OF CAS
1. SIMPLE AND INTEGRATED
CAS is designed to automate and integrate all the business operations, such as sales, finance,
purchase, inventory and manufacturing. CAS is integrated to provide accurate, up-to-date business
information rapidly. The CAS may be integrated with enhanced MIS (Management Information
System), Multi-lingual and Data Organisation capabilities to simplify all the business processes of
the organisation easily and cost-effectively.
2. TRANSPARENCY AND CONTROL
CAS provides sufficient time to plan, increases data accessibility and enhances user satisfaction.
With computerised accounting, the organisation will have greater transparency for day-to-day
business operations and access to the vital information.
3. ACCURACY AND SPEED
CAS provides user-definable templates (data entry screens or forms) for fast, accurate data entry
of the transactions. It also helps in generalising desired documents and reports.
4. SCALABILITY
CAS enables in changing the volume of data processing in tune with the change in the size of the
business. The software can be used for any size of the business and type of the organisation.
5. RELIABILITY
CAS makes sure that the generalised critical financial information is accurate, controlled and
secured.
ADVANTAGES OF CAS
Following are the advantages of Computerised Accounting System (CAS):
1. Timely generation of reports and information in desired format.
2. Efficient record keeping.
3. Ensures effective control over the system.
4. Economy in the processing of accounting data.
5. Confidentiality of data is maintained.
LIMITATIONS OF CAS
Following are the limitation of CAS software:
1. Faster obsolescence of technology necessitates investment in shorter period of time.
2. Data may be lost or corrupted due to power interruptions.
3. Data are prone to hacking.
4. Un-programmed and un-specified reports cannot be generated.
SPREADSHEET AND ITS BUSINESS APPLICATIONS

A spreadsheet is a large sheet having data and information arranged in rows and columns. As you know,
Excel is one of the most widely used spreadsheet applications. It is a part of Microsoft Office suite. M.S.
Excel Microsoft Excel shortly known as MS Excel is one of the most popular windows spreadsheet
program because of its power and ease of use to organize date about transactions for a person to examine.
It provides a grid of cell for data entry and a range of mathematics, financial and statistical functions to
manipulate the data (such as sum, average and maximum values of date in columns or rows). This allows
the creation of business spreadsheets for sales, staff or product information with the associated monthly
costs, sales, wages and profit etc. Excel may also be used for data collection and simple statistical
analysis in research.

EXCEL WORKSHEET
Excel allows you to create worksheets much like paper ledgers that can perform automatic calculations.
Each Excel file is a workbook that can hold many worksheets. The worksheet is a grid of columns
(designated by letters) and rows (designated by numbers). The letters and numbers of the columns and
rows (called labels) are displayed in gray buttons across the top and left side of the worksheet. The
intersection of a column and a row is called a cell. Each cell on the spreadsheet has a cell address that is
the column letter and the row number. Cells can contain text, numbers, or mathematical formulas.

What can a spreadsheet do?

In spreadsheet manipulates numerical data and text. Using a spreadsheet, one can create budgets, analyze
data, produce financial plans, and perform various other simple and complex numerical applications. By
having formulas that automatically recalculate, either built by you, the user, or the built-in math
functions, you can play with the numbers to see how the result is affected. Using this ― “what-if?”
analysis, you can see what affect changing a data value or calculation can have on your monitoring
program. Spreadsheets can also be used for graphing data points, reporting data analyses, and organizing
and storing data.
STARTING EXCEL
1. Click the Start button on the Windows taskbar.
a. The Start menu opens
2. Point to Programs
a. The Programs menu opens
3. Click Microsoft Excel
a. Excel opens a new workbook
Note: an icon for MS Excel may be located either on the desktop or on the Office toolbar.
The Excel Screen

MS Excel automatically edits the result if any changes are made in any of the cells.
FORMULA & FUNCTIONS
Functions are typed in a cell in following format=Function name (argument) Argument may be in any
one of the following format.
1. Numeric value
2. Cell address
3. Range (Starting cell address : Ending cell address)
4. Range name
5. Cell addresses (Address1, address2, .....)
Financial Functions
1. DDB() - calculate depreciation using double declining method
2. FV() - calculate future value of an investment
3. PMT() - calculate periodic payment value
4. PV() - calculate present value
5. SLN() - calculate depreciation using straight line method
6. SYD() - calculate depreciation using sum of years
Organizing Chart and graphs
Numeric data can be summarised into charts such as line chart, bar chart, pie chart etc. Bar chart and
Line chart can be used to show the relationship of multiple data. Pie chart shows the relation of a
single data. In excel, data can be easily converted into chart. Chart can be drawn in the same
worksheet or in a new work sheet. Following steps are followed to draw a chart
1. Select the data series range
2. Choose Chart option in Insert menu and select On New sheet or On this sheet.
3. If On this sheet is selected, specify the area where the chart is to be drawn
4. Verify the selected series range
5. Choose the type of chart such as pie chart, bar chart etc
6. Give the title of chart, X axis title, Y axis title.
7. If the legend is to be displayed, choose Show legend and choose
Finish button After drawing chart, we can change the type of chart by clicking the type tool in chart
toolbar.

PREPARING PAYROLL STATEMENT IN EXCEL SHEETS

Every employee is paid salary on a pre-determined date within the framework of employee contract
and related personnel policy in force from time to time in an organisation.
The computation of salary payment is based on the number of days an employee has worked, rate per
grade of pay, rate of applied allowances and deductions to be made therefrom.
The preparation of salary bill should provide for the following :
• Maintaining payroll related data such as Employee No., Name, Attendance, Basic Pay, applicable
Dearness and other Allowances, deductions to be made.
• Periodic payroll computations: the payroll computation includes the calculation of various earning
and deduction heads, which are to be derived from basic values (such as basic salary, number of days
under leave without pay (LWP) and unauthorised absence, etc) as per the formulae.
Preparation of salary statement and employees salary slips
• Generation of advice to bank: It contains the net salary to be transferred to individual bank account
of employees and other salary related statutory payments such as provident fund, tax, etc.

PAYROLL COMPONENTS

Every employee is under contractual relationship of service with an organisation, and is paid salary
accordingly. The following elements are important for salary computation and its payment:

CURRENT PAYROLL PERIOD (MONTH AND YEAR)

EARNINGS

 Basic Pay (BP) : It is the pay in the pay scale plus Grade Pay, but does not include Special Pay.
 Grade Pay (GP) : It is the pay to be added to the Basic Pay according to the Designation of the
employee and applicable pay band or scale of pay.
 Dearness Pay (DP) : It is that portion of Dearness Allowance, which has been declared and deemed
to have been merged with the Basic Pay.
 Dearness Allowance (DA) : It is a compensation for erosion in the purchasing power of wage
earner due to price rise. It is granted by the Government periodically as a percentage of (Basic Pay
+ Dearness Pay, if applicable).
 House Rent Allowance (HRA) : It is an amount paid to facilitate employee in acquiring on lease of
residential accommodation.
 Transport Allowance (TRA) : It is an amount to facilitate commuting to the place of work.
 Any Other Earning : It may include any other allowance not included above but declared from
time to time, such as Education Allowance, Medical Allowance, Washing Allowance, etc.

DEDUCTIONS

 Professional Tax (Applicable in some states) (PT) : It is a statutory deduction according to the
legislature of the State Government.
 Provident Fund (PF) : It is a statutory deduction, as part of social security. It is decided by the
Government under the Provident Fund Act and is computed as a percentage of (Basic Pay +
Dearness Pay, if applicable).
 Tax Deduction at Source (TDS) : It is a statutory deduction, which is deducted monthly towards
Income Tax liability of an employee. It is essentially an apportionment of yearly Income Tax
liability over 12 months.
 Recovery of Loan Instalment (LOAN) : Any amount signified by the employee for deduction on
account of any loan taken up by him/her.
 Any Other Deduction : It may include any other deduction not included above such as Recovery
of “Advance against Salary”, deductions on account of “Food Grain Advance”, “Festival
Advance”, etc.
Practical -1

Prepare payroll of the SJ Enterprises employees in the spreadsheet

Employee Employees Name Basic Pay PF Loan

No.
105 Sindhu Roy 39500 11500
108 Ashly Mathew 41500 11800
341 Mohammed Zaeem 42500 10500
364 Unni Varma 54000 0
461 Ranjith Rajesh 47500 0
568 Bindhu Sarath 48000 15000
685 Amar Khan 41500 12000
1061 Arunkumar 42000 12500
1181 Rahim Rahman 62000 0
1421 James Varghese 35500 10000
1661 Motilal Sharma 33500 0
2141 Sachin Rao 38000 5500
Additional information:
1. DA-36% of basic pay
2. HRA-Rs1750 for employees Basic pay Greater than ₹ 52000,for others ₹ 1500
3. TA-400 per employee
4. PF subscription-10% for gross pay
5. TDS-20% for Gross Pay greater than ₹ 60, 000, otherwise 10%

PROCEDURE:
Functions required to use: Sum(),IF
Syntax=IF(test, then value, otherwise value)
Step-1 Open a new worksheet in spreadsheet.

Step-2 Select the range A1:J1 and select “Merge and Center” and type the heading “Payroll Statement
of Employees”

Step-3 Type the label as

A2- Employee No.
B2-Employees Name
C2-BP(Basic pay)
D2-DA
E2-HRA
F2-TA
G2-Gross Pay
H2-PF
I2-PF Loan
J2-TDS
K2-Total Deduction
 L2-Net Salary
Step-4
Enter the Employees No.A3-A12
Enter the name of employees in the range B3:B12
Step-5 Enter the basic pay of employees in the range C3:C12
Step-6 Calculate DA in the cell D3
D6=C6*36%( i.e 36% of Basic Pay).Select cell D6 again and Drag upto D17
Step-7 Calculate HRA in the cell E3
E6=IF(C6>52000,1750,1500).Select cell E6 again and drag
Step-8 Type ₹ 400 as TA in the rangeF:F17
Step-9 Compute Gross Pay in the cell G6
G6=SUM(C6:F6).Select cell G6 again and drag.
Step-10 Compute PF in the cell H6
H6=G6*10%(i.e.10% of Gross Pay).Select cell H6 again and Drag.
Step-11 Enter PF loan of employees as given in the Practical in respective cells.
Step-12 Calculate TDS in cell J6
J6=IF(G6>60000,G6*20%,G6*10%).Select cell J6again and drag.
Step-13 Calculate Total Deduction in the cell K6
K6=SUM(H6:J6).Select J6 again and drag
Step-14 Compute Net Salary in cell L6
L6=[Link] cell L6 again and Drag
 VLOOKUP
The VLOOKUP function, which stands for vertical lookup, helps us to find specific information in large
data tables such as an inventory list of parts or a large employee contact list. The VLOOKUP function
searches and matches first the required value from the column of a range of cells, and then returns a
value from any cell on the same row of the range.
The syntax is VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Practical-2
From the following Table, find out the BASIC PAY of Mr. Ajith using VLOOKUP Function
using EMPCODE 1846
Employees Name of the Employee Basic Pay
Code
1512 Vinu Sharma 27000
3475 Akhil Ashish 25000
1846 Ajith Sarathkumar 30000
5435 Sonu Mathew 45000
PROCEDURE:
Step 1 Open Spreadsheet
Step 2 Enter all the details given in the Practical in respective cells
Employees Name of the Employee Basic Pay
Code
1512 Vinu Sharma 27000
3475 Akhil Ashish 25000
1846 Ajith Sarathkumar 30000
5435 Sonu Mathew 45000

Step 3 To find out the BASIC PAY of Mr. Ajith using VLOOKUP Function using EMPCODE
1846, enter the following formula in D7
=VLOOKUP(1846,A2:C5,3,0)

OUTPUT
 HLOOKUP
The HLOOKUP function (short name of Horizontal Lookup), searches for a value in the first row of a
table array and returns the corresponding value in the same column from another row of the same table
array.
The syntax for HLOOKUP is as follows:
HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
Practical-3
From the following details, Find out the actual profit for QUARTER II using HLOOKUP
Function
Quarter I Quarter II Quarter Quarter IV
III
Total Sales 200000 300000 450000 500000
Total Cost 150000 180000 430000 370000
Profit 50000 120000 20000 130000

PROCEDURE:
Step 1 Open Spreadsheet
Step 2 Enter all the details given in the Practical in respective cells
Quarter I Quarter II Quarter Quarter IV
III
Total Sales 200000 300000 450000 500000
Total Cost 150000 180000 430000 370000
Profit 50000 120000 20000 130000

Step 3
To find out the profit of the QUARTER II in the cell E7 by the formula
=HLOOKUP(C1,B1:E4,4,0)
Output:
 GRAPHICAL REPRESENTATION
A. COLUMN CHART:
1. Click chart group from insert tab.
2. Select column chart and click next.
3. Make all data click finish.

B. LINE CHART:
1. Click chart group from insert tab.
2. Select line chart and click next.
3. Make all data and click finish

Total Sales Total Cost Profit

1000000
900000
800000
700000
600000
500000
400000
300000
200000
100000
0
1 2 3 4
C AREA CHART:
1. Click chart group from insert tab.
2. Select area chart and click next.
3. Make all data and click finish.

600000

500000

400000

300000

200000

100000

0
Quarter I Quarter II Quarter III Quarter IV

Total Sales Total Cost Profit

D. PIECHART
1. Click chart group from insert tab.
2. Select pie chart and click next.
3. Make all data and click finish.

Total Sales

14%
Quarter I
34%
Quarter II
21%
Quarter III
Quarter IV
31%
E. COLUMN AND LINE CHART
1. Click chart group from insert tab.
2. Select Compo, chart type and click next.
3. Make all data and click finish.

ASSET ACCOUNTING
Assets are resources of the organisation, which can be classified into fixed and current assets.
Fixed assets are long-term assets and provide productive capability to the firm. The examples of fixed
assets are land, building, plant & machinery, etc. It includes both tangible and intangible assets. Tangible
assets are physical in nature, which have form, shape and size. Intangible assets are resources capable
of adding value but do not have a physical dimension such as patents, copyrights, trade mark, etc.
DEPRECIATION ACCOUNT
The monetary value of an asset decreases over time due to use, wear and tear or obsolescence. This
decrease is measured as depreciation.
Depreciation, is a decrease in an asset's value, may be caused by a number of other factors as well such
as unfavorable market conditions, etc. Machinery, equipment, currency are some examples of assets that
are likely to depreciate over a specific period of time. Opposite of depreciation is appreciation which is
increase in the value of an asset over a period of time.
Accounting estimates the decrease in value using the information regarding the useful life of the asset.
This is useful for estimation of property value for taxation purposes like property tax etc. For such assets
like real estate, market and economic conditions are likely to be crucial such as in cases of economic
downturn.
The depreciation on fixed assets is provided to recognise the cost of the asset consumed during an
accounting period since the life of such assets extends beyond single accounting year. Usually,
depreciation is not provided on free hold land.
Total Amount of Depreciation = Acquisition Cost – Salvage Value (Over Life of the Asset)
Year-to-date depreciation is the accumulated depreciation from the date to which the asset is put to use
till the current accounting year.
Depreciation is calculated according to the policy of the organisation. There are basically two methods,
namely the Straight Line Method (SLM) and the Written Down Value Method (WDV).
Asset accounting requires maintenance of asset register, computation of depreciation and preparation of
schedule of fixed assets for reporting in the balance sheet as part of the annual accounts. In order to
prepare this report the depreciation calculation sheet is also to be prepared.
Practical- 4
Calculate Depreciation under Straight Line Method using Spreadsheet based on the details given below.
Assets Cost of Installation Transportation Pre Salvage Life in
Purchase Charge Charge Operating Value years
Expenses
Machinery 20000 2000 4600 1200 2000 10
Furniture 40000 3500 1500 500 3000 8

PROCEDURE:
The SLN function performs the following calculation.
Syntax = SLN(Cost,Salvage,Life)
Step-1 Open a new worksheet in spreadsheet
Step-2 Type the Labels as
Cell Label
A1 Asset
B1 Cost of Purchase
C1 Installation Charges
D1 Transportation Charges
E1 Pre-Operating Expenses
F1 Salvage value
I1 Life in years
J1 Total Cost
K1 Annual Depreciation

Step-3 Enter the details given in the Practical in respective cells

Step-4 Calculate Total cost in cell H2
H2=SUM(B2:E2) or B2+C2+D2+E2 and copy(Drag) the formula to Cell H3
Step-5 Calculate annual depreciation in cell I2 using SLN function
I2=SLN(Cost,Salvage,Life)
OR
I2=SLN(H2,F2,G2)
OUTPUT:
 50000

45000

40000

35000

30000

25000

20000

15000

10000

5000

0
Total Cost Annual Depreciation

Machinery Furniture

ANNUAL DEPRECIATION
Machinery Furniture

33%

67%

Practical- 5
A machinery was purchased on 1st April 2013 for ₹ 200000. Its estimated life is 10 years with salvage
value of ₹ 20000. Accounting year is 1st April to 31st March every year. Using built-in function
(Spreadsheet) calculate depreciation under the Diminishing Balance Value method for 5th year.
PROCEDURE:
Step 1: Open spreadsheet
Step 2: Enter the labels and values as given below

Step 3: Calculate Depreciation for 5th year in cell B5

Syntax=DB(cost,salvage,life,period,month)

B5=DB(B1,B2,B3,B4)

OUTPUT: