Copy the file name Motor Claims from the shared folder and paste it on a new work sheet. Carry out the following Analysis on the given data.
1. Frequency distribution analysis for Motor Claims with Veh Group and also with Driver’s Age.
2. Calculate Average, Standard Deviation, Minimum and Maximum values of Claims.
3. Perform Descriptive Analysis using the tool: Data Analysis for Claims Data.
4. Plot a claims distribution with claim size intervals of 10000, 20000, 30000, 50000, and 100000.
Comment on the analysis carried out.
Guidelines:
I. Claims Frequency analysis for Motor Claims:
1. Select the option “Data” from the menu and click Pivot table and Pivot chart wizard.
2. Select pivot chart with pivot …show more content…
Select new worksheet and layout option. Then drag variable Vehgroup (small icon on the right) into row and Claims data into data column. Then double click Claims in the data column, select count function and click O.K.
5. Select the new worksheet option. Then Click finish button in the pivot table box.
II. Calculation of Claims Statistics (Average, Stdev., Min & Max values):
1. Type the given formula to calculate Average: =AVERAGE (A1:A1034).
2. Type . =STDEV(A1:A1034) to calculate standard deviation of claims
3. To find out Minimum and Maximum values of claims, type =MIN(A1:A1034) & =MAX(A1:A1034) on the data sheet..
III. Descriptive Analysis for motor claims:
1. Select the option “Tools” from the menu and select data analysis (in case data analysis function is not available – then you double click ‘Add-in’ option and select Analytical Tool Pak option, then you go to tools and select data analysis.
2. Select Descriptive analysis, then define cell range (A1:A1034) for the under the label “Input Range” for the variables (Claims) for which you want to perform descriptive analysis..
3. Select labels option and summary statistics.
4. Select output range and type the cell address, where you want to place the