University of North Texas Leveraging Information Technology Worksheet
Description
| Case3 | |||
| You are working with the IS Strategy team at VITA. The team is building an web site to showcase their vision to the agencies and employees. You are also working on the outsourcing startegy and vendor selection. |
|||
| Steps to Perform: | |||
| Step | Instructions | Points | |
| You should write smart formulas (wherever feasible), i.e., write once and drag/copy to other cells. If you write multiple formulas where a single formula was possible, then points will be deducted. Marks will not be awarded if you write the answers where formula is required. |
|||
| 1 | On the Q1 worksheet, perform the following | ||
| Using the data in Table1, create a Gantt chart in the range B17:K33 to display the timeline of project activities. | |||
| 1(a) | Create a clustered bar chart with G14:G22 as the series | 8 | |
| 1(b) | Add Duration (days) as additional series | 4 | |
| 1(c) | Add activity description to vertical axis | 1 | |
| 1(d) | Sort vertical axis in reverse order | 2 | |
| 1(e) | Add start date to horizontal axis | 1 | |
| 1(f) | Change the minimum value to 44228 | 1.5 | |
| 1(g) | Change the maximum value to 44244 | 1.5 | |
| 1(h) | Change the chart title to “Gantt Chart : Development of web page” | 1 | |
| 2 | On the Q2 worksheet, perform the following | ||
| IT Outsourcing is managed by the firm itself, hosted and managed on-site or at a colocation facility, while Managed Services is a managed infrastructure service delivered by a provider and hosted in their data centers. It would help if you compared IT Outsourcing and Managed Services and recommend a better solution to the VITA management. Table 1 has IT Outsourcing and Managed Services’s ratings on the various parameter; the rating is on a scale of 1 to 4, where 1 is least preferred, and 4 is most preferred. Cell range C12:C20 has the weights for the assessment criteria. Range D12:D20 and E12:E20 have the ratings for IT Outsourcing and Managed Services, respectively. |
|||
| 2(a) | Using SUMPRODUCT Function, write a formula in cell range D22 to compute the weighted score of IT Outsourcing. Copy the formula to cell E22 to calculate Managed Services’ weighted score. | 5 | |
| 2(b) | Using IF function, write a formula in cell range D24 to display your recommendation. Hint: IF the Weighted Score of IT Outsourcing is more than Managed Services, then IT Outsourcing should be recommended, else Managed Services should be recommended. | 2 | |
| Based on your recommendation, management is evaluating various Vendors for Managed Services. You have categorized vendors based on various assessment criteria and listed them in Table 2. Table 3 shows the categories and Ratings. | |||
| Based on the data in Table 2 and Table3, convert the categorical values to Rating Scale values. | |||
| 2(c) | Using VLOOKUP write a formula in cell D60 to lookup for the cell D32 value in Table 3 (using Exact match type). Copy the formula to cells D60:F69. | 5 | |
| 2(d) | Using SUMPRODUCT Function, write a formula in cell range D71 to compute the weighted score of CSC. Copy the formula to cells E71 and F71 to calculate other vendor’s weighted scores. | 5 | |
| 2(e) | Using MAX Function, write a formula in cell range D73 to compute the maximum weighted score. | 2 | |
| 2(f) | Using MATCH Function, write a formula in cell range D74 to compute the cell’s column number having the maximum score. Hint: Match the value D73 in the array D71:F71 using exact match type. | 4 | |
| 2(g) | Using INDEX Function, write a formula in cell range D75 to display the vendor. Hint: INDEX the value of cell D74 in the array D59:F59 | 4 | |
| Based on feedback from key stakeholders regarding the project risks and vendor experience, the weights for those criteria are changed. Based on the data in Table 2 and Table3, convert the categorical values to Rating Scale values. |
|||
| 2(h) | Using VLOOKUP write a formula in cell D86 to lookup for the cell D32 value in Table 3 (using Exact match type). Copy the formula to cells D86:F95. | 5 | |
| 2(i) | Using SUMPRODUCT Function, write a formula in cell range D97 to compute the weighted score of CSC. Copy the formula to cells E97 and F97 to calculate other vendor’s weighted scores. | 5 | |
| 2(j) | Using MAX Function, write a formula in cell range D73 to compute the maximum weighted score. | 2 | |
| 2(k) | Using MATCH Function, write a formula in cell range D74 to compute the cell’s column number having the maximum score. Hint: Match the value D73 in the array D71:F71 using exact match type. | 4 | |
| 2(l) | Using INDEX Function, write a formula in cell range D75 to display the vendor. Hint: INDEX the value of cell D74 in the array D59:F59 | 4 | |
| 3 | On the Q3 worksheet, perform the following | ||
| One of the requirments of VITA project is Desktop refresh, which means that the desktops need to be changed as per the new technology. You are on a committee to evaluate three desktop refresh vendors. Table 1 has ratings (out of 5) of the desktop refresh vendors based on criteria. Create a visualization to show this data. |
|||
| 2(a) | Select the data in the range B4:E9 and create a Radar chart with markers in the range G3:O20. | 6 | |
| 2(b) | Change the chart title to “Vendor 3 looks promising”. | 1 | |
| 2(c) | Change the legend position to bottom. | 1 | |
| 4 | On the Q4 worksheet, perform the following | ||
| Vendors adopt a strategy wherein they give discounts when the project is renewed, but charge higher in the initial years. Table 1 has the cash flows for the two proposals from the same vendor for a Managed Services project implementation. The project is due for renewal at the end of year 10, so the cash flows in year 11, 12 and 13 will be incurred only if the project is renewed. Discount rate to discount the future cash flows is given in cell C5. |
|||
| 4(a) | Using NPV function, Write a formula in G8 to compute the NPV for cash flows from Year 1 to 10 of proposal 1, copy the formula to H8 to compute the NPV for cash flows from Year 1 – 10 for proposal 2. | 9 | |
| 4(b) | Using NPV function, Write a formula in G9 to compute the NPV for cash flows from Year 1 – 13 of proposal 1, copy the formula to H9 to compute the NPV for cash flows from Year 1 – 13 for proposal 2. | 9 | |
| If the vendor is given extension then NPV from year 1 to 13 should be considered, however if the vendor is not given extension then NPV from year 1-10 should be considered. However at the start of the project, we have no idea whether the vendor will be given extension, we can only assign some probability to it. The probabilities are given in Table 2. You need to compute the Weighted (or Expected) NPV of both the proposals. Weighted NPV = Probability of extension * NPV for year 1 to 13 + (1 – Probability of extension) * NPV for year 1 to 10 |
|||
| 4(c) | Write formula in D23 to compute weighted NPV for Proposal 1, copy the formula down to cell D29 and then across till E29. (Create formula such that you can copy down and across and get the correct result, use proper referencing) |
5 | |
| 4(d) | Write a IF function to decide which proposal to be selected. Note : We should select proposal which has Lower NPV. | 2 | |
| Save the file and upload it | |||
| Total Points | 100 | ||
Have a similar assignment? "Place an order for your assignment and have exceptional work written by our team of experts, guaranteeing you A results."
Complete the 
Complete the order payment after filling in the order form. We’ll receive your order and assign it to a writer.
The assigned writer will complete your paper and forward it to our Editing Team for review and approval.
Once approved by the editor, the completed paper will be uploaded to your account for you to download, review and approve.