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.
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.
Purpose of this Exercise
This activity allows you to demonstrate your understanding of the components of a technology solution and demonstrate your grasp of the concepts covered in the IFSM curriculum (hardware, software, data, networks, etc.). This assignment specifically addresses the following course outcomes to enable you to:
- Evaluate, select, and apply appropriate analytical and measurement methods/tools and system development life cycle (SDLC) - methodologies to meet organizational needs.
- Research, assess, recommend/select, and implement information technology that aligns with business needs and meets business objectives.
- Effectively communicate with stakeholders orally, visually, and in writing to determine stakeholders' business requirements, explain how their requirements will be met, and provide ongoing audience-appropriate information.
- Responsibly protect organizations' critical information and assets by integrating cybersecurity best practices and risk management throughout global enterprises.
It also addresses the business strategy, project management, risk management, SDLC, and the enterprise themes.
Assignment
Using the Case Studyand the technology solution (system) identified by your group in Section III of your business case, you should independently develop the detailed technical description of the system your group is proposing and supporting with its business case. You are to describe the technical components of your proposed solution, provide a graphical representation of your solution and a spreadsheet of costs. The three parts of this assignment are more fully described below.
1. Begin your paper with a brief (1 paragraph) description of the business need identified by your Group in Section II of the Business Case. Then, describe the overall functionality of the system your group selected and included in Section III. Describe each of the components of your proposed solution, to include:
- Resources required:
o hardware
o software
o network/communications
o explain how these work together to provide the solution
- How the system will work:
o people - the people who will use the system and how they will use it
o data - A discussion of the data needed for the system, where it will come from and how will it get into the system, how it will be used, and how it will, at a high level, flow through the system
o input - when the system is operational, what data will be input to be processed and/or stored?
o processing - what processing will be done by the system; what will the system do with the data to create the output?
o output - what output will the system produce?
o storage - where will the data be stored?
o security - what security devices, software, or policies will be needed?
Your paper should explain how these components work together to support the business need identified and how the solution as a whole is appropriate to the business described in the Case Study. The who, what, when, where and how of the system should all be covered, as you envision it being implemented and used.From your description, the reader should be able to envision howthe IT solution will be setup and used by the organization in the Case Study. While external resources are not required, you must correctly cite and reference any resources you use in APA format.
Attachment:- ROI Calculator.xls