Computerised Accounting Practical Questions, Procedure and Solved Videos

Libre Office CalcL-FUNCTIONS AND FORMULA 

PRACTICAL – 1

Given below is a table showing the Name, Designation and Monthly Salary paid for different employees in Royal Traders for March 2017 

Employee Name Designation Monthly Salary
Kumar CEO 80000
Anil AO 50000
Jithesh FM 40000
Alex FM 15000
Arshad MM 45000
Angel FM 30000

Find out the following: 
a) The total monthly salary by Naming the concerned range as TOTAL_SALARY.
b) The total monthly salary paid to the Finance Manager (FM) in the firm. 
c) The Name of employee with monthly salary of ₹ 40,000 by using LOOKUP Function

PROCEDURE 
1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter in cell A1 “Employee name”, in B1 “Designation” and in C1 “Monthly Salary”
3. Enter the Employee names in A2 to A7, Designation in B2 to B7 and Monthly salary in C2 to C7 
4. Select the range C2:C7 ,then click “Data ”———> “Define Range” 
5. Enter the name “TOTAL_SALARY” against name filed and then click “OK”.
6. In cell B9 enter the formula =SUM(TOTAL_SALARY) to find the total monthly salary.
7. In cell B10 enter the formula =SUMIF(B2:B7,”FM”,C2:C7) to find the total salary paid to finance manager (FM). 
8. In cell B11 enter the formula =LOOKUP(40000,C2:C7,A2:A7) to find the employee with monthly salary of Rs.40,000. 

OUT PUT 

a. 2,60,000 
b. 85,000 
c. Jithesh 

PRACTICAL – 2

J
390 651 856 765 STOCK 192 CASH 1032
342 9899 658 456 765 398 155T DRS CRS INVESTMENT

Answer the following questions using appropriate functions: 
a. How many cells contain Numbers only 
b. Count the Number of cells contain any value 
c. Count the number of cells containing the value exceeding 1000. 
d. How many empty cells there in the table? 

PROCEDURE 

1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the given details in A1 to J2 same as in the question. 
3. In the cell D4 enter the formula =COUNT(A1:J2) to find the number of cells that contains Numbers only. 
4. In cell D5 enter the formula =COUNTA(A1:J2) to find the number of cells that contains any value. 
5. In cell D6 enter the formula =COUNTIF(A1:J2,”>1000”) 
6. In cell D7 enter the formula =COUNTBLANK(A1:J2) 

OUT PUT 

a. 12 
b. 18 
c. 2 
d. 2 

PRACTICAL-3

A. Following are the scores obtained by some students in a competitive examination.find the HIGHEST ,LOWEST and AVERAGE scores using appropriate functions in spreadsheet. 

H
Name ARUN BIBIN CINI DENNY EBIN FABIN GEO
Scores 150 180 410 480 260 161 515

B-From the data given below fill the address in F2 using CONCATENATE Function. 

E
Name House Name Place Post PIN
JAYA DEEPAM NEWSTREET KOZHIKODE 680534

PROCEDURE 

1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the given details in A1 to H2 same as in the question. 
3. In cell C3 enter the formula =MAX(B2:H2) to find the highest value in scores B2:H2
4. In cell C4 enter the formula =MIN(B2:H2) to find the lowest value in scores.
5. Inn cell C5 enter the formula =AVERAGE(B2:H2) to find the average score. 


1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the given details in cell A1 to E2 as in the question. 
3. In the cell F2 enter the formula 

=CONCATENATE(A2,”space”,B2,”space”,C2,”space”,D2,”space”,E2) 

OUT PUT 

A- Highest Score = 515 
Lowest Score = 150 
Average Score = 308 

B- JAYA DEEPAM NEWSTREET KOZHIKODE 680534

PRACTICAL-4

List of debtors and the amount dues from them are given below. Apply conditional formatting to highlight receivables with the date that have expired on 31/01/2016. Also highlight the receivables more than Rs 35,000 with red colour. 

SI.NO Name Amount Receivables 31/01/2016
JIBY 25000 28/01/2016
JINU 30000 15/01/2016
GRACE 45000 15/01/2016
LUCY 37000 31/03/2016
BABU 32000 18/01/2016

PROCEDURE 

1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter SI.No in cell A1, Name in cell B1, Amount in cell C1 and Due Date in cell D1 3. Enter the data in cell A2 to D6 
4. Select the range D2:D6,click “ Format” ——-> Conditional Formatting———-> Condition
5. In conditional Formatting dialogue box select ‘ Cell value is ——> Greater than—–>Click on the next Text box and enter “ D1” 
6. To Apply styles select New style , from the Dropdown list, and click on “Background” tab and select “Red colour” then click OK button. 
7. Then again click “OK” button. 
8. Select the range C2:C6, click “ Format” ——-> Conditional Formatting———-> Condition
9. In conditional Formatting dialogue box select ‘ Cell value is ——> Greater than—–>Click on the next Text box and enter the amount“35000 ” 
10. To Apply styles select New style , from the Dropdown list, and click on “Background” tab and select “Blue colour” then click OK button. 
11. Then again click “OK” button. 

