Hi!

I need help filling out the excel questions and spreadsheet for the Dallas Health Network. i have attached the reading material for the excel spreadsheet.

Case Study: Dallas Health Network - ABC Analysis Excel Calculations: The following tab has excel calculations for ABC costing. The spreadsheet have entered the correct inputs. However, please ensure you understand the calculations befo credit, your answers will require explanations that show you understand the calculations. Questions on the case are listed below. Please answer each question fully, showing your excel explanations for each numerical answer as well. You may submit assignment on a Word docum 1. Estimate the base case cost of Alternatives 1 and 2 regarding the provision of ultrasound services. 2. Which alternative has the lower total cost? 3. Redo the analysis assuming that per unit supplies cost; billing and collection cost; general administration cost; and transportation, setup, and breakdown cost are higher than the base case values by 10 percent. Redo the analysis again assuming these costs are 20 percent higher than the base case values. 4. Return to the base case. What value for transportation and set-up costs would make the costs of the two alternatives the same? 5. Again use all base case data but assume that a 5 percent discount is available if three machines are purchased. What impact does this have on the decision? What discount amount would make the two alternatives equal in costs? 6. Redo the base case analysis assuming a useful life of 3 years. Now assume a life of 7 years. 7. Do the analyses conducted for Questions 3 through 6 impact your decision as to which alternative has the lower cost? 8. What subjective factors would influence the decision as to which alternative to choose? 9. What is your final decision? sting. The spreadsheet will do the calculations once you nd the calculations before answering questions. To get full d the calculations. ully, showing your excel calculations. Provide written nment on a Word document or an Excel spreadsheet. n of ultrasound on cost; general r than the base e 20 percent ould make the costs able if three t discount amount me a life of 7 years. n as to which e to choose? Calculations INPUT DATA: KEY OUTPUT: Alternatives I and II Cost Detail: Appointment scheduling Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration Per Procedure Operating Cost: Volume 0 0 0 0 0 0 0 0 0 0 Unit minutes minutes minutes minutes procedure minutes minutes procedure procedure procedure Unit Cost $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Alternative II Only Cost Detail: Transportation, set-up, and breakdown 0 minutes Total Lifetime: Alt. I Alt. II $0 $0 Total Annual: #DIV/0! #DIV/0! $0.00 Total Per Procedure: Alt. I Alt. II 0 $0 0.0% $0 $0 $0 $0 0 MODEL-GENERATED DATA: Alternative I Operating Cost per Procedure: Schedule the appointment $0.00 $0.00 Total Costs Including Capital and Maintenance Costs: Alt. I Alt. II Other Data: Expected annual volume Capital cost per machine Capital cost discount Cost of van Annual van maintenance Annual machine maintenance: One unit Three units ($ per unit) Expected machine/van life Alt. I Alt. II $0.00 Page 3 #DIV/0! #DIV/0! Calculations Patient check-in Ultrasound testing: Technician time Physician time Supplies Patient check-out Film processing Film reading Billing and collection General administration 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 $0.00 Alternative II Operating Cost per Procedure: Alternative I cost Transportation, set-up, and breakdown $0.00 0.00 $0.00 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 Alternative I Alternative II $0 0 $0 0 0 0 0 0 $0 $0 Annual costs #DIV/0! #DIV/0! Per procedure costs #DIV/0! #DIV/0! Page 4 Calculations Page 5