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 ABCs 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 students predicted GPA?