OUTPUT 

PRACTICAL-5

Following table gives the details of some products. 

SI No Product Code Name of product Name of Supplier Quantity
E-234 PUMP SETS ARON PVT LTD 23
E-546 WATER HEATER ALPHA TRADERS 16
E-678 AIR CONDITIONER AIR COOL LINKS 25
E-789 VACUUM CLEANER READY CLEAN CO. 17

Enter the details in to a text file(Note pad/text editor) and import the same to a spreadsheet

PROCEDURE 
1. Open a text Editor – Application—->Accessories——-> Text Editor 
2. Type the details by lines , separate with comma( , ) SI NO,Product Code,Name of Product,Name of Supplier,Quantity 

1,E-234,PUMP SETS,ARON PVT LTD,23 
2,E-546,WATER HEATER,ALPHA TRADERS,16 
3,E-678,AIR CONDITIONER,AIR COLL LINKS,25 
4,E-789,VACUUM CLEANER,READY CLEAN CO,17 

3. Then save the text editor file(Document) click File Tab —–> Save 
4. Select the location for saving the file (Select Desktop) 
5. Open Libre Office Calc , Application —–> Office——> Calc 
6. CLICK ON “Insert” Tab ———–> Sheet from File (In ubuntu 14.04) 

“Sheet —–> Insert Sheet from file ( in Ubuntu 18.04) 

7. Select the text file,from desktop and click “ok” 
8. Select “Before current sheet” then click “ok” 

OUT PUT 

PRACTICAL-6(PQ-9) 

The XYZ company Ltd.Furnishes you the list of their employees and their taxable income 

NAME SEX TAXABLE INCOME TAX
SHIBU Male 2,39,000
SULAIMAN Male 4,75,000
SASI Male 5,25,000
AJITHA Female 4,25,000
HUSAIN Male 6,00,000

Compute tax based on the following criteria by using appropriate function in spreadsheet 
a. If taxable Income is below 2,50,000 tax is Nil 
b. If Taxable income is 2,50,000 to 5,00,000 rate is 10% 
c. If taxable income is above 5,00,000 tax rate is 20% 

PROCEDURE 

1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the given details from A1 to C6 as in the question 
3. Type “ TAX” in D1 
4. In cell D2 enter the formula =IF(C2>=500000,C2*20/100,IF(C2>=250000,C2*10/100,”Nil”))
5. Use fill handle to copy the formula D2 to D6 

OUT PUT 

Libre Office Calc- PIvot Table 

PRACTICAL – 7

From the following prepare Pivot Table 

SI NO PRODUCTS SALES VOLUME(Rs) COUNTRY
SAMSUNG 25000 KOREA
LG 20000 AMERICA
iPHONE 30000 ENGLAND
MICROMAX 50000 INDIA
VIVO 40000 INDIA
MI 15000 CHINA

PROCEDURE 

1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the details as in the question in Cell A1 : D7 
3. After entering data , select the range A1:D7 
4. Go to Data ——> Pivot Table——–> Create 
5. Select “ Current Selection” and Press Ok,Then the Pivot table Layout will appear.
6. Drag “Products” to Row fields, “Country” to Column fields and” Sales volume” to Data fields.(Right click on the Pivot table and select “ Edit Layout” for changes in Raw and Column fields to desired result) 

OUT PUT 

Libre Office Calc- One variable Data table 

PRACTICAL-8 

Consider the following information 

• Loan amount – 300,000 

• No. of Payments – 48 months 

• Annual Rate of interest – 10% 

• Prepare a one variable table showing the repayment of the above loan 

in different number of payment such as 12 months, 24 months, 36 months, 48 months, 60 months and 72 months. Use PMT Function. 

PROCEDURE 

1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the given details as follows 

B
Loan Amount 300000
No. of Payments (Months) 48
Interest rate 10% ( Monthly rate 10% / 12) 0.83%
Monthly Installment
No.of Payments Monthly Installments
12
24
36
48
10 60
11 72

3. In cell B4 Enter the formula =PMT(B3,B2,B1,0,0) Press Enter. 
4. Select the range A6:B11, Go to Data Menu ——–> Multiple Operations, In the Dialogue box, Select the Cell B4 in the “Formulas” Field and in the “Column Input Cell” Field select the cell B2 and Press Ok. 

