Plus Two Computerised Accounting Notes Chapter 2 Spread Sheet

Kerala Plus Two Computerised Accounting 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:
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 keyword 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 determined.

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

Plus Two Accountancy Notes