\& GEITING STARTED Open the file SC_EX19_EOM8-1_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as SC_EX19_EOM8-1_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_EOM8-1_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. 4 PROJECT STEPS 1. Sonia Mohtero is a sales manager for Barasch \& Company, an insurance agency with three offices in Wichita, Kansas. 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. Go to the Policies Sold worksheet, which summarizes the number of insurance policies Sonia Moffero is a sales manager for Barasch \& Company, an insurance agency with three offices in Wichit, Kansas. 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. Go to the Policies Sold worksheet, which summarizes the number of insurance policies sold from 2016 to 2021 . Sonia wants to illustrate the trend in the data and forecast policy sales for the next two years. Create a Scatter chart based on the range B2:H3. Add a Linear Forecast trendline to the chart to project sales to 2023 . Resize and position the chart so that it covers the range B4:H18. Go to the Sales Table worksheet, which contains a table named Policies with details about insurance policies sold in 2020 and 2021 . Sonia wants to display the amount of revenue by branch. Insert the Sum of Revenue 2021 by Branch (+) recommended PivotTable. [Mac hint Insert a PivotTable based on the Policies table on a new worksheet. Add the Branch and Agent ID fields as rows, and the Revenue 2021 field as the values.] Use Revenue by Branch as the name of the new worksheet. Apply the Coral, Pivot Style Medium 12 style to the PivotTable. Remove the Agent ID field from the Rows area. Add the Revenue 2020 field as the first field in the Values area. Change the number format of the two value fields to Currency with 0 decimal places and the $ symbol. Use 2020 Revenue as the column heading in cell B3, and use 2021 Revenue as the column heading in cell C3. Sonia wants to include the average revenue in the PivotTable. 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 . Use Average 2020-2021 as the column label in cell D3. Sonia also wants to display the revenue by branch data as a chart. Insert a Combo PivotChart based on the new PivotTable. Display the 2020 Revenue and ility: Investigate \& GEITING STARTED Open the file SC_EX19_EOM8-1_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as SC_EX19_EOM8-1_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_EOM8-1_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. 4 PROJECT STEPS 1. Sonia Mohtero is a sales manager for Barasch \& Company, an insurance agency with three offices in Wichita, Kansas. 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. Go to the Policies Sold worksheet, which summarizes the number of insurance policies Sonia Moffero is a sales manager for Barasch \& Company, an insurance agency with three offices in Wichit, Kansas. 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. Go to the Policies Sold worksheet, which summarizes the number of insurance policies sold from 2016 to 2021 . Sonia wants to illustrate the trend in the data and forecast policy sales for the next two years. Create a Scatter chart based on the range B2:H3. Add a Linear Forecast trendline to the chart to project sales to 2023 . Resize and position the chart so that it covers the range B4:H18. Go to the Sales Table worksheet, which contains a table named Policies with details about insurance policies sold in 2020 and 2021 . Sonia wants to display the amount of revenue by branch. Insert the Sum of Revenue 2021 by Branch (+) recommended PivotTable. [Mac hint Insert a PivotTable based on the Policies table on a new worksheet. Add the Branch and Agent ID fields as rows, and the Revenue 2021 field as the values.] Use Revenue by Branch as the name of the new worksheet. Apply the Coral, Pivot Style Medium 12 style to the PivotTable. Remove the Agent ID field from the Rows area. Add the Revenue 2020 field as the first field in the Values area. Change the number format of the two value fields to Currency with 0 decimal places and the $ symbol. Use 2020 Revenue as the column heading in cell B3, and use 2021 Revenue as the column heading in cell C3. Sonia wants to include the average revenue in the PivotTable. 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 . Use Average 2020-2021 as the column label in cell D3. Sonia also wants to display the revenue by branch data as a chart. Insert a Combo PivotChart based on the new PivotTable. Display the 2020 Revenue and ility: Investigate
