Ancrum_NaQuay_Assignment7

.xlsx

School

University of North Carolina, Greensboro *

*We aren’t endorsed by this school

Course

210

Subject

Statistics

Date

Apr 3, 2024

Type

xlsx

Pages

Uploaded by CommodoreGoatPerson762 on coursehero.com

Assignment Steps to Pe Step 1 2 3 4 5 6 1 2 3 4 5 6 7 1 2 3 4 5 6 You are wor You are expl

1 2 3 4 1 2 3 4 5 6 7 8 9 10

7 erform: Instructions Select the range B4:D33 and sort the column percentage column (highest to lowest). Select the top 10 Category and Percentage rows and create a bar chart in the range F4:N18 Sort the categories in the reverse order Change the maximum value of the horizontal axis to 22% or 0.22 Change the minimum value of the horizontal axis to 0. Change the chart title to "Top 10 Fraud Categories" Select data B4:F12 and create a 100% Stack Column chart in the range H5:Q21 (Chart 1 Placeholder). The default chart is across age group; Switch Row/Column to display the distribution across years. Change the chart title to "Agewise Fraud Distribution across years" Select data B4:C12 and create a Bar of Pie Chart in the range B16:G31 (Chart 2 Placeholder). Change the chart title to "Agewise Distribution for FY 2016" Remove both horizontal and vertical gridlines Add Linear Trendline to the scatter plot Display equation on the chart. Display R square values on the chart. Table 4 has data on reported fraud losses in $1 - $10,000 + Range. rking as an analyst with the Fraud Analytics department. loring data related to fraud and trying to get an understanding using charts, graphs and pivot table. On the Fraud worksheet, perform the following Table 1 shows the number of frauds reported across various categories for the year 2019. Create a bar chart to display the top 10 fraud categories. The chart should look similar to the image in the Sample Answers sheet. On the AgeWise worksheet, perform the following Table 2 has age-wise fraud data across years. You need to show the distribution using different types of appropriate charts. Change the chart style to Style 8 (note that the style may differ in your Excel version - choose a style similar to the sample answer image). The chart should look similar to the Chart 1 Placeholder image in the Sample Answers sheet. Change the chart style to Style 3 (note that the style may differ in your Excel version - choose a style similar to the sample answer image). The chart should look similar to the Chart 2 Placeholder image in the Sample Answers sheet. On the QrtrWise worksheet, perform the following Table 3 has Identity theft and fraud data across various quarters. You are trying to understand the relationship between Identity theft (independent variable) and Fraud (dependent variable). Select B4:C20 and Create a scatter plot with Identity theft as the independent variable and Fraud as the dependent variable. The plot should be in the yellow shaded area F4:M20. Change the chart title to "There is a positive relationship between identity theft and frauds". Your chart will be similar to the Chart shown in the Sample Answers sheet. On the Fraud$ worksheet, perform the following

Add the chart title as "Frequency Distribution of Amount Lost". Table 5 has data on reported fraud losses in $1 - $1,000 Range. Add the chart title as "Frequency Distribution of Amount Lost between $1 to $1000". Table 6 has the state-wise fraud under the top 10 categories. Select the data in the range A4:D524 and create a Pivot table in the cell range A4 in the sheet Top10_Pivot. Add state to the Rows area Add Percentage to the Values area and summarize values fields by sum function Change the number format of "Sum of Percentage" to percentage with 2 decimal places Find the state having maximum frauds Find the state having minimum frauds Save the file and upload it Total Points Select data A5:B16 and insert a column chart to show the frequency distribution of the amount lost in frauds. The chart should be in the yellow shaded area D4:L21. Select data A24:B34 and insert a column chart to show the frequency distribution of the amount lost in frauds. The chart should be in the yellow shaded area D23:L40. On the Top10 & Top10_Pivot worksheet, perform the following Using the Max function, write a formula in cell F7 of Top10_Pivot Sheet to find the maximum "Sum of Percentage" value. Do not select the Grand Total range in the function . Using the MATCH function only, write a formula in cell G7 of Top10_Pivot Sheet to find the Row Number of Maximum Sum of percentage. Using the INDEX function only, write a formula in cell H7 to find the state name having the maximum "Sum of Percentage". The formula should include the value(row number) that is calculated in G7. Using the Min function, write a formula in cell F10 of Top10_Pivot Sheet to find the minimum "Sum of Percentage" value. Do not select the Grand Total range in the function. Using the MATCH function only, write a formula in cell G10 of Top10_Pivot Sheet to find the Row Number of Minimum "Sum of Percentage". Using the INDEX function only, write a formula in cell H10 to find the state name having the minimum "Sum of Percentage". The formula should include the value(row number) that is calculated in G10.

Maximum Points 2 7 2 1 1 1 8 2 1 2 8 1 2 7 1 2 1 1 1

7 1 7 1 8 2 2 1 2 4 4 2 4 4 100

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help