Explain how a Boxplot can be Used to Determine Whether the Associated Distribution of Values is Essentially Symmetric: Business Analytics, Applied Modelling and Prediction Assignment, UOL, Singapore

1. (a) Explain how a boxplot can be used to determine whether the associated distribution of values is essentially symmetric. Include a sketch of a symmetric boxplot in your answer, drawn in your answer booklet (no graph paper required).

(b) A shop faces normally distributed demands for its product each week. For the next four weeks, expected demands are 65, 80, 75 and 80, with variances of 64, 81, 49 and 36, respectively. Assume that all four weekly demands are probabilistically independent of each other. The shop initially has 320 units of the product in stock and will not be receiving any further deliveries from its supplier during the next four weeks.

An Excel analysis produced the following:

i. Explain what the value of 0.0936 returned by the function:

=1-NORMDIST(B8,F5,SQRT(F6),1) represents in cell B10.

ii. Explain what the value of 331.15 returned by the function:

=NORMINV(0.98,F5,SQRT(F6))

represents in cell B12.

iii. Explain how the function in cell B12 could be used by the manager of the shop.

(c) An airline knows, from previous experience, that 4% of ticket holders fail to show up for their flight. Suppose a plane has 300 seats and the airline wishes to sell more tickets than seats due to the occurrence of ‘no shows’. A flight is classified as overbooked if more passengers turn up for the flight than there are seats available. An Excel analysis produced the following.

i. Explain the use of the function:

=1-BINOMDIST(B4,B6,B3,1) used in cell B7.

ii. Explain what the data table is showing

2. A motorist is deciding whether to pay $170 for a one-year collision insurance policy with a $300 deductible on a $30,000 car. This means that in the event of an accident the motorist must pay for the first $300 of damage, with the insurer covering all damage costs in excess of the first $300.

The motorist believes there is a 5% probability of having an accident in the next year. Given an accident occurs, the cost of damage depends on the severity of the accident according to the following probability distribution:

(a) Draw the decision tree for this problem.

(b) Based on the decision tree constructed in part (a), should the motorist purchase the one-year collision insurance policy? Explain your answer.

(c) A sensitivity analysis of the expected annual cost to the deductible amount produced the following.

3. A financial analyst wanted to build a regression model to predict stock prices (in dollars) based on the following variables:

– the return on average equity in %, denoted X1

– the annual dividend rate in %, denoted X2

– the interaction between X1 and X2, denoted X3.

A multiple regression analysis produced the following results:

(a) Explain how the interaction variable would have been calculated using X1 and X2, as well as the purpose of including this interaction variable in this multiple regression.

(b) State the estimated regression equation and comment on R2

(c) Assess the overall significance of the model, as well as the significance of the individual regression coefficients.

(d) Calculate an approximate 95% prediction interval for a stock price when the return on average equity is 12% and the annual dividend rate is 3%, respectively. Use a t coefficient of 2.179.

(e) The fit from a regression analysis is often overly optimistic. One way to see if the regression was successful is to split the original data into two subsets: one subset for estimation and one subset for validation. Explain how these two subsets could be used to validate the fit.

4. (a) Define each of the following terms in the context of optimisation models:
i. binding constraint
ii. sensitivity analysis.

(b) Suppose you use Solver to find the optimal solution to a maximisation model.

Then you remember that you omitted an important constraint. After adding the constraint and running Solver again, is the optimal value of the objective guaranteed to decrease? Why or why not?

(c) A market research company needs to conduct a telephone survey with a minimum of 600 married females, 480 married males, 400 single males and 440 single females as respondents. Calls can be made in the daytime at a cost of $3 per call, or in the evening at a cost of $5 per call (labor costs are higher in the evening due to the unsociable hours) with these costs applying even when a call is not answered.

Due to evening calls being more expensive, a maximum of 40% of the calls can be evening calls. The market research company wants to minimize the total cost of conducting the survey.

An Excel spreadsheet model for the problem is shown below.

Cell range B4:B8 (on the previous page) gives the distribution of daytime calls answered, and cell range C4:C8 gives the distribution of evening calls answered.

Note ‘none’ refers to unanswered calls.

i. Formulate the market research company’s decision as a linear programming problem by constructing an algebraic model.

ii. Invoking Solver, the optimal numbers of calls during daytime and evening are shown in the cell range B14:C14 (on the previous page). Calculate the total cost of conducting the telephone survey.

iii. A one-way sensitivity analysis was run where the daytime call cost increases in increments of $0.50, from $1.00 to $6.00. The results of the sensitivity analysis are shown below.

Interpret the results of the sensitivity analysis, including whether the number of daytime calls could ever be zero for a sufficiently high level of daytime call cost.

5. (a) Explain when you might consider using each of the following probability distributions for input variables in a Monte Carlo simulation:

i. a discrete distribution

ii. a triangular distribution.

(b) A publisher of a textbook currently has 1,000 copies in its warehouse and it will produce a print run of 6,000 further copies for the coming year. A print run incurs a fixed cost of $8,000 and a variable cost of $15 per textbook. The selling price is $60 per textbook.

If consumer demand exceeds supply, then there will be a loss of goodwill valued at $10 per textbook. A new edition will be printed in the following year, so any unsold stock will have to be disposed of.

However, a discount retailer is willing to pay $20 per textbook for any unsold stock up to 1,500 textbooks.

An analyst built a Monte Carlo simulation model in which the uncertain input variable is textbook demand in the coming year. It is assumed that demand will be one of 3,000, 4,000, 6,000, 8,000 or 10,000 textbooks, with probabilities of 0.15, 0.20, 0.40, 0.20 and 0.05, respectively.

The following is the spreadsheet model which shows the results of five simulations in the cell range A13:G17.

i. For the first simulation result in row 13, explain how the profit figure of $292,000 (in cell G13) was calculated given the simulated demand of 10,000 in cell A13.

ii. Write down an appropriate Excel IF function for cell D15 which would return the value of $10,000. Your function should be of the form:

=IF(logical test, [value if true], [value if false]) where the arguments of the function should be in terms of cell references corresponding to the above spreadsheet model.

iii. The analyst ran one thousand simulations and the distribution of outcomes produced the following:

Write down (as a table) the probability distribution of the output variable (profit) and use this to calculate the expected profit.

iv. The results of the simulation are sensitive to the textbook demand distribution. If the publisher decided to advertise the textbook, explain how you would expect this to affect the input probability distribution.

v. Should the publisher definitely advertise the textbook as suggested in part iv? Explain why or why not.