A1 - X - - for Collegiate Seminars S % ? Conditional Format Formatting as Table Call Styles Format A CI Collegiate Seminars 2 3 4 5 Author Date Purpose To track seminar bookings 8 Fuld 9 Seminar ID 10 Topic 11 Type 12 Instructor 13 Lensth 14 Location 15 Cost Seminar 10 Seminar Topic type of Seminar Instructor Name Length of seminar (days) Location of Seminar Cost of Seminar Maximum Enrollment Data Definition Table Da The Notes Number Text Accounting, Finance Marketing Management, Undecided Text General Interest, Graduate, Post Grad, and Undergrad Text Number Use Number format with one decimal place Text Main Downtown Betway all buildings of Collegate Seminars Number Use Accounting format with two decimal places Number Use Number format with no decimal places Documentation Data Review View General Home Insert Draw Page Layout Formulas X Calibri (Body) 11 A A Pastelo BIUR A A1 A X fx Collegiate Seminars A B C $ % D GH Length Cost 300 1000 250 525 Instructor Wheeler Connor Ginder Wheeler Ginder Moir Vinson | Nguyen Welker Moir Conn 700 1200 525 1200 1000 Moit 1 Seminar ID Topic 101 Marketing 102 Accounting 103 Information Systems 104 Marketing 105 Information Systems 106 Management 108 Marketing 109 Management 110 Finance 112 Management 113 Information Systems 114 Management 115 Marketing 116 Management 117 Management 118 Marketing 119 Marketing 120 Accounting 121 Information Systems 122 Accounting 123 Information Systems 124 information Systems 125 Finance 126 Marketing 127 Marketing 128 Management 129 Marketing Type Undergrad Graduate Undergrad General Interest Graduate Post Grad General Interest Post Grad Graduate General Interest Undergrad Undergrad Undergrad Post Grad Graduate Post Grad General Interest Graduate Graduate Undergrad Undergrad Graduate Post Grad Post Grad Graduate Post Grad General Interest 250 300 Location 3 Beltway 5 Beltway 2.5 Main 3 Beltway 3.5 Main 4 Beltway 3 Main 4 Main 5 Downtown 3 Beitway 25 Main 3 Beltway 3 Main 4 Downtown 4 Beltway 4 Main 3 Beltway S Main 3.5 Main 3 Betway 2.5 Main 3.5 Main 5 Downtown 4 Downtown 4 Downtown 4 Downtown 3 Main Vinson Teague Moir Vinson Wheeler Hamilton 300 1200 800 1200 525 1000 700 Connor 300 250 700 Welke Parker 1500 1200 800 Teague Vinson 1200 525 Data Review View Home Insert DrawPage Layout Formulas X C alibri (Body) 11 -AA BIU x v fx Collegiate Seminars = = 29 Gen 120 1500 900 525 300 300 130 Marketing 131 Finance 133 Information Systems 134 Accounting 135 Management 136 Finance 137 Accounting 138 Management 139 Marketing 140 Marketing 141 Management 143 Marketing 144 Marketing 145 Management 146 Information Systems 147 Management 149 Marketing 150 Management 151 Marketing 152 Marketing 153 Marketing 154 Marketing 155 Accounting 156 Narketing 157 Information Systems 158 Information Systems 159 Finance 160 Management Post Grad Post Grad Post Grad General Interest Undergrad Undergrad Post Grad Graduate Post Grad Undergrad Graduate General Interest Post Grad Undergrad Post Grad General Interest Post Grad Post Grad Graduate Post Grad Graduate Post Grad Graduate Graduate General Interest General Interest Graduate General interest Wheeler Sulter Ginder Hamilton Nguyen Welker Connor Nguyn Vinson Wheeler Teague Vinson Parker Moir Conn Nguyn Parker Moir Vinson Wheeler Parker Vinson Hamilton Wheeler Flores Ginder Suiter | Nguyen 4 Beltway 5 Beltway 3 Main 3 Main 3 Main 3 Downtown 5 Beltway 4 Main 4 Main 3 Beltway 4 Downtown 3 Main 4 Downtown 3 Beltway 3 Main 3 Main 4 Downtown 4 Beltway 4 Main 4 Beltway 4 Downtown 4 Main 5 Main 4 Beltway 2 Main 2 Main 5 Beltway 1500 800 1200 300 800 525 1200 300 900 525 1200 1200 800 1200 800 1200 1000 800 350 Documentation Seminars 3 Main 350 1000 525 + View - Formulas Calibri (Body) 11 A A BIURA A Data = Review = X General $ % aste ! 350 1000 525 x fx Collegiate Seminars BCDE 158 Information Systems General Interest Ginder 2 Main 159 Finance Graduate Sulter 5 Betway 160 Management General Interest Nguyen 3 Main 161 Management General Interest Teague 3 Downtown 163 Management Post Grad Teague 4 Downtown 164 Management Post Grad Moll 4 Beltway 1 65 Management General Interest Teague 3 Downtown 169 Management Post Grad Moir 4 Beltway 171 Finance Graduate Sulter 5 Betway 175 Management General Interest Teague 3 Downtown 176 Accounting 176 General Interest Connor 3 Beitway 178 Finance General Interest Welker 3 Downtown 179 Finance Post Grad Suiter 5 Betway 180 Marketing Graduate Parker 4 Downtown 525 1200 1200 0 525 51 62 63 54 65 1200 1000 525 525 525 1500 800 Documentation Seminars Case Problem 2 Data File needed for this Case Problem: Seminars.xlsx Collegiate Seminars Phillip Cunningham is the new manager of Collegiate Seminars in McLean, Virginia. To help him better understand the current schedule, he create the data he has collected about currently scheduled seminars, including topic, type, instructor, length, location, cost, and maximum enrollment. He asks you to analyze this data. Complete the following: 1. Open the Seminars workbook located in the Excel5 > Case2 folder included with your Data Files, and then save the workbook as Seminar Bookings in the location specified by your instructor 2. In the Documentation worksheet, enter your name and the date. 3. In the Seminars worksheet, create an Excel table, and then name it SeminarsTblFormat the Cost column with the Accounting format and no decimal places. Format the SeminarsTbl table with the table style of your choice. 4. Make a copy of the Seminars worksheet, and then rename the copied worksheet as Sort by Type (Hint: Press the Ctrl key, and drag the Seminars sheet tab to the right of the Seminars sheet tab to make a copy of the worksheet.) Sort the Seminars Tbl table in ascending order by Type, then in descending order by Cost. Copyright 2017 Cangage Learning. All Rights Reserved. May not be copied, sand, or duplicated, in whole or in part. WCN 200202 8:06 LTE ereader.chegg.com Excel Module 5 Working with Excel Tables, PivotTables, and PivotCharts 5. Use conditional formatting to highlight all Seminars with a cost greater than $950 with yellow fill with dark yellow text. 6. Make a copy of the Seminars worksheet, and then rename the copied worksheet as Filter by Location. Insert a slicer to filter by Location. Place the slicer to the right of the top of the Seminars Tbl table. Select a slicer style that matches the style you used to format the Seminars Tol table. Resize the slicer's height and width to improve its appearance. 7. Use the slicer to filter the Seminars Tbl table to display only Seminars at the Downtown location. 8. Expand the filter to also display Beltway seminars in the Seminars Tbl table. Sort the filtered table in ascending order by cost. 9. Make a copy of the Seminars worksheet, and then rename the copied worksheet as Filter Top 25%. Filter the SeminarsTbl table to display Seminars whose Costs are in the top 25 percent. (Hint: Use the Top 10 number format.) Sort the data in descending order by Cost. 10. Use the Total row to include the average cost at the bottom of the table, and then change the Total row label to Average. Remove the entry in the Max column of the Total row. 11. Make a copy of the Seminars worksheet, and then rename the copied worksheet as Subtotals Use the Subtotal command to display the total cost for each Topic in the Cost column. Make sure your table is sorted in the correct sequence for the required subtotals, and remember to convert the table to a range before subtotaling. 12. Based on the Seminars Thl table in the Seminars worksheet, create a Pivot Table in a new worksheet that totals cost by Type and Topic. Place the Type field in the COLUMNS area. Format the cost in the Pivot Table with the Accounting format and no decimal places. Format the Pivot Table with the style of your choice. Rename the worksheet as Pivot Table by Type 13. Insert a slicer to filter the Pivot Table by Type. Resize the slicer object and buttons as needed, and then select a slicer style that matches the PivotTable. Use the slicer to filter the Pivot Table to display totals for Graduate and Undergrad. 14. Based on the Seminars Thl table in the Seminars worksheet, create a Pivot Table in a new worksheet that calculates average Cost by Location and the count of Seminar IDs. Format the average cost to the Accounting format with no decimal places. Apply the same Pivot Table style to this Pivot Table. Rename the worksheet as Pivot Table for Average Cost 15. Save the workbook, and then close it. A1 - X - - for Collegiate Seminars S % ? Conditional Format Formatting as Table Call Styles Format A CI Collegiate Seminars 2 3 4 5 Author Date Purpose To track seminar bookings 8 Fuld 9 Seminar ID 10 Topic 11 Type 12 Instructor 13 Lensth 14 Location 15 Cost Seminar 10 Seminar Topic type of Seminar Instructor Name Length of seminar (days) Location of Seminar Cost of Seminar Maximum Enrollment Data Definition Table Da The Notes Number Text Accounting, Finance Marketing Management, Undecided Text General Interest, Graduate, Post Grad, and Undergrad Text Number Use Number format with one decimal place Text Main Downtown Betway all buildings of Collegate Seminars Number Use Accounting format with two decimal places Number Use Number format with no decimal places Documentation Data Review View General Home Insert Draw Page Layout Formulas X Calibri (Body) 11 A A Pastelo BIUR A A1 A X fx Collegiate Seminars A B C $ % D GH Length Cost 300 1000 250 525 Instructor Wheeler Connor Ginder Wheeler Ginder Moir Vinson | Nguyen Welker Moir Conn 700 1200 525 1200 1000 Moit 1 Seminar ID Topic 101 Marketing 102 Accounting 103 Information Systems 104 Marketing 105 Information Systems 106 Management 108 Marketing 109 Management 110 Finance 112 Management 113 Information Systems 114 Management 115 Marketing 116 Management 117 Management 118 Marketing 119 Marketing 120 Accounting 121 Information Systems 122 Accounting 123 Information Systems 124 information Systems 125 Finance 126 Marketing 127 Marketing 128 Management 129 Marketing Type Undergrad Graduate Undergrad General Interest Graduate Post Grad General Interest Post Grad Graduate General Interest Undergrad Undergrad Undergrad Post Grad Graduate Post Grad General Interest Graduate Graduate Undergrad Undergrad Graduate Post Grad Post Grad Graduate Post Grad General Interest 250 300 Location 3 Beltway 5 Beltway 2.5 Main 3 Beltway 3.5 Main 4 Beltway 3 Main 4 Main 5 Downtown 3 Beitway 25 Main 3 Beltway 3 Main 4 Downtown 4 Beltway 4 Main 3 Beltway S Main 3.5 Main 3 Betway 2.5 Main 3.5 Main 5 Downtown 4 Downtown 4 Downtown 4 Downtown 3 Main Vinson Teague Moir Vinson Wheeler Hamilton 300 1200 800 1200 525 1000 700 Connor 300 250 700 Welke Parker 1500 1200 800 Teague Vinson 1200 525 Data Review View Home Insert DrawPage Layout Formulas X C alibri (Body) 11 -AA BIU x v fx Collegiate Seminars = = 29 Gen 120 1500 900 525 300 300 130 Marketing 131 Finance 133 Information Systems 134 Accounting 135 Management 136 Finance 137 Accounting 138 Management 139 Marketing 140 Marketing 141 Management 143 Marketing 144 Marketing 145 Management 146 Information Systems 147 Management 149 Marketing 150 Management 151 Marketing 152 Marketing 153 Marketing 154 Marketing 155 Accounting 156 Narketing 157 Information Systems 158 Information Systems 159 Finance 160 Management Post Grad Post Grad Post Grad General Interest Undergrad Undergrad Post Grad Graduate Post Grad Undergrad Graduate General Interest Post Grad Undergrad Post Grad General Interest Post Grad Post Grad Graduate Post Grad Graduate Post Grad Graduate Graduate General Interest General Interest Graduate General interest Wheeler Sulter Ginder Hamilton Nguyen Welker Connor Nguyn Vinson Wheeler Teague Vinson Parker Moir Conn Nguyn Parker Moir Vinson Wheeler Parker Vinson Hamilton Wheeler Flores Ginder Suiter | Nguyen 4 Beltway 5 Beltway 3 Main 3 Main 3 Main 3 Downtown 5 Beltway 4 Main 4 Main 3 Beltway 4 Downtown 3 Main 4 Downtown 3 Beltway 3 Main 3 Main 4 Downtown 4 Beltway 4 Main 4 Beltway 4 Downtown 4 Main 5 Main 4 Beltway 2 Main 2 Main 5 Beltway 1500 800 1200 300 800 525 1200 300 900 525 1200 1200 800 1200 800 1200 1000 800 350 Documentation Seminars 3 Main 350 1000 525 + View - Formulas Calibri (Body) 11 A A BIURA A Data = Review = X General $ % aste ! 350 1000 525 x fx Collegiate Seminars BCDE 158 Information Systems General Interest Ginder 2 Main 159 Finance Graduate Sulter 5 Betway 160 Management General Interest Nguyen 3 Main 161 Management General Interest Teague 3 Downtown 163 Management Post Grad Teague 4 Downtown 164 Management Post Grad Moll 4 Beltway 1 65 Management General Interest Teague 3 Downtown 169 Management Post Grad Moir 4 Beltway 171 Finance Graduate Sulter 5 Betway 175 Management General Interest Teague 3 Downtown 176 Accounting 176 General Interest Connor 3 Beitway 178 Finance General Interest Welker 3 Downtown 179 Finance Post Grad Suiter 5 Betway 180 Marketing Graduate Parker 4 Downtown 525 1200 1200 0 525 51 62 63 54 65 1200 1000 525 525 525 1500 800 Documentation Seminars Case Problem 2 Data File needed for this Case Problem: Seminars.xlsx Collegiate Seminars Phillip Cunningham is the new manager of Collegiate Seminars in McLean, Virginia. To help him better understand the current schedule, he create the data he has collected about currently scheduled seminars, including topic, type, instructor, length, location, cost, and maximum enrollment. He asks you to analyze this data. Complete the following: 1. Open the Seminars workbook located in the Excel5 > Case2 folder included with your Data Files, and then save the workbook as Seminar Bookings in the location specified by your instructor 2. In the Documentation worksheet, enter your name and the date. 3. In the Seminars worksheet, create an Excel table, and then name it SeminarsTblFormat the Cost column with the Accounting format and no decimal places. Format the SeminarsTbl table with the table style of your choice. 4. Make a copy of the Seminars worksheet, and then rename the copied worksheet as Sort by Type (Hint: Press the Ctrl key, and drag the Seminars sheet tab to the right of the Seminars sheet tab to make a copy of the worksheet.) Sort the Seminars Tbl table in ascending order by Type, then in descending order by Cost. Copyright 2017 Cangage Learning. All Rights Reserved. May not be copied, sand, or duplicated, in whole or in part. WCN 200202 8:06 LTE ereader.chegg.com Excel Module 5 Working with Excel Tables, PivotTables, and PivotCharts 5. Use conditional formatting to highlight all Seminars with a cost greater than $950 with yellow fill with dark yellow text. 6. Make a copy of the Seminars worksheet, and then rename the copied worksheet as Filter by Location. Insert a slicer to filter by Location. Place the slicer to the right of the top of the Seminars Tbl table. Select a slicer style that matches the style you used to format the Seminars Tol table. Resize the slicer's height and width to improve its appearance. 7. Use the slicer to filter the Seminars Tbl table to display only Seminars at the Downtown location. 8. Expand the filter to also display Beltway seminars in the Seminars Tbl table. Sort the filtered table in ascending order by cost. 9. Make a copy of the Seminars worksheet, and then rename the copied worksheet as Filter Top 25%. Filter the SeminarsTbl table to display Seminars whose Costs are in the top 25 percent. (Hint: Use the Top 10 number format.) Sort the data in descending order by Cost. 10. Use the Total row to include the average cost at the bottom of the table, and then change the Total row label to Average. Remove the entry in the Max column of the Total row. 11. Make a copy of the Seminars worksheet, and then rename the copied worksheet as Subtotals Use the Subtotal command to display the total cost for each Topic in the Cost column. Make sure your table is sorted in the correct sequence for the required subtotals, and remember to convert the table to a range before subtotaling. 12. Based on the Seminars Thl table in the Seminars worksheet, create a Pivot Table in a new worksheet that totals cost by Type and Topic. Place the Type field in the COLUMNS area. Format the cost in the Pivot Table with the Accounting format and no decimal places. Format the Pivot Table with the style of your choice. Rename the worksheet as Pivot Table by Type 13. Insert a slicer to filter the Pivot Table by Type. Resize the slicer object and buttons as needed, and then select a slicer style that matches the PivotTable. Use the slicer to filter the Pivot Table to display totals for Graduate and Undergrad. 14. Based on the Seminars Thl table in the Seminars worksheet, create a Pivot Table in a new worksheet that calculates average Cost by Location and the count of Seminar IDs. Format the average cost to the Accounting format with no decimal places. Apply the same Pivot Table style to this Pivot Table. Rename the worksheet as Pivot Table for Average Cost 15. Save the workbook, and then close it