SC_EX19_8a_RileyKneisl_Report_1

Shelly Cashman Excel 2019 | Module 8: SAM Project 1a Riley Kneisl GE ver. 17.1.0-rc0000 1. 7/7 Insert a chart. Resize and reposition a chart. Update the data source in a chart. 2. 5/7 Insert a chart. Resize and reposition a chart. Insert a chart title. In the Plans worksheet, the Scatter chart's title should be "Ad Amount and Plans Sold". 3. 7/7 Add a trendline to a chart. 4. 6/7 Create a PivotTable. Change the name of a worksheet. Apply a PivotTable style. Add a value to a PivotTable. Modify the number format of a PivotTable field. Update the name of a PivotTable Field. 5. 8/8 Create a PivotChart. Add a secondary axis to a PivotChart. Change the layout of a chart. Change the chart colors. Resize and reposition a chart. 6. 8/8 Create a PivotTable. Add columns to a PivotTable. Add rows to a PivotTable. Add values to a PivotTable. Add a filter to a PivotTable. Hide field headers in a PivotTable. 7. 8/8 Insert a chart. Resize and reposition a chart. Insert a chart title. 8. 8/8 Remove a value from a PivotTable. Change the layout of a PivotTable. 9. 8/8 Add values to a PivotTable. Add a calculated field to a PivotTable. Change a column heading. 10. 8/8 Update Rows in a PivotTable. 11. 8/8 Update a value in a cell. 12. 8/8 Create a PivotChart. Change the layout of a PivotChart. Resize and reposition a chart. 13. 8/8 Add a slicer to a PivotTable and PivotChart. Reposition a slicer. Filter a PivotTable and PivotChart using a slicer. SUBMISSION #1 | SCORE IS: 97 OUT OF 100 Marina Andros works in the Financials Department of Eastern Mobile, a communications company in Alexandria, Virginia. She is using an Excel workbook to analyze the company's recent financial performance, and asks for your help in creating advanced types of charts and PivotTables to provide an overview of mobile phone plans, customers, and orders. Go to the Plans worksheet, which contains a column chart in the range F18:K31 showing sales of each plan type in Quarter 2. Marina wants to create a similar chart showing the Quarter 1 data. Provide a chart showing Quarter 1 data as follows: a. Copy the Q2 Sales chart. b. Paste the chart in cell A18. c. Select the copied chart and drag the blue outline from the range G4:G16 to the range F4:F16 to change the data shown in the chart. 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. Marina asks you to create a chart that shows the relationship between the advertising spent and the plans sold. a. Insert a Scatter chart that shows the relationship between the advertising amount (range J4:J16 ) and the number of plans sold (range K4:K16 ). b. Resize and position the Scatter chart so that it covers the range L3:R16. c. Use Ad Amount and Plans Sold as the chart title. Marina wants to analyze the relationship between advertising spent and the plans sold. Add a Linear trendline to the scatter chart. Go to the Customers worksheet, which contains customer details in a table named Customers . Marina wants to display the payments customers made in each of five states where Eastern Mobile does business. Insert a recommended PivotTable based on the Customers table as follows: a. Insert the Sum of Payment by State recommended PivotTable. [Mac Hint: Use the State field in the Rows area, the Payments field in the Values area, and show grand totals for rows and columns.] b. Use Payments by State Pivot as the name of the new worksheet. c. Apply the Light Orange, Pivot Style Medium 12 style to the PivotTable. d. Add a second copy of the Payment field to the Values area of the Field List, and then change its summary function to Average so that Marina can compare the average payments to the totals. e. Change the Number format of the two value fields to Currency with 0 decimal places and the $ symbol. f. Use Total Payments as the column heading in cell B3, and use Average Payment as the column heading in cell C3. In the Payments by State Pivot worksheet, the PivotTable in the range A3:C9 should be formatted using the Light Orange, Pivot Style Medium 12 PivotTable style. Insert a PivotChart based on the new PivotTable as follows to help Marina visualize the data: a. Insert a Combo PivotChart based on the Payments by State PivotTable. b. Display the Total Payments as a Clustered Column chart and the Average Payment as a Line chart. c. Include a secondary axis for the Average Payment data. d. Hide the Field List so that you can format the value axis of the chart. e. Change the maximum bounds for the value axis on the left to 1,400 . f. Change the PivotChart colors to Monochromatic Palette 4 . g. Resize and position the chart so that it covers the range D3:J17. h. Display the Field List again. Marina also wants to insert a PivotTable that includes other Customer information so that she can analyze Family plan customer data. Return to the Customers worksheet, and then create a PivotTable based on the Customers table as follows: a. Place the PivotTable on a new worksheet, and then use Customers Pivot as the name of the worksheet. b. Display the order location as column headings. c. Display the state field and then the customer field as row headings. d. Display the years as the values. e. Display the plan type as a filter, and then filter the PivotTable to display customer information for Family plans only. f. Hide the field headers to reduce clutter in the PivotTable. Go to the Orders worksheet. The Order_Summary table in the range G4:J15 shows the sales for three categories of mobile device orders, with each category divided into types and then into models. Marina wants to display these hierarchies of information in a chart. Insert a Sunburst chart to display the hierarchies for Marina as follows: a. Insert a Sunburst chart based on the mobile device order data in the range G4:J15. b. Resize and position the chart so that it covers the range G16:K34. c. Use Mobile Device Orders as the chart title. Go to the Customers by Plan worksheet, which contains a PivotTable showing the payments, customers, and lines for each plan. Marina does not need to display the number of lines, but does want to show subtotals in a different layout since Eastern Mobile is running a special on unlimited plans with three or more lines. Marina wants to know where the current customers of these plans reside so that she can encourage sales representatives to promote the plans in those states. Modify the PivotTable for Marina as follows: a. Remove the Sum of # of Lines field from the Values area. b. Change the report layout to Tabular Form . Go to the Sales Pivot worksheet, which contains a PivotTable comparing sales for plans in Quarter 1 and Quarter 2. Marina wants to know the difference between the two quarters. Reorder the fields and add a calculated field to the PivotTable as follows: a. Change the order of the fields in the Values area to display the Total of Q1 Sales before the Total of Q2 Sales. b. Create a calculated field using Difference as its name. c. The formula should subtract the Total Q1 Sales field value from the Total Q2 Sales field value to calculate the difference. d. Use +/- Q1 to Q2 in cell D1 as the column heading for the calculated field. Go to the Location Pivot worksheet, which contains a PivotTable and PivotChart that should show customer plan payments made according to where customers ordered their plans. Move the State field below the Order Location field in the Rows area so that the PivotTable is easier to interpret. Eastern Mobile is raising the cost per line of their Family plans. Marina wants you to fine-tune the PivotChart on the Location Pivot worksheet, but first asks you to update the data. a. Return to the Plans worksheet, and then change the cost per line to $50.00 for the three Family plans. b. Refresh the PivotChart data on the Location Pivot worksheet so that it displays accurate data. Now you can make the PivotChart easier to understand and use as follows: a. Change the PivotChart to a Stacked Column chart. b. Apply Layout 4 to the PivotChart to display values in the columns, and then apply the Monochromatic Palette 4 colors if the colors changed when you changed the chart type. c. Increase the height of the chart until the lower-right corner is in cell G30. Add a slicer to the PivotTable and PivotChart as follows to make it easy for Marina to filter the data: a. Add a slicer based on the Plan Type field. b. Without resizing the slicer, position it so that its upper-left corner is in cell I9. c. Use the slicer to filter the PivotTable and PivotChart to display Family and Unlimited plans only.

