Prasanna

Students can Download Chapter 6 Data Base Management System for Accounting Notes, Plus Two Accountancy Notes helps you to revise the complete Kerala State Syllabus and score more marks in your examinations.

Kerala Plus Two Accountancy Notes Chapter 6 Data Base Management System for Accounting

Database/ Data source – Introduction
A database is a collection of related data. It is organised in such a way that its contents can easily be accessed, managed and updated. In LibreOffice, database is also called data source. Database consists of interrelated data tables that are structured in a manner that ensures-data consistency and integrity. LibreOffice base, MS Access, Oracle, SQL server, etc. are the commonly used softwares for data base management.

Database Management System (DBMS)
DBMS is a collection of programs. It enables the users to create and maintain a data base. It is a general purpose software system that facilitates the process of defining, constructing and manipulating database for various applications.

Advantage of database/data source

1. All of the information is together
2. The information is portable
3. Information can be accessed at any time
4. Many users can access the same database at the same time.
5. Reduced data entry, storage and retrieval cost.

Disadvantages of database/Data source

1. Designing of database is a complex and time consuming process
2. Initial training is required for all the users
3. Installation cost is high

Basic concepts of LibreOffice Base

1. Entities: Anything in the real world is called entities. It may be person, place or things.
   Eg: Employee is an entity, Orange is an entry
2. Attributes: These.define the characteristics of an entity.
   Eg: Name, Age, Caste, Salary etc.
3. Identifiers: The unique attribute of an entity is called identifier. This is also called primary key.
   Eg: Admission number of a student, Aadhar Number of a person etc.
4. Relationships: These are the logical links between two entities or tables.

Components /Elements of LibreOffice Base

1. Fields: Individual pieces of data in a database are called fields.
2. Table: rows and columns to present fields in a database is called table. When creating a table, the characteristics of each field to be defined.
3. Forms: Forms are used to enter or modify data (fields) in to tables. Forms allow the user to display the data in a Table or Query.
4. Query: Query is a question. Queries are used to view, change and analyse data in different ways. It creates a new table from the existing tables based upon the question/ request asked to the data base.
5. Reports: It is used to create and present information based on queries in a easily readable format.

Planning (or designing) a database/ Data source
The first step in creating a database is to list down the various fields which are necessary for creating a database. The listed fields are used to create tables of the database. While entering fields into Tables, a primary key or an identifier is to be set for each table.

The primary key field cannot be left blank. The relationships of entities or tables can be created with the support of primary key. The relationships may be

• One -to-One
• One -to-Many
• Many-to-Many

The database created on the basis of relationships between different data tables is called relational database. The database design can be used to describe the structure of different parts of the overall data base. Avoiding the duplication of attributes/ fields is key criteria of database design.

Creating a new database
To Create a new database, select File → New → Database from the menubar, or click the arrow next to the New icon on the standard tool bar and select Database from the drop-down menu. Both methods open the Database wizard. On the first page of the Database wizard, Select create → a new databases → click Next.
The second page has two questions

1. Yes, register the data base for me
2. Open the database for editing.

Choose any one from the above and click Finish. In LibreOffice Base, the entire database is encompassed in a file with extension .odb. This file format is actually a container of all elements of the database, including Fields, Tables, Forms, Queries and Reports.

Creating database Tables
In DBMS, data is organised in Tables. A Table is a collection of data about a specific topic. Tables organise data into columns and rows. Each table is given a name. This is used to refer the table. The name depicts the content of the table. A database must have at least one table and may have several.

To work with Table, clicks the Tables icon in the Database list or Press Alt+A. The three tasks that we can perform on a table are in the Task list given below.

• Using the wizard to create a Table
• Creating a table by copying an existing table
• Creating tables in Design view.

1. Using the wizard to create a new Table:
Step 1: Open LiberOffice Base
Application → Office → LibreOfficeBase

Step 2: From the Data base wizard Screen, Create a database file or open an existing database file.
Select Database → Select Create a new database option and click on Finish button → then we get save dialogue box.

Step 3: Type appropriate file name. The default extension ‘.odb’ will be automatically added.

Step 4: Select location and click on Save

2. Using the wizard to open an Existing database:
Step 1: Open LibreOffice Base. From Database
wizard → Select open an existing database file option and click on Open button.

Step 2: Choose the file from the destination and click open.

3. Creating a table by Copying an existing table:
Step 1: Open LibreOffice Base
Application → Office → LibreOffice Base

Step 2: Click on the Tables icon in the database pane to see the existing tables
Select Database → Click on the Table icon → Right click on the Table from the Existing Tables.

Step 3: Choose Copy form the pop-up menu

Step4: Move the mouse pointer below the table, → right click → Select Paste. The copy Table dialogue opens.

Step 5: Change table name and click Next

Step 6:

Step 7: Click Create the new table is created

Step 8: Click the Save button at the top of the main database window.

4. Creating Tables in Design View:
Step 1: Click Tables from Data base Pane Database Pane → click Tables.

Step 2: Click Create Tables in design view in Tasks area. The design view of the new table will appear in the work area of the window.

Step 3: In design view, we can see three columns like Field Name, Field Type and Description. Create required field for the Table. Click Field Name cell → center Field names from top to bottom.

Step 4: Right click on the Field Name required to set as unique identifier for the tables, Select Primary Key option from the pop-up menu.

Step 5: In the Field Type, select appropriate field type from the combo box.

Step 6: In Description Field, we canenter appropriate description for each attributes.

Step 7: Save the table by providing table name.

5. Defining Relationships between Tables:
Relationships are used for connecting tables in database to get the advantage of data redundancy. Having completed the designs of all data tables, the next step is to establish relationships between different tables.
Step1: Click on the Tools menu and then Relationships

Step 2: Relation Design window opens and in the work area, a Add Tables dialogue box will appear.

Step 3: Select a Table and clik Add bottom to add it in the relationships.

Step 4: Add two tables in this manner after that click the close button.

Step 5: Create a relationship between two tables, Position the mouse pointer over desired field in table object, hold down the left mouse button, drag the pointer right to targeted field of the next table object and then release the mouse button.
This can also be done Insert → New Relation menu.

