Solve the following Using Excel & demonstrate all excel formulas used

Volume Methodology

• There are currently 6 Orthopedic and Sports Medicine physicians using an extremities MRI at 850. When these 6 physicians are in need of a full-body MRI they refer to the main campus, however, they are finding that patients are less likely to go downtown, and leakage is occurring.
• It is estimated that these 6 physicians order 2 MRIs per day, at 5 days a week for 48 weeks, creating a demand of approximately 2,880 MRIs per year.
• In addition to the Orthopedic and Sport Medicine practices, also located at 850 are the Womens Health physicians. These physicians are increasingly ordering more MRI for breast imaging in place of the plain film. And this would add approximately 430 additional cases.
• Grand total estimated volume for the MRI at 850 Boylston would be 3,308 at capacity.

MRI NPV Calculation & Assumptions

• 9% Discount Rate
• Total Capital Request: $4.325M
• $1.825M: Construction
• $2.50M: 1.5 TESLA MRI
• Grew total cost per case by 5%
• Grew revenue per case by 3%
• Held volume steady at 3,308 per year
• Cost and Revenue per case based on MRI Department FY05 Actuals Source TSI

acles Kiver 8 20 Minute Drive Time from 850 Boylston Winchester 10 D-mile Radius around 850 Boylston PR Kond) Sale 3/ Resen Salen Pines 850 Boylston St Arlington Reservoir 225 Upper Mystic Lake Lower Mystic Lake Malden 60 and Ave O 2 Lincoln Medfos Concorda Trapeto Broadway Arlington OR Revere bridge Reservou aly wise MEN Ver Everett Belmont 16 117 Somervillister Chels 2A Waltham Belmonts Fresh Pona 1 1A O W 99 Cambridge 93 20 Watertown nd toga Internati Sg2 projects a 34% growth in use of MRI in the next 5 years. In 2009, with no in-migration, we can expect to have an undersupply of 4.5 MRIs in the defined 20-minute market. If we account for a 10% in-migration, approximately 7 MRIs would be needed to meet the needs of the opulation. The analysis does not account for possible new MRI installations within the market in the next 5 years. There are currently 16 MRI machines within a 5 mile radius of 850 Boylston Weston 20 south S4 Chario A B ston Storrow or Neti.ays st West Newton Chandley pod Newton 30 Beacon Chestnut Hill o Newton Highlands Hammond Pond Jamaica Pond O Brooine eacon Mass Boston Glen Rd Beacon sy hitud e Clit for 16 Washin on Oudley S Haslon Redon 850 Boylston (20 minute Drive Time) 9 con St tick 28 Corey St Pond Rd Central Ave Lagrange 203 Union 135 Needham Quincy B $ South St Holst 2009 101,085 Nepos Milton de Newport Ave MASSACHUSETLIS 09 Dedham Milton Center 3 st Quincy 2004 75,408 4,000 23 16 12 20.8 st Bover Brush Head Estimated Annual MRI Cases Estimated Annual Case Capacity per MRI Total MRIs in Market Total MRIs in Market within 5-mile radius Total MRIs in Market within 5-mile radius not owned by PHS Total MRIs in Market (adjusted for Outpatient Activity) (1) MRIs Required in 2009 for Outpatient Care MRIs Required in 2009 @ 10% In-migration for Outpatient Care Under/ Oversupply in Market for Outpatient Care Under/ Oversupply in Market @ 10% In-migration for Outpatient Care Waipales Canton Aye 109 Rd Centro St. Gay S8 Westwood East chickatawid Braintree Slade St Hartford West St 28 25.3 27.8 (4.5) Union S 37 3 Univers Pond St Noma Memorial Ford St V Grove S (70) Norwood + Airport 138 24 Medfield W SE Oak o 1 Hospital-based MRIs are used by outpatients 80% of the time. To capture only the outpatient component, the total number of hospital-based MRIs was multiplied by 0.8. What is this project's NPV? Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 0 Volume Net Revenue Per Case Total Cost Per Case Net Revenue Total Cost Net Margin $1,348 $577 $0 $0 $0 2,481 $1,389 $605 $3,445,605 $1,502,031 $1,943,574 3,308 3,308 3,308 3,308 3,308 3,308 3,308 3,308 $1,430 $1,473 $1,518 $1,563 $1,610 $1,658 $1,708 $1,759 $636 $667 $701 $736 $773 $811 $852 $894 $4,731,964 $4,873,923 $5,020,141 $5,170,745 $5,325,868 $5,485,644 $5,650,213 $5,819,719 $2,102,844 $2,207,986 $2,318,385 $2,434,305 $2,556,020 $2,683,821 $2,818,012 $2,958,912 $2,629,121 $2,665,937 $2,701,756 $2,736,441 $2,769,848 $2,801,823 $2,832,201 $2,860,807 Capital Cost $4,325,000 Calculate (note please round calculations to nearest 1,000): NPV IRR Payback AAR (assume project cost fully depreciates by end of yr 8; no taxes as it is an NFP) PI Make sure to address these questions: What do you need to know to determine if this is a good project for the hospital or not? How do you address depreciation if you are looking at an analysis? acles Kiver 8 20 Minute Drive Time from 850 Boylston Winchester 10 D-mile Radius around 850 Boylston PR Kond) Sale 3/ Resen Salen Pines 850 Boylston St Arlington Reservoir 225 Upper Mystic Lake Lower Mystic Lake Malden 60 and Ave O 2 Lincoln Medfos Concorda Trapeto Broadway Arlington OR Revere bridge Reservou aly wise MEN Ver Everett Belmont 16 117 Somervillister Chels 2A Waltham Belmonts Fresh Pona 1 1A O W 99 Cambridge 93 20 Watertown nd toga Internati Sg2 projects a 34% growth in use of MRI in the next 5 years. In 2009, with no in-migration, we can expect to have an undersupply of 4.5 MRIs in the defined 20-minute market. If we account for a 10% in-migration, approximately 7 MRIs would be needed to meet the needs of the opulation. The analysis does not account for possible new MRI installations within the market in the next 5 years. There are currently 16 MRI machines within a 5 mile radius of 850 Boylston Weston 20 south S4 Chario A B ston Storrow or Neti.ays st West Newton Chandley pod Newton 30 Beacon Chestnut Hill o Newton Highlands Hammond Pond Jamaica Pond O Brooine eacon Mass Boston Glen Rd Beacon sy hitud e Clit for 16 Washin on Oudley S Haslon Redon 850 Boylston (20 minute Drive Time) 9 con St tick 28 Corey St Pond Rd Central Ave Lagrange 203 Union 135 Needham Quincy B $ South St Holst 2009 101,085 Nepos Milton de Newport Ave MASSACHUSETLIS 09 Dedham Milton Center 3 st Quincy 2004 75,408 4,000 23 16 12 20.8 st Bover Brush Head Estimated Annual MRI Cases Estimated Annual Case Capacity per MRI Total MRIs in Market Total MRIs in Market within 5-mile radius Total MRIs in Market within 5-mile radius not owned by PHS Total MRIs in Market (adjusted for Outpatient Activity) (1) MRIs Required in 2009 for Outpatient Care MRIs Required in 2009 @ 10% In-migration for Outpatient Care Under/ Oversupply in Market for Outpatient Care Under/ Oversupply in Market @ 10% In-migration for Outpatient Care Waipales Canton Aye 109 Rd Centro St. Gay S8 Westwood East chickatawid Braintree Slade St Hartford West St 28 25.3 27.8 (4.5) Union S 37 3 Univers Pond St Noma Memorial Ford St V Grove S (70) Norwood + Airport 138 24 Medfield W SE Oak o 1 Hospital-based MRIs are used by outpatients 80% of the time. To capture only the outpatient component, the total number of hospital-based MRIs was multiplied by 0.8. What is this project's NPV? Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 0 Volume Net Revenue Per Case Total Cost Per Case Net Revenue Total Cost Net Margin $1,348 $577 $0 $0 $0 2,481 $1,389 $605 $3,445,605 $1,502,031 $1,943,574 3,308 3,308 3,308 3,308 3,308 3,308 3,308 3,308 $1,430 $1,473 $1,518 $1,563 $1,610 $1,658 $1,708 $1,759 $636 $667 $701 $736 $773 $811 $852 $894 $4,731,964 $4,873,923 $5,020,141 $5,170,745 $5,325,868 $5,485,644 $5,650,213 $5,819,719 $2,102,844 $2,207,986 $2,318,385 $2,434,305 $2,556,020 $2,683,821 $2,818,012 $2,958,912 $2,629,121 $2,665,937 $2,701,756 $2,736,441 $2,769,848 $2,801,823 $2,832,201 $2,860,807 Capital Cost $4,325,000 Calculate (note please round calculations to nearest 1,000): NPV IRR Payback AAR (assume project cost fully depreciates by end of yr 8; no taxes as it is an NFP) PI Make sure to address these questions: What do you need to know to determine if this is a good project for the hospital or not? How do you address depreciation if you are looking at an analysis