Homework answers / question archive / Shelly Cashman Excel 2019 | Module 8: SAM Critical Thinking Project 1c Eastern Mobile ANALYZE DATA WITH CHARTS AND PIVOTTABLES   GETTING STARTED Open the file SC_EX19_CT8c_FirstLastName_1

Shelly Cashman Excel 2019 | Module 8: SAM Critical Thinking Project 1c Eastern Mobile ANALYZE DATA WITH CHARTS AND PIVOTTABLES   GETTING STARTED Open the file SC_EX19_CT8c_FirstLastName_1

MS Excel

Share With

---

Shelly Cashman Excel 2019 | Module 8: SAM Critical Thinking Project 1c

Eastern Mobile

ANALYZE DATA WITH CHARTS AND PIVOTTABLES

 

• GETTING STARTED

• Open the file SC_EX19_CT8c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_CT8c_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 SC_EX19_CT8c_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. 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 she 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 showing sales of each plan type in Quarter 2. Marina wants to create a similar chart showing Quarter 1 data. Create a chart showing Quarter 1 data as follows:
   1. Copy the Q2 Sales chart and paste the new chart in cell A18.
   2. Replace the Quarter 2 sales data in the new chart with Quarter 1 sales data.
2. The Ads table 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 amount spent on advertising and the number of plans sold.
   1. Insert a Scatter chart that shows the relationship between the advertising amount and the number of plans sold.
   2. Resize and position the Scatter chart so that it covers the range L3:R16.
   3. Use Ad Amount and Plans Sold as the chart title.
   4. Add a linear trendline to the Scatter chart.
3. Go to the Customers worksheet, which contains customer details in the Mobile Phone Customers table. Marina wants to display the payments customers made in each of the five states where Eastern Mobile does business. Insert a PivotTable based on the Customers table as follows:
   1. Insert the second recommended PivotTable. [Mac Hint: Use the State field in the Rows area and the Payments field in the Values area, and show grand totals for rows and columns.]
   2. Use Payments by State Pivot as the name of the new worksheet.
   3. Apply the Light Orange, Pivot Style Medium 12 style to the PivotTable.
   4. Add a second copy of the Payment field, and then summarize the new field as an average so that Marina can compare the average payments to the totals.
   5. Display all value fields in the PivotTable as currency with zero decimal places and the $ symbol.
   6. Use Total Payments as the first column heading, and then use Average Payment as the second column heading.
4. Insert a PivotChart based on the new PivotTable as follows to help Marina visualize the data:
   1. Insert a Combo PivotChart based on the Payments by State PivotTable.
   2. Display the Total Payments data as a Clustered Column chart and the Average Payment data as a Line chart. Include a secondary axis for the Average Payment data.
   3. Change the maximum bounds for the value axis on the left to 1,400.
   4. Change the PivotChart colors to Monochromatic Palette 4.
   5. Resize and position the chart so that it covers the range D3:J17.
5. 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:
   1. Place the PivotTable on a new worksheet, and then use Customers Pivot as the name of the worksheet.
   2. Display the order locations as column headings; display the State field and Customer field data as row headings; and display the years as the values.
   3. Include a filter to display customer information for Family plan types only.
   4. Hide the field headers to reduce clutter in the PivotTable.
6. Go to the Orders worksheet. The Order_Summary table 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 based on the mobile device orders. Resize and position the chart so that it covers the range G16:K34. Use Mobile Device Orders as the chart title.
7. 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 she does want to show subtotals in a different layout because 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.
   
   Remove the Sum of # of Lines field from the Values area, and then change the report layout to Tabular Form.
8. 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 in sales between the two quarters.
   
   Reorder the fields and add a calculated field to the PivotTable as follows:
   1. Display the Total Q1 Sales column to the left of the Total Q2 Sales column.
   2. Create a calculated field named Difference that subtracts Quarter 1 sales from Quarter 2 sales.
   3. Use +/- Q1 to Q2 as the column heading for the calculated field.
9. Go to the Location Pivot worksheet, which contains a PivotTable and PivotChart that show customer plan payments 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.
10. Eastern Mobile is raising the cost per line of its Family plans. Marina wants you to fine-tune the PivotChart on the Location Pivot worksheet, but she first asks you to update the data.
    
    Return to the Plans worksheet, and then change the cost per line to $50.00 for the three Family plans. Refresh the PivotChart data on the Location Pivot worksheet so that it displays accurate data.
11. Now, make the PivotChart easier to understand and use as follows:
    1. Change the PivotChart to a Stacked Column chart.
    2. Apply Layout 4 to the PivotChart to display values in the columns, and then apply the Monochromatic Palette 4 colors, if necessary.
    3. Increase the height of the chart until the lower-right corner is in cell G30.
12. Add a slicer to the PivotTable and PivotChart as follows to make it easy for Marina to filter the data:
    1. Add a slicer based on the Plan Type field.
    2. Without resizing the slicer, position it so that its upper-left corner is in cell I9.
    3. Use the slicer to filter the PivotTable and PivotChart to display Family and Unlimited plans only.

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.