Need help completing the spreadsheet and answer questions at end of case.

sh is ar stu ed d vi y re aC s ou ou rc rs e eH w er as o. co m Th https://www.coursehero.com/file/12351394/BedfordClinicsPracticeValuation/ sh is ar stu ed d vi y re aC s ou ou rc rs e eH w er as o. co m Th https://www.coursehero.com/file/12351394/BedfordClinicsPracticeValuation/ sh is ar stu ed d vi y re aC s ou ou rc rs e eH w er as o. co m Th https://www.coursehero.com/file/12351394/BedfordClinicsPracticeValuation/ sh is ar stu ed d vi y re aC s ou ou rc rs e eH w er as o. co m Th https://www.coursehero.com/file/12351394/BedfordClinicsPracticeValuation/ sh is ar stu ed d vi y re aC s ou ou rc rs e eH w er as o. co m Th https://www.coursehero.com/file/12351394/BedfordClinicsPracticeValuation/ sh is ar stu ed d vi y re aC s ou ou rc rs e eH w er as o. co m Th https://www.coursehero.com/file/12351394/BedfordClinicsPracticeValuation/ sh is ar stu ed d vi y re aC s ou ou rc rs e eH w er as o. co m Th https://www.coursehero.com/file/12351394/BedfordClinicsPracticeValuation/ Powered by TCPDF (www.tcpdf.org) CASE24 CASE 25 11/20/2016 Student Version BEDFORD CLINICS Practice Valuation This case illustrates the valuation of a medical practice, including cash flow estimation and the use of both DCF and market multiple techniques. Furthermore, the DCF valuation consists of both the free operating cash flow (FOCF) and free cash flow to equity (FCFE) methods. The model consists of a complete base case analysis--no changes need to be made to the existing MODEL-GENERATED DATA section. However, all values in the student version INPUT DATA section have been replaced with zeros. Thus, students must determine the appropriate input values and enter them into the model. These cells are colored red. When this is done, any error cells will be corrected and the base case solution will appear. Note that the model does not contain any risk analyses, so students will have to create their own if required by the case. Furthermore, students must create their own graphics (charts) as needed to present their results. INPUT DATA: KEY OUTPUT: Year 1-5 revenue growth rate Year 6+ revenue growth rate Tax rate Fixed operating costs Fixed operating costs inflation rate Variable operating costs (before interest) as a % of revenue Annual capital investment requirement Capital investment inflation rate Depreciation growth rate Non-operating assets Cost of equity Cost of debt Percentage of debt in capital structure Number of physician FTEs Market multiple - Physician FTEs Market multiple - Net patient revenue Market multiple - EBITDA 0.0% 0.0% 0.0% $0 0.0% 0.0% $0 0.0% 0.0% $0 0.0% 0.0% 0.0% $0 0.00 0.00 Page 1 FOCF Method Value FCFE Method Value Market Multiple Value: Physician FTEs Net patient revenue EBITDA CASE24 MODEL-GENERATED DATA: Current Year 1 Net patient revenue Operating expenses (including depreciation) Earnings before interest and taxes (EBIT) Interest expense Earnings before taxes (EBT) Taxes Net profit $ 1,491,791 1,069,076 $ 422,715 25,575 $ 397,140 119,142 $ 277,998 $ Total Liabilities and Equity $ 2,351,451 $ $ $ $ 1,491,791 1,491,791 1,491,791 1,491,791 Year 2 $ $ 1,491,791 1,491,791 1,491,791 1,491,791 3,843,242 $ 5,335,033 $ $ FREE OPERATING CASH FLOW (FOCF) METHOD: Corporate cost of capital NOPAT Plus depreciation Less capital requirements (total retentions) FOCF Terminal value FOCF plus terminal value 0.0% Current $ Year 1 Year 2 $ 295,901 $ 11,070 15,000 291,971 $ 1,491,791 $ 11,070 1,502,861 $ 1,491,791 11,070 1,502,861 $ 291,971 $ 1,502,861 $ 1,502,861 Present value of future cash flows Non-operating assets Existing debt FOCF Method Value #DIV/0! (352,718) #DIV/0! FREE CASH FLOW TO EQUITYHOLDERS (FCFE) METHOD: Cost of equity Net profit Plus depreciation Less capital requirements (equity retentions) FCFE Terminal value FCFE plus terminal value Present value of future cash flows Non-operating assets 0.0% Current $ Year 1 Year 2 $ 277,998 $ 11,070 15,000 274,068 $ 1,491,791 $ 11,070 1,502,861 $ 1,491,791 11,070 1,502,861 $ 274,068 $ 1,502,861 $ 1,502,861 #DIV/0! Page 2 CASE24 FCFE Method Value #DIV/0! Page 3 CASE24 Copyright 2014 Health Administration Press #DIV/0! #DIV/0! $ $ $ - Page 4 CASE24 Year 3 $ Year 4 $ 1,491,791 1,491,791 1,491,791 1,491,791 $ 1,491,791 1,491,791 1,491,791 1,491,791 $ 6,826,824 $ 8,318,615 $ 9,810,406 Year 4 Year 5 $ 1,491,791 $ 11,070 1,502,861 $ 1,491,791 $ 11,070 1,502,861 $ $ 1,502,861 $ 1,502,861 1,491,791 11,070 1,502,861 #DIV/0! #DIV/0! Year 4 Year 5 $ 1,491,791 $ 11,070 1,502,861 $ 1,491,791 $ 11,070 1,502,861 $ $ 1,502,861 $ 1,502,861 1,491,791 11,070 1,502,861 #DIV/0! #DIV/0! $ $ $ $ $ $ Year 3 $ Year 3 $ 1,491,791 1,491,791 1,491,791 1,491,791 Year 5 $ $ $ Page 5 CASE24 END Page 6