Spreadsheet Basics

Spreadsheet Basics SPREADSHEETS. Definition of a Spreadsheet. A Spreadsheet is a ledger sheet that lets the user enter, edit, and manipulate numerical data. A Spreadsheet usually consists of a series of rows & columns in which data entries can be made. Types of Spreadsheets: There are 2 types of spreadsheets: 1). Manual spreadsheet: A Manual spreadsheet is ledger book with many sheets of papers divided into rows and columns for entering/writing data. The data is entered manually using a pen or pencil. 2). Electronic Spreadsheet: A computer program that looks like the manual ledger sheet with rows & columns for entering data that can be manipulated mathematically using of formulae. Advantages of Electronic Spreadsheets over Manual Worksheets. An electronic spreadsheet: 1. Has a large worksheet for data entry & manipulation as compared to manual worksheet. 2. Has inbuilt formulae called Functions that are non-existent in manual worksheets. These functions enable the user to quickly manipulate mathematical data. 3. Uses the power of the computer to quickly carry out operations. 4. Has better formatting & editing qualities than the manual worksheet. 5. Utilizes the large storage space available on computer storage devices to save & retrieve documents. 6. Can easily be modified in its form, while a manual spreadsheet involves a lot of manual calculations & are very difficult to amend. 7. The user can very quickly & efficiently perform complicated computations using the information stored in an electronic spreadsheet. 8. It is accurate in its calculations & allows automatic recalculation on formulae. I.e., when one value/figure is changed, the result of the formula is automatically adjusted by the computer so as to correspond with the different input. For a manual spreadsheet, changing one value means rubbing the result & writing the correct one again. 9. It offers graphical representation of data leading to comprehensive decisions. 10. Replaces the pencil & paper approach of the manual operations of the worksheet. I.e., it enables the user to produce neat work because; all the work is edited on the screen and a final copy is printed. With a manual spreadsheet, neatness & legibility of the work depends on the writer’s hand-writing skills. 11. It improves on the capabilities & speed of the Calculator. 1 Spreadsheet Basics Examples of the commonly used Spreadsheet packages VisiCalc – this was the 1st type of spreadsheet to be developed for PCs. Lotus 123 – this is an integrated software with spreadsheet module, graphs, and database. Microsoft Excel. Corel Quattro-Pro Microsoft Works Excel Super calculators. Multiplan. Vp-Planner. Review Questions. 1. 2. 3. Define a Spreadsheet. Differentiate between the traditional analysis ledger sheet and an electronic spreadsheet. Name three commonly spreadsheet packages. COMPONENTS OF A SPREADSHEET A spreadsheet has 3 main components, namely; (a). Workbook. (b). Database. (c). Graphs / Charts. Workbook: When working in any spreadsheet program, you use workbook files to hold your information.  A Workbook is a file in a spreadsheet package that contains one or more worksheets. The worksheets are made up of rows & columns in which you work and store your data. A Workbook allows the user to organize various kinds of related information in a single file. Database: Spreadsheet programs such as Excel have special features, which can be used to manage data values entered in the cells of the spreadsheet. These features, which are found on the Data menu, were incorporated in Excel but they belong to Database Management System software. Examples of such features include: Filtering of records, use of Forms, calculating of Subtotals, data validation, Pivot tables and Pivot chart reports. Example: If related data values are entered on the same row, they form a Record. Hence, a worksheet can be manipulated as a database that has data records entered in it. Graphs/Charts: A Chart is a graphical/pictorial representation of data in a worksheet. Charts are used to summarize data in a worksheet in a pictorial form. They enable the users to present complex data elements from a worksheet in a simple format that they can understand. Charts make it easy for users to see comparisons, patterns, and trends in data, e.g., instead of having to analyse several columns of a worksheet, one can see at a glance whether sales are falling or rising. Examples of charts are: Pie charts, Line graphs, Bar charts, Histograms, Column charts, etc. 2 Spreadsheet Basics Review Questions. 1. 2. Name and describe the three components of a spreadsheet. Explain the following terms as used in spreadsheets. i) Workbook. ii) Chart. Application areas of spreadsheets (Areas where Spreadsheets are used) 1. Accounting. Spreadsheets provide an easy & streamlined means of financial management. They are mostly used by Accountants to record their daily transactions & also keep financial records. For example; a spreadsheet can be used to do the following:  Record sales & purchases.  Calculate profits.  Produce Invoices, and also compile financial statements.  Prepare budgets.  Assist the management of an organization to monitor the current state of payments from customers in relation to goods delivered.  Detect aged debtors (i.e., those people who have owed you money for more than the period allowed in your terms of business.  Track the value of assets over time (i.e., Appreciation and Depreciation). Note. Most spreadsheet programs come with inbuilt functions such as SUM, AVERAGE, PRODUCT, etc, which enable the Accountant to carry out his/her daily accounting tasks easily. 2. Data management. A spreadsheet enables information to be produced easily and kept up-to-date. For example;  It enables the user to create, edit, save, retrieve and print worksheet data & records.  It enables data to be arranged neatly in tabular structure.  Related data can be typed on the same worksheet. If the data is on different worksheets, the worksheets can be linked so that the data can be accessed easily. Some of the data management functions include:  Sorting (i.e., arranging worksheet records in a particular order so as to easily access the data items).  Filtering (i.e., displaying only the records that meet a given condition).  Use of Forms to enter & view records.  Use of Total/Subtotal function. 3. Scientific Applications. Spreadsheet programs can be used by Scientists & Researchers to compile & analyse their results. 4. Statistical analysis / Mathematical operations. Spreadsheets provide a set of data analysis tools that can be used to develop complex statistical analyses. In addition, some of the tools generate charts. Examples of statistical functions include:  AVERAGE – used to calculate the mean of a set of values.  MEDIAN – used to give the value in the middle of a set of values. Such mathematical operations can be used by: 3 Spreadsheet Basics - Teachers to compile their students’ marks and produce results. - Clerks & Secretaries to enable them easily create tables of figures and manipulate them quickly as required. 5. Forecasting (What if analysis). The automatic recalculation feature makes it possible to use the “What if” analysis technique. What if analysis is a feature in a spreadsheet that is used to find out the effect of changing certain values in a worksheet on other cells. It involves changing the value of one of the arguments in a formula in order to see the difference the change would make on the result of the calculation. This method can be used for financial forecasting, budgeting, cost analysis, etc. Review Questions. 1. 2. Explain five application areas where spreadsheet software can be used. Explain the concept of “What if” analysis. Common features of Electronic spreadsheets. The following are the typical facilities provided by electronic spreadsheets: 1. 2. 3. Have the ability to create, edit, save & retrieve worksheets. Have inbuilt functions & formulae which can be used to perform calculations. Allows Automatic recalculation, i.e., when you change one value, the rest of the values in the spreadsheet are automatically recalculated by the computer to correspond with the different input. This enables you to play “what if” games with your system. 4. Have the ability to Sort and filter data (i.e., arrange data in a predefined order). 5. Have a Data validation facility, which ensures that the correct data is entered into the Spreadsheet. 6. Have a Chart facility that can be used to draw line graphs, Bar charts, histograms, etc. 7. Have the ability to format data (both text & numeric data) using predefined formats. 8. Some Spreadsheets have a SOLVER facility that is used to uncover the best uses of scarce resources so that desired goals can be achieved. 9. Have the ability to adjust Column widths & Row heights automatically. 10. Have the ability to hide and unhide rows & columns, and also freeze panes. 11. They enable printing of worksheets within the shortest time possible. 12. They have pre-designed Templates for automating tasks. A Template is a document that acts as a blueprint or outline for other documents of the same type. It contains the standard text, graphics & formatting that will be used in all documents of this type. This means that, all formulas and formatting for similar workbooks such as Invoices can be saved as templates and then be used to automate the task without having to create the workbook again. 13. Have the ability to summarize data using Consolidation and Pivot tables. Consolidation allows the merging of several worksheets into a summary sheet, while still keeping the original worksheets intact. Consolidation adds together cells with the same coordinates in the various worksheets. Pivot tables can be used to cross-tabulate large amounts of data. Differences between an Electronic Spreadsheet and a Calculator 4 Spreadsheet Basics An electronic spreadsheet: 1. 2. 3. 4. Has more memory than calculator. Is able to perform complex logical operations, but a calculator cannot. Uses the large storage capacity of the computer that a calculator does not have. Has a large working area that a calculator does not have. Review Questions. 1. 2. 3. Describe any five features of a spreadsheet program. State five features of spreadsheets that are useful in financial modelling. How does a spreadsheet differ from a Calculator? 5