i need help with my excel analyzing

QUESTION

i have provided a excel file, where there are my results, i am missing some things in the project

Frequency analysis. Your first task is to analyze claim frequency by the rating variables available
for your data. Analyze each variable separately, looking at frequency = claim counts / exposure.
a) What are the frequency relativities for each variable?
b) How does the outer product of these relativities compare to the empirical relativities for
these variables? You can obtain the empirical frequencies by creating a cross tabulation
of the variables.
c) Are there any low-volume cells that are candidates for being combined? What criteria
could you use to select these candidates?
2. Severity analysis. In this section, you will look at the severity (Tot_Inc/Counts) for the rating
variables. The format and questions are similar to the frequency analysis:
a) Create tables of total loss, claim counts and severity for each variable.
b) Convert both severity tables to relativities to their respective base classes. What
stands out here

c)Create a cross tabulation of total loss, claim counts and severity by territory and rating class. What stands out here? Are there any combinations of the variables that are rare?

d) Convert the severity table you created in d) to a relativity table based on territory 4
and rating class A. Create an alternative relativity table using the outer product of
relativities from the tables in part c). The latter implicitly assumes that these
variables are independent. How do these two tables compare? Does it appear
appropriate to assume that these variables are independent?
e) Comment on the differences between your conclusions on claim frequency and
severity. Is there justification for considering frequency and severity separately, or
does one dimension appear to capture most of the differences in these classification
variables?
3. Pure premium analysis. In this section, you will analyze pure premium (Inc_loss/expos). Since
you have already looked at the components separately, you can go directly to the pure premium
tables.
a) Create a table of pure premium for each rating variable. Convert them to a relativity
table. How do these relativities compare to the frequency and severity relativities for
the same variable?
b) Create the cross tabulation of pure premium by territory and rating class. Convert this to
a relativity table. How do these relativities compare to the frequency and severity
relativities?
c) Create a pure premium relativity table from the product of the relativities in part 4a).
What can you conclude about using separate multiplicative factors to predict pure
premium?
4. Minimum Bias Method. Since we don’t know the relativities currently used in the rating plan,
the adjusted pure premium method cannot be applied directly. However, you could try using
the estimated relativities for territory to compute the adjusted relativities for rating class, and
vice versa.
a) Try this method. Does it improve the results (in the sense of moving the estimates closer
to the “true” values?)
b) Chapter 9 of the Werner & Modlin text describes a “minimum bias” method for
estimating relativities. Your answer to part a) is the first step in this minimum bias
method. You can compute additional steps using the relativities from the previous step
to compute the adjusted relativities in the current step. Do this and run additional
iterations until there is no difference in the result for the first 3 decimal places.
c) Check to see if the outer product of the relativities produced by this method produce a
result closer to the actual relativities that you produced in step 3
d) Prepare a rate table. You may convert pure premiums to rates using the “all variable”
method with a permissible loss ratio of .73.

5. Credibility Adjustment. Prepare an alternative rate table using credibility to compute the
classification pure premiums. Use the traditional method (“square root rule”) based on claim
counts with 1,082 claims as the standard for full credibility. The complement of credibility
should be the pure premium for the entire state (country). Covert these estimates to rates,
adjusted for any off-balance introduced by the credibility process. How do these rates compare
with the minimum bias rates? Comment on differences? Which do you think best meets
ratemaking objectives?

Get your college paper done by experts

Do my question How much will it cost?

Place an order in 3 easy steps. Takes less than 5 mins.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *