Solve EXCEL ASSIGNMENT FORMULAS

B10 f Table 1 E H 1 4 B F G 5 Table 1 has VDI and Daas'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. 6 Cell range C12:C20 has the weights for the assessment criteria. Range D12:D20 and E12:E20 have the ratings for VDI and Daas, respectively. 7 (a) Using SUMPRODUCT Function, write a formula in cell range D22 to compute the weighted score of VDI. Copy the formula to cell E22 to calculate Daas' weighted score. (6) Using IF function, write a formula in cell range D24 to display your recommendation. Hint: If the Weighted Score of VDI is more than Daas, then VDI should be recommended, else 8 DaaS should be recommended. 9 10 Table 1 11 Assessment Criteria Weight VDI Daas 12 Platform Maintenance 15% 4 13 Initial Cost 15% 1 14 Ongoing Cost 15% 15 Control 5% 16 Time to setup 5% 17 Modification 10% 18 Dedicated IT Staff 15% 19 Total Cost of Ownership 10% 3 Technology Maturity 10% 4 21 22 Weighted Score Weighted Score 23 24 Your Recommendation Your Recommendation 25 4 1 4 2 1 4 3 1 1 4 3 20 2 Sample Answer 2.31 Daas 25 26 Based on your recommendation, management is evaluating various Vendors for VDI/Daas. 27 You have categorized vendors based on various assessment criteria and listed them in Table 2. 28 Table 3 shows the categories and Ratings. 29 30 31 Weight 32 33 34 Assessment Criteria Ease of management Accessibility Scalability Cost Savings Security Customer Support Customizability Table 2 Vendor1 12.0% Meets Expectations 10.0% Exceeds Expectations 8.0% Exceeds Expectations 30.0% Exceeds Expectations 20.0% Meets Expectations 15.0% Exceeds Expectations 5.0% Exceeds Expectations Vendor2 Exceeds Expectations Meets Expectations Does Not Meet Expectations Meets Some Expectations Exceeds Expectations Meets Some Expectations Meets Some Expectations Vendor3 Meets Expectations Meets Expectations Exceeds Expectations Exceeds Expectations Meets Expectations Meets Expectations Meets Expectations 35 36 37 38 39 40 Table 3 41 Rating Scale 42 43 Rating Category Does Not Meet Expectations Meets Some Expectations Meets Expectations Exceeds Expectations 44 45 46 A B D G H 47 Based on the data in Table 2 and Table3, convert the categorical values to Rating Scale values. 48 (c) Using VLOOKUP write a formula in cell D56 to lookup for the cell D32 value in Table 3 (using Exact match type). Copy the formula to cells D57:F62. 49 (d) Using SUMPRODUCT Function, write a formula in cell range D64 to compute the weighted score of Vendor 1. Copy the formula to cells E64 and F64 to calculate other vendor's weighted scores. 50 (e) Using MAX Function, write a formula in cell range D66 to compute the maximum weighted score. 51 (f) Using MATCH Function, write a formula in cell range D67 to compute the cell's column number having the maximum score. Hint: Match the value D66 in the array D64:F64 using exact match type. 52 (g) Using INDEX Function, write a formula in cell range D68 to display the vendor. Hint: INDEX the value of cell D67 in the array D55:F55. 53 54 Table 4 55 Assessment Criteria Weight Vendor 1 Vendor 2 Vendor 3 56 Ease of management 12.0% 57 Accessibility 10.0% 58 Scalability 8.0% 59 Cost Savings 30.0% 60 Security 20.0% 61 Customer Support 15.0% 62 Customizability 5.0% 63 64 Weighted Score 65 66 Maximum Score Column Number of Maximum Score (using B10 f Table 1 E H 1 4 B F G 5 Table 1 has VDI and Daas'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. 6 Cell range C12:C20 has the weights for the assessment criteria. Range D12:D20 and E12:E20 have the ratings for VDI and Daas, respectively. 7 (a) Using SUMPRODUCT Function, write a formula in cell range D22 to compute the weighted score of VDI. Copy the formula to cell E22 to calculate Daas' weighted score. (6) Using IF function, write a formula in cell range D24 to display your recommendation. Hint: If the Weighted Score of VDI is more than Daas, then VDI should be recommended, else 8 DaaS should be recommended. 9 10 Table 1 11 Assessment Criteria Weight VDI Daas 12 Platform Maintenance 15% 4 13 Initial Cost 15% 1 14 Ongoing Cost 15% 15 Control 5% 16 Time to setup 5% 17 Modification 10% 18 Dedicated IT Staff 15% 19 Total Cost of Ownership 10% 3 Technology Maturity 10% 4 21 22 Weighted Score Weighted Score 23 24 Your Recommendation Your Recommendation 25 4 1 4 2 1 4 3 1 1 4 3 20 2 Sample Answer 2.31 Daas 25 26 Based on your recommendation, management is evaluating various Vendors for VDI/Daas. 27 You have categorized vendors based on various assessment criteria and listed them in Table 2. 28 Table 3 shows the categories and Ratings. 29 30 31 Weight 32 33 34 Assessment Criteria Ease of management Accessibility Scalability Cost Savings Security Customer Support Customizability Table 2 Vendor1 12.0% Meets Expectations 10.0% Exceeds Expectations 8.0% Exceeds Expectations 30.0% Exceeds Expectations 20.0% Meets Expectations 15.0% Exceeds Expectations 5.0% Exceeds Expectations Vendor2 Exceeds Expectations Meets Expectations Does Not Meet Expectations Meets Some Expectations Exceeds Expectations Meets Some Expectations Meets Some Expectations Vendor3 Meets Expectations Meets Expectations Exceeds Expectations Exceeds Expectations Meets Expectations Meets Expectations Meets Expectations 35 36 37 38 39 40 Table 3 41 Rating Scale 42 43 Rating Category Does Not Meet Expectations Meets Some Expectations Meets Expectations Exceeds Expectations 44 45 46 A B D G H 47 Based on the data in Table 2 and Table3, convert the categorical values to Rating Scale values. 48 (c) Using VLOOKUP write a formula in cell D56 to lookup for the cell D32 value in Table 3 (using Exact match type). Copy the formula to cells D57:F62. 49 (d) Using SUMPRODUCT Function, write a formula in cell range D64 to compute the weighted score of Vendor 1. Copy the formula to cells E64 and F64 to calculate other vendor's weighted scores. 50 (e) Using MAX Function, write a formula in cell range D66 to compute the maximum weighted score. 51 (f) Using MATCH Function, write a formula in cell range D67 to compute the cell's column number having the maximum score. Hint: Match the value D66 in the array D64:F64 using exact match type. 52 (g) Using INDEX Function, write a formula in cell range D68 to display the vendor. Hint: INDEX the value of cell D67 in the array D55:F55. 53 54 Table 4 55 Assessment Criteria Weight Vendor 1 Vendor 2 Vendor 3 56 Ease of management 12.0% 57 Accessibility 10.0% 58 Scalability 8.0% 59 Cost Savings 30.0% 60 Security 20.0% 61 Customer Support 15.0% 62 Customizability 5.0% 63 64 Weighted Score 65 66 Maximum Score Column Number of Maximum Score (using