Technology Solution Assignment

ROI_CalculatorandCostSpreadsheet.xls

Home >Information Systems homework help >Technology Solution Assignment

Instructions

	ROI Calculator - for IT system projects
	2015 Version (v1.04) SCROLL TO THE TOP to see all entries
	Published by Axia Consulting Ltd, 17 New Road Avenue, Chatham, Kent ME4 6BA, United Kingdom.
	Web: www.axia-consulting.co.uk Email: [email protected]
	Axia Consulting Ltd gives no warranty (either expressed or implied) in relation to the quality, accuracy, performance and fitness for purpose of this 'ROI Calculator'. Axia Consulting Ltd will not be liable for any loss or damage (whether directly or indirectly suffered), or any consequential loss arising from the use of this 'ROI Calculator'.
	Copyright © 2015 Axia Consulting Ltd. All rights reserved.
	This ‘ROI Calculator’ is free for personal use and to help you with projects internally within your organisation. However, you are not permitted to display this ‘ROI Calculator’ on the Internet, whether in part or whole, whether modified or not, nor to advertise, resell or obtain a commercial benefit from it.
	ROI Calculator objective: to quickly calculate ROI (return on investment) and payback year for IT system projects
	INSTRUCTIONS are below (click the tab at the bottom of the screen to access the ROI Calculator Example)
	Note: The ROI Calculator includes some fictitious data in the ROI Calculator Example, for illustration purposes. On the Costs and Services and ROI Calculations tabs you will enter data about your specific project. The notation $'000 means that the data are shown in thousands of dollars.
	All calculated figures are in coloured in orange, with totals in blue, to assist in easy recognition. The worksheet uses simple Excel calculations which should not be altered.
	Graphs show analyses of project cash flows, income / cost savings and implementation costs. The graphs in your ROI Calculations will change depending on the headings and data you enter into the spreadsheet.
	Instructions
	1. Review the ROI Calculator Example
	2. Go to the Costs and Sources tab and enter the costs associated with your technology solution.
	3. Go to the ROI Calculations tab and enter the categories of savings and amount to be saved each year - for your solution. Edit the entries and delete the rows where excess entries are shown. Use 'insert row' if you need to add rows. This will retain the formulas correctly.
	4. On the ROI Calculations page enter the categories of costs (initial investment costs and ongoing costs) and the amounts for each of the first 5 years of use of your solution using the data you entered on the Costs and Sources tab. Edit the entries and delete the rows where excess entries are shown. Use 'insert row' if you need to add rows. This will retain the formulas correctly.
	5. Review the totals that are calculated by the spreadsheet formulas and make sure they are correct.
	6. Review the two rows in Section 3 below the costs and examine how the cash flow and cumulative cash flow are calculated.
	7. In section 4, enter any assumptions that affected the costs recorded; some examples are shown
	8. Review the 4 charts at the top of the page to see how the categories of savings and costs break out.
	9. The top right chart shows the Return on Investment (ROI) for your project and the Payback Year. These will be included in your written narrative that explains your technology solution.
	10. Your written narrative will also include the total investment (one-time) costs and the annual ongoing cost amounts.
	11. Your written nattative will also discuss the categories and amounts of savings expected from implementing your solution.
	Further information about software selection may be found at:
	http://www.axia-consulting.co.uk/html/resources.html
	Downloaded from:
	http://www.axia-consulting.co.uk/html/roi_calculator.html

http://www.axia-consulting.co.uk/html/resources.html

http://www.axia-consulting.co.uk/html/roi_calculator.html

ROI Calculator Example

	ROI Calculator - for IT system projects
	Project name:
						Results Summary
						Total project cost savings/income			845000
						Total project expenditures			-310000
						Net project savings / income			535000		Net = Savings - Project Expenditure
						ROI (return on investment - after 5 years)			172.6%		ROI = Net Savings/Project Expenditure
						Payback year			Year 3		Payback year = year in which the accumulated savings
											exceeds the project expenditures
	ROI Input Year:	0	1	2	3	4	5	Total
Ref	Description
1	Project cost savings / income
1.1	Improved invoice preparation			$ 20,000	$ 40,000	$ 40,000	$ 40,000	$ 140,000
1.2	Reduced storage costs		$ 40,000	$ 60,000	$ 80,000	$ 80,000	$ 80,000	$ 340,000
1.3	Time saved manually creating basic reports			$ 15,000	$ 30,000	$ 30,000	$ 30,000	$ 105,000
1.4	Existing software annual maintenance		$ 25,000	$ 50,000	$ 50,000	$ 50,000	$ 50,000	$ 225,000
1.5	Reduced consumables			$ 5,000	$ 10,000	$ 10,000	$ 10,000	$ 35,000
	Total cost savings / income	$ - 0	$ 65,000	$ 150,000	$ 210,000	$ 210,000	$ 210,000	$ 845,000
2	Project expenditures
2.1	Selection costs									Shown in year 0; prior to start of project
2.1.1	Software selection staff costs	$ 10,000						$ 10,000
2.1.2	Travel and expenses	$ 2,000						$ 2,000
2.1.3	Selection tools / programs	$ 1,000						$ 1,000
2.1.4	Consultancy costs	$ 7,000						$ 7,000
	Sub total selection costs	$ 20,000	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ 20,000
