excel Homework Help

1. Consider an auto wash shop with 2 machines. There are 6 spaces available for the cars to wait. Any car that arrives and finds the shop full, leaves the shop. The arrival rate is exponential with a rate of 2 cars per hour. The service time of a repairman is normally distributed with a mean of 10 minutes and a standard deviation of 2 minutes. The number of waiting spaces can be changed by the management. Identify range of the following variables and the parameters for this system: .State variables . Decision variables .System parameters

2. A warehouse distribution system employs 10 trucks to customer locations. Three trucks can be loaded at the same time in the warehouse. Loading time is exponential with a mean of 1 hour. Travel time to the customers is also exponential with a mean of 1.5 hours. Unloading time is exponential with a mean of 1.2 hours. Return time is also exponential with a mean of 1.4 hours. Loading and unloading times can be changed by adding more workers. Determine the followings: ● State variables ● Decision variables ● System parameters.

Task 4 Tourists seeking a luxury tour on the "Palace on rail' in India can opt for either the 'Luxur service or the 'Maharajah service. The manager of the Palace on rail' has produced the following table to show the bookings she received last season: Tourist's home country France Germany Holland England Type of Booking Maharajah Service 13 29 24 8 Luxury Service 44 36 UNIVERSITY SCHOOL OF BUSINES AND TECHNOLOGY 19 27

Tourists seeking a luxury tour on the Palace on rail in India can opt for either the Luxury service or the Maharajah service. The manager of the Palace on rail has produced the following table to show the bookings she received last season: | Type of Booking Tourist's home Maharaja Germany Holland 13 29 24 Luxury Service 44 36 19 27 The data is also provided in the "MBF Summative data.xx" file under the "Task 4-Data"

Assignment 2 The 2021 Intel Form 10-K can be found at the following Web site SEC.gov. Use 2021 Financial Statements. Be precise on your calculations. When you are finished with this assignment you will complete the quiz on D2L title Assignment 2 to record your answers. The questions will be presented as multiple choice or true/false. You will have only 30 minutes to enter your answers to D2L which means you need to complete this assignment before recording your answers. Assume I do not know anything about these metrics. Answer the following questions so that a non-financial minded person can understand: 1. Compute the following KPIs for just 2021. i) CURRENT RATIO ii) QUICK RATIO iii) DEBT / EQUITY RATIO 2. Compare your results calculated in item (1) to the semi-conductor industry listed below. Explain your results in terms of your comparisons. How does Intel compare and what does that mean for Intel's financial position related to its peers? Financial ratio Solvency Radios Debt-to-equity ratio Interest coverage ratio Liquidity Ration Current Ratio Quick Ratio Cash Rafo 2020 0.67 4.70 3.32 2:41 1.61 3. Explain why the current ratio for the semi-conductor industry would be higher than the overall market average of 1.9? (Hint: think about the components of the current ratio for a semi-conductor company versus other types of companies) 4. Prepare a common size balance sheet for 2021 and 2020. Identify any significant trends between the two years. Provide a possible explanation for the identified trends.

Part #1: Download and Work with the Data Open the data in Excel and Explore the Data Using excel on your location computer, apply what you learned in class to clean and aggregate the data into more useful and meaningful information. Tips: Excel is a very powerful tool that can be a bit overwhelming. For this assignment, make use of column filters, SUM, AVERAGE, and SUBTOTALS functions. 1. In Excel, put your cursor focus on the first cell for the "Student List' table. 2. In the "Data" tab, click the "Filter" button. 3. After clicking the "Filter" button, you should see the header row change to now contain arrows to indicate dropdown filters. Full Student List with Attendance and Midterm Grades Student (Last Name, First Nam Attendan Mid Term Gra Lee, Eli 24 Brown, Jaxon Young, Kimiko n.a 26 13 14.2 13.7 Late Submissi The syntax for the SUBTOTAL function in Excel is- By using the dropdown filters, you can gain more insight into the data by isolating a single collection of data. Section TTh 4-6 PM Online TTh 4-6 PM 4) To find the Average Attendance for a specific section, first add a SUBTOTAL row to the bottom of the table. SUBTOTAL(function_num,ref1,ref2,...) The sub functions that can be performed by declaring the "function_num" are as follows --

At the bottom of the table, add the equation for SUBTOTAL with the AVERAGE sub function and select all data in the Attendance column. Collins, Willow Scott, Hunter Harris, David Totals 22 26 22 -SUBTOTAL(1,CS:C64) 14.2 14 14.9 Based on the section you filter the table with, your SUBTOTAL will automatically update with the average attendance for that section. 5) For ease of understanding, update the precision for the SUBTOTAL cells to 2 decimal places. Right click on the cell, select "Format Cell". In the dialog box select Category as "Number" and Decimal Places as "2" - IMF 1-3 PM MF 1-3 PM MF 1-3 PM 6) Some students in the MF and TTh sections have not attended any classes and they have a NULL or Non-Number value listed in the attendance column. NULL and Non-Number values will not be calculated in the SUBTOTAL Average. You must include those students in your averages to get the correct average, so to make sure they are included into your SUBTOTAL, enter 0 into those cells. 14.9 This is an important data cleansing step. 7) Perform the same techniques in "5)" and "6)" to clean and calculate the Average Midterm Score for each section. 8) To calculate the Total Late Midterms, use the SUBTOTAL function with a sub function of COUNTA MF 1- -SUBTOTAL(3,ES:E64) 9) Start filling out the template table. The final column to be calculated is the Average Midterm Grade (%). To calculate the percentage, divide the average score for each section with the total possible score and select the column category type as "Percentage". The total possible score is 10. The category type can be selected from the same "Format Cells" dialog window as detailed in *5)".

1. (15) (a) APEX Machine Tool and Die has 5 key departments, A through E, and the departments are arranged as shown in the diagram below. Develop a classic "From-To" distance chart for the layout shown. Assume that the each department tooling is located at the "X" in each of the department areas. i. ii. iii. Product 20 1 2 3 4 A E x 65" Routing A-B-C-D-E A-D-E A-B-C-E A-C-D-E B X 100 feet- 25 X 10' (b) There are 4 products produced at the APEX facility. Routing and forecasting information is shown in the chart below. Batch Size 50 17 50 50 50 X 25' OP 50 10' 100 feet Production Forecast (Units/week) 200 900 400 650 Create a "From-To" Chart that shows the number of material handling trips/week between departments. Given that the cost to transport items is $2 per foot, create a "From-To" chart showing material handling cost/week between departments. Create an AR for APEX based on the material handling cost from the chart developed in ii above. Use the standard A,E,I,O,U for AR charts

