Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

BCOR 3010

Assignment #3

Data analysis in Excel

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 KAT Insurance Corporation data set is based on real-life data from a national insurance company. The data set contains more than 65,000 insurance sales records from 2017. All data and names have been anonymized to preserve privacy.

General learning objectives

1. Clean the data in a data set

2. Analyze sales trends

3. Interpret findings

Requirements (You will be uploading the completed Excel file to Canvas)

1. There are some typographical errors in the data set in the Region and Insurance Type fields. Find and correct these errors.

2. Calculate the variable cost and contribution margin for each policy sold.  

3. Total the sales revenue, variable cost, and contribution margin for each Insurance Type.  

a. How many total insurance policies were sold?  

b. Which Insurance Type had the highest average contribution margin? 

4. Calculate the contribution margin ratio for each policy. Rank the Insurance Type field from the highest contribution margin ratio to lowest contribution margin ratio. 

a. Which Insurance Type had the highest contribution margin ratio?

b. Do these rankings agree with the rankings you found in Requirement 3b? Should these two rankings always be the same? Explain. 

5. Calculate the fixed cost for each policy sold. Determine the number of policies, in total and for each insurance type, that need to be sold to breakeven.  (Note: the company sells approximately the same number of each policy.  This allows us to calculate breakeven by insurance type using our basic calculation).

a. How many total policies does the company need to sell to breakeven?  

b. How does this compare to the number of polices are they currently selling? What does the difference indicate?

6. Calculate the operating income for each state within each region. (Hint: Operating income = Contribution margin – fixed costs).

a. Which region had the most profitable state?  Name the region and the state. 

b. Which region had the least profitable state?  Name the region and the state.

7. Calculate the profit earned by each salesperson.

a. Which salesperson sold the most policies? Name the salesperson and the number of policies sold.

b. Which salesperson had the highest total operating income? Name the sales person and their operating income.

8. Analyze all the information you have gathered or created in the preceding requirements. Identify and explain at least 2 trends or takeaways.

Data set

The file “BCOR 3010 – Assignment #3 – Student Data Set.xlsx” posted on Canvas contains all the data needed for the project.  There are 4 tabs in the Excel file; MainData (main data set), VariableCostPct (variable cost percentages for each policy), FixedCost (fixed costs for each policy), and Assignment Answers.  Please record the answers to each requirement above on this tab before you submit the assignment.

Step-by-step instructions

The step-by-step instructions are provided in the file “BCOR 3010 – Assignment #3 – Step by Step Excel Instructions.pdf” posted on Canvas.

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. (KAT 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, KAT Insurance only sells policies in the US.

Data dictionary for VariableCostPct and FixedCost data tables (separate tabs in Excel file)

· Variable Cost Percent: This field contains the variable cost percentage of each policy.

· Fixed Cost: This field contains the fixed cost of each policy by state and policy type.

Update your version of Office 365

To do the project with Excel, you will need to use Office 365. Before starting the project, be sure to update your version of Excel. For instructions on how to update your Excel application, search for “how to update Office 365” on the internet. Either Windows or Mac will work. The instructions included in the step-by-step tutorial video use Excel for Windows, but Excel for Mac will be similar. Because there are still a few differences between the online version of Excel and the desktop version, the desktop version of Excel is recommended. The mobile app for Excel is also not full-featured and will not be adequate for this project. Important: Downloading Office 365 for the first time from your school site may not be the latest version of Office 365. Once you download Office 365, update it.

Commonly asked questions and answers

Question: I need help with doing XYZ in Excel. How can I get help?

Answer: Search the help system that is built into Excel. Alternatively, use a Search engine to find additional information. It is helpful if using a search engine to put “office 365 excel” in your query in addition to the item you are searching for.

 

Figure 1Windows help

 

Figure 2Mac help

Question: My numbers in Excel are showing as ###. What should I do?

Solution: Widen the column containing the ### signs.

 

Question: I am using a Mac and cannot find the “Select and find” command.

Solution: Go to the Edit item at the top of the screen, then click Find, and then Replace. (Remember too that you can access the Mac version of the scripted notes – see this section for the Mac link.)

 

Question: My map chart will not display – it is empty. What should I do?

Answer: First, make sure you are giving your computer enough time to process the records. Secondly, if your computer is older or lacking in available memory, it may not be able to process the map chart. You may need to try another computer. Also, you should look at the information from Microsoft Office Support at this link; it shows an alternative way to create the map chart (this is for both Windows and Macs.)

Question: How do I update Office 365 on a Windows computer (Windows 10)?

Answer: In Excel, click on File (near the top of the screen.) Then click Account, and Office Updates. (Search online for other versions of Windows.)

 

Question: How do I update Office 365 on a Mac computer?

Answer: In Excel, click on Help, then Check for Updates. (Make sure your operating system is up to date as well.)

 

Question: I cannot do anything in Excel – there are no commands visible. What do I do?

Answer: If you are viewing the Excel file in Dropbox, download the file from Dropbox and then open it in Excel (this screenshot is from using Chrome as the browser.)

 

Question: When I create my pivot table by dragging the fields into the Rows and Values, no numbers appear in the pivot table. What should I do?

Answer: This issue can be resolved by one of the following steps, including:

· You need to have the latest version of Office 365. Update your version of Office 365. Remember that downloading Office 365 for the first time may not be the latest version – update Office 365 after installing it. Office 365 update instructions are found in this document on an earlier page.

· You may need to right-click the data in the pivot table after you have updated Office 365 and select “refresh.”

· If you added data columns in the main data worksheet, make sure those columns are formatted as Accounting format (or as specified in the instructions.)

· If you are working on the Map chart and it will not work, you may need to wait a minute or two for it to populate, depending on the speed of your computer.

· If you are using an older computer or a computer without enough RAM, you may need to use another computer. This Excel file, while not exceedingly large, does have 65,000 rows and may be challenging for computers that are several years old or that have minimal RAM.