Description

INSTRUCTIONS FOR USING THE ROI CALCULATOR SPREADSHEET

The ROI Calculator spreadsheet is to be included with the individual technology solution description and with the group assignment for Section VIII of the business case.  This allows each member of the team to specify costs and savings and be able to see how the Return on Investment (ROI) and payback period are calculated, and for the information to be presented in both documents.  The individual assignment is due prior to Section VIII.  The team members will share their technology solutions and spreadsheets with each other and determine which ones to use (with possible modifications) for the business case.

HOW THE CALCULATOR WORKS

First, it would be helpful to understand how the ROI Calculator works.  Open the ROI Calculator spreadsheet and click on the "Instructions" tab.  Read the instructions and then click on the "ROI Calculator Example" tab.  At the top you will see 4 charts; scroll down to the data and come back to the charts later.  In the data area, you will note that savings and costs are identified for the first 5 years of the project, and you will see the following:

1.      Project Cost Savings/Income.  These are areas where savings are expected to be achieved, and the amounts that are projected to be saved over each of the first 5 years.  Total yearly savings are shown in blue.

2.     Project Expenditures.

2.1.   Selection Costs.  Note that these are shown in year 0, as they occur prior to starting the project.  The subtotals by item and by year are in orange.

2.2.   Implementation Costs.  These are one-time costs, but may occur in more than one year.  For example, training may be done at the beginning and then again the next year.  (It may even need to be done at some minimal level every year after the initial training; if that is the case the recurring training costs go below.)  Hardware and software may be purchased incrementally, depending on the implementation schedule.  It is possible that all one-time costs for a small project will occur in year 1.  Part of the implementation cost is an amount that should be set aside for contingencies.  It may only be needed in year 1, and the amount depends on the project.  The subtotals by item and by year are in orange.

2.3.   Ongoing Costs.  These are costs that will recur year after year.  They may not be the same every year.  For example if a business is growing in employees, additional software licenses may be needed in year 3 for those employees.  However, for purposes of these assignments, the same annual ongoing costs will be used for each year, including year 1.  The subtotals by item and by year are in orange.

Total Project Expenditures are shown in blue.

3.     The next two lines on this tab of the spreadsheet show the calculated Cash Flow and the calculated Cumulative Cash.

3.1.   Cash Flow.  Cash Flow by year is automatically calculated by the spreadsheet.  Cash Flow is calculated by subtracting the expenditures for the year from the savings for that year.  For example, in Year 1 (not year 0), the savings are projected to be $65,000 and the total expenditures are $230,000, with a net negative cash flow of $-165,000.

3.2.   Cumulative Cash Flow. Cumulative Cash Flow by year is automatically calculated by the spreadsheet.  It is calculated by taking the previous year's cash flow amount and adding the current year's cash flow amount.  The cumulative cash flow for year 1 takes the $-20,000 cash flow from year 0 ($-20,000) and adds the cash flow from year 1 ($-165,000) for a total negative cumulative cash flow for year 1 of $-185,000. Note that in year 2, the Cash Flow is positive (savings exceed costs for that year) and in year 3 the cash flow is very positive and the cumulative cash flow is now positive.  Take a quick look at the chart at the top right side of the sheet and you will see that the "Payback Period" is Year 3 – the year that the accumulated savings exceed the accumulated expenses. 

4.     Section 4 provides a place to record any assumptions that have an impact on the cost data.  Examples might be assumptions about how the number of users or transactions might grow over time, how the business might change over the 5 year period, which users would be given access to the system (affecting the number of licenses, etc.).

5.     Then, scroll to the top of the page on the "ROI Calculations" tab, and you will see 4 charts. 

5.1.   The lower two charts show the categories of costs and the expenditures in pie charts; this shows at a glance where the largest savings and the highest costs are. 

5.2.   The chat in the top left shows the costs and savings by year. Notice what happens in year 3 – the cumulative cash flow (in purple) is above zero.

5.3.   The chart in the upper right is the most important one.  It shows the calculated Return on Investment (ROI) for the project.  The ROI is calculated by computing the Net Savings (Total Savings over the 5 year period minus the costs incurred during the 5 year period) and dividing the Net Savings by the Total Expenditure.  Then the payback period is calculated to determine in which year the accumulated savings exceed the project costs.