BINF 2321 Individual Project Due Date: This Project is due no later than Sunday of Week 7 at 11:59pm. Due to the time given to complete the assignment, late assignments will not be accepted and will result in a zero. Summary: This project will require a basic working knowledge of Microsoft Excel. I have provided a template of information on this assignment along with the figures that will require basic Excel formulas (highlighted in yellow) containing addition, subtraction, multiplication and division. I also have provided what areas need to be calculated together using basic mathematical formulas (highlighted in teal). You will have to build the spreadsheet utilizing this information, including all data and any required formulas to produce the actual Total Cost Ownership per PC. There are three components to this project, a Prezi Presentation, the Management Recommendation and the Excel Spreadsheet. Each component will be issued an individual grade. The average of all three grades will be the number that will count 20% of your overall project grade. For example, if you earn a 100 for the Prezi, 90 for the Management Summary and an 85 for the Excel Spreadsheet this gives you an average of 91.67, this number (91.67) will count 20% of your overall project grade. You will have about 4 weeks to complete this project so it’s probably best not to wait until the last week to get started. Because of the varying experience levels of each student. I would be happy to provide guidance along the way; all you have to do is ask! However, because of the time you are given to complete this project, please do not wait until the day before the project is due to begin asking your questions. Contacting each other and working together on this project is allowable or if you prefer to work on this individually that is fine also. Just make sure each individual student turns in their own work and submits their own individual assignment. Assignment: You are the Chief Information Officer (CIO) of a large manufacturing company. Profits have been down over the last few years and the CEO has noticed that IT expenditures have substantially increased during this time. In a recent meeting you mentioned that a standard PC Build for the company costs $893.00. The CEO has mentioned that she feels the total cost is substantially higher and wants to know the total cost of ownership (TCO) per PC in this situation, a decision needs to be made regarding employee and budget cuts in the IT department. The break point is $5,300 if the total cost of ownership per PC is more than the amount the CEO suggests cutting two positions in the IT Department. If the total cost of ownership is less than $5,300, these cuts will not have to be made. As CIO you are being tasked with determining the total cost of ownership for a single PC. In order to accomplish this you must gather the following information and create a total cost ownership calculator utilizing Microsoft Excel. This tool will act as your decision support system once you have calculated the TCO per PC. Since the budget cycle is approaching, the CEO is asking that you provide her this information no later than the Sunday of Week 7 at 11:59pm. This is when she will be making a decision on employee and budget cuts in hopes of remaining profitable in the upcoming years. The CEO is also asking that you provide a two to three page management recommendation on the problem at hand and how this total cost ownership tool will assist in making this important decision. Finally, the CIO would like for you to present your management summary findings to the board of directors using a Prezi presentation. (www.prezi.com) No PowerPoint! Excel Spreadsheet Template ORGANIZATION INFORMATION Total Company personal computers: 826 End Users of PC’s: 776 Average PC end users Salary: $44,883 Number of work hours in a year: 1760 DIRECT COSTS HARDWARE AND SOFTWARE Hardware Expenses in an average year: $527,877 Software Expenses in an average year: $195,567 Annual computer supplies: $83,768 Subtract Annual depreciation on hardware and software capital purchases: $45,887 Total annual hardware and software direct costs: (calculation) MANAGEMENT AND STAFF SALARIES Help Desk Personnel: $168,900 System Administration Personnel: $193,887 Network Administration Personnel: $192,534 Internal Training Personnel: $53,500 General End User Support Personnel: $43,000 IT Management Salaries: $211,392 Total IT Management and Staff Salary plus 30% overhead to account for salary increases (calculation): OTHER EXPENSES Travel Costs for above personnel: $45,000 Annual consulting and professional services fees related to infrastructure: $25,000 $30,000 is spent annually for training infrastructure personnel $125,000 is spent annually on maintenance contracts There was a minimal outsourcing cost of $2500 Total Other Management Expenses: (calculation) DEVELOPMENT COSTS Costs incurred for new application development areas are as follows: Design $45,000 Development $47,500 Testing $35,000 Documentation: $10,000 Existing application maintenance areas: $125,000 Consulting fees and professional services: $25,000 Total annual development costs: (Calculation) COMMUNICATION COSTS Leased Lines $122,000 Remote Access Communications $167,000 Annual WAN (Wide Area Network) costs $145,000 Total Communication costs: (Calculation) TOTAL DIRECT COSTS: (Calculation) Hint: Total Hardware and Software costs plus Total IT Management and Staff Salaries plus Total Other Management Expenses plus Total Annual Development Costs plus Total Communication Costs INDIRECT COSTS END USER ACTIVITY 40 hours annually on PC user training 25 hours annually on local file maintenance Annual cost of individual end user IT Activity: (Calculation) Hint: Average PC end user salary divided by number of work hours in a year then multiply this number by the total number of hours on PC training and Local File Maintenance. Then multiply this number by the total end users of PC’s DOWNTIME The Company loses 1.5 hours per month of end---user productivity due to planned or unplanned downtime. Total person hours lost annually due to downtime: Hint: Total number of hours lost per month of end user productivity multiplied by number of months in a year multiplied by total number of end users in the company Total annual cost of downtime: Hint: Average PC end users salary divided by number of work hours in a year then multiply this number by total person hours lost annually due to downtime. TOTAL INDIRECT COSTS: (Calculation) Hint: Total annual cost of End User IT activity plus Total Annual Cost of Downtime FINAL CALCULATIONS: Total annual infrastructure costs: (calculation) Hint: Total Direct Costs plus Total Indirect Costs TOTAL COST OF OWNERSHIP PER PC: (Calculation) Hint: Total annual infrastructure costs divided by total company personal computers What to turn in: 1) Excel Spreadsheet Total Cost Ownership Calculator with all information provided above including all calculations. Upload this spreadsheet into the Blackboard portal. Grading: I will grade the excel spreadsheet based on the formulas you use to solve the required math. There are several ways to accomplish this using Excel, I am ultimately looking for the correct answer and the excel formula you used to get to this answer. Don’t just plug in the answer into a cell without using a formula! I also will look at how your spreadsheet is organized, in other words is it logical, organized and easy to read? And last I will be looking if your TCO number is correct or not. Keep in mind there is the potential for departmental cuts if you miscalculate! Double check your work! 2) A management recommendation (in APA format) not exceeding 3 pages explaining: The problem of the business entity that will drive the need for this TCO tool. This could be a problem, supporting existing strategy or in reaction to external environment. The current constraints of the business entity and the benefits of the new TCO Tool, what it will do and not do. The project timeframe that explains requirements. Describe the types of system hardware and/or software that will be used for the system. The answer to the question: • Will there be cuts in this organization? Why or why not? • Does this TCO tool help with anything else in the company? • Will it improve organizational productivity? • Will it improve enterprise business value? If so please explain in your recommendation. Grading: I will be grading this section as per the rubrics in our syllabus. Keep in mind that points will be deducted if proper APA format is not used. For example if you do not submit a cover page or do not include the proper Running head, points will be deducted. Also, write this paper as if you were the CIO writing a recommendation to the CEO of your company, therefore write it as if your job depended on it! Upload this Word Document into the Blackboard Portal. 3) A Prezi Presentation (www.prezi.com ) No PowerPoint! Summarizing your management recommendation. This can be accomplished in about 8 to 12 slides. For this presentation, create an account on www.prezi.com. The only information required to create a free account is your name and email address, you can use your UIW e-mail address to create this, you can also use a fictitious name if prefer not to use your real name. No other information is required for the free version. After you have finished your presentation, submit it along with your Excel Spreadsheet and management recommendation paper. You can simply place the link in the “COMMENTS” section of your submission (see image below). I realize that many of you may have never used Prezi before; this is exactly why I would like everyone to get familiar with this product. In any job function you sometimes are required to learn new technologies in short time frames with very little if any training. With this presentation, I am more concerned about you giving effort into learning new software and providing a simple presentation on the management summary. Therefore the presentation does not have to be snazzy, please don’t spend more time than you need to on this. I would rather you spend more time on the spreadsheet and management recommendation. As long as the presentation contains the key information provided in your management recommendation you will be fine! Final Notes - For a company name, this is a fictitious company so you can simply make up a name. This sequence seems to work best for students who have completed this project before: • First create your Excel Spreadsheet and make sure to double check your answers (calculations)! • Next, write your management summary based on the information you gathered from your spreadsheet. • Last, develop the Prezi Presentation based on the information on your Management Summary. Here is a basic tutorial on how to create a Prezi: http://prezi.com/learn/ Please feel free to e-mail me with any questions or concerns that you may have.