In this tutorial you will practice constructing a spreadsheet-based break-even analysis.

This exercise requires that you calculate the profitability of implementing a new information system, specifically an electronic medical records system installed in a group of physician's offices.Use Microsoft Excel and the following facts to conduct a break-even analysis to determine system costs, system benefits, net annual benefits, break even totals, net present value, and internal rate of return for the system. The following links explain NPV, http://www.investopedia.com/terms/n/npv.asp, and IRR, http://www.investopedia.com/terms/i/irr.asp.

Costs

The physician's group consists of 10 offices and the plan is to install the system in 2 offices in 2018, 3 offices in 2019, and the remaining offices in 2020.

There is a one-time installation cost of $95,000 per office in the year of installation.

The annual operating costs will be $22,000 for each office with a system installed.

Benefits

Each office has 2 physicians

The system will improve efficiencies in assessing and treating patients so that each physician will be able to see one more patient each day.

Each additional patient will increase revenue by an average of $136.

Each physician sees patients 220 days per year.

Calculations

The break-even analysis will cover a 4 year period from 2018-2021. For each year:

Calculate the system costs and benefits.

Calculate the net benefits of the system and the break-even totals for the system.Break even totals are simply the accumulated net benefits.The project breaks even when the accumulated net benefits value is positive.

For the total time period:

Calculate the net present value (NPV) of the investment using a rate of 10%.

Calculate the internal rate of return (IRR).

Answer the four questions at the bottom of the spreadsheet.

• In what year will the project break even?
• Based on the target rate of return of 10%, should the physician's group go forward with this project?
• In what year will the project break even if the profit increase per additional patient improves to $178?
• What is the NPV if the annual operating costs are increased by $500?

Deliverables

Use the provided spreadsheet template to construct and compute the specified calculations.

Use formulas in cells for the calculations. Do not just use a calculator and enter output values.

Use a list of variables that can be changed to see their effects on the calculated totals.This will require the use of absolute cell referencing.

Use conditional formatting for the net annual benefits, break-even calculations, and NPV so that positive numbers are highlighted in green and negative numbers are highlighted in red.