Cash flow modelling

Using the information below, put together a financial analysis in Excel spreadsheet.

• The purchase price for the colonoscopy simulator is $4,000
• The revenue from each colonoscopy, on average, is $450.
• Each colonoscopy requires $200 worth of supplies.
• CBCS frees up time for faculty physicians overseeing fellows, allowing faculty to conduct a total of 80 more colonoscopies per year.
• Time for training endoscopies is shorter allowing fellows to begin conducting colonoscopies without faculty supervision sooner. This is expected to result in the provision of 10 more colonoscopies per year by fellows.
• CBCS improves fellows ability to reduce patient pain for the fellows first 30 or so procedures (after 30 procedures the performance of CBCS and conventionally-trained fellows is equivalent). As a result

• Patient experience improves as a result of reductions in pain during the procedure. Finance estimates these improvements will result in 10 additional procedures per year as patients choose your health system
• Economists studying patient experience have valued a low-pain colonoscopy as worth $500 more to the average patient, although current reimbursement does not reflect this additional value

• 2% of colonoscopies will identify a polyp that will have to be surgically removed. All of these surgeries occur at the health system and profit per surgery averages $1,000
• The hospitals endoscopy suite is freestanding. Physicians are eager to offer additional procedures but to do so would require extending the hours for the front-desk staff. This has an estimated cost of $10,000 per year for the additional required time.
• Annual rent on the current endoscopy suite is $300,000.

Using the above information, please answer the following questions

1. Based on the above assumptions, what is the financial value proposition CBCS offers? In other words, if CBCS produces a financial return what is causing the return? This is a conceptual question. You dont need to do any calculation at this point.
2. Create a model in Excel that quantifies the financial return on CBCS. Create your projections for 5 years. Use the modelling practices discussed in the lecture.
3. Using an 8% discount rate, calculate the NPV of the CBCS project?
4. Using an 8% discount rate, calculate the IRR of the CBCS project?
5. Calculate the payback period of the CBCS project?
6. Given your answers to #3-5 above, would you say that this is a financially-beneficial project?