Assignment: Grade Analysis As Dr. Dae Jeong's teaching assistant, you maintain the gradebook for the ACC 200 Financial Accounting class. Dr. Jeong wants you to create a pie chart that shows the percentage of students who earn each letter grade. You will also create a bar chart to show a sample of the students' test scores. Furthermore, Dr. Jeong wants to see if a correlation exists between attendance and students' final grades; therefore, you will create a scatter chart depicting each student's percentage of attendance with their respective final grade average. 1. Open 603m2Grades and save it as e03m2Grades_LastFirst. 2. Use the Insert tab to create a pie chart from the Final Grade Distribution data located below the student data in the range F35-G39 and move the pie chart to its own sheet named Distribution. 3. Customize the pie chart with these specifications: Apply the Style 12 chart style • Type ACC 200 Final Grades: Fall 2024 for the chart title. • Explode the A grade slice by 7%. Change the F grade slice to Dark Red • Remove the legend. Add the Alt Text The pie chart shows the percentage of the class that earned each letter grade. Most students earned B and C grades (including the period) 4. Add data labels and customize the labels with these specifications: Display these data labels: Percentage and Category Name in the Inside End position. Remove other data labels. Change the font size to 18 and apply Black, Text 1 font color 5. Create a clustered bar chart using the ranges AS:DS and A18:D23 in the Grades worksheet and move the bar chart to its own sheet named Sample Scores 6. Customize the bar chart with these specifications: Apply the Style 5 chart style./nfmu.blackboard.com GY 4. Add data labels and customize the labels with these specifications: • Display these data labels: Percentage and Category Name in the Inside End position. Remove other data labels. - Change the font size to 18 and apply Black, Text 1 font color. a 8-Fall 8. Add the Alt Text The scatter chart shows the relationship of each student's final gratie and their attendance record. (including the period). 9. Apply these label settings to the scatter chart: • Type Final Average-Attendance Relationship for the chart title and bold the title. • Add the following primary horizontal axis title: Percentage of Attendance. • Add the following primary vertical axis tite: Student Final Averages. a 5. Create a clustered bar chart using the ranges AS:DS and A18:023 in the Grades worksheet and move the bar chart to its own sheet named Sample Scores. 6. Customize the bar chart with these specifications: Apply the Style 5 chart style. Type Sample Student Test Scores for the chart title. Format the plot area with the Light Gradient-Accent 2 fill color. • Position the legend on the right side and change the font size to 11. Format the Final Exam data series with Blue-Gray, Text 2 fill color. Add data labels in the Outside End position. • Select the category axis and display the categories in reverse order in the Axis Options category in the Format Axis task pane so that O'Hair is listed at the top and Sager is listed at the bottom of the bar chart. 10. Select the vertical axis. Change the Minimum Bound to 40, the Maximum Bound to 100, and select Number format with zero decimal places. 11. Select the horizontal axis. Change the Minimum Bound to 40 and the Maximum Bound to 100. 12. Apply the Parchment texture fill to the plot area. Q • Select the category axis and change the font size to 12. • Add the Alt Text The bar chart shows test scores for six students in the middle of the list. Quinn earned the highest scores on all tests. (including the period). 7. Create a scatter chart using the range ES:F31 in the Grades worksheet, the attendance record and final averages from the Grades worksheet. Cut the chart and paste it in cell A42 Adjust the width to 5.5" and the height to 5.96". 13. Add a linear trendline chart element. 14. Insert Column sparklines in the range H6:H31 using the range B6:D31 as the data source. Display the low points. Format the sparklines axis by using the same minimum value for all sparklines and the same maximum value for all sparklines Change the row height to 22 for rows 6 through 31. 15. Save and close the file. Exit Excel. Based on your instructor's directions, submit: e03m2Grades LastFirst in this location. + 69 G

Part I: Excel (60 marks) Steps to Perform: Step 1 2 3 5 6 7 8 Instructions Start Excel. Download and open the file named grades_excel_for_al_xlsx. Rename the given worksheet as grades_Winter2022. Enter a heading for this document titled 'Student Grades for CIS1201 Winter 2022 in G2; merge G2 with cells H2, 12, J2, K2, and L2, such that the heading appears in the center of G2:L2. Increase its font to 16, change the font to Arial, and bold it. Note that A5:A104 are empty. Use the Fill Series feature to enter student ids beginning with 1111111 for A5, increase by 1 such that A104 gets 1111210. Note that the grades stored in the given sheet have an inconsistent format. Format all grades in the range B5:S104 with two decimal places and apply bold and center. The entire range of values should look the same after this step. Add a new column between Chapter11 and Final Exam and enter its description as Textbook assessment. This column will store the average grade a student achieved in the textbook chapter activities. Note that there are 11 chapters (columns H to R) - you must use a function that drops the lowest grade and uses the top 10 grades from these 11 columns to find the average grade and store it in Textbook assessment column. Format all grades in this column with two decimal places. The average must be in the percentage number format. Note that columns C and D store student's lab exam I and lab exam II grades. The max grade for these 2 columns is 10 but the instructor would like all grades to be stored in percentage (out of 100). Enter 10 in column C2; Use the copy-and-paste-special method to update the existing grades in columns C and D to be stored out of a max of 100 now (e.g. 9 should be updated 90). Reapply any formatting if it is removed, so the entire table is consistent. Create column U and call it 'Overall Grade'. Use a formula as given below to calculate the overall grade in a percentage number format (same as Step 6): Overall Grade = 10% of Lab Attendance (Column B) + 10% of Lab Exam I (Column C) + 10% of Lab Exam II (Column D) + 25% of (Assignment1+Assignment2+Assignment3) + 10% of Textbook Assessment + 35% of Final Exam / 100 Points Possible 0 2 2 2 2 6 5/nStep 9 10 11 12 13 14 15 16 17 Create column V and call it "Letter Grade'. Use a formula as given below to calculate the letter grade: OVERALL GRADE (COLUMN U) >= 80 < 80 AND >= 70 Instructions B с D F LETTER GRADE (COLUMN V) <70 AND >= 60 < 60 AND >= 50 <50 Hint: Use function IF in a nested way. Create a new worksheet and name it Overall statistics_W2022. Using cell referencing, copy the values in columns with Id. Overall Grade and Letter Grade from grades Winter2022 to this worksheet. ABCDE Count 52 23 Create a heading for this worksheet titled "Overall Statistics of Student Grades CIS1201 Winter 2022". This heading must have the same format as the heading from Step 3. F In worksheet Overall statistics_W2022, create a column, name it Grades, and fill it with letters A, B, C, D, and F. Create another column, name it Count and fill it with the total number of students that scored grades A, B, C, D or F. You must get the following count Grades A 11 8 6 Hint: Use function COUNTIF on C5:C104 of this worksheet. Draw a 3D-column graph using Grades as the X-axis and Count as the Y-axis. Title the graph as "Distribution of Grades - CIS1201 Winter2022"; title the X-axis as "Letter grades" and Y- axis as "Total Number of Students". In Rows 106, 107, 108 and 109, use functions to calculate and display the Mean, Median, Max value and Min Value of the overall grades in Column B of worksheet Overall statistics_W2022. Create a third worksheet and name it grades_Sorted_FinalExam. Add columns id, Final Exam, Overall Grade and Letter Grade. Sort this worksheet on the Final Exam grades, in order of largest to smallest. On the grades_Sorted_FinalExam sheet, insert the Draft.png picture on the Sheet Background. This can be downloaded from the assignment page on Courselink. Ensure that the worksheets are in the following order: grades_Winter2022, Overall statistics_W2022, grades_Sorted_FinalExam. Make sure that the workbook is saved as an Excel workbook, not a macro-enabled workbook. Close Excel, and then submit the workbook as directed. Total Points Points Possible 10 3 2 6 6 4 4 2 0 60