OUT PUT 

RACTICAL – 9

Name Basic Pay PF Loan
Sindhu 39500 11100
Ashly 41500 11800
Unni 41500 11300
Ranjith 54000 0
Bindu 48000 11700
Ambily 48000 11850
James 41500 12000
Thara 62000 0
Arun 33500 12000
Rahim 38000 5500

Additional information 

1. DA-36% of Basic Pay |
2. HRA – Rs.1750 for employees Basic Pay greater than Rs.52000, for others Rs.1500
3. TA- rs.400 per Employee 
4. PF subscription – 10% for Gross Pay 
5. TDS -20% for Gross pay greater than Rs.60000, otherwise 10%. 

PROCEDURE 

1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the column headings as follows 

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1
Name Basic pay DA HRA TA Gross pay PF Subscription TDS PF Loan Total Deduction Net Pay

3.Enter the given details , Names in range A2:A11, Basic Pay in range B2:B11 , PF Loan amount in range I2:I11 and TA Rs.400 for all employees ,ie, E2:E11 
4.Enter the formulas :- 

For calculating DA in cell C2 =B2*36% 
For calculating HRA enter in D2 =IF(B2>52000,1750,1500) 
For calculating Gross Pay enter in F2 =SUM(B2:E2) 
For calculating PF Subscription enter in G2 = F2*10% 
For calculating TDS enter in cell H2 =IF(F2>60000,F2*20%,F2*10%) 
For calculating Total Deduction enter in cell J2 =G2+H2+I2 
For calculating Net Pay enter in cell K2 =F2-J2 
Copy the formulas in remaining cells using Fill handle 

OUTPUT 

PRACTICAL-9

Below are the details of various assets in a firm.Calculate depreciation under straight line method using spreadsheet software 

Asset Cost of purchaseInstallation chargesTransportation ChargesPre-operati ng expensesSalvage valueLife in years
Machinery 20000 2000 4600 1200 2000 10
Furniture 40000 3500 1500 500 3000 8

PROCEDURE 
1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the details as follows:-in cell A1-Name of assets,in cell B1 Purchase cost, in cell C1-Installation charges, in cell D1-Transportation charges, in cell E1-Pre operating expenses, in cell F1-Salvage value, in cell G1-Life in years, in cell H1-Total cost, in cell I1-Annual depreciation 
3. Enter the data given in question in the range A2:G2 as given below 

I
Asset Cost of purchaseInstallation chargesTransportation ChargesPre-oper ating expensesSalvage valueLife in yearsTotal costDepreciation
Machinery 20000 2000 4600 1200 2000 10
Furniture 40000 3500 1500 500 3000 8

4. Find the Total cost in the cell H2 by =SUM(B2:E2) and drag down the formula using Fill handle to find the total cost of the next asset. 
5. Find the annual depreciation in the cell I2 by =SLN(H2,F2,G2) and copy down the formula using Fill handle in order to get the annual depreciation of the asset furniture 

OUT PUT 

Calc-PIE CHART 

PRACTICAL-11

Draw a pie chart from the following data on vehicles registered in the motor vehicles department during 2016-2017 in a city. 

Vehicle TypeBus Truck Auto rikshawCar Two wheelerHeavy Vehicle
Number of Vehicles575 5889 12345 9765 23456 65

PROCEDURE 
1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the data as in the question and select the all data. 
3. Go to Insert———> Chart and select chart type “Pie” and click Next button.
3. Data Range already selected ,no change is required then select Data series in Rows and put Tick Marks in “ First Row as label” and “First column as label” Then click Next Button. In Data series section no changes is required. click Next. 
4. Enter the Chart Title “ No of vehicle registered “ Set the Legend Position right and click Finish. 

OUT PUT 

Calc – COLUMN CHART 

PRACTICAL-12

Draw a column chart for the following data and give a title Marks Scored by Students. 

Marks 0-20 21-40 41-60 61-80 81-100 Total
No.of students113 180 350 232 125 1000

PROCEDURE 
1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the data as in the question and select the range A1:F2 
3. Go to Insert———> Chart and select chart type “Column” and click Next button.
4. Data Range already selected ,no change is required then select Data series in Rows and put Tick Marks in “ First Row as label” and “First column as label” Then click Next Button. In Data series section no changes is required. click Next. 
5. Enter the Chart Title “ Marks Scored by Students “ , in X -axis “ Score” , in Y Axis “ No of Students “ and Set the Legend Position right and click Finish.

OUTPUT

Calc – COLUMN CHART /BAR CHART 

The sales of two products of a company are given below, Prepare Bar Chart using spreadsheet 