Creation of Forms in LibreOffice Base
Forms are used to input data into the database. In the language of database, a Form is a front end for data entry and editing. A Form is a window or screen that contain numerous fields or spaces to enter data. Each field holds a field label so that any user gets an idea of its contents.
The following two methods are used to create forms in LibreOffice Base

• Create Form in Design View
• Use wizard to create Form. The easy way to create Forms is use wizard to create Form.

1. Use Wizard to Create Form:
Step 1: Select Forms options from Database Pane

Step 2: Click on Use Wizard to Create Form Then. Form wizard window will appear.

Step3: Under the Table or queries, select Tables. The fields in the selected tables are listed in Available Fields list. Select the required field on by one and click on

Step 4 – After selecting the required field proceed by clicking Next.

Step 5 – Add sub Form fields. This step is similar to step 3

Step 6 – Get joined Fields.
This step is for tables and queries for which no relationships has been defined. The wizard skip this step. Because we have already defined the relationship.

Step 7: Arrange controls: A control in a Form consist of two parts: label and field. This step in creating the Form determines where a controls label and field are placed relative each other. Four choices are available.

Step 8: Set data entry: It is better to accept the default settings. Click Next.

Step 9: Apply styles: The background colour, field boarder etc. can be selected from this options.

Step 10: Set the name of the form: we can give the name of the Form we are creating. The name must be unique and must have a relation with the data to be stored → click Finish. The Form opens in Edit mode.

Entering data in a form
The text box can be used to add data in the Form. Click on the text Box Icon, and click on the work area. The cursor will be positioned on the Top left of the work area. Then the matter is to be added. The text entered can be formatted. Images can also be inserted in the Form by selecting Image Insert Icon.

Creation of Query in LibreOffice Base
Queries are used to get specific information from the database. Queries are also used to manipulate the database content. Structured Query Language (SQL) is the most widely-used query language. LibreOffice Base also uses SQL command for querying its database. The query operations can be done in two different ways.

• Using the wizard to create a query
• Using the design view

1. Using the wizard to create a query:
Step 1: Selection of Fields
The first step in Query wizard is field selection. All the tables included in the data base can be seen in the Table list select the appropriate Table. All the fields of the selected table can be seen in the Available Fields list.

The user can select the fields needed from the list using the tools arranged right to Available fields list. The selected fields can be seen in Fields in the Query list. The order of the selected fields can change using the tools (∧ and ∨) right to Fields in the query list. Then, press Next button or Finish button.

Step 2: Select the sorting order: In this step, the field name to sort the query result can be selected. (Skip this step, if no sorting is needed).

Step 3: Select the search conditions: This step specifies the search conditions to filter the query. (Skip the step, if no filtering is needed).

Step 4: Details of summary: This page specifies whetherto display all records of the query, or only the results of aggregate functions. This page is only displayed when there are numerical fields in the query that allow the use of aggregate functions.

Step 5: Grouping conditions: Specfies wheter to group the query. The data source must support the SQL statement “order by clause” to enable this page of the wizard.

Step 6: Assign aliases if desired: – Thisjsage helps to assign aliases to field name. Aliases are optional, and can provide more user- friendly names, which are displayed in place of field names.

Step 7: Overview: This wizard page gives an overview of the query made. It helps to enter a name of the query, and specifies whether to display or to modify the query afterthe wizard is finished.

Step 8: Press Finish button after the completion of Query wizard entry. The Query will be saved. The user can run this query at any time.

Step 9: Run the saved Query: Select Queries option from the left panel of the LibreOffice Base window. The saved query can be seen in the right side. Double click on the query name to run the query.

2. To create query in design view:

• Step 1: Use the option Create Query in Design view from Base window to create query in design view.
• Step 2: Use Add Table or Query dialogue to include table(s) to query design.
• Step 3: Include the fields and formula in the top row and give aliases in the second now, if needed.
• Step 4: Press Run Query button to execute the query.

Creation or Reports in LibreOffice Base
Information from a database can be generated through the Reports in LibreOffice Base. The reports can be printed and formatted as perthe requirements of user. The reports can be edited, printed and exported to PDF format.
The reports can be created by the following two ways.

• Create Report in Design view
• Use wizard to ere ate Report

1. Use wizard to create Report in LibreOffice Base:

• Step 1: Click the icon Reports in Database pane
• Step 2: Click on Use wizard to create Report option in Task area.
• Step 3: Select table or query from the drop down option for which reports need to be created.
• Step 4: Select the required fields
• Step 5: Enter title for the report in the field Title of the report and click on Finish button.
• Step 6: The report generated is in the read only mode. It can be edited by clicking on Edit Document option.

In short, When the Report wizard is opened, the Report Builder is also opened. As we make our selections in the wizard these appear in layout in the Report Builder. After finishing the selections, save the report, name it and then close it.

Accessing other Data Sources
LibreOffice Base allows other data sources to be accessed and linked into LibreOffice documents. To acess a data source which is not a .odb file.

• Step 1: File → New → Database opens the Data base wizard window.
• Step 2: Select Connect to exisiting data base. Click the arrow next to Data base type field and select the Database type from drop down list. Click Next.
• Step 3: Click Browse and select the database click Next.
• Step 4: Accept the default settings: Register the data base for me, and open the database for editing. Click Finish. Name and save the database in the location of our choice.

1. Accessing a spreadsheet as a data source:

• Step 1: Choose File → New → Data base.
• Step 2: Select Connect to an existing database. Select Spread sheet as a database type.
• Step 3: Click Browse to locate the spreadsheet we want to access. If spreadsheet is password protected, Check the password required box, click Next.
• Step 4: If the spreadsheet requires a users name, enter it. If a password is also required, check its box. Click Next → save the file.

2. Registering *.odb databases:
Databases created by LibreOffice base are in the *.odb format. Other programs can also produce database in this format. Registering a*.odb data base is simple.

• Step 1: Select Tools → Options → Libre Office Base → Database
• Step 2: Under Registered data Bases, Click New.
• Step 3: Browse to where the database is located.
• Step 4: Make sure the registered name is correct .Odb
• Step 5: Click OK.

