Part 1 – In-depth decision analysis

Note: This is very similar to what you did in Part 2 of Assignment 2 but please read carefully as there are some minor changes.

Monte waited patiently for the board of directors of AgroPest International to absorb his presentation. The company, which produced agricultural fertilisers and pesticides, had a new CEO who values evidence-based decision making. The board of directors were used to presentations that relied on instinct and theory. They were not used to what they just saw, which was a decision tree with probabilities, expected values, and return to risk ratios. They just wanted to hear how staying with the old pesticide was the best idea because that’s what they were comfortable with. But Monte had just shown that the best decision based on evidence would be to go with the new pesticide. He knew there were concerns with the new product being delayed for release because it was still in development. But the old product might get banned due to health concerns! Shouldn’t that be reason enough? He shook his head and leaned back to wait for the comments to begin.

Figure 1: Decision tree Monte presented to the board.

“Well, I’ll start,” said John, one of the older board members. “This new product mumbo jumbo doesn’t make sense to me. But I vehemently disagree with your numbers for the current product. Yes, there are health concerns, but they are being blown out of proportion by those hippies. It’s way too high at 30%. It needs to go down.” He looked at a sheet in front of him. “Yes, that’s more reasonable: a 20% chance of an out-and-out ban, all other assumptions saying the same.”

“Yeah, and even if there were a ban, the current product still has value. We can sell it in other countries that have more favourable ‘regulations’. Even with a ban, it must be worth $300,000 at least,” added Pete, a close friend of John’s. “With a banned value of $300,000 plus John’s belief in the smaller chance of a ban, surely we’re better off with the old product.”.

Marla cleared her throat, “You both have a better handle on the old product, but as I’ve been working on the new product, I’d like us to be more conservative with our estimates. “I can live with Pete’s valuation of the old product, if banned, at $300,000. I can also live with John’s belief that there is only a 20% probability of the old product being banned. But the value of the new product based on different sales conditions seems a bit high. They should all be reduced by $100,000 just to play it safe. Hmmm … I also think the probability of high sales is very optimistic. We should lower it to 50% regardless of whether there is a delay or not. I would rather we err on the side of caution.”

Steve piped up, “I agree with everything said so far, except on the overly optimistic chance of a delay for the new product. I think your probabilities about a delay are backwards, Monte. Let’s be realistic. With a new product, we know that a delay is likely. It should be a 60% chance of a delay. So, let’s go with John’s 20% probability of a ban; Steve’s valuation of the old product being worth $300, 000, if banned; and Marla’s 50% probability of high sales, regardless of whether there is a delay. But let’s change the probability of a new product delay to 60%.

Monte made notes on what was said. He ran different analyses based on each individual suggestion and a combination of suggestions. After a few moments, he let out a sigh of relief, “If we make all of your changes, the evidence still looks like it favours going with the new product. Let me explain.”

(This case is taken and modified from pp. 228-9 of Clemen, R. T., & Reilly, T. (2014). Making hard decisions (3rd edition). Cengage Learning.)

IMPORTANT NOTE: You should notice that each member builds on the previous member’s input. That is, each of the board member’s input adds a new change to the scenario but does NOT affect any previous board member’s input.

STEPS FOR PART I

ONE: Using the decision tree in Figure 1, create a payoff and probability table representing Monte’s initial conclusions (Tab 1). NOTE: Be sure to use correct units (e.g. dollars, probabilities, percentages, where appropriate)

As with Assignment Two, the conditions are different depending on the decision.

  • Stop: What are the TWO decisions?
  • Stop: For each decision, what are the associated conditions? Hint: One decision has two conditions. The other has four conditions.

Table 1 – Payoff table

Conditions for Decision 1Decision 1Conditions for Decision 2Decision 2
 (payoff) (payoff)
 (payoff) (payoff)
   (payoff)
   (payoff)

Table 2 – Probability table

Conditions for Decision 1Probabilities for Decision 1Conditions for Decision 2Probabilities for Decision 2
 (probability) (probability)
 (probability) (probability)
  (probability)
   (probability)

Hint: The probabilities for Decision 1 can be read straight off the tree. But the probabilities for Decision 2 need to be calculated. Look at p. 165 of the textbook (Multiplication rules) for help on how to do this.

   

In Excel, leave the calculations in place for the probabilities for Decision 2. For example, leave them as “=0.5*0.5” instead of  “0.25” so that your instructor can click on the cell and see how you arrived at your answer.

   

TWO: Complete the decision table representing Monte’s initial decision (Tab 2). Engage in decision making by finding the optimal decision using each of the following decision methods. Put work in the tab entitled “Monte’s analysis” in Excel.

  • Maximax
  • Maximin
  • Expected monetary value
  • Coefficient of variation
  • Return to risk ratio

THREE: Complete the decision tables representing each of the board member’s suggestions (Tab 3)

FOUR: Create an automated interactive spreadsheet that allows the board members to put in their own values to explore the decision analysis (Tab 4). There is a template on Tab 4 and some brief instructions for how to proceed.

NOTE: For all of the Excel work that you are asked to do. Do NOT remove formulas. For example, leave your EMV cell as something like “=A2*B2+A3*B3” instead of typing in the final answer. You will be marked on your answers and your work. The work is what is written in the cells.

