Assignment in total cost of ownership for IT solutions using excel
lauren II
it_economics_assignment_.docxASSIGNMENT CONTEXT
You have been appointed to an IT Steering Committee as a resource. The IT Steering Committee is co-chaired by the Deputy Chief Information Officer and the Chief Executive Officer’s Chief of Staff. The purpose of the steering committee is to approve and prioritize the medium-to-large IT projects of the organization. Historically, the volume of such IT project proposals far exceeds the available resources. Accordingly, some projects will be approved, and others will not.
While many (if not most) of the project proposals include a financial analysis of the proposed investment, each candidate project’s sponsor or manager is free to use whatever financial analysis format they choose in order to justify their project. Several 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 as fact.
Furthermore, after projects get approved, it is not uncommon to discover that “surprise” expenditures get proposed after the fact, leaving the committee to wonder if the original cost estimates were intentionally deflated to make the investment appear more attractive than claimed. If not, they wonder if those making the estimates are competent.
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? And wouldn’t it be nice if it included standardized cost categories that require a thorough documentation of what an IT solution will cost during both development and its lifetime of operation.”
ASSIGNMENT REQUIREMENTS
You have been appointed to the committee to address the issue. Your first charge is to get a handle on cost estimates for proposed IT projects.
Build a spreadsheet based upon, or similar to the TCO sample presented in your CNIT 55100 class. That sample is a work-in-progress that was developed by another company. (It has been posted has been posted to Blackboard as a part of this learning module.) The sample spreadsheet is not complete, adequately tested, or audited (It was not developed by the instructor.) Past students have found errors and omissions as compared to the books, class lessons, their experience, and their research. It is intended only as a sample. You can use it as a point of your own departure, but only if you recognize that it is flawed. Or you can start over, but if you do, your tool must be as comprehensive as the instructor template.
YOUR spreadsheet should force IT project managers and analysts to more fully investigate and estimate the total cost of ownership of every IT solution. Its use will eventually become mandated.
Your design will be considered at a special meeting of the steering committee. The committee has provided the following high-level expectations:
1. There should be sufficient instructions built into the tool, both to train new users, and to help all users to properly use the tool. Why build instructions into the tool? Because bundling the instructions and tool into one spreadsheet file allows it to be used as a template that can be instantiated for each project. The sample used the approach of created an instructions tab. An option would be to build instructions into each tab as needed.
2. The tool should include mechanisms to demonstrate who will pay for what costs. It is, however, recognized that these commitments might not be known at the time the proposal is considered by the steering committee. But at the very least, the funding sources should default to “to be determined.” Take note of how the sample handled assigning costs to cost centers.
3. The tool should be customizable such that allowable values for non-numeric fields can be changed as our organization changes. The sample demonstrates an approach.
4. It should be very clear what fields are for user inputs, which are calculated, and which are just row and column headings or instructions.
5. The spreadsheet should encourage users to think of cost categories and costs that they might otherwise forget without such a spreadsheet.
6. It should be reasonably easy to add rows within established categories because projects come in all shapes and sizes.
7. Ideally, the spreadsheet should offer users and management the ability to view various levels of detail, without going to an entirely new spreadsheet of tab. Different levels of management may or may not want to view all details. (Excel allows rows to be grouped, and groups can be expanded and contracted at will, with summary data presented to some readers, and both detailed and summary data presented to other users).
8. It should be very easy to distinguish between project and post-project, operational costs. The absence of post-project, recurring costs has been a sore-point with the committee.
10. Some steering committee members have suggested graphical representations of the data.
You are welcome and encouraged to do research in support of this exercise. Total cost of ownership is a common term that should reveal many published ideas. Any implementations that build on published papers or intellectual property must be properly cited somewhere.
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. Although it is permitted to use esoteric features such as VBscript, this exercise can be completed at the proficient level without having to resort to such programming.
· b) A decently structured sample TCO developed by another company has been provided via Blackboard (I can send you this doc if needed). It contains some level of the implementation guidelines and suggestions presented in this document. But since it was not developed in conjunction with the exercise or course, it should NOT be considered perfect. It does not include formulas. That it left to you to demonstrate you know what costs are applicable to what years and how to calculate them You can use a copy of the aforementioned sample as a point of departure; however, you may find it more useful to study its features and implementation to create your own template from scratch.
· c) There is an element of professional creativity/innovation to this assignment.
SUBMISSION
Submit your worksheet as an e-template. Proper templates have a different extension than actual spreadsheets created with that template. Templates do NOT have sample data in them. But they do include instructions for use, contextual help for appropriate cells, and good data editing to ensure that users enter appropriate data.
Again, a template should not include ANY data. It is designed to force its users to enter data into appropriate cells, and have that data extended to other cells using logic and formulas.
To prove that you have tested your spreadsheet to insure all rows, columns, and cells work, you should submit a file created from your template that is named TEST PROJECT.
INSTRUCTIONS
1. You may work alone, or in a team of no more than two students. If you work in teams, you must request approval before doing so (via email).
2. Normally, spreadsheet users should be locked out of making changes to output fields. Otherwise, they may compromise the integrity of the data. In other words, formulas should be protected from tampering. However, locking cells with passwords prevents the instructor from grading. Therefore, you should NOT lock your spreadsheet in any fashion for this assignment.
3. 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.
4. When printed, your spreadsheet should fit width-wise on one letter- or legal-sized page. Test its printability!
5. 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.)
6. If you choose to submit your file in any format other than Microsoft Excel, you must test it to make sure that Excel will open and work with your tool.
