Case Study 10 Instructions Denver Health Network: ABC Analysis This case study is relatively simple and has a good template, so data entry is minimal. The point of the case is to look at several scenarios to determine which of two alternatives is the lowest cost alternative, and, especially, which alternative would be your choice (all things considered). This will give you practice in sensitivity/scenario analysis related to costs, and introduces you to the concept of ABC analysis. Note this is NOT exactly the same as Cleverley?s specific formulation of ABC; however, while the language may be different, the concepts used in this case are the same. REMEMBER (AS ALWAYS): Replace ONLY the RED numbers in the input section. Fiddle with the other numbers and funny things may happen. I will not be held responsible for exploding spreadsheets? The questions you are to answer: 1. Estimate the base (initial) cost of each alternative. Here, you just need to put in the appropriate RED input from the information in the case study. The answers will be spit out. Which alternative has the lower total cost? Why? 2. Redo the base analysis, this time assuming that per unit supplies cost, billing and collection costs, general administration costs, and transportation and setup costs are higher than the base case values by 10 percent (?10% Increase?). Redo the base analysis again, this time assuming these costs are 20% higher than the base case values (20% Increase). Please do this by creating two new worksheets, as you have done before. Given the results represented by these two worksheets, which is the lower cost alternative? Do you notice anything about the increasing costs of the alternatives that you find interesting or noteworthy? 3. Return to the base case (see 1. above), and assume a 5% discount if all three machines are purchased (i.e., change only ?capital cost discount?). Label this new worksheet, ?5% discount?. What impact does the discount have on your decision? 4. Return to the base case yet again (see 1. above), this time changing only useful life (?Expected machine/van life?) to 3 years (?3 Years? Life?). Now assume a life of 7 years (?7 Years? Life?). Here, we are doing a scenario/sensitivity analysis based on changing capital costs per procedure, not operating costs. What happens to the cost of each alternative in the short run? In the long run? Which alternative would you pick, given the results of the two scenarios? 5. What are some practical operational considerations in this case? You are encouraged to speculate about the characteristics of a mobile vs. fixed ultrasound service as each may affect equipment life and the ability to deliver service, meet demand, and so on. 6. Given the costs and benefits of each alternative, what would be your final recommendation? Under what conditions, if any, could you be persuaded to change your mind?