Year Product A Product B
2011 45000 33000
2012 25000 46000
2013 33000 44000
2014 42000 25000
2015 52000 59000

PROCEDURE 

1. Open Libre Office Calc , Application —–> Office——> Calc 
2. Enter the data as in the question in range A1:C6, and select the range A1:C6
3. Go to Insert———> Chart in “Chart wizard “ select chart type “Bar” and click Next button. 
4. Data Range already selected ,no change is required then select Data series in Rows and put’ Tick’ Marks in “ First Row as label” and “First column as label” Then click Next Button. In Data series section no changes is required. click Next. 
5. Enter the Chart Title “Total Sales “ , in X -axis “ Year” , in Y Axis “ Amount“ and Set the Legend Position right and click Finish. 

OUTPUT 

Practical – ROUND,ROUNDUP AND ROUND DOWN 

Production of major Crops in 2020 

Products Value in Rs.
Rice – 356.753
Wheat – 865.63
Ground nut – 536.2541
Mustard – 14.6532
Green Gram – 2792.25
Coconut – 3695
Tea – 518.652
Coffee – 625.7294 

Round the Amounts Using Appropriate ROUND function
1 – Round the value of “Rice” to the nearest integer.
2 – Round the value of “Wheat” to Nearest 10 
3 – Round the value of “Groundnut” to nearest 100
4 – Round the value of “Mustard” to Two decimal place
5 – Roundup the value of “Green gram ” to nearest 1
6 – Roundup the value of “Coconut” to nearest 10
7 – RoundUP the value of “Tea” to nearest 100 
8 – Round Down the value of “Coffee” to Two decimal

PROCEDURE 

1 – Open Libre Office Calc – Application —> Office—-> CALC
2 – Enter “Products” in Cell A1 and “ Value in Rs.: in Cell B1
3 – Fill the details of products and Values in Cell A2 to B9
4 – Enter “Rounded Amount” in C1 
5 – Enter Formula in Cell C2 =ROUND(B2,0) 
6 – Enter Formula in Cell C3 =ROUND(B3,-1) 
7 – Enter Formula in Cell C4 =ROUND(B4,-2) 
8 – Enter Formula in Cell C5 =ROUND(B5,2) 
9 – Enter Formula in Cell C6 =ROUNDUP(B6,0) 
10 – Enter Formula in Cell C7 =ROUNDUP(B7,-1) 
11 – Enter Formula in Cell C8 =ROUNDUP(B8,-2) 
12 – Enter Formula in Cell C9 =ROUNDDOWN(B9,2) 

OUTPUT

GNUKhata-PRACTICAL-15

The various transactions relating to Zion Chemicals for the month of January 2016 is given below.Ascertain cash balance for the month using an accounting software 

JAN.1 –started business with cash Rs.50000 
JAN.1-Purchased office furniture Rs.4500 
JAN.1-Cash purchases Rs.25000 
JAN.1-Credit sales to Anand Rs.43000 
JAN.2-Salary to staff Rs.12000 
JAN.2-Received from Anand Rs.17500 

PROCEDURE 

1. Open GNUkhata – Application —-> Office ———>GNUkhata 
2. Click on “Create Organisation” tab and 
3. enter “Organisation Name “ “Zion chemicals” 
Case : As Is 
Organisation Type : Profit Making 
Financial year :01-01-2016 to 31-12-2016 
Select “ Accounts only” and click “Proceed” button 


4. Create Admin – Enter User name ,Password,security question for resetting password and answer for security question (Enter zion in all fields) and click on “ Create & Login”.
5. Create Ledger Accounts. click on Master —–> Create Account. 

Enter Group Name,Sub Group Name and Account Name 

Group Name Sub Group Name Account Name
Current Asset Cash Cash Account
Capital None Capital Account
Fixed Asset Furniture Furniture Account
Direct expenses None Purchases Account
Current Assets Sundry Debtors Anands Account
Direct Income None Sales Account
Indirect Expenses None Salary Account