1 7 points Copy and paste the data from the spreadsheet of class height data into excel, and use the data analysis tool to answer the questions. Report your answers to two decimal places, and include leading zeros for answers less than 1. For the last blank, enter is or is not. The mean height of students who prefer the pronoun She is type your answer... standard deviation for students who prefer the pronoun She is type your answer... cm. The mean height of students who prefer a pronoun other than She is type your answer.... The standard deviation is type your answer... value for these two groups is type your answer.... cm. The cm. cm. The F-statistic type your answer... test is type your answer... that the variance type your answer... significantly different (type is or is not). The critical F value is .The p-value for the F , which means

In the spreadsheet layout shown, what value would: ActiveCell.Offset(ActiveCell.Offset(0, -1), Cells(1, 3)) refer to? 1 2 3 4 5 A 3 8 -2 9 B 4 -4 11 7 C 1 3 5 2/nHow will the worksheet appear after the MakeArray subroutine is executed? A. C. 12 1 12 2 A A Option Explicit Sub MakeArray () Dim A (2, 2) As Double Dim i As Integer, j As Integer A(1, 1) -3.4 <=-9.7 5.8 4.5 Range ("A1: B2") - A End Sub A(1, 2) A(2, 1) A(2, 2) 3.4 5.8 0 OC 0 B 3 -9.7 4.5 04 3.4 B. 12 D. 1 2 A A 3.4 -9.7 4.5 -9.7 B B 5.8 4.5 5.8 3.4

New Perspectives Excel 365/2021 | Module 1: End of Module Project 1 Natal Biotech GETTING STARTED WITH EXCEL GETTING STARTED 1. 2. O 3. 4. O PROJECT STEPS As the operations manager, you use Excel to maintain project data and schedule facilities usage. You are finalizing the Projects and Scheduling worksheets for the current week. 5. Save the file NP_EX365_2021_EOM1-1_FirstLastName_1.xlsx as NP_EX365_2021_EOM1-1_FirstLastName_2.xlsx Edit the file name by changing "1" to "2". If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. Begin on the Projects worksheet by increasing the font size of the range A2:A3 to 12 point. Enter the values shown in Table 1 into the corresponding cells in the range B8:C11. Table 1: Data for the Range B8:C11 With the file NP_EX365_2021_EOM1-1_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. If cell B6 does not display your name, delete the file and download a new copy. 8 9 1 0 1 1 B Yvonne Byrom Fiona Hernandez Taisha Zygmont Jared Sandor Adjust the width of Column B using AutoFit. In cell E6, enter the following: AFS51EZ24 C 202 1 202 3 202 4 202 4 Select the range E6:E11, and then use the Flash Fill button (in the Editing group on the Home tab) to automatically enter codes into the remaining cells in the range. (Hint: You must use the Flash Fill button to properly receive credit for this step.) Hide Column C. CENGAGE New Perspectives Excel 365/2021 | Module 1: End of Module Project 1 6. 7. 8. 9. 10. 11. Switch to the Scheduling worksheet. In cell A3, change the cell content to Week of February 22, 2024 (instead of Week of February 15, 2024). Cut the contents and formatting of the range D15:E18 and paste them into the range A13: B16. In cell B13, create a formula using the COUNT function to determine the number of projects that have booked facility hours by counting the values in the range C6:C11. Adjust the zoom level of the Scheduling worksheet to 120%. Change the page orientation of the Scheduling worksheet to Portrait. Some weeks you need to track facilities-related information, but that's not necessary this week. Move the Facilities worksheet between the Projects and Scheduling worksheets, then hide the Facilities worksheet. 12. When you hand off your worksheet to the facilities team, another colleague will add information about contractors working on site. To create a placeholder worksheet for this data, insert a new worksheet in the workbook, rename the worksheet Contractors, and if necessary, move the new worksheet after the Scheduling worksheet. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workboo and then exit Excel. Follow the directions on the website to submit your completed project. CENGAGE New Perspectives Excel 365/2021 | Module 1: End of Module Project 1 Final Figure 1: Projects Worksheet Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved. A Natal Biotech 1 2 Current Projects 3 Week of February 15, 2024 4 5 Project Title 6 Algae Fuel Synthesis 51 7 Biomarkers 264 8 Immune Therapy 33 9 Protein Folding 5852 10 Water Reclamation 402 11 Gene Splicing 288 12 13 B Project Lead Ernestine Zellers Hester Alvarado Yvonne Byrom Fiona Hernandez Taisha Zygmont Jared Sandor Documentation Projects Scheduling Final Figure 2: Scheduling Worksheet D Branch White Red Red Informatics Blue Red Contrac... (+) ⠀ CENGAGE E Project Code AFS51EZ24 B264HA23 IT33YB21 PF5852FH23 WR402TZ24 GS288JS24 F A New Perspectives Excel 365/2021 | Module 1: End of Module Project 1 A 1 Natal Biotech 2 Project Schedule 3 Week of February 22, 2024 4 5 Project Code 6 AFS51EZ24 7 B264HA23 8 IT33YB21 9 PF5852FH23 10 WR402TZ24 11 GS288JS24 12 13 Total projects for week 14 Median hours per project 15 Greatest project hours 16 Fewest project hours 17 18 ◄ | Projects Scheduling Contractc ... B Resource Lab 2 Lab 1 Computing 2 Computing 1 Lab 1 Lab 2 ◄ CENGAGE 6 42 168 13 с Hours 48 24 168 13 96 36 D