Using data sources in LibreOffice
Any data source registered in spreadsheet or text document can use in other LiberOffice components including writer and calc.

1. Viewing data sources:
Open a document in writer or calc. To view the data sources available, Press F4 or select View → Data sources from the pull down menu. This brings up a list of registered databases. To view each data base, click on the arrow to the left of the database’s name.

2. Editing Data sources:
Some data sources can be edited in the Data view window. A record can be edited, added or deleted. Editing the data requires only a click in the cell whose data should be changed. To delete the record, right click on the gray box to the left of row to highlight the entire row, and select Delete row to . remove the selected row.

3. Launching Base to work on data sources:
We can launch LibreOffice Base at any time from the Data source window. Just right click on a database, or its tables or Queries icons and select Edit Database File. In Base, we can edit, add, and delete tables, queries, forms and reports.

4. Using Datasources in writer and Calc.:
Data can be placed into writer and Calc documents from the tables in the data source window. In writer, values from individual fields can be inserted. Or a complete table can be created in the writer document. One common way to use a data source is to perform a mail merge.

Students can Download Chapter 5 Accounting Software Package – GNUKhata Notes, Plus Two Accountancy Notes helps you to revise the complete Kerala State Syllabus and score more marks in your examinations.

Kerala Plus Two Accountancy Notes Chapter 5 Accounting Software Package – GNUKhata

GNUKhata – Introduction
GNUKhata is an accounting software based on Double Entry Book Keeping system. It is a Free and Open Source Software (FOSS).

GNUKhata is developed by Digital Freedom Foundation in association with International Centre for Free and Open Source Software (ICFOSS). It is a free and flexible software for accounting and inventory management.

Features of GNUKhata

1. It is free and open source accounting software.
2. It is based on double entry book keeping
3. All financial reports can be prepared
4. Display of dual ledger facility
5. Attachment of source document to vouchers is possible
6. Linking of sales and purchase transactions to invoice.
7. Export or import of data from spread sheet is possible.
8. It ensures password security and data audit facility.

Installing GNUKhata
GNUKhata is Free and Open Source Software (FOSS). It can be legally downloaded and copied without having to pay anything to anyone. It is very simple to install GNUKhata in Linux. The installer file can be downloaded from the website www.anukhata.in. When we open GNUKhata for the first time, we can see a welcome screen.

Create Organisation
The first step in GNUKhata is to create an the organisation. To create a new organisation, click on “Create organisation” or press shift + control + R. While creating an organisation, the following details are to be given.

• Organisation Name: enter the name of the organisation and press Enter Key.
• Case: Choose the appearance of the organisation name, the options are As -is, Upper case, Lower Case or Title Case.
• Organisation Type: Select the organisation type either Profit Making or Not for Profit.
• Financial year: Enter the opening date of financial year then press enter key, closing date will show automatically which can be edited.
• Inventory: Tick the box of Inventory for maintaining inventory accounts.

1. Create Admin and log in:
There are Four levels of users in GNUKhata. They are Admin, Manager, Operatorand internal Auditor. Each has different authorities. Only one user can log in as “Admin”. There may be any number of users in the role of manager, operator and internal auditor.

“Create Admin” is mandatory. After creating an organisation, the next step is “Create Admin”. Fill all the fields in the Create Admin window and Enter/click on create and login. Now we can see a Menu bar at the top. Click Menu items to activate the Keyboard shortcuts.

2. Organisation Particulars:
We can enter the organisation details like Address, Country, State, city, etc through Edit Organisation Particulars in Master menu. Enter or click on Save to save the details and click on Reset to clear the fields, if necessary.

3. Change Organisation:
Select Change Organisation from Sign Out menu to exit the active organisation. To change the user, select Logout from Sign Out menu.

4. Selecting Organisation:
To select the existing organisation, click on Select Existing Organisation tab and select Organisation name from the drop down menu.

5. Deleting Organisation:
After login as “Admin” user, select Delete Organisation from Administration menu. Confirm the decision to delete the organisation, the organisation will be deleted.

Groups and Sub-Groups
Grouping of account is a method of organising the large number of ledger accounts into sequential arrangement for recording and summarisation of accounting data. GNUKhata has predefined Groups and Sub-Groups.
They are:

• Balance Sheet Groups
• Profit & Loss / Income & Expenditure Account Group

1. Balance sheet Groups in GNUKhata:

2. Profit and loss / Income & Expenditure Account Groups in GNUKhata:

3. Description of the Groups and Sub Groups:

(i) (a) Capital: Amount contributed by proprietor, partners and share holders are recorded in this group.
(b) Corpus: Amount Contributed by the members of a non profit organisation (capital fund) are recorded in this group.

(ii) Current Assets: The assets which are consumed in operations are known as current assets. Accounts of such assets generated in the course of doing business are recorded in this group. The sub groups are

• Bank-(Deposits)
• Cash – (in hand, at factory, petty cash)
• Inventory-(closing stock)
• Loans and Advances-(temporary advance to Staff)
• Sundry Debtors – (Credit sales)

(iii) Current Liability: The liabilities which are to be paid with in a short period (less than one year) are called current liabilities. The sub groups of current liabilities are.

• Provisions: (PF, ESI, TDS dues, etc.)
• Sundry CreditorsforExpenses: (Outstanding expenses)
• Sundry Creditors for Purchase: (Amount payable to suppliers)

(iv) Fixed Assets: Accounts of all fixed assets (life span more than one year) are recorded in this Group. The sub groups are

• Building
• Furniture
• Land
• Plant and Machinery

(v) Investments: Contains accounts of investment made by the organit#ation. The sub groups are

• Investment in Bank Deposits
• Investment in shares and Debentures

(vi) Loan (Assets): Includes accounts of long term loans given

(vii) Loans (Liability): Amount borrowed from financial institutions. The sub Groups are

• Secured: (Loan against Security)
• Unsecured: (No Security)

(viii) Miscellaneous Expenses (Assets): This includes preliminary and preformation Expenses to the extent those are not written off.

(ix) Direct Income: Income from sale of goods, included in this group. If it is a service organisation, income from fees will come under this group.

(x) Indirect Income: All incomes which is not a direct income come under this group.
Eg: rent received, discount received, dividend received etc.

