Select cell B16. Sonia asks you to create a chart comparing the number of policies sold in 2021 to the three categories of experience for each agent. Final Figure 1: Plans Worksheet Ad Amount and Sold Q1 Sales CZERZURRRR Final Figure 2: Payments by State Pivot Worksheet 3 Row Labels n Average Payment 599 100 Total Payments Average Payment Tow $695 $550 $138 $535 $107 $1,200 $645 $1,350 5194 $3,785 $140 5800 5600 $161 51.000 9 Grand Total Total Payments Avenge Payment 5400 OC DE MD NOVA Sebe Documentation Plans Payments by State Plot Customers Pivot Customers Orders - Remember to use a structured reference to the Service Yearsand the College Graduatecolumns. 2. exit Excel. the upper right corner of the pivot chart. Return to the, Place the PivotTable on a new worksheet, and then use. Benicio Cortez is a financial consultant with NewSight Consulting in Denver, Colorado. With the file SC_EX19_5b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. Click the Manage arrow, click the Excel Add-Ins option, and then click the Go button. 9. Use the range C4:E4 as the changing variable cells. Create a Scatter chart based on the range B2:H3. Create a Clustered Column PivotChart based on the PivotTable. PROJECT STEPS Create a Scenario PivotTable report for result cells C17:E17. ANALYZE DATA WITH CHARTS AND PIVOTTABLES Change the Number format of the two value fields to. The function should return the text Yes if a staff member meets both of those criteria. phone plans sold. these hierarchies of information in a chart. Dean now wants to determine how many members of his staff will be available to drive. Bruce wants to determine whether partnering with another fitness company would reduce the costs of fitness training services. Apply a conditional formatting Highlight Cells Rule to the range A2:A31 that formats any duplicate values with Light Red Fill with Dark Red Text. Go to the sales by Category worksheet. Remove the Filter field from the PivotTable. Nadia Ivanov is a partner at Qualey Consulting, a consulting firm with headquarters in Hoboken, New Jersey. He asks you to help analyze the data to determine how they can increase profits. 2. Save the model to the range B14:B21, and then close the Solver Parameters dialog box. is in cell G30. If this add-in is not available on the Data tab in the Analyze group (or if the Analyze group is not available), install Solver as follows: In Excel, click the File tab, and then click the Options button in the left navigation bar. Shelly Cashman Excel 2019 | Module 8: SAM Project la Final Figure 5: Orders Worksheet Days Bins Type Catery Phone Mobile Device Orders Mode Smartphone Large Standard Voice phone Flip Tablet State Mind 11 12 13 Sales S6,709 55.928 $1,046 $2.188 $3,075 $4,210 S2.150 S2.568 $1.205 $2.677 $1,345 Mobile Device Deliveries CustID Order ID 5118 10.112 3015 10-220 5102 10-221 3010 10-237 5104 10-337 5031 10.499 3023 10.333 5108 10-663 9019 10.882 5120 11-037 5110 11-044 5020 11.098 5122 11-364 5112 11.366 0 5106 11.999 5029 5025 11-721 5039 11.882 3114 12.076 5027 12-171 12.504 5116 12.754 3 5033 12.764 Wearable Standard Hybrid Standard Large Activity tracker Fitness Health Watch Standard Rugged Mobile Device Orders 17 18 19 20 21 23 5037 27 29 Bin Frequery Cumulative 56.52% 69.52% 82.61% 91. s sheet. chart that shows the relationship between the advertising amount. Nadia has created a Pivottable named CategoryPivot to list the sales by business category, date, and service type. b. Excel Module 2: SAM Textbook Project (Part 1: 2-1, 2-2, 2-3, 2-4) Transcript Notes The Ads table in the range I4:K16 compares the amount of This updated Collection includes the most recent features of Microsoft 365 and enhanced support for Mac users. Thanks. PIVOTTABLES With the blank PivotChart selected, add the Years Experience field to the Axis (Categories) area, Revenue 2020 as the first Values field, and Revenue 2021 as the second Values field. Staff members with over 3 years of experience are considered Senior Staff members and receive first choice of staffing assignments. PO Box 55071 #20335 1. MindTapCollections for Microsoft 365 and Office 2021 deliver a complete suite of ready-to-go texts and activities, designed to support your Digital Literacy curriculum. Modify the number format of a PivotChart field. 12. Go to the Staff Listing worksheet and complete the following steps: a. Include a secondary axis for the Average 2020-2021 data. Sonia wants to include the average revenue in the PivotTable. Modify the number format of a PivotTable field. Our best tutors earn over $7,500 each month! MANAGE YOUR DATA WITH DATA TOOLS Open the file NP_EX19_EOM6-1_FirstLastName_1.xlsx, available for download from the SAM website. You can block these cookies and then we will not be able to collect data during your visit. worksheet, which contains a PivotTable and PivotChart that, should show customer plan payments made according to where customers ordered their, field below the Order Location field in the Rows area so that the, Eastern Mobile is raising the cost per line of their Family plans. Cengage SAM Projects Excel Module 1 Project 1a Debra McCusker 623 subscribers Subscribe 39 Share 4.8K views 4 years ago In this video, we walk through the major tasks involved in completing the. 6. Illustrated Excel 2019 | Module 9: SAM Project 1a Entre Sales AUTOMATE WORKBOOK DATA. Change the value field settings of both value fields to display averages, using the Currency number format with 2 decimal places and the $ symbol. FirstLastName Dean Hilson is the director of Camp Bright Firewood. Generator. program will add the file extension for you automatically. Converter, Free College GPA The expertise of our seasoned writers allows us to say that we have no dead-end cases: they are ready to lend a hand even if you have a tight deadline, lack the necessary materials, or just have no time to handle the job yourself. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. The changing cells for both scenarios are the nonadjacent cells. Change the chart colors to Monochromatic Palette 1 to coordinate with the data source range. range A35:G48. and the group name field to the rows area. Use the number of days until delivery (range. SC_EX19_8a_FirstLastName_2.xlsx by Remove the Agent ID field from the Rows area. Many of the special staff teams require leadership training, which is offered to staff with more than 1 year of service at Camp Bright Firewood. Hide the field headers to reduce clutter in the Dean wishes to determine several totals and averages for his staff. Since base salary is tiered based on the number of years of service, find an approximatematch. 15. range G4:G16 to the range F4:F16 to change the data shown in the In cell R10, enter a formula using the AVERAGEfunction and a structured reference to determine the average number of years of service of all staff as shown in the Service Years column. Drill down into the Auto policies sold in the Downtown branch by agent BC-1283 (cell D6). If cell B6 does not display your name, delete the file and download a new copy from the SAM website. Include a secondary axis for the Average Payment data. Shelly Cashman Collection Excel 2019 Assignments Name: _____ . 2021 as the values, and the years of experience as the filter. [Mac hint: There is no checkbox, so Mac users can ignore this. These papers are intended to be used for research and reference purposes only. I'll refer to the instructions now. Follow any remaining prompts to install Solver. last name is displayed in cell B6 of the Documentation sheet. Are you in need of an additional source of income? Change the PivotChart layout to Layout 3, and use Revenue by Years of Experience as the chart title. Course Hero is not sponsored or endorsed by any college or university. password, Study Help Me, Inc. 703, Prairie Rose Cir, Brampton, ON L6R 1R7, Canada, Shelly Cashman Excel 2019 | Module 8: End of Module Project 1 specifically G2 in this case, equals Yes. changing the 1 to a 2. Save your changes, close the workbook, and then Place the PivotTable on a new worksheet, using Policies by Type as the worksheet name. b. Nadia also needs to calculate the total sales for marketing plans and the average sales for clients in New Jersey. The Optical Boutique. ANALYZE DATA WITH CHARTS AND PIVOTTABLES Change the report layout to Compact Form to make the Pivottable look less cluttered 9 Go to the sales by Client worksheet. 5. Display the slicer buttons in two columns, and then move and resize the slicer so that the upper-left corner is in cell E3 and the lower-right corner is in cell G8. Calculator, Thesis Statement Task 11 is going to have us go to cell R9. 10. Marina wants to, know where the current customers of these plans reside so that she can encourage sales. I'm clicking on C2 and I get my structured. (Hint: You can test that this formula is working by changing the value in cell Q2 to 0, but remember to set the value of cell Q2 back to 1036 when the testing is complete.). question archive Display the legend at the bottom of the chart to allow more room for the data. Resize and reposition the chart so that it covers the range E15:J30. Add a calculated field to the PivotTable named Average Revenue that adds the Revenue 2020 and the Revenue 2021 field values and then divides the result by 2 . the Total of Q1 Sales before the Total of Q2 Sales. room for the data. Sonia wants to improve the appearance of the PivotTable and focus on policies sold by agents with 10 or more years. PivotChart based on the Payments by State PivotTable. Use 2021 Policies Sold by Agent Experience as the chart title. Add a slicer to the PivotTable and PivotChart as follows to make it easy for Marina to filter. 0:18 5, 0:19 and Now you can make the PivotChart easier to understand and use as _^a8Wq#jjt>?wf4h_9Y'wT#Tu dkM5Jc@C4LW@G,Ayf2 K0|yyy;P]HT}/.MZW=~LFpx?&D|M8pDO(Jh#qpLH2#@,%It87 j-Z With the file NP_EX16_8a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. Disclaimer : MyAssignmentGuru.com provides assignment and homework help for guidance and reference purpose only. Select low cost funds Price shown above includes the solution of all questions mentioned on this page. Key Term excel module 1 sam project a Course Hero uses AI to attempt to automatically extract content from documents to surface to you and others so you can study better, e.g., in search results, to enrich docs, and more. The function should return the text No if a staff member meets none or only one of those criteria. Data ribbon, click the File tab, click Options, and then click the In cell 62, use +/-04 to 01 as the column label O. 8 terms . Operations Management questions and answers, Shelly Cashman Excel 2019 | Module 8: SAM Project 1a OK, doesn't like something that I've done. of experience.Change the layout of the PivotTable to the Outline form to separate the policy types from the agent IDs. Calculator, Thesis Statement performance, and asks for your help in creating advanced types of Customer information so that she can analyze Family plan customer Click the Add-Ins option in the left pane of the Excel Options dialog box. Follow the directions on the SAM website to submit your completed project. ? Insert a PivotTable and PivotChart on a new worksheet, using Average Revenue as the worksheet name. Resize and position the Histogram chart so that it covers the range A35:G48. ON OFF. Shelly Cashman Excel 365/2021 | Module 8: End of Module Project 1 Shield illustration P_2002704_1 Shelly Cashman Excel 365/2021 | Module 8: SAM Project 1b Title Background Zodar/Shutterstock.com P_2002706_1 Shelly Cashman Excel 365/2021 | Module 9: End of Module Project 1 Forno Pizza logo Micra/Shutterstock.com P_2002639_1 If your name does not appear in cell B6, please download a new copy of the file. 11. What Excel tool can you use if you do not know the name or category of a function? begin the test with AND open parentheses. Sonia Montero is a sales manager for Barasch & Company, an insurance agency with three offices in Wichita, Kansas. Shelly Cashman Excel 2019 | Module 8: End of Module Project 1 lilyona martinez 1 Subject: MS Excel Price: 19.99 Bought 11 Share With Shelly Cashman Excel 2019 | Module 8: End of Module Project 1 lilyona martinez 1. Madhu is developing a workbook to analyze the profitability of the. Use 2020 Revenue as the column heading in cell B3, and use 2021 Revenueas the column heading in cell C3. Since the formula is looking for a specific staff members data, find an exactmatch. Please, try our service. plans. Insert the Sum of Revenue 2021 by Branch (+) recommended PivotTable. Change the Bounds Axis Options for the new chart as follows: 10. 3. PivotTable to display customer information for. 0:03 - 0:07 - [Instructor] This is Excel, Module 11 SAM Project 1a. This updated Collection includes the most recent features of Microsoft 365 and enhanced support for Mac users. In cell Q9, enter a formula using the COUNTIF function and structured references to count the number of staff members who have participated in LeadershipTraining. a. spent and the plans sold. Shelly Cashman Excel 2019 | Module 8: End of Module Project 1 san Barasch & Company ANALYZE DATA WITH CHARTS . Display the plan type as a filter, and then filter the PivotTable to display customer. Generator. In cell N1, enter the text Staff Level as the calculated column heading. In cell E2, enter a formula using the HLOOKUPfunction to determine a staff members base salary (which is based on the number of years of service). the worksheet and display the data clearly: Resize and position the Histogram chart so that it covers the a. Marina wants to. Excel Module 5 SAM End of Module Project 1 Created: 02/11/2021 Transcript Notes 0:00 Hi everyone. Add a slicer based on the Policy Type field, and then apply the Coral, Slicer Style Light 4. The function should return the textYesif the staff members age is greater than or equal to 23. c. The function should return the textNoif the staff members age is not greater than or equal to 23. The brief, user-friendly Collection uses a proven two-page layout, helping students focus on discrete Microsoft Office skills as they complete the module readings. For Module 9, students receive a Start File and continue working on the same file until Module 12. Shelly Cashman Excel 2019 | Module 8: SAM Project la Final Figure 4: Customers Worksheet B C D E F G H I Mobile Phone Customers Years State DC Customer 3 5010 5015 5019 3 5020 MD DC Plan ID Plan Type of Lines BA2 Basic FA2 Family FA3 Family UN2 Unlimited FA Family S11 Single UN2 Unlimited FA3 Family INI International UN Unlimited UN2 Unlimited BA3 Basic FAZ Family 1 Single UN3 Unlimited UN2 Unlimited FAB Family FA2 Family Payments by State Pivot Customers Pivot Order Location Online Online EM store Online Phone store Online Phone store Online EM store Online Online Online EM store Phone store Online Online EM store Phone store Customers Orders 5023 5025 5027 5029 5031 5033 5037 5039 5102 5104 5106 5108 5110 5112 VA Payment $60.00 $150.00 $300.00 $130.00 $100.00 $45.00 $130.00 $300.00 $60.00 $325.00 $130.00 $120.00 $150.00 $45.00 $325.00 $130.00 $300.00 $150.00 DE MD 4 NC 20 6 5 VA MD 22 Documentation Plans Module 7 Project A. SAM project 1A. After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified. Switch to the Consulting Create a Scatter with Straight Lines chart based on the range E4:G14 in the data table titled Consulting Break-Even Analysis. headings. Remember to use a structured reference to the Service Years and the Leadership Training columns.