2.2	Implementation costs
2.2.1	Software		$ 55,000					$ 55,000
2.2.2	Database		$ 5,000					$ 5,000
2.2.3	Additional licences		$ 5,000					$ 5,000
2.2.4	Hardware		$ 70,000					$ 70,000
2.2.5	Network		$ 10,000					$ 10,000
2.2.6	System configuration		$ 15,000					$ 15,000
2.2.7	Other labour costs		$ 20,000					$ 20,000
2.2.8	Training		$ 15,000					$ 15,000
2.2.9	Contingency		$ 20,000					$ 20,000
	Sub total implementation costs	$ - 0	$ 215,000	$ - 0	$ - 0	$ - 0	$ - 0	$ 215,000
2.3	Ongoing costs
2.3.1	Annual maintenance / service charges for software, hardware, database, network (either the total annual cost, or listed by each item of the annual cost)		$ 15,000	$ 15,000	$ 15,000	$ 15,000	$ 15,000	$ 75,000
	Sub total ongoing costs	$ - 0	$ 15,000	$ 15,000	$ 15,000	$ 15,000	$ 15,000	$ 75,000
	Total expenditure	$ 20,000	$ 230,000	$ 15,000	$ 15,000	$ 15,000	$ 15,000	$ 310,000
3	Cash Flow
	Cash flow (Savings - Expenditure)	$ (20,000)	$ (165,000)	$ 135,000	$ 195,000	$ 195,000	$ 195,000	$ 535,000
	Cumulative cash flow (previous yr cumulative	$ (20,000)	$ (185,000)	$ (50,000)	$ 145,000	$ 340,000	$ 535,000
	cash flow + current year cash flow)
4	Assumptions
	list any assumptions here - examples might be:
	number of licenses needed
	growth in number of users
	growth in amount of data/transactions

ROI Calculator Example

Cash flow (Savings - Expenditure)

Cumulative cash flow (previous yr cumulative

Years

$/£'000

Project cash flows

Costs and Sources

Project cost savings / income

Analysis of project cost savings / income

ROI Calculations

Analysis of project implementation costs

INSTRUCTIONS: List each item separately and use Excel functions to calculate total item costs, subtotals, and total expenditure. Enter costs in appropriate columns and show the source (URL) of the cost of each item. Edit the items in Column B to match your solution, inserting rows as needed.
Implementation costs		Unit Cost	Number of Units	Total Item Cost	Source of cost (website, etc)
Software	Software
	Database
	Additional licences
	Other software costs
Hardware	(list each hardware component)
Network	(list each network component and connectivity/communication costs)
Labor/Service Costs	System configuration
	Other labour costs (specify)
	Training
Contingency	Contingency
	Sub total implementation costs
Ongoing costs				Annual Cost
	(List Annual maintenance / service charges for software, hardware, database, communications/connectivity)
	Sub total ongoing costs				Total annual cost
	Total expenditure

	ROI Calculator - for IT system projects
	Project name:
	NOTE: Everything looks messed up, but will look correct after you enter your data
						Results Summary
						Total project cost savings/income $'000			0
						Total project expenditures $'000			0
						Net project savings / income $'000			0		Net = Savings - Project Expenditure
						ROI (return on investment - after 5 years)			0.0%		ROI = Net Savings/Project Expenditure
						Payback year			Year 1		Payback year = year in which the accumulated savings
											exceeds the project expenditures
	ROI Workings Year:	0	1	2	3	4	5	Total
Ref	Description
1	Project cost savings / income									Edit the headings for YOUR project.
										Delete rows that remain and do not apply to your project
1.1	Improved invoice preparation							$ - 0		Enter the amounts you estimate will be saved each year
1.2	Reduced storage costs							$ - 0		Below, enter the costs from your Costs and Sources tab.
1.3	Time saved manually creating basic reports							$ - 0
1.4	Existing software annual maintenance							$ - 0
1.5	Reduced consumables							$ - 0
								$ - 0
	Total cost savings / income	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0
2	Project expenditures
2.1	Selection costs									Shown in year 0; prior to start of project
2.1.1	Software selection staff costs							$ - 0
2.1.2	Travel and expenses							$ - 0
2.1.3	Selection tools / programs							$ - 0
2.1.4	Consultancy costs							$ - 0
	Sub total selection costs	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0
2.2	Implementation costs									Shown only in year 0 or year 1
2.2.1	Software							$ - 0
2.2.2	Database							$ - 0
2.2.3	Additional licences							$ - 0
2.2.4	Hardware							$ - 0
2.2.5	Network							$ - 0
2.2.6	System configuration							$ - 0
2.2.7	Other labour costs							$ - 0
2.2.8	Training							$ - 0
2.2.9	Contingency							$ - 0
	Sub total implementation costs	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0
2.3	Ongoing costs									Shown beginning in year 1
2.3.1	Annual maintenance / service charges for software, hardware, database, network (either the total annual cost, or listed by each item of the annual cost)							$ - 0
	Sub total ongoing costs	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0
	Total expenditure	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0
	Cash flow (Savings - Expenditure)	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0
	Cumulative cash flow (previous yr cumulative	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0
	cash flow + current year cash flow)