Computerised Accounting Practicals

Questions , Procedure and solution videos

1 – Libre Office Calc – Functions and Formula – MATHEMATICAL FUNCTIONS

SUM,SUMIF 

Given below is a table showing the Name, Designation and Monthly Salary 

paid for different employees in Royal Traders for March 2022 

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: 

  1. a) The total monthly salary by naming the range as’ TOTAL_SALARY’
  2. b) The total monthly salary paid to the Finance Manager (FM) in the firm. 
  3. c) 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 the ‘DATA’ , next ‘Define Range’
  5. Enter the name ‘SALARY’ in the Name box and 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 the 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 

  1. 2,60,000                                                                                                            
  2. 85,000
  3. Jithesh

 

2 – COUNT,COUNTA,COUNTIF,COUNTBLANK

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 

3 – MAX,MIN,AVERAGE,CONCATENATE

A. Following are the scores obtained by some students in a competitive examination.find the HIGHEST ,LOWEST and AVERAGE scores using appropriate functions in the 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 For Solved

4 – CONDITIONAL FORMATTING

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 

5 – DATA ENTRY,TEXT MANAGEMENT

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 

,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 

5A – ROUND,ROUNDUP,ROUNDDOWN
Production of major Crops in 2021Products Value in Rs.
Rice356.753
Wheat865.63
Groundnut536.2541
Mustard14.6532
Green Gram2792.25
Coconut3695
Coffee625.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) 

To watch video : Click here 

OUTPUT

6 – TAX computation using IF

PRACTICAL-6

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 

7 – PIVOT TABLE

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 

8 – ONE VARIABLE DATA TABLE

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. 

www.comlive.in

OUT PUT 

9 – PAY ROLL ACCOUNTING
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 
A1B1C1D1E1F1G1H1I1J1K1
NameBasic PayDAHRATAGross PayPF SubscriptionTDSPF LoanTotal DeductionNet Pay
  1. 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 
  2. Enter the formulas :- 
    1. For calculating DA in cell C2 =B2*36% 
    2. For calculating HRA enter in D2 =IF(B2>52000,1750,1500) 
    3. For calculating Gross Pay enter in F2 =SUM(B2:E2) 
    4. For calculating PF Subscription enter in G2 = F2*10% 
    5. For calculating TDS enter in cell H2 =IF(F2>60000,F2*20%,F2*10%) 
    6. For calculating Total Deduction enter in cell J2 =G2+H2+I2 
    7. For calculating Net Pay enter in cell K2 =F2-J2 
  3. Copy the formulas in remaining cells using Fill handle  or drag formula

OUTPUT 

10 – Depreciation SLN

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 

11 – Pie Chart

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 

1. Enter the data as in the question and select the all data. 

2. 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 

12 – Column Chart

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 

13 – 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. 

4. Enter the Chart Title “Total Sales “ , in X -axis “ Year” , in Y Axis “ Amount“ and Set the Legend Position right and click Finish. 

OUTPUT 

14 – GNUKhata – Company creation,ledger creation and voucher entry

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 

Jna.1 –started business with cash Rs.50000 

Jna.1-Purchased office furniture Rs.4500 

Jna.1-Cash purchases Rs.25000 

Jna.1-Credit sales to Anand Rs.43000 

Jna.2-Salary to staff Rs.12000 

Jna.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

www.comlive.in

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 ) 

15 – GNUKhata- P&L A/c and Balance Sheet

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. 

www.comlive.in

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 

16  GNUKhata- Bank Reconciliation statement

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 www.comlive.in

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 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 

16A- GNUKhata – Ledger creation with opening Balance

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

www.comlive.in

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. 

OUTPUT 

Balance sheet total 3,78,000 

17 Base TABLE,FORM,QUERY

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. 

www.comlive.in

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 

b ) Query 2 

18 – Base PAY ROLL

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. 

www.comlive.in

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 

19 – Base FORM , QUERY

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. 

www.comlive.in

19. Run the Query by clicking F5 or Edit—–>Run Query. 

OUTPUT 

Table 

Form 

20 Base – RELATIONSHIP

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. 

www.comlive.in

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 

21 Base REPORT

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. 

www.comlive.in

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