te ForrmulAy Project 2 (40 pts.) Central Hospital - A Capital Budgeting Oblective Central Hospital, established in 1904, is the premier hospital in Oklahid onditions nt over Es Patients travel from all over the county to be diagnosed for rare from Central's leading staff of doctors and nurses. The hospital r receives an influx of over 25,000 visitors diversify, Zach Moore, CEO, is each year and has grown rapidly since its establishment. In an effort to heart conditions and receive treatment Thatcher Clinic, in memory of Central's late Uco President, R Richard Thatcher, and treat the public for of the project is 15%. Central Hospital is a for- general illnesses and diseases. The appropriate WACC of the project iital proceed ng clinic. The institution will be renamed profit company and has a marginal tax rate of 40%. Should Central Clinic project? Operating Cash Flows-All revenues and expenses occur at ye year year-end and begin at the end of the first Revenue ous space. The clinic is capable of treating 20.000 through the use of foldout beds and One of the key features of the target clinic is its enormous space. patients a year and is expandable to accommodate 90,000 patients more efficient use of the waiting areas. Moore looked at previously recorded patient counts at the oof these 40% In order to estimate future profitability, clinic number of patient Moore predicts that in the year of Thatcher Clinic's initial opening tsi visits will be between 50,000 and 60,000 with the most likely number being S3,000. (60%) are expected to be children (Adults) d 6% with the most For the next 5 years (till t-6), the growth in patient base should be between 2% an After that (till perpetuity), the growth rate for Thatcher Clinic is expected to be aches capacity. Although the number of patients is expected to grow each year, the likely number being 4%. 3% until the clinic re proportion of adult and child patients will remain constant. Thatcher Clinic will provide general patient-care that includes three types of services: 1) simple procedures, 2) in-house lab tests, and 3) routine checkups. Last year, Central Hospital paid Phillips Consulting $250,000 to conduct market research on the services demanded by patients. Based on their analysis, Moore estimates that 25% of all adult patients are expected to go in for simple procedures, 35%are expected to have in-house lab tests done, and the remaining go in for routine checkups. For children, Moore estimates that 10% are expected to go in for simple procedures, 20% are expected to have in-house lab tests done, and the remaining children have routine checkups. The demand for all patient lab test and simple procedures are normally distributed with standard errors of 2% and 3% respectively. Moore will use these patient behavior trends to forecast its varying revenue streams. T information along with the price of each of these three forms of patient care are available in the chart below his Seruces 665 Proportion of Adults Charged Vaccingtions& splints 130 In-House Lab Test S135 35%, std dev 2% 3s toutine checkups 5140 Regular annual exoms per visit. incur only one of the above services of revenue for Thatcher Clinic, however volatlity of the monetary amounts For forecasting purposes, Moore assumes that each patient will in Private and public funding for the clinic are an additional source Moore will ignore them for the time being due to the extreme vo Expenser The variable cost of each of the above services for sterile needles, ton s, dressing gowns etc. average out to $55 per simple procedure, $30 per in-house lab ne checkup. and $35 These costs incurred are the same for both adults and children. The labor force at the dinc is comprised of 4 docters & phrpakctan's snta and directr are sav ouble ns assistants director are Director, and 4 office staff employees. The doctors, physician's workers, where as 310 days out of the year, the 12 nurses work 8 hours at a e the current minimum wage and the 4 office staff members make: Hourly Workers: The prevailing minimum wage is $7.25. Office staff make 513. $13.00/hr 6 physician's assistants and Uniform performance Salaried Workers: As the only salaried employees at the clinic, the 4 doctors, single Clinic Director earn $130,000, $65000, and $150,000, respectively bonuses are alliocated equally to all salari FICA and benefits for all employees are expected to be 20% of their salary and pay. All medical facilities pay a high premium for malpractice insurance, and Thatcher Clinic is no different. Moore expects that each doctor will cost the clinic $25,000 in premiums per year and each physicians ed employees and occur at the end of the year assistant will cost the clinic $10,000 in premiums per year In accordance to EMTALA (1986 Emergency Medical Treatment and Active Labor Act), hospitals and clinics are required to provide care to anyone needing emergency healthcare treatment regardless of citizenship, legal status, or ability to pay. Moore expects that this unfunded mandate will result in uninsured patient costs that consume 20% of total revenue. G&A expenses are fixed at $430,000 per year As stated previously, the acquisition cost of the clinic amounts to $12 million. It will be depreciated on a straight-line basis over the next 30 years. In addition to the initial acquisition cost, in each year starting trotalexpnditures upgrades and maintenance to the facility will require annual capitale which will be tax deductible expenses. expenditures of 12% of revenues ivables and The total net working capital requir inventory items such as cotton balls, swabs, band-aids, etc. will be ement at the beginning of the project for accounts rece $1,700,000. t 40%. As was implicit in all the can be completely recuperated For the entire analysis, assume that Thatcher Clinic will be taxed a discussions in class, assume any tax breaks from operating losses answer these questions you will be using several excel functions. 1. Capacity Constraint: What is the maximum number of years it will t . To its capacity of 90,000 patients? Base-case Analysis: Under the ba cash flows the clinic will generate from year O to year 10. Also cale subsequent questions is to build an excel-based model that can calculate feasible set of values of the input drivers. T 1. Identify all 2. se-case/expected demand, revenues, and costs, estimate the total ahr Is it a profitable project? The best way to accomplish this goal and ansvwr the NPV of Thatcher Clinic under any o accomplish this goal, the following hints will be useful. the input drivers in the model. Separate out the input drivers whose values are known with certainty and those whose value are not known with certainty 2. In building the model, put all the input drivers at the top left of the sheet. You might find it usef ul to group (in a logical way) all the input drivers whose values are not known with certainty together at the top. Below those you can group (ogain, in a logical way) all the input drivers whose values are known for certainty 3. Below the input drivers, set up the formulas to calculate the cash flows of the project for each 4. Right below where you have calculated the cash flows for year T, set up the formula to 5. Set up the formula to calculate the NPV year till T+1 where T is the year capacity is reached calculate the terminal value of the project at T 3. Scenario Analysis: Copy the model onto a new spreadsheet and calculate expected project NPV of STO Thatcher Clinic for a best and worstcase. What is the expected NPV, the standard deviation of NPV and the Coefficient of Variation? Based on the analysis thus far, explain your results. 4. Investment Decision: Do you think Moore should proceed with the Thatcher Clinic project? Explain. te ForrmulAy Project 2 (40 pts.) Central Hospital - A Capital Budgeting Oblective Central Hospital, established in 1904, is the premier hospital in Oklahid onditions nt over Es Patients travel from all over the county to be diagnosed for rare from Central's leading staff of doctors and nurses. The hospital r receives an influx of over 25,000 visitors diversify, Zach Moore, CEO, is each year and has grown rapidly since its establishment. In an effort to heart conditions and receive treatment Thatcher Clinic, in memory of Central's late Uco President, R Richard Thatcher, and treat the public for of the project is 15%. Central Hospital is a for- general illnesses and diseases. The appropriate WACC of the project iital proceed ng clinic. The institution will be renamed profit company and has a marginal tax rate of 40%. Should Central Clinic project? Operating Cash Flows-All revenues and expenses occur at ye year year-end and begin at the end of the first Revenue ous space. The clinic is capable of treating 20.000 through the use of foldout beds and One of the key features of the target clinic is its enormous space. patients a year and is expandable to accommodate 90,000 patients more efficient use of the waiting areas. Moore looked at previously recorded patient counts at the oof these 40% In order to estimate future profitability, clinic number of patient Moore predicts that in the year of Thatcher Clinic's initial opening tsi visits will be between 50,000 and 60,000 with the most likely number being S3,000. (60%) are expected to be children (Adults) d 6% with the most For the next 5 years (till t-6), the growth in patient base should be between 2% an After that (till perpetuity), the growth rate for Thatcher Clinic is expected to be aches capacity. Although the number of patients is expected to grow each year, the likely number being 4%. 3% until the clinic re proportion of adult and child patients will remain constant. Thatcher Clinic will provide general patient-care that includes three types of services: 1) simple procedures, 2) in-house lab tests, and 3) routine checkups. Last year, Central Hospital paid Phillips Consulting $250,000 to conduct market research on the services demanded by patients. Based on their analysis, Moore estimates that 25% of all adult patients are expected to go in for simple procedures, 35%are expected to have in-house lab tests done, and the remaining go in for routine checkups. For children, Moore estimates that 10% are expected to go in for simple procedures, 20% are expected to have in-house lab tests done, and the remaining children have routine checkups. The demand for all patient lab test and simple procedures are normally distributed with standard errors of 2% and 3% respectively. Moore will use these patient behavior trends to forecast its varying revenue streams. T information along with the price of each of these three forms of patient care are available in the chart below his Seruces 665 Proportion of Adults Charged Vaccingtions& splints 130 In-House Lab Test S135 35%, std dev 2% 3s toutine checkups 5140 Regular annual exoms per visit. incur only one of the above services of revenue for Thatcher Clinic, however volatlity of the monetary amounts For forecasting purposes, Moore assumes that each patient will in Private and public funding for the clinic are an additional source Moore will ignore them for the time being due to the extreme vo Expenser The variable cost of each of the above services for sterile needles, ton s, dressing gowns etc. average out to $55 per simple procedure, $30 per in-house lab ne checkup. and $35 These costs incurred are the same for both adults and children. The labor force at the dinc is comprised of 4 docters & phrpakctan's snta and directr are sav ouble ns assistants director are Director, and 4 office staff employees. The doctors, physician's workers, where as 310 days out of the year, the 12 nurses work 8 hours at a e the current minimum wage and the 4 office staff members make: Hourly Workers: The prevailing minimum wage is $7.25. Office staff make 513. $13.00/hr 6 physician's assistants and Uniform performance Salaried Workers: As the only salaried employees at the clinic, the 4 doctors, single Clinic Director earn $130,000, $65000, and $150,000, respectively bonuses are alliocated equally to all salari FICA and benefits for all employees are expected to be 20% of their salary and pay. All medical facilities pay a high premium for malpractice insurance, and Thatcher Clinic is no different. Moore expects that each doctor will cost the clinic $25,000 in premiums per year and each physicians ed employees and occur at the end of the year assistant will cost the clinic $10,000 in premiums per year In accordance to EMTALA (1986 Emergency Medical Treatment and Active Labor Act), hospitals and clinics are required to provide care to anyone needing emergency healthcare treatment regardless of citizenship, legal status, or ability to pay. Moore expects that this unfunded mandate will result in uninsured patient costs that consume 20% of total revenue. G&A expenses are fixed at $430,000 per year As stated previously, the acquisition cost of the clinic amounts to $12 million. It will be depreciated on a straight-line basis over the next 30 years. In addition to the initial acquisition cost, in each year starting trotalexpnditures upgrades and maintenance to the facility will require annual capitale which will be tax deductible expenses. expenditures of 12% of revenues ivables and The total net working capital requir inventory items such as cotton balls, swabs, band-aids, etc. will be ement at the beginning of the project for accounts rece $1,700,000. t 40%. As was implicit in all the can be completely recuperated For the entire analysis, assume that Thatcher Clinic will be taxed a discussions in class, assume any tax breaks from operating losses answer these questions you will be using several excel functions. 1. Capacity Constraint: What is the maximum number of years it will t . To its capacity of 90,000 patients? Base-case Analysis: Under the ba cash flows the clinic will generate from year O to year 10. Also cale subsequent questions is to build an excel-based model that can calculate feasible set of values of the input drivers. T 1. Identify all 2. se-case/expected demand, revenues, and costs, estimate the total ahr Is it a profitable project? The best way to accomplish this goal and ansvwr the NPV of Thatcher Clinic under any o accomplish this goal, the following hints will be useful. the input drivers in the model. Separate out the input drivers whose values are known with certainty and those whose value are not known with certainty 2. In building the model, put all the input drivers at the top left of the sheet. You might find it usef ul to group (in a logical way) all the input drivers whose values are not known with certainty together at the top. Below those you can group (ogain, in a logical way) all the input drivers whose values are known for certainty 3. Below the input drivers, set up the formulas to calculate the cash flows of the project for each 4. Right below where you have calculated the cash flows for year T, set up the formula to 5. Set up the formula to calculate the NPV year till T+1 where T is the year capacity is reached calculate the terminal value of the project at T 3. Scenario Analysis: Copy the model onto a new spreadsheet and calculate expected project NPV of STO Thatcher Clinic for a best and worstcase. What is the expected NPV, the standard deviation of NPV and the Coefficient of Variation? Based on the analysis thus far, explain your results. 4. Investment Decision: Do you think Moore should proceed with the Thatcher Clinic project? Explain