Need both excel and word file. we need to paste formula view and normal view. reference in APA formatting
Assessment item 3
back to top
Assignment 3
Value: 20%
Due Date: 14-May-2019
Return Date: 06-Jun-2019
Submission method options: Alternative submission method
Task
back to top
QUESTION 1 Decision Analysis
Student guide to marks: 20 – 4 for a, 2 for b and 14 for c (2 for each of the 7)
Show all calculations to support your answers.
(a) Describe the advantage in using a payoff matrix to analyse decisions. Explain the steps required in developing such a matrix.
(b) What advantage do decision trees provide and in what situations are they preferred to a payoff matrix?
(c) George Goleb is considering the purchase of two types of industrial robots. The ROB1 is a large robot capable of performing a variety of tasks, including welding and painting. The ROB1 is a smaller and slower robot, but it has all the capabilities of ROB1. The robots will be used to perform a variety of repair operations on large industrial equipment. Of course, George can always do nothing and not buy any robots.
The market for the repair operation could be either favourable or unfavourable. If the market is favourable ROB1 is expected to return $50,000 profit and ROB2 $30,000 profit. If the market is unfavourable ROB1 is expected to lead to a loss of $40,000 and ROB2 to a loss of $20,000.
1. Construct a payoff matrix showing the 3 possible alternatives and the associated profits or losses under the 2 market conditions.
2. Showing all calculations, what is the optimum action and its expected payoff if George is an optimist?
3. Showing all calculations, what is the optimum action and its expected payoff if George is a pessimist?
4. Showing all calculations, what is the optimum action and its expected payoff if George follows the Laplace criterion?
5. Showing all calculations, what is the optimum action and its expected payoff if George uses the criterion of regret?
6. If George believes that the probability of a favourable market is 0.6, showing all calculations determine the optimum action and expected return.
7. What is the expected value of perfect information?
QUESTION 2 Value of information
Student guide to marks: 20 – 10 for a, 2 for b, 6 for c, 2 for d
Show all calculations to support your answers. You may follow the methods shown in the mp4 on Value of Info for a way to answer this question if you wish.
Round all probability calculations to 2 decimal places.
Referring to Q1c, Recall the optimum action based on expected values was to purchase ROB1 with an EMV of $14,000.
George Goleb is considering the possibility of conducting a survey on the market potential for industrial equipment repair using robots. The cost of the survey is $5,000.
The market survey results can be classified as positive or negative. Given past experience with the market survey personnel, the conditional probabilities are p(positive signal|favourable market = 0.9 and p(negative signal|unfavourable market) = 0.8.
a) Revise the prior probabilities of 0.6 and 0.4 in light of these likely survey results.
b) What is the posterior probability of a favourable market given a positive survey result?
c) Calculate the EVSI and ENGSI.
d) What is the maximum the firm should pay for the market survey?
QUESTION 3 Simulation
Student guide to marks: 20 – 10 for a, 5 for b, 5 for c
This is a work integrated assessment item. The tasks are similar to what would be carried out in the workplace.
You have just been hired as an analyst to assist the manager of ABC airlines. Your first assignment is to examine and report on the profitability and policies of the airline.
ABC Airlines flies a six-passenger commuter flight once a day to Myrtle Beach. A non-refundable one-way fare with a reservation costs $79.
The daily demand and probability distribution for this flight are shown below along with the probability distribution of no-shows. A no-show has a reservation but does not arrive at the gate and forfeits the fare.

If there are not enough seats for a passenger at the gate ABC Airlines refunds his or her fare and also provides $50 compensation. The fixed cost for each flight is $350, regardless of the number of passengers.
1. Set up a simulation model and calculate ABC’s profit each day for a month (30 days) and the average profit per flight. There are to be no numbers in the model (only in the data section), and only formulas in the model. Print in WORD the output and then the formulas.
Below is a partial template to help you:

2. ABC would like to investigate the profitability of varying the fare and the compensation for overbooking. What is your recommendation?
3. Write report with your comments on the profitability of the airline and any recommendations you may have for improvement. (no more than 300 words but you may also include any variations in your spreadsheet to demonstrate your points). Address your report to the manager and sign off.
QUESTION 4 Regression Analysis
Student guide to marks: 20 – 12 for a, 4 for b and 4 for c
Pike College is a small business school that offers an MBA program. The main entrance criterion for admission to the MBA program is the Graduate Management Admission Test (GMAT) score (maximum score =800). The following table provides the GPAs (Grade Point Averages) of 12 students who have graduated recently along with their GMAT scores and ages.
Student GPA GMAT AGE
1 3.7 660 34
2 3 580 29
3 3.25 450 24
4 4 710 39
5 3.52 550 30
6 2.83 430 27
7 3.8 540 35
8 4 590 42
9 3.65 720 24
10 3.47 480 30
11 3.33 520 27
12 3.75 670 28

1. Using Excel, perform three regression analyses to regress GPA against GMAT score, then against Age, then against both of them simultaneously. Paste your results into Word. State the cost equation from each. Analyse and comment on the results of each regression as you perform it and determine the best one to use as a basis for future use.
2. If you had to settle for the results of a simple regression, which one would you use and why? Explain any reservations you might have with your selection.
3. A new graduate student has a GMAT score of 600 and is 29 years old. Using the multiple regression output what is this student’s predicted GPA?
QUESTION 5 CVP Analysis
Guide to student marks: 20: 6 for a, 4 for b (2+2), 4 for c, 6 for d
1. Product A sells for $12 per unit. The variable cost is $6 per unit. Fixed costs are $1,200. What is the breakeven point in (i) units, and (ii) dollars?
2. How many units are required to earn a target level of profit before tax of $600? What is the margin of safety?
3. What profit would be earned by selling 250 units?
4. A second product B is added, selling for $20 per unit with a variable cost of $8. Total fixed costs for both products are $5,200. A and B are sold in the ratio of 2 of A to 1 of B. Given a tax rate of 30c in the dollar, how many units of each product are required to earn a profit after tax of $1,400?