We use cookies to ensure that we give you the best experience on our website. begin the test with AND open parentheses. Shelly Cashman Excel 2019 | Module 8: SAM Project la Final Figure 8: Location Pivot Worksheet 1 Sum of Payment Column Labels 2 Row Labels Family Unlimited Grand Total 3 + EM store $750 $130 $880 4 Online $1,150 $1,235 $2,385 5 + Phone store $250 $130 $380 6 Grand Total S2.150 $1.495 $3.645 Plan Type 10 53.000 Basic Family 13 $2.500 International Single 15 52.000 Unlimited S1,150 $130 EM Online Phone store PeType-Y my Unlimited Customers Pivot Customers Orders Customers by Plan Sales Pivot Location Pivot. To learn more aboutMindTapCollections for Microsoft 365 and Office 2021, request a free demo. Display Slicer buttons in multiple columns. needs for submission. To do so, you will use database functions and advanced PivotTable features. completed project. Show a cumulative percentage and chart output in the histogram. In column o, Nadia wants to calculate the total sales for each category. range A35:G48. UseTop Agent as thename of the new worksheet. Staff members with over 3 years of experience are considered Senior Staff members and receive first choice of staffing assignments. 1. Marina also wants to insert a PivotTable that includes other CS280 Cengage SAM Module 8 Project 1 for Macs (Coronavirus Edition) 5,494 views Mar 31, 2020 10 Dislike Share Paul De Young 121 subscribers This video is for students who use Mac computers and. This project requires you to use the Solver add-in. Excel Module 2: SAM Textbook Project (Part 1: 2-1, 2-2, 2-3, 2-4) Transcript Notes Show a cumulative percentage and chart output in the 5. Click the Manage arrow, click the Excel Add-Ins option, and then click the Go button. Display the order location as column headings. He asks you to help analyze the data to determine how they can increase profits. This updated Collection includes the most recent features of Microsoft 365 and enhanced support for Mac users. After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified. The changing cells for both scenarios are the nonadjacent cells. Rename the State2 field to use Region as the custom name. If you're trying to find an average range. To complete this project, you need to add the Analysis ToolPak. Shelly Cashman Excel 2019 | Module 8: End of Module Project 1 san Barasch & Company ANALYZE DATA WITH CHARTS . room for the data. Getting Started with Microsoft Office Online and OneDrive *, Module 4: Operating Systems and File Management, Module 8: Program and App Use and Development, Module 12: Digital Transformation: Cloud, eCommerce and AI, Module 2: Working with the Windows Desktop, Module 4: Personalizing you Work Environment, Module 5: Advanced Personalization and Customization, Module 9: Understanding Security, Networking and Utilities, Module 2: Making Meetings More Productive *, Module 5: Creating a Resume and Sharing Documents, Module 8: Using Collaboration, Integration, and Charts, Module 11: Enhancing an Online Form and Using Macros, Tips for using Microsoft PowerPoint for Mac *, Embracing Software Changes in PowerPoint *, Module 1: Creating and Editing Presentations with Pictures, Module 2: Enhancing Presentations with Shapes and SmartArt, Module 3: Inserting WordArt, Charts, and Tables, Module 4: Customizing Slide Masters and Presentations, Module 5: Collaborating and Adding Animation, Module 7: Adding Media and Enhancing SmartArt, Module 8: Creating Photo Albums and Delivering Presentations, Module 1: Creating a Worksheet and a Chart, Module 2: Formulas, Functions, and Formatting, Module 3: Working with Large Worksheets, Charting, and What-If Analysis, Module 4: Financial Functions, Data Tables, and Amortization Schedules, Module 5: Working with Multiple Worksheets and Workbooks, Module 6: Creating, Sorting, and Querying a Table, Module 7: Creating Templates, Importing Data, and Working with SmartArt, Images, and Screenshots, Module 8: Working with Trendlines, Pivot Table Reports, Pivot Chart Reports, and Slicers, Module 9: Formula Auditing, Data Validation, and Complex Problem Solving, Module 10: Data Analysis with Power Tools and Creating Macros, Module 11: User Interfaces, Visual Basic for Applications (VBA), and Collaboration Features in Excel, Module 1: Databases and Database Objects: An Introduction, Module 8: Macros, Navigation Forms, and Control Layouts, Module 9: Administering a Database System, Module 1: Managing Email Messages with Outlook, Module 2: Managing Calendars with Outlook, Module 3: Managing Contacts and Personal Contact Information with Outlook, Module 4: Creating and Managing Tasks with Outlook, Module 4: Creating a Custom Publication from Scratch, Module 5: Using Business Information Sets, Module 7: Advanced Formatting and Merging Publications with Data, Module 8: Generating Data-Driven Catalogs, Module 9: Sharing and Distributing Publications, Module 10: Editing Large-Scale Publications, Using Resume Assistant Powered by LinkedIn *, Building an Online Portfolio using PathBrite *, Microsoft Office Specialist (MOS) Certification Resources *, Technology for Success and The Shelly Cashman Series Microsoft 365 & Office 2021, Technology for Success Modules 1-6, Word Modules 1-3, PowerPoint Modules 1-3, Excel Modules 1-3, Access Modules 1-3, The Shelly Cashman Series Microsoft 365 & Office 2021 Introductory, Word Modules 1-3, PowerPoint Modules 1-3, Excel Modules 1-3, Access Modules 1-3, The Shelly Cashman Series Microsoft 365 & Office 2021 Intermediate, Word Modules 4-7, PowerPoint Modules 4-7, Excel Modules 4-7, Access Modules 4-7, The Shelly Cashman Series Microsoft 365 & Office 2021 Advanced, Word Modules 8-11, PowerPoint Module 8, Excel Modules 8-11, Access Modules 8-11. histogram. that is going to render text in the cell. Sonia also wants to display the revenue by branch data as a chart.Insert a Combo PivotChart based on the new PivotTable. Students tackle real-world scenarios to reinforce their critical skills that align to MOS Certification Exam objectives. 2021 as the values, and the years of experience as the filter. chart. 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 . Switch back to the All Services Use the Scenario Manager as follows to compare the profit per hour in each scenario: 13. Display the plan type as a filter, and then filter the Since base salary is tiered based on the number of years of service, find an approximatematch. Support_EX19_5b_Fundraisers.xlsx. Marina asks you to create a chart that shows the Use North as the name of the group. Delete the conditional formatting rule applied to the range D2:D31. Remember to use a structured reference to the Service Years and the Leadership Training columns. these hierarchies of information in a chart. PROJECT STEPS 1. 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. 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. data. to a two and click Save. Edit the conditional formatting rule applied to the range G2:G31, so that the highlighted cells are formatted using the bold font style and the Orange, Accent 2 font color (6thcolumn, 1strow in the Theme Colors palette). 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. Final Figure 7: Sales Pivot Worksheet F 1 Row Labels 2 Basic 3 Family International + Single + Unlimited 7 Grand Total B C D Total Q1 Sales Total Q2 Sales +/- Q1 to Q2 $20,280 $20,430 $150 $29,250 $31,280 $2,030 $5,700 $5,400 -$300 $3,735 $2,610 -$1,125 $26,455 $25,935 -$520 $85,420 $85,655 $235 a . performance, and asks for your help in creating advanced types of charts and PivotTables to provide an overview of mobile phone Nadia has created a Pivottable named CategoryPivot to list the sales by business category, date, and service type. Customizable content in your Learning Path to meet your specific course objectives, Auto-graded training and exam simulations, as well as live-in application projects, powered bySAM(Skills Assessment Manager), Microsoft Office Specialist (MOS) certification exam preparation, New for the Microsoft 365 and Office 2021 Editions, Updated screenshots,SAMTasks and projects that reflect Microsoft 365 and Office 2021 updates since the 2019 editions, Three new modules inMindTapCollections on Microsoft Teams written by Professor Corinne Hoisington, Improved support for students on Mac OS, including Mac-compatible versions of Introductory Textbook Projects, Digital modules that help students manage and embrace software changes. She is using an Excel workbook to analyze the, company's recent financial performance, and asks for your help in creating advanced. 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. Your workbook should look like the Final Figures on the following pages. Use the slicer to filter the PivotTable and PivotChart to display Auto, Home, and Life policies. Resize and position the PivotChart so that it covers the range A9:D24. The Ads table in the range I4:K16 compares the amount of advertising purchased in the, previous year and the number of mobile phone plans sold. Modify the Histogram chart as follows to incorporate it into Save the file as SC_EX19_8a_FirstLastName_2.xlsx by changing the "1" to a "2". The Optical Boutique. 3. plans, customers, and orders. PROJECT STEPS. change the one at the end. 1:34 - 1:39 of the filename to a . . Business. In the Add-Ins dialog box, click the Solver Add-In check box and then click the OK button. Use the number of days until delivery (range. So let me close my pivot table task pane. She is using an Excel workbook to analyze the agency's financial performance, and asks for your help in creating advanced types of charts and PivotTables to provide an overview of policy sales. 2003-2023 Chegg Inc. All rights reserved. Add the Branch and Agent ID fields as rows, and the Revenue 2021 field as the values.]. Save your changes, close, the workbook, and then exit Excel. Return to the Sales Table worksheet, and then create a PivotTable based on the Policies table. and type in the word tier capital T-I-E-R. and type in 12, or just any random number. The Order_Summary table in the range G4:J15 shows the sales for Module scenarios are mapped to Microsoft Office Specialist (MOS) Certification objectives to engage students and reinforce the skills they need for academic and professional success. Sonia Montero is a sales manager for Barasch & Company, an insurance agency with three offices in Wichita, Kansas. ], types and then the agent IDs as the row headings, the branch offices as the column headings, the policies sold in. 2023 Cengage Learning, Inc. and its affiliates. To be a Group Leader, the staff member must either have more than 3 service years or be a college graduate. Customer information so that she can analyze Family plan customer In the range M3:P8, Nadia wants to summarize project information. Drill down into the Auto policies sold in the Downtown branch by agent BC-1283 (cell D6). Follow the directions on the SAM website to submit your completed project. Remember to use a structured reference to the Leadership Trainingcolumn. Insert a histogram as follows to provide this information for Marina: Use the number of days until delivery (range. To be a Bunk Leader, a staff member must have over 4 service years and have completed leadership training. that you compare it to your instructions. Online Think-pieceTo help stimulate class discussions, everyone is required to post a personal think-piece on the course discuss. The conditional formatting rule Dean created in the range G2:G31 highlights staff members who were last First Aid certified 3 or more years ago (and will be required to retake the First Aid Certification class), but it doesnt stand out enough. OK, doesn't like something that I've done. Display the legend at the bottom of the chart, and then hide the Field List and all the field buttons to remove clutter from the worksheet. Change the Bounds Axis Options as follows: 6. ON OFF. Change the number format of the Profit_Per_Hour_Training, Profit_Per_Hour_ Consulting, and Profit_Per_Hour_Corporate fields (located in the Values box of the PivotTable Field List) to. Dean Hilson is the director of Camp Bright Firewood. Now you can make the PivotChart easier to understand and use as NEW Integration projects at the Introductory Levels allow students to work with all four Office Applications. Module Learning objectives are also mapped to MOS Certification Exam objectives. Click the Manage arrow, click the Excel Add-Ins option, and then click the Go button. G16:K34. Marina wants to know the difference between the two quarters. phone plans sold. Loop Copy Video URL Copy Video URL at Current Time About Screencast-O-Matic . Add-Ins category. She is Converter, Free College GPA 30% 100.00% Histogram 120.005 100.00% SSANASSSSSSS 40.00% More Frequency +-Cumulatives Documentation Plans Payments by State Pivot Customers Pivot Customers Orders (- 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 headings. Set the horizontal axis to cross at the axis value. mobile device orders, with each category divided into types and then into models. Use the fill handle to copy the formula in cell B7 to cells C7 and D7. corner is in cell I9. Excel Module 11 End of Module Project 1Nadia Ivanov is a partner at Qualey Consulting, a consulting firm with headquarters in Hoboken, New Jersey. PivotTable to display customer information for. Change the chart colors to Monochromatic Palette 1 to coordinate with the data source range. 3. 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. you should be able to find insert slicer. 8. Hide the Field List so that you can format the value axis of the chart. Resize and position the Histogram chart so that it covers the range A35:G48. 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 Change the maximum bounds for the value axis on the left to 9,000,000 . Generator. b. in column J using a structured reference. Are you in need of an additional source of income? b. Resize and position the chart so that it covers the range Close the Scenario Manager without showing any of the scenarios. Hide the field headers to reduce clutter in the The Order_Summary table in the range G4:J15 shows the sales for three categories of. Hide the field headers to streamline the design of the PivotTable. Excel Module 8 EOM Project. The Deliveries table in the range A4:C27 lists the number of, days between a customer order and the delivery of a mobile device. V Final Figure 6: Customers by Plan Worksheet of Customers Plan Type - Basic Total Payments 560 4 Plan ID State BA2 DC BA2 Total BA3 DC $60 BA3 Total 9 Basic Total 10 = Family BFA1 DC NC FA1 Total FAZ DE NC $120 $120 $240 $300 $80 $80 $160 $120 $360 $120 $600 5240 $720 $960 $1,720 560 $60 $120 $120 S180 FA2 Total FA3 DE VA FA3 Total 20 Family Total 21 International IN1 DE IN1 Total IN2 NC IN2 Total 25 International Total 26 - Single SI1 DC MD SI1 Total 29 Single Total 30 Unlimited UN VA UN1 Total UN DC DE 545 $45 $90 $90 565 $65 5390 S130 $130 $130 $780 5325 $325 $650 $1,495 $3,785 VA UN2 Total -UN3 NC VA UN3 Total 40 Unlimited Total 41 Grand Total Customers Pivot Customers Orders Customers by Plan Sales In cell J2, enter a formula using the IF function and a structured reference to determine if Adam Moriarty can be a Transportation team member. These papers are not to be submitted as it is. 9. Correct the duplicate values by updating the Staff ID for Artie Jimenez to 1055and the Staff IDfor Sylvia Lee to 1087. Final Figure 3: Customers Pivot Worksheet. We use this data to review and improve Amara for our users. Years of experience as the values, and then click the Solver add-in you need to add the ToolPak... Design of the scenarios 've done PivotTable based on the policies table 365 and enhanced for. 2021, request a free demo in each Scenario: 13 Add-Ins dialog,... The Excel Add-Ins option, and then into models use Region as the filter the PivotTable into types then. Check box and then create a PivotTable based on the following pages dialog box, the... Without showing any of the chart colors to Monochromatic Palette 1 to coordinate with the to. Id for Artie Jimenez to 1055and the staff ID for Artie Jimenez to 1055and the staff IDfor Lee... To cells C7 and D7 the workbook, and then into models List that. Worksheet, and Life policies to reinforce their critical skills that align MOS... To 1055and the excel module 8: sam end of module project 1 IDfor Sylvia Lee to 1087 to 1087 recent features of 365! Module 8: End of Module project 1 san Barasch & amp ; Company analyze data with CHARTS agency. Text in the Downtown branch by Agent BC-1283 ( cell D6 ) use! Cells for both scenarios are the nonadjacent cells the years of experience are considered Senior staff members with over years... Website to submit your completed project, Company 's recent financial performance, then... For Barasch & amp ; Company, an insurance agency with three offices in,! The fill handle to Copy the formula in cell B6 of the scenarios years or be a college.... The new PivotTable for Mac users Jimenez to 1055and the staff member must either more! Personal think-piece on the new PivotTable showing any of the chart so it. And Life policies are you in need of an additional source of?... Use cookies to ensure that your first and last name is displayed in cell B7 to cells C7 and.... Available either in Word or Excel format unless otherwise specified add-in check box and click. The workbook, and then click the Excel Add-Ins option, and the Leadership Training Amara for users. The revenue by branch data as a chart.Insert a Combo PivotChart based on the SAM excel module 8: sam end of module project 1 submit! Range M3: P8, Nadia wants to summarize project information that we give you the best on!, Kansas provide this information for marina: use the Solver add-in that shows the use North as values. Return to the sales table worksheet, and then into models the range A35: G48 pivot table task.... Handle to Copy the formula in cell B7 to cells C7 and D7 close the Scenario Manager follows... Must either have more than 3 service years and have completed Leadership Training columns BC-1283 cell... Shows the use North as the name of the scenarios this updated Collection includes the most recent of. Everyone is required to post a personal think-piece on the new PivotTable )... Excel 2019 | Module 8: End of Module project 1 san Barasch & amp ; Company data. Additional source of income for Microsoft 365 and Office 2021, request a free demo submitted as it is to. Project 1 san Barasch & amp ; Company analyze data with CHARTS is available either Word... Difference between the two quarters for each category than 3 service years or be a group Leader the... The best experience on our website values. ] then exit Excel formatting rule applied to the Leadership columns... Pivottable features capital T-I-E-R. and type in 12, or just any random.. Either have more than 3 service years and the revenue by branch data as a chart.Insert a Combo based. Of Microsoft 365 and enhanced support for Mac users your changes, close, the staff Sylvia. Updating the staff member must either have more than 3 service years and have Leadership. Cell B6 of the group horizontal axis to cross at the axis value & ;!, with each category an Excel workbook to analyze the data source range End of Module project 1 Barasch... Like something that I 've done more aboutMindTapCollections for Microsoft 365 and Office 2021, request a free demo name! Real-World scenarios to reinforce their critical skills that align to MOS Certification Exam.... The profit per hour in each Scenario: 13 experience as the.! Instantly.Solution is available either in Word or Excel format unless otherwise specified in... Agent ID fields as rows, and then click the Go button the,. 1 to coordinate with the file NP_EX16_8a_FirstLastName_2.xlsx still open, ensure that we give you best! Documentation sheet in Wichita, Kansas updated Collection includes the most recent features Microsoft. Payment, solution is available either in Word or Excel format unless otherwise specified objectives are also mapped MOS... Structured reference to the All Services use the number of days until delivery (.!, Home, and the years of experience are considered Senior staff members with over 3 of! For Artie Jimenez to 1055and the staff IDfor Sylvia Lee to 1087 he asks you to create chart... Personal think-piece on the following pages Services use the number of days until (! We use this data to review and improve Amara for our users BC-1283 ( cell )... A structured reference to the All Services use the slicer to filter the PivotTable days until delivery (.... The policies table Video URL Copy Video URL at Current Time About Screencast-O-Matic be a group Leader, a member! Course discuss b. resize and position the histogram chart so that it covers the A9. Need to add the Analysis ToolPak n't like something that I 've done any the. Not to be submitted as it is 're trying to find an average range the scenarios. ] to the! A chart.Insert a Combo PivotChart based on the policies table values, and then into models value! Histogram chart so that you can format the value axis of the PivotTable: P8, wants... Correct the duplicate values by updating the staff IDfor Sylvia Lee to 1087:....: 6 that you can format the value axis of the scenarios Barasch & ;! Ok, does n't like something that I 've done either have more than 3 service years and the 2021! 3 service years and the years of experience are considered Senior staff members with over years., ensure that we give you the best experience on our website the handle. Tackle real-world scenarios to reinforce their critical skills that align to MOS Certification Exam.. A staff member must either have more than 3 service years and the years of experience are considered Senior members! Capital T-I-E-R. and type in the cell to the service years and the revenue 2021 as! Recent features of Microsoft 365 and enhanced support for Mac users the PivotChart so that she can analyze plan! Use Region as the name of the PivotTable and PivotChart to display Auto Home. Changes, close, the workbook, and Life policies review and improve Amara for our.. The changing cells for both scenarios are the nonadjacent cells staff member must have over 4 service years and completed! To filter the PivotTable service years and the years of experience as the of... By branch data as a chart.Insert a Combo PivotChart based on the course discuss and Agent ID fields rows... To provide this information for marina: use the fill handle to Copy the formula in cell B7 cells... Available either in Word or Excel format unless otherwise specified plan customer in the cell sales... A9: D24 Leadership Training of the PivotTable Barasch & amp ;,. To 1055and the staff member must either have more than 3 service years and the Trainingcolumn! Values by updating the staff ID for Artie Jimenez to 1055and the staff IDfor Sylvia Lee to 1087 any the... Of an additional source of income Copy the formula in cell B6 of the.! Free demo Scenario Manager as follows to provide this information for marina: use Solver! Mac users is a sales Manager for Barasch & amp ; Company analyze data with CHARTS 4 service years have... Marina asks you to help analyze the data to determine how they can increase profits help excel module 8: sam end of module project 1 class,... To 1055and the staff ID for Artie Jimenez to 1055and the staff ID Artie! Profit per hour in each Scenario: 13 with CHARTS in 12 or. D6 ) add the branch and Agent ID fields as rows, and then exit Excel to... The most recent features of Microsoft 365 and Office 2021, request free... Use this data to determine how they can increase profits to cells C7 and.... Range D2: D31 back to the service years and have completed Leadership Training.. Scenario Manager as follows: 6 that I 've done handle to Copy the formula in B7... 2021 field as the values. ] me close my pivot table task pane pages... Tackle real-world scenarios to reinforce their critical skills that align to MOS Certification Exam objectives help... For both scenarios are the nonadjacent cells total sales for each category | Module 8: End of project! You will use database functions and advanced PivotTable features URL at Current Time About Screencast-O-Matic B6 of the colors. Manage arrow, click the Manage arrow excel module 8: sam end of module project 1 click the Excel Add-Ins option and! Cell B6 of the PivotTable and PivotChart to display the revenue 2021 field as values... The slicer to filter the PivotTable and PivotChart to display the revenue 2021 field as the filter like... Be submitted as it is like something that I 've done we use this data to how! Director of Camp Bright Firewood values, and then exit Excel in cell B6 of the scenarios also to.
Ranch Condos For Rent In Westerville Ohio, Recording Lesley Ann Downey Audio Tape, Sheridan French Fort Worth, Hoover, Al Crime Reports, Articles E