Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Clipboard Font Alignment Number Style H39 M A D N Variable Cost Variable Contribution Percentage Cost Margin 98935 1 Region State Salesperson tourance Type 2

image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
Clipboard Font Alignment Number Style H39 M A D N Variable Cost Variable Contribution Percentage Cost Margin 98935 1 Region State Salesperson tourance Type 2 Southeast AR Frank Auto Southeast AR Frank 4 West CA Kellie Auto 5 Midwest CO Paul Auto New England CT Cry Auto 7 Southeast FL John Auto 8 Southeast FL John Auto Southeast AR Frank Disabay 10 West AZ Kolle Disability 11 Midwest CO Paul Disability 12 Northeast DC Matt Disability 11 Southeast AL John Home 14 New England CT Crowy Home 15 Northeast DE Fred Home 16 Northeast DE Fred Home 17 West Kellie Life 1 West Kele Life 19 Northeast DE Fred Life 20 Southeast PE John uite 21 Southeast John 22 Southeast AL John Medical 22 West AZ Medical 24 New England CT Cro Medical 25 Northeast DE Fred Medical 20 Sales Date of Sale invoke No State Type $935 25 11/8/2017 A 2017-054995 AR AUTO $69193 10/13/2017 A 2017-050573 Ar Auto $ 86109 5/19/2017 A 2017.094590 CA Auto $ 417.15 12/3/2017 - 2017-055654 Auto $ 45.9 11/17/2017 - 2017-056679 1. Auto 5 1.270.15 1/28/2017 A 2017-015376 F Auto $742.20 8/26/2017 A 2017-041947 F Auto $ 640.32 7/11/2017 0 2017033106 Alability $18.15 6/20/2017 0 2017-030104 AZ.Dabilty $ 27.04 4/12/2017 0 2017-018015 CO- $ 451.57 6/16/2017 2017-029374 DC-Dablity $ 1.080.48 1/7/2017 H 2017-001177 AL Home 5 2/15/2017 2017 008234 CT-Home $ 591.00 2/6/2017 2017.006570 Home $ 1,065.446 12/31/2017 - 2017-064872 DE Home $ 21417 10/26/2017 2017-052739 Azute $ 211.20 1/1/2017 2017 03:59 AM $ 229.11 1/17/2017 2017002932 D-Lite $ 1536 12/25/2017 - 2017-063106 File $11.20 7/25/2017 2017-036251 Fulife 3 1.14153 1/7/2017 M 2017001122 AL Medical $ 1.318.00 3/5/2017 M 2017011434 Al Medical 5 1.120.13 7/17/2017 M-2017 034941 CT Medical 31,33295 11/11/2017 M 2017-056072 DE Medical Country USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA 20 29 10 31 File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri - 11 Wrap Text General Paste X cut Copy Format Painter Clipboard BIU. Merge & Center - $ - % . 000 Conditional Formatting Font Alignment 1 Number H39 fx D G M Variable Cost Variable Percentage Cost Paul 1 Region 2 Southeast Southeast 4 West 5 Midwest & New England 7 Southeast 8 Southeast 9 Southeast 10 West 11 Midwest 12 Northeast 13 Southeast 14 New England 15 Northeast 16 Northeast 17 West 18 West 19 Northeast 20 Southeast 21 Southeast 22 Southeast 23 West 24 New England 25 Northeast 26 27 za 29 30 State Salesperson insurance Type AR Frank Auto AR Frank Auto CA Kellie Auto CO Auto CT Crissy Auto FL John Auto FL John Auto AR Frank Disability AZ Kellie Dissability CO Paul Disability DC Matt Disability AL John Home CT Crissy Home DE Fred Home DE Fred Home AZ Kellie Life AZ Kellie Life DE Fred Life FL John Life FL John Life AL John Medical AZ Kellie Medical Crissy Medical DE Fred Medical Sales Date of Sale Invoice No State Type $ 935.25 11/8/2017 A 2017-054995 AR-Auto S691.93 10/13/2017 A 2017-050573 AR Auto $ 861.89 5/19/2017 A-2017-024590 CA-Auto S 447.15 12/3/2017 A 2017-059464 CO-Auto $ 845.98 11/17/2037 A-2017-056679 CT-Auto $ 1,270.15 3/28/2017 A 2017-015376 FL Auto $742.20 8/26/2017 A 2017-041947 FC Auto $ 640.32 7/11/2017 D-2017-033806 AR-Disability $ 488.15 6/20/2017 D-2017-630104 AZ-Disability $ 327.04 4/12/2017 D-2017-018015 Co-Disability $ 451.52 6/16/2017 D-2017-029374 DC-Disability $ 1,080.48 1/7/2017 H-2017-001177 AL Home $ 662.19 2/15/2017H-2017-008234 CT-Home $ 591.06 2/6/2017 H-2017-006570 DE-Home $ 1,065 46 12/31/2017 H-2017-064472 DE Home $ 284.17 10/26/2017 L-2017-052739 A2-Life $ 211.20 8/8/2017 L-2017-038859 AZ-Life $ 229.31 1/17/2017 L-2017-002932 DE-Life $ 153.56 12/25/2017 L-2017-063304 FL-Life $ 211.20 7/25/2017 L-2017-036251 FL-Life $ 1,141.53 1/7/2017 M-2017-001122 AL Medical $ 1.318.00 3/5/2017 M-2017-011434 AZ Medical $ 1.120.73 7/17/2017 M-2017 034941 CT Medical $ 1.332.95 10/13/2017 M 2017.056072 DE Medical Contribution Margin Country USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA thap KAT FA guide tableau student Full 2020 (3) - Word View Help Tell me what you want to do References Mailings Review A AalbCod Aabbcod AaBbcd AaBbccc AaBbocDe Abbc 1. Normal 1 No Space Heading 1 Heading 2 Heading 3 Headin Paragraph Styles KAT Insurance Corporation Introductory Financial Accounting 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 a variety of tools including Excel, Power Band Tableau. As you analyze this case, you will be learning how to drill down into a company's sales data to gain a deeper understanding of the company's sales 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 anonymired to preserve privacy Requirements To follow 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. 2. Rank the states from the highest total insurance sales to lowest total insurance sales. Sort the data by sales, from highest to lowest Which state had the highest sales? What was the total dollar amount? b. Which state had the lowest sales? What was the total dollar amount? c. What is the average amount of insurance sold per state? Which state had the highest average policy amount? Which state had the lowest average policy amount? d. How many insurance policies were sold in each state? Do any states not meet the $800,000 minimum sales level? 3. Rank the regions from the highest total insurance sales to lowest total insurance sales. Sort the data by sales, from highest to lowest Which region had the highest sales? What is the total dollar amount? 5. Which region had the lowest sales? What is the total dollar amount? 4. Who is the leading salesperson in each region? 5. What is the total dollar amount sold for each type of insurance? Create a graph to show total dollar amount of each type of insurance sold by region What does this graph show? 6. Create a map chart that shows total sales for each state. What can you surmise from this map chart? 7. Create an interactive dashboard and include the graphs you have created in requirements 2-6 you need help with how to do this. 8 Analyse all the information you have gathered or created in the preceding requirements to create your video (separate instruction provided) What trends or takewways do you see? Use the dashboard to help create your video go O KAT FA guide tableau student Hall 2020 (5) Word eferences Mailings Review View Help Tell me what you want to do A. AEB... 11 A.A. AaBbceDd AaBbCcDd AaBb C AaBb C AaBbCcDE 1 Normal 1 No Spac. Heading 1 Heading 2 Heading 3 Paragraph Styles KAT Insurance Corporation: Introductory Financial Accounting Data Analytics Case Student Guide for Tableau Project Overview In this case, you will be using Tableau to analyze the sales transactions for an insurance company. You wil first have to find and correct errors in the data set using Excel. Using Tableau, you will then sort the data.join tables, format data, filter data, create a calculated field, create charts, and other items, and will draw conclusions based on these results. A step by step tutorial video to guide you through the Tableau portions of the case analysis is available. General learning objectives 1. Clean the data in a data set 2. Analyse sales trends 3. Interpret findings Tableau learning objectives 1 Sort the data 2. Join two tables 3. Build visualizations by dragging fields to the view 4 Format data types within the view 6. Utilize the Marks card to change measures for sum and average 7. Create a calculated field to counters 8. Sort data in visualization by stated criteria 9. Create a barchart in the view 10. Greate a table in the view 10. Create a map chart to O i Clipboard Font Alignment Number Style H39 M A D N Variable Cost Variable Contribution Percentage Cost Margin 98935 1 Region State Salesperson tourance Type 2 Southeast AR Frank Auto Southeast AR Frank 4 West CA Kellie Auto 5 Midwest CO Paul Auto New England CT Cry Auto 7 Southeast FL John Auto 8 Southeast FL John Auto Southeast AR Frank Disabay 10 West AZ Kolle Disability 11 Midwest CO Paul Disability 12 Northeast DC Matt Disability 11 Southeast AL John Home 14 New England CT Crowy Home 15 Northeast DE Fred Home 16 Northeast DE Fred Home 17 West Kellie Life 1 West Kele Life 19 Northeast DE Fred Life 20 Southeast PE John uite 21 Southeast John 22 Southeast AL John Medical 22 West AZ Medical 24 New England CT Cro Medical 25 Northeast DE Fred Medical 20 Sales Date of Sale invoke No State Type $935 25 11/8/2017 A 2017-054995 AR AUTO $69193 10/13/2017 A 2017-050573 Ar Auto $ 86109 5/19/2017 A 2017.094590 CA Auto $ 417.15 12/3/2017 - 2017-055654 Auto $ 45.9 11/17/2017 - 2017-056679 1. Auto 5 1.270.15 1/28/2017 A 2017-015376 F Auto $742.20 8/26/2017 A 2017-041947 F Auto $ 640.32 7/11/2017 0 2017033106 Alability $18.15 6/20/2017 0 2017-030104 AZ.Dabilty $ 27.04 4/12/2017 0 2017-018015 CO- $ 451.57 6/16/2017 2017-029374 DC-Dablity $ 1.080.48 1/7/2017 H 2017-001177 AL Home 5 2/15/2017 2017 008234 CT-Home $ 591.00 2/6/2017 2017.006570 Home $ 1,065.446 12/31/2017 - 2017-064872 DE Home $ 21417 10/26/2017 2017-052739 Azute $ 211.20 1/1/2017 2017 03:59 AM $ 229.11 1/17/2017 2017002932 D-Lite $ 1536 12/25/2017 - 2017-063106 File $11.20 7/25/2017 2017-036251 Fulife 3 1.14153 1/7/2017 M 2017001122 AL Medical $ 1.318.00 3/5/2017 M 2017011434 Al Medical 5 1.120.13 7/17/2017 M-2017 034941 CT Medical 31,33295 11/11/2017 M 2017-056072 DE Medical Country USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA 20 29 10 31 File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri - 11 Wrap Text General Paste X cut Copy Format Painter Clipboard BIU. Merge & Center - $ - % . 000 Conditional Formatting Font Alignment 1 Number H39 fx D G M Variable Cost Variable Percentage Cost Paul 1 Region 2 Southeast Southeast 4 West 5 Midwest & New England 7 Southeast 8 Southeast 9 Southeast 10 West 11 Midwest 12 Northeast 13 Southeast 14 New England 15 Northeast 16 Northeast 17 West 18 West 19 Northeast 20 Southeast 21 Southeast 22 Southeast 23 West 24 New England 25 Northeast 26 27 za 29 30 State Salesperson insurance Type AR Frank Auto AR Frank Auto CA Kellie Auto CO Auto CT Crissy Auto FL John Auto FL John Auto AR Frank Disability AZ Kellie Dissability CO Paul Disability DC Matt Disability AL John Home CT Crissy Home DE Fred Home DE Fred Home AZ Kellie Life AZ Kellie Life DE Fred Life FL John Life FL John Life AL John Medical AZ Kellie Medical Crissy Medical DE Fred Medical Sales Date of Sale Invoice No State Type $ 935.25 11/8/2017 A 2017-054995 AR-Auto S691.93 10/13/2017 A 2017-050573 AR Auto $ 861.89 5/19/2017 A-2017-024590 CA-Auto S 447.15 12/3/2017 A 2017-059464 CO-Auto $ 845.98 11/17/2037 A-2017-056679 CT-Auto $ 1,270.15 3/28/2017 A 2017-015376 FL Auto $742.20 8/26/2017 A 2017-041947 FC Auto $ 640.32 7/11/2017 D-2017-033806 AR-Disability $ 488.15 6/20/2017 D-2017-630104 AZ-Disability $ 327.04 4/12/2017 D-2017-018015 Co-Disability $ 451.52 6/16/2017 D-2017-029374 DC-Disability $ 1,080.48 1/7/2017 H-2017-001177 AL Home $ 662.19 2/15/2017H-2017-008234 CT-Home $ 591.06 2/6/2017 H-2017-006570 DE-Home $ 1,065 46 12/31/2017 H-2017-064472 DE Home $ 284.17 10/26/2017 L-2017-052739 A2-Life $ 211.20 8/8/2017 L-2017-038859 AZ-Life $ 229.31 1/17/2017 L-2017-002932 DE-Life $ 153.56 12/25/2017 L-2017-063304 FL-Life $ 211.20 7/25/2017 L-2017-036251 FL-Life $ 1,141.53 1/7/2017 M-2017-001122 AL Medical $ 1.318.00 3/5/2017 M-2017-011434 AZ Medical $ 1.120.73 7/17/2017 M-2017 034941 CT Medical $ 1.332.95 10/13/2017 M 2017.056072 DE Medical Contribution Margin Country USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA thap KAT FA guide tableau student Full 2020 (3) - Word View Help Tell me what you want to do References Mailings Review A AalbCod Aabbcod AaBbcd AaBbccc AaBbocDe Abbc 1. Normal 1 No Space Heading 1 Heading 2 Heading 3 Headin Paragraph Styles KAT Insurance Corporation Introductory Financial Accounting 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 a variety of tools including Excel, Power Band Tableau. As you analyze this case, you will be learning how to drill down into a company's sales data to gain a deeper understanding of the company's sales 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 anonymired to preserve privacy Requirements To follow 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. 2. Rank the states from the highest total insurance sales to lowest total insurance sales. Sort the data by sales, from highest to lowest Which state had the highest sales? What was the total dollar amount? b. Which state had the lowest sales? What was the total dollar amount? c. What is the average amount of insurance sold per state? Which state had the highest average policy amount? Which state had the lowest average policy amount? d. How many insurance policies were sold in each state? Do any states not meet the $800,000 minimum sales level? 3. Rank the regions from the highest total insurance sales to lowest total insurance sales. Sort the data by sales, from highest to lowest Which region had the highest sales? What is the total dollar amount? 5. Which region had the lowest sales? What is the total dollar amount? 4. Who is the leading salesperson in each region? 5. What is the total dollar amount sold for each type of insurance? Create a graph to show total dollar amount of each type of insurance sold by region What does this graph show? 6. Create a map chart that shows total sales for each state. What can you surmise from this map chart? 7. Create an interactive dashboard and include the graphs you have created in requirements 2-6 you need help with how to do this. 8 Analyse all the information you have gathered or created in the preceding requirements to create your video (separate instruction provided) What trends or takewways do you see? Use the dashboard to help create your video go O KAT FA guide tableau student Hall 2020 (5) Word eferences Mailings Review View Help Tell me what you want to do A. AEB... 11 A.A. AaBbceDd AaBbCcDd AaBb C AaBb C AaBbCcDE 1 Normal 1 No Spac. Heading 1 Heading 2 Heading 3 Paragraph Styles KAT Insurance Corporation: Introductory Financial Accounting Data Analytics Case Student Guide for Tableau Project Overview In this case, you will be using Tableau to analyze the sales transactions for an insurance company. You wil first have to find and correct errors in the data set using Excel. Using Tableau, you will then sort the data.join tables, format data, filter data, create a calculated field, create charts, and other items, and will draw conclusions based on these results. A step by step tutorial video to guide you through the Tableau portions of the case analysis is available. General learning objectives 1. Clean the data in a data set 2. Analyse sales trends 3. Interpret findings Tableau learning objectives 1 Sort the data 2. Join two tables 3. Build visualizations by dragging fields to the view 4 Format data types within the view 6. Utilize the Marks card to change measures for sum and average 7. Create a calculated field to counters 8. Sort data in visualization by stated criteria 9. Create a barchart in the view 10. Greate a table in the view 10. Create a map chart to O

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamental Managerial Accounting Concepts

Authors: Edmonds, Tsay, olds

6th Edition

71220720, 78110890, 9780071220729, 978-0078110894

More Books

Students also viewed these Accounting questions