Class 7 > Computer Science for Class 7 > Textbook: Spreadsheet- Data Analysis

Download, print and study this document offline |

Page 1 Arrange the customers in the alphabetic order. 1. Find the top five customers based on their expenditure in the last month, to give them a 2. discount of 8% on their bill. Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. Find out which of the fast selling item, is sold most. (Example: Ice creams are in various sizes. 4. Which size is sold most?) Enter and format data to make it easy to read and print. 5. Print various portions of the spreadsheet. 6. Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and asked us to become his consultants! Moz: What kind of help does he need? Jyoti: He records customers’ information, sales and inventory data in various spreadsheets. He wants us to teach him how to organize and analyze this data. He wants to know how to: Moz: How are you planning to proceed? Tejas: We have taken sample data from the shop owner. Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of formatting to select Font colour and highlight selected columns. Spreadsheet Jyoti: Now, we will explore and learn the features required for the analysis of the data. Moz: Good strategy. Let us start. Spreadsheets are used to enter and analyse numeric data. They can be used to make projections and draw graphs based on data. 1 1 Aim: In this lesson, you will learn: - To Organize the given data in a spreadsheet. - To Calculate percentage, sum, average using functions option in a spreadsheet. - To Represent data in multiple ways. Spreadsheet: Data Analysis 7 Page 2 Arrange the customers in the alphabetic order. 1. Find the top five customers based on their expenditure in the last month, to give them a 2. discount of 8% on their bill. Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. Find out which of the fast selling item, is sold most. (Example: Ice creams are in various sizes. 4. Which size is sold most?) Enter and format data to make it easy to read and print. 5. Print various portions of the spreadsheet. 6. Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and asked us to become his consultants! Moz: What kind of help does he need? Jyoti: He records customers’ information, sales and inventory data in various spreadsheets. He wants us to teach him how to organize and analyze this data. He wants to know how to: Moz: How are you planning to proceed? Tejas: We have taken sample data from the shop owner. Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of formatting to select Font colour and highlight selected columns. Spreadsheet Jyoti: Now, we will explore and learn the features required for the analysis of the data. Moz: Good strategy. Let us start. Spreadsheets are used to enter and analyse numeric data. They can be used to make projections and draw graphs based on data. 1 1 Aim: In this lesson, you will learn: - To Organize the given data in a spreadsheet. - To Calculate percentage, sum, average using functions option in a spreadsheet. - To Represent data in multiple ways. Spreadsheet: Data Analysis 7 Arrange the customers in the alphabetic order. 1. Tejas: Here is the list of frequent customers. Let us sort the list to arrange customers in alphabetic order. Moz: Yes, you can sort both textual and numerical data in spreadsheets. You can also sort in either ascending or descending order. Jyoti: Let us sort customer names in ascending order. Sorting is the process of placing the data in some well defined order. • Sorting can be performed on data in one or more columns. • The order is defined for each column (ascending or descending), and • the sort is performed on the column in the defined order. Sorting Concept Skill Sorting of data Click on the column which needs to be sorted. The first row data is assumed as the 1. header of the column. Now select ‘Data’ from the menu 2. bar, in the drop down list select ‘Sort’. In the displayed window, select the sort order 3. whether ascending or descending. You can sort the same data in three levels. Click OK to sort the data. 4. Page 3 Arrange the customers in the alphabetic order. 1. Find the top five customers based on their expenditure in the last month, to give them a 2. discount of 8% on their bill. Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. Find out which of the fast selling item, is sold most. (Example: Ice creams are in various sizes. 4. Which size is sold most?) Enter and format data to make it easy to read and print. 5. Print various portions of the spreadsheet. 6. Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and asked us to become his consultants! Moz: What kind of help does he need? Jyoti: He records customers’ information, sales and inventory data in various spreadsheets. He wants us to teach him how to organize and analyze this data. He wants to know how to: Moz: How are you planning to proceed? Tejas: We have taken sample data from the shop owner. Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of formatting to select Font colour and highlight selected columns. Spreadsheet Jyoti: Now, we will explore and learn the features required for the analysis of the data. Moz: Good strategy. Let us start. Spreadsheets are used to enter and analyse numeric data. They can be used to make projections and draw graphs based on data. 1 1 Aim: In this lesson, you will learn: - To Organize the given data in a spreadsheet. - To Calculate percentage, sum, average using functions option in a spreadsheet. - To Represent data in multiple ways. Spreadsheet: Data Analysis 7 Arrange the customers in the alphabetic order. 1. Tejas: Here is the list of frequent customers. Let us sort the list to arrange customers in alphabetic order. Moz: Yes, you can sort both textual and numerical data in spreadsheets. You can also sort in either ascending or descending order. Jyoti: Let us sort customer names in ascending order. Sorting is the process of placing the data in some well defined order. • Sorting can be performed on data in one or more columns. • The order is defined for each column (ascending or descending), and • the sort is performed on the column in the defined order. Sorting Concept Skill Sorting of data Click on the column which needs to be sorted. The first row data is assumed as the 1. header of the column. Now select ‘Data’ from the menu 2. bar, in the drop down list select ‘Sort’. In the displayed window, select the sort order 3. whether ascending or descending. You can sort the same data in three levels. Click OK to sort the data. 4. Moz: Good observation. You should not select and sort on a single column when you have multiple columns of data. Instead, select all the columns in the table while sorting. Tejas: Since expenditure is our focus now, let us sort in ascending order of expenditure (in sort criteria tab: see the figure below ). Tejas: If only the Monthly expenditure column is selected and the first column remains unchanged, then the amounts will not match the customer names. Jyoti: We have to extend the selection to customers too. Jyoti: We have to now compute the discount amount for the top 5 customers and also provide the final bill amount for each customer. Tejas: In the next two columns, we can calculate the discount and the final amount payable by the customer. We need to write the formulas for each of these columns. Find the top five customers based on their expenditure of last one month. Then give discount 2. of 8% on their bill. Tejas: We also have data on the monthly expenditure of these customers. Let us enter this data as the next column. Jyoti: We have to find the top 5 customers using the monthly expenditure. We can select the data in the Monthly expenditure column and sort it in the descending order. Page 4 Arrange the customers in the alphabetic order. 1. Find the top five customers based on their expenditure in the last month, to give them a 2. discount of 8% on their bill. Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. Find out which of the fast selling item, is sold most. (Example: Ice creams are in various sizes. 4. Which size is sold most?) Enter and format data to make it easy to read and print. 5. Print various portions of the spreadsheet. 6. Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and asked us to become his consultants! Moz: What kind of help does he need? Jyoti: He records customers’ information, sales and inventory data in various spreadsheets. He wants us to teach him how to organize and analyze this data. He wants to know how to: Moz: How are you planning to proceed? Tejas: We have taken sample data from the shop owner. Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of formatting to select Font colour and highlight selected columns. Spreadsheet Jyoti: Now, we will explore and learn the features required for the analysis of the data. Moz: Good strategy. Let us start. Spreadsheets are used to enter and analyse numeric data. They can be used to make projections and draw graphs based on data. 1 1 Aim: In this lesson, you will learn: - To Organize the given data in a spreadsheet. - To Calculate percentage, sum, average using functions option in a spreadsheet. - To Represent data in multiple ways. Spreadsheet: Data Analysis 7 Arrange the customers in the alphabetic order. 1. Tejas: Here is the list of frequent customers. Let us sort the list to arrange customers in alphabetic order. Moz: Yes, you can sort both textual and numerical data in spreadsheets. You can also sort in either ascending or descending order. Jyoti: Let us sort customer names in ascending order. Sorting is the process of placing the data in some well defined order. • Sorting can be performed on data in one or more columns. • The order is defined for each column (ascending or descending), and • the sort is performed on the column in the defined order. Sorting Concept Skill Sorting of data Click on the column which needs to be sorted. The first row data is assumed as the 1. header of the column. Now select ‘Data’ from the menu 2. bar, in the drop down list select ‘Sort’. In the displayed window, select the sort order 3. whether ascending or descending. You can sort the same data in three levels. Click OK to sort the data. 4. Moz: Good observation. You should not select and sort on a single column when you have multiple columns of data. Instead, select all the columns in the table while sorting. Tejas: Since expenditure is our focus now, let us sort in ascending order of expenditure (in sort criteria tab: see the figure below ). Tejas: If only the Monthly expenditure column is selected and the first column remains unchanged, then the amounts will not match the customer names. Jyoti: We have to extend the selection to customers too. Jyoti: We have to now compute the discount amount for the top 5 customers and also provide the final bill amount for each customer. Tejas: In the next two columns, we can calculate the discount and the final amount payable by the customer. We need to write the formulas for each of these columns. Find the top five customers based on their expenditure of last one month. Then give discount 2. of 8% on their bill. Tejas: We also have data on the monthly expenditure of these customers. Let us enter this data as the next column. Jyoti: We have to find the top 5 customers using the monthly expenditure. We can select the data in the Monthly expenditure column and sort it in the descending order. Sample data of the products, stock and sale: Moz: Suppose the shopkeeper decides to change the discount percentage, all you have to do is change the formula in the cell number: C2 and copy the same formula in the other cells. Alternately, you can also use the handle and drag the formula to the other cells (Illustrated in 3 above.) Tejas: We had earlier used the Sum icon to compute the sum. Is there any other way of doing this? Moz: You can type in the formula in the cell where you want to display the sum. For example, if you want to find the total discount given to the top five customers, type in the formula: =SUM(C2,C3,C4,C5,C6) or =SUM(C2:C6) Jyoti: For each product, we have to compute the percentage of the item sold to the stock of the item. Then compare the data and decide which are the fast selling products. Moz: Correct. Jyoti: I have also seen other built in Functions in Spreadsheets like Average (AVE), Maximum (MAX), Minimum (MIN), etc. MAX(D2:D6) will give us the maximum amount spent by any customer. Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. 1. Calculate discount for one customer 3. Drag handle to repeat calculation for other customer. 2. Calculate final amount for one customer. 4. Final amounts for each customer. Page 5 Arrange the customers in the alphabetic order. 1. Find the top five customers based on their expenditure in the last month, to give them a 2. discount of 8% on their bill. Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. Find out which of the fast selling item, is sold most. (Example: Ice creams are in various sizes. 4. Which size is sold most?) Enter and format data to make it easy to read and print. 5. Print various portions of the spreadsheet. 6. Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and asked us to become his consultants! Moz: What kind of help does he need? Jyoti: He records customers’ information, sales and inventory data in various spreadsheets. He wants us to teach him how to organize and analyze this data. He wants to know how to: Moz: How are you planning to proceed? Tejas: We have taken sample data from the shop owner. Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of formatting to select Font colour and highlight selected columns. Spreadsheet Jyoti: Now, we will explore and learn the features required for the analysis of the data. Moz: Good strategy. Let us start. Spreadsheets are used to enter and analyse numeric data. They can be used to make projections and draw graphs based on data. 1 1 Aim: In this lesson, you will learn: - To Organize the given data in a spreadsheet. - To Calculate percentage, sum, average using functions option in a spreadsheet. - To Represent data in multiple ways. Spreadsheet: Data Analysis 7 Arrange the customers in the alphabetic order. 1. Tejas: Here is the list of frequent customers. Let us sort the list to arrange customers in alphabetic order. Moz: Yes, you can sort both textual and numerical data in spreadsheets. You can also sort in either ascending or descending order. Jyoti: Let us sort customer names in ascending order. Sorting is the process of placing the data in some well defined order. • Sorting can be performed on data in one or more columns. • The order is defined for each column (ascending or descending), and • the sort is performed on the column in the defined order. Sorting Concept Skill Sorting of data Click on the column which needs to be sorted. The first row data is assumed as the 1. header of the column. Now select ‘Data’ from the menu 2. bar, in the drop down list select ‘Sort’. In the displayed window, select the sort order 3. whether ascending or descending. You can sort the same data in three levels. Click OK to sort the data. 4. Moz: Good observation. You should not select and sort on a single column when you have multiple columns of data. Instead, select all the columns in the table while sorting. Tejas: Since expenditure is our focus now, let us sort in ascending order of expenditure (in sort criteria tab: see the figure below ). Tejas: If only the Monthly expenditure column is selected and the first column remains unchanged, then the amounts will not match the customer names. Jyoti: We have to extend the selection to customers too. Jyoti: We have to now compute the discount amount for the top 5 customers and also provide the final bill amount for each customer. Tejas: In the next two columns, we can calculate the discount and the final amount payable by the customer. We need to write the formulas for each of these columns. Find the top five customers based on their expenditure of last one month. Then give discount 2. of 8% on their bill. Tejas: We also have data on the monthly expenditure of these customers. Let us enter this data as the next column. Jyoti: We have to find the top 5 customers using the monthly expenditure. We can select the data in the Monthly expenditure column and sort it in the descending order. Sample data of the products, stock and sale: Moz: Suppose the shopkeeper decides to change the discount percentage, all you have to do is change the formula in the cell number: C2 and copy the same formula in the other cells. Alternately, you can also use the handle and drag the formula to the other cells (Illustrated in 3 above.) Tejas: We had earlier used the Sum icon to compute the sum. Is there any other way of doing this? Moz: You can type in the formula in the cell where you want to display the sum. For example, if you want to find the total discount given to the top five customers, type in the formula: =SUM(C2,C3,C4,C5,C6) or =SUM(C2:C6) Jyoti: For each product, we have to compute the percentage of the item sold to the stock of the item. Then compare the data and decide which are the fast selling products. Moz: Correct. Jyoti: I have also seen other built in Functions in Spreadsheets like Average (AVE), Maximum (MAX), Minimum (MIN), etc. MAX(D2:D6) will give us the maximum amount spent by any customer. Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. 1. Calculate discount for one customer 3. Drag handle to repeat calculation for other customer. 2. Calculate final amount for one customer. 4. Final amounts for each customer. Tejas: We can provide a chart which can give a clear idea about the fast moving items. We will make a bar chart which shows the stock and sale and another chart that shows the percentage sold, for easy comparison. Multiple representations Jyoti: By looking at this graph the shopkeeper will get an idea about how much of each product he should stock. Moz: Good idea. Tejas: The stock and sale comparison clearly shows that gap is wider for soft drinks. Moz: Which product is of highest demand? Jyoti: Ice creams, because the percentage sold is highest and the stock is also highest. Moz: Right. Now see which type of ice creams are in most demand. The shop keeper can then decide how much to stock of each ice cream. Stock and sale of products Percentage of stock sold Tejas: We can again apply the knowledge of calculating the percentage sold for different types of ice cream. Jyoti: Family pack ice cream is sold least while the cone ice creams and cup ice creams are sold the most. This means there is little demand for family pack but high demand for cup and cone ice creams. Moz: Very well interpreted. Now show me how you formatted the tables to make them look so neat. For the fast selling item, find out the kind and size that is sold most? (Example: Ice creams 4. are in various sizes. Which size is sold most?) Information can be represented in lists, tables, mind map, graphs or charts. Choose a form of representation that matches the nature of information and purpose of use. 1 1Read More

Download as PDF