I need help with this case study.

  Please see exhibit 6.1 and 6.2 in attachments

The excel spreadsheet is also attached

 Questions:

1. Answer the following questions related to this case.  Use the Excel spreadsheet as needed.  Show computations, as necessary, to support your answers.
2. Using the historical data as a guide (Exhibit 6.1), construct a pro forma (forecasted) profit and loss statement for the clinic’s average month for all of 2010 assuming the status quo. With no change in (volume) utilization, what profit (loss) is the clinic projected to make?
3. Consider the clinic’s situation without the new marketing program. How many additional daily visits must be generated to break even?
4. Consider the clinic’s situation with the new marketing program. How many additional daily visits must be generated to break even?
5. Focus solely on the expected profitability of the proposed marketing program. How many incremental daily visits must the program generate to make it worthwhile? (In other words, how many incremental visits would it take to pay for the marketing program, irrespective of overall clinic profitability?) Construct a graph showing the expected profitablilty of the proposed program versus incremental daily visits.
6. Thus far, the analysis has considered the clinic’s near-term profitability, that is, an average month in 2010. Recast the pro forma (forecasted) profit and loss statement developed in Question 1 for an average month in 2015, five years hence, assuming that volume is constant over time. (Hint: You must consider likely changes in revenues and costs due to inflation and other factors. The idea here is to see if the clinic can “inflate” its way to profitablity even if volume remains flat.)
7. Although you are basically satisfied with the analysis thus far, you are concerned about the uncertainties inherent in the revenue and expense data supplied by the clinic’s director. Assess each element in your Question 1 pro forma profit and loss statement. Are any items more uncertain than the others? How could uncertainty be worked into the analysis? What additional information, if any, might you want to obtain from the clinic’s director?
8. Suppose you just found out that the $3,215 monthly malpractice insurance charge is based on an accounting allocation scheme that divides the hospital’s total annual malpractice insurance costs by the total annual number of inpatient days and outpatient visits to obtain a per episode charge. Then, the per episode value is multiplied by each department’s projected number of patient days or outpatient visits to obtain each department’s malpractice cost allocation. Does this allocation scheme bias your break-even analysis? Explain. (No calculations are necessary.)
9. After all the work performed thus far in the analysis, you suddenly realize that the hospital, as a for-profit corporation, must pay taxes. What impact does tax status have on your break-even analysis?
10. Does the clinic have any value to the hospital beyond that considered by the numerical analysis just conducted? Do the actions by Baptist Hospital have any bearing on the final decision regarding the clinic?
11. What is your final recommendation concerning the future of the walk-in clinic?

Background of case:

Columbia Memorial hospital an acute care hospital with 300 beds and 160 staff drs is one of 75 hospitals owned and operated by health Services of America, a for-profit, publicly owned company. Although two other acute care hospitals serve the same population, Columbia historically has been highly profitable because of its well-appointed facilities, fine medical staff, and reputation for quality care and the amount of individual attention it gives to patients.  In addition to the standard range of inpatient and outpatient services, Columbia operate an emergency department within the hospital complex and a stand-alone walk in clinic located across the street from the area’s major shopping mall, about two miles from the hospital.