New Perspectives Excel 365/2021 | Module 3: End of Module Project 1 Barrel Barnard Consulting PERFORM CALCULATIONS WITH FORMULAS AND FUNCTIONS GETTING STARTED 2. 3. 4. PROJECT STEPS 5. O 1. Chanelle Larson is a professional assistant with Barrel Barnard Consulting. Chanelle is responsible for collecting consultant hours for some of Barrel Barnard's projects and processing the payments. She wants to automate some tasks and calculate summary information automatically on the worksheet that consultants use to track their hours. Switch to the Timesheet worksheet. In cell C5, insert the NOW function to record the current date. 6. O 7. Save the file NP_EX365_2021_EOM3-1_FirstLastName_1.xlsx as NP_EX365_2021_EOM3-1_FirstLastName_2.xlsx Edit the file name by changing "1" to "2". If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. With the file NP_EX365_2021_EOM3-1_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. If cell B6 does not display your name, delete the file and download a new copy. Each worksheet covers a period of 20 work days. In cell C4, create a formula using the WORKDAY function to calculate the date 19 days from the start date (cell C3). Use AutoFill to complete the labels in the table of hours. Use the values in the range A9:A10 to extend the week numbering to the range A11:A12. Use the value in cell B8 to autofill the remaining weekday abbreviations in the range C8:F8. Add formulas to complete the table of hours used. In cell B17, create a nested formula with the IF and SUM functions that check if the total number of hours worked in week 1 (cells B9:F9) is equal to 0. If it is, the cell should display nothing (indicated with two quote marks: ""). Otherwise, the cell should display the total number of hours worked in week 1. Copy the formula from cell B17 to fill the range B18: B20. In cell H2, create a formula using the WORKDAY function to calculate the date 5 days after the period end date (cell C4). Create lookup functions to complete the summary section. In cell 16, create a formula using the VLOOKUP function to display the number of hours worked in the selected week. Look up the week number in cell 15 in the range A17:G20, and return the value in the 2nd column. Use absolute references for cell 15 and the range A17:G20. Copy the formula from cell 16 to the range 17:111 and then edit the formula in cell 17 to return the value in the 3rd column, the formula in cell 18 to return the value in the 4th column, the formula in cell 19 to return the value in the 5th column, the formula in cell I10 to return the value in the 6th column, and the formula in cell I11 to return the value in the 7th column. CENGAGE New Perspectives Excel 365/2021 | Module 3: End of Module Project 1 8. 9. Add formulas to complete the balances section. In cell K8, create a formula using the SUM function that calculates the total of the range D17:D20 and subtracts it from the value in cell J8. In cell K9, create a formula using the SUM function that calculates the total of the range E17:E20 and subtracts it from the value in cell 19. 10. In cell K10, create a formula using the SUM function that calculates the total of the range F17:F20, subtracts it from the value in cell J10, adds the total of the range G17:G20, and subtracts 160. 11. Copy the formula in cell J11 to cell K11. 12. In cell J16, create a formula using the AVERAGE function that calculates the average of the range B9:F12. 13. In cell J17, create a formula using the MAX function that returns the maximum value from the range B9:F12. 14. In cell J18, create a formula using the MIN function that returns the minimum value from the range B9:F12. Your workbook should look like the Final Figures on the following pages. The value in cell C5 has been intentionally blurred as it will never be constant. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project. CENGAGE New Perspectives Excel 365/2021 | Module 3: End of Module Project 1 Final Figure 1: Timesheet Worksheet Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 A Week # 1 2 3 4 1 2 Week # Hours Worked 3 B Reporting Period Hours 4 Namel Period start Period end Date completed Mon 8 8 0 9 28.00 40.00 32.00 44.00 2/1/2024 2/28/2024 Tue 8.5 7.5 8 8.5 Holiday Documentation Timesheet Hours Worked Wed 8.00 D + 7.5 9 8.5 8 Hours Used Other Hours Used Vacation E Thu 4 7.5 8.5 9.5 Sick 10.00 F Fri 0 8 7 9 Comp 2.00 G Total Hours For Week 40.00 40.00 40.00 44.00 CENGAGE H Timesheet due 3/6/2024 Summary Week # Worked Holiday Vacation Sick Comp Total I 28.00 0.00 0.00 10.00 2.00 40.00 Average Maximum J Minimum Balances Hours worked per day Period Start 56.00 24.00 6.50 86.50 7.2 9.5 0 Period End 56.00 14.00 8.50 78.50

