Dear Teachers,

​

These slides have been prepared based on the SCERT syllabus to support you in teaching Plus One and Plus Two Accountancy and Computerised Accounting.

​

Please review and verify the content before using it in your classrooms. If you find any errors or have feedback, please let me know.

​

Mujeeb Rahiman C

HSST Commerce

GHSS Pattikkad

Malappuram Dt.

​

✉️ mujeebchemmala@gmail.com

9995983075 �

Use of Spreadsheet in Business Applications

Chapter- 3

LibreOffice Calc

Use of Spreadsheet in Business

Business firms use spreadsheet for varied purposes ranging from accounting to presentation of data in the form of graphs and charts for decision making.

1. Payroll Accounting
2. Asset Accounting
3. Loan Repayment Schedule

​

Both large and small scale business firms use spreadsheet for the preparation of

1. Payroll Accounting

All employees of an organization receive salary or wages. The process of documenting records for employee compensation (salary) is known as payroll accounting.

The term “payroll” refers to the list of employees who receive salary/wages from a company. The term also refers to the records that detail how much paid to each employee.

Format of Payroll

Earnings

Deductions

Employee details

Net Pay

Payroll Components

Earnings

1. Basic Pay (BP) : It is the fixed amount paid to the employees by their employers based on their work.

2. Dearness Allowance (DA) : It is a compensation to make up the purchasing power of employees due to price rise. It is granted as a percentage of BP.

3. House Rent Allowance (HRA) : It is an amount paid to enjoy the benefit of a residential accommodation.

4. Transport Allowance (TA) : It is an amount paid to travel between his home and place of work.

5. Other earnings : Grade Pay, Dearness Pay, Education allowance, Medical allowance, hill track allowance etc.

Payroll Components

Total Earnings

​

Gross Pay = Basic Pay + Dearness Allowance + House Rent Allowance

+ Travelling Allowance + Other Earnings

​

Payroll Components

Deductions

1. Professional Tax (PT) : It is the tax levied by the Local Self Government Institutions under which the place of employment falls.

2. Provident Fund (PF) : It is a statutory deduction. It is decided by the Government. It is computed as a percentage of BP or BP + DA

3. Tax Deduction at Source (TDS) : It is a statutory deduction made on a monthly basis towards the Income Tax liability of an employee

4. Recovery of Loan : Amount of deduction on account of any loan taken.

5. Other deductions : Life insurance premium, Group Insurance premium, State Life Insurance Premium etc.

Payroll Components

Total Deductions

​

Total Deductions = Professional Tax + Provident Fund + Recovery of Loan

+ Tax Deduction at Source + Other Deductions

​

​

Payroll Components

Gross Pay = Basic Pay + Dearness Allowance + House Rent Allowance

+ Travelling Allowance + Other Earnings

Total Deductions = Professional Tax + Provident Fund + Recovery of Loan

+ Tax Deduction at Source + Other Deductions

​

Net Pay = Gross Pay – Total Deductions

Example 1 :

Toms Ltd. intends to prepare payroll statement for August 2020

1. Dearness Allowance is 20% of Basic Pay

2. HRA – Manager 3% of BP, Technician 2% of BP

3. Provident fund is 10% of BP

4. State Life Insurance is ₹ 500 for all employees

Procedure : -

Step 1 >> Open LibreOffice Calc

Applications -> Office -> LibreOffice Calc

Step 2 >> Enter headings in Cell A1 and A2

A1 = Toms Ltd.

A2 = Payroll for the month of August 2020

Step 3 >> Enter column headings Emp. No, Name, Designation, Basic Pay, DA, HRA, Gross Pay, PF, SLI, Total deduction and Net Pay from cells A3 to K3

Step 4 >> Enter given data in the respective cells

Step 5 >> Enter formulae in the respective cells

Step 6 >> Select cells E4 to K6 and press Ctrl + D

or select cells and drag down up to last row.

Example 2 :

PQR Ltd. wants to prepare payroll statement for the month of November 2020. Salary details are given below.

1. Dearness Allowance (DA) 80% of BP

2. Professional Tax (PT) 2% of Gross Salary

3. Provident Fund 10 % of BP + DA

4. TDS 20% if Gross Pay greater than 50,000, otherwise 10%

​

Procedure : -