FIVE: Use your interactive spreadsheet in Tab 4 to run at least four additional what-if analyses. Is the optimal decision always the new product? If not, what changes make the old product better? Are those changes realistic? These questions will be discussed in your report in the next step.

SIX: Write a one- to two-page memo report to the Board.  Organise the report as follows:

  1. Descriptive title: Write a succinct title that lets the reader know exactly what the report is about.
  2. Recommendations: State which product you recommend and list your reasons why. (Two or three sentences)
  3. Discussion: Elaborate on your reasons: Choose two or three high level reasons for why the product you have recommended is the best choice. Also, briefly discuss which changes to the what-if scenarios, if any, would change your recommendation.
  4. Evidence: Insert a table that contains the results of at least four different what-if scenarios from Step 5. The table will provide specific values. Be sure to title your table: “Table 1: [clear title that indicates what it is about]”. Put the title above the table. Include headings on your table, and organise it so it is easy to read.
  5. Limitations (caution): Briefly discuss any limitations you see to your conclusions.

Part 2 – Model building in linear regression

Problem

A real estate firm is interested in building a model that will allow it to more efficiently assess the value of apartment buildings. To this end, they have gathered a random sample of 32 apartment buildings that have sold in Calgary over the past 3 years. As a starting point, they will work only with numerical data (i.e. location and other important factors will not be included in the initial model). The four initial predictor variables they will use to assess the value of apartments are square feet per floor, number of tenants (capacity), age of the building, and monthly revenues.

Business goal

We are required to find the best model for predicting the value of apartment buildings in Calgary.

STEPS FOR PART II

ONE: Investigate the independent variables. Are they potentially good predictors of the dependent variable? Explain your answer. If a variable is not a good predictor, eliminate it from the list of candidates.

  • In an Excel spreadsheet, use the tab called “Potential good predictors”. Put all your work for this step in that tab.
  • In the Word document,
    • copy and paste the results of the work you did. For example, if you calculated a confidence interval, do not include the steps to get the confidence interval but only include the final confidence interval.
    • Then write a short summary (two to three sentences) that explains what you found out and how you know it.

TWO: Does collinearity exist in the model? If so, which independent variable is problematic? How do you know this? If there is a problem with collinearity, remove the offending variable and rerun the model. Does collinearity still exist in the model? If so, which independent variable is problematic? How do you know this? Repeat until collinearity is no longer a problem.

  • In an Excel spreadsheet, use the tab called “Collinearity”. Put all your work for this step in that tab. 
  • In a Word document,
    • copy and paste the results of the work you did. For example, if you calculated a confidence interval, do not include the steps to get the confidence interval but only include the final confidence interval.
    • Then write a short summary (two to three sentences) that explains what you found out and how you know it.

THREE: Using the variables that have minimal collinearity and are good predictors of the dependent variable (i.e., the results of your work from steps ONE and TWO, build ALL possible models. Make a table of the relevant information for each model that you would use to determine if it is the best model.

  • In an Excel spreadsheet, make as many tabs as needed to display all possible models. Name each tab based on the dependent variables used in that specific model. Put the work you did to build that model in that tab.

FOUR:

  • Create a table that summarizes the key measures from each of your models and put it in its own tab called “Summary Table”
  • Copy and paste this table into the Word document

FIVE: Now that you have the best model, provide a detailed summary of the model that helps management understand the business situation predicted by the model and the accuracy of the model. The goal here is to demonstrate that you can effectively communicate the results of an analysis by explaining:

1) as each independent variable changes, how does the dependent variable change?

2) how good is the model? (consider both accuracy and appropriateness)

3) how the model can be used within the business context. When discussing accuracy, include a prediction of the dependent variable with a discussion of its accuracy.

What do I submit?

  1. Completed Excel spreadsheet for Part 1 that shows Steps 1, 2, 3, 4, 5, which includes the following tabs, in this order:
    1. Payoff and probability tables (Tab 1)
    2. Monte’s analysis (Tab 2)
    3. John’s suggestion, Pete’s suggestion, Marla’s suggestion, Steve’s suggestions (Tab 3)
    4. Interactive spreadsheet (Tab 4)
  1. Completed Excel spreadsheet for Part 2 that shows Steps 1, 2, 3, which includes the following tabs, in this order:
    1. Original data (nothing to do here, but make sure the dataset you are using is included)
    2. Potential good predictors
    3. Collinearity
    4. Multiple tabs that show the model building for Step 3.
  1. Word document: See Final Exam Submission.docx for a template of what this needs to look like.
    1. Report from Step 6 of Part 1
    2. Results and summary for Step 1 of Part 2
    3. Results and summary for Step 2 of Part 2
    4. Table for Step 4 of Part 2
    5. Detailed summary of best model for Step 5 of Part 2
    6. List of “Outside sources”.

All papers are written by ENL (US, UK, AUSTRALIA) writers with vast experience in the field. We perform a quality assessment on all orders before submitting them.

Do you have an urgent order?  We have more than enough writers who will ensure that your order is delivered on time. 

We provide plagiarism reports for all our custom written papers. All papers are written from scratch.

24/7 Customer Support

Contact us anytime, any day, via any means if you need any help. You can use the Live Chat, email, or our provided phone number anytime.

We will not disclose the nature of our services or any information you provide to a third party.

Assignment Help Services
Money-Back Guarantee

Get your money back if your paper is not delivered on time or if your instructions are not followed.

We Guarantee the Best Grades
Assignment Help Services