Module 1 – Background
Assumed Certainty: Pivot Tables and Multi-Attribute Decision Making
Case Background: Data Analysis and Pivot Tables
Pivot Tables. One of the most useful tools in Excel that you can use for data analysis is the Pivot Table also called the Pivot Table Report. This is an interactive way to quickly summarize large amounts of data. You can query large amounts of data, do subtotaling, expand and collapse levels of data to focus your results, move rows to columns and vice versa (pivoting), and filter and sort the data.
Source Data. In order to create a Pivot Table, you must first have a source of data organized in List format. It is easy to create an Excel table in list format with column labels in the first row. Each cell in subsequent rows should contain data appropriate to its column heading. There should not be any blank rows or columns within the data of interest. Excel uses your column labels for the field names in the report.
View a Pivot Table Report. Before you get into creating your own Pivot Table, use this link and download this Excel file that has a Pivot Table using an Excel table in list format. Pivot Table-Example.xlsx
Watch this video that shows you how to do it:
If the video above does not work, please click this link: https://permalink.fliqz.com/aspx/permalink.aspx?at=d4fd059eda8a46a2b90041deffd6c061&a=5fae3cf0f1624f39b0341263a6541ea0
PRACTICE: Now create your own Pivot Table using this Excel file that has the data in an organized manner.
Download this Excel file: Pivot Table-Practice Data.xlsx
Organize the data into a data table in list format.
Create the Pivot Table (and the Pivot Chart).
Go to Quiz Tab and answer the questions.
Check your Pivot Table and answers by looking at the Solution Tab in the spreadsheet.
You should be ready to do the Case 1 Assignment.
SLP BACKGROUND: DECISION MAKING using Multi-Attribute Decision Making (MADM)
For a general understanding of decision making and the process, watch this video:
If the video above does not work, please click the following link: https://permalink.fliqz.com/aspx/permalink.aspx?at=d55a346d20aa466d84ffd99b15f7d128&a=5fae3cf0f1624f39b0341263a6541ea0
We will begin to cover the finer elements of decision making. There are different ways to categorize decisions. The most common and useful way is based on the amount of uncertainty in the decision situation or the future. And there are three different levels of uncertainty: assumed certainty, risk, and uncertainty.
Assumed Certainty. The decision maker assumes that the future is known with certainty. In other words, there is only one possible future and the data and information about this future is known or knowable. There may be few or many options to choose from and relevant data about these options is obtained. In some cases, the data is estimated, but always with assumed certainty. Examples of this situation are: purchasing an item, such as a vehicle, house, copy machine, production machine, etc. OR deciding where to go on vacation, or where to hold the annual convention.
Risk. In this situation, the decision maker distinguishes several possible future states, and is able to determine the probabilities of these distinct futures, or estimate the probabilities with a degree of confidence. There may be few of many options to choose from and the outcomes of these options may be different in the possible future states. For example, consider the weather which is always risky. And we usually have some estimates of the future states based on what the weatherman says. Two possible states are Rain and No Rain. The choices to consider here might be: Walk w/no umbrella, Walk w/umbrella, or Drive. The decision maker can determine the probabilities of Rain/No Rain from the forecast, for example, 60% chance of Rain (and 40% No Rain.) There are costs and payoffs involved with each option. Walking is enjoyable, but you could get wet, not enjoyable. Driving costs money for gas. As the decision maker, you would look at the costs and payoffs of each option under each possible future and use a decision rule to decide your best option.
Uncertainty. In this type of situation, the scenario is much like that of Risk, but the decision maker does not have any idea of the probabilities of the future. He must use some form of decision rule to determine what is the best option without knowing how likely each possible future might be.
Bounded Rationality. Note that in each of the types of decisions, the decision maker must consider the future (after all, that is what a decision is all about, the future), and also consider what options are available. Novice decision makers may not see all of the possible futures nor all of the possible options. Experience provides skill in determining each of these. But even skilled decision makers are constrained by Bounded Rationality, as was discussed in the Home page of this module. The difference is that expert decision makers understand bounded rationality and are still able to discern the possible futures and options more quickly and easily than are novices and thereby achieve a more informed decision.
Utility. Many options are evaluated based on money, either Income or Costs, net Income less costs. But sometimes we need to use other ways to evaluate options, especially if there are factors that are measured differently. For example, if you were choosing a car to buy, the cost of the car is only one factor. You might consider gas mileage (MPG), color, safety rating, number of passengers, etc. All of these are measured differently. In order to compare options, these factors must be converted to a common metric for comparison. The metric that is used in this situation is Utility. This metric is a number between 0 and 1, where 0 is no utility and 1 is the maximum utility. The decision maker must convert (sometimes subjectively) each factors value to a Utility score. For example, in choosing between a red car and blue car, the decision maker may assign Red a utility of 1 and Blue a Utility of 0, and perhaps a Silver one a Utility of 0.5. The red color is most desirable, the silver color is half as desirable, and the blue one is not desired at all. But there are other factors to consider as well. And then all factors are aggregated into one final overall Utility score for each option.
Module 1 focuses on decisions under assumed certainty. The case assignment is about how to analyze data in a situation in which you can assume certainty. And the SLP assignment is about making a decision under assumed certainty uses the multi-attribute decision matrix (MADM). This method is also known by other names, i.e. MAU (multi-attribute utility), MADA (multi-attribute decision analysis).
Readings and Videos
Read this resource: MADM.docx
If the video above does not work, please click the following link: https://permalink.fliqz.com/aspx/permalink.aspx?at=2cc5262c22b543ec928bec51be6c23f6&a=5fae3cf0f1624f39b0341263a6541ea0
Download this Excel file with the Car Decision Example. SLP 1-Car Example.xlsx
PRACTICE EXERCISE: Now that you have seen how to develop a Decision table, try this Practice Exercise. Then Check your solution in this Excel file. SLP 1-Practice.xlsx
Practice Exercise Scenario: Hiring a key person:
You are hiring a person for a top position in your company. You have narrowed the field down to the top four candidates. You want to use multi-attribute decision analysis. You have determined that there are four decision criteria that are most important.
Four Attributes (criteria): Salary, experience, education, leadership personality.
The salary number is the amount that the candidate said he/she needed to accept the job.
Experience is based on number of years of direct experience, plus an add-on for other related experience that is equal to about half of the years.
Education is the level of degree plus any other training or certifications. You have decided to use a scale of 1 to 5 to evaluate. 1=bachelor, 3=master, 4=PhD or other doctoral degree. Add-ons for certifications, i.e. CPA, Certified Coach, etc. and for second degrees can be applied from .5 points, or 1 point. The max score cannot exceed 5 points.
Leadership Personality is based on your subjective evaluation including the opinion of your Supervisor who will be working with this person. This score is also a rating scale of 1 to 5.
1 = probably needs a lot of effort to be a leader, and 5 = probably will perform at top leadership capability.
Here is the information on the four top candidates:
Bob:
Salary: $75,000, Experience: 22 years direct, 8 other related, Education: Bachelors plus certified coach and certified leadership graduate; Leadership personality: 4
Sam:
Salary: $68,000, Experience: 18 years direct, 10 other related, Education: MBA; Leadership personality: 3.5
Mary:
Salary: $69,000, Experience: 15 years direct, 4 other related, Education: Masters, plus certified HR Professional; Leadership personality: 4.3
Lisa:
Salary: $62,000 Experience: 5 years direct, 6 other related, Education: Doctor of Business Administration (DBA); Leadership personality: 3.7
Create a multi-attribute decision analysis using Excel. What are the weights that you would assign to the four criteria? How do you convert the raw data into utility values? Which criteria are “Less is better” and which are “more is better”?
When you have worked through this example, download this Excel file and check your work: SLP 1-Practice.xlsx
Optional Reading for SLP 1 Assignment
Spaeder, K. (n.d.). How to Find the Best Location, retrieved from https://www.entrepreneur.com/article/73784#
For additional examples of MADM Utility Analysis, please review Dr. David Holcomb’s presentation.
Adobe
PowerPoint
Additional Required Reading
What is bounded rationality? How does it affect decision making? Read this article that discusses the foundational theory expounded by Herbert Simon in 1956.
Ibrahim, M. (2009). Theory of bounded rationality. PM. Public Management, 91(5), 3-5. Retrieved from ProQuest.
Reading for Module 3 to Read Now
Start reading about Forecasting demand, which will be the subject of Cases 3–5. Starting now will allow you to get familiar with the background information.
Read: Chapter 1 (all) and Chapter 2 (pp. 31–53), from: Chase, Charles W., (2013). Demand-Driven Forecasting: A Structured Approach to Forecasting, John Wiley & Sons: Somerset, NJ. Retrieved from Ebrary.
Module 1 – Case
Writer: Please write four pages only on this assignment, also this will be the first assignment, so write this different than assignment below with reference
Assumed Certainty: Pivot Tables and Multi-Attribute Decision Making
Assignment Overview
You are working as a data analyst for the Excellent Consulting Group. Your client, Buddy’s Floor Barn, wants to analyze how they are doing in their 18 locations in four regions across their five product lines over the last 3 quarters. Each Regional Manager has compiled the data for his/her region showing the sales revenue for each of the five product lines (premium flooring) for each of the first three quarters.
Case Assignment
Assemble the data from each Store Manager into a data table in list format. And then generate Pivot Table Report. Use this report to analyze the data to answer Buddy’s questions. Write a business report to Buddy’s Floor Barn that provides the answers and discusses the issues.
Data: Download Case 1-Data.xlsx with the data from the four regional managers for the first three quarters showing sales of their five premium flooring products.
Assignment Expectations
Analysis:
Accurate and complete analysis in Excel.
Written report:
Length requirements = 4–5 pages minimum (not including Cover and Reference pages)
Provide a brief introduction/ background of the problem.
Complete and accurate Excel analysis.
Written analysis that supports Excel analysis, and provides thorough discussion and analysis of assumptions, rationale, and logic used.
Complete, meaningful, and accurate recommendation(s).
Module 1 – SLP
Writer: Please write two pages only on this assignment, also this will be the second assignment, so write this different than assignment above with reference
Writer: I will attached the data sheet with this assignment
Assumed Certainty: Pivot Tables and Multi-Attribute Decision Making
Scenario: You are the VP of Franchise services for the Happy Buns Restaurant. You have been assigned the task of evaluation the best location for the next HB that a prospective franchisee has suggested in the Columbus, Ohio, area. You are using the standard template that provides for which criteria (attributes) you should evaluate. But the specific weights for these are open to adjustment depending on the specific area. These are the six criteria that you will use to evaluate this decision.
Close to drive through traffic – traffic counts (avg. thousands/day)
Property cost/investment and taxes = NPV of investment ($$)
Size of building (square feet in thousands)
Size of parking (max number of customers parking)
Insurance costs (thousands $ per year)
Ease of access from streets (subjective evaluation from observation)
There are five possible locations. You have collected the data from various sources including your VP Finance, Real estate agents, etc. This document summarizes the raw data for each of the five locations: Abberton, Bellview, Casstown, Denton, and Eddington, all suburbs of Columbus. See Data Below.
Assignment
Review the information and data regarding the different alternatives for restaurant location. Develop a MADM table with the raw data. Convert the raw data to utilities (scaled on 0 to 1). Determine the relative weights of each criteria. Evaluate the Decision Table for the best alternative. Do a sensitivity analysis.
Write a report to your boss, Executive VP. Explain your analysis and your recommendation. Provide a rationale for your decision including the logic you used to determine your weights.
Data
Download this Word doc with the data: Happy Buns Raw Data.docx
Upload both your written report and Excel file to the SLP 1 Dropbox.
SLP Assignment Expectations
Analysis
Accurate, complete analysis (in Excel and Word) using the MADM model and theory.
Written Report
Length requirements = 2–3 pages minimum (not including Cover and Reference pages)
Provide a brief introduction/ background of the problem.
Complete and accurate Excel analysis.
Written analysis that supports Excel analysis, and provides thorough discussion of assumptions, rationale, and logic used.
Complete, meaningful, and accurate recommendation(s).
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!