New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 Tetra Sammander Insurance ANALYZE AND CHART FINANCIAL DATA GETTING STARTED 1. 2. 3. 4. 5. PROJECT STEPS Geneva Wood is the owner of Tetra Sammander Insurance. Geneva is preparing year- end financial data to summarize and visualize the company's position and modeling costs of a new life insurance product that the company is considering offering. She has created charts to illustrate some of her data and is using functions to calculate the future value of insurance policies. 6. O 7. O Save the file NP_EX365_2021_EOM4-2_FirstLastName_1.xlsx as NP_EX365_2021_EOM4-2_FirstLastName_2.xlsx Edit the file name by changing "1" to "2". If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. With the file NP_EX365_2021_EOM4-2_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. If cell B6 does not display your name, delete the file and download a new copy. Switch to the Revenues worksheet. In the range F6:F10, add line sparklines based on the data in the range B6:E10 and then add markers. Apply the Green, Accent 6 sparkline color and the Green, Accent 6, Darker 25% marker color. Apply a Gradient Fill, Green Data Bar conditional formatting rule into the range G6:G10. Geneva wants a clustered column chart representing the total revenue for each insurance type by year. Create a 2-D clustered column chart based on the range A5:E9. Resize and reposition the chart so that the upper-left corner is located within cell H5 and the lower-right corner is located within cell 024. Enter Yearly Revenues by Insurance Type as the chart title, then bold the title, and change the font size to 16 point. Apply a Green, Accent 6 border to the plot area of the chart and change the width of the border to 1.5 point. Apply a Glow Shape Effect to the chart area using 5 point Green, Accent Color 6. In the 2024 Revenues by Insurance Type 3-D pie chart (in the range H26:043), change the legend position to bottom. Add data labels that contain only percentages (not values) and that use the Center position. In the Total Revenues by Year 2021-2024 line chart (in the range A11:G28), change the minimum bound of the vertical axis to 2500000, change the major units of the vertical axis to 500000 change the minor units to 250000, and then add Inside tick marks for the minor units on the vertical axis. Apply a solid fill using the Orange, Accent 2, Lighter 80% fill color to the chart area. Apply the Green, Accent 6, Darker 50% font color to the chart title, and change the font to 16 point bold. Apply the Green, Accent 6 color to the data line, and change the width to 2.25 point. CENGAGE New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 8. 9. In the Insurance Type Contribution to Annual Revenue 2021-2024 stacked column chart (in the range A29:G46), remove the data series labeled Trends from the chart. Apply a Shape Fill to the chart area using the Orange, Accent 2, Lighter 80% fill color. Apply a Solid Line border to the chart area with a White, Background 1, Darker 35% border color. Create a 2-D Column chart based on the range A5:E10. Move the 2-D Column chart to the Revenue Summary worksheet, and then resize and reposition the chart so the upper-left corner is located within cell A5 and the lower-right corner is located within cell K22. Select Switch Row/Column to edit the chart data source. Change the chart type to create a Custom Combination Combo chart. Represent the following data series as a Clustered Column chart: Life, Homeowners, Auto, and Travel, and represent the Total data series as a Line chart using the Secondary Axis. [Mac hint - Keep the main chart a Clustered Column chart, select the Total data series, change the chart type to a Line chart, and plot the series on the Secondary Axis.] 10. Enter Policy Type and Total Yearly Revenue as the chart title. Add axis titles to the chart, enter Revenue by Policy Type as the left vertical axis title, and then enter Total Revenue as the right vertical axis title. [Mac hint - Add Primary Vertical and Secondary Vertical Axis titles.] Delete the horizontal axis title placeholder, if necessary. 11. Switch to the Payments worksheet, select the range A6:B156 and create a Histogram chart. Resize and reposition the chart so that the upper-left corner is located within cell D5 and the lower-right corner is located within cell N23. 12. For the horizontal axis, change the bin width to 250, add an overflow bin with a value of 1500, and change the number format to the Currency number format with decimal places to 0. Enter Distribution of Payout Amount per Policy in 2024 as the title of the chart, and format the chart title using bold. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project. CENGAGE New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 Final Figure 1: Revenues Worksheet Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved. 2 5 6 18 19 20 21 7 8 Auto 9 Travel 10 Total 11 12 13 19 14 15 16 17 31 32 33 34 35 36 37 38 39 42 43 44 Insurance Type 45 46 47 Life $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $- $ B 2021 2022 2023 2024 528,153 $ 828,672 $919,825 $ 670,553 772,046 936,491 1,475,911 1,793,231 1,690,384 1,085,226 1,658,226 1,038,049 23,831 21,185 33,642 24,189 $ 3,014,414 $ 2,871,574 $ 4,087,604 $ 3,526,022 2021 Tetra Sammander Insurance Revenues by Product Area 2021-2024 2021 Total Revenues by Year 2021-2024 2022 2022 2023 Insurance Type Contribution to Annual Revenue 2021-2024 Revenues Revenue Summary Payments + 2023 Life Homeowners Auto Travel Trends 2024 Total $ 2,947,203 4,977,679 5,471,885 102,847 $13,499,614 G 2024 Final Figure 2: Revenue Summary Worksheet H $2,000,000 $1,800,000 $1,600,000 $1,400,000 $1,200,000 $1,000,000 $800,000 $600,000 $400,000 $200,000 $- I J CENGAGE 2021 K Yearly Revenues by Insurance Type 2022 29% L Life Homeowners Auto Travel 1% M 2024 Revenues by Insurance Type 19% 51% 2023 N Life Homeowners Auto Travel 2024 O New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 < Revenue by Policy Type 4 A 4 5 6 ZA10661 7 AC90797 8 XJ12955 9 YK84968 10 WW23072 11 DG41533 12 HN13664 13 QE93359 14 SU88195 15 TS61649 16 HE37809 17 IJ58657 18 IQ60494 19 LX33566 20 1040160 21 RJ28180 22 JA60855 23 NY74376 24 AK48460 B $2,000,000 $1,800,000 $1,600,000 $1,400,000 $1,200,000 $1,000,000 $800,000 $600,000 $400,000 $200,000 $- Policy Number 2021 Final Figure 3: Payments Worksheet Payout $329.38 $0.00 $0.00 $25,000.00 $0.00 $0.00 $0.00 $0.00 $430.44 $771.45 D Tetra Sammander Insurance Revenue Summary 2021-2024 $0.00 $0.00 $735.07 $25,000.00 $0.00 $604.52 $0.00 $0.00 Life Revenues Revenue Summary Payments Policy Type and Total Yearly Revenue D E Tetra Sammander Insurance Payments per Policy (weighted sample) 2024 120 100 E 80 60 40 2022 20 Homeowners 0 [$0, $250] $783.59 Revenues Revenue Summary Payments + F Auto G 2023 H H ⠀ 4 Travel -Total I 2024 J CENGAGE K $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $- L Distribution of Payout Amount per Policy in 2024 ($250, $500] ($500, $750] ($750, $1,000] ($1,000, $1,250]) ($1,250, $1,500] M K Total Revenue > $1,500 N

1 A state driver's license exam center would like to examine its operation for poten- tial improvement. Arriving customers enter the building and take a number to determine their place in line for the written exam which is self administered by one of five elec- tronic testers. The testing times are distributed as EXPO(8); all times are in minutes. Thirteen percent of the customers fail the test (it's a hard test with lots of questions). These customers are given a booklet on the state driving rules for further study and leave the system. The customers who pass the test select one of two photo booths where their picture is taken and the new license is issued. The photo booth times are distributed TRIA(2.5, 3.6, 4.3). The photo booths have separate lines, and the customers tend to en- ter the line with the fewest waiting customers. If there is a tie, they enter the nearest booth, Booth 1. These customers then leave the system proudly clutching their new li- censes. The center is open for arriving customers eight hours a day, although the services are continued for an additional hour to accommodate the remaining customers. The cus- tomer arrival pattern varies over the day and is summarized below: Hour Hour 1 2 3 1 2 3 4 Arrivals per Hour 22 35 40 31 Hour 5 6 7 Run your simulation for ten days keeping statistics on the average number of test fail- ures per day, electronic-tester and photo-booth utilization (utilization for the testing re- source overall, but separate utilizations for each photo booth), average number in queue, and average customer system time for those customers passing the written exam. Arrival Per hour 22 35 40 Hour 5 6 7 Arrivals per Hour 35 43 29 8 22 Arrival per hour 35 43 29 22

