A Total Benefits of Ownership Analysis Tool

CNIT 55100 and 58100-EPMInformation Technology EconomicsAssignment for Learning Module 4A Total Benefits of Ownership Analysis ToolINTRODUCTIONThis is the second of a series of three related homework assignments: Total Cost of Ownership and Funding Analysis PREVIOUS ASSIGNMENT Total Benefits of Ownership THIS ASSIGNMENT Cost / Benefit AnalysisThe purpose of this exercise is to assess your understanding of the total benefits ofownership of proposed IT solutions. This is an extension of the previous total costs ofownership homework project. The forecasting of the lifetime benefits of an informationtechnology solution has long challenged IT professionals, even more so than lifetime costs.Many executives of IT solutions believe that business managers and other non-IT professionalspurposefully over-inflate lifetime benefits, or double-count selected benefits to make solutionseasier to sell and fund. But truthfully, many estimators also, or actually understate manybenefits to their own disadvantage.The problem with IT solution benefits is that tangible benefits frequently do not offsettotal costs. This is particularly a problem when proposed IT investments must compete withnon-IT investment alternatives that have greater tangible benefits. Accordingly, it is to thebenefit of the analysts, project managers, and other estimators to quantify both tangible andintangible benefits to prove the financial worthiness of a proposed IT project.This assignment has a long-term benefit of helping you to develop a working templatefor your future forecasting endeavors AFTER you compete this course. The project has a longterm benefit of asking you to develop a working template for your future budgeting endeavors.This assignment may not be as time intensive as the TCO assignment. But it will requiremore creativity and critical thinking as you endeavor to identify potential benefits for theworksheets user to explore. Unlike the TCO assignment, the instructor has NOT provided youwith a shell template. You need to build your own TBO worksheet, as a separate tab of thesame spreadsheet template you started in the prior assignment. You want you benefitsworksheet to stimulate estimators to think of as many potential benefits as possible especiallybenefits that some forget to consider.For the more tangible benefits, you can provide input fields with formulas, just as youdid for TCO. But for less tangible benefits, all you can do is provide input fields for them toenter best guesses (sometimes called guesstimates) outside of the spreadsheet. Multipleexperts (Gildersleeve, Hubbard, and others) have suggested techniques for estimatingintangible benefits you would subsequently enter in the worksheet.ASSIGNMENT CONTEXTThis is a continuation of the previous assignment. Recall that you have been appointedto an IT Steering Committee as a resource. The IT Steering Committee is co-chaired by therespective chiefs of staff of the Chief Information Officer (CIO) and the Chief Financial Officer(CFO). The purpose of the steering committee is to approve, fund, and prioritize the mediumto-large IT projects of the organization. Historically, the volume of such IT project proposals farexceeds the available funds and other resources. Accordingly, some projects get approved, andothers do not.Also recall that many (if not most) of the project proposals include a financial analysis ofthe proposed investment, each candidate projects executive sponsor, business analyst, andproject manager is free to use whatever financial analysis content and format they choose tojustify their project. Several members of the steering committee expressed concern that thefinancial information is frequently incomplete, inconsistent, misleading, and questionable. Theyfeel that in many cases, decisions to approve projects are often based as much on emotion asfact.At the end of the last steering committee meeting, several members essentiallyexpressed the following paraphrased position:Wouldnt it be nice if we received the same financial information for everyproposed project, so we could make more intelligent decisions and hold thosewho propose and manage projects to more accurate financial projections.And wouldnt it be nice if it included standardized cost and benefit categoriesthat require a thorough assessment of costs and benefits that might occur.You previously competed a spreadsheet for total costs of ownership and fundinganalysis. Members of the steering committee would now like to see that spreadsheet extendedto include a total benefits of ownership worksheet (tab). Your design will be considered at aspecial meeting of the steering committee. By extended, the committee means that thisworksheet should be added to the TCO worksheet rather than developed as a separate file.DEFINITIONS Spreadsheet a collection of one or more tabular data worksheets in a single file. Worksheet a single tabular structure contained within a spreadsheet. In Excel, aworksheet is a single tab of a single spreadsheet file.ASSIGNMENT REQUIREMENTS STATEMENTYou were appointed to the committee to address the need to develop a comprehensiveand consistent template for use in economic justification decisions. Your next milestone is toget a handle on benefits estimates for ANY proposed IT project. The committee believes thatthis is a bigger challenge than TCO but they think you are up to it. And they believe that ifyour spreadsheet could creatively address both costs and benefits, that any subsequentcost/benefit and financial analyses would be more consistent and accurate for project selectionand prioritization.Extend your spreadsheet from the last assignment to calculate or record total benefitsof ownership (TBO). The spreadsheet should guide users to more fully investigate and estimatethe total benefits of ownership of every IT solution. It should help them identify categories andbenefits that may not have thought of, and stimulate their creativity to identify furthercategories and benefits. Its use will eventually be mandated except in cases where funding isalready established, or when the steering committee predetermines that the project must beapproved regardless of a benefits analysis.The sample template you completed in the last assignment did not include a TBO(although it did include a tab for one). And for this assignment, you will not be providedtemplate shell by your instructor. You will have to create this worksheet from scratch. The TBOshould be a separate tab(s).RECOMMENDATION It is recommended you make a copy of your TCOassignment file to use as a point of departure. You will retain the TCO worksheetfrom the last assignment, and focus on the TBO worksheet tab for thisassignment.It is further recommended that you start your TBO tab by copying and pastingthe TCO worksheet into the TBO tab to using as a design point of departure.Specifically, you should probably use the Paste Special command to create yournew worksheet this preserves your formatting (e.g., column widths, colors,etc.) to ensure a consistent, professional look and feel. But for the TBOworksheet, youll have to reorganize and rename the rows. And you will have tore-purpose and possibly add and delete columns. Remember, this newworksheet must address benefits, not costs. Because you will be re-purposingmany rows and columns, you will have to update almost all of your pop-updefinitions, comments, and help.YOUR worksheet should force IT project managers and business analysts to more fullyinvestigate and estimate the total benefits of ownership of every IT solution. Since they maynot have as much education and experience as you, the template should seek to remind themto consider benefits that are common to IT solutions. We covered these in the lessons. The useof the template will eventually be mandated for all proposed projects.You are welcome and encouraged to do research in support of this exercise. Yoursecondary books and TBO lesson notes will help. Also, Bannister included a section in Chapter 4,under the heading of cost benefit analysis, that focuses more on benefits than costs. So, itcould help. You may add benefit categories not covered in class these could be from your ownexperience or research. As graduate students, you are always encouraged to pursue your ownresearch. Since benefits analysis is more subjective, such research may reveal interesting ideasand/or existing templates or tools. Any implementations that builds on published papers orintellectual property must include proper APA citations for that property (perhaps in areferences tab, or in the instructions tab of the spreadsheet).The committee has provided the following functional requirements and qualityproperties:1. The steering committee and top management wants to force estimates to considermore candidate benefits. They are concerned that IT projects frequently do not getas much executive consideration because benefits are underestimated.2. The steering committee wants to better understand when benefits will be realizedacross the years. They understand that most benefits cant START until after the ITsolution has been developed and implemented. But they want to help executivesbetter appreciate that once these benefits initially occur, most of them tend to recurand accumulate over the subsequent years.3. For the time being, they request NO rounding of numbers in the TBO.4. Your tool should provide mechanisms to enter who (what organization unit or units)will realize each benefit. It is entirely possible that multiple units, or all units willrealize a given benefit.5. There should be sufficient general instructions built into the tool, both to train newusers, and to help all users to properly use the tool. Why build instructions into thespreadsheet as opposed to in a separate document? Because separate documentsfrequently get detached from one another. Your instructions should teach me (andothers) how to use your tool.It should be noted that these TBO benefit instructions should probably be anextension of those that you should have created in the TCO assignment.6. Users may not be familiar with your column and row names. You should teach theminside the tool. There are two ways to do this:a. One way would be to build definitions directly inside your row and columnheaders as pop-ups. Excel provides two ways to do this:i. You can insert a comment into the cell to pop up whenever thecursor floats over the cell. Be forewarned that Excel does notautomatically adjust the comment cell size and may, therefore,truncate your comment to make it less useful. Also, somespreadsheet users find it annoying to have all those messages pop-upas they move their cursor across the screen.ii. Or you can make a comment only pop-up only when you click-on (orotherwise select) the row or column header. This is done using ExcelsData > Validation menu path. There is a 256-character size limit tothe comments added this way. This has been the preferred approachof most past students.b. IMPORTANT Because many (if not most) rows, sub-rows, columns, and subcolumns will change when you design your TBO from a copy of your TCO, theTCO pop-ups will not be appropriate.7. Good worksheets make it visually clear what fields are for user inputs, which arecalculated, and which are just row and column headings or instructions. This can beaccomplished with consistent color. But for a good user experience, you should notchange color schemes when designing the new tab. In other words, if WHITE wasthe color for a user input cell in the TCO, WHITE should also be the color for a userinput cell in the TBO.8. Speaking of user input cells, some tangible benefits can calculated with the same orsimilar formulas as on the TCO. They are reverse costs (savings). But many formulascannot be reused.More importantly, for intangible benefits, they cannot be estimated in the actualTBO worksheet. They are intangible. So, many of such intangible benefits will notuse input cells for formulas. Those cells will get blacked out (assuming you use thecolor scheme from the instructors original prototype shell). Instead, the estimatorwill use off-line methods to estimate them, and then directly enter the results in tothe appropriate Year columns thus, those Year cells become input fields asopposed to the calculated fields they were in the TCO.9. Normally, you could further protect cells by locking all but the input fields in aspreadsheet. This technique prevents heading fields and formula fields from beingchanged by the spreadsheets users. However, locking cells with passwords mightprevent the instructor from grading. Therefore, if you decide to lock cells in yourspreadsheet, you must provide the instructor with the password in your Blackboardsubmission comments, or risk point penalties for being late, or not followinginstructions. Cell locking is NOT a requirement for this assignment, and frankly, it isnot recommended. After the course is over, and should you share your templatewith future colleagues, cell locking would be highly recommended in order topreserve the consistency and integrity of your template.10. Good worksheets apply formatting to appropriate fields/cells. For example, currencyfields should include a currency marker (e.g., $ for U.S.). Another example is thatfields that are percentages include the %.11. Good worksheets always take steps to ensure users enter correct data intoappropriate INPUT cells. This might take the form of pop-up help messages,restriction of values that can be entered (e.g., drop-down lists and ranges of values),and error messages when illegal data is entered. Fortunately, the Excel menu, Data> Validation allows you to quickly develop these capabilities. This simple-to-learntool is worth a little learning.12. You should make it reasonably easy to add rows within your established categoriesof benefits because projects come in all shapes and sizes.13. Your worksheet template should offer users and management the ability to viewvarious 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. In Excel,see the menu path Data > Group and Outline to learn how to accomplish this.14. You need to test your worksheet to make sure users will enter proper data, and thatall formulas execute properly, and the results are correct. Also, every itemized costshould properly get included in appropriate sums.15. Some steering committee members have suggested graphical representations of thedata.16. Your TCO and TBO worksheets must be consolidated into a single spreadsheet file.The committee suggests a separate tab (called a worksheet in Excel) for TCO andTBO; however, the committee is open to other possibilities. The TCO tab shouldprobably precede the TBO tab since TCO is usually calculated before TBO.COMMON MISTAKES ON THIS ASSIGNMENTv A common cause of stress is procrastinating the start of this assignment. There isnothing I can do to help such an issue other than to emphasize getting started assoon as possible and pacing ones self. Nd you have less time for this assignmentbecause it should be more familiar after having done your TCO.v WARNING One of the most common mistakes made by students is to not changenames of columns and rows to reflect BENEFITS as opposed to costs, and change theassociated pop-up help messages. This occurs because the students sensibly startwith a copy of the TCO rows and columns so that they can merely adjust theformulas. While some benefits are inverse-costs and can be easily adjusted, somebenefits are totally unrelated to any cost and those rows must be deleted. A fewcolumns are also irrelevant to benefits and must be deleted. And some cells especially for intangible benefits must be blacked out because the estimationtechniques do not lend themselves to Excel formulas (as discussed in the lessons).v WARNING Each semester, one or two students seem to make the mistake ofthinking this assignment is just about submitting a sample TBO with real numbers.While you are encouraged to submit a sample file to demonstrate your testing, thegrade will be assigned for a tool with no data, but which can be later used to enterdata and estimate the benefits of ANY project, with minimal advising and mentoringby you. As part of grading, the instructor will be testing your blank template byentering his own data.v WARNING Each semester, one or two students seem to make the mistake ofoversimplifying the tool by consolidating categories or not having users enteritemized benefits, or by not assigning benefits automatically to the correct years.This is a serious error that typically results in a C or D score.v WARNING A more common mistake is to indiscriminately copy formulas from onebenefit category to another, if they are same between categories. In class, wediscussed numerous differences between and within categories. These differencesrequire adjustments in formulas to demonstrate that you understand the oftensubtle differences.v WARNING Another common mistake is not use the spreadsheet technology toassign benefits to the correct years. Hint: You can use IF technology in Excelformulas.ASSUMPTIONSa) You know how to use Microsoft Excel or equivalent and have the ability to teachyourself new features and capabilities of the spreadsheet software, as needed.Although it is permitted to use esoteric features such as VBscript, this exercise can becompleted at the proficient level without having to resort to such programming.Remember, I am using Excel to test your understanding of course content notexplicitly to teach advanced Excel. Any Excel learning that occurs is just a bonus forfuture career.b) You may discover your need to make changes to your TCO worksheet to synchronizewith your TBO worksheet in the same file.c) Again, there is an element of professional creativity/innovation to this assignment more so than the previous homework since the original template that was supplied didnot include a fleshed-out benefits tab.d) Because this is a graduate course, we have not made teaching spreadsheet technology atopic. That would be more suited to an undergraduate course, typically at the freshmanlevel in most contemporary technical and non-technical curricula. But the followingreference has been endorsed by several recent students as helping to improve Excelskills, specifically written to these type of financial assignments:Fairhurst, D. S. (2015) Using Excel for business analysis: A guide to financial modelingfundamentals (Excel 2013 edition). Singapore: John Wiley & Sons Singapore Pte. Ltd.ISBN 978-1-119-06246-2 (paperback)ISBN 978-1-119-06245-5 (ePDF)ISBN 978-1-119-06244-8 (ePub)ISBN 978-1-119-07001-6 (o-Book)SUBMISSION Submit your worksheet as an e-template file. Proper templates have a differentextension than actual spreadsheets created with that template (in MS Excel, templateshave an .xltx or .xltm file extension that prevents users from accidentally overwriting thetemplate). It forces those users to assign a new name with an extension of .xlsx.Templates should NEVER have actual data in them. But they do include theaforementioned instructions for use, contextual help for appropriate cells, and gooddata editing to ensure that users enter appropriate data. Your TCO and Funding tabs from the last assignment must be included in the file. Youare welcome to make improvements to your TCO. Remember, the value of theseassignments deliberately go beyond our course. To prove that you have tested your TBO worksheet to insure all rows, columns, and cellswork, you must ALSO submit a separate file created from your template that is namedTEST PROJECT. When you test a benefit item, dont forget to ask yourself if it makessense. For example, if your solution eliminates employees, you save the salary andbenefits of those employees every year after they are laid off. But if you sell acomputer, you only get that benefit in the year sold (unless, of course, it also eliminatesa recurring maintenance expense).OTHER INSTRUCTIONS I. You must work alone on this assignment. You are not permitted to share spreadsheetswith one another, in any format. Doing so would constitute academic dishonesty(cheating) and would be dealt with harshly as described in the course syllabus.CAUTION: This exercise has proven to stress some students. To these students, it oftenII. appeared that it was a course goal to learn Excel. In reality, learning more about Excel ismerely a vehicle towards the true goal of demonstrating that you understand the truetotal benefits of ownership of an IT solution.Another VERY common cause of stress is procrastinating the start of this assignment.There is nothing the instructor can do to help such an issue other than to emphasizegetting started as soon as possible and pacing ones self. THERE, I said it again. III. If you use any other spreadsheet technology other than Microsoft Excel, you areresponsible to make sure that it will open and function properly within Excel. This includes any formatting features, calculations, macros, graphs, frozen panes, or otherfeatures you choose to include.IV. Be sure to thoroughly test your solution thoroughly to ensure that calculations,features, and limitations work as expected. Your instructor will be testing your solutionwith his own data. (NOTE: Excel provides useful tools to audit spreadsheets as part oftesting). Failure to thoroughly test their spreadsheet is a common cause of lost points inthis assignment. V. You must submit your assignment electronically via the Blackboard system. You mayresubmit any time before the due date; however, only the last submission will begraded. Include all files in EVERY new submission. Why? Because the old submission andits files will be deleted without grading.Name your files as follows: {component} {last name}. For example:VI. TBO Template Whitten.xltm TBO Test Project Whitten.xls xltm is the extension for an Excel template .xlsx is the extension of an actual spreadsheet with test data GRADING RUBRICSee the grading rubric in the Blackboard Assignment for the grading criteria andweights. It will tell you how your instructor will score your submission; therefore, you shouldstudy it BEFORE you start your work. Subsequently, you should use it as a checklist before yoursubmission.The grading rubric does NOT include possible late penalties (which are enforcedseparately).VIRTUAL OFFICE HOURS FOR HELP ON THIS ASSIGNMENTA Blackboard Discussion Forum has been established for this specific assignment. This isNOT a graded forum. Participation is highly recommended. The discussion forum allows you toask questions of your instructor and fellow students. (BUT IS NOT TO BE USED TO SHARESOLUTIONS, EVEN PARTIAL SOLUTIONS). That would constitute academic dishonesty(cheating).The value of this discussion forum is that when your instructor (or a fellow student)answers a question, ALL students will be able to see that answer. Personal questions shouldnever be asked in any discussion forum personal questions or issues are better served byemail.It is HIGHLY recommended (if not mandatory) that you subscribe to this forum toreceive email notifications of when students and the instructor make posts. This has historicallybeen a very active assignment forum.The following posting guidelines are suggested: Give each discussion thread a meaningful name so that all students will recognize thetopic and be able to determine if the thread is useful to them. Limit all replies to the topic of the original thread. If you think of a unrelated questionor issue, start a different thread. The above guidelines are intended to ensure that students can pick and choosequestions to monitor based on similarities to their own questions or issues. If messagesin a particular thread deviate from the original topic, fellow students may not discoveruseful answers. You are allowed to discuss Excel and spreadsheet commands and methods as part of theforum so long as you do not share solutions to the homework, in part or in whole. Theinstructor may not reply to all spreadsheet questions since it is not a course goal toteach spreadsheets.

Pssst…We can write an original essay just for you.

Any essay type. Any subject. We will even overcome a 6 hour deadline.

<< SAVE15 >>

Place your first order with code to get 15% discount right away!

Impressive sample results