Patients, who need immediate care for injuries or illness, be it a nail gun puncture or a sore throat are increasingly turning to walk in clinics (urgent care centers). These clinics aim to fill the gap between the growing shortage of primary care physicians and already crowded (and expensive) emergency departments.  Walk in clinics are staffed by Drs, offer wait times as little as a few minutes, and charge $60 to $200, depending on the procedure.  Furthermore, no appointments are necessary and evening and weekend hours are frequently available.  Finally, many offer discounts to the uninsured, and for those with coverage, copayments are typically much less than for emergency department visits.  Currently 8,000 of these clinics exist around the country, including about 1200 affiliated with hospitals.

                Mike Reynolds, Columbia’s chief executive officer (CEO) is concerned about the clinics overall financial soundness.  About ten years ago, all three area hospitals jumped onto the walk-in-clinic bandwagon and within a short time, there were five such clinics scattered around the city. Now only three are left and none of them appears to be a big money maker.  Mike wonders if Columbia should continue to operate its clinic or close it down.  The clinic is currently handling a patient load of 45 visits per day, but it has the physical capacity to handle many more visits-up to 85 a day.  Mikes decision has been complicated by the fact that Rose Daniels, Columbia’s marketing director, has been pushing to embark on a new marketing program for the clinic.  She believes that an expanded marketing effort aimed at local business would bring in the number of new patients needed to make the clinic a financial winner.

                Mike has asked Brent Williams, Columbia’s chief financial offer, to look into the whole matter of the walk-in clinic. In their meeting, Mike stated that he visualizes three potential outcomes for the clinic: 1. The clinic could be closed. 2.) The clinic could continue to operate as is-that is, without expanding its marketing program or 3.) the clinic could continue to operate, but with the expanded marketing effort.  As starting point for the analysis, Brent has collected the most recent historical financial and operating data for the clinic, which are summarized in exhibit 6.1.  In assessing the historical data, Brent noted that one competing clinic had recently closed its doors.  Furthermore, a review of several years of financial data revealed that the Columbia clinic does not have a pronounced seasonal utilization pattern.

                Next Brent met several times with the clinic’s director. The primary purpose of the meetings was to estimate the additional costs that would have to be borne if clinic volume rose above the current January/February average level of 45 visits per day.  Any incremental usage would require additional expenditures for administrative and medical supplies, estimated to be $3.00 per patient visit for medical supplies, such as tongue blades and rubber gloves, and $.50 per patient visit for administrative supplies, such as file folders and clinical record sheets.

                Because of the relatively low volume level, the clinic has purposely been staffed at the bare minimum.  In fact, some clinic employees have started to grumble about not being able to do their jobs as well because of overwork.  Thus, any increase in the number of patient visits would require immediate administrative and medical staff increases.  Furthermore, as the number of visits increase, the clinic would have to hire additional staff members.  The incremental costs associated with increased volume are summarized in Exhibit 6.2

                In addition, Brent learned that the building is leased on a long-term basis.  Columbia could cancel the lease, but the lease contract calls for a cancellation penalty of three months’ rent ($37,500) at the current lease rate.  Brent was startled to read in the newspaper that Baptist Hospital, Columbia’s major competitor, had just brought the city’s largest primary care group practice, and Baptist’s CEO said that more group practice acquisitions are planned. Brent wondered whether Baptist’s actions would influence the decision regarding the clinics fate.

                Finally, Brent met with Rose (Columbia’s marketing director) to learn more about the proposed expansion of the clinic’s marketing program.  The primary focus of the new marketing program would be on occupational health service (OHS). OHS involves providing medical care to local businesses, including physical examinations for managers and employees; treatment of illnesses that occur during work hours; and treatment of work-related injuries, especially those covered by Workers’ Comp. Although some of the clinic’s current business is OHS related, Rose believes that a strong marketing effort, coupled with specialized OHS record keeping, could bring additional patients to the clinic.  The proposed marketing expansion requires a marketing assistant who will run the clinic’s OHS program.  Additionally, the new marketing program would incur costs for newspaper, radio, TV, and Internet ads as well as for brochures and handouts.  The incremental costs associated with the new marketing program are also summarized in exhibit 6.2.

                With a blank spread sheet on the screen, Brent began to construct a model that would provide the information needed to help the board make a rational, informed decision. At first Brent planned to conduct a standard capital budgeting analysis that focused on the profitability of the clinic as measured by net present value or internal rate of return.  Then he realized that the expanded marketing program requires no capital investment.  He also realized that no valid data are available on the incremental increase in visits that would be generated either by an increasing population base or by the expanded marketing program.

                Finally he remembered that mike requested that the analysis consider the inherent profitability of the clinic without the expanded marketing program.

                In addition, Brent wonders if the clinic could inflate its way to profitability; that is, if volume remained at its current level, could the clinic be expected to become profitable in, say five years, solely because of inflationary increases in revenues? Finally Brent is concerned about whether or not the analysis gave the clinic full credit for its financial contributions to the hospital. Brent does not want to change the spreadsheet at this late date, but he does want to make sure that any additional financial value is at least considered qualitatively. Overall Brent must consider all relevant factors- both quantitative and qualitative-and come up with a recommendation regarding the future of the clinic.