# BUS302 California State Total Costs of Physician Visits vs Number of Employees Paper *** I just need questions 2 & 3 from the word document to be answered.

BUS302 California State Total Costs of Physician Visits vs Number of Employees Paper *** I just need questions 2 & 3 from the word document to be answered.

*** The instructions are included in the powerpoint attachment.

***** For each question, there MUST be at least 1 – 1 1/2 pages.

Use the guidelines for writing a report on the course web site and use the following questions as a guide:

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:

The total cost of physician visits vs. # of employees
# of employees vs. # of visits
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. 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
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)

attachment

Calculate the Price of your PAPER Now
Pages (550 words)
Approximate price: -

Why Choose Us

Top quality papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

We have hired a team of professional writers experienced in academic and business writing. Most of them are native speakers and PhD holders able to take care of any assignment you need help with.

Free revisions

If you feel that we missed something, send the order for a free revision. You will have 10 days to send the order for revision after you receive the final paper. You can either do it on your own after signing in to your personal account or by contacting our support.

On-time delivery

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & confidential

We use several checkers to make sure that all papers you receive are plagiarism-free. Our editors carefully go through all in-text citations. We also promise full confidentiality in all our services.

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

## Calculate the price of your order

Total price:
\$0.00

How it works?

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.