CASE 22 Student Version Copyright 2014 Health Administration Press 8/13/13 NORTHWEST SUBURBAN HEALTH SYSTEM Make or Buy Analysis This case examines three alternatives for handling the printing needs within a large health system. The case emphasizes cash flow analysis and cost of capital considerations for both not-for-profit and for-profit subsidiaries. The model calculates NPV, IRR, and MIRR on the basis of input data for three different printing alternatives. Note that the model extends out to Column H. The model consists of a complete base case analysisno 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. Finally, note that the some of the inputs regarding the new (incremental) values associated with expanding the printshop are somewhat difficult to identify, so students using the student version of this model need to be careful when choosing these values. INPUT DATA: KEY OUTPUT: General: Overall and Divisional Costs of Capital: Projected general inflation rate 0.0% Corporate 0.0% Projected printing volume increase 0.0% PROPERTIES 0.0% Supplies as % of billing 0.0% SUPPORT 0.0% Divisional Cost of Capital Data: Alternative 1: Overall corporate 0.0% Cost of capital 0.0% FP subsidiary (PROPERTIES) NPV #DIV/0! Debt weight 0.0% IRR #VALUE! Cost of debt 0.0% MIRR #DIV/0! Equity weight 100.0% Cost of equity 0.0% Alternative 2: Tax rate 0.0% Cost of capital 0.0% NFP subsidiary (SUPPORT) NPV $0 Debt weight 0.0% IRR #NUM! Cost of debt 0.0% MIRR #NUM! Equity weight 100.0% Cost of equity 0.0% Alternative 3: Cost of capital : Current (2013) Costs and Values: In-house 0.0% Annual lease costs $0 External 0.0% Annual utilities/insurance $0 NPV (Internal) $0 Annual labor $0 NPV (Total) $0 Annual material costs $0 IRR #NUM! Annual depreciation exp $0 MIRR #NUM! Market value of existing equipment $0 New Costs: Added (incremental) lease costs $0 Building remodeling $0 Equipment investment $0 Annual depreciation $0 Added annual labor costs $0 Added annual utilities/insurance $0 Annual vehicle expense $0 Marketing of external printing $0 Incremental Savings: Graphics printing $0 Forms printing $0 Profits on Commercial Printing: Pre-tax profits for 2014 $0 Pre-tax profits for 2015 $0 Long-term profit growth rate 0.0% Discount Rates Applied to the CFs: Alternative 1 0.0% Hint:The discount rates must reflect the riskiness of the cash flows Alternative 2 0.0% being discounted, as opposed to the divisional placement of the Alternative 3 printshop. Also, it might be best to first consider the appropriate In-house cash flows 0.0% rate for Alternative 2, and then choose the rates for Alternatives External cash flows 0.0% 1 and 3 to reflect their risk relative to that of Alternative 2. MODEL-GENERATED DATA: Alternative 1 - No Print Shop/All Outside Vendors: Capital Flows 2013 2014 2015 2016 2017 2018 Sale of equipment $0 Incremental Savings Materials $0 $0 $0 $0 $0 Lease $0 0 0 0 0 Utilities 0 0 0 0 0 Labor 0 0 0 0 0 Total savings $0 $0 $0 $0 $0 $0 Incremental Costs Vendor billing #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Total costs #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Net cash flow $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Project cost of capital 0.0% Net present value #DIV/0! Internal rate of return #VALUE! Modified IRR (MIRR) #DIV/0! Alternative 2 - Expand Print Shop in NFP Subsidiary (SERVICES): Capital Flows 2013 2014 2015 2016 2017 2018 Building remodeling $0 Equipment purchases 0 Total investment $0 Incremental Savings Graphics printing $0 $0 $0 $0 $0 Forms printing 0 0 0 0 0 Total savings $0 $0 $0 $0 $0 Incremental Costs Lease expense $0 $0 $0 $0 $0 Printing supplies 0 0 0 0 $0 Utilities 0 0 0 0 0 Labor 0 0 0 0 0 Vehicle expense 0 0 0 0 0 Total costs $0 $0 $0 $0 $0 $0 Net cash flow $0 $0 $0 $0 $0 $0 Project cost of capital 0.0% Net present value $0 Internal rate of return #NUM! Modified IRR (MIRR) #NUM! Alternative 3 - Expand Print Shop in FP Subsidiary (PROPERTIES): Capital Flows 2013 2014 2015 2016 2017 2018 Building remodeling $0 Equipment purchases 0 Total investment $0 Incremental Savings Graphics printing $0 $0 $0 $0 $0 Forms printing 0 0 0 0 0 Total savings $0 $0 $0 $0 $0 Incremental Costs Lease expense $0 $0 $0 $0 $0 Printing supplies 0 0 0 0 $0 Utilities 0 0 0 0 0 Labor 0 0 0 0 0 Vehicle expense 0 0 0 0 0 Total costs $0 $0 $0 $0 $0 $0 Revenues - Costs $0 $0 $0 $0 $0 $0 Taxes 0 0 0 0 0 0 After-tax op CF $0 $0 $0 $0 $0 $0 Depreciation shield 0 0 0 0 0 Internal work cash flow $0 $0 $0 $0 $0 $0 Pre-tax earnings on external work 0 0 0 0 0 Marketing costs 0 0 0 0 0 Taxes on external work 0 0 0 0 0 External work cash flow $0 $0 $0 $0 $0 Net cash flow $0 $0 $0 $0 $0 $0 Project cost of capital: Internal cash flows 0.0% External cash flows 0.0% NPV (internal flows only) $0 NPV (all cash flows) $0 Internal rate of return #NUM! Modified IRR (MIRR) #NUM! END VX