Computerised Accounting Practicals

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

PRACTICAL – 2 

	A	B	C	D	E	F	G	H	I	J
1	390	651			856	765	STOCK	192	CASH	1032
2	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 

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. 

	A	B	C	D	E	F	G	H
1	Name	ARUN	BIBIN	CINI	DENNY	EBIN	FABIN	GEO
2	Scores	150	180	410	480	260	161	515

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

	A	B	C	D	E
1	Name	House Name	Place	Post	PIN
2	JAYA	DEEPAM	NEWSTREET	KOZHIKODE	680534

PROCEDURE 

A 

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. 

B 

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

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
1	JIBY	25000	28/01/2016
2	JINU	30000	15/01/2016
3	GRACE	45000	15/01/2016
4	LUCY	37000	31/03/2016
5	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 

Following table gives the details of some products. 

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 COOL LINKS	25
4	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 

Production of major Crops in 2021	Products Value in Rs.
Rice	356.753
Wheat	865.63
Groundnut	536.2541
Mustard	14.6532
Green Gram	2792.25
Coconut	3695
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) 

To watch video : Click here 

OUTPUT

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 

From the following prepare Pivot Table 

SI NO	PRODUCTS	SALES VOLUME(Rs)	COUNTRY
1	SAMSUNG	25000	KOREA
2	LG	20000	AMERICA
3	iPHONE	30000	ENGLAND
4	MICROMAX	50000	INDIA
5	VIVO	40000	INDIA
6	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 

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 

	A	B
1	Loan Amount	300000
2	No. of Payments (Months)	48
3	Interest rate 10% ( Monthly rate 10% / 12)	0.83%
4	Monthly Installment
5	No.of Payments	Monthly Installments
6	12
7	24
8	36
9	48
10	60
11	72

l 

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 

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 :-
   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 
5. Copy the formulas in remaining cells using Fill handle  or drag formula

OUTPUT 

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

Asset	Cost of purchase	Installation charges	Transportation Charges	Pre-operati ng expenses	Salvage value	Life 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 

A	B	C	D	E	F	G	H	I
Asset	Cost of purchase	Installation charges	Transportation Charges	Pre-oper ating expenses	Salvage value	Life in years	Total cost	Depreciation
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 

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

Vehicle Type	Bus	Truck	Auto rikshaw	Car	Two wheeler	Heavy Vehicle
Number of Vehicles	575	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 

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

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 

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 Type	Key	Date	Account Name	Dr /Cr	Amount
Receipt	F4	01-10-2016	Cash Account Capital Account	Dr Cr	50000

www.comlive.in

Payment	F5	01-01-2016	Furniture Account Cash	Dr Cr	4500
Purchases	F7	01-01-2016	Purchases Account cash Account	Dr Cr	25000
Sales	F6	01-01-2016	Anand Account Sales Account	Dr Cr	43000
Payment	F5	02-01-2016	Salary Account Cash Account	Dr Cr	12000
Receipt	F4	02-01-2016	Cash Account Anand Account	Dr Cr	17500

7. Display cash Balance 

Report—->Ledger ——>Cash Account——-> View 

OUTPUT 

26000 (Balance ) 

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 Type	Key	Date	Account Name	Dr /Cr	Amount
Receipt	F4	01-06-2016	Cash A/c Capital A/c	Dr Cr	70000
Payment	F5	01-06-2016	Machinery A/c Cash A/c	Dr Cr	10000
Payment	F5	01-06-2016	Rent A/c Cash A/c	Dr Cr	7500
Contra	F8	01-06-2016	Canara bank A/c Cash A/c	Dr Cr	20000
Purchases	F7	01-06-2016	Purchases A/c cash A/c	Dr Cr	8400
Sales	F6	01-06-2016	Cash A/c Sales A/c	Dr Cr	3260
Sales	F6	02-06-2016	Rajesh A/c Sales A/c	Dr Cr	2800

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

Voucher Type	Ke y	Date	Account Name	Dr /Cr	Amount
Receipt	F4	01-01-2017	Cash A/c Capital A/c	Dr Cr	100000
Contra	F8	01-01-2017	PNB Bank A/c Cash A/c	Dr Cr	50,000
Purchase	F7	02-01-2017	Purchases A/c PNB Bank A/c	Dr Cr	12000(Enter cheque No:as narration)
Receipt	F4	02-01-2017	PNB Bank A/c Alvin Traders A/c	Dr Cr	15000( enter cheque No:as narration)
Payment	F5	02-01-2017	Bibin and Co A/c PNB Bank A/c	Dr Cr	4000(Enter cheque No: as narration)
Contra	F8	02-01-2017	Cash A/c PNB Bank A/c	Dr Cr	16000

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 

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 Purchase	Creditors 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 expense	Outstanding 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 

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

EMPID	EMPNAME	EMPSEX	EMPBASICPAY
100	ARUN	M	30,000
101	NISHA	F	60,000
102	ANIL	M	40,000
103	ROOPA	F	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 

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 

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 

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 

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