Assignment: Introduction to Excel and Probability
Introduction:
In this exercise, you will use Microsoft Excel to evaluate a decision that has both quantitative and qualitative factors. This problem will provide you with the opportunity to practice your Excel skills, compute normal probabilities using Excel, and learn a useful decision analysis method. Variations of this method are frequently used by managers when a decision has a significant subjective component.
Factor Rating Model Explained:
The method that you will be using on this assignment is commonly called a factor rating model or, alternatively, a weighted scoring model. A factor rating model is a relatively simple tool that can be used to evaluate qualitative factors or a mix of qualitative and quantitative factors, which management has decided are critical to the decision. This method is usually used for group decision making because the preliminary weighting of factors and the actual evaluation of individual alternatives is likely to be less biased if a group works together to assign the values.
Briefly, here is how a factor rating model works:
1. Identify the factors on which you plan to base your decision.
2. Assign each factor a weight based on the priority that you would like to give to the factor. Note that some textbooks will require the sum of the weights to equal 1 and others will state that the weights should all fall in a given range like 1-10 or 1-100, for example.
3. Identify your decision alternatives.
4. For each alternative, evaluate the alternative based on the criteria and assign it a rating, which should also fall in a range like 1-10 or 1-100.
5. To compute the overall rating for each alternative, multiply each rating by its associated weight. Then take the sum of these products. In Excel, this is very easily accomplished with the SUMPRODUCT() function.
6. Select the alternative with the best overall rating.
Note: To see a written example of how this works, see the factor rating example that appears below this assignment on the course website.
Problem Scenario: You are the security executive at a defense contractor that manufactures classified communications equipment. Your boss has asked your team to evaluate several access control systems for the company's 20 facilities to upgrade an 18 year-old system that is obsolete. From your experience, you know that there is a wide range of solutions available.
These solutions vary significantly in cost and level of risk mitigation. A comprehensive solution will cost a total of well over $5 million for the 20 facilities. More importantly, the solution will impact your ability to secure the sensitive technology that you protect from competing companies and foreign governments.
Your team has identified the following factors for evaluating the systems: Cost, Risk Mitigation, Ease of Use, Scalability, Computer Interface, System Reliability, and Vendor Reputation (important for demonstrating to the DOD that you are doing your due diligence to protect your technology). After extensive research, your team has assigned weights to each factor, they have identified a set of decision alternatives, and they have rated the qualitative factors for each alternative. The work they have completed thus far is shown in the Data tables. As you can see, the job is not done. It is up to you to evaluate the quantitative factors (cost and risk probabilities), create the factor rating models, compute the overall ratings, and then make a decision.
Alternatives:
A1. Current System (do nothing): This system is a combination of keys and badges worn on a lanyard around the employee's neck. Badges are visually evaluated by a security guard at each entrance and entrance/exit is logged into a computer application.
A2. Access Control Cards: This system is similar to the current system but it replaces the keys with access cards, which can be used to restrict access given an employee's current status.
Badges visually evaluated by security guards are still required in this alternative. All system transactions are tracked by computer software.
A3. Fingerprint Scanner: In this alternative, a security guard is placed at each entrance and badges are still visually evaluated, but the identity and access privileges are evaluated by a fingerprint/biometric scanner. All system transactions are tracked by computer software.
A4. Retina Scanner: In this alternative, the security guard detail is minimized (but not eliminated) and badges are still worn, but access is completely controlled by retina scan. All system transactions are tracked by computer software.
Questions:
1. Using the data below, finish building the factor rating model and compute the overall rating for each of the four alternatives. Note: find the factor rating associated with the computed cost in the Cost Factor Equivalence table at the end of this document.
2. Based on your analysis, which is the best decision? Briefly justify your answer.
3. Discuss how bias could undermine the efficacy of this approach? If you were using this approach at work, what would you do to limit the impact of bias on your decision?
Attachment:- Assignment.rar