Author: Note: Do

M

Mobile Phone Plans Plan ID Plan Type # of Lines Cost / Line Total Cost BA1 Basic 1 $30.00 $30.00 BA2 Basic 2 $30.00 $60.00 BA3 Basic 4 $30.00 $120.00 FA1 Family 2 $50.00 $100.00 FA2 Family 3 $50.00 $150.00 FA3 Family 6 $50.00 $300.00 IN1 International 1 $60.00 $60.00 IN2 International 2 $60.00 $120.00 SI1 Single 1 $45.00 $45.00 UN1 Unlimited 1 $65.00 $65.00 UN2 Unlimited 2 $65.00 $130.00 UN3 Unlimited 5 $65.00 $325.00 BA1 BA2 BA3 FA1 FA2 FA3 IN1 IN2 SI1 UN1 UN2 UN3 $0 $2,000 $4,000 $6,000 $8,000 $10,000 $12,000 Q1 Sales

$4,500 $5,000 $5,500 $6,000 $6,500 $7,000 $7,500 $8,000 $8,500 $9,000 0 10 20 30 40 50 60 70 80 90 Plans Sold

Data State Sum - Payment Average - Payment DC $715.00 $102.14 DE $640.00 $160.00 MD $625.00 $125.00 NC $695.00 $173.75 VA $1,540.00 $220.00 Total Result $4,215.00 $156.11 State DC DE $0 $200 $400 $600 $800 $1,000 $1,200 $1,400