(xi) Direct Expenses: Expenses of purchase or manufacturing of goods are included in this group.
Eg. Wages, carriage inward, consumables etc. GNUKhata opens Opening Stock Account under this group.

(xii) Indirect Expenses: All office, administration sellng and distribution expenses are coming under this group.
Eg: Salary, Interest, depreciation, etc.

(xiii) Reserves: Contains retained earnings reserves and surplus.

System Generated Ledger Accounts in GNUkhata
GNUkhata has 29 predetermined Groups and subGroups. Out of these 13 are Groups and 16 are sub Groups. 25 Predefined Groups and sub groups are related with Balance sheet. Out of these 9 are Groups and 16 are Sub Groups. 4 Groups are related with Profit and Loss Account/ Income and Expenditure Account.

In GNUkhata, there are 4 system generated ledger accounts. We can neither change the name nor delete these accounts. Do not create accounts with the similar names. These are.

Creating Ledger Accounts in GNUKhata
A ledger account contains record of all transactions relating to Assets, Capital, Liabilities, Expenditure and Revenues. A ledger account is to be created under any of the above five groups. Based on the group under which a ledger account is created, the “ balance of the ledger account will be appeared either in Trading, Profit and loss account or in Balance sheet.

1. Steps for Creation of Ledger Accounts:

1. Step 1: Click on Master Menu or Press Shift + Control + M or Press F2
2. Step 2: Select Create Account option. Then a dialogue box appears. Enter all the details.
   • Group Name: Select the name of the group from the drop down list.
   • Sub group name: Select the name of sub gorup from the drop down list depending on the group we selected. A new subgroup can also be created.
   • Account name: Enter the name of account which we want to create
3. Step 3: Click on Save button

2. Display Ledger Accounts:
To display Ledger accounts, select List of Accounts from Report menu. Now we can see a table containing a list of all ledger accounts along with its group name and sub group name.

3. Editing a ledger account:
To edit a ledger account, select Edit Account from Master menu. Here we can change Account name and opening balance. But we can not change the name of Group and sub Group.

4. Deleting a ledger account:
To delete a ledger account, select Edit Account from Master menu. Select the ledger account – we want to delete, click on Delete Button and confirm the deletion. System generated account and the ledger account already used in voucher cannot be deleted.

Types of Vouchers
GNUKhata has the following pre defined voucher types. We can not create a new voucher type.

Voucher Entry
Recording a transaction through voucher is called voucher entry. While recording a voucher the debit part of the transaction recorded first and there after credit part. We can add any number of debits and credits in a voucher.

1. Finding and Editing a Voucher Entry:
To edit a voucher entry, it must be found out first. For this, the given steps are to be followed.

• Step 1: Select Find/ Edit voucher from voucher menu.
• Step 2: Select any one Criteria and press Enter key. All transactions fulfilling the criteria will be displayed.
• Step 3: Select the transaction that we want to edit and press Enter key
• Step 4: Click Edit button to open the transaction in edit mode make necessary changes.
• Step 5: Click on Save button to save changes.

2. Deleting a voucher entry:
To delete a voucher, First find it and click on Delete, after confirmation, the record will be deleted. Deleted vouchers cannot be restored.

3. Add account while in voucher entry:
While recording transactions in voucher entry mode, we can add a ledger account by clicking on Add Account, select the Group, sub Group, enter the account name and opening balance, if any. Click on Save Button and return to Voucher entry mode.

Reports
One of the tabs in the menu bar is Reports. From the Report menu, we will enable to view reports such as Ledger, Trial Balance, Balance sheet, Profit and Loss Account, List of accounts and List of deleted vouchers. For all these reports, we have to specify the periods.

1. Display Ledger Account:

• Step 1: Click on Report tab from the menu bar
• Step2: Select Ledger option from the list, Now view Ledger screen appears
• Step 3: Select the name of ledger account we want to display
• Step 4: Enter from date and to date
• Step 5: Click on view button to view the ledger

2. Display Trial Balance:

• Step1: Click on Report tab from the menu bar
• Step 2: Select Trial balance from the menu
• Step 3: Enter From date and To date
• Step 4: Select the Trial Balance Type
• Step 5: Click on view button to view the Trial Balance

3. Display Profit and Loss Account/Income and Expenditure Account:

• Step 1: Click on Report tab from the menu bar
• Step 2: Select Profit and loss or Income and Expenditure from the list
• Step 3: Enter From date and To date.
• Step 4: Click on view button

4. Display Balance sheet:

• Step 1: Click on Report Tab from the menu bar
• Step 2: Select Balance sheet from the list
• Step 3: Select Balance sheet type
• Step 4: Click on View button

Bank Reconciliation statement (BRS)
Bank Reconciliation statement is prepared by an account holder to reconcile cash book balance and pass book balance on a specific date. It is prepared for bank accounts opened under the Sub Group Bank of the Group Current Assets. To open Bank Reconciliation Statement, select Bank Reconciliation statement from the Master menu.

1. Causes of Difference between Cash Book Balance and Pass Book Balance:

• Cheque issued; but not presented for payment
• Cheque deposited; but not collected
• Direct payment by a customer to the bank
• Interest on deposit credited by the bank
• Dividend, rent, etc collected by the bank
• Payment made on behalf of the customer
• Bank charges as per pass book
• Bills receivables discounted, but dishonored
• Interest on overdraft debited in the pass book

2. Terms associated with BRS:

• Transaction Date: The date of the transaction
• Clearance date: The date on which a particular transaction appears in a pass book
• Reconciliation period: The period for which Bank Reconciliation is done is called Reconciliation period.
• Statement of uncleared items: When the clearance date and Transaction date of a transaction falls with in the Reconciliation period, that transaction is said to be cleared. Otherwise it is uncleared item.

Short cut keys in GNUKhata

Students can Download Chapter 4 Graphs and Charts for Business Data Notes, Plus Two Accountancy Notes helps you to revise the complete Kerala State Syllabus and score more marks in your examinations.

Kerala Plus Two Accountancy Notes Chapter 4 Graphs and Charts for Business Data