New Perspectives Excel 2019 | Module 7: SAM Project 1a Valerian State College SUMMARIZING YOUR DATA WITH PIVOTTABLES sam' GETTING STARTED Open the file NP_EX19_7a_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as NP_EX19_7a_FirstLastName_2.xlsx by changing the "1" to a "2". If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the file NP_EX19_7a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. 2. Lael Masterson works in the Student Activities Office at Valerian State College in Illinois. Lael has started compiling information on students who are interested in helping run student organizations at Valerian State, and she needs your help completing the workbook. Switch to the Student Representatives worksheet. In cell E2, enter a formula using the HLOOKUP function as follows to determine a student's potential base hourly rate (which is based on the number of years of post-secondary education): a. Use a structured reference to look up the value in the Post-Secondary Years column. Retrieve the value in the 2nd row of the table in the range P13:U14, using an absolute reference. Because base hourly rate is tiered based on the number of years of education, find an approximate match. b. Fill the formula into the range E3:E31, if necessary. Student organizations sometimes require transportation for off-campus activities, and school policy requires students to be over 23 years old to serve as transport. Lael wants to determine how many of the active students will be eligible to transport other group members. In cell J2, enter a formula using the IF function and structured references as follows to determine if Kay Colbert can serve as authorized transport: a. The function should use a reference to the Age column to determine if the student's age is greater than 23, and should return the text Yes if true and No if false. b. 3. Fill the formula into the range J3:J31, if necessary. To be eligible for the leadership training program offered by the office, a student must have at least 2 years of post-secondary education or have gone through the organization finance training. In cell K2 enter a formula using the IF and OR functions and structured references as follows to determine if Kay Colbert can join the leadership training program: a. The IF function should determine if the student's Post-Secondary Years is greater than or equal to 2 OR if the student's finance certified status is "Yes", returning CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project la 4. 5. 6. 7. the text Yes if a student meets one or both of those criteria or the text No if a student meets neither of those criteria. b. Fill the formula into the range K3:K31, if necessary. Experienced students may serve as mentors if they are at least age 21 and have at least 3 years of post-secondary education. In cell L2, enter a formula using the IF and AND functions and structured references as follows to determine if Kay Colbert is eligible to serve as a mentor: a. b. The IF function should determine if the student's age is greater than or equal to 21 AND the student's post-secondary years are greater than or equal to 3, and should return the text Yes if a student meets both of those criteria or the text No if a student meets none or only one of those criteria. Fill the formula into the range L3:L31, if necessary. Lael is always on the lookout for students who might be interested in running for office in student groups. In cell M2, enter a formula using a nested IF function and structured references as follows to determine first if a student has already been elected to office in a student group, and if not, whether that student meets the qualifications to run in the future: If the value in the Elected column is equal to the text "Yes", the formula should display Elected as the text. a. b. Otherwise, the formula should determine if the value in the Finance Certified column is equal to the text "Yes" and return the text Yes if true And No if false. Students who work with student organizations are also considered for employment at the Student Activities Office. Students with more than 4 years of post-secondary education are qualified for more complex Tier 2 jobs. In cell N1, enter the text Tier as the column heading. In cell N2, enter a formula using the IF function and structured references as follows to determine which work tier Kay Colbert is qualified for: a. The IF function should determine if the student's Post-Secondary Years is greater than or equal to 4, and return the value 2 if true or the value 1 if false. 8. 9. b. Fill the formula into the range N3: N31, if necessary. Lael wants a quick way to look up students by their Student ID. In cell Q3, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, the text Invalid Student ID should display. Lael wants to determine several totals and averages for active students. In cell Q8, enter a formula using the COUNTIF function and structured references to count the number of students who have been elected to offices in student organizations. 10. In cell R8, enter a formula using the AVERAGEIF function and structured references to determine the average number of post-secondary years for students who have been elected. 11. In cell R9, enter a formula using the AVERAGE function and structured references to determine the average number of years of post-secondary education of all students as shown in the Post-Secondary Years column. CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1a 12. Switch to the Academic Groups worksheet. In cell A14, use the INDEX function and structured references to display the value in the first row and first column of the AcademicGroups table. 13. In cell A17, use the SUMIF function and structured references to display the total membership in 2023 for groups with at least 40 members. 14. Lael is also planning for student groups that the office will be working with in the coming year. She decides to create a PivotTable to better manipulate and filter the student group data. Switch to the Academic PivotTable worksheet, then create a PivotTable in cell A1 based on the AcademicGroups table. Update the PivotTable as follows so that it matches Final Figure 2: a. b. Change the PivotTable name to: AcademicPivotTable Add the Activities field and the Group Name field (in that order) to the Rows area. C. Add the 2021, 2022, and 2023 fields (in that order) to the Values area. d. e. f. g. h. Change the display of subtotals to Show all Subtotals at Top of Group. Change the report layout to Show in Outline Form. Update the Sum of 2021 field in the Values area to display the name 2021 Membership with the Number number format with 0 decimal places. Update the Sum of 2022 field in the Values area to display the name 2022 Membership with the Number number format with 0 decimal places. Update the Sum of 2023 field in the Values area to display the name 2023 Membership with the Number number format with 0 decimal places. 15. Lael wants to summarize data for all student groups in a PivotTable. To do so, she must first update the AllGroups table. Switch to the All Groups worksheet then edit the record for the Astronomy Society to use 76 as the 2023 field value. 16. Switch to the All Groups PivotTable worksheet. Refresh the PivotTable data, then verify that the 2023 Membership value for the Astronomy Society in row 6 reflects the change you made in the previous step. 17. Apply the Light Blue, Pivot Style Medium 2 PivotTable style to the PivotTable. 18. Add the Office field to the Filters area of the Pivot Table. Filter the table so that only organizations with private offices are visible. 19. Filter the PivotTable as follows: a. Create a Slicer based on the Activities field value. b. Resize the slicer so that it has a height of 2.2" and a width of 3.2". C. Move the slicer so that its upper-left corner appears within cell F3 and its lower- right corner appears within cell J14. d. Use the slicer to filter the PivotTable so that only Fraternal groups are visible. 20. Lael also wants to summarize membership data for all organizations using a PivotChart to help determine which groups are showing the most interest from students. Switch to the Activities PivotTable worksheet. Based on the PivotTable on the Activities CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project la PivotTable worksheet, insert a PivotChart using the Clustered Column chart type and format it as follows: a. b. C. Resize and reposition the PivotChart so that the upper-left corner is located within cell F3 and the lower-right corner is located within cell 019. Add the chart title Membership by Type to the PivotChart using the Above Chart option. Filter the PivotChart so that only the membership data for groups with educational, field, and fraternal activities in each type of group displays in the chart. (This filter may be automatically applied when you create the table.) Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1a Final Figure 1: Student Representatives Worksheet R T U- Total Students Average Post-Secondary Years M 1 Student ID Name Age Post-Secondary Years 2 G62918 Kay Colbert 6 2022 Yes 3 P49234 Michael Crozier 25 7 16.50 2023 Yes Yes Yes No Yes ase Rate Class Finance Certified Grad Student Elected Qualified Driver Leadership Training Mentor Officer Qualified ♥ Tier ♥ 16.50 Yes Yes Yes Yes Yes Yes Yes Elected 2 Student ID Student Name G62918 Kay Colbert 4 W99035 Brandon Miles 21 3 15.75 2022 Yes Yes No Yes Yes Elected 1 5 V42056 Michael Alvarez 22 4 15.75 2023 Yes No No No Yes Yes 6 T59828 Ida Smith 19 2 15.75 2024 No No Yes No Yes No Elected 1 7 F72412 Betty Garza 24 6 16.50 Yes No Yes Yes Yes Yes 2 8 W20999 Leroy Pirkle 28 10 17.50 2022 Yes No Yes Yes Yes Yes 2 Elected Students 9Q18870 Warren Stewart 18 15.00 2026 No No No No No No 1 All Students 10 W75774 Stacy Wiggins 24 6 16.50 Yes Yes Yes Yes Yes Elected 2 11 N25220 Billy Herald 3 15.75 2022 No No No No Yes Yes No 1 12 094361 Margaret Cruz 23 5 16.50 Yes Yes Yes 2 2023 Yes 13 GB7578 Chester Keese 18 0 15.00 2024 No No No No No NO No 1 Postsecondary Years 14 B68170 Claudette Littell 19 1 15.25 No No Yes No Yes 1 15 M89375 Florence Miller 21 3 15.75 2023 Yes No No No Yes Yes Yes 1 Yes Yes Yes Yes 2 16 563900 Kimberly Gerace 26 8 17.50 2023 Yes 17 196036 Roman Goble 22 4 15.75 No No No Yes Yes Yes 2 18 C82505 19 Y75358 Jocelyn Allen 22 4 15.75 2022 Yes No Yes No Yes Yes Elected 2 Ron Morse 20 15.75 2022 Yes No No Yes Yes 20 60811 Roberta Ervin 21 3 15.75 2026 No No No No Yes Yes No 1 21 Q56783 James Rusnak 19 1 2022 Yes No Yes No No Elected 1 22 Q75281 Suzanne Lawrence 24 2 15.75 2025 Yes No Yes Yes Yes No Elected 1 23 V73596 ShannonGarner 18 0 15.00 2024 No No No No No No No 1 24 L86947 Emory Little 25 5 16.50 2023 Yes Yes Yes Yes Yes Yes Elected 2 25 W90960 Laura LI 24 6 10.30 2022 Yes Yes Yes Yes Yes Elected 2 26 P40886 No No No No Floretta Cauthen 18 15.00 2025 No 27 UBB627 Beverly Berry 23 5 16.50 2023 Yes No No No Yes Yes 2 28 Y88831 Stephanie Papa 15.25 Yes No Yes No 1 29 Р83373 Wallace Rivera 29 9 17.50 30 853454 Larry Russell 31 A60088 Aaron Sifford 19 25 15.25 16.50 2023 Yes 2022 No Yes No Yes Yes Yes 2 No No No No No 1 Elected 2 Yes Yes Yes Yes Documentation Student Representatives Academic Groups Academic PivotTable All Groups All Groups PivotTable Activities PivotTable Final Figure 2: Academic Groups Worksheet 11 3.8 2 15.25 15.75 16.5 17.5 B E பட F G 1 Group Name 2 Computing Club 3 Astronomy Society 4 Humanities and English Club Type Activities Office 2021 2022 2023 Academic Field Public 54 81 93 Academic Field Private 37 51 76 Academic Professional None 47 54 64 5 Environmental Management Club Academic Service Private 45 44 52 6 Communication Studies Club 7 Nursing Club Academic Professional Public Academic Service 30 32 51 Private 44 47 41 8 History Club 9 Psychology Association for Students 10 Investigative Forensics Club Academic Professional None Academic Professional Private Academic Field 48 40 40 29 26 23 Public 6 8 10 Academic Professional None 5 6 5 11 Accounting and Finance Forum 12 13 Largest Academic Club, 2023: 14 Computing Club 15 16 2023 membership in large groups: 17 417 10 Academic Groups Academic Pivo CENGAGE