Enter Save after each Account details entered. 
6. Select Voucher – Appropriate Voucher Type (Transaction —— > Voucher Type 

Voucher TypeKey Date Account Name Dr /CrAmount
Receipt F4 01-10-2016 Cash Account Capital AccountDr Cr50000
Payment F5 01-01-2016 Furniture Account CashDr Cr4500
Purchases F7 01-01-2016 Purchases Account cash AccountDr Cr25000
Sales F6 01-01-2016 Anand Account Sales AccountDr Cr43000
Payment F5 02-01-2016 Salary Account Cash AccountDr Cr12000
Receipt F4 02-01-2016 Cash Account Anand AccountDr Cr17500

7. Display cash Balance 
Report—->Ledger ——>Cash Account——-> View 

OUTPUT 
26000 (Balance ) 

PRACTICAL-16 -GNUKhata Final Accounts 

Enter the following transactions by using suitable accounting vouchers and display the Profit and Loss Account and Balance sheet 

01/06/2016 – Commenced business with cash Rs.70,000 
01/06/2016 – Purchased machinery Rs.10,000 
01/06/2016 – Paid rent of building Rs.7500 
01/06/2016 – cash deposited with canara bank Rs.20000 
01/06/2016 – Purchased goods for Rs.8400 
02/06/2016 – Sold goods for cash Rs.3260 
02/06/2016 – Sold goods on credit to Mr.Rajesh Rs.2800 

PROCEDURE 

1. Open GNUkhata – Application —-> Office ———>GNUkhata 
2. Click on “Create Organisation” tab and 
3. enter “Organisation Name “ “ABC Ltd” 

Case : As Is 

Organisation Type : Profit Making 

Financial year :01-01-2016 to 31-12-2016 

Select “ Accounts only” and click “Proceed” button 

4. Create Admin – Enter User name ,Password,security question for resetting password and answer for security question (Enter abc in all fields) and click on “ Create & Login”.
5. Create Ledger Accounts. click on Master —–> Create Account. 

Enter Group Name,Sub Group Name and Account Name 

Group Name Sub Group Name Account Name
Current Asset Cash Cash A/c
Capital None Capital A/c
Fixed Asset Plant & Machinery Machinery A/c
Indirect Expenses None Rent A/c
Current Assets Bank Canara Bank A/c
Direct expenses None Purchases A/c
Direct Income None Sales A/c
Current Assets Sundry Debtors Rajesh A/c

Enter Save after each Account details entered. 

6. Select Voucher – Appropriate Voucher Type (Transaction —— > Voucher Type 

Voucher TypeKey Date Account Name Dr /CrAmount
Receipt F4 01-06-2016 Cash A/c Capital A/cDr Cr70000
Payment F5 01-06-2016 Machinery A/c Cash A/cDr Cr10000
Payment F5 01-06-2016 Rent A/c Cash A/cDr Cr7500
Contra F8 01-06-2016 Canara bank A/c Cash A/cDr Cr20000
Purchases F7 01-06-2016 Purchases A/c cash A/cDr Cr8400
Sales F6 01-06-2016 Cash A/c Sales A/cDr Cr3260
Sales F6 02-06-2016 Rajesh A/c Sales A/cDr Cr2800

7. Display Profit and Loss Account 
Report——-> Profit & & Loss Account—–>Display 
8. Display Balance sheet 
Report——->Balance Sheet ——-> Display 

OUTPUT 

Gross loss : 2340 
Net Loss : 9840 
Balance sheet Total: 60160 

Enter the following transactions in appropriate accounting vouchers and prepare the BRS(Bank reconciliation statement) as on 31-01-2017 for M/s Royal Stores 

01-01-2017 – Commenced business with cash Rs.100,000 
01-01-2017 – open the bank account with PNB Rs 50,000 
02-01-2017 – Purchased goods by cheque no:10051 Rs.12000 
02-01-2017 – Received cheque no:20101 from Alvin traders Rs.15000 
02-01-2017 – Issued a cheque no:10052 to Bibin and Co Rs.4000 
02-01-2017 – withdraw from bank for office use Rs.16000 

On comparison of cash book with pass book the following details were obtained.
Cheque no: 10051 was cashed on 02-01-2017 
Cheque no: 10052 was cashed on 02-02-2017 
Cheque received from Alvin Traders cheque no.20101 was collected on 02-02-2017

PRECEDURE 

1. Open GNUkhata – Application —-> Office ———>GNUkhata 
2. Click on “Create Organisation” tab and 
3. enter “Organisation Name “ “Royal Stores” 

Case : As Is 
Organisation Type : Profit Making 
Financial year :01-01-201 to 31-12-2017 
Select “ Accounts only” and click “Proceed” button 

4. Create Admin – Enter User name ,Password,security question for resetting password and answer for security question (Enter royal in all fields) and click on “ Create & Login”. 5. Create Ledger Accounts. click on Master —–> Create Account. 

Enter Group Name,Sub Group Name and Account Name 

Group Name Sub Group Name Account Name
Current Asset Cash Cash A/c
Capital None Capital A/c
Current Asset Bank PNB Bank A/c
Direct expenses None Purchases A/c
Current Asset Sundry Debtors Alvin Traders A/c
Current Liability Sundry Creditors for Purchase Bibin and Co A/c

Enter Save after each Account details entered. 

6. Select Voucher – Appropriate Voucher Type (Transaction —— > Voucher Type )Enter all transactions in appropriate voucher type.

Voucher TypeKe yDate Account Name Dr /CrAmount
Receipt F4 01-01-2017 Cash A/c Capital A/cDr Cr100000
Contra F8 01-01-2017 PNB Bank A/c Cash A/cDr Cr50,000
Purchase F7 02-01-2017 Purchases A/c PNB Bank A/cDr Cr12000(Enter cheque No:as narration)
Receipt F4 02-01-2017 PNB Bank A/c Alvin Traders A/cDr Cr15000( enter cheque No:as narration)
Payment F5 02-01-2017 Bibin and Co A/c PNB Bank A/cDr Cr4000(Enter cheque No: as narration)
Contra F8 02-01-2017 Cash A/c PNB Bank A/cDr Cr16000

6. For reconciliation, after entering all transactions, click Master ——-> Bank Reconciliation statement 
7. Select Bank account PNB Bank A/c ,give 01-01-2017 as Starting date ,31-01-2017 as To date and put tick on Narration, Then click View. 
8. Enter Clearance Date of each transaction. 

Cheque No : 10051 – Clearance date is 02-01-2017 
Cheque No : 10052 – Clearance date is 02-02-2017 
Cheque No : 20101 – Clearance date is 02-02-2017 

9. Click on “View Statement” 

OUTPUT 

RACTICAL-18

Create the following ledgers in an accounting software and display the balance sheet as on 01/01/2017 

Items Amount
Capital 2,50,000
Loose tools 50,000
Creditors 50,000
Bank loan 75,000
Land and Building 100,000
Plant and Machinery 100,000
Motor vehicle 75,000
Debtors 50,000
Outstanding salary 3000
Cash in hand 3000

PROCEDURE 

1. Open GNUkhata – Application —-> Office ———>GNUkhata 
2. Click on “Create Organisation” tab and 
3. enter “Organisation Name “ “XYZ Ltd” 

Case : As Is 
Organisation Type : Profit Making 
Financial year :01-01-2017 to 31-12-2017 
Select “ Accounts only” and click “Proceed” button 

4. Create Admin – Enter User name ,Password,security question for resetting password and answer for security question (Enter xyz in all fields) and click on “ Create & Login”.
5. Create Ledger Accounts. click on Master —–> Create Account. 

Enter Group Name,Sub Group Name, Account Name and enter opening balance 

Group Name Sub Group Name Account Name Opening Balance
Capital None Capital A/c 2,50,000
Current Assets Create “loose Tools” Loose tools A/c 50,000
Current Liabilities Sundry Creditors for PurchaseCreditors A/c 50,000
Loans(Liability) Secured/Unsecured bank loan A/c 75,000
Fixed Assets Land/Building Land & Building A/c 1,00,000
Fixed Assets Plant & Machinery Plant & Machinery A/c 1,00,000
Fixed Assets Create “ Vehicles” Motor Vehicle 75,000
Current Assets Sundry Debtors Debtors A/c 50,000
Current Liabilities Sundry Creditors for expenseOutstanding Salary 3,000
Current Assets cash Cash in Hand 3,000

Enter Save after each Account details entered. To display Balance sheet Report—> Balance sheet

OUTPUT 

Balance sheet total 3,78,000 

PRACTICAL-19 – LibreOffice Base 

Enter the following in a database table with the file name Empdetails 

EMPID EMPNAME EMPSEX EMPBASICPAY
100 ARUN 30,000
101 NISHA 60,000
102 ANIL 40,000
103 ROOPA 80,000

a. Display the name of employees drawing BASIC PAY greater than or equal to 60000
b. Display the name of employees begin with “A” 

PROCEDURE 

1. Open LIbreOffice Base Application—->Office——>LibreOffice Base 
2. In Database Wizard window click on Create a new database 
3. Click Next and then Finish 
4. Give a name for the Database ,Empdetails and select the location (Ex :Desktop)for saving the file, and click Save. 
5. Create a table , select Tables under Database and click on Create table in Design view. 6. Enter Field Name and Field Type as follows 

Field Name Field Type
EMPID Number[NUMERIC]
EMPNAME Text [VARCHAR]
EMPSEX Text [VARCHAR]
EMPBASICPAY Number[NUMERIC]

Click Save under File Menu and give name Employee and click OK 
7. Set a “Primary key” by selecting the EMPID row and right click the mouse and select Primary key and Save the table and close the table creation window. 
8. Create Forms – Select Forms under Database and select Use wizard to create form
9. Click on “>>” button to add all fields to form .Then Click Next 
10. Click Next button and select Columnar – Labels left under Arrangements of the main form and click Next. 
11. Click Next to skip “Select the data entry mode” and click again Next 
12. Click Next and enter the form Name “Employee details” and Click Finish
13. Enter employee details as per the question through the Form , Save the entered details by clicking File——> Save,Then close the data entry window. 
14. Create Query 1 Click on Queries icon and select Create query in design view
15. Select Tables and add Employee table by clicking Add Button then click Close. 
16. In Query design view select “EMPNAME” in first column and “EMPBASICPAY” in second column from dropdown list. 
17. Type >=60000 in second column against Criterion row and save the query, File—–>save.
18. Run the Query by clicking F5 or Edit—–>Run Query. 
19. Create Query 2 Click on Queries icon and select Create query in design view
20. Select Tables and add Employee table by clicking Add Button then click Close.
21. In Query design view select “EMPNAME” in first column and “EMPBASICPAY” in second column from dropdown list. 
22. Type like A* in second column against Criterion row and save the query, File—–>save.
23. Run the Query by clicking F5 or Edit—–>Run Query. 

a)Query 1 

Query 1 

PRACTICAL-18(Pool of question no:30) 

A ) Prepare a payroll of employees with the Gross Pay on the basis of the following database table 

