70.2K

Verified Solution

Link Copied!

8/files/99805016?module item id=18382277 A guide tableau student.docx KAT MA guide tableau student.docx (31.9 KB) IU TOHUw are le requirements 101 analyzing als recursor die als seu. 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. Which Insurance Type had the highest total contribution margin? b. Which Insurance Type had the lowest 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? 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. Do these rankings agree with the rankings you found in Requirement 3? Should these two rankings always be the same? Explain 5. 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. What trends or takeaways do you see? Explain. ous ENG E O OB KATirane-Du-Sat Alle B-6 Home D Page Layout View Help POF d Share Cardinal for the style So The D Gate 3 H D Variable Cost Variable Percentage Cont + Camibution Marghe Country Starte Salesperson Inn Type Sales Date of Sale Inice No State Type 2 M TX Tom Auto 200 3/11/2017 - 2017-000010 TX Auto > Southeast TH Jenn Auto 53241 1/1/2037 A 3011000012 TWO 4 Tiew and Ct Ch TURUT 1/1/2007 A 2017000016 CT-Auto singland Oy Auto 21.35 1/1/2017 A 2017-000017 MAA sehen 6 John 49.51 1/1/2017 A 3017000 GA Auto 7 West UT Aute 52:37 11/2017 2017000009 Utle We NV Ke ht 1/1/2017 A 0000013 Nw Auto We WA Peter Auto 94.11 1/1/2017 2017000006 WA 1 North Cara MO War BOL 1/11/2017 2017000007 M Ada 11 Saha MS A 47635 11/2017 A 211000041 MS Auto Narthe Nel 468.32 1/1/2017 A 2017 000046 MIA 13. New ME Sale 435 1/11/2017 A 20300000 MAH West M De 509.05 1/1/2017 12.000 MA 19 DE Tred Ah 520 1/12/2017 A 2017-000041 DFB 16 New Era ME Auto 1/1/2017 A 3010S MEA What TH Auto 665 1/1/07 A 2005-2000 10 Now Auto SU 1/2013 10175 at OM M 1/11 2017 000 verland w ht 1 2017 Mate Mantec USA USA USA US USA USA LA USA USA USA USA MID USA USA USA LA USA Type to search O HOW R 5 8/files/99805016?module item id=18382277 A guide tableau student.docx KAT MA guide tableau student.docx (31.9 KB) IU TOHUw are le requirements 101 analyzing als recursor die als seu. 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. Which Insurance Type had the highest total contribution margin? b. Which Insurance Type had the lowest 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? 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. Do these rankings agree with the rankings you found in Requirement 3? Should these two rankings always be the same? Explain 5. 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. What trends or takeaways do you see? Explain. ous ENG E O OB KATirane-Du-Sat Alle B-6 Home D Page Layout View Help POF d Share Cardinal for the style So The D Gate 3 H D Variable Cost Variable Percentage Cont + Camibution Marghe Country Starte Salesperson Inn Type Sales Date of Sale Inice No State Type 2 M TX Tom Auto 200 3/11/2017 - 2017-000010 TX Auto > Southeast TH Jenn Auto 53241 1/1/2037 A 3011000012 TWO 4 Tiew and Ct Ch TURUT 1/1/2007 A 2017000016 CT-Auto singland Oy Auto 21.35 1/1/2017 A 2017-000017 MAA sehen 6 John 49.51 1/1/2017 A 3017000 GA Auto 7 West UT Aute 52:37 11/2017 2017000009 Utle We NV Ke ht 1/1/2017 A 0000013 Nw Auto We WA Peter Auto 94.11 1/1/2017 2017000006 WA 1 North Cara MO War BOL 1/11/2017 2017000007 M Ada 11 Saha MS A 47635 11/2017 A 211000041 MS Auto Narthe Nel 468.32 1/1/2017 A 2017 000046 MIA 13. New ME Sale 435 1/11/2017 A 20300000 MAH West M De 509.05 1/1/2017 12.000 MA 19 DE Tred Ah 520 1/12/2017 A 2017-000041 DFB 16 New Era ME Auto 1/1/2017 A 3010S MEA What TH Auto 665 1/1/07 A 2005-2000 10 Now Auto SU 1/2013 10175 at OM M 1/11 2017 000 verland w ht 1 2017 Mate Mantec USA USA USA US USA USA LA USA USA USA USA MID USA USA USA LA USA Type to search O HOW R 5

Answer & Explanation Solved by verified expert

See Answer