cost/benefit analysis

Assignment for Learning Module 5 Cost/Benefit (Financial Investment) Analysis Tool

INTRODUCTION

The purpose of this exercise is to assess your understanding of cost/benefit analysis o proposed or candidate f IT solutions (sometimes called “financial analysis” or “economic analysis” or “investment analysis”). This is an extension of the previous total costs and benefits of ownership homework assignments.

This is an easier assignment; however, you may need to adjust your TCO and TBO tabs to make it work correctly. This assignment has a long-term benefit of completing your working template for your future forecasting and financial analysis endeavors – AFTER you compete this course.

CONTEXT

This is a continuation of the previous assignment. Recall that you were appointed to an IT Steering Committee as a resource. The IT Steering Committee is co-chaired by the Deputy Chief Information Officer and the President’s Chief of Staff. The purpose of the committee is to approve and prioritize the largest IT projects of the organization. Historically, the volume of IT project proposals far exceeds the available resources.

Also recall that members of the steering committee have expressed concern that the financial information is frequently incomplete, inconsistent, and misleading. They feel that in many cases, decisions to approve projects are often based as much on emotion than fact. There is also concerns that the user community of IT services does not really put much effort into quantifying benefits of proposed IT solutions – meaning, they rely heavily on an emotional response to vaguely worded intangible benefits.

At the end of the last steering committee meeting, several members essentially expressed the following paraphrased position: “Wouldn’t it be nice if we received the same financial information for every proposed project, so we could make more intelligent decisions and holding those who propose projects to more completely and accurately present their financial data?”

You previously competed a spreadsheet that included worksheets (tabs) for total costs and benefits of ownership. Members of the steering committee would like to see that spreadsheet extended with a worksheet (tab) to analyze the costs and benefits to determine a project’s financial worthiness.

DEFINITIONS

· Spreadsheet – a collection of one or more tabular data worksheets in a single file. 


· Worksheet – a single tabular worksheet contained within a spreadsheet. In Excel, a 
worksheet is a single “tab” of a spreadsheet file. 


ASSIGNMENT REQUIREMENTS 


· You were appointed to the committee to address the issue. Your next charge is to analyze the costs and benefits to determine financial worthiness. 
Extend your spreadsheet from the projects from Learning Modules 3 and 4 to address cost/benefit analysis (CBA) taught in Learning Module 5. (NOTE: Recall that we use the term CBA as an umbrella for ALL financial analysis techniques, as opposed to Bannister who used CBA to mean a single technique.) 
There should be very little user data input required for this milestone. The data was input using the tabs from the TCO and TBO assignments. Your CBA tab should merely import selected data from the TCO and TBO tabs, and then do the analysis. The steering committee wants four different analyses: 


· Net Present Value (NPV) 


· Discounted Return on Investment (ROI) 


· Internal Rate of Return (IRR) 


· Discounted Payback Period 
In all of the above, cash flows (summed costs and benefits) should be discounted to their present value based on the years in which those cash flows are expected to be realized. That’s why you import the data from the TCO and TBO tabs first. That way, you discount the flows in this new tab without changing any numbers on the original TCO and TBO tabs. 


Your design will be considered at a special meeting of the steering committee.

While you are welcome and encouraged to do research in support of this exercise, this exercise should be fairly simple. TCO was just BIG. TBO was more creative. CBA is mostly an exercise in applying built-in spreadsheet functions and known financial formulas to calculate the numbers. You may find creative ways to do this using Web searches.

The committee has provided the following high-level expectations and suggestions:

1. Costs, benefits and cost benefit analysis should be summed and imported you’re your new CBA worksheet. Recall that we stated the not all costs get imported. 


2. As emphasized in class, in order to make financial analyses work properly, all net costs should be expressed as negative numbers in the CBA worksheet, and all benefits should be positive numbers. 


3. There should be sufficient instructions built into your tool, both to train new users, and to help all users to properly use the tool. Most of you have already created a separate tab for instructions. You will need to update these instructions for CBA. 


4. It should be very clear what fields are for user input, and which are output fields (that calculate themselves). Color is your friend! 


5. Some steering committee members would welcome graphical representations of the analysis. 


ASSUMPTIONS

· a) You know how to use Microsoft Excel or equivalent, and have the ability to teach yourself new features and capabilities of the spreadsheet software, as needed. 


· b) In the past, most students adapted the structure and style of the TCO and TBO tabs as a point of departure for a CBA tab. But the CBA worksheet is usually much smaller because cost and benefit data are summed before importing. 


· c) You may discover your need to make changes to your TCO and TBO worksheets to synchronize with your CBA worksheet in the same file. 


· d) There is an element of professional creativity/innovation to this assignment – more so than the TCO or TBO worksheets that were based on a template that was supplied to you. 


· e)  (Because this is a graduate course, we have not made teaching spreadsheet technology as course learning outcome. That would be more suited to an undergraduate course, typically at the freshman level in most contemporary technical and non-technical curricula.)

SUBMISSION DOCUMENTS

· Submit your worksheet as an e-template. Proper templates have a different extension than actual spreadsheets created with that template. 


· Submit a separate file with sample data to show your solution works. You are responsible for sufficiently testing your solution.


INSTRUCTIONS 


i. You must use Microsoft Excel or equivalent to develop your solution, but if you use anything other than Excel, you are responsible to make sure that it will open and function properly within Excel. This includes any formatting features, calculations, macros, graphs, frozen panes, or other features you choose to include. 


ii. Be sure to thoroughly test your solution to ensure that calculations, features, and limitations work as expected. Your instructor will be testing your solution. (NOTE: Excel provides useful tools to audit spreadsheets.) 


iii. NO NOT APPLY PROTECTION! (Normally, you would, but it may interfere with grading.) 


iv. You must submit your assignment electronically via the Blackboard system. 


v. If you worked in teams, each member must submit their own assignment, but the submission should clearly indicate the names of all team members. Name your files as follows: {component} {last name}. For example: 
CBA Template YOURLASTNAME.xlt or .xlm and CBA Test Sample.xls