Excel
SOLUTION: BUS302 California State Total Costs of Physician Visits vs Number of Employees Paper
SOLUTION: BUS302 California State Total Costs of Physician Visits vs Number of Employees Paper.
Week
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Total
Total Costs of Physician Visits
77,302
79,189
74,897
73,410
74,554
75,480
74,236
75,508
76,317
76,021
78,845
79,737
80,263
79,010
81,235
84,212
82,183
82,476
81,098
84,207
90,131
95,107
99,865
97,770
98,707
2,051,760
# Visits
397
439
388
419
393
425
410
458
449
455
445
467
471
478
491
494
519
467
467
493
475
511
512
512
535
11,570
# Employees
4,048
4,055
4,065
4,077
4,086
4,064
4,067
4,089
4,096
4,078
4,103
4,105
4,075
4,092
4,093
4,118
4,117
4,127
4,121
4,122
4,146
4,146
4,140
4,120
4,125
102,475
TRUE MASTER PLAN – QUESTIONS
Use the guidelines for writing a report on the course web site and use the following questions as a guide:
1. Using only the information provided in Exhibit 1, explain why further analysis of physician visits may be
needed. Compare the profitability of hospital and surgical services to physician services, using the
allocation of revenue that was given. Show the breakdown of the $250 premium using a bar chart or pie
chart. Does the allocation of the $250 per employee per month payment across the types of health care
services seem reasonable, given the past two months’ utilization?
2. The weekly utilization data is provided in the Excel file on the class website. Create scatter plots to show
the relationships between the following and explain the results:
2.1 The total cost of physician visits vs. # of employees
2.2 # of employees vs. # of visits
2.3 Total cost for physician visits vs. # of visits
3. For question # 2, calculate visits per employee and the cost per visit for each week. Calculate and
interpret the mean, standard deviation, and coefficient of variation for these measures for the six-month
period. Explain how these statistics are useful in understanding the trend in total outpatient physician
costs per DigiTech Media employee.
4. Regression analysis can be useful to tease out the importance of various factors in explaining costs.
4.1 Evaluate the relationship between visits per week and week. Interpret your regression results by
discussing the significance of the regression equation and the magnitude of the estimated
coefficients.
4.2 Evaluate the relationship between cost per visit and week. Interpret your regression results by
discussing the significance of the regression equation and the magnitude of the estimated
coefficients.
4.3 Compare the results from the two regressions and explain how they can be used to help Mr.
Glen in making his decision.
5. Mr. Gordon referred to a national study of copayment levels. The important results of this study are
provided in Exhibit 3.
5.1 Calculate the price elasticity of demand for physician visits at each copayment level using the
DigiTech method. What does the data tell you about the price elasticity of demand for physician
visits? How does this information help Mr. Glen in making his decision?
5.2 Using the six months of data provided in the Excel file, simulate the profitability of the physician
services department if copayments are increased to $20 per visit.
6. Using the six months of data provided in the Excel file, calculate the percentage reduction in physician
payments that would be required to achieve the same level of profits as in Q.5.2.
7. In addition to the options suggested by his staff, Mr. Glen recently read an article about rationing health
care services as a method of controlling costs. The general idea of rationing is that more expensive
treatments are excluded so that basic health benefits can be provided to a wider population. Health
plans can implement rationing by limiting the types of services they will cover. While they commonly
exclude coverage for experimental treatments and cosmetic surgery, many are now considering
adding physical therapy, mental health services, and therapies that treat fatal conditions to the list of
excluded services. Would you recommend that Mr. Glen consider this approach? Discuss ethical
considerations.
True Master Plan:
Coaching Notes
1
Question 2:
Sample Statistics and Scatter Plot
◼
◼
◼
Create two new variables: visits/employee and
cost/visit. How do these compare over time?
Calculate means and standard deviations:
consider confidence intervals for these variables.
Scatter Plots:
◼
◼
Instructions for making scatter plots with Excel are on
the Materials section of BUS 302 web site.
There are instructions for Excel 2003 and Excel 2007.
Be sure to use the appropriate instructions.
2
Question 3:
Regression Analysis
◼ Evaluate the time trends in visits per week
and cost per visit.
◼ Use regression.
◼
◼
Instructions are in the Materials section of the BUS
302 web site for both Excel 2003 and Excel 2007.
Be sure to correctly indentify the X and Y
variables.
3
Question 3:
Regression Analysis
◼
Coefficient of Determination – (R2)
◼
◼
Regression Coefficients
◼
◼
The percent of variation in the dependent variable (Y)
accounted for by variation in the independent variable
(X). The closer to 1 the better.
Coefficient on x-variable measures the slope of the
trend Line. (Positive Sign – Positive Relationship,
Negative Sign – Negative Relationship)
t-statistic and p-value are used to indicate the
observed level of significance.
4
Using Excel for Statistics
Office 2010 and 2013
1
Concepts Covered
◼
Statistics
– Descriptive Statistics
– Histograms
– Hypothesis Testing
– Scatter Plots
– Regression Analysis
2
To Set Up Statistical Package
◼
◼
◼
◼
Click File Tab, and Then Click Options.
Click Add-ins. In View and Manage Box,
Select Analysis ToolPak.
Click Go.
In the Add-Ins Available Box, Select Analysis
ToolPak Check Box and Click OK. (If ToolPak
Is Not Listed, Click Browse to Locate It.)
3
Using Excel:
Descriptive Statistics
◼
◼
◼
◼
◼
Click Data/Data Analysis (Far Right) /Descriptive
Statistics & OK.
Put Checkmarks on Summary Statistics, 95% or
99% Confidence Interval, & Labels in First Row
Boxes.
Move Cursor to Input Range Window, Highlight
Data to Analyze including Labels, & Click OK.
Your Data will Appear on New Worksheet.
Widen Columns by Clicking
Home/Format/AutoFit Column Width.
4
Using Excel:
Constructing Histograms
◼
◼
◼
◼
◼
Click Data/Data Analysis/Histogram & OK.
Put Checkmarks on Chart Output & New
Worksheet Boxes.
Move Cursor to Input Range Window, Highlight
Data Going into Histogram.
Move Cursor to Input Bin Range, Highlight Data
Showing Upper Value of Each Bin & Click OK.
Histogram will be on New Worksheet. You May
Lengthen it by Clicking Blank Space in Window,
Moving Cursor to Window Bottom Line & Holding
Down Mouse Button as You Pull Down Window.
5
Using Excel:
Hypothesis Testing
◼
◼
◼
◼
◼
◼
Go to Sheet One.
Click Data/Data Analysis/ and the Appropriate
Statistical Test. Then Click OK.
On New Window Check Labels Box and Put
Cursor on Variable 1 Range.
Highlight Variable 1 Data Including Label.
Put Cursor on Variable 2 Range & Highlight
Variable 2 Data (Including Label). Then Click
OK.
Click Home/Format/AutoFit/Column Width
6
Using Excel:
Scatter Plots
◼
◼
◼
◼
Go to Sheet One.
Highlight Data (Be Sure X Values are in Left
Column and Y Values are in Right Column).
Click Insert/Scatter. Pull down menu and click
Upper Left Icon.
Click a Datum Point on Chart with Right Mouse
Key, Add Trendline, & Click Linear.
7
Using Excel:
Regression Analysis
◼
◼
◼
◼
◼
◼
Go to Sheet One.
Click Data/Data Analysis (On Far Right)
/Regression & Click OK.
On New Window Check Labels Box and Put
Cursor on X Range.
Highlight X Data Including Label.
Put Cursor on Y Range & Highlight Y Data
(Including Label), Then Click OK.
Click Home/Format/AutoFit Column Width.
8
How to Do a Regression in
Excel – Office 2003
A Tutorial for Gateway Students
1. Enter the data into a
spreadsheet
2. Tools/DataAnalysis/Regression
3. Enter the dependent variable in
the “y” column and the independent
variable (or variables) in the “x”
columns
4. Indicate where output should go
(the 1st cell in the range works)
5. The basic regression is done.
(You may need to widen columns)
Purchase answer to see full
attachment
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Total
Total Costs of Physician Visits
77,302
79,189
74,897
73,410
74,554
75,480
74,236
75,508
76,317
76,021
78,845
79,737
80,263
79,010
81,235
84,212
82,183
82,476
81,098
84,207
90,131
95,107
99,865
97,770
98,707
2,051,760
# Visits
397
439
388
419
393
425
410
458
449
455
445
467
471
478
491
494
519
467
467
493
475
511
512
512
535
11,570
# Employees
4,048
4,055
4,065
4,077
4,086
4,064
4,067
4,089
4,096
4,078
4,103
4,105
4,075
4,092
4,093
4,118
4,117
4,127
4,121
4,122
4,146
4,146
4,140
4,120
4,125
102,475
TRUE MASTER PLAN – QUESTIONS
Use the guidelines for writing a report on the course web site and use the following questions as a guide:
1. Using only the information provided in Exhibit 1, explain why further analysis of physician visits may be
needed. Compare the profitability of hospital and surgical services to physician services, using the
allocation of revenue that was given. Show the breakdown of the $250 premium using a bar chart or pie
chart. Does the allocation of the $250 per employee per month payment across the types of health care
services seem reasonable, given the past two months’ utilization?
2. The weekly utilization data is provided in the Excel file on the class website. Create scatter plots to show
the relationships between the following and explain the results:
2.1 The total cost of physician visits vs. # of employees
2.2 # of employees vs. # of visits
2.3 Total cost for physician visits vs. # of visits
3. For question # 2, calculate visits per employee and the cost per visit for each week. Calculate and
interpret the mean, standard deviation, and coefficient of variation for these measures for the six-month
period. Explain how these statistics are useful in understanding the trend in total outpatient physician
costs per DigiTech Media employee.
4. Regression analysis can be useful to tease out the importance of various factors in explaining costs.
4.1 Evaluate the relationship between visits per week and week. Interpret your regression results by
discussing the significance of the regression equation and the magnitude of the estimated
coefficients.
4.2 Evaluate the relationship between cost per visit and week. Interpret your regression results by
discussing the significance of the regression equation and the magnitude of the estimated
coefficients.
4.3 Compare the results from the two regressions and explain how they can be used to help Mr.
Glen in making his decision.
5. Mr. Gordon referred to a national study of copayment levels. The important results of this study are
provided in Exhibit 3.
5.1 Calculate the price elasticity of demand for physician visits at each copayment level using the
DigiTech method. What does the data tell you about the price elasticity of demand for physician
visits? How does this information help Mr. Glen in making his decision?
5.2 Using the six months of data provided in the Excel file, simulate the profitability of the physician
services department if copayments are increased to $20 per visit.
6. Using the six months of data provided in the Excel file, calculate the percentage reduction in physician
payments that would be required to achieve the same level of profits as in Q.5.2.
7. In addition to the options suggested by his staff, Mr. Glen recently read an article about rationing health
care services as a method of controlling costs. The general idea of rationing is that more expensive
treatments are excluded so that basic health benefits can be provided to a wider population. Health
plans can implement rationing by limiting the types of services they will cover. While they commonly
exclude coverage for experimental treatments and cosmetic surgery, many are now considering
adding physical therapy, mental health services, and therapies that treat fatal conditions to the list of
excluded services. Would you recommend that Mr. Glen consider this approach? Discuss ethical
considerations.
True Master Plan:
Coaching Notes
1
Question 2:
Sample Statistics and Scatter Plot
◼
◼
◼
Create two new variables: visits/employee and
cost/visit. How do these compare over time?
Calculate means and standard deviations:
consider confidence intervals for these variables.
Scatter Plots:
◼
◼
Instructions for making scatter plots with Excel are on
the Materials section of BUS 302 web site.
There are instructions for Excel 2003 and Excel 2007.
Be sure to use the appropriate instructions.
2
Question 3:
Regression Analysis
◼ Evaluate the time trends in visits per week
and cost per visit.
◼ Use regression.
◼
◼
Instructions are in the Materials section of the BUS
302 web site for both Excel 2003 and Excel 2007.
Be sure to correctly indentify the X and Y
variables.
3
Question 3:
Regression Analysis
◼
Coefficient of Determination – (R2)
◼
◼
Regression Coefficients
◼
◼
The percent of variation in the dependent variable (Y)
accounted for by variation in the independent variable
(X). The closer to 1 the better.
Coefficient on x-variable measures the slope of the
trend Line. (Positive Sign – Positive Relationship,
Negative Sign – Negative Relationship)
t-statistic and p-value are used to indicate the
observed level of significance.
4
Using Excel for Statistics
Office 2010 and 2013
1
Concepts Covered
◼
Statistics
– Descriptive Statistics
– Histograms
– Hypothesis Testing
– Scatter Plots
– Regression Analysis
2
To Set Up Statistical Package
◼
◼
◼
◼
Click File Tab, and Then Click Options.
Click Add-ins. In View and Manage Box,
Select Analysis ToolPak.
Click Go.
In the Add-Ins Available Box, Select Analysis
ToolPak Check Box and Click OK. (If ToolPak
Is Not Listed, Click Browse to Locate It.)
3
Using Excel:
Descriptive Statistics
◼
◼
◼
◼
◼
Click Data/Data Analysis (Far Right) /Descriptive
Statistics & OK.
Put Checkmarks on Summary Statistics, 95% or
99% Confidence Interval, & Labels in First Row
Boxes.
Move Cursor to Input Range Window, Highlight
Data to Analyze including Labels, & Click OK.
Your Data will Appear on New Worksheet.
Widen Columns by Clicking
Home/Format/AutoFit Column Width.
4
Using Excel:
Constructing Histograms
◼
◼
◼
◼
◼
Click Data/Data Analysis/Histogram & OK.
Put Checkmarks on Chart Output & New
Worksheet Boxes.
Move Cursor to Input Range Window, Highlight
Data Going into Histogram.
Move Cursor to Input Bin Range, Highlight Data
Showing Upper Value of Each Bin & Click OK.
Histogram will be on New Worksheet. You May
Lengthen it by Clicking Blank Space in Window,
Moving Cursor to Window Bottom Line & Holding
Down Mouse Button as You Pull Down Window.
5
Using Excel:
Hypothesis Testing
◼
◼
◼
◼
◼
◼
Go to Sheet One.
Click Data/Data Analysis/ and the Appropriate
Statistical Test. Then Click OK.
On New Window Check Labels Box and Put
Cursor on Variable 1 Range.
Highlight Variable 1 Data Including Label.
Put Cursor on Variable 2 Range & Highlight
Variable 2 Data (Including Label). Then Click
OK.
Click Home/Format/AutoFit/Column Width
6
Using Excel:
Scatter Plots
◼
◼
◼
◼
Go to Sheet One.
Highlight Data (Be Sure X Values are in Left
Column and Y Values are in Right Column).
Click Insert/Scatter. Pull down menu and click
Upper Left Icon.
Click a Datum Point on Chart with Right Mouse
Key, Add Trendline, & Click Linear.
7
Using Excel:
Regression Analysis
◼
◼
◼
◼
◼
◼
Go to Sheet One.
Click Data/Data Analysis (On Far Right)
/Regression & Click OK.
On New Window Check Labels Box and Put
Cursor on X Range.
Highlight X Data Including Label.
Put Cursor on Y Range & Highlight Y Data
(Including Label), Then Click OK.
Click Home/Format/AutoFit Column Width.
8
How to Do a Regression in
Excel – Office 2003
A Tutorial for Gateway Students
1. Enter the data into a
spreadsheet
2. Tools/DataAnalysis/Regression
3. Enter the dependent variable in
the “y” column and the independent
variable (or variables) in the “x”
columns
4. Indicate where output should go
(the 1st cell in the range works)
5. The basic regression is done.
(You may need to widen columns)
Purchase answer to see full
attachment
SOLUTION: BUS302 California State Total Costs of Physician Visits vs Number of Employees Paper