Spreadsheets PDF

programs. (3mks)  Saving of documents for future reference  Printing of multiple documents after editing  formatting of documents into required form  Mailing features such as mail merging of standard document with another saved file....

programs. (3mks)  Saving of documents for future reference  Printing of multiple documents after editing  formatting of documents into required form  Mailing features such as mail merging of standard document with another saved file. For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/ Spreadsheet Specific Objectives By the end of the topic, the learner should be able to: a) Define a spreadsheet; b) Describe the components of a spreadsheet; c) State the application areas of a spreadsheet; d) Create and edit a worksheet; e) Explain different cell data types; f) Apply cell referencing; g) Apply functions and formulae; h) Apply worksheet formatting; i) Apply data management skills; j ) Apply charting and graphing skills; k) Print worksheet and graph. Content a.) Definition of a Spreadsheet b.) Components of a spreadsheet i. Worksheet ii. Database iii. graphs c.) Application areas of a spreadsheet Statistical analysis Accounting Data management  Forecasting (what i f analysis) Scientific application d.) Creating a worksheet/workbook Getting started Worksheet layout Running the program i. Creating a worksheet ii. Editing a cell entity iii. Saving iv. Retrieving v. Closing a worksheet vi. Exiting from spreadsheet e.) Cell Data Types Labels Values For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/ Formulae Functions f.) Cell referencing Cell addressing Absolute referencing Relative referencing g.) Basic functions and formulae Functions i. Statistical (average, count, max, min) ii. logical (if, count-if sum-if) iii. mathematical (sum, product, div) Arithmetic formulae (using operators +,-,/,*, h.) Worksheet formatting Text Numbers Rows and columns Global i.) Data management Sorting Filtering Total/subtotals function Forms j.) Charts/graphs Types Data ranges Labels Headings and titles Legends k.) Printing  i. page set-up ii. print preview iii. print options select printer selection worksheet/workbook orientation pages and copies v) Printing Introduction Definition For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/ An essentially large sheet that lets you enter, edit and manipulate numerical data. Types of Spreadsheet Manual Electronic Examples of Spreadsheet Lotus 123 VisiCalc MS-Excel VP Planner Advantages of Electronic over manual spreadsheet 1. It utilizes powerful aspects of the computer like: speed, accuracy and efficiency to enable the user accomplish the task. 2. It offers a large virtual sheet for data entry and manipulation. 3. It utilizes large storage space on computer storage devices to store and retrieve documents. 4. Enables the user to produce neat work because traditional paper, pencil, rubber and calculator are not required 5. Has a better document formatting capabilities. 6. Has inbuilt formulas called functions that enables the user to quickly manipulate mathematical data. 7. Automatically adjusts the results of a formula if the data in the worksheet is changed- Automatic recalculation. Components of a Spreadsheet 1. Worksheet: where data is entered. It consists of cells, columns and rows. 2. Database: Does the actual management of data e.g. filtering records, using forms, calculating subtotals, data validation, pivot tables, pivot charts and reports. 3. Graph and charts: Pictorial representation of the base data in a worksheet. Types of charts Line, bar, column, pie, scattered, histogram, Legend: A key that explain what each colour or pattern of data representation in a chart means. Application Areas of Spreadsheet 1. Statistical analysis 2. Accounting: Recording daily transactions and keeping of financial records. Also in; 3. Track the value of assets overtime (Depreciation and Appreciation). 4. Calculate profits 5. Prepare budgets. 6. Data management: Sorting, filtering data, calculating subtotals, and using forms. 7. Forecasting/ What if analysis: Changing values of a cell or argument in a formula to see the For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/ difference the change would make to the calculation results. Worksheet Layout Cell: Intersection between a row and column. Rows: Horizontal arrangement of cells. Columns: Vertical arrangement of cells. Range: A group of rectangular cells that can be manipulated as a block. Cell Data Types 1. Label: Any text or alphanumeric characters entered in a cell. 2. Values: Numbers that can be manipulated automatically. 3. Formula: Designed mathematical expression that create a relationship cell and return a value in a chosen cell. 4. Functions: Inbuilt predefined formula that the user can quickly use instead of creating new one each time a calculation has to be carried out. Cell Referencing Identifies a cell or range of cells on the worksheet and shows MS-Excel where to look for the values or data needed to be used in a formula. 1. Relative cell referencing: The formula keeps on changing automatically depending on the position on the worksheet. 2. Absolute: Cell reference that is always referring to a cell in a specified location of the worksheet even if they are copied from one cell to another. number. A1 style R1C1 B2 R2C2 C2 R10C3 E20 R20C5 Data Management 1. Sorting 2. Filtering data: Quick and efficient method of finding and working with a subset of data in a list. 3. Auto filter: Uses simple criteria and include filter by selection 4. Advanced filter: Uses more complex criteria. 5. Subtotal 6. Total function 7. Forms: Special prepared templates that the user can use to make data entry fast. For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/ Basic Functions and Formulas Statistical Functions 1. Average: Returns the average or mean of a certain formula which can be numbers or array. 2. Count: Counts the number of cells that contain values. 3. Max: Returns the largest value in a set of values. 4. Min: Returns the smallest value in a set of values. 5. Mode: Returns the most frequent occurring value in a set of values. 6. Rank: Returns the rank of a number in a list by comparing its size in relation to the others. For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/ Logical Functions 1. If: Returns a specified value if a condition is executed and found to be true and another value if it is false. 2. Count if: Counts a number of cells within a specified range that meet the given condition of criteria. Example A10:E10 contain eggs, in cell 6, 5 and 2 =COUNTIF (A10:E10,”eggs”) will return 3. 3. Sum If: Adds values in the specified cells by giving a condition or criteria. Example A10:E10 contain 10, 50, 60, 30, 70 sum all values greater than 50 =SUMIF (A10:E10,”50”) returns 180. Mathematical Functions 1. Sum: Adds values in a range of cells as specified and returns the result in a specified cell. 2. Product: Multiplies values in a range of cells as specifies and returns result in a specified cell. For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/ Order of execution  End of topic Did you understand everything? If not ask a teacher, friends or anybody and make sure you understand before going to sleep! Solved KCSE Questions on the topic What is electronic spreadsheet software? (2mks)  A computer program that looks like the manual ledger sheet with rows & columns for entering data that can be manipulated mathematically using formulae. 2. Give any two application programs classified as spreadsheets. (2mks)  Microsoft Excel  Lotus 123 For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/  -Corel Quattro Pro  -super calculators  -Multiplan  -VP-Planner  -VisiCalc 3. Differentiate between the traditional analysis ledger sheet and an electronic spreadsheet. (5mks) An electronic spreadsheet; - Has a large worksheet for data entry & manipulation as compared to manual worksheet - Has inbuilt formulae (called functions) that are non-existent in manual worksheets - Uses the power of the computer to quickly carry out operations - Has better document formatting & editing qualities than a manual worksheet - Utilizes the large storage space available on computer storage devices to save & retrieve documents. - Can easily be modified, while manual spreadsheets involve a lot of manual calculations & are very difficult to amend - The user can very quickly & efficiently perform complicated computations using the information stored in an electronic spreadsheet. - Enables the user to produce neat work - Offers graphical representation of data leading to comprehensive decisions. - It is accurate in its calculations & allows automatic recalculation on formulae. For a manual worksheet, changing one value means rubbing the result & writing the correct one again. 4. Explain five application areas where spreadsheet software can be used. (5mks) i) Accounting: - spreadsheet software can be used by accountants to record their daily transactions & also keep financial records, e.g. they can record sales & purchases, produce invoices, compile financial statements, calculate profits, prepare budgets, etc. ii) Data management: - a spreadsheet enables data & information to be arranged neatly in tables, produced easily and also kept up-to-date, e.g. one can edit, save , sort, filter, use forms to enter and view records, and worksheet data. iii) Scientific applications: - spreadsheets can be used by scientists & researchers to compile and analyze their results. iv) Statistical analysis: - spreadsheets provide a set of statistical Functions/tools that can be used to develop complex statistical or engineering analyses, e.g., teachers can compile their students’ marks and produce results. v) Forecasting: - using the ‘what if’ analysis technique, spreadsheets can be used to find out the effect of changing certain values in a worksheet on the other cells. This helps in financial forecasting, budgeting, etc. 5. State five features of spreadsheets that are useful in financial modeling. (5mks)  Have inbuilt functions & formulae which can be used to perform most For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/ mathematical statistical, trigonometric or financial calculations  Allows automatic recalculation on formulae  #have ability to perform ‘what if’ analysis, which can be used to find out the effect of changing certain values in a worksheet on the other cells  Have the ability to sort & filter data  Have a data validation facility, which ensures that the correct data is entered into the spreadsheet.  Have a chart facility that can be used to draw Line graphs, Bar charts, Pie charts, Histograms, etc.  Some spreadsheets have a SOLVER facility that is used to uncover the best uses of scarce resources so that desired goals such as profit can be maximized, or undesired goals such as cost can be minimized.  They enabled printing of entire worksheets, portions of a worksheet or several worksheets within the shortest time possible  Have the ability to summarize data using Consolidation and Pivot tables. 6. Define the following terms as used in a worksheet: (3mks) i) Columns These are the fields that make up the worksheet of a spreadsheet, and are identified by letters. They run vertically from top downwards ii) Rows These are the records that form a worksheet, and are identifies by numbers. They run horizontally from left to right. iii) Cell A box formed when a row & a column intersect in a worksheet where the data is entered 7. Explain the following concepts as used in spreadsheets: (4mks) i) Automatic recalculation This is whereby an electronic spreadsheet will adjust the result of a formula automatically when the values are changed, so that they correspond with the different input. ii) ‘What if’ analysis ‘What if’ analysis is a feature in spreadsheets that is used to find out the effect of changing certain values in a worksheet on the other cells. It involves changing the values of one of the arguments in a formula in order to see the difference the change would make on the result of the calculation. iii) Sheet tabs These are names of the worksheets that appear at the bottom of the worksheet window. They enable the user to move from one worksheet to another. iv) Worksheet A page (single sheet) stored in a workbook 8. Name four data types used in spreadsheets (2mks)  Labels  Values For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/  Functions  Formulae 9. An Agrovet Company wants to analyze its employee’s personal details using Microsoft Excel. The company has five employees: Mary Anne, Francis Kihara, Lena Achieng, Hellen Wanjiku, and mwangi Peter. Their ages are 20, 45, 90, 45 and 34 years respectively. Mary department; Mwangi Peter in finance department and Hellen Wanjiku in Computer department Mary Anne and Hellen Wanjiku are single whereas Francis Kihara, Lena Achieng and Mwangi Peter are married. Construct a worksheet showing the above information. Use appropriate column headlings (6mks) A B C D 1 AGROVET COMPANY 2 NAMES AGE DEPARTMENT STATUS 3 Mary Anne 23 Research Single 4 Francis 45 Personnel Married Kihara 5 Lena Achieng 90 Research Married 6 Hellen 45 Computer Single Wanjiku 7 Mwangi Peter 34 Computer Married 8 10. a) What is a cell reference? (1mk) A cell reference is the identity of a cell in a worksheet. A cell is identified by use of the column letter and the row number headings. b) For each of the following, state the type of cell reference. (4 mks) i) A5 Relative reference ii) $F$5 Absolute row reference (only the row reference is absolute) For free KCSE Notes, Exams, and Past Papers Visit https://Teacher.co.ke/ iii) H$21 Absolute column reference (only the column reference is absolute) Database Specific Objectives By the end of the topic, the learner should be able to: a) Define a database; b) Explain the concepts of a database, c) Explain data organization in a database; d) Create a database; e) Edit a database; f) Design a form; g) Apply basic concepts of queries; h) Create report and labels; i) Print queries, forms and reports. Content a.) Definition of Database b.) Database concepts Traditional filing methods (manual flat files) Functions of databases Types of database models Database software Features of a database (e.g. data structures, report generating, querry language,modules) c.) Data Organization