Excel Case study

Please read the case below and solve it in Excel

after the excel do a powerpoint and add screenshots and short explanation for each step you did! “one screenshot for each slide, slides numbers depend on the steps you did in Excel” maximum slides is 7 slides only!

theres an example in the attachments file!

The grading rubric is in the attachments also!

RISK ANALYSIS CASE

ESTABLISHING A PERFORMANCE MANAGEMENT OFFICE

Introduction

An individual was recently hired as an organization’s new Chief Information Officer (CIO).
This is a large IT organization, and has two sites of about 50 people each where software is developed continuously throughout the year. Much of this software is enhancements to reflect changing government regulations, laws, company policies, and customer requirements, and much of this software is new development. The IT organization also has cybersecurity, hardware, and help desk components. After being in the office for a few weeks, he realizes that although the IT team is very talented in their technology, the operation of the business side of the IT organization needs significant improvement.

The CIO wants to consider establishing a Performance Management Office (PMO). This office would be tasked to measure the return on investment of IT investments, to measure the quality of software, to perform internal audits to ensure that software development money is being spent wisely, measure productivity rates of project teams, measure the CIO’s contribution to the organization’s Strategic Plan, collect and publish IT statistics, etc. The proposed organizationwill include a PMO manager, a senior Operations Research (Analytics) Analyst, a senior Business Case/Financial Analyst, and an experienced Admin Assistant. The purpose of this case study is to recommend to the CIO a 95% confidence interval for the first year costs of running the kind of PMO he is considering. Assume that the PMO people needed have been identified from the parent organization and would start together at the start of the calendar year.

When building the risk analysis model, assume that all probabilistic costs are modeled by the PERT method.

Office Space

Existing organizational office space will be used, so there will be no expense incurred for this.

Salaries

Those forming the PMO would retain their current salaries. The PMO manager salary is $160,000, the Operations Research Analyst salary is $145,000, the Business Case/Financial Analyst salary is $130,000, and the Admin Assistant salary is $85,000. The Admin Assistant is

senior to the position and may leave the PMO due to promotion at any time after the PMO is started up. The replacement salary could run anywhere between $67,500 and $82,500. Staff performance awards are expected to be $8300, with a range of between $6000 and $9000.

Travel Expenses

There are two software development sites. One is on the main property of where the PMO will be located, and the other is about 100 miles away. There is a standard automobile mileage rate, which is $.465 per mile. There is a daily per diem travel expense of $172 for that location which includes meals, personal items, and hotel room. The PMO Manager probably will make between 7 and 12 trips to the offsite location, expecting to make 10 trips. These trips will take an average 3 days each. The Operations Research Analyst will make between 5 and 10 trips to the off-site location, expecting to make 6; these should average 2 days each. The Business Case/Financial Analyst probably will make between 5 and 10 trips to the off-site location, expecting to make 7; these trips should average 2 days each. The Admin Assistant is not expected to make any trips to the off-site location.

Training/Conferences

All organizational employees are expected to attend about two weeks of conference or training session annually for professional development. These are usually out of town. Although there may be multiple sessions to take up all or most of the two weeks, each session should cost (tuition/conference fees) an average of $2200. Travel expenses (air fare, taxi, etc.) average $550 per trip. Trip lengths should average 5 days. The PMO Manager will take between 0 and 2 sessions, most likely taking 2. The Operations Research Analyst will take either 1 or 2 sessions, expecting to take 2; the same goes for the Business Case/Financial Analyst.

The Admin Assistant will usually obtain professional development locally, with a travel reimbursement of $14 for each leg of the trip each day. Therefore, there is no other per diem expense incurred. Tuition/conference fees should average $1850. The number of sessions should be 2. The number of days per session should run 2 days.

New Software

Several version of a new software type are currently being tested, and will be bought during the forecast year. Each version will be installed at 4 seats in the PMO, and costs range from a low of $450, to a high of $1000, with an expected cost of $650.

Requirements

1. Recommend to the CIO a 95% confidence interval for the first year costs of running the kind of PMO his is considering. Develop a spreadsheet showing all logic and calculations.

2. Also explain why this probabilistic approach was used instead of the traditional single estimate for each line item and the added value it brings.

3. Upload two documents into Canvas. One is the spreadsheet with your forecast model, and the second is a Word file with the answer to question 2 and any further explanatory material for the model. Explain your decision(s) on how to interpret any “gray areas” in the model.