Overview: In this module, you learned about uncertainty and probability. In this exercise, you will apply your understanding to different problems. You will become more familiar with the concepts and build an interface in QlikView to use these concepts.

Prompt: The management is impressed with the visualizations added to the dashboard and have asked you to add another feature. They would like to see a way to investigate what-if questions related to the insurance industry. You will build a Container object in QlikView that will allow management to adjust the various factors involved in determining the appropriate premiums for medical malpractice insurance. The executives have provided you with the following scenario, based on question E2 on pages 116 and 117 of Data-Driven Business Decisions:

An obstetrics specialist (baby deliverer) faces a small but nonzero chance of being sued for negligence. You estimate that the chance of being sued in any year is 5%. [If the client is sued] you estimate that there is a 30% chance the client would win the case with costs awarded, a 40% chance the client would win the case but have to pay legal fees of $100,000 and a 30% chance the client would lose the case and have to pay a total of $1,000,000, including all legal fees.

(a) How much would a reasonable yearly insurance premium be?

(b) Pegasus Insurance adjustors are worried about the increasing trend in litigation. They think that the probability of being sued could be as high as 10%. What would an appropriate premium be?

(c) The adjustors also estimate that payouts when clients lose are more likely to be $5,000,000 than $1,000,000 and want to insure on this basis. How much will the premium be?

(d) Pegasus researchers estimate that orthopedic specialties have the following conditions: (1) the chance of being sued is 7%, (2) the chance of winning but without costs awarded is 35% [legal fees ($100,000) are the insurance companies responsibility], (3) the chance of losing is 35%, and (4) the amount awarded when a case is lost averages $2,000,000. What is a fair premium for this specialty?

To answer these questions, refer to the What-if Tutorial in the Assignment Guidelines and Rubrics section of the course. Follow the tutorial to create a Container object on the dashboard. This will create the tools that the Pegasus executives would use to investigate the scenarios presented.

Your submission should include the following:

Software Application

Include a screen shot of the final what-if dashboard.

Scenario Analysis

Include a justified recommendation for the appropriate premium for each situation presented in the scenario.

WHAT IF TUTORIAL:

Creating Variables in QlikView

Create probability variables
In the Settings menu, select Variable Overview.
Click Add.
Create the variable sue_percent to indicate the likelihood of a suit being brought to the physician.
Create the remaining variables:
fees_awarded case won and all legal fees are paid by the plaintiff
fees_legal case won, but client/insurer responsible for the legal fees
loss case lost, client/insurer responsible for damages and legal fees
Click OK.

Creating Input Boxes in QlikView

Create the first input box from the Qlikview main screen.
Right-click and select New Sheet Object
Select Input Box.
General Tab:
In the Title field, type Probability of Lawsuit
In the Object ID field, type sue_percent
From the Available Variables box, Add sue_percent to the Displayed Variables
Highlight sue_percent and Type % Lawsuit in the Label field

Constraints Tab:
In the Settings for Selected Variable field, type 0.05 (this is the 5% chance of the obstetrician being sued)
In Value List section, choose Predefined Values in Drop-down
In Predefined Values section:
Check Number Series
From: 0 To 0.21
Step: 0.01

Numbers Tab:

• Select Fixed to Decimals with a value of 2

Caption tab:

• Check the Show Caption box. Type Probability of Lawsuit into the Title Text field.
• Follow the previous directions to Create Input Box for the remaining variables. Use the values from Table 1:

Table 1

General Tab	Constraints Tab	Numbers Tab	Caption Tab
Title	Displayed Variable	Object ID	Label	Value for Selected Variable	Value List: Predefined Values in Drop-down	Number Series	Predefined Values	Fixed to ___ Decimals	Show Caption	Title
Probability of Legal Fees Awarded	fees_awarded	Probability of Legal Fees Awarded	0.30	R	R	From: 0 To: 0.66 Step: 0.05	2	R	Probability of Legal Fees Awarded
Probability of Assessed Legal Fees	fees_legal	Probability of Assessed Legal Fees	0.40	R	R	From: 0 To: 0.66 Step: 0.05	2	R	Probability of Assessed Legal Fees
Probability of Loss	loss	Probability of Loss	0.30	R	R	From: 0 To: 0.66 Step: 0.05	2	R	Probability of Loss

Now we need to create cost variables.
In the Settings menu, select Variable Overview.
Click Add.
Create the variable cost_awarded
Create the remaining variables:
cost_legal
cost_loss
Click OK.

Creating Input Boxes

Create the first selection field
Right-click and select New Sheet Object
Select Input Box.
General Tab:
In the Title field, type Cost of Fees Awarded
From the Available Variables box, Add cost_awarded to the Displayed Variables
Highlight cost_awarded and type Cost of Fees Awarded in the Label field
In the Object ID field, type cost_awarded

Constraints Tab:
In the Settings for Selected Variable field, type 0 (as the case was won with legal fees paid by plaintiff)
In the Input Constraints section, select Predefined Values Only
In the Value List section, choose No List
In Predefined Values section:
Select Listed Values and fill the field with a value of 0

