Homework answers / question archive / Overview This case is designed to give you an opportunity to apply your Excel skills in a practical way

Overview This case is designed to give you an opportunity to apply your Excel skills in a practical way

Accounting

Share With

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. You can revisit our training modules and other class materials as needed. ACCT 401 – ACCOUNTING INFORMATION SYSTEMS Page 3 of 3 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 2017. 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. (4 Points) a) 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 or Sort to examine the two fields and look for typos) b) Complete the “variable cost percentage” column by using a VLOOKUP function. (Hint: use “State Type” as the lookup value and the information in the “VariableCostPct” tab as the table_array). c) Complete the “Variable Cost Column” (Hint: Variable Cost = Sales * Variable Cost Percentage). d) Compete the “Contribution Margin Column) (Hint: Contribution Margin = Sales – Variable Cost). 2. (4 Points) - Create 4 separate tabs to answer the following questions. Label the tabs 2a, 2b, 2c,2d. Use separate Pivot Tables to answer the below: a. Which Insurance Type had the highest sales? b. Which Insurance Type had the highest total contribution margin? c. How many insurance policies were sold in each Insurance Type? d. What is the average contribution margin per policy in each Insurance Type? 3. (5 Points) - Create 5 separate tabs label the tabs 3a-Auto, 3b-Disability, 3c-Home, 3d-Life, 3eProfessional. Each tab will illustrate a respective insurance type. Each tab will illustrate a pivot table and pivot chart. In summary -5 insurance types therefore 5 separate tabs. Tasks: 1. Filter by the respective insurance type as described above 2. Pivot by sum of sales and by state 3. Use value filter to display the top 10 sales 4. Create a Pivot Chart in the same tab (Clustered Column Chart) 4. (2 Points) - Analyze all the information you have gathered or created in the preceding requirements. Create a word document and import at least three pivot charts. Provide a written analysis (few sentences) to describe any trends or takeaways that you have identified. Page 3 of 3 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