EMPID EMPNAME EMPBASICPAY DA HRA
201 SUBHASH 40,000 250
202 GEETHA 41,500 250
203 SAJNA 48,000 250
204 AKHIL 54,000 250

(DA – 20% of basic pay) 

B ) Display the salary details of the employees whose name ending with “A”. 

PROCEDURE 

1. Open LIbreOffice Base Application—->Office——>LibreOffice Base 
2. In Database Wizard window click on Create a new database 
3. Click Next and then Finish 
4. Give a name for the Database ,PAYROLL and select the location (Ex :Desktop)for saving the file, and click Save. 
5. Create a table , select Tables under Database and click on Create table in Design view.
6. Enter Field Name and Field Type as follows 

Field Name Field Type
EMPID Number[NUMERIC]
EMPNAME Text [VARCHAR]
EMPBASICPAY Number[NUMERIC]
HRA Number[NUMERIC]

7. Set a “Primary key” by selecting the EMPID row and right click the mouse and select Primary key and Click Save under File Menu and give name SALARYDETAILS then click OKand close the table creation window. 
8. Open SALARYDETAILS table by Select the table,Mouse Right click Open(or Double click on table) and enter the given details as in the question. 
9. Save the records File—–> save the current record and close the table. 
10. Create Query Click on Queries icon and select Create query in design view
11. Select SALARYDETAILS table by clicking Add Button then click Close. 
12. In Query Design window ,double click on EMPID, EMPNAME and EMPBASICPAY to add these fields to Query. 
13. In the next to EMPBASICPAY column enter the formula “EMPBASICPAY”*20/100 to calculate the DA as 20% on Basic Pay. Then enter DA as field name against Alias.
14. Double click on HRA to add the field to Query. 
15. Next to HRA column enter the formula EMPBASICPAY+(EMPBASICPAY*20/100)+HRA to calculate the Gross Salary of the employees.Against the “Alias” field Enter GROSSPAY.
16. Press F5 or click on Run Query button to display the output. 
17. in the Criterion field of the EMPNAME enter the following criteria to display only the employee whose name ends with A, lIke *A 