CASE10 INPUT DATA: KEY OUTPUT: Alternatives I and II Cost Detail: Per Procedure Operating Cost: Volume 3 5 Appointment scheduling Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration 45 1.5 1 5 10 1 1 1 Unit minutes minutes minutes minutes procedure minutes minutes procedure procedure procedure Unit Cost $0.20 $0.20 $0.40 $3.00 $9.00 $0.20 $0.40 $40.00 $6.80 $1.25 Alternative II Only Cost Detail: Transportation and set-up Alt. I Alt. II Total Costs Including Capital and Maintenance Costs: Total Lifetime: Alt. I $1,341,300 Alt. II $1,255,800 Total Annual: Alt. I Alt. II 6 minutes $86.15 $92.15 $268,260 $251,160 $1.00 Total Per Procedure: Other Data: Alt. I Alt. II Expected annual volume 2,400 Capital cost per machine $100,000 Capital cost discount 0.0% Cost of van $40,000 Annual van maintenance $1,000 Annual machine maintenance: One unit $1,000 Three units ($ per unit) $500 Expected machine/van life 5 MODEL-GENERATED DATA: Alternative I Operating Cost per Procedure: Schedule the appointment Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration $0.60 1.00 18.00 4.50 9.00 1.00 4.00 40.00 6.80 1.25 $86.15 Alternative II Operating Cost per Procedure: Alternative I cost Travel and set-up costs $86.15 6.00 $92.15 Total, Annual, and Per Procedure Costs Including Capital and Maintenance Costs: Cost of van Van maintenance cost Cost of machines Machine maint. cost Operating costs Total costs Annual costs Per procedure costs Alternative I Alternative II $40,000 5,000 $300,000 100,000 7,500 5,000 1,033,800 1,105,800 $1,341,300 $1,255,800 $268,260 $251,160 $111.78 $104.65 Page 1 $111.78 $104.65 10% Increase CASE 10 9/17/09 Student Version Copyright 2010 by FACHE DENVER HEALTH NETWORK ABC Analysis This case illustrates the use of activity based costing (ABC) to estimate the costs associated with two alternative approaches to providing ultrasound services at clinic locations. The worksheet uses both operating and capital (equipment) cost input data to estimate both operating and total costs for the two alternatives. 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: Alternatives I and II Cost Detail: Per Procedure Operating Cost: Volume 3 5 Appointment scheduling Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration 45 1.5 1 5 10 1 1 1 Unit minutes minutes minutes minutes procedure minutes minutes procedure procedure procedure Unit Cost $0.20 $0.20 $0.40 $3.00 $9.90 $0.20 $0.40 $40.00 $7.48 $1.38 Alternative II Only Cost Detail: Transportation and set-up Alt. I Alt. II Total Costs Including Capital and Maintenance Costs: Total Lifetime: Alt. I $1,361,760 Alt. II $1,283,460 Total Annual: Alt. I Alt. II 6 minutes $87.86 $94.46 $272,352 $256,692 $1.10 Total Per Procedure: Other Data: Alt. I Alt. II Expected annual volume 2,400 Capital cost per machine $100,000 Capital cost discount 0.0% Cost of van $40,000 Annual van maintenance $1,000 Annual machine maintenance: One unit $1,000 Three units ($ per unit) $500 Expected machine/van life 5 $113.48 $106.96 MODEL-GENERATED DATA: Alternative I Operating Cost per Procedure: Schedule the appointment Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration $0.60 1.00 18.00 4.50 9.90 1.00 4.00 40.00 7.48 1.38 $87.86 Alternative II Operating Cost per Procedure: Alternative I cost Travel and set-up costs $87.86 6.60 $94.46 Total, Annual, and Per Procedure Costs Including Capital and Maintenance Costs: Cost of van Van maintenance cost Cost of machines Machine maint. cost Operating costs Total costs Annual costs Per procedure costs Alternative I Alternative II $40,000 5,000 $300,000 100,000 7,500 5,000 1,054,260 1,133,460 $1,361,760 $1,283,460 $272,352 $256,692 $113.48 $106.96 END Page 2 20% Increase INPUT DATA: KEY OUTPUT: Alternatives I and II Cost Detail: Per Procedure Operating Cost: Volume 3 5 Appointment scheduling Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration 45 1.5 1 5 10 1 1 1 Unit minutes minutes minutes minutes procedure minutes minutes procedure procedure procedure Unit Cost $0.20 $0.20 $0.40 $3.00 $10.80 $0.20 $0.40 $40.00 $8.16 $1.50 Alternative II Only Cost Detail: Transportation and set-up Alt. I Alt. II Total Costs Including Capital and Maintenance Costs: Total Lifetime: Alt. I $1,382,220 Alt. II $1,311,120 Total Annual: Alt. I Alt. II 6 minutes $89.56 $96.76 $276,444 $262,224 $1.20 Total Per Procedure: Other Data: Alt. I Alt. II Expected annual volume 2,400 Capital cost per machine $100,000 Capital cost discount 0.0% Cost of van $40,000 Annual van maintenance $1,000 Annual machine maintenance: One unit $1,000 Three units ($ per unit) $500 Expected machine/van life 5 MODEL-GENERATED DATA: Alternative I Operating Cost per Procedure: Schedule the appointment Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration $0.60 1.00 18.00 4.50 10.80 1.00 4.00 40.00 8.16 1.50 $89.56 Alternative II Operating Cost per Procedure: Alternative I cost Travel and set-up costs $89.56 7.20 $96.76 Total, Annual, and Per Procedure Costs Including Capital and Maintenance Costs: Cost of van Van maintenance cost Cost of machines Machine maint. cost Operating costs Total costs Annual costs Per procedure costs Alternative I Alternative II $40,000 5,000 $300,000 100,000 7,500 5,000 1,074,720 1,161,120 $1,382,220 $1,311,120 $276,444 $262,224 $115.19 $109.26 Page 3 $115.19 $109.26 5% Discount INPUT DATA: KEY OUTPUT: Alternatives I and II Cost Detail: Per Procedure Operating Cost: Volume 3 5 Appointment scheduling Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration 45 1.5 1 5 10 1 1 1 Unit minutes minutes minutes minutes procedure minutes minutes procedure procedure procedure Unit Cost $0.20 $0.20 $0.40 $3.00 $9.00 $0.20 $0.40 $40.00 $6.80 $1.25 Alternative II Only Cost Detail: Transportation and set-up Alt. I Alt. II Total Costs Including Capital and Maintenance Costs: Total Lifetime: Alt. I $1,326,300 Alt. II $1,255,800 Total Annual: Alt. I Alt. II 6 minutes $86.15 $92.15 $265,260 $251,160 $1.00 Total Per Procedure: Other Data: Alt. I Alt. II Expected annual volume 2,400 Capital cost per machine $100,000 Capital cost discount 5.0% Cost of van $40,000 Annual van maintenance $1,000 Annual machine maintenance: One unit $1,000 Three units ($ per unit) $500 Expected machine/van life 5 MODEL-GENERATED DATA: Alternative I Operating Cost per Procedure: Schedule the appointment Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration $0.60 1.00 18.00 4.50 9.00 1.00 4.00 40.00 6.80 1.25 $86.15 Alternative II Operating Cost per Procedure: Alternative I cost Travel and set-up costs $86.15 6.00 $92.15 Total, Annual, and Per Procedure Costs Including Capital and Maintenance Costs: Cost of van Van maintenance cost Cost of machines Machine maint. cost Operating costs Total costs Annual costs Per procedure costs Alternative I Alternative II $40,000 5,000 $285,000 100,000 7,500 5,000 1,033,800 1,105,800 $1,326,300 $1,255,800 $265,260 $251,160 $110.53 $104.65 Page 4 $110.53 $104.65 3 Years Life INPUT DATA: KEY OUTPUT: Alternatives I and II Cost Detail: Per Procedure Operating Cost: Volume 3 5 Appointment scheduling Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration 45 1.5 1 5 10 1 1 1 Unit minutes minutes minutes minutes procedure minutes minutes procedure procedure procedure Unit Cost $0.20 $0.20 $0.40 $3.00 $9.00 $0.20 $0.40 $40.00 $6.80 $1.25 Alternative II Only Cost Detail: Transportation and set-up Alt. I Alt. II Total Costs Including Capital and Maintenance Costs: Total Lifetime: Alt. I Alt. II minutes $924,780 $809,480 Total Annual: Alt. I Alt. II 6 $86.15 $92.15 $308,260 $269,827 $1.00 Total Per Procedure: Other Data: Alt. I Alt. II Expected annual volume 2,400 Capital cost per machine $100,000 Capital cost discount 0.0% Cost of van $40,000 Annual van maintenance $1,000 Annual machine maintenance: One unit $1,000 Three units ($ per unit) $500 Expected machine/van life 3 MODEL-GENERATED DATA: Alternative I Operating Cost per Procedure: Schedule the appointment Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration $0.60 1.00 18.00 4.50 9.00 1.00 4.00 40.00 6.80 1.25 $86.15 Alternative II Operating Cost per Procedure: Alternative I cost Travel and set-up costs $86.15 6.00 $92.15 Total, Annual, and Per Procedure Costs Including Capital and Maintenance Costs: Cost of van Van maintenance cost Cost of machines Machine maint. cost Operating costs Alternative I Alternative II $40,000 3,000 $300,000 100,000 4,500 3,000 620,280 663,480 Total costs $924,780 $809,480 Annual costs $308,260 $269,827 $128.44 $112.43 Per procedure costs Page 5 $128.44 $112.43 7 Years Life INPUT DATA: KEY OUTPUT: Alternatives I and II Cost Detail: Per Procedure Operating Cost: Volume 3 5 Appointment scheduling Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration 45 1.5 1 5 10 1 1 1 Unit minutes minutes minutes minutes procedure minutes minutes procedure procedure procedure Unit Cost $0.20 $0.20 $0.40 $3.00 $9.00 $0.20 $0.40 $40.00 $6.80 $1.25 Alternative II Only Cost Detail: Transportation and set-up Alt. I Alt. II Total Costs Including Capital and Maintenance Costs: Total Lifetime: Alt. I $1,757,820 Alt. II $1,702,120 Total Annual: Alt. I Alt. II 6 minutes $86.15 $92.15 $251,117 $243,160 $1.00 Total Per Procedure: Other Data: Alt. I Alt. II Expected annual volume 2,400 Capital cost per machine $100,000 Capital cost discount 0.0% Cost of van $40,000 Annual van maintenance $1,000 Annual machine maintenance: One unit $1,000 Three units ($ per unit) $500 Expected machine/van life 7 MODEL-GENERATED DATA: Alternative I Operating Cost per Procedure: Schedule the appointment Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration $0.60 1.00 18.00 4.50 9.00 1.00 4.00 40.00 6.80 1.25 $86.15 Alternative II Operating Cost per Procedure: Alternative I cost Travel and set-up costs $86.15 6.00 $92.15 Total, Annual, and Per Procedure Costs Including Capital and Maintenance Costs: Cost of van Van maintenance cost Cost of machines Machine maint. cost Operating costs Total costs Annual costs Per procedure costs Alternative I Alternative II $40,000 7,000 $300,000 100,000 10,500 7,000 1,447,320 1,548,120 $1,757,820 $1,702,120 $251,117 $243,160 $104.63 $101.32 Page 6 $104.63 $101.32