Chapter 10

Content Type

User Generated

User

Whunzc09

Subject

Business Finance

Description

Hello, Please complete all of the steps for the exercises attached. Will upload student data files when accepted.

Unformatted Attachment Preview

iPad 10:16 PM * 69% Succeeding in Business with Microsoft Excel 2013: A Problem-S... Aa » Q ม Using Data Tables and Excel Scenarios for What-lf Analysis Chapter 8 its Quotes workbook to include information on its cars’ gas mileage and an estimate of the gas expense related to customers' planned trips. All that remains to be added is information specific to each of the car types, and a more detailed analysis of the interaction between gas prices and total cost of transportation, and the gas mileage of cars and its impact on the total cost of transportation. Complete the following: 1. Open the workbook named Quotes.xlsx located in the Chapter 8 folder, and save the file as Transport Quotes.xlsx. 2. Examine the Analysis worksheet, and apply appropriate names to cells D17:D20. 3. Set up the structure of a one-variable data table on the Analysis worksheet that shows the car charge, mileage charge, gas expense, and total transportation expense given changes in the average price of gas from $2.20 to $3.80 by increments of $0.20. 4. Instruct Excel to complete the one-variable data table. TROUBLESHOOTING: In order to complete the one-variable data table successfully, be sure to select the correct data table range. If you instruct Excel to complete an incorrect range, you cannot just delete any “extra” results that might have appeared at the bottom or right of the intended data table area. When you try to do so, you will receive the “Cannot change part of a data table” error message. You cannot delete only some results from a filled-in data table; you must delete all results values from a one- variable data table if you want to delete any results. 5. Add headings and basic formatting to the data table so it has a more professional appearance. 6. A few rows below the one-variable data table, create the structure for a two-variable data table that shows the total projected transportation expense given the interac- tion between average gas prices from $2.20 to $3.80 by increments of $0.20 and miles per gallon from 18 to 28 by increments of 2 miles per gallon. 7. Instruct Excel to complete the two-variable data table. 8. Add headings and some basic formatting to the data table so it has a more professional appearance. 9. Save and close the Transport Quotes.xlsx workbook. 8 Chapter Exercises Level 2 - Evaluating Expansion Financing Options for Granite City Books $ Finance Granite City Books is planning a $2.5 million expansion of its facilities. It needs to evaluate its options for financing the expansion. The company's bank might not allow it to obtain more long-term debt financing if its debt-to-equity ratio gets too high. Alternatively, common stockholders might be displeased if their ownership rights become diluted by issuing a substantial amount of preferred stock, or even additional common stock if it's not issued on a pro rata basis. A workbook has been started that contains a basic Balance jij C - 547 iPad 10:16 PM * 69% Succeeding in Business with Microsoft Excel 2013: A Problem-S... Aa -» a D Chapter 8 Using Data Tables and Excel Scenarios for WhatIf Analysis Sheet, solvency, and capital structure ratio data. Your task is to create scenarios for each financing alternative and prepare reports that Granite City Books' bank and management can use to compare the various financing alternatives. Complete the following: 1. Open the workbook named Granite.xlsx located in the Chapter 8 folder, and save the file as Granite City Expansion.xlsx in the same location. 2. In the Options worksheet, apply appropriate names to the worksheet's scenario changing cells as described in Table 8.5. Table 8.5: Descriptions of the changing cells Changing Cell Description F9 Change in assets F12 Change in long-term debt F14 Change in the dollar amount of common stock issued F15 Change in the dollar amount of preferred stock issued © 2014 Cengage Learning 3. Create four scenarios in the Options worksheet using the scenario names and changing cell values shown in Table 8.6. Table 8.6: Data for the four scenarios Changing Cell Long-Term Debt Financing 2,500 2,500 Common Stock Financing 2,500 0 Preferred Stock Financing 2,500 F9 Balanced Financing 2,500 1,000 750 F12 0 F14 0 2,500 0 F15 0 0 2,500 750 © 2014 Cengage Learning 4. Based on the information in Table 8.7, apply appropriate names to the worksheet's result cells. Table 8.7: Descriptions of the result cells Cell Description F13 Change in total liabilities F17 Change in total equity G21 Debt-to-equity ratio G24 Long-term-debt-to-common-equity ratio G25 Preferred stock ratio © 2014 Cengage Learning jij - 548 iPad 10:16 PM * 69% Succeeding in Business with Microsoft Excel 2013: A Problem-S... Aa » Q ม Chapter 8 Using Data Tables and Excel Scenarios for WhatIf Analysis In Level 3, you learned how to use two-variable data tables to run simulations of data models. In this case, one set of input values is replaced with a sequence of numbers that determines the number of iterations of the simulation that are run. Because one set of input values is replaced by an iteration sequence, the replaced input variable must be determined by some other means, usually related to a random number generating function, such as RAND. When specifying how to complete a two-variable data table used to run a simulation, the sequence of iteration numbers is referred to an empty cell instead of a cell in the worksheet's input section, as is the real set of input values. Because a large quantity of simulated data is often difficult to interpret directly, some method of classifying or aggregating the data, such as the use of statistical functions, can help facilitate the interpretation process related to simulated data. Conceptual Review 1. Compare break-even analysis and sensitivity analysis. 2. How does a data table help you perform what-if analysis? 3. What is the difference between a one-variable data table and a two-variable data table? When would you use each type of data table? 4. What are the two major steps involved in creating a data table in Excel 5. In a two-variable data table, what do the first column and first row contain? 6. When should you create a scenario instead of a data table? Give an example of a business situation that could best be analyzed with scenarios. 7. Why are defined names important when you create scenarios? 8. What types of reports can you create for scenarios? Which type shows results only? 9. What type of what-if question can a simulation answer? 10. Explain how you must vary the setup of a two-variable data table to run a simulation via the table. Case Problems Level 1 - Estimating Travel Expenses for Customers of Executive Transport, Inc. $ Sales Executive Transport, Inc. is a company that rents cars, vans, and limos to a wide range of customers. Customers frequently call Executive Transport's sales staff to receive quotes for rental expenses. As gas prices have been increasing the last few years, customers have become more conscious of fuel efficiency and its impact on their total cost of transportation. As a service to its customers, Executive Transport has modified jij IK 546 iPad 10:16 PM * 69% Succeeding in Business with Microsoft Excel 2013: A Problem-S... Aa » Q Using Data Tables and Excel Scenarios for What-lf Analysis Chapter 8 5. Create a professional-looking scenario summary report that shows all the result cells listed in Table 8.7. 6. Create a professional-looking scenario PivotTable that shows the last three result cells listed in Table 8.7. Also generate a PivotChart based on the PivotTable. 7. Save and close the Granite City Expansion.xlsx workbook. Level 3 – Analyzing Health Insurance Plan Options for CKG Auto DE ロロロ Human Resources СKG Auto has traditionally paid 75% of the premiums for its employees' health insurance. Like many companies, CKG is re-evaluating this employee benefit as health insurance premiums have increased at a rate much higher than the general rate of inflation. CKG is evaluating all of its options with regard to health insurance. The company has received quotes from insurance companies for three different health plans: a co-pay plan, a low- deductible plan, and a high-deductible plan. In addition, it is considering instituting a self-insurance health plan, under which the employees pay a quasi-premium and co-pay directly to CKG, and in turn, CKG pays the employees' health claims, just as an insurance company would. 8 CKG's human resources staff has started a workbook that contains data about the cash effects of the options for purchasing health insurance plans and the proposed self-insurance plan based on CKG's employees' past health insurance participation and claim history. You have been asked to finish the worksheet, complete with scenarios under which CKG pays different percentages of employees' health insurance premiums, and a simulation estimating the cash effects of the self-insurance plan. Complete the following: Chapter Exercises 1. Open the workbook named CKGHealth.xlsx located in the Chapter 8 folder, and save the file as CKG Health Insurance.xlsx. 2. In the Self-InsuranceSim worksheet, complete two cumulative probability distribu- tion tables: one for the co-pay events and one for the dollar value of claims. 3. Add a randomly generated value somewhere in the worksheet. 4. In cells D12 and D13, use the appropriate functions to return a number of co-pay events and a dollar value of claims based on a random entry from the cumulative distribution tables. 5. Set up a data table that will run a simulation to calculate the net cash paid for employee health coverage given various numbers of co-pay events, dollar value of claims, and monthly premiums paid by employees of $50 to $130 by increments of $10. CKG Auto's management has decided that 1000 iterations of the simulation are sufficient. 6. Instruct Excel to complete the data table. Add informative headings and formatting so the table has a more professional appearance. jij - 549 iPad 10:17 PM * 69% Succeeding in Business with Microsoft Excel 2013: A Problem-S... Aa » A) Q ม Chapter 8 Using Data Tables and Excel Scenarios for WhatIf Analysis 7. Use the AVERAGE, STDEV.S, MAX, and MIN functions to create a range that summarizes the simulation’s net cash effects for each monthly premium value. 8. Add informative headings and formatting to the summary range so it has a more professional appearance. 9. Save and close the CKG Health Insurance.xlsx workbook. jij - 550
Purchase answer to see full attachment

