Question
You have recently been hired as a summer student by KAT Insurance. The CEO, Kitty Kat, asks if you would create visualizations to identify the
You have recently been hired as a summer student by KAT Insurance. The CEO, Kitty Kat, asks if you would create visualizations to identify the star salesperson and show factors that influenced the "bottom line" (contribution margin and net profit) of the company in 2017. She hears that Tableau can even display data on a map! She also asks you to make recommendations for ways to improve profitability based on the information she gives you. Specifically, she wants to know in 2017: 1. What are the total sales in each state? Can you sort total sales by state from the highest to the lowest? Which state has the highest total sales? 2. Can you display total sales and total contribution margins by state on a map? (Hint: contribution margins=sales-variable costs) 3. Which type of insurance contributes most to the company's sales? Can you show the percentage of sales by insurance type? 4. Can you show a chart with total contribution margins by region and insurance type? Can you show a chart that shows contribution margins by each type in each region? What are your observations? 5. Which type of insurance is most profitable (i.e. has the highest average contribution margin ratio (not contribution margin) per insurance policy)? (Hint: each transaction is one insurance policy) 6. Can you show a chart of sale commissions by salesperson and state? Who is the best performing salesperson (highest sales commissions) and who is the worst performing salesperson (lowest sales commissions)? What are your observations? 7. Can you show total sales per capita by insurance type on maps? Could you identify three underdeveloped states that have the most potential to grow? (Hint: total sales per capita=sales/population) 8. Assume total sales per capita remains the same in each state and each insurance type. Based on the population estimate in 2018 and 2019, could you project sales in 2018 and 2019 by state? (Hint: sales only changes with the population) 9. Kitty Kat wants to start an advertisement campaign. She has two choices now: (1) an advertisement based on one specific insurance type (i.e. one of the five insurance types Auto, Disability, Home, Life, Professional) or (2) a general advertisement targeting one specific state. Both campaigns have the same cost. Kitty Kat estimates the insurance typebased advertisement will boost the contribution margins of the advertised type of insurance in ALL states by 0.5 percent. And a general advisement will boost the contribution margin of the targeted state (all insurance types) by 25 percent. Kitty Kat wants to maximize the contribution margins. She wonders which type of Page 3 of 4 campaign she should choose: targeting one insurance type or one state? If so, which insurance type or which state should she target? 10.Kitty Kat would also like your recommendations for any additional data (at least two) that you think should be collected to improve your data analysis next year and why. No need to use charts for this requirement. Kitty Kat emails you a data file and assures you that all the data in the file is "clean" in that there are no missing values. Kitty Kat wants you to use Tableau to visualize all charts. Kitty Kat is also aware of imperfect data issues (i.e. you might not be given all the data/information you needed for your analysis). She is fine if you make any reasonable assumptions
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started