ACCT 401 – ACCOUNTING INFORMATION SYSTEMS SafetyCover Insurance Corporation: Data Analytics Case Student Guide & Instructions Overview This case is designed to give you an opportunity to apply your Excel skills in a practical way

Subject:MS ExcelPrice:5.99 Bought10

Share With

ACCT 401 – ACCOUNTING INFORMATION SYSTEMS

SafetyCover Insurance Corporation: Data Analytics Case

Student Guide & Instructions

Overview

This case is designed to give you an opportunity to apply your Excel skills in a practical way. You will be using Excel to analyze the sales and cost transactions for an insurance company. You will first have to find and correct errors in the data set. You will then create calculated columns, pivot tables, charts, and other items, and will draw conclusions based on these results.

General learning objectives

1. Clean the data in a data set
2. Analyze sales trends
3. Interpret findings

Excel learning objectives^*

1. Create and format a pivot table
2. Use Find & Replace
3. Refresh the data in a pivot table
4. Use the VLOOKUP function
5. Use absolute references in a VLOOKUP function
6. Create calculated columns
7. Create sum, count, and average columns in a pivot table
8. Sort a pivot table by stated criteria
9. Create a calculated field
10. Create and format (Pivot) charts

^* All these tools were covered previously in this class. Please, revisit our training modules and other class materials as needed.

Data Analytics Case Handout

Overview

The demand for college graduates with data analytics skills has exploded. While the tools and techniques are continuing to evolve and change at a rapid pace, this case illustrates how data analytics can be performed using Excel. As you analyze this case, you will be learning how to drill-down into a company’s sales and cost data to gain a deeper understanding of the company’s sales and costs and how this information can be used for decision-making. 

Background

This SafetyCover Insurance Corporation data set is based on real-life data from a US-based insurance company. The data set contains tens of thousands of insurance sales records from 2019. All data and names have been anonymized to preserve privacy.

Requirements & Instructions

The following are the requirements for analyzing sales records in the data set.

1. There are some typographical errors in the data set in the Region and Insurance Type fields. Find and correct these errors. (Hint: use a Pivot Table to examine the two fields and look for typos – does the data deviate from the descriptions in the data dictionary on page 3 of this document?)

2. Calculate the variable cost and contribution margin for each policy sold. (Hint: review your materials from your Management Accounting or Cost Accounting classes if you are not sure about the calculations; use a VLOOKUP function to find the right variable cost percentage)

3. Total the sales revenue, variable cost, and contribution margin for each Insurance Type. Then, use separate Pivot Tables to answer the below:

1. Which Insurance Type had the highest total contribution margin?  

2. Which Insurance Type had the lowest total contribution margin? 

3. How many insurance policies were sold in each Insurance Type? 

4. What is the average contribution margin per policy in each Insurance Type? 

4. Create a Pivot Table and calculate the contribution margin ratio for each policy. Rank the Insurance Type field from the highest contribution margin ratio to lowest contribution margin ratio. Do these rankings agree with the rankings you found in Requirement 3? Should these two rankings always be the same? Explain. 

5. Create a Pivot Table and calculate the contribution margin ratio for each state. Rank the states from the highest contribution margin ratio to the lowest contribution margin ratio. Which states had a contribution margin ratio greater than 75%? 
6. Within each region, what was the most profitable state in the most recent year, as measured by the contribution margin ratio? The least most profitable state in each region?  
7. Analyze all the information you have gathered or created in the preceding requirements. Create at least three charts to visualize the relevant information. What trends or takeaways do you see? Explain.

Data dictionary for main data set

• Region: This field contains the region in which the insurance was sold. There are six regions: Midwest, New England, North Central, Northeast, Southeast, and West.
• State: This field contains the state in which the insurance policy applies. The data is from sales to the 48 states in continental US and the District of Columbia. (SafetyCover Insurance does not offer insurance in the states of Alaska and Hawaii.)
• Salesperson: This field contains the name of the salesperson who sold the policy.
• Insurance Type: This field contains the type of insurance policy.
• Sales: This field contains the selling price of the insurance policy.
• Date of Sale: This field contains the date that the policy was sold.
• Invoice No: This field contains the invoice number.
• State Type: This field is a combination of the State and Insurance Type fields.
• Country: This field contains the country in which the policy was sold. At this time, SafetyCover Insurance only sells policies in the US.

Separate data table for variable cost percentages

• State Type: This field is a combination of the State and Insurance Type fields.
• Variable Cost Percent: This field contains the variable cost percentage of each policy.

I need help with this excel file