New Perspectives Excel 2019 | Module 7: SAM Project 1b Brevard County Swim Clubs SUMMARIZING YOUR DATA WITH PIVOTTABLES sam GETTING STARTED Open the file NP_EX19_7b_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as NP_EX19_7b_FirstLastName_2.xlsx by changing the "1" to a "2". If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the file NP_EX19_7b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. 2. 3. Brittany Lambert is a volunteer for the Brevard County Swim Clubs in Melbourne, Florida, and has offered to compile data on the swim club employees and teams. She needs your help completing the workbook and analyzing the data. Switch to the All Employees worksheet. In cell E3, enter a formula using the HLOOKUP function as follows to determine an employee's potential pay rate, which is based on their years of experience: a. b. Use a structured reference to look up the value in the Years of Experience column. Retrieve the value in row 2 of the table in the range P14:U15, using an absolute reference. Because hourly pay rate is tiered based on the number of years of experience, find an approximate match. Fill the formula into the range E4:E32, if necessary. Each summer, the swim clubs host a camp for young swimmers. To work as a counselor at one of the summer camps, an employee must be at least 21 years old. Brittany wants to determine how many employees are eligible to be camp counselors. In cell J3, enter a formula using the IF function and structured references as follows to determine if Tiffany Adams can work as a camp counselor: a. b. The function should use a reference to the Age column to determine if the employee's age is greater than or equal to 21, and should return the text Yes if true and No if false. Fill the formula into the range J4:J32, if necessary. To be eligible to work with the swim team, an employee must have at least two years of experience or have completed CPR training. In cell K3 enter a formula using the IF and OR functions and structured references as follows to determine if Tiffany Adams can work with the swim team: a. The IF function should determine if the employee's Years of Experience is greater than or equal to 2 OR if the employee's CPR trained status is "Yes", returning CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1b 4. 5. the text Yes if an employee meets one or both of those criteria or the text No if an employee meets neither of those criteria. b. Fill the formula into the range K4:K32, if necessary. Employees may serve as swim team coaches if they are at least age 23 and have at least three years of experience. In cell L3, enter a formula using the IF and AND functions and structured references as follows to determine if Tiffany Adams is eligible to serve as a swim team coach: a. b. The IF function should determine if the employee's age is greater than or equal to 23 AND the employee's years of experience are greater than or equal to 3, and should return the text Yes if an employee meets both of those criteria or the text No if an employee meets none or only one of those criteria. Fill the formula into the range L4:L32, if necessary. Brittany wants to identify employees who are eligible to take a CPR course at the clubs' expense. Employees who can work as camp counselors are eligible for the course. In cell M3, enter a formula using a nested IF function and structured references as follows to determine first if an employee has already been trained in CPR, and if not, whether that employee meets the qualifications to take the course: a. b. If the value in the CPR Trained column is equal to the text "Yes", the formula should display Trained as the text. Otherwise, the formula should determine if the value in the Camp Counselor column is equal to the text "Yes" and return the text Yes if true and No if false. 6. 7. 8. 9. C. Fill the formula into the range M4: M32, if necessary. Employees who work at the swim clubs are also considered for employment at the Brevard County Parks and Recreation Department. Employees with more than four years of experience are qualified for more complex Level 2 jobs. In cell N2, enter the text Level as the column heading. In cell N3, enter a formula using the IF function and structured references as follows to determine which employment level Tiffany Adams is qualified for: a. The IF function should determine if the employee's Years of Experience is greater than or equal to 4, and return the value 2 if true or the value 1 if false. b. Fill the formula into the range N4:N32, if necessary. Brittany created a formula with the VLOOKUP function to look up an employee names by their Employee ID. She also wants to alert users when they enter an incorrect ID number. In cell Q4, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, the text Invalid Employee ID should be displayed. Brittany wants to determine several totals and averages for active employees. In cell Q9, enter a formula using the COUNTIF function and structured references to count the number of employees who can work as lifeguards. 10. In cell R9, enter a formula using the AVERAGEIF function and structured references to determine the average number of years of experience for lifeguards. 11. In cell R10, enter a formula using the AVERAGE function and structured references to determine the average number of years of experience of all employees as shown in the Years of Experience column. CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1b 12. Switch to the Melbourne Swim Teams worksheet. In cell B14, use the INDEX function and structured references to display the value in the first row and first column of the Swim Teams table. 13. In cell B16, use the SUMIF function and structured references to display the total wins for teams in the Youth league. 14. Brittany wants to display the Melbourne swim teams data by league and year. She asks you to create a PivotTable to better manipulate and filter the data. Switch to the Melbourne PivotTable worksheet, and then create a PivotTable in cell A1 based on the Swim Teams table. Update the PivotTable as follows so that it matches Final Figure 3: Change the PivotTable name to: MelbournePivotTable Add the League field and the Team Name field (in that order) to the Rows area. a. b. C. Add the 2020, 2021, and 2022 fields (in that order) to the Values area. d. e. f. g. h. Change the display of subtotals to Show all Subtotals at Bottom of Group. Change the report layout to Show in Outline Form. Update the Sum of 2020 field in the Values area to display the name 2020 Wins with the Number number format with 0 decimal places. Update the Sum of 2021 field in the Values area to display the name 2021 Wins with the Number number format with O decimal places. Update the Sum of 2022 field in the Values area to display the name 2022 Wins with the Number number format with 0 decimal places. 15. Brittany wants to summarize data for all teams in a PivotTable. To do so, she must first update the AllTeams table. Switch to the All Teams worksheet then edit the record for the Brevard Blue Wave (row 26) to use 10 as the 2020 field value. 16. Switch to the All Teams PivotTable worksheet. Refresh the PivotTable data, then verify that the 2020 Wins value for the Brevard Blue Wave reflects the change you made in the previous step. 17. Apply the Lavender, Pivot Style Medium 13 PivotTable style to the PivotTable. 18. Add the League field to the Filters area of the Pivot Table. Filter the table so that only teams in the Youth league are displayed. 19. Continue to filter the PivotTable as follows: a. Create a Slicer based on the Level field value. b. Resize the slicer so that it has a height of 2.3" and a width of 1.5". C. Move the slicer so that its upper-left corner appears within cell F3 and its lower- right corner appears within cell G14. d. Use the slicer to filter the PivotTable so that only Level 4 teams groups are visible. CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1b 20. Brittany also wants to summarize and compare the performance of the swim teams by level. Switch to the Swim Levels PivotTable worksheet. Insert a PivotChart using the Stacked Column chart type and format it as follows: a. b. Resize and reposition the PivotChart so that the upper-left corner is located within cell E2 and the lower-right corner is located within cell L22. Add the chart title Wins by Level to the PivotChart using the Above Chart option. C. Filter the PivotChart so that only wins in Levels 4, 5, and 6 are displayed. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1b Final Figure 1: All Employees Worksheet 8029 Employee ID | A213 404 Megan Beckman Catherine Coleman Carrie F Com · Agu Camp Counselor-Swim Team Team Coach CPR Con You V188 No V Trained Yes % You You Yes * Trained Yes Twe No Yes You Trained Yes No Yes Yes Trained Total Employees |„Average Years of Experience 9GETS Bryant Grossburg No No No No Martyn Jeger 11 L Jonathan Lempert 13 M993 Gina Mila 2014 2019 3030 No Yes No You Yes You No Yes No Trained Jeff News Yes Tes No Yes 14 0142 Crystal Check 23 No Too Tee Trainer Yolanda Packle 18 202 No No No No No $16.90 $16.75 $16.00 $16.50 $12.80 Michael Perwak 35 2017 Yes Yes Yes Yes Yes Trane 17 P33 Eh Pekk 24 2014 Tes No Yes Tee Tee Trained James Quirian 24 Tes Yes Yea Yes Yes Trained Ryan Quinlan 20 Yes You No Yes No Traned Andrea Shelby 2 No Yes Yes Yes Anza Tran 19 No Yes No You No No Tracy Upho 23 Yes No You You Yes JV 18 No No No No No Jose Vazque 23 No No Yes Yes No V No No No No 28 You Tox No You You You Trainer Julie Weine W909 Mary Winslow 24 34 Yes * Yes 2018 Yes Yes Yes Yes Traine Brandon Waddel Yes No You You frained Aaron Ye 2019 No You frained 31 7888 Key Yamashite Yes Trained Rochard Young Documentation 2014 Yes No Yes Trained All Employees Melboume Swim Teams Melbourne PivotTable | All Teams |-σ Final Figure 2: Melbourne Swim Teams Worksheet 1 2 A Team Name B C D E F G H I Wins Location Level▾ League ▾ 2020▾ 2021▾ 2022▾ Total 3 Melbourne Vipers Melbourne 6 Adult 6 8 9 23 4 Southern Surge Melbourne 5 Adult 5 7 10 22 19 20 56781D=22=56=222222 23 21 5 Florida Wave Runners Melbourne 3 Youth 7 7 7 7 Summer Barracudas 6 Swift Swordfish 8 Coastal Tarpons 9 Melbourne Sharks 10 Slippery Seals 11 Mighty Bluefish 12 Melbourne Wahoos 13 14 Most wins, 2020-2022: Melbourne Vipers Melbourne 2 Adult 3 7 8 Melbourne 1 Summer 4 6 6 Melbourne 6 Youth 5 6 5 Melbourne 3 Summer 2 5 6 Melbourne 4 Youth 2 4 6 Melbourne 1 Youth 3 2 4 Melbourne 2 Youth 4 2 1 28663217 9 Total Youth wins: 65 ་ Documentation All Employees Melbourne Swim Teams Melbourne PivotTable All Teams CENGAGE