Introduction
One of the very useful features of Libre Office Calc Spreadsheet is the capability to create charts and graphs of the data. They are visual representations of numerical data, which has atleast two-dimensional relationship. The graph has at least two axes-x and y. X axis is usually horizontal while Yaxis is vertical.

Types of Graphs and Charts
Libre Office Calc provides various types of chart to help us to display data in different ways as per the need of the viewers. The major types available are given below:

1. Column chart:
It is the most commonly used chart type. It shows a bar chart or bar graph with vertical bars. The X axis shows the categories and Y axis shows the value for each category. Column chart are used to compare values across categories.

2. Bar chart:
This type of chart shows a bar graph or column chart with horizontal bar. The Y axis shows categories and the X axis shows the value for each category. It is suitable for comparing multiple values.

3. Line chart:
A line chart shows values in the Y axis and categories in X axis. The Y values of each data series is connected by a line. Line chart shows data changes for a certain period of time.

4. Pie chart:
A pie chart displays the contribution of each value to a total. It represents multiple subgroup of a single variable. It contains only one data series. A pie chart shows values as circular sectors of the total circle. Pie chart may be

• Normal Pie chart
• Exploded Pie chart
• Doughnut chart or Donut chart
• Exploded Doughnut chart

5. Area chart:
The chart shows values as points on the Y axis. The X axis shows categories. The Y values of each data series are connected by a line. The area between each two lines is filled with a colour.

6. Scatter chart:
Scatter chart is also known as XY chart. In this type of chart, both axes display values. This chart is used to show the relationship among two variables.

7. Radar chart:
It is also known as Net chart or Star chart. A radar chart has a separate axis for each category and the axes extend outward from the center of the chart. The value of each data point is plotted on the corresponding axis.

Basic steps for Graphs /Charts/Diagrams using Libre Office Calc
The steps involved in the chart preparation is given below:

• Step 1: Data entry (in spread sheet, in the form of a table)
• Step 2: Data selection (select data for plotting chart)
• Step 3: Plotting the chart (Standard tool bar → Insert → chart wizard)
• Step 4: Chart type (choose a chart type from the list)

Elements of a Chart

Advantages in using Graph/Chart

1. It summarises a large data set in visual form.
2. Charts or graphs can clarify trends better than do tables.
3. It helps to estimate key values at a glance.
4. It shows each data category in a frequency distribution.
5. It permits a visual check of the accuracy and reasonableness of calculations.
6. The charts and graphs allow the investigator to draw a valid conclusion.

Students can Download Chapter 3 Use of Spread Sheet in Business Application Notes, Plus Two Accountancy Notes helps you to revise the complete Kerala State Syllabus and score more marks in your examinations.

Kerala Plus Two Accountancy Notes Chapter 3 Use of Spread Sheet in Business Application

Business Applications
The following accounting applications are done with the help of spreadsheet

• Payroll Accounting
• Asset Management
• Loan Repayment

Pay Roll Accounting
Payroll is a statement prepared to show the detailed salary calculation of employees. It contains Basic
Pay, Dearness Allowance, Travelling Allowance, Provident Fund Contribution, ESI Premium, etc. The computation of salary payment is based on the number of days an employee has worked, rate per grade, rate of allowances and deductions to be made therefrom.

1. Preparation of Salary Bill:
The preparation of salary bill should provide for the following:

• Maintaining payroll related data such as Employee No., Name, attendance, Basic Pay, DA, and other allowances, deductions to be made, etc.
• Periodic Payroll Computations: It includes the calculation of various earnings and deductions.
• Preparation of salary statement and employee’s salary slip.
• Generation of advice to bank: It contains the net salary to be transferred to individual bank account of employees and other salary related statutory payments such as provident fund, tax, etc.

2. Pav Roll Components:
The following elements are important for salary computation and its payment.

Earnings:

• Basic Pay (BP): It is the pay in the pay scale
• Grade Pay (GP): It is the pay to be added to the basic pay according to the designation.
• Dearness Pay (DP): Portion of dearness allowance merged with Basic Pay
• Dearness allowance (DA): Compensation for erosion in the purchasing power of wage earner due to Price rise.
• House Rent Allowance (HRA): An amount paid as rent of residential accommodation.
• Transport Allowance (TA): It is an amount to faclitate commuting to the palace of work.

Deductions:

• Professional TAX: Statutory deduction levied by State Government.
• Provident Fund (PF): It is a statutory deduction under provident Fund Act. It is deducted from salary as a part of social security.
• Tax Deducted at Source (TDs)- Monthly deduction towards income Tax liability of an employee.
• Recovery of Loan instalment: An amount deducted on account of any loan taken up by employee.

3. Net Salary Calculation:
Step 1: Calculate Gross salary by using the given formula.

Step 2: Calculate Total Deduction by using the following formula.
Total Deduction = Professional Tax + Provident Fund + Tax deducted at source + Loan Recovery + Any other deductions.

Step 3: Calculate net salary by the given formula
Net Salary = Gross salary – Total Deduction.

Asset Accounting
Assets are resources of the organisation. Assets which are used in the business for more than one year, called Fixed assets. The value of Fixed assets may be reduced due to depreciation. The gradual and permanent diminution in the value of assets due to wear and tear is called depreciation.

The depreciation on fixed assets is provided to recognise the cost of the asset consumed during an accounting period since the life of such assets extends beyond single accounting year.
Total amount of Depreciation – Acquisition cost – Salvage value.

1. Methods of calculation of depreciation:

• Straight Line Method (SLM)
• Written Down Value Method (WDV)

Straight Line Method
Under this method a fixed amount is deducted from the value of an asset year after year on account of depreciation and debited to profit and loss account. This method is also called Fixed Instalment method, or Original Cost method. Under this method value of asset will be reduced to zero.
Depreciation = \(\frac{\text { cost of the asset }-\text { Scrap Value }}{\text { Life of the asset }}\)

1. Cost of the asset/Acquisition cost = Purchase Price + Other expenses directly related with the asset (ie., carriage inward, freight, installation, renewal or reparis, Pre operating expenses).

2. Scap value / salvage value: It is the value of an asset which is realisable at the end of its useful life. Salvage value is the estimated residual value of depreciable asset or property at the end of its economical or useful life.