Tags: excel excelhelp

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Granite City Books
Financing Options for Expansion
As of December 31, 2016
(000's omitted)

2015
Summary Balance Sheet
Total assets
Current liabilities
Long-term liabilities
Total liabilities
Common stock
Preferred stock
Retained earnings
Total stockholders' equity
Total liabilities & stockholders' equity
Ratio Analysis
Debt-to-equity ratio
Long-term-debt to equity ratio
Debt-to-common-equity ratio
Long-term-debt to common equity
Preferred stock ratio
Common-to-preferred equity

Changes

2016

% Change

$

2,225

$

2,500

$

4,725

112.36%

$

278
550
828
1,000
397
1,397
2,225

$

(27) $
2,500
2,473
27
27
2,500 $

251
3,050
3,301
1,000
424
1,424
4,725

-9.71%
454.55%
298.67%
0.00%
0.00%
6.80%
1.93%
112.36%

$

59.27%
39.37%
82.80%
55.00%
0.00%
0.00%

$

231.81%
214.19%
330.10%
305.00%
0.00%
0.00%

Scenario Summary
Current Values:

Changing Cells:
Change_in_Assets
$
Change_in_Longterm_Debt
Change_in_Common_Stock
Change_in_Preffered_Stock
Result Cells:
$G$9
$
$I$9
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.

2,500
2,500
4,725
112.36%

Original

Change in assets

$

2,500
2,500
-

$

$

4,725 $
112.36%

2,500
2,500
2,500
2,500
4,725
112.36%

Change in longterm debt

$

$

1,000
2,500
-

Change in dollar amount of common stock issued

$

3,225 $
44.94%

750
2,500
2,975
33.71%

Change in the dollar amount of prefered stock issued

$

750
2,500

$

2,975
33.71%

The Pivot Table

Change_in_Assets,$F$12,$F$14,$

(All)

Row Labels

$G$9

Change in assets
Change in dollar amount of common stock issued
Change in longterm debt
Change in the dollar amount of prefered stock issued
Original

4725
2975
3225
2975
4725

$I$9
1.123595506
0.337078652
0.449438202
0.337078652
1.123595506

Executive Transport, Inc.
Rental Car Cost Analysis
As of June 30, 2016
Car Rental Contract Terms
Car type:
Weekly rental car charge:
Charge per mile:

Mid-size
$185.00
$0.15

Gas-related data
Average miles per gallon for car
Average cost per gallon of gas

Quote Customer Projected Costs of:
Car charge
Mileage charge
Gas expenses
Total projected transportation exp

Customer-related data
Number of weeks renting car:
Estimated number of miles to drive:

20
$2.85

$

$

370.00
71.25
67.69
508.94

One Variable Data table

Average Gas Price
$
2.85
$
2.20
$
2.40
$
2.60
$
2.80
$
3.00
$
3.20
$
3.40
$
3.60
$
3.80
Two Variable Data table

Total Projected transport cost by changing ave

$
$
$
$
$
$
$
$
$
$

508.94
2.20
2.40
2.60
2.80
3.00
3.20
3.40
3.60
3.80

rt, Inc.
nalysis
016

mer-related data
r of weeks renting car:
ed number of miles to drive:

2
475

Total Projected Cost
$
508.94
$
493.50
$
498.25
$
503.00
$
507.75
$
512.50
$
517.25
$
522.00
$
526.75
$
531.50

rojected transport cost by changing average gas price and miles per gallon.

$
$
$
$
$
$
$
$
$

18
499.31
504.58
509.86
515.14
520.42
525.69
530.97
536.25
541.53

$
$
$
$
$
$
$
$
$

20
493.50
498.25
503.00
507.75
512.50
517.25
522.00
526.75
531.50

$
$
$
$
$
$
$
$
$

22
488.75
493.07
497.39
501.70
506.02
510.34
514.66
518.98
523.30

$
$
$
$
$
$
$
$
$

24
484.79
488.75
492.71
496.67
500.63
504.58
508.54
512.50
516.46

$
$
$
$
$
$
$
$
$

26
481.44
485.10
488.75
492.40
496.06
499.71
503.37
507.02
510.67

$
$
$
$
$
$
$
$
$

28
478.57
481.96
485.36
488.75
492.14
495.54
498.93
502.32
505.71

Attached.

CKG Auto
Self-Insurance Health Plan Simulation - Employer Perspective
For the Year Ended Dec 31, 2016

Assumptions:
Number of employees electing health coverage
Monthly premium paid per employee
Co-pay paid by employee per event

112
$85
$20

Estimated number of co-pay events per year
Estimated dollar value of health claims for year

250
200000

Cumulative
Probability
10%
25%
45%
65%
85%
100%

Cumulative
Probability

Random Variable

10%
20%
35%
55%
75%
90%
100%

11%

Two Variable Date Table of Net Cash paid for emplo

$

80,760
22%
33%
71%
42%
2%
67%
82%
18%
90%
51%
13%

$
$

$
$
$
$

$50
#N/A
262,800.00
#N/A
127,800.00
#N/A
#N/A
#N/A
217,800.00
127,800.00
222,800.00
357,800.00

45%
77%
40%
55%
62%
36%
77%
68%
42%
7%
98%
63%
51%
65%
57%
32%
44%
96%
55%
19%
3%
83%
2%
91%
17%
34%
1%
20%
51%
9%
94%
61%
15%
33%
12%
9%
53%
38%
71%
62%
12%
100%
2%
70%

$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

127,800.00
262,800.00
172,800.00
312,800.00
262,800.00
267,800.00
217,800.00
172,800.00
262,800.00
127,800.00
312,800.00
357,800.00
#N/A
#N/A
217,800.00
357,800.00
177,800.00
217,800.00
#N/A
267,800.00
222,800.00
262,800.00
262,800.00
312,800.00
172,800.00
312,800.00
222,800.00
312,800.00
267,800.00
262,800.00
312,800.00
177,800.00
312,800.00
#N/A
262,800.00
262,800.00
#N/A
267,800.00
127,800.00
267,800.00
#N/A
357,800.00
177,800.00
312,800.00

28%
70%
56%
13%
68%
10%
69%
29%
69%
49%
24%
22%
18%
22%
89%
68%
8%
15%
65%
11%
61%
34%
12%
44%
64%
77%
11%
65%
23%
44%
18%
66%
71%
98%
28%
42%
23%
28%
35%
98%
24%
47%
3%
92%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

312,800.00
217,800.00
127,800.00
222,800.00
#N/A
217,800.00
312,800.00
357,800.00
267,800.00
222,800.00
357,800.00
217,800.00
172,800.00
357,800.00
312,800.00
#N/A
267,800.00
#N/A
357,800.00
127,800.00
357,800.00
177,800.00
312,800.00
177,800.00
312,800.00
357,800.00
#N/A
172,800.00
267,800.00
127,800.00
312,800.00
172,800.00
312,800.00
177,800.00
127,800.00
267,800.00
262,800.00
357,800.00
127,800.00
267,800.00
127,800.00
#N/A
357,800.00
127,800.00

45%
39%
45%
37%
14%
35%
40%
66%
38%
43%
12%
93%
63%
100%
62%
33%
95%
83%
23%
79%
34%
28%
63%
36%
64%
45%
80%
78%
64%
73%
71%
35%
29%
30%
77%
67%
51%
87%
78%
11%
64%
26%
84%
11%

$ 172,800.00
$ 312,800.00
$ 262,800.00
#N/A
$ 172,800.00
$ 222,800.00
$ 217,800.00
#N/A
$ 357,800.00
$ 172,800.00
$ 172,800.00
$ 172,800.00
$ 217,800.00
$ 357,800.00
$ 222,800.00
$ 267,800.00
$ 172,800.00
$ 127,800.00
$ 357,800.00
$ 312,800.00
$ 222,800.00
$ 172,800.00
$ 267,800.00
$ 127,800.00
$ 357,800.00
$ 357,800.00
$ 127,800.00
$ 312,800.00
$ 307,800.00
$ 357,800.00
$ 262,800.00
$ 127,800.00
$ 172,800.00
$ 172,800.00
$ 177,800.00
$ 312,800.00
#N/A
$ 267,800.00
$ 267,800.00
#N/A
#N/A
#N/A
$ 127,800.00
$ 217,800.00

20%
81%
9%
24%
1%
14%
29%
55%
93%
86%
43%
87%
20%
39%
4%
77%
35%
63%
43%
59%
38%
91%
36%
26%
14%
90%
26%
32%
0%
47%
79%
21%
67%
32%
96%
13%
83%
34%
20%
10%
35%
49%
23%
62%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

357,800.00
312,800.00
262,800.00
312,800.00
262,800.00
312,800.00
357,800.00
357,800.00
307,800.00
357,800.00
222,800.00
262,800.00
172,800.00
262,800.00
222,800.00
#N/A
357,800.00
267,800.00
312,800.00
222,800.00
172,800.00
357,800.00
177,800.00
312,800.00
222,800.00
357,800.00
172,800.00
127,800.00
127,800.00
217,800.00
357,800.00
267,800.00
217,800.00
217,800.00
267,800.00
357,800.00
#N/A
172,800.00
217,800.00
222,800.00
#N/A
262,800.00
262,800.00
312,800.00

91%
86%
86%
69%
98%
79%
16%
37%
35%
6%
92%
43%
3%
11%
65%
12%
17%
30%
96%
97%
3%
10%
100%
64%
34%
6%
40%
28%
25%
48%
59%
99%
17%
90%
36%
42%
63%
26%
89%
71%
32%
86%
23%
8%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

127,800.00
262,800.00
127,800.00
267,800.00
357,800.00
267,800.00
172,800.00
127,800.00
172,800.00
312,800.00
217,800.00
172,800.00
307,800.00
127,800.00
262,800.00
#N/A
357,800.00
312,800.00
262,800.00
262,800.00
172,800.00
357,800.00
177,800.00
177,800.00
312,800.00
217,800.00
357,800.00
262,800.00
222,800.00
222,800.00
#N/A
262,800.00
267,800.00
#N/A
222,800.00
172,800.00
127,800.00
222,800.00
262,800.00
267,800.00
172,800.00
357,800.00
267,800.00
222,800.00

42%
75%
17%
24%
55%
27%
67%
100%
81%
52%
85%
76%
44%
61%
73%
17%
28%
2%
2%
47%
93%
75%
15%
26%
25%
40%
97%
24%
44%
22%
8%
4%
72%
11%
36%
4%
89%
40%
7%
99%
25%
83%
62%
7%

$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$

217,800.00
217,800.00
172,800.00
127,800.00
127,800.00
222,800.00
172,800.00
357,800.00
312,800.00
177,800.00
307,800.00
262,800.00
#N/A
#N/A
217,800.00
177,800.00
267,800.00
357,800.00
217,800.00
262,800.00
217,800.00
177,800.00
312,800.00
222,800.00
262,800.00
267,800.00
127,800.00
267,800.00
312,800.00
267,800.00
127,800.00
222,800.00
172,800.00
217,800.00
357,800.00
#N/A
#N/A
312,800.00
#N/A
267,800.00
357,800.00
357,800.00
262,800.00
307,800.00

73%
38%
92%
4%
8%
52%
68%
19%
51%
88%
30%
57%
1%
99%
62%
63%
21%
29%
39%
29%
71%
12%
72%
77%
55%
12%
34%
62%
32%
95%
40%
72%
4%
14%
79%
60%
62%
1%
26%
93%
9%
79%
31%
14%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

312,800.00
127,800.00
262,800.00
267,800.00
172,800.00
217,800.00
222,800.00
262,800.00
222,800.00
262,800.00
307,800.00
307,800.00
357,800.00
357,800.00
307,800.00
357,800.00
262,800.00
217,800.00
#N/A
262,800.00
262,800.00
267,800.00
222,800.00
262,800.00
#N/A
#N/A
222,800.00
262,800.00
127,800.00
127,800.00
217,800.00
222,800.00
#N/A
172,800.00
217,800.00
217,800.00
172,800.00
262,800.00
262,800.00
312,800.00
357,800.00
172,800.00
357,800.00
217,800.00

30%
45%
69%
67%
87%
98%
66%
71%
63%
8%
40%
91%
70%
57%
30%
95%
66%
63%
93%
25%
13%
20%
29%
96%
38%
67%
22%
38%
39%
54%
52%
80%
64%
30%
85%
69%
72%
35%
2%
42%
74%
60%
43%
16%

$ 222,800.00
$ 357,800.00
$ 312,800.00
#N/A
$ 172,800.00
$ 172,800.00
$ 127,800.00
$ 172,800.00
$ 307,800.00
$ 127,800.00
$ 222,800.00
$ 222,800.00
$ 262,800.00
$ 172,800.00
$ 357,800.00
$ 127,800.00
#N/A
$ 312,800.00
$ 177,800.00
$ 222,800.00
$ 307,800.00
$ 262,800.00
#N/A
$ 177,800.00
$ 357,800.00
$ 222,800.00
$ 262,800.00
#N/A
$ 217,800.00
$ 222,800.00
$ 267,800.00
$ 222,800.00
$ 172,800.00
$ 262,800.00
$ 262,800.00
$ 177,800.00
$ 172,800.00
$ 172,800.00
$ 127,800.00
#N/A
$ 172,800.00
$ 262,800.00
$ 357,800.00
$ 312,800.00

96%
26%
85%
3%
47%
55%
27%
35%
56%
19%
66%
25%
18%
67%
8%
52%
44%
12%
89%
29%
42%
16%
90%
2%
60%
78%
63%
18%
4%
57%
84%
23%
96%
16%
87%
26%
49%
62%
13%
32%
99%
99%
23%
96%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$

172,800.00
177,800.00
127,800.00
172,800.00
172,800.00
172,800.00
222,800.00
312,800.00
172,800.00
217,800.00
307,800.00
222,800.00
127,800.00
177,800.00
262,800.00
312,800.00
267,800.00
217,800.00
127,800.00
357,800.00
357,800.00
357,800.00
312,800.00
217,800.00
262,800.00
262,800.00
307,800.00
262,800.00
222,800.00
357,800.00
#N/A
#N/A
357,800.00
307,800.00
312,800.00
307,800.00
312,800.00
172,800.00
267,800.00
262,800.00
222,800.00
#N/A
262,800.00
172,800.00

8%
30%
58%
58%
82%
74%
64%
46%
97%
30%
72%
95%
54%
9%
69%
18%
60%
62%
36%
16%
23%
41%
95%
93%
78%
19%
7%
20%
1%
43%
69%
97%
85%
93%
59%
57%
27%
45%
24%
8%
63%
74%
1%
73%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

#N/A
307,800.00
307,800.00
222,800.00
217,800.00
267,800.00
262,800.00
#N/A
177,800.00
222,800.00
127,800.00
357,800.00
262,800.00
127,800.00
127,800.00
172,800.00
172,800.00
357,800.00
172,800.00
267,800.00
357,800.00
127,800.00
267,800.00
#N/A
357,800.00
172,800.00
222,800.00
262,800.00
357,800.00
222,800.00
#N/A
172,800.00
262,800.00
#N/A
312,800.00
172,800.00
172,800.00
217,800.00
262,800.00
222,800.00
262,800.00
217,800.00
177,800.00
217,800.00

20%
96%
44%
53%
61%
13%
58%
73%
94%
52%
3%
84%
44%
37%
4%
52%
79%
61%
30%
81%
49%
97%
33%
76%
7%
36%
37%
44%
98%
49%
55%
78%
34%
16%
47%
78%
40%
98%
95%
74%
58%
67%
16%
38%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

172,800.00
262,800.00
222,800.00
262,800.00
#N/A
172,800.00
267,800.00
217,800.00
312,800.00
262,800.00
172,800.00
312,800.00
267,800.00
127,800.00
222,800.00
262,800.00
312,800.00
357,800.00
222,800.00
307,800.00
172,800.00
222,800.00
262,800.00
222,800.00
172,800.00
312,800.00
312,800.00
177,800.00
267,800.00
#N/A
177,800.00
262,800.00
127,800.00
312,800.00
217,800.00
222,800.00
262,800.00
172,800.00
217,800.00
217,800.00
357,800.00
#N/A
307,800.00
222,800.00

66%
86%
67%
28%
11%
24%
95%
20%
28%
3%
2%
96%
60%
46%
66%
70%
27%
83%
0%
43%
86%
13%
55%
97%
31%
21%
28%
81%
78%
94%
67%
65%
30%
70%
53%
23%
28%
50%
88%
2%
74%
32%
42%
31%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$

267,800.00
267,800.00
217,800.00
172,800.00
307,800.00
217,800.00
222,800.00
127,800.00
217,800.00
127,800.00
172,800.00
222,800.00
262,800.00
357,800.00
127,800.00
222,800.00
217,800.00
312,800.00
172,800.00
307,800.00
312,800.00
#N/A
312,800.00
357,800.00
172,800.00
#N/A
172,800.00
267,800.00
172,800.00
357,800.00
312,800.00
217,800.00
267,800.00
262,800.00
127,800.00
312,800.00
312,800.00
#N/A
#N/A
172,800.00
172,800.00
262,800.00
172,800.00
262,800.00

18%
44%
27%
91%
21%
95%
83%
4%
12%
24%
39%
98%
95%
37%
31%
20%
43%
46%
27%
58%
92%
97%
5%
46%
15%
7%
18%
63%
62%
86%
55%
84%
86%
92%
45%
93%
69%
26%
88%
73%
58%
55%
86%
7%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

357,800.00
262,800.00
217,800.00
127,800.00
262,800.00
312,800.00
312,800.00
357,800.00
#N/A
217,800.00
127,800.00
262,800.00
267,800.00
267,800.00
312,800.00
262,800.00
177,800.00
#N/A
307,800.00
262,800.00
262,800.00
222,800.00
127,800.00
217,800.00
#N/A
222,800.00
127,800.00
222,800.00
#N/A
222,800.00
267,800.00
267,800.00
312,800.00
172,800.00
#N/A
127,800.00
#N/A
127,800.00
357,800.00
172,800.00
217,800.00
267,800.00
312,800.00
262,800.00

81%
4%
69%
14%
95%
65%
47%
90%
73%
51%
13%
52%
5%
78%
8%
49%
12%
89%
94%
17%
21%
13%
38%
3%
23%
77%
38%
44%
57%
73%
8%
51%
77%
53%
32%
85%
56%
68%
58%
29%
39%
21%
15%
68%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

267,800.00
357,800.00
262,800.00
172,800.00
267,800.00
222,800.00
177,800.00
217,800.00
217,800.00
262,800.00
222,800.00
307,800.00
357,800.00
172,800.00
312,800.00
307,800.00
357,800.00
262,800.00
#N/A
172,800.00
267,800.00
307,800.00
#N/A
312,800.00
262,800.00
312,800.00
262,800.00
127,800.00
217,800.00
267,800.00
222,800.00
307,800.00
267,800.00
217,800.00
#N/A
267,800.00
357,800.00
312,800.00
127,800.00
267,800.00
222,800.00
#N/A
222,800.00
177,800.00

88%
2%
7%
94%
18%
74%
52%
20%
95%
93%
54%
32%
15%
31%
96%
29%
66%
47%
56%
25%
23%
21%
76%
83%
93%
48%
44%
84%
85%
88%
18%
43%
72%
12%
19%
57%
38%
78%
42%
54%
56%
29%
12%
89%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

262,800.00
267,800.00
357,800.00
177,800.00
222,800.00
172,800.00
172,800.00
217,800.00
177,800.00
357,800.00
#N/A
312,800.00
357,800.00
222,800.00
#N/A
172,800.00
307,800.00
#N/A
217,800.00
312,800.00
222,800.00
172,800.00
222,800.00
267,800.00
267,800.00
262,800.00
222,800.00
307,800.00
267,800.00
#N/A
172,800.00
312,800.00
357,800.00
172,800.00
267,800.00
127,800.00
172,800.00
#N/A
312,800.00
127,800.00
127,800.00
177,800.00
267,800.00
217,800.00

64%
44%
79%
20%
20%
84%
7%
7%
65%
11%
41%
89%
96%
10%
23%
31%
86%
46%
97%
55%
7%
5%
60%
80%
87%
8%
98%
27%
17%
100%
37%
46%
25%
9%
88%
91%
14%
35%
52%
94%
80%
87%
53%
32%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$

267,800.00
357,800.00
262,800.00
172,800.00
262,800.00
307,800.00
357,800.00
307,800.00
#N/A
357,800.00
#N/A
177,800.00
172,800.00
127,800.00
262,800.00
222,800.00
312,800.00
312,800.00
#N/A
#N/A
222,800.00
307,800.00
217,800.00
222,800.00
177,800.00
357,800.00
262,800.00
267,800.00
127,800.00
222,800.00
127,800.00
#N/A
#N/A
357,800.00
217,800.00
127,800.00
172,800.00
217,800.00
217,800.00
357,800.00
217,800.00
312,800.00
222,800.00
217,800.00

35%
52%
73%
79%
43%
32%
72%
48%
43%
55%
77%
11%
8%
98%
10%
10%
59%
84%
77%
75%
24%
17%
12%
46%
33%
14%
30%
12%
93%
17%
7%
84%
3%
55%
75%
94%
99%
85%
35%
86%
3%
49%
78%
55%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

262,800.00
312,800.00
217,800.00
267,800.00
222,800.00
222,800.00
312,800.00
172,800.00
127,800.00
#N/A
172,800.00
357,800.00
357,800.00
312,800.00
#N/A
267,800.00
312,800.00
222,800.00
357,800.00
262,800.00
357,800.00
177,800.00
267,800.00
172,800.00
267,800.00
217,800.00
267,800.00
177,800.00
172,800.00
177,800.00
217,800.00
262,800.00
127,800.00
357,800.00
262,800.00
262,800.00
172,800.00
222,800.00
312,800.00
#N/A
357,800.00
357,800.00
222,800.00
312,800.00

7%
61%
6%
75%
57%
64%
8%
77%
25%
55%
28%
53%
62%
10%
74%
35%
33%
80%
52%
78%
50%
61%
75%
82%
65%
42%
75%
9%
14%
25%
87%
50%
60%
89%
80%
42%
88%
73%
67%
89%
32%
22%
29%
41%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

177,800.00
222,800.00
357,800.00
172,800.00
127,800.00
172,800.00
262,800.00
312,800.00
#N/A
357,800.00
#N/A
312,800.00
#N/A
267,800.00
307,800.00
262,800.00
177,800.00
312,800.00
267,800.00
357,800.00
267,800.00
262,800.00
217,800.00
217,800.00
#N/A
357,800.00
262,800.00
217,800.00
267,800.00
267,800.00
217,800.00
357,800.00
222,800.00
217,800.00
127,800.00
262,800.00
217,800.00
267,800.00
357,800.00
262,800.00
127,800.00
#N/A
267,800.00
307,800.00

99%
65%
80%
94%
28%
61%
2%
26%
21%
71%
70%
67%
26%
65%
68%
69%
49%
1%
24%
72%
26%
69%
40%
43%
7%
16%
16%
17%
89%
50%
53%
50%
33%
29%
15%
13%
5%
32%
64%
62%
26%
59%
76%
44%

$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

312,800.00
267,800.00
217,800.00
172,800.00
267,800.00
267,800.00
262,800.00
262,800.00
307,800.00
267,800.00
177,800.00
262,800.00
357,800.00
127,800.00
#N/A
#N/A
#N/A
357,800.00
307,800.00
262,800.00
267,800.00
#N/A
267,800.00
172,800.00
#N/A
127,800.00
127,800.00
#N/A
127,800.00
307,800.00
217,800.00
312,800.00
312,800.00
267,800.00
357,800.00
262,800.00
222,800.00
307,800.00
#N/A
262,800.00
312,800.00
267,800.00
222,800.00
357,800.00

66%
15%
80%
70%
90%
33%
99%
20%
60%
63%
49%
29%
72%
48%
9%
27%
86%
0%
14%
79%
4%
91%
40%
6%
10%
80%
66%
49%
44%
72%
56%
99%
90%
48%
72%
49%
39%
61%
39%
51%
65%
10%
28%
50%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

262,800.00
312,800.00
262,800.00
312,800.00
312,800.00
262,800.00
357,800.00
307,800.00
357,800.00
267,800.00
222,800.00
312,800.00
262,800.00
127,800.00
312,800.00
267,800.00
307,800.00
262,800.00
357,800.00
127,800.00
222,800.00
222,800.00
222,800.00
267,800.00
267,800.00
267,800.00
127,800.00
357,800.00
307,800.00
222,800.00
222,800.00
172,800.00
127,800.00
357,800.00
267,800.00
127,800.00
267,800.00
172,800.00
217,800.00
217,800.00
267,800.00
312,800.00
177,800.00
357,800.00

43%
36%
22%
84%
87%
97%
37%
47%
25%
100%
78%
99%
90%
90%
2%
95%
39%
28%
36%
32%
80%
34%
27%
97%
42%
29%
48%
86%
15%
41%
78%
71%
92%
27%
2%
54%
45%
96%
76%
71%
57%
37%
85%
80%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

217,800.00
127,800.00
262,800.00
357,800.00
262,800.00
172,800.00
172,800.00
127,800.00
262,800.00
357,800.00
267,800.00
262,800.00
172,800.00
262,800.00
172,800.00
177,800.00
312,800.00
177,800.00
357,800.00
222,800.00
222,800.00
312,800.00
262,800.00
267,800.00
267,800.00
262,800.00
172,800.00
#N/A
267,800.00
267,800.00
127,800.00
312,800.00
127,800.00
312,800.00
222,800.00
262,800.00
127,800.00
172,800.00
357,800.00
#N/A
217,800.00
127,800.00
307,800.00
222,800.00

68%
88%
69%
44%
10%
98%
4%
76%
92%
12%
45%
63%
53%
83%
59%
66%
8%
14%
99%
11%
33%
23%
52%
43%
81%
64%
52%
15%
82%
94%
36%
11%
68%
39%
55%
82%
98%
45%
63%
79%
45%
17%
47%
96%

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

267,800.00
222,800.00
267,800.00
222,800.00
262,800.00
262,800.00
217,800.00
#N/A
177,800.00
217,800.00
#N/A
312,800.00
312,800.00
172,800.00
177,800.00
172,800.00
357,800.00
267,800.00
262,800.00
307,800.00
172,800.00
262,800.00
222,800.00
177,800.00
222,800.00
217,800.00
217,800.00
127,800.00
177,800.00
222,800.00
267,800.00
357,800.00
307,800.00
312,800.00
217,800.00
262,800.00
267,800.00
127,800.00
177,800.00
127,800.00
267,800.00
217,800.00
222,800.00
127,800.00

81%
76%
3%
26%
16%
53%
93%
32%
20%
15%
53%
39%
50%
95%
87%
40%
79%
26%
87%
65%
83%
23%
Average
STDEV.S
MAX
MIN

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

262,800.00
222,800.00
262,800.00
172,800.00
217,800.00
267,800.00
217,800.00
262,800.00
357,800.00
312,800.00
172,800.00
177,800.00
#N/A
262,800.00
#N/A
357,800.00
262,800.00
222,800.00
267,800.00
312,800.00
357,800.00
222,800.00
#N/A
#N/A
#N/A
#N/A

KG Auto
Simulation - Employer Perspective
Ended Dec 31, 2016

Projections:
Cash received from employee premium payments
Cash received from employee co-pays
Total cash paid into health insurance fund

$

Cash paid for health claims

200,000

Net cash paid for employee health coverage
Estimated Number of
Co-pay Events per
Year
250
500
750
1,000
1,250
1,500

114,240
5,000
119,240

$

80,760

Individual
Probability
10%
15%
20%
20%
20%
15%
100%

Estimated Dollar
Value of Claims
200,000.00
250,000.00
300,000.00
350,000.00
400,000.00
450,000.00
500,000.00

Individual
Probability
$
10%
$
10%
$
15%
$
20%
$
20%
$
15%
$
10%
100%
Variable Date Table of Net Cash paid for employees health at different numbers of co-pay event,dollar value and monthly premium paid

$
$

$
$
$
$

$60
#N/A
249,360.00
#N/A
114,360.00
#N/A
#N/A
#N/A
204,360.00
114,360.00
209,360.00
344,360.00

$
$

$
$
$
$

$70
#N/A
235,920.00
#N/A
100,920.00
#N/A
#N/A
#N/A
190,920.00
100,920.00
195,920.00
330,920.00

$
$

$
$
$
$

$80
#N/A
222,480.00
#N/A
87,480.00
#N/A
#N/A
#N/A
177,480.00
87,480.00
182,480.00
317,480.00

$90
#N/A
$ 209,040.00
#N/A
$ 74,040.00
#N/A
#N/A
#N/A
$ 164,040.00
$ 74,040.00
$ 169,040.00
$ 304,040.00

$100
#N/A
$ 195,600.00
#N/A
$ 60,600.00
#N/A
#N/A
#N/A
$ 150,600.00
$ 60,600.00
$ 155,600.00
$ 290,600.00

$110
#N/A
$ 182,160.00
#N/A
$ 47,160.00
#N/A
#N/A
#N/A
$ 137,160.00
$ 47,160.00
$ 142,160.00
$ 277,160.00

$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

114,360.00
249,360.00
159,360.00
299,360.00
249,360.00
254,360.00
204,360.00
159,360.00
249,360.00
114,360.00
299,360.00
344,360.00
#N/A
#N/A
204,360.00
344,360.00
164,360.00
204,360.00
#N/A
254,360.00
209,360.00
249,360.00
249,360.00
299,360.00
159,360.00
299,360.00
209,360.00
299,360.00
254,360.00
249,360.00
299,360.00
164,360.00
299,360.00
#N/A
249,360.00
249,360.00
#N/A
254,360.00
114,360.00
254,360.00
#N/A
344,360.00
164,360.00
299,360.00

$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

100,920.00
235,920.00
145,920.00
285,920.00
235,920.00
240,920.00
190,920.00
145,920.00
235,920.00
100,920.00
285,920.00
330,920.00
#N/A
#N/A
190,920.00
330,920.00
150,920.00
190,920.00
#N/A
240,920.00
195,920.00
235,920.00
235,920.00
285,920.00
145,920.00
285,920.00
195,920.00
285,920.00
240,920.00
235,920.00
285,920.00
150,920.00
285,920.00
#N/A
235,920.00
235,920.00
#N/A
240,920.00
100,920.00
240,920.00
#N/A
330,920.00
150,920.00
285,920.00

$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

87,480.00
222,480.00
132,480.00
272,480.00
222,480.00
227,480.00
177,480.00
132,480.00
222,480.00
87,480.00
272,480.00
317,480.00
#N/A
#N/A
177,480.00
317,480.00
137,480.00
177,480.00
#N/A
227,480.00
182,480.00
222,480.00
222,480.00
272,480.00
132,480.00
272,480.00
182,480.00
272,480.00
227,480.00
222,480.00
272,480.00
137,480.00
272,480.00
#N/A
222,480.00
222,480.00
#N/A
227,480.00
87,480.00
227,480.00
#N/A
317,480.00
137,480.00
272,480.00

$ 74,040.00
$ 209,040.00
$ 119,040.00
$ 259,040.00
$ 209,040.00
$ 214,040.00
$ 164,040.00
$ 119,040.00
$ 209,040.00
$ 74,040.00
$ 259,040.00
$ 304,040.00
#N/A
#N/A
$ 164,040.00
$ 304,040.00
$ 124,040.00
$ 164,040.00
#N/A
$ 214,040.00
$ 169,040.00
$ 209,040.00
$ 209,040.00
$ 259,040.00
$ 119,040.00
$ 259,040.00
$ 169,040.00
$ 259,040.00
$ 214,040.00
$ 209,040.00
$ 259,040.00
$ 124,040.00
$ 259,040.00
#N/A
$ 209,040.00
$ 209,040.00
#N/A
$ ...