Number Tab:
Select Money with a Format Pattern of $#,##0;($#,##0) (This is the default with the .00 cent place holders removed)
Caption Tab:
Check the Show Caption box.
Type Cost of Fees Awarded
Click OK.

• Add more choice fields:
• Repeat the above procedures using the values from Table 2.

Table 2

General Tab	Constraints Tab	Numbers Tab	Caption Tab
Title	Displayed Variable	Object ID	Label	Value for Selected Variable	Input Constraints	Number Format Settings	Format Pattern	Show Caption	Title
Cost of Legal Fees	cost_fees	Cost of Legal Fees	100,000	R Standard: Numbers	R Money	$#,##0;($#,##0)	R	Cost of Legal Fees
Cost of Loss	cost_loss	Cost of Loss	1,000,000	R Standard: Numbers	R Money	$#,##0;($#,##0)	R	Cost of Loss

Create Expected Value variables In the Settings menu, select Variable OverviewClick AddCreate the variable expected_fees_awardedHighlight the new variable and in the Definition section type:sue_percent*fees_awarded*cost_awarded Create the remaining variables: Click OK

Create calculated Expected Value boxes:Right-click and select New Sheet ObjectSelect Text ObjectIn the Object ID field, type exp_awardedClick OK

• Create two additional Text Objects with the following:

General Tab		Caption Tab
Object ID: exp_legal	Text: ('Expected Cost for Legal Fees Assessed: ') &num($(expected_fees_legal),'$#,##0')	R Show Caption 'Expected Cost for Fees Assessed
Object ID: exp_loss	Text: ('Expected Cost for Loss: ') &num($(expected_fees_loss),'$#,##0')	R Show Caption 'Expected Cost for Loss

Creating Containers in QlikView

Create a container for the What-If Parameters
Right-click and select New Sheet Object
Select Container
General Tab:
From the Existing Objects box, add (in order): fees_awarded, cost_awarded, and exp_awarded to the Objects Displayed in Container box [Note: the numbers following the IB may be different than these]
Presentation Tab:
Change the Container Type to Grid.
Change Columns to 3 and Rows to 1.
Click OK.

Repeat these steps to create two additional containers:
Objects Displayed in Container (in order): fees_legal cost_legal exp_legal Objects Displayed in Container (in order): fees_loss cost_loss exp_loss

Finally, create the summation figures for executives to calculate Premiums.

New variables:	In the Settings menu, select Variable Overview
Click Add.	Create the variable expected_ total

In the Definition section, type: Add the variable profit (No Definition)Add the variable premiumIn the Definition section, type: Add a Total Cost box: New Sheet Object Text ObjectCreate the Profit selection box:Add New Sheet Object Input Object with the following parameters: General

Tab

Constraints

Tab

Numbers Tab

Caption

Tab

Title

Displayed Variable

Label

Value for Selected Variable

Value List: Predefined Values in Drop-down

Number Series

Predefined Values

Fixed to

___ Decimals

Show Caption

Title

Profit

profit

Profit

0.05

R

R

From: 0

To: 0.26

Step: 0.01

2

R

Profit

• Create the Premium calculation box
• Add a New Sheet Object Text Object

General Tab		Caption Tab
Object ID: premium	Text: ($(premium),'$#,##0')	R Show Caption Caption: Premium

Add New Sheet Object Container with the following parameters:

Objects Displayed in Container (in order): total_exp IB12 Profit premium

Presentation: Container Type = Grid Columns = 3; Rows = 1

The final piece in creating the dashboard is clean-up/styling:

After creating containers, you will be left with two versions of the objectthe original object and the one in the container. To clean things up:Right-click on the original object and choose Linked Objects Unlink Objects.Right-click on the object again and choose Remove.These steps can be completed for each object individually or on multiple objects if you select them together.

• Finally, make any aesthetic changes you see fitaligning objects, colors, fonts, etc.

One example of a finished dashboard shown below:

CreatingVariables in QlikView

1) Create probability variables
a. In the Settings menu, select Variable Overview.
b. Click Add.
c. Create the variable sue_percent to indicate the likelihood of a suit being brought to the physician.
d. Create the remaining variables:
i. fees_awarded case won and all legal fees are paid by the plaintiff
ii. fees_legal case won, but client/insurer responsible for the legal fees
iii. loss case lost, client/insurer responsible for damages and legal fees
e. Click OK.

Creating Input Boxes in QlikView

2) Create the first input box from the Qlikview main screen.
a. Right-click and select New Sheet Object
b. Select Input Box.
General Tab:
i. In the Title field, type Probability of Lawsuit
ii. In the Object ID field, type sue_percent
iii. From the Available Variables box, Add sue_percent to the Displayed Variables
iv. Highlight sue_percent and Type % Lawsuit in the Label field

Constraints Tab:
v. In the Settings for Selected Variable field, type 0.05 (this is the 5% chance of the obstetrician being sued)
vi. In Value List section, choose Predefined Values in Drop-down
vii. In Predefined Values section:
1. Check Number Series
2. From: 0 To 0.21
3. Step: 0.01

Numbers Tab:
viii. Select Fixed to Decimals with a value of 2
Caption tab:
ix. Check the Show Caption box.
x. Type Probability of Lawsuit into the Title Text field. .
c. Click OK