Question

Insurance Company Ratings

Company Name Money Received Money Paid Out Complaints New Customers Rating
Delta Insurance $102,400.00 $113,550.00 143 512
Sunset Insurance $144,000.00 $100,232.00 290 720
Western Insurance $206,000.00 $188,231.00 330 1030
First Insurance $633,600.00 $542,020.00 231 3168
White River Insurance $112,000.00 $143,340.00 71 560
Note: must use formulas to calculate the ratings!

Your company is about to pick an insurance company to serve your employees. You are looking at the rating system for insurance companies based on how much money they have received in premiums, in comparison to how much money they had to pay out. Also looked at is the percentage of complaints compared to new clients. The following ratings are as follows: A, B, C, D, F. The criteria of rating is as follows: • If the ratio of complaints to new customers is less than 10%, the insurance company is graded as A, otherwise • If the ratio of complaints to new customers is less than 20%, the insurance company is graded as B, otherwise • If the ratio of complaints to new customers is less than 30%, the insurance company is graded as C, otherwise • If the ratio of complaints to new customers is less than 40%, the insurance company is graded as D, otherwise • Otherwise, the insurance company is graded as F • Also, if the amount of money paid out is less than what is received, there is an additional 5% penalty tacked on to the original ratio of complaints to new customers. Given the data on several insurance companies (see attachment), evaluate the insurance companies and rate them accordingly. Pick the company that has the highest rating by highlighting the row in Excel. To accomplish this task, you will use the “What-If” analysis. What-if analysis is very crucial to many business firms as it enables effective decision making to take place. What If analysis can be performed in Excel. In this event, we will go over the IF() statement in Excel. It can be used to evaluate an expression, or a value within certain Excel. With this information, you can generate specific outputs based on the evaluations used. For example, if you want to make a decision whether to accept or decline an investment proposal, you must select the criteria (e.g > 100,000) to determine what the decision columns will be. There are nearly an infinite number of methods to utilize IF statements and nested IF statements to make critical business decisions. IF(logical_test, [value_if_true], [value_if_false]) – logical_test - Required. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. value_if_true – this is what you want the cell to output if the logical_test evaluates to TRUE value_of_false – this is what you want the cell to output if the logical_text evaluates to FALSE

Answer 1

In the table, the thing is as below. First insurance has the lowest paid-to-received ratio and complaints-to-newcustomers. Hence that has the best rating

Company name	Money received	Monay paid out	Money paid/rec	Complaints	New customer ratings	Complaints / new customer	Grade
Delta Insurance	$1,02,400	$1,13,550	111%	143	512	33%	D
Sunset Insurance	$1,44,000	$1,00,232	70%	290	720	40%	F
Western Insurance	$2,06,000	$1,88,231	91%	330	1030	32%	D
First Insurance	$6,33,600	$5,42,020	86%	231	3168	7%	A
White River Insurance	$1,12,000	$1,43,340	128%	71	560	18%	B