3. The depreciation under straight line method is computed by using the built in LibreOffice calc function SLN.

Written Down Value Method (WDV)
This method is also known as Diminishing balance method or Reducing balance method. Under this method, a fixed percentage is written off every year on the book value of the asset at the beginning of the year. Here the amount of depreciation goes on decreasing and there fore, the book value of asset will not become zero after its working life.
Amount of depreciation = Written Down Value of asset × Rate of depreciation

1. This method is also called Declining Balance (DB) method and uses the LibreOffice Calc function DB to compute the depreciation.

Loan Repayment Schedule
Loan is a sum of borrowed money for a specified period at a pre-specified rate of interest. The loan is repaid through a number of periodic repayment instalments over the loan repayment period.

LibreOffice Calc function PMT is used to calculate loan repayment schedule. The parameters of the function PMT are as follows.

Students can Download Chapter 2 Spread Sheet Notes, Plus Two Accountancy Notes helps you to revise the complete Kerala State Syllabus and score more marks in your examinations.

Kerala Plus Two Accountancy Notes Chapter 2 Spread Sheet

Spreadsheet – Meaning
Spreadsheet application is a computer program that allows to record, calculate and compare numerical or financial data. Using a spreadsheet program. We can store a lot of data in the worksheet and also arrange and analyse the data by using different functions and formulae for meaningful object.

It is used to establish relationship between two or more sets of data. Libre Office Calc, MS Office Excel, Open Office Spreadsheet etc. are examples of spreadsheet software.

Features of Spreadsheet

1. A spreadsheet is a configuration of rows and columns.
2. A spreadsheet is also known as worksheet.
3. Spreadsheet application allows to enter (ie., add) and process data.
4. It makes quick and easy financial and numerical analysis of data.
5. The data stored in the spreadsheet can be converted into graphs, charts etc.
6. It can be used to store, arrange and filter data.

Introduction to Libre Office Calc
Libre Office Calc is one of the best spreadsheet applications, which can be used to calculate, analyse and manage data. It is a Free and Open Source Software (FOSS). Libre Office Calc is available for a variety of platforms including Linux, OSX, Microsoft windows and Free BSD.
1. Features of Libre Office Calc:

• It is a spreadsheet application.
• It is both free software and open source software.
• It can be used to calculate, analyse and manage data.
• The worksheet in Libre Office Calc contains Rows and Columns in a Table format.

Basic concept of Libre Office Calc Spread Sheet
(a) Workbook:
A file in spreadsheet is known as a workbook. A work book is a collection of a number of work sheets.

(b) Work sheets:
The work area which consists of rows and columns in a spreadsheet is called worksheet. By default three worksheets – sheet-1, sheet-2, sheet-3 are available in work book.

(c) Active work sheet:
The worksheet which is available to the user for carrying out operations is called active work sheet. The name of the active worksheet will be shown in bold letters in the Sheet Tab at bottom of work sheet.

(d) Sheet Tab:
It shows the name of the work sheet at bottom left of the screen. Additional sheets can be added by clicking on the right hand side of the sheet Tab.

(e) Rename:
The Sheet-1, Sheet-2, and Sheet-3 etc. can be renamed by right clicking the mouse over the worksheet and selecting “Rename” option.

(f) Rows:
Rows are the horizontal vectors in the worksheet. These are numbered numerically
from Top to Bottom.

(g) Columns:
Columns are vertical vectors in the worksheet. These are referred by alpha characters from left to right such as A, B, C, …, AA, AB, AC …etc.

(h) Cell:
The intersection of a row and a column is called a cell. A cell is identified by a combination of an alpha – numeric character eg: A1, B6, C10, etc. This alpha numeric character is called cell address. Hence each cell has a unique address.

(i) Active cell:
When we start Libre Office Calc, the pointer (cursor) points the first cell ie., A1, and this cell is called the active cell. The active cell is distinguished by a dark box called cell pointer. We can move the cell pointer by using the arrow keys UP, DOWN, LEFT and RIGHT.

(j) Cell Reference:
A cell reference identifies location of a cell or group of selected cells in spreadsheet.

(i) Types of Cell References:
1. Relative Cell references:
By default cell reference is relative; which means that as a formula or function is copied and pasted to other cells, the cell references in the formula or function change to reflect the new location.

2. Absolute Cell reference:
The absolute cell reference consist of the column letter and row number surrounded by dollar ($) signs. Eg $A$5. An absolute cell reference is used when we want a cell reference to stay fixed on a specific cell.

3. Mixed cell reference:
It is a combination of relative and absolute cell references that holds either row or column constant when the formula or function is copied to another location.

(k) Inserting Rows and Columns:
We can add or delete Rows and Columns in a spreadsheet. To add column, click at the column header (right click on the mouse) there we get an option to add column.

Likewise, we can add rows. To delete the column, click the column header (right click on the mouse) there we get an option to delete column. Likewise we can remove rows.

(l) Ranges:
Range is a group of adjacent cells that forms a rectangular area. A range is specified by giving the address for first cell in range and the last cell in the rage, eg: range starting from A10 to A20 is written asA10:A20 where colon (:) is the range operator.

(m) Spreadsheet navigation:
We can move around a worksheet through four arrow keys. ie;

• Left arrow key
• Right arrow key
• Up arrow key
• Down arrow key

The mouse can also be used for navigation in spreadsheet except data entry. Some common operations/navigations are listed below:

(n) Labels:
A descriptive information for rows or columns in the form of a text, or a special character is called Label.
Eg: Name, Roll No, Address

(o) Formula:
The formula means a mathematical calculation on a set of cells. The formula must start with an = (equal to) sign. When a cell contains a formula, it often contains reference to other cells.
Eg: = Basic pay + DA + HRA.

Spreadsheet Functions
Functions:
A function is a special key word which can be entered into a cell in order to perform and process the data which is appended within brackets. There is a function button f(x) on the formula bar.

When we click on it, function offers assistance through Function Wizard. Alternatively we can enter the function directly into the formula bar. A function is a build in set of formulae which starts with an ‘equal to sign’ (=).
Eg =SUM (), =AVERAGE (), =COUNT() etc.
There are twelve different categories of functions available in LibreOffice Calc. Important functions are

• Date and Time Function
• Financial Function
• Logical Function
• Mathematical Function
• Look Up and Reference Function
• Statistical Function

Date and Time Functions
These functions are used for inserting, editing and manipulating date and time. “Date” must be entered with quotation marks. Libre Office Calc internally handleds a date/time value as a numerical value. The most commonly used Date and Time Functions are:

1. TODAY ():
It is the function for today’s date in the worksheet. This helps to update the date value when we reopen the spreadsheet or modify the values of the document.
Syntax: =TODAY()

2. NOW ():
It is the function for today’s date and present time. This helps to update the date and the time value when the cell value is modified.
Syntax: =NOW()

3. YEAR ():
It helps to update the year for the given date value
Syntax: = YEAR (Date value) or = YEAR (“Date”)

4. MONTH ():
It helps to update the month for the given date value. The month is recorded as an integer between 1 and 12.
Syntax: =MONTH(“date”) or =MONTH (date value)

5. DAY ():
It helps to update the day of the given date value. The day is recorded as an integer between 1 and 31
Syntax: = DAY(“Date”) or = DAY (date value)

6. DATEVALUE ():
This function converts the given date into the corresponding date number. By default 31/12/1899 has the value as 1.
Syntax: =DATEVALUE (“Date”)

7. DATE ():
This function calculates a date specified by year, month, day and displays it in the cell’s formatting.
Syntax: = DATE (Year; Month; Day)

Mathematical Functions
The following mathematical functions are very useful in business applications.

1. SUM ():
This function adds all the numbers in a range of cells. The AutoSum (S) button can also be used directly for summation of values from cells.
Syntax:=SUM (number 1, number 2,…….)

2. SUM IF ():
It returns the sum of the cells as per a given criteria.
Syntax: = SUMIF (range, “criteria”, sum_range) where

• Range is the range of cells to evaluate.
• Criteria defines which cells will be added.
• Sum_range, is the range from which values are summed.

3. ROUND ():
This function rounds a number to specified number of digits or decimal places.
Syntax: = ROUND (Number, Count)

4. ROUNDUP():
Rounds a number up, away from zero without considering the value next to the rounding digit.
Syntax: = ROUNDUP (Number, Count)

5. ROUNDDOWN ( ):
Rounds a number down towards zero without considering the value next to the rounding digit.
Syntax: = ROUNDDOWN (Number, Count)

Statistical Functions
Statistical function operates on a set of data and give summarised results. Libreoffice Calc provides a number of statistical functions. They are

1. COUNT ():
This function used to count the number of cells in a range contains numbers only.
Syntax :=COUNT(Value 1, Value2,…)
or := COUNT (Range)

2. COUNTA():
This function used to count the number of ceils In a range contains any value. It will count number, text, time, date, logical values, error code, etc. In other words, this function counts the number of cells that are not empty in a range.
Syntax: = COUNTA (Value 1, Value 2….) or = COUNTA (Range)

3. COUNTBLANK():
This function in LibreOffice Calc count the number of empty cells in the given range. It is the opposite function of COUNTA.
Syntax: =COUNTBLANK (Range)

4. COUNTIF():
This function counts the number of cells within a given range that meet the criteria or condition.
Syntax: COUNTIF (Range, Criteria)

5. ROWS():
This function returns the number of rows in a reference or array.
Syntax = ROWS (Array)
Where Array is the reference or named area whose total number of rows is to be determihed.

6. Columns ():
This function returns the number of columns in an array or reference
Syntax: = COLUMNS (Array)
Where Array is the reference to a cell range whose total number of columns is to be found.

Text Manipulation Function
There are two types of Text Manipulation Functions in LibreOffice Calc. They are

1. TEXT ( ):
This function converts a number or I numerical value into text according to a defined format.
Syntax: TEXT (Number, Format)
This function is useful in situations to display numbers in a more readable format.

2. CONCATENATE ( ):
This function is used to combines several text strings in different cells into one string.
Syntax:=CONCATENATE (“Text 1 ”, “…..”,“ Text n”)

Logical Functions
Logical functions are used for comparison and checking a test condition. The major logical functions are IF, AND and OR.

1. IF ():
This function is used to test a condition. When the condition is TRUE, then first action is taken. When it is FALSE, then the second action is taken.
Syntax: IF (Test, Then value, Otherwise value)

2. NESTED IF ()
Libre Office Calc allows to include one function inside another function. It is called nesting of functions. The IF function can be nested, when you have multiple condition to meet.
Syntax: =IF (Test_1, Then value_1, If (Test_2, Then value _2, If (_____)))

3. AND ():
This function gives only a TRUE or FALSE answer.
Syntax: =AND (Logical value 1, Logical value 2,…)

4. OR ():
Returns TRUE if atleast one argument is TRUE. This function returns the value FALSE, If all the arguments have the logical value FALSE
Syntax: = OR (Logical value 1, Logical value 2,…)

Spreadsheet Reference Functions
The important spreadsheet reference functions are

1. LOOKUP () functions:
The LOOKUP function returns a value either from a one-row or one-column range or from an array. The lookup function has two syntax forms: Vector form and Array form.

The vector form of LOOUP looks in a one-row or one-column range (known as a vector) for a value, and then returns a value from the same position in a second one-row or one-column range.

The array form of LOOKUP looks in the first row or column of an array for the specified value, and then returns a value from the same position in the last row or column of the array.

• LOOKUP (Vector from)
  Syntax: =LOOKUP (search criterion, Search vector, Result vector)
