Show work in Excel Sheet and formulas used 1. County Hospital contracts for an anesthesiologists services and can select from two providers. Provider 1 charges a guaranteed $16,000 a month, while Provider 2 charges $12,000. For every service the anesthesiologist provides on a procedure, the hospital incurs additional expenses of $200 when using Provider 1 and $300 when using Provider 2. For every time the anesthesiologist provides a service, the hospital gains revenue of $1200 just for the anesthesiologists portion of the procedure. Be sure to make excel do the calculations and indicate any units for your answers.

• How many procedures would an anesthesiologist have to work on monthly for the hospital to Break Even for each Provider? (20 pts)
• At what volume in monthly procedures would the two Providers have the same cost? (15 pts)
• The hospitals revenue from the anesthesiologist portion of the procedure is a sixth of the overall procedures revenue. What would the hospitals total monthly revenue be at the point of indifference? (15 pts)
• The hospital has identified that the customer satisfaction rates are higher for Provider 1 vs. Provider 2 and this rating can get their insurance reimbursement for the entire procedure 10% higher than the existing total procedure revenues. Assuming there would be no increase in fixed or variable expenses for the total procedure, how much would the annual profit increase at the point of indifference if the County Hospital contracted with Provider 1? (10 pts)
• Assuming everything from parts a-d of the problem, what should County Hospitals anesthesiologist contract strategy be and why? (extra credit 20 pts)

2. The ticketing office at a professional sports venue has noticed that ticket sales seem to fluctuate. The manager wants to introduce the strategy of demand-pricing in order to maximize attendance and revenue. Based on historical sales, manager wonders if there is a connection between winning percentage (in decimal form) of the team and attendance (seats).

Attendance (seats)	Winning Percentage (decimal form)
78,000	0.6
89,000	0.67
93,000	0.7
59,000	0.5
65,000	0.55
97,000	0.9
86,000	0.6
77,000	0.55
91,000	0.8
61,000	0.53

a. Run a regression analysis. What is the regression equation? (15 pts)

b. Is the model statistically significant at the 0.05 level? (10 pts)

c. How much of the variability in attendance is determined by the winning percentage? (10 pts)

d. What is the forecasted attendance if winning percentage is 0.72? (10 pts)

e. Develop a regression equation by adding a non-linear variable for winning percentage (decimal form) and evaluate these additional variables.

Opponents winning percentage (decimal form)	Conference vs. Non-conference
0.7	Non-Conference
0.5	Conference
0.4	Conference
0.9	Non-Conference
0.6	Non-Conference
0.5	Conference
0.6	Conference
0.7	Conference
0.8	Non-Conference
0.5	Non-Conference

What is the regression equation only for those variables that are statistically significant? (35 pts)

f. What is the forecasted attendance if winning percentage is 0.72, opponents winning percentage is 0.72 and it is a Conference game? (10 pts)