Business Math Quarter 2 Module 8 PDF

Business Math Quarter 2 – Module 8: Analysis and Presentation of Data Using MS Excel/SPSS Business Math – Grade 11 Alternative Delivery Mode Quarter 2 – Module 8: Analysis and Presentation of Data Using MS Excel/SPSS First Edition, 2020 Republic Act 8293, section 176 states that: No cop...

Business Math Quarter 2 – Module 8: Analysis and Presentation of Data Using MS Excel/SPSS Business Math – Grade 11 Alternative Delivery Mode Quarter 2 – Module 8: Analysis and Presentation of Data Using MS Excel/SPSS First Edition, 2020 Republic Act 8293, section 176 states that: No copyright shall subsist in any work of the Government of the Philippines. However, prior approval of the government agency or office wherein the work is created shall be necessary for exploitation of such work for profit. Such agency or office may, among other things, impose as a condition the payment of royalties. Borrowed materials (i.e., songs, stories, poems, pictures, photos, brand names, trademarks, etc.) included in this module are owned by their respective copyright holders. Every effort has been exerted to locate and seek permission to use these materials from their respective copyright owners. The publisher and authors do not represent nor claim ownership over them. Published by the Department of Education Secretary: Leonor Magtolis Briones Undersecretary: Diosdado M. San Antonio SENIOR HS MODULE DEVELOPMENT TEAM Author : Evangeline M. Fechalin Co-Author - Content Editor : Gilda A. Dagan Co-Author - Language Reviewer : Emelita G. Paguio Co-Author - Illustrator : Evangeline M. Fechalin Co-Author - Layout Artist : Jeremy M. Flores Team Leaders: School Head : Emelita G. Paguio LRMDS Coordinator : Frances Therese D. San Juan DIVISION MANAGEMENT TEAM: Schools Division Superintendent : Romeo M. Alip, PhD, CESO V OIC- Asst. Schools Division Superintendent : William Roderick R. Fallorin, CESE Chief Education Supervisor, CID : Milagros M. Peñaflor, PhD Education Program Supervisor, LRMDS : Edgar E. Garcia, MITE Education Program Supervisor, AP/ADM : Romeo M. Layug Education Program Supervisor, Senior HS : Danilo C. Caysido Project Development Officer II, LRMDS : Joan T. Briz Division Librarian II, LRMDS : Rosita P. Serrano Division Book Designer : Jeremy M. Flores Printed in the Philippines by Department of Education – Schools Division of Bataan Office Address: Provincial Capitol Compound, Balanga City, Bataan Telefax: (047) 237-2102 E-mail Address: [email protected]  Business Math Quarter 2 – Module 8: Analysis and Presentation of Data Using MS Excel/SPSS Introductory Message For the facilitator: Welcome to the Business Math – Grade 11 Alternative Delivery Mode (ADM) Module on Analysis and Presentation of Data Using MS Excel/SPSS! This module was collaboratively designed, developed and reviewed by educators both from public and private institutions to assist you, the teacher or facilitator in helping the learners meet the standards set by the K to 12 Curriculum while overcoming their personal, social, and economic constraints in schooling. This learning resource hopes to engage the learners into guided and independent learning activities at their own pace and time. Furthermore, this also aims to help learners acquire the needed 21st century skills while taking into consideration their needs and circumstances. In addition to the material in the main text, you will also see this box in the body of the module: Notes to the Teacher This contains helpful tips or strategies that will help you in guiding the learners. As a facilitator you are expected to orient the learners on how to use this module. You also need to keep track of the learners' progress while allowing them to manage their own learning. Furthermore, you are expected to encourage and assist the learners as they do the tasks included in the module. 1 For the learner: Welcome to the Business Math – Grade 11 Alternative Delivery Mode (ADM) Module on Analysis and Presentation of Data Using MS Excel/SPSS! Business Math is a mathematical tool intended to deliver an utmost understanding about money and information needed to make good financial decisions. It does not only concentrate on the essentials of business finances, such as owning or operating a business, but it also provides an avenue where people in every walk of life learn the significance of managing their personal finances. Getting familiar with the concepts of Business Math leads to making a better decision with your money! This module was designed to provide you with fun and meaningful opportunities for guided and independent learning at your own pace and time. You will be enabled to process the contents of the learning resource while being an active learner. This module has the following parts and corresponding icons: What I Need to Know This will give you an idea of the skills or competencies you are expected to learn in the module. What I Know This part includes an activity that aims to check what you already know about the lesson to take. If you get all the answers correct (100%), you may decide to skip this module. What’s In This is a brief drill or review to help you link the current lesson with the previous one. What’s New In this portion, the new lesson will be introduced to you in various ways such as a story, a song, a poem, a problem opener, an activity or a situation. What is It This section provides a brief discussion of the lesson. This aims to help you discover and understand new concepts and skills. What’s More This comprises activities for independent practice to solidify your understanding and skills of the topic. You may check the answers to the exercises using the Answer Key at the end of the module. What I Have Learned This includes questions or blank sentence/paragraph to be filled into process what you learned from the lesson. 2  What I Can Do This section provides an activity which will help you transfer your new knowledge or skill into real life situations or concerns. Assessment This is a task which aims to evaluate your level of mastery in achieving the learning competency. Additional Activities In this portion, another activity will be given to you to enrich your knowledge or skill of the lesson learned. This also tends retention of learned concepts. Answer Key This contains answers to all activities in the module. At the end of this module you will also find: References This is a list of all sources used in developing this module. The following are some reminders in using this module: 1. Use the module with care. Do not put unnecessary mark/s on any part of the module. Use a separate sheet of paper in answering the exercises. 2. Don’t forget to answer What I Know before moving on to the other activities included in the module. 3. Read the instruction carefully before doing each task. 4. Observe honesty and integrity in doing the tasks and checking your answers. 5. Finish the task at hand before proceeding to the next. 6. Return this module to your teacher/facilitator once you are through with it. If you encounter any difficulty in answering the tasks in this module, do not hesitate to consult your teacher or facilitator. Always bear in mind that you are not alone. We hope that through this material, you will experience meaningful learning and gain deep understanding of the relevant competencies. You can do it! 3  What I Need to Know This module is made to help you establish a better understanding on the use of gross margin on sales. Our goal in this module is to help know how to analyze and interpret data using MS Excel and SPSS. At the end of this module, you are expected to: 1. Analyze and interpret the data presented in a graph/table. (ABM_BM11PAD-IIi-10) 2. Use software (i.e., MS Excel, SPSS) programs to compute and present graphical representation of business data. (ABM_BM11PAD-IIj-11) 4  What I Know DIRECTIONS: On a separate sheet of paper, write the letter that corresponds to your answer to the following questions. 1. What is the most appropriate formula you can put in the C2 cell to calculate a 7% tax? A. =C2*0.07 C. =C2*7% B. =C2*1.07 D. =C2+(C2*0.07) 2. Which of the following is not a term of MS Excel? A. Cells C. Columns B. Rows D. Document 3. Which would you choose to create a bar diagram? A. Edit, Chart C. Insert, Chart B. Tools, Chart D. Format, Chart 4. What do you call the chart that shows the proportions of how one or more data elements relate to another data element? A. XY Chart C. Line Chart B. Pie Chart D. Column Chart 5. In EXCEL, you can sum a large range of data by simply selecting a tool button called _______? A. Auto Fill C. Auto Correct B. Auto Sum D. Auto Format 6. The first step to create a graph is _______. A. Highlight the data C. Insert chart B. Input data D. Click format 7. An Excel formula must begin with what symbol? A. ‘ (single quote mark) C. / (forward slash) B. + (plus sign) D. = (equal sign) 8. The box on the chart that contains the name of each individual record is called the _______. A. Cell C. Axis B. Title D. Legend 5 9. You accidentally erased a record in the sheet. What command can be used to restore it immediately? A. Insert C. Undo B. Copy D. Replace 10. Using the bar graph below, what does the scale on the left beginning with 0 and ending with 7 represent? VEGETABLE SALES from BAHAY-ANI PROJECT per BAG 7 6 5 4 3 2 1 0 SEPT OCT NOV DEC JAN FEB MAR MONTH A. Number of students selling vegetables B. Number of bags of vegetables sold C. Number of vegetables in each bag D. Number of days each month that vegetables was sold 11. Which TWO MONTHS had approximately the same amount of vegetables sold? A. September and February C. October and March B. November and March D. September and December For numbers 12-15, using the pie chart below, answer the following questions. HOW STUDENTS SPEND THEIR TIME EATING 8% SCHOOL TIME HOMEWORK 25% 8% SOCIALIZING SLEEPING 13% 33% WATCHING TV 13% 6 12. Approximately how many hours a day did they spend sleeping? A. 6 hours C. 9 hours B. 8 hours D. 10 hours 13. According to this graph, for every 24 hours, about how many hours are spent socializing and watching TV? A. 4 hours C. 5 hours B. 2 hours D. 6 hours 14. If a student ate ¾ (three-fourths) of their meals away from home, what % of the total day is spent eating other than at home? A. 6% C. 7% B. 4% D. 12% 15. Approximately how many hours a day are spent in school and doing homework? A. 7 hours C. 9 hours B. 8.5 hours D. 8 hours Good job on our pre-assessment! This is just a preview of our lesson. More interesting ideas will be introduced in this module. Keep going! 7  Lesson Analysis and Presentation of 8 Data Using MS Excel/SPSS Graphs show the relationship between two or more sets of ideas. The most common types of graphs you will come across in your reading are line graphs, bar graphs, and pie charts, and this time by using MS Excel/SPSS. What’s In Tables and graphs are visual representations of data used to organize information to show patterns and relationships. More specifically, in research context, the first stage in the process of making evidence-based decisions is to collect accurate data and to describe, summarize, and present it for analysis. What’s New DIRECTIONS: Miss Martinez wants to know which type of video conferencing platforms she should use in her online discussions. The results are presented in the table below. Can you help her know the percentage of each result? Fill in the boxes. FB Messenger Google Meet Zoom Lark Skype Room 4 5 6 1 4 CHOICE OF VIDEO CONFERENCING PLATFORMS FB Messenger Room Skype Lark Google Meet Zoom If you answer and fill in the percentage correctly, then you are good to go! 8  What is It Doing pie charts, or bar graphs manually can be tedious, especially when you are making reports in finances or businesses. That is why it is easy to do these in Microsoft Excel or other software such as SPSS (Statistical Package for the Social Sciences or Statistical Product and Service Solutions) but we will focus on MS Excel since this is user-friendly than that of SPSS. To create charts and graphs, input your Data > Select the data by highlighting the data. Click Insert > Insert Column or Bar Chart icon and select a column chart option of your choice. (For Column Charts and Bar Graphs) Now you are done! You can customize your chart to apply a different chart layout, click Design > Charts Layout, and select a layout. ▪ To apply a different chart style, click Design > Chart Styles, and pick a style. ▪ To apply a different shape style, click Format > Shape Styles, and pick a style. 9  A bar graph is useful for looking at a set of data and making comparisons. For example, it’s easier to see which items are taking the largest chunk of sales by glancing at the chart. Point out that Company A sales are steadily increasing from year to year, from Php100,000 in 2011 to slightly over Php200,000 in 2015. In creating circle or pie chart, input your Data > Select the data by highlighting the data. Click Insert > Insert Pie or Doughnut Chart > Select 2D Pie Right click the pie chart and click Format Data Labels. Check Category Name, uncheck Value, check Percentage and click Center. 10  Calculate the total frequency. In this case the total frequency is 20 respondents. Next, calculate the percentage of the total for each piece of data by dividing each one by the total frequency. Note: Check that the sum of the ‘Frequency ÷ Total Frequency’ column equals 1. What’s More DIRECTIONS: Using MS Excel, create a bar graph and a pie chart; screenshot it or print screen it. Then, paste it on another sheet of paper. Use the data below. Mrs. Fech wants to know which type of modality of learning her home facilitators want this coming school year. She surveyed 40 people and below are the results. Help her present the data in a nice and presentable manner using a bar graph and a pie chart. TYPE OF MODALITY OF LEARNING RESPONDENTS Face-To-Face Learning 13 Modular Learning 11 Online Distance Learning 3 Blended Learning 8 Home Schooling 5 11  What I Have Learned Now, let me check if you can still recall the discussion on analysis, interpreting the data presented in a graph/table and using MS Excel in creating graphs/table and charts. DIRECTIONS: On a separate sheet of paper, answer the questions below. 1. In your own words, where do we use BAR GRAPH? PIE CHART? 2. What are the steps to follow in creating graphs in MS Excel? 3. In using pie charts, how will you determine the percentage in every response? What I Can Do DIRECTIONS: Read and analyze the problem below. On a separate sheet of paper, write your answers to the questions. ARE YOU UPDATED? As of October 13, 2020, the COVID cases in the municipalities of Bataan are as follows. Using MS Excel, create a bar graph focusing on the comparison of Confirmed Cases and Recovered, and answer the questions below. 1. What is the relationship of Confirmed, Recovered Cases to Active Cases? 2. Which of the following Municipality has practiced IATF protocols strictly? Graph Source: https://datastudio.google.com/u/0/reporting/daa2c0d5-00e0-4a1c- 909a-1ad595e3b056/page/90hMB?s=tHc7YeLFLJA 12  Assessment DIRECTIONS: On a separate sheet of paper, write the letter that corresponds to your answer. Please do not write anything on the module. 1. Which type of graph represents size relationship between parts and the whole? A. Bar Graph C. Line Graph B. Scatter Plot D. Pie Chart 2. What is the purpose of a bar graph? A. Represents size relationship between parts and the whole of the data. B. Shows how one variable is connected to another. C. Visual comparison of two variables. D. Used to display and compare numerical data. COLORS OF NOTEBOOK COVER 7 6 5 4 3 2 1 0 red blue green yellow orange pink 3. The bar graph shows selected colors that 20 students have chosen for their notebook cover. How many of them favored orange more compared to those who favored green? A. 2 C. 4 B. 3 D. 5 13 For numbers 4 and 5, the graph shows the usual ways of transportation to school for the students in a random selection. WAYS OF TRANSPORTATION 60 50 50 43 40 32 30 18 20 12 10 10 0 BUS TAXI BIKE TRAIN CAR WALK 4. How many students are there altogether in the survey? A. 165 C. 166 B. 160 D. 162 5. What is the total of those who responded they prefer to bike and walk? A. 97 C. 75 B. 87 D. 65 For numbers 6 and 7, use the pie chart and answer the following questions. PRINTING OF MODULES OVERALL EXPENSE promotion cost paper cost 10% 25% royalty 15% transportation cost 10% binding 20% printing cost 20% 14 6. The price of the book is marked 20% above the Cost Price. If the marked price of the book is Php180, then what is the cost of the paper used in a single copy of the book? A. Php36.00 C. Php42.00 B. Php37.50 D. Php44.25 7. Royalty on the book is less than the printing cost by _______. A. 5% C. 33 1/5% B. 20% D. 25% 8. If for an edition of the book, the cost of paper is Php56,000.00, then find the promotion cost for this edition. (Hint: Use a ratio of 25:10 = 56,000) A. Php20,000 C. Php22,400 B. Php25,000 D. Php28,125 For numbers 9 to 11, the choices are the following: A. Format C. Design B. Shape D. Text Styles 9. Once you decide on a graph format, you can use the _______ section near the top of the Excel window to select a different template, change the colors used, or change the graph type entirely. 10. To have percentages on your pie chart using MS Excel, you should right click the pie chart and click _______. Check Category Name, uncheck Value, check Percentage and click Center. 11. To apply a different chart layout, click Design > Charts Layout, and select a _______. For numbers 12 to 13, use the graph below. Total Expenditure of a Company 25 20 20 20 17.5 15 15 12.5 10 10 5 5 0 Infrastracture transportation advertisement taxes salary research and interest on development loans 15 12. The total amount of expenditures of the company is how many times of expenditure on research and development? A. 27 C. 20 B. 18 D. 17 13. The total expense to infrastructure, advertisement, and salary covers how many percent? A. 40% C. 45% B. 50% D. 55% 14. To insert this type of chart in Excel, which of the following options must you choose from the Insert Chart group? A. Click on Other Charts and select Doughnut from the menu list. B. Click on Other Charts and select Bubble from the menu list. C. Click on Area and select 3D area. D. Click on Pie and select Pie from the menu. 15. If you want to know the percentages in your data manually, how will you do it? A. Calculate the percentage of the total for each piece of data by dividing each one by the total frequency. B. Calculate the frequency of the total for each piece of data by dividing each one by the total percentage. C. Calculate the percentage of the total for each piece of data by adding each one by the total frequency. D. Calculate the percentage of the total for each piece of data by multiplying each one by the total frequency. 16  Additional Activities DIRECTIONS: Read and analyze the problems below. Write your solutions and answers to the questions on a separate sheet of paper. Please do not write anything on the module. The pie chart below shows the percentage of types of transportation used by 700 students in coming to school. WAYS OF TRANSPORTATION bus 30% bicycle 45% car 10% walking 15% A) How many students in the school come to school by bicycle? B) How many students do not walk to school? C) How many students come to school by bus or in a car? 17 Answer Key What’s More 18  19 Additional Activities 1. 315 2. 595 3. 210+70 = 280 References Lopez, B., Martin-Lundag, L. and Dagal, K., 2016. Business Math (Teacher's Manual). Quezon City: Vibal Group, Inc. “How to Create a Bar Chart in MS Excel”, (TechOnTheNet.com) https://www.techonthenet.com/excel/charts/bar_chart2016.php “Pie Charts”, (Indiabix.com) https://www.indiabix.com/data-interpretation/pie-charts/ “Create a Bar Chart in Excel - Easy Excel Tutorial”, (Excel easy) https://www.excel-easy.com/examples/bar-chart.html “Add a Pie Chart”, (Support.Microsoft.com) https://support.microsoft.com/en-us/office/add-a-pie-chart-1a5f08ae- ba40-46f2-9ed0-ff84873b7863 “COVID Bataan Update”, (datastudio.google.com) https://datastudio.google.com/u/0/reporting/daa2c0d5-00e0-4a1c-909a- 1ad595e3b056/page/90hMB?s=tHc7YeLFLJA Microsoft Office, Microsoft excel 0365 20 For inquiries or feedback, please write or call: Department of Education – Region III, Schools Division of Bataan - Curriculum Implementation Division Learning Resources Management and Development Section (LRMDS) Provincial Capitol Compound, Balanga City, Bataan Telefax: (047) 237-2102 Email Address: [email protected] 21