• LOOKUP (Array form)
  =(LOOKUP (lookup_value, array)

2. VLOOK UP ():
VLOOK UP is the vertical LOOKUP function. Use VLOOK UP to search the first column (columns are vertical) of a block of data and return the value from another column in the same row.
Syntax: = VLOOKUP (Search criterion; Array; Index; Sort Order)

3. HLOOKUP ( ):
It is the Horizontal LOOKUP function, searches for a value in the first row of a table array and returns the corresponding value in ‘ the same column from another row of the same table array.
Syntax: -HLOOKUP (search criteria; index; sorted)

Financial Functions
The major financial functions of LibreOffice Calc are discussed below:

1. ACCRINT ():
This function returns the accrued interest for a security that pays periodic interest.
Syntax:= ACCRINT(Issue, First_nterest, settlement, rate, Par, frequency, basis, calc_method)

2. CUMIPMT():
This function returns the cumulative interest paid between two periods.
Syntax: = CUMIPMT (Rate, NPER, PV, S, E, Type)

3. PV():
This function is used to calculate the amount of money needed to be invested at a fixed rate today, to receive a specific amount, over a specified number of periods.
Syntax: = PV (Rate, NPER, Pmt, FV, Type)

4. PMT ():
PMT function calculates the equal periodic payment for an annuity with constant interest rates.
Syntax: = PMT (Rate, NPER, PV, FV, Type)

5. FV():
This function calculates the future value of an investment based on periodic, constant payment and a constant interest rate.
Syntax: = FV (RATE, NPER, PMT, PV, TYPE)

6. RATE ():
This function returns the interest rate per period of an annuity.
Syntax: = RATE (NPER, PMT, PV, FV, Type, Guess)

7. NPV ():
This function calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values)
Syntax:- NPV(Rate, Value 1, Value 2,….)

Data Entry, Text Management, and Cell Formatting
In any computerised business application, the basic requirement is to input data, which may be either for processing or to update various data elements. In both the cases, data should be correct, accurate and should be in proper format.

Data Entry
Libreoffice Calc facilitates fast data entry. Some of the methods for data entry are mentioned below:

1. The data fill options:
The ‘fill’ command can be used to fill data into worksheet cells. To fill quickly in several types of data series, we select cells and drag the fill handle (A fill handle is the small black square in the lower-right corner of the selection). When we point to the fill handle, the pointer changes to + symbol.

2. Import/Copy Data from other sources:
This method will transfer data into required cells by copying or importing an external file to calc sheet.

3. Data validation:
This is a feature to define restrictions on type of data entered into a cell. We can configure data validation rules for cells data that will not allow users to enter invalid data. There may be warning messages when users tries to type wrong data in the cell.

Data Formatting
Formatting of spread sheets makes easier to read and understand the important information. On the Ribbon there are several tools and shortcuts to format spreadsheets effectively. Some of them are explained below.

1. Number formatting:
Number formatting includes adding percent symbols (%), commas(,), decimal places (.), and currency signs (?) date (dd/mm/yyyy), time (HH: mm), scientific values and as well as some special formats to a spreadsheet.

2. Currency:
If we enter a financial value with the currency sign, calc assigns a currency format to the cell along with the entry.

3. Dates:
If we enter the date, that follows one of the built-In Calc number formats, such as 01/06/2018 or 01 June 2018, the program assigns a Date format that follows the pattern of the date.

4. Changing cell colours:
Select the range to format. From the Toolbar, select format and click on cells to display format cells dialog box and choose background tab. The back ground tab provide Background colourforthe cell. Select the desired colour from the colour pallets. Then click OK to make necessary changes.

5. Create a custom cell border:
We can create a cell style that includes a custom border, and can apply to that cell style when we want to display the custom border around selected cells.

• Select the properties option on the side bar (Right)
• Click cell appearance
• We can modify cell border properties

6. Conditional formatting:
We can apply conditional formatting to a cell range, a table, or a Pivot Table report. A conditional format changes the appearance of a cell range based on a condition or criteria. If the condition is true, the cell range is formatted based on that condition; if the condition is false, the cell range is not formatted based on that condition.

Changing The Alignment Of Data In Cells
There are several options to change the alignment of text (Data) in cells.

1. Text Formatting:
We can format the text in the cell in the following way.

• The font style can be changed
• The font size can be changed
• If we need bold letters, Italics, or underlined, choose from appropriate field.
• The back ground colour can be changed
• The text alignment in the cell either is left, right, center or justify
• The position in the cell ie., Top, Center or Bottom of the cell can be selected.
• The borders to the cell can choose from the appropriate field.
• Different types of borders can be given in the field.

All these actions are also given in property windows appears on the right of the work sheet.

2. Merging a range of cells:
Merged cells are’ a single cell that is created by combining two or more selected cells. When two or more adjacent horizontal or vertical cells are merged, the cells become one large cell and displayed across multiple columns or rows. The contents of cell appear in the centre of the merged cell.

3. Split a merged cell:
Select the merged cell which we want to split into two. When we select a merged cell, the merge and centre button also appears selected in the Alignment group on the Home Tab. Click merge and centre.

4. Formatting a table:
There are predefined table styles or quick styles that we can use to quickly, format a table. Select cells where the Table hasto be inserted. Then select the format option in the menu bar, in the dropdown menu select the auto format. We can select the predefined table format.

5. Headers and Footers:
In calc spreadsheet, headers and footers are lines of text that are printed at the top (Header) and bottom (footer) of each page in the menu bar. They contain descriptive text such as titles, dates, and / or page numbers and are used to add information to a printed spreadsheet. These options are available under insert option in the menu bar.

Output Reports
We can print entire or partial worksheets and work books, one at a time, or several at once.

1. Page set Up:
We can customize our output Report by editing the page set up option.

2. Print Out:
Before Printing, we have to verify print preview which gives an idea about how the print out may come. The modification can be done by this verification.

3. Defining the Print:
By default, Libre Office Calc prints all data on the current worksheet. The print area can be customized/defined by using Dialog Box option.

4. Preparation of reports using Pivot Tables:
A Pivot Table is a way to present information in a report format. A PivotTable report provides enhanced layout, attractive and formatted report with improved readability. There are two types of data table.

• One-Variable Data Table
• Two-Variable Data Table

The one variable Data Table allows us to identify a single decision variable in our model and see how changing the values for that variable affect the values calculated by one or more formulas in our model.

The two variable Data table allows us to Specify two decision variables and a variety of inputs and only a single formula.

Common Error Codes (Messages) in Libre Office Calc
Libre Office Calc provides some messages for errors of miscalculation, incorrect use of functions, invalid cell references, and values, and other user initiated mistakes. Some of the error codes/messages are given below.