Plan Type Family Sum - Years Order Location State Customer EM store Online Phone store Total Result DC 2 2 5118 2 2 DE 1 2 3 5019 1 1 5201 2 2 MD 3 5 5 13 5102 3 3 5112 5 5 5120 5 5 NC 2 5 7 5015 2 2 5023 5 5 VA 6 12 18 5029 7 7 5110 6 6 5122 5 5 Total Result 10 23 10 43

Mobile Phone Customers Customer Years State Plan ID Plan Type # of Lines 5010 4 DC BA2 Basic 2 5015 2 NC FA2 Family 3 5019 1 DE FA3 Family 6 5020 3 DC UN2 Unlimited 2 5023 5 NC FA1 Family 2 5025 6 MD SI1 Single 1 5027 4 DC UN2 Unlimited 2 5029 7 VA FA3 Family 6 5031 2 DE IN1 International 1 5033 3 VA UN3 Unlimited 5 5037 1 DE UN2 Unlimited 2 5039 1 VA BA3 Basic 4 5102 3 MD FA2 Family 3 5104 2 DC SI1 Single 1 5106 4 NC UN3 Unlimited 5 5108 6 DC UN2 Unlimited 2 5110 6 VA FA3 Family 6 5112 5 MD FA2 Family 3 5114 4 DC BA3 Basic 4 5116 3 VA UN1 Unlimited 1 5118 2 DC FA1 Family 2 5120 5 MD FA2 Family 3 5122 5 VA FA3 Family 6 5128 6 NC IN2 International 2 5201 2 DE FA2 Family 3 5207 1 MD UN2 Unlimited 2 5208 4 VA UN2 Unlimited 2

Mobile Device Deliveries M Cust ID Order ID Days Bins Category 5118 10-112 1 2 Phone 5015 10-220 2 4 5102 10-221 3 6 5010 10-237 0 8 Tablet 5104 10-337 5 5031 10-499 6 5023 10-553 5 5108 10-663 0 Wearable 5019 10-882 2 5120 11-037 7 5110 11-054 10 5020 11-098 3 5122 11-364 2 5112 11-366 0 5106 11-599 1 5029 11-633 1 5025 11-721 0 5039 11-882 7 5114 12-076 0 5027 12-171 10 5037 12-504 1 5116 12-754 3 5033 12-764 0 Bin Frequency Cumulative % 2 13 56.52% 4 3 69.57% 6 3 82.61% 8 2 91.30% More 2 100.00% 2 4 6 8 More 0 2 4 6 8 10 12 14 0.00% 20.00% 40.00% 60.00% 80.00% 100.00% 120.00% Histogram Frequency This chart isn't ava Editing this shape permanently break

Frequency Cumulative % Bin

Data Plan Type Plan ID State Sum - Payment Count - Customer Basic BA2 DC $60.00 1 BA2 Result $60.00 1 BA3 DC $120.00 1 VA $120.00 1 BA3 Result $240.00 2 Basic Result $300.00 3 Family FA1 DC $100.00 1 NC $100.00 1 FA1 Result $200.00 2 FA2 DE $150.00 1 MD $450.00 3 NC $150.00 1 FA2 Result $750.00 5 FA3 DE $300.00 1 VA $900.00 3 FA3 Result $1,200.00 4 Family Result $2,150.00 11 International IN1 DE $60.00 1 IN1 Result $60.00 1 IN2 NC $120.00 1 IN2 Result $120.00 1 International Result $180.00 2 Single SI1 DC $45.00 1 MD $45.00 1 SI1 Result $90.00 2 Single Result $90.00 2 Unlimited UN1 VA $65.00 1 UN1 Result $65.00 1 UN2 DC $390.00 3 DE $130.00 1 MD $130.00 1 VA $130.00 1 UN2 Result $780.00 6 UN3 NC $325.00 1 VA $325.00 1 UN3 Result $650.00 2 Unlimited Result $1,495.00 9 Total Result $4,215.00 27

Data Plan Type Sum - Q1 Sales Sum - Q2 Sales Basic $20,280 $20,430 Family $29,250 $31,280 International $5,700 $5,400 Single $3,735 $2,610 Unlimited $26,455 $25,935 Total Result $85,420 $85,655

sult 50 95 This shape represents a slicer. Slicers are supported in Excel 2010 or later. If the shape was modified in an earlier version of Excel, or if the workbook was saved in Excel 2003 or earlier, the slicer cannot be used.