New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 Tetra Sammander Insurance ANALYZE AND CHART FINANCIAL DATA GETTING STARTED 1. 2. 3. 4. 5. PROJECT STEPS Geneva

Wood is the owner of Tetra Sammander Insurance. Geneva is preparing year- end financial data to summarize and visualize the company's position and modeling costs of a new life insurance product that the company is considering offering. She has created charts to illustrate some of her data and is using functions to calculate the future value of insurance policies. 6. O 7. O Save the file NP_EX365_2021_EOM4-2_FirstLastName_1.xlsx as NP_EX365_2021_EOM4-2_FirstLastName_2.xlsx Edit the file name by changing "1" to "2". If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. With the file NP_EX365_2021_EOM4-2_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. If cell B6 does not display your name, delete the file and download a new copy. Switch to the Revenues worksheet. In the range F6:F10, add line sparklines based on the data in the range B6:E10 and then add markers. Apply the Green, Accent 6 sparkline color and the Green, Accent 6, Darker 25% marker color. Apply a Gradient Fill, Green Data Bar conditional formatting rule into the range G6:G10. Geneva wants a clustered column chart representing the total revenue for each insurance type by year. Create a 2-D clustered column chart based on the range A5:E9. Resize and reposition the chart so that the upper-left corner is located within cell H5 and the lower-right corner is located within cell 024. Enter Yearly Revenues by Insurance Type as the chart title, then bold the title, and change the font size to 16 point. Apply a Green, Accent 6 border to the plot area of the chart and change the width of the border to 1.5 point. Apply a Glow Shape Effect to the chart area using 5 point Green, Accent Color 6. In the 2024 Revenues by Insurance Type 3-D pie chart (in the range H26:043), change the legend position to bottom. Add data labels that contain only percentages (not values) and that use the Center position. In the Total Revenues by Year 2021-2024 line chart (in the range A11:G28), change the minimum bound of the vertical axis to 2500000, change the major units of the vertical axis to 500000 change the minor units to 250000, and then add Inside tick marks for the minor units on the vertical axis. Apply a solid fill using the Orange, Accent 2, Lighter 80% fill color to the chart area. Apply the Green, Accent 6, Darker 50% font color to the chart title, and change the font to 16 point bold. Apply the Green, Accent 6 color to the data line, and change the width to 2.25 point. CENGAGE New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 8. 9. In the Insurance Type Contribution to Annual Revenue 2021-2024 stacked column chart (in the range A29:G46), remove the data series labeled Trends from the chart. Apply a Shape Fill to the chart area using the Orange, Accent 2, Lighter 80% fill color. Apply a Solid Line border to the chart area with a White, Background 1, Darker 35% border color. Create a 2-D Column chart based on the range A5:E10. Move the 2-D Column chart to the Revenue Summary worksheet, and then resize and reposition the chart so the upper-left corner is located within cell A5 and the lower-right corner is located within cell K22. Select Switch Row/Column to edit the chart data source. Change the chart type to create a Custom Combination Combo chart. Represent the following data series as a Clustered Column chart: Life, Homeowners, Auto, and Travel, and represent the Total data series as a Line chart using the Secondary Axis. [Mac hint - Keep the main chart a Clustered Column chart, select the Total data series, change the chart type to a Line chart, and plot the series on the Secondary Axis.] 10. Enter Policy Type and Total Yearly Revenue as the chart title. Add axis titles to the chart, enter Revenue by Policy Type as the left vertical axis title, and then enter Total Revenue as the right vertical axis title. [Mac hint - Add Primary Vertical and Secondary Vertical Axis titles.] Delete the horizontal axis title placeholder, if necessary. 11. Switch to the Payments worksheet, select the range A6:B156 and create a Histogram chart. Resize and reposition the chart so that the upper-left corner is located within cell D5 and the lower-right corner is located within cell N23. 12. For the horizontal axis, change the bin width to 250, add an overflow bin with a value of 1500, and change the number format to the Currency number format with decimal places to 0. Enter Distribution of Payout Amount per Policy in 2024 as the title of the chart, and format the chart title using bold. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project. CENGAGE New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 Final Figure 1: Revenues Worksheet Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved. 2 5 6 18 19 20 21 7 8 Auto 9 Travel 10 Total 11 12 13 19 14 15 16 17 31 32 33 34 35 36 37 38 39 42 43 44 Insurance Type 45 46 47 Life $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $- $ B 2021 2022 2023 2024 528,153 $ 828,672 $919,825 $ 670,553 772,046 936,491 1,475,911 1,793,231 1,690,384 1,085,226 1,658,226 1,038,049 23,831 21,185 33,642 24,189 $ 3,014,414 $ 2,871,574 $ 4,087,604 $ 3,526,022 2021 Tetra Sammander Insurance Revenues by Product Area 2021-2024 2021 Total Revenues by Year 2021-2024 2022 2022 2023 Insurance Type Contribution to Annual Revenue 2021-2024 Revenues Revenue Summary Payments + 2023 Life Homeowners Auto Travel Trends 2024 Total $ 2,947,203 4,977,679 5,471,885 102,847 $13,499,614 G 2024 Final Figure 2: Revenue Summary Worksheet H $2,000,000 $1,800,000 $1,600,000 $1,400,000 $1,200,000 $1,000,000 $800,000 $600,000 $400,000 $200,000 $- I J CENGAGE 2021 K Yearly Revenues by Insurance Type 2022 29% L Life Homeowners Auto Travel 1% M 2024 Revenues by Insurance Type 19% 51% 2023 N Life Homeowners Auto Travel 2024 O New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 < Revenue by Policy Type 4 A 4 5 6 ZA10661 7 AC90797 8 XJ12955 9 YK84968 10 WW23072 11 DG41533 12 HN13664 13 QE93359 14 SU88195 15 TS61649 16 HE37809 17 IJ58657 18 IQ60494 19 LX33566 20 1040160 21 RJ28180 22 JA60855 23 NY74376 24 AK48460 B $2,000,000 $1,800,000 $1,600,000 $1,400,000 $1,200,000 $1,000,000 $800,000 $600,000 $400,000 $200,000 $- Policy Number 2021 Final Figure 3: Payments Worksheet Payout $329.38 $0.00 $0.00 $25,000.00 $0.00 $0.00 $0.00 $0.00 $430.44 $771.45 D Tetra Sammander Insurance Revenue Summary 2021-2024 $0.00 $0.00 $735.07 $25,000.00 $0.00 $604.52 $0.00 $0.00 Life Revenues Revenue Summary Payments Policy Type and Total Yearly Revenue D E Tetra Sammander Insurance Payments per Policy (weighted sample) 2024 120 100 E 80 60 40 2022 20 Homeowners 0 [$0, $250] $783.59 Revenues Revenue Summary Payments + F Auto G 2023 H H ⠀ 4 Travel -Total I 2024 J CENGAGE K $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $- L Distribution of Payout Amount per Policy in 2024 ($250, $500] ($500, $750] ($750, $1,000] ($1,000, $1,250]) ($1,250, $1,500] M K Total Revenue > $1,500 N