Overview
Part 1:
- There are some typographical errors in the data set in the Region and Insurance Type fields. Find and correct these errors by using a pivot table.
- Calculate the variable cost and contribution margin for each policy sold.
- Analyze insurance sales by using a pivot table. Rank the states from highest total contribution margin to lowest total contribution margin. Sort the contribution margin data by insurance type, from highest to lowest.
- Analyze insurance sales by using a pivot table. Rank the Insurance Type field from the highest contribution margin ratio to lowest contribution margin ratio.
- Analyze insurance sales by using a pivot table. Sort the states from the highest contribution margin ratio to the lowest contribution margin ratio.
- Analyze insurance sales by using a pivot table. Sort the states from the highest contribution margin ratio to the lowest contribution margin ratio within each region.
- Analyze all the information you have gathered or created in the preceding requirements. Part 2 - Analysis Complete Requirement 7 by writing an analysis of the data in a Word Document. The analysis should address the following: • Summarize the overall data/results by pointing out key metrics. For example, which state, region, sales person, insurance type, had the highest/lowest performance in terms of sales and contribution margin? • What trends or takeaways do you see? Explain. Note: Create additional Pivot Tables and manipulate the data in various ways to gain additional insight. For example, what month is sales the highest? Why might this be? • What are the components of Cost-volume-profit (CVP) analysis as it relates to an insurance company (i.e. define each component and give examples of each component using an insurance company)? Additional Instructions Part 2 – Analysis requirements ➢ The Word document for Part 2 should be APA formatted: -include a title page, -headings for each of the 3 requirements, - and a reference page (if applicable).