Hire Me For Complete Course
For Help Contact Me on
WhatsApp Number : +994 40 158 33 14
Whatsapp Link : [ Ссылка ]
#Module8
#EndofModuleProject1
#ShellyCashmanExcel2019
#Barasch&Company
#SC_EX19_EOM8-1_FirstLastName
#SC_EX19_EOM8-1_FirstLastName_1
#SC_EX19_EOM8-1_FirstLastName_1.xlsx
#SC_EX19_EOM8-1_FirstLastName_2.xlsx
#SC_EX19_EOM8-1_FirstLastName_2
#cengagesolution
#PearsonSolution
#PoliciesSoldWorksheet
#RevenuebyBranchWorksheet
#TopAgentWorksheet
#PoliciesbyTypeWorksheet
#AverageRevenueWorksheet
#2021PoliciesbyExperienceWorksheet
#SalesTableWorksheet
1. Sonia Montero 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 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.
2. 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
3. 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.
4. 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 the 2021 Revenue data as a Clustered Column chart and the Average 2020-2021 data as a Line chart. Include a secondary axis for the Average 2020-2021 data.
5. Format the PivotChart to make it easier to interpret and to coordinate it with the PivotTable.
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 maximum bounds for the value axis on the left to 9,000,000. Change the PivotChart colors to Monochromatic Palette 4. Resize and position the PivotChart so that it covers the range A9:D24.
6. Sonia asks you to provide a quick way to filter the PivotTable and PivotChart by policy type.
Add a slicer based on the Policy Type field, and then apply the Coral, Slicer Style Light 4. Display the slicer buttons in two columns, and then move and resize the slicer so that the upper-left corner is in cell E3 and the lower-right corner is in cell G8. Use the slicer to filter the PivotTable and PivotChart to display Auto, Home, and Life policies.
7. Sonia also wants to insert a PivotTable that includes other policy information so that she can analyze the sales of life insurance policies in 2021.
Return to the Sales Table worksheet, and then create a PivotTable based on the Policies table. Place the PivotTable on a new worksheet, using Policies by Type as the worksheet name. Make sure the 'Add this data to the Data Model' checkbox is unchecked.
8. Sonia wants to improve the appearance of the PivotTable and focus on policies sold by agents with 10 or more years of experience.
Change the layout of the PivotTable to the Outline form to separate the policy types from the agent IDs. Hide the field headers to streamline the design of the PivotTable. Display the subtotals at the bottom of each group, and then filter the PivotTable to show the policies sold by agents with 10 or more years of experience.
9. Sonia wants to know more details about the policies sold by the top-selling agent.
Drill down into the Auto policies sold in the Downtown branch by agent BC-1283 (cell D6). Use Top Agent as the name of the new worksheet.
10. Return to the Sales Table worksheet. Sonia wants to compare the average revenue generated by insurance agents based on their years of experience.
Insert a PivotTable and PivotChart on a new worksheet, using Average Revenue as the worksheet name. Make sure the 'Add this data to the Data Model' checkbox is unchecked. [Mac hint: There is no checkbox, so Mac users can ignore this.] With the blank PivotChart selected, add the Years Experience field to the Axis (Categories) area, Revenue 2020 as the first Values field, and Revenue 2021 as the second Values field. Change the value field settings of both value fields to display averages, using the Currency number format with 2 decimal places and the $ symbol. Change the PivotChart layout to Layout 3, and use Revenue by Years of Experience as the chart title. Move and resize the PivotChart so that its upper-left corner is in cell A9 and its lower-right corner is in cell C24.
