Practical 1: Count Functions in Spreadsheet
Aim
To count the number of cells containing numbers, any value, and values above a given limit using COUNT, COUNTA and COUNTIF functions.
Data
Enter the following data in cells A1 to H2:
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| 390 | 651 | 856 | 765 | 9899 | 192 | 1032 | STOCK |
| 349 | 298 | 995 | 684 | 567 | CASH | 1500 | TDRS |
Procedure
- Open LibreOffice Calc: Applications → Office → LibreOffice Calc.
- Enter the data in cells A1:H2.
- In cell B4, enter the formula to count numbers:
=COUNT(A1:H2)
- In cell B5, enter the formula to count any value:
=COUNTA(A1:H2)
- In cell B6, enter the formula to count values above 1000:
=COUNTIF(A1:H2,">1000")
Result
Numbers only: 12
Any value: 15
Values above 1000: 3 (9899, 1032, 1500)
Practical 2: Maximum, Minimum and Average Functions
Aim
To find the highest, lowest and average score of students using MAX, MIN and AVERAGE functions.
Data
| Name | ARUN | BIBIN | CINI | DENNY | BINFA | GEO |
|---|---|---|---|---|---|---|
| Score | 150 | 180 | 410 | 480 | 260 | 161 |
Procedure
- Open LibreOffice Calc.
- Enter the names in A1:G1 and scores in A2:G2.
- In B4, find the highest score:
=MAX(B2:G2)
- In B5, find the lowest score:
=MIN(B2:G2)
- In B6, find the average score:
=AVERAGE(B2:G2)
Result
Highest Score: 480
Lowest Score: 150
Average Score: 273.5
Practical 3: IF Function for Tax Calculation
Aim
To compute income tax using the IF function based on the given income slabs.
Data
| Name | Sex | Taxable Income (₹) |
|---|---|---|
| SHIBU | Male | 2,39,000 |
| SULAIMAN | Male | 4,75,000 |
| SASI | Male | 5,25,000 |
| AJITHA | Female | 4,25,000 |
| HUSSAIN | Male | 6,00,000 |
Tax Rules
- Income below ₹2,50,000 → Nil
- Income ₹2,50,000 to ₹5,00,000 → 10%
- Income above ₹5,00,000 → 20%
Procedure
- Open LibreOffice Calc.
- Enter the headings: A1 – Name, B1 – Sex, C1 – Taxable Income, D1 – Tax.
- Enter the data in rows 2 to 6.
- In cell D2, enter the IF formula:
=IF(C2>=500000,C2*20/100,IF(C2>=250000,C2*10/100,"NIL"))
- Copy the formula from D2 to D6.
Result
| Name | Income (₹) | Tax (₹) |
|---|---|---|
| SHIBU | 2,39,000 | NIL |
| SULAIMAN | 4,75,000 | 47,500 |
| SASI | 5,25,000 | 1,05,000 |
| AJITHA | 4,25,000 | 42,500 |
| HUSSAIN | 6,00,000 | 1,20,000 |
Practical 4: Nested IF Function for Grading
Aim
To convert scores into grades using the nested IF function.
Data
| Subject | Score |
|---|---|
| ENGLISH | 85 |
| MALAYALAM | 96 |
| BUSINESS STUDIES | 75 |
| ACCOUNTANCY | 67 |
| ECONOMICS | 29 |
| COMPUTER APPLICATION | 45 |
Grading Scale
| Score | Grade |
|---|---|
| 90 – 100 | A+ |
| 80 – 89 | A |
| 70 – 79 | B+ |
| 60 – 69 | B |
| 50 – 59 | C+ |
| 40 – 49 | C |
| 30 – 39 | D+ |
| 20 – 29 | D |
| Below 20 | E |
Procedure
- Open LibreOffice Calc.
- Enter the headings: A1 – Subject, B1 – Score, C1 – Grade.
- Enter the subject data in rows 2 to 7.
- In cell C2, enter the nested IF formula:
=IF(B2>=90,"A+",IF(B2>=80,"A",IF(B2>=70,"B+",IF(B2>=60,"B",IF(B2>=50,"C+",IF(B2>=40,"C",IF(B2>=30,"D+",IF(B2>=20,"D","E"))))))))
- Copy the formula to C3:C7.
Result
| Subject | Score | Grade |
|---|---|---|
| ENGLISH | 85 | A |
| MALAYALAM | 96 | A+ |
| BUSINESS STUDIES | 75 | B+ |
| ACCOUNTANCY | 67 | B |
| ECONOMICS | 29 | D |
| COMPUTER APPLICATION | 45 | C |
Practical 5: SUM, SUMIF and LOOKUP Functions
Aim
To find the total salary, the total salary of a particular designation, and the name of an employee with a specific salary using SUM, SUMIF and LOOKUP functions.
Data
| Employee Name | Designation | Monthly Salary (₹) |
|---|---|---|
| JOSE | CEO | 80,000 |
| CICY | AO | 50,000 |
| SIJO | FM | 40,000 |
| ALEX | FM | 15,000 |
| JOMY | MM | 45,000 |
| SIMI | FM | 30,000 |
Procedure
- Open LibreOffice Calc.
- Enter the headings: A1 – Employee Name, B1 – Designation, C1 – Monthly Salary.
- Enter the data in rows 2 to 7.
- Total salary in C8:
=SUM(C2:C7)
- Salary paid to FM in C9:
=SUMIF(B2:B7,"FM",C2:C7)
- Name of employee with salary ₹40,000 in C10:
=LOOKUP(40000,C2:C7,A2:A7)
Note: The LOOKUP function requires the lookup column to be sorted in ascending order. Here the salary column is in descending order, so the result may not be accurate. A safer formula is:
=INDEX(A2:A7,MATCH(40000,C2:C7,0))
Result
Total Monthly Salary: ₹2,60,000
Total Salary to FM: ₹85,000
Employee with ₹40,000: SIJO
Practical 6: PMT Function – Loan Installment
Aim
To calculate the monthly installment (EMI) of a loan using the PMT function.
Data
| Item | Value |
|---|---|
| Loan Amount | ₹3,00,000 |
| Annual Interest Rate | 10% |
| Number of Payments | 48 months |
| Future Value | 0 |
| Type | 0 |
Procedure
- Open LibreOffice Calc.
- Enter the labels and values:
Cell Item Value A1 Loan Amount 300000 A2 Annual Rate 10% A3 Type 0 A4 No of Payments 48 - In cell B6, enter the PMT formula:
=PMT(B2/12,B4,B1,B3)
Result
Monthly Installment: ₹7,608.40
Practical 7: SLN Function – Depreciation
Aim
To calculate annual depreciation under the Straight Line Method using the SLN function.
Data
| Asset | Purchase Cost | Installation | Transport | Pre-operating | Salvage Value | Life (Years) |
|---|---|---|---|---|---|---|
| Machinery | 20,000 | 2,000 | 4,600 | 1,200 | 2,000 | 10 |
| Furniture | 40,000 | 3,500 | 1,500 | 500 | 3,000 | 8 |
Procedure
- Open LibreOffice Calc.
- Enter the headings in row 1:
A1 – Asset, B1 – Purchase Cost, C1 – Installation, D1 – Transport, E1 – Pre-operating, F1 – Salvage Value, G1 – Life, H1 – Total Cost, I1 – Depreciation. - Enter the data in rows 2 and 3.
- In cell H2, calculate Total Cost:
=SUM(B2:E2)
- Copy the formula to H3.
- In cell I2, calculate Depreciation:
=SLN(H2,F2,G2)
- Copy the formula to I3.
Result
| Asset | Total Cost (₹) | Annual Depreciation (₹) |
|---|---|---|
| Machinery | 27,800 | 2,580.00 |
| Furniture | 45,500 | 5,312.50 |
Practical 8: Payroll Statement
Aim
To prepare a payroll statement using formulas and absolute cell references.
Rules
- DA = 36% of Basic Pay
- HRA = ₹1,750 if Basic Pay > ₹52,000; otherwise ₹1,500
- TA = ₹400 per employee
- PF = 10% of Gross Pay
- TDS = 20% of Gross Pay if Gross Pay > ₹60,000; otherwise 10%
Data
| Name | Basic Pay (₹) | PF Loan (₹) |
|---|---|---|
| SINDHU | 39,500 | 11,100 |
| ASHLY | 41,500 | 11,800 |
| RENJITH | 54,000 | 0 |
Procedure
- Open LibreOffice Calc.
- Enter the constants:
Cell Item Value B2 DA Rate 36% B3 TA 400 B4 PF Rate 10% - Enter the headings in row 6: A – Name, B – Basic Pay, C – DA, D – HRA, E – TA, F – Gross Pay, G – PF, H – PF Loan, I – TDS, J – Total Deduction, K – Net Pay.
- Enter the employee names and Basic Pay from row 7 onwards.
- Enter the formulas in row 7 (using $ for absolute references):
Cell Formula C7 =B7*$B$2D7 =IF(B7>52000,1750,1500)E7 =$B$3F7 =SUM(B7:E7)G7 =F7*$B$4H7 Enter PF Loan directly I7 =IF(F7>60000,F7*20/100,F7*10/100)J7 =SUM(G7:I7)K7 =F7-J7 - Copy the formulas down to the remaining rows.
Result
| Name | Basic | DA | HRA | TA | Gross | PF | PF Loan | TDS | Total Ded. | Net Pay |
|---|---|---|---|---|---|---|---|---|---|---|
| SINDHU | 39,500 | 14,220 | 1,500 | 400 | 55,620 | 5,562 | 11,100 | 5,562 | 22,224 | 33,396 |
| ASHLY | 41,500 | 14,940 | 1,500 | 400 | 58,340 | 5,834 | 11,800 | 5,834 | 23,468 | 34,872 |
| RENJITH | 54,000 | 19,440 | 1,750 | 400 | 75,590 | 7,559 | 0 | 15,118 | 22,677 | 52,913 |
Practical 9: Column Chart and Line Chart
Aim
To draw a Column Chart and convert it into a Line Chart for the given sales data.
Data
| Month | X Ltd (₹) | Y Ltd (₹) |
|---|---|---|
| January | 1,20,000 | 2,00,000 |
| February | 1,50,000 | 1,90,000 |
| March | 1,80,000 | 1,30,000 |
| April | 2,00,000 | 1,50,000 |
| May | 2,50,000 | 1,20,000 |
Procedure
- Open LibreOffice Calc.
- Enter the data with headings in A1:C6.
- Select the range A1:C6.
- Click Insert → Chart.
- Choose Column chart type → Next.
- Set the data range → Next → Next.
- Enter the Chart Elements:
- Title: Sales Volume Report
- X-axis: Months
- Y-axis: Sales Volume
- Click Finish.
- To change to Line Chart: Double-click the chart → Right-click → Chart Type → Line → Select "Lines only" → OK.
Result
The Column Chart shows the monthly sales of X Ltd and Y Ltd side by side.
The Line Chart shows the trend clearly:
- X Ltd – sales are increasing from ₹1.2 L to ₹2.5 L.
- Y Ltd – sales are decreasing from ₹2 L to ₹1.2 L.
Practical 10: Pie Chart
Aim
To draw a Pie Chart for the PTA expenditure of a school.
Data
| Category | Amount (₹) |
|---|---|
| Repairs | 5,000 |
| Lab items | 12,000 |
| Library | 6,000 |
| Electricity | 2,000 |
| Telephone | 3,000 |
| Others | 8,000 |
Procedure
- Open LibreOffice Calc.
- Enter the categories in A1:F1 and the amounts in A2:F2.
- Select the range A1:F2.
- Click Insert → Chart.
- Choose Pie chart → Finish.
- Right-click the chart → Insert Data Labels (to show percentage).
- Give a suitable title and colour the slices.
Result
| Category | Amount (₹) | Percentage |
|---|---|---|
| Lab items | 12,000 | 33.33% |
| Others | 8,000 | 22.22% |
| Library | 6,000 | 16.67% |
| Repairs | 5,000 | 13.89% |
| Telephone | 3,000 | 8.33% |
| Electricity | 2,000 | 5.56% |
| Total | 36,000 | 100% |
Largest expense: Lab items (33.33%)
Practical 11: Cash Book using GNU Khata
Aim
To prepare a Cash Book in GNU Khata and ascertain the closing cash balance.
Transactions
| Date | Particulars | Amount (₹) |
|---|---|---|
| Jan 1 | Started business with cash | 50,000 |
| Jan 1 | Purchased office furniture | 4,500 |
| Jan 1 | Cash purchases | 25,000 |
| Jan 1 | Credit sales to Anand | 43,000 |
| Jan 2 | Salary to staff | 12,000 |
| Jan 2 | Received from Anand | 17,500 |
Procedure
- Open GNU Khata: Applications → Office → GNU Khata.
- Create an Organisation with the name Zion Chemicals:
- Type: Profit Making
- Financial Year: 01-01-2019 to 31-12-2019
- Select "Accounts only"
- Create an Admin with username and password.
- Create the following Ledger Accounts:
- Cash (Current Asset → Cash)
- Capital (Capital → None)
- Furniture (Fixed Asset → Furniture)
- Purchases (Direct Expenses → None)
- Sales (Direct Income → None)
- Anand (Current Asset → Sundry Debtors)
- Salary (Indirect Expenses → None)
- Enter the following vouchers:
- Jan 1: Cash Dr 50,000, Capital Cr 50,000 (Receipt – F4)
- Jan 1: Furniture Dr 4,500, Cash Cr 4,500 (Payment – F5)
- Jan 1: Purchases Dr 25,000, Cash Cr 25,000 (Purchase – F7)
- Jan 1: Anand Dr 43,000, Sales Cr 43,000 (Sales – F6)
- Jan 2: Salary Dr 12,000, Cash Cr 12,000 (Payment – F5)
- Jan 2: Cash Dr 17,500, Anand Cr 17,500 (Receipt – F4)
- Display the Cash Book: Reports → Ledger → Cash → View.
Result
| Particulars | Amount (₹) |
|---|---|
| Opening Balance | 0 |
| Add: Total Receipts (50,000 + 17,500) | 67,500 |
| Less: Total Payments (4,500 + 25,000 + 12,000) | 41,500 |
| Closing Cash Balance | 26,000 |
Note: The credit sale to Anand (₹43,000) is not entered in the Cash Book, because it is a credit transaction and does not affect cash.
Practical 12: Table and Query in LibreOffice Base
Aim
To create an employee table in LibreOffice Base and write queries to display specific records.
Data
| EMP ID | EMP NAME | EMP SEX | EMP BASIC PAY (₹) |
|---|---|---|---|
| 100 | ARUN | M | 30,000 |
| 101 | NISHA | F | 60,000 |
| 102 | ANIL | M | 40,000 |
| 103 | ROOPA | F | 80,000 |
Queries to Create
- Display employees whose Basic Pay is greater than or equal to ₹60,000.
- Display employees whose names begin with the letter "A".
Procedure
- Open LibreOffice Base: Applications → Office → LibreOffice Base.
- Create a new database and save it as Emp_details.
- Create a table in Design View with the fields:
Field Name Field Type EMP_ID Text [VARCHAR] EMP_NAME Text [VARCHAR] EMP_SEX Text [VARCHAR] EMP_BASICPAY Number [NUMERIC] - Right-click the row selector of EMP_ID and select Primary Key.
- Save the table as Emp_details and enter the given records.
- Create the first query:
- Queries → Create Query in Design View
- Add the Emp_details table
- Add all fields
- In the criterion of EMP_BASICPAY, enter
>=60000 - Press F5 to run and save it.
- Create the second query:
- Create another query in Design View
- Add all fields from Emp_details
- In the criterion of EMP_NAME, enter
Like 'A*' - Press F5 to run and save it.
Result
Query 1 – Employees with Basic Pay ≥ ₹60,000:
| EMP ID | EMP NAME | EMP SEX | Basic Pay (₹) |
|---|---|---|---|
| 101 | NISHA | F | 60,000 |
| 103 | ROOPA | F | 80,000 |
Query 2 – Employees whose names start with "A":
| EMP ID | EMP NAME | EMP SEX | Basic Pay (₹) |
|---|---|---|---|
| 100 | ARUN | M | 30,000 |
| 102 | ANIL | M | 40,000 |
End of Practical Record
