Using Excel and PowerPoint, compare 3 facilities data for acquisition. Determine the NVP and IRR for each facility and construct tables for comparison. Choose the best option for acquisition and then determine the expected NVP and IRR.
Im studying for my Excel class and dont understand how to answer this. Can you help me study?
Major Organizational Decision
Many of the major decisions undertaken by healthcare organizations (HCOs) are guided by managerial accounting tools and methods. Given the significant capital requirements that are often required to build a hospital, expand a service, or even purchase advanced technology, senior leaders and operational managers look to managerial accounting to make sense of available information.
It is important to understand that managerial accounting assessments are not perfect but that they provide an evidence-based approach to decision-making. Estimates about future cash flows, interest rates, and even patient demand undoubtedly contain a measure of error. Two available options for protecting the accuracy of decisions include sensitivity analysis prior to decision and variance analysis after decisions are made.
Sensitivity analysis comes in many forms but essentially seeks to determine the impact of inaccurate or even evolving decision support input variables. By providing best, likely, and worst-case input values, sensitivity analysis provides HCO managers and leaders with a risk assessment of sorts. Variance analysis to assess operational performance compared to developed budgets and managerial accounting estimates, provides an opportunity to change inputs to course correct when necessary. The initial decision must be continually evaluated to provide the desired profit margin and outcomes. Sensitivity analysis can also be associated with variance analysis to guide decisions. For example, if variance analysis shows that projected patient volume hasnt materialized as expected in the likely scenario, costs can be adjusted downward and/or alternative patient volume or other revenue sources can be pursued as already outlined in the worst-case scenario. These techniques will increase the accuracy of decision support analyses and tools and protect margins and outcomes.
As all departments dont produce revenue to cover their expenses, these costs must be methodically and equitably distributed to departments and services that generate profit and loss (P&L). Critical and necessary servicessuch as housekeeping, internal audit, and human resourcesdo not directly provide revenue. However, without their support, surgery, oncology, and other product lines would be unable to complete their revenue-generating work. As a result, consistently capturing all expenses and outflows, and allocating them comprehensively, is necessary to accurately assess break-even points, contribution margins, and other financial outcomes.
Finally, managerial accounting tools and techniques can help to guide financing decisions. Debt financing provides the benefit of retaining ownership and has tax advantages but has strict qualification requirements. Equity financing eliminates the burden of a loan that must be repaid and sometimes ignores creditworthiness but ownership must be shared producing a loss of some control. Managerial accounting helps to decide whether debt, equity or a combination is in the best interests of the HCO. Under each option, an analysis of the cost of capital provides an assessment of how much financing would cost the HCO. Revenues or inflows must cover these costs as well as produce any desired margin to define success for the HCO.
QVC Medical Group (QVC MG) is a profitable and very busy multi-specialty group practice. As a part of its growth strategy, QVC MG is considering purchasing one of three medical practices in the community. Each of the practices provides a unique strategic advantage that is aligned with QVC MGs long-term plans.
Clinics to compare:
Senior Clinic is located in a community offering extensive and very popular services for older patients. Junior Clinic/Pediatric Clinic serves a growing but younger population with the largest population of children in the area. Sports Clinic provides sports medicine services and is the preferred provider for the local all-state high school teams as well as the local college sports programs.
As the vice president of operations for QVC MG, youve been asked to lead this effort and recommend a decision to the board. Although all three practices are very attractive and have expressed an interest in being acquired, the board will only choose one. The others may be considered at a later date.
Youve collected the following data related to acquisition costs, cash inflows, and overhead expenses for the next 5 years. The cost of capital is determined to be 11%:
Senior Clinic will cost $20M to acquire. Additionally, there are several roofing and facility maintenance needs that will cost $200,000 in Year 1, and $150,000 in Year 2. Finally, lab services will cost $100,000 per year beginning in Year 1. Expected cash inflows from Senior Clinic are $4.5M, $8.5M, $10.265M, $11M, and $500K for Years 1 to 5.
Pediatric Clinic will cost $19M to acquire. The practice is only two years old, and the facilities are in excellent condition. However, the clinic will have debt payments of $130,000 in Years 4 and 5. Finally, Pediatric Clinic has a lab outreach program that generates $20,000 in revenue every year beginning in Year 1. Half of this revenue will flow to QVC MG. In addition to the lab revenue, expected cash inflows from Pediatric Clinic are $6M, $6.5M, $7M, $7.5M, and $8M for Years 1 to 5.
Sports Clinic will cost $21M to acquire. The clinic is in a state-of-the-art facility with owned and leased equipment. Annual lease payments are $90,000 per year and maintenance agreement costs are $50,000 per year, both beginning in Year 1. Finally, the clinic receives $75,000 per year from the local college for medical coverage beginning in Year 1, all of which will flow to QVC MG. In addition to the college revenue, expected inflows from Sports Clinic are $9M, 7.5M, $8.5M, $6M, and $3.25M for Years 1 to 5.
*** In addition to the above information, youve determined that for the selected clinic, the NPV probabilities are:
* 20% for the worst-case scenario(expected cash flow will equal 90% of data from above)
* 60% for the most-likely scenario (data above)
* 20% for the best-case scenario (expected cash flow will equal 108% of data above)
Finally, the board would like your recommendation on other financing options. Ignoring the previous 11% cost of capital, youve discovered that:
* equity financing costs 15%
* 20% debt financing costs 10% (after tax) with equity costing 16%
* 45% debt financing costs 11% (after-tax) with equity costing 17%
Assignment
As VP-Operations for QVC MG, assess each clinic option. In your assessment, develop tables showing the NPV and IRR for each option. After selecting a clinic to recommend, determine its expected NPV and make a financing (equity and/or debt) recommendation to the board.
Provide your supporting documentation (spreadsheets or solution sheets) and develop a PowerPoint presentation to present and explain your findings to the board.
Presentation Length: 7-10 slides with 150-200 words (speaker notes) per slide
Excel Workbook Length: Three Excel Workbooks