OUT PUT 

PRACTICAL- 21

Create a Database table named TABLE_EMPLOYEE and enter the following details using a Form. 

EMP_ID EMP_NAME BP HRA
1001 MAJEED 10000 1500
1002 ABHILASH 20000 1500
1003 SUNIL 30000 1500

Also create a Query to display EMP_NAME and BP 

PROCEDURE 

1. Open LIbreOffice Base Application—->Office——>LibreOffice Base 
2. In Database Wizard window click on Create a new database 
3. Click Next and then Finish 
4. Give a name for the Database ,ABC and select the location (Ex :Desktop)for saving the file, and click Save. 
5. Create a table , select Tables under Database and click on Create table in Design view.
6. Enter Field Name and Field Type as follows 

Field Name Field Type
EMP_ID Number[NUMERIC]
EMP_NAME Text [VARCHAR]
BP Number[NUMERIC]
HRA Number[NUMERIC]

7. Set a “Primary key” by selecting the EMP_ID row and right click the mouse and select Primary key then Click Save under File Menu and give name TABLE_EMPLOYEE and click OK 
8. and close the table creation window. 
9. Create Forms – Select Forms under Database and select Use wizard to create form
10. Click on “>>” button to add all fields to form .Then Click Next 
11. Click Next button and select Columnar – Labels left under Arrangements of the main form and click Next. 
12. Click Next to skip “Select the data entry mode” and click again Next 
13. Click Next and enter the form Name “EMPLOYEE_DETAILS” and Click Finish
14. Enter employee details as per the question through the Form , Save the entered details by clicking File——> Save,Then close the data entry window. 
15. Create Query 1 Click on Queries icon and select Create query in design view
16. Select Tables and add Employee table by clicking Add Button then click Close.
17. In Query design view select “EMPNAME” in first column and “BP” in second column from dropdown list. 
18. Save the query, File—–>save. 
19. Run the Query by clicking F5 or Edit—–>Run Query. 

