show all excel formulas/ work answering the following:

Assignment Instructions:

1.Read the case background

2.Take data from the last page of this document "What is this projects NPV?" and enter it into an Excel spreadsheet

3.Copy the data into multiple tabs in Excel making one tab for each of the analyses that you are being asked to complete (e.g. NPV, IRR, etc)

4.Create a Summary Tab that shows the results for all your separate analyses, so that it is easy to compare and contrast their respective values in one location. Please use the summary page to also answer the questions on pg #10 at the bottom

5.For each analysis, on the separate tab, use the Good Decision Criteria on pg 3 of this document, to comment whether you believe sufficient information is available in the document for you to know if these criteria have been addressed sufficiently.

" Good Decision Criteria:

All cash flows considered?

- cannibalization, terminal value

TVM considered?

- tied to margin, alternative investment opportunities

Risk-adjusted?

- execution risk; competitor strategies; liability, environmental, community impacts

Ability to rank projects?

- management buy-in

Indicates added value to the firm?

- agreed upon value metrics "

6.If one or another of the tools is not appropriate to use in this case, please explain your reasoning why you think it is not and include that on the summary page

XYZ Hospital @ 850 Boylston MRI NPV Calculation and Market Analysis

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

ats NIK 8 ES 20 Minute Drive Time from 850 Boylston 0 Wincy . 1D D-mile Radius around 850 Boylston in Pond Seb See Pines . 850 Boylston St Lincoln 3 Resep Arlington Usper Wystic Lake Malden 60 Reservoir Lower Mystic Make Medios Arlington Men 16 38 TODO Concord Bordwes Revere brid Reservo Boston A Everett Belmon ti Somerville ste 5.286 2A Chek 146 IA Waltham Belmont Fresh Polia bar V 28 09 Cambridge 20 Waterowe nd 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 population. 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 boga Internati Airpe Weston is was 20 ston . Mass . Storyou West Newton 90 Charley P Newton 30 Seson Broopane Chestnut Hill Newton Highlands Hammond fond | Jamaica 16 Pond 9 850 Boylston (20 minute Drive Time) Boscon Boston Glen Rd Bascota hitude cle Washington udley St haston . 0 con 51 tick 29 Pond Rd Corey Se Layang $ 203 Union 135 Needham Quicy B . South St MASSACHUSETTS Dedham Nepos Milton Milton Center Newport Ab Aast Quincy Bover st 2004 2009 75,408 101,085 4,000 23 16 12 20.8 25.3 27.8 (4.5) ) 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) 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 Bruh Nopad 100 Gays Canta Ayo FS Braintree Westwood Chicken Hatford 28 West Union S 37 Pond St 3 1A NOM Memorial Airport Pond Grove & Norwood st24 Meedfield Da 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 Volume Net Revenue Per Case Total Cost Per Case Net Revenue Total Cost Net Margin 0 $1,348 $577 SO SO SO Year 1 2,481 $1,389 $605 $3,445,605 $1,502,031 $1,943,574 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 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? ats NIK 8 ES 20 Minute Drive Time from 850 Boylston 0 Wincy . 1D D-mile Radius around 850 Boylston in Pond Seb See Pines . 850 Boylston St Lincoln 3 Resep Arlington Usper Wystic Lake Malden 60 Reservoir Lower Mystic Make Medios Arlington Men 16 38 TODO Concord Bordwes Revere brid Reservo Boston A Everett Belmon ti Somerville ste 5.286 2A Chek 146 IA Waltham Belmont Fresh Polia bar V 28 09 Cambridge 20 Waterowe nd 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 population. 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 boga Internati Airpe Weston is was 20 ston . Mass . Storyou West Newton 90 Charley P Newton 30 Seson Broopane Chestnut Hill Newton Highlands Hammond fond | Jamaica 16 Pond 9 850 Boylston (20 minute Drive Time) Boscon Boston Glen Rd Bascota hitude cle Washington udley St haston . 0 con 51 tick 29 Pond Rd Corey Se Layang $ 203 Union 135 Needham Quicy B . South St MASSACHUSETTS Dedham Nepos Milton Milton Center Newport Ab Aast Quincy Bover st 2004 2009 75,408 101,085 4,000 23 16 12 20.8 25.3 27.8 (4.5) ) 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) 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 Bruh Nopad 100 Gays Canta Ayo FS Braintree Westwood Chicken Hatford 28 West Union S 37 Pond St 3 1A NOM Memorial Airport Pond Grove & Norwood st24 Meedfield Da 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 Volume Net Revenue Per Case Total Cost Per Case Net Revenue Total Cost Net Margin 0 $1,348 $577 SO SO SO Year 1 2,481 $1,389 $605 $3,445,605 $1,502,031 $1,943,574 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 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