Step 1 >> Open LibreOffice Calc

Applications -> Office -> LibreOffice Calc

Step 2 >> Enter headings in Cell A1 and A2

A1 = PQR Ltd.

A2 = Payroll for the month of November 2020

Step 3 >> Enter column headings Emp. No, Name, Basic Pay, DA, Grade Pay, TA, Gross Pay, Prof. Tax, PF, TDS, Loan Recovery, Total deduction and Net Pay from cells A3 to M3

Step 4 >> Enter given data in the respective cells.

Step 5 >> Enter formulae in the respective cells

Step 6 >> Select cell D4 and drag down up to last row.

Select cells G4 to J6 and drag down up to last row.

Select cells L4 to M4 and drag down up to last row.

MUJEEB RAHIMAN C

HSST COMMERCE

GHSS PATTIKKAD

MALAPPURAM DT

2. Asset Accounting

Accounting of assets covers the complete life cycle of an asset. It includes the maintenance of records right from the acquisition of assets till its disposal.

The inbuilt functions of LibreOffice Calc makes the asset accounting process more easier.

Methods of calculating Depreciation

1. Straight Line Method

OR Fixed Instalment Method

• Written Down Value Method

OR Diminishing Balance Method

OR Reducing Balance Method

OR Declining Balance Method

​

​

1. Straight Line Method

Depreciation is calculated based on the original cost of the asset using the following formula :

Depreciation =

Acquisition Cost – Scrap Value

Estimated Working Life

Acquisition Cost = Purchase Cost + Installation Expenses

+ Other Expenses till the date of installation

Scrap Value – It is the value which is realisable at the end of its useful life

Estimated Working Life – The period for which the asset can be effectively

put to use

SLN Function is used for finding out the amount of annual depreciation under straight line mehtod

Syntax :

= SLN(Cost,Salvage,Life)

Cost = Acquisition Cost

Salvage = Scrap Value

Life = Total life of an asset

Example : -

The cost of Machinery is ₹ 10,000 and installation charge ₹ 1,000. The salvage value after 5 years is ₹ 2,000. Calculate depreciation using SLN function in LibreOffice Calc.

= SLN(11000,2000,5)

= SLN(Cost,Salvage,Life)

Syntax :

2. Written Down Value Method

It is also called Diminishing Balance (DB) Method. This method uses current book value as the base for computing the depreciation. DB function is used for calculating depreciation under this method.

Syntax :

= DB(Cost,Salvage,Life,Period,Month)

Cost = Acquisition Cost

Salvage = Scrap Value

Life = Total life of an asset

Period = Period (year) for which depreciation is calculated

Month = Number of months in the first year (It is required only if

the asset is purchased during a financial year)

Example : -

A machinery is purchased on 1^st August 2014 for ₹ 40,000 and installation charges is ₹ 2,000. The salvage value after 5 years will be ₹ 3,000. Ascertain the amount of depreciation of third year using DB function in Calc assuming that the books are closed on 31^st March every year.

Syntax :

= DB(Cost,Salvage,Life,Period,Month)

= DB(42000,3000,5,3,8)

1. Straight Line Method

OR Fixed Instalment Method

​

​

• Written down value Method

OR Diminishing Balance Method

OR Reducing Balance Method

OR Declining Balance Method

Syntax :

= DB(Cost,Salvage,Life,Period,Month)

= SLN(Cost,Salvage,Life)

Syntax :

MUJEEB RAHIMAN C

HSST COMMERCE

GHSS PATTIKKAD

MALAPPURAM DT

3. Loan Repayment Schedule

The Loan Repayment Schedule is a complete table of periodic loan repayments, showing the amount of principal and interest components in each instalment until the loan is fully paid off. This schedule also shows the outstanding balance of loan amount after the payment of each instalment.

For splitting the instalment into the interest and principal, we may use IPMT and PPMT functions respectively.

Example : -

Vanasree Agencies took an advance of ₹60,000 for 6 months from Indian Bank @ 14% interest for that period. Prepare a Loan Repayment Schedule.

​

Select cell B9 and drag down upto cell B13

Select range C8:F8 and drag down upto C13:F13

​

​

MUJEEB RAHIMAN C

HSST COMMERCE

GHSS PATTIKKAD

MALAPPURAM DT