OUTPUT 

Table 

Form 

PRACTICAL-22

Create database tables named EMPLOYEE and PAY_DETAILS with the following field names 

Table Name Fields
EMPLOYEE EMP_ID , EMP_NAME , EMP_SEX
PAY_DETAILS EMP_ID , BP, DA , HRA

Create relationship between these two tables. 

PROCEDURE 
1. Open LIbreOffice Base Application—->Office——>LibreOffice Base 
2. In Database Wizard window click on Create a new database 
3. Click Next and then Finish 
4. Give a name for the Database ,XYZ and select the location (Ex :Desktop)for saving the file, and click Save. 
5. Create table EMPLOYEE , select Tables under Database and click on Create table in Design view. 
6. Enter Field Name and Field Type as follows 

Field Name Field Type
EMP_ID Number[NUMERIC]
EMP_NAME Text [VARCHAR]
EMP_SEX Text [VARCHAR]

7. Set a “Primary key” by selecting the EMP_ID row and right click the mouse and select Primary key then Click Save under File Menu and give name EMPLOYEE and click OK
8. Create table PAY_DETAILS , select Tables under Database and click on Create table in Design view. 
9. Enter Field Name and Field Type as follows 

Field Name Field Type
EMP_ID Number[NUMERIC]
BP Number[NUMERIC]
DA Number[NUMERIC]
HRA Number[NUMERIC]

10. Set a “Primary key” by selecting the EMP_ID row and right click the mouse and select Primary key then Click Save under File Menu and give name PAY_DETAILS and click OK 11. and close the table creation window. 
12. Create Relationship Tools———–> Relatioship. 
13. Select the table EMPLOYEE click Add and select table PAY_DETAILS click Add then click Close. 
14. Click Insert ———– New Relation, then Select EMP_ID under Table EMPLOYEE and EMP_ID under table PAY_DETAILS in Fields involved 
15. Then click OK 

OUTPUT 

PRACTICAL-21 – LIbreOffice Report Creation

Create STUDENT table in database with the following fields. 

Field Name Data Type Field Width
STUD_NO TEXT 5
STUD_NAME TEXT 25
SEX TEXT 1
PLACE TEXT 20
CLASS TEXT 10

(Hint: Classes are Science, Commerce and Humanities) 
a) Enter six records with imaginary details. 
b) Prepare of report of students from commerce 

PROCEDURE 

1. Open LIbreOffice Base Application—->Office——>LibreOffice Base 
2. In Database Wizard window click on Create a new database 
3. Click Next and then Finish 
4. Give a name for the Database ,STUDENTS DATA and select the location (Ex :Desktop)for saving the file, and click Save. 
5. Create a table , select Tables under Database and click on Create table in Design view. 6. Enter Field Name and Field Type as follows 

Field Name Field Type Set Field Width Under FIELD PROPERTIES :- Length
STUD_NO Number[NUMERIC] 5
STUD_NAME Text [VARCHAR] 25
SEX Text [VARCHAR] 1
PLACE Text [VARCHAR] 20
CLASS Text [VARCHAR] 10

7. Set a “Primary key” by selecting the STUD_NO row and right click the mouse and select Primary key and Click Save under File Menu and give name STUDENT and click OK then close the table creation window. 
8. Open STUDENT table by Select the table,Mouse Right click Open(or Double click on table) and enter the details as per the question.(six imaginary details) 
9. Save the records File—–> save the current record and close the table. 10. Create Query Click on Queries icon and select Create query in design view 11. Select STUDENT table by clicking Add Button then click Close. 
12. In Query Design window ,select click on STUD_NO, STUD_NAME,SEX,PLACE and CLASS .
13. in the Criterion field of the CLASS type COMMERCE to display only the Commerce students.save the Query by File—–>Save as Query:1 
14. Create Report click on Report under Database 
15. Select Use wizard to create a report, in the “Table or Queries” selection field ,select STUDENT table and press >> to add all fields to report. 
16. Click Next in “labelling fields”,”Grouping levels”,”sort options”, and choose layout”
17. In create report section ,enter the title of report as “STUDENT” and click “ Finish” button. 

OUT PUT