T2187编程代写、代写Python，Java程序

日期：2022-10-28 09:52

T2187 Business analytics, applied modelling and prediction

Important note

This commentary reflects the examination and assessment arrangements for this course in the

academic year 2019–20. The format and structure of the examination may change in future years,

and any such changes will be publicised on the virtual learning environment (VLE).

Information about the subject guide and the Essential reading

references

Unless otherwise stated, all cross-references will be to the latest version of the course (2019). You

should always attempt to use the most recent edition of any Essential reading textbook, even if the

commentary and/or online reading list and/or subject guide refer to an earlier edition. If different

editions of Essential reading are listed, please check the VLE for reading supplements – if none are

available, please use the contents list and index of the new edition to find the relevant section.

General remarks

Learning outcomes

At the end of the course and having completed the essential reading and activities you should be

able to:

apply modelling at varying levels to aid decision-making

understand basic principles of how to analyse complex multivariate datasets with the aim of

extracting the important message contained within the large amount of data which is often

available

demonstrate the wide applicability of mathematical models while, at the same time,

identifying their limitations and possible misuse.

Format of the examination

The examination is two hours long and you must answer four questions out of five. The examination

is worth 70% of the final grade. The other 30% is determined by the coursework component. (The

coursework comprised a freestyle data visualisation project using Tableau, requiring the design of a

five-dashboard story and accompanying report – see the ‘Assessment’ section in the VLE for details.)

Overall performance

The performance of candidates in the examination was very pleasing, with many excellent answers.

Use of Excel is not directly examined, rather some questions required the interpretation of Excel

1

ST2187 Business analytics, applied modelling and prediction

output and/or formulae. Some answers lacked sufficient depth of explanation – remember to

comment in detail on each argument of a function.

Although this is an applied statistics course, candidates are reminded that commercial insight is also

important. Always think about which business decisions could be taken as a consequence of the

analysis, justifying the decision(s) based on the results – the course is about business analytics after

all!

Examination revision strategy

Many candidates are disappointed to find that their examination performance is poorer than they

expected. This may be due to a number of reasons, but one particular failing is ‘question

spotting’, that is, confining your examination preparation to a few questions and/or topics which

have come up in past papers for the course. This can have serious consequences.

We recognise that candidates might not cover all topics in the syllabus in the same depth, but you

need to be aware that examiners are free to set questions on any aspect of the syllabus. This

means that you need to study enough of the syllabus to enable you to answer the required number of

examination questions.

The syllabus can be found in the Course information sheet available on the VLE. You should read

the syllabus carefully and ensure that you cover sufficient material in preparation for the

examination. Examiners will vary the topics and questions from year to year and may well set

questions that have not appeared in past papers. Examination papers may legitimately include

questions on any topic in the syllabus. So, although past papers can be helpful during your revision,

you cannot assume that topics or specific questions that have come up in past examinations will

occur again.

If you rely on a question-spotting strategy, it is likely you will find yourself in difficulties

when you sit the examination. We strongly advise you not to adopt this strategy.

2

Examiners’ commentaries 2020

Examiners’ commentaries 2020

ST2187 Business analytics, applied modelling and prediction

Important note

This commentary reflects the examination and assessment arrangements for this course in the

academic year 2019–20. The format and structure of the examination may change in future years,

and any such changes will be publicised on the virtual learning environment (VLE).

Information about the subject guide and the Essential reading

references

Unless otherwise stated, all cross-references will be to the latest version of the course (2019). You

should always attempt to use the most recent edition of any Essential reading textbook, even if the

commentary and/or online reading list and/or subject guide refer to an earlier edition. If different

editions of Essential reading are listed, please check the VLE for reading supplements – if none are

available, please use the contents list and index of the new edition to find the relevant section.

Comments on specific questions

Candidates should answer FOUR of the following FIVE questions. All questions carry equal marks.

Question 1

(a) Consider a modified version of the Monty Hall problem. In this version, there

are 8 boxes, of which 1 box contains the prize and the other 7 boxes are empty.

You select one box at first. Monty, who knows where the prize is, then opens 6

of the remaining 7 boxes, all of which are shown to be empty. If Monty has a

choice of which boxes to open (i.e. if the prize is in the box you chose at first),

he will choose at random which one of the boxes to leave unopened.

i. Suppose that you have chosen Box 1, and then Monty opens Boxes 3 to 8,

leaving Box 2 unopened. After we have observed this, what is the probability

that the prize is in Box 1, and what is the probability that it is in Box 2?

(10 marks)

ii. How should a risk-neutral decision-maker use the probabilities computed in i.

to inform their strategy?

(3 marks)

Reading for this question

Block 1 on the VLE covers the Monty Hall problem as part of decision-making under

uncertainty.

3

ST2187 Business analytics, applied modelling and prediction

Approaching the question

i. Let Bj denote the event that the prize is in Box j, for j = 1, 2, . . . , 8, and let N2 denote

the event that Monty does not open Box 2. Bayes’ theorem tells us that the conditional

probability that the prize is in Box 1 is:

P (B1 |N2) = P (N2 |B1)P (B1)8∑

j=1

P (N2 |Bj)P (Bj)

=

P (N2 |B1)P (B1)

P (N2 |B1)P (B1) + P (N2 |B2)P (B2) +

8∑

j=3

P (N2 |Bj)P (Bj)

.

According to the rules of the game, the probabilities to use here are:

? P (B1) = P (B2) = · · · = P (B8) = 1/8, since all the boxes are initially equally likely to

contain the prize.

? P (N2 |B1) = 1/7. If the prize is in Box 1, Monty has a choice, so he chooses at

random which of the remaining boxes to leave unopened.

? P (N2 |B2) = 1. If the prize is in Box 2, Monty must leave it unopened.

? P (N2 |Bj) = 0 for j = 3, 4, . . . , 8. If the prize is in either Box 3 to 8, Monty cannot

open that box. This means that he must open all the other boxes not chosen by you,

including Box 2.

Therefore, the conditional probabilities are:

P (B1 |N2) = 1/7× 1/8

1/7× 1/8 + 1× 1/8 + 6× (0× 1/8) =

1

8

and P (B2 |N2) = 7/8. Hence you have an 87.5% chance of winning the prize if you

switch to Box 2.

ii. While neither conditional probability computed in i. offers certainty, a risk-neutral

decision-maker would ‘play to the probabililties’ and choose the strategy to always switch

to the unopened box, since a long-run expected win rate of 87.5% by switching is far

better than a long-run expected win rate of 12.5% by not switching.

(b) ‘If there is uncertainty about some monetary outcome and you are concerned

about return and risk, then all you need to see are the mean and standard

deviation. The entire distribution provides no extra useful information.’ Do you

agree or disagree? Provide an example to back up your argument.

(5 marks)

Reading for this question

Block 5 on the VLE covers probability and probability distributions.

Approaching the question

The mean and standard deviation are very important measures of a probability distribution,

but the entire shape of the distribution, especially the tails, can be relevant in

decision-making. This is especially true when you are risk-averse and large losses (or gains)

are possible!

(c) It is assumed that inter-person arrival times at a bank during the peak period

covering lunchtime follow an exponential distribution with a mean of 20

seconds. An Excel analysis was conducted as follows:

4

Examiners’ commentaries 2020

Explain what the value of 0.0498 in cell B5 represents and why this function is

used.

(7 marks)

Reading for this question

Block 6 on the VLE covers common probability distributions in business applications. This

question combines the Poisson and exponential distributions.

Approaching the question

This returns the probability of having to wait at least 1 minute (equivalently 60 seconds) for

the next arrival. The Excel function returns P (X = 0), where X ～ Pois(3), which is:

P (X = 0) =

e?λλx

x!

=

e?3 × 30

0!

= e?3.

Since the inter-person arrival times, Y , follow an exponential distribution, then Y ～ Exp(λ)

with E(Y ) = 1/λ = 20, hence λ = 1/20, which represents the per-second rate parameter for

the Poisson distribution. Since this is multiplied by 60 in cell B5, we have the Poisson

parameter for 1 minute.

Question 2

A landlord is deciding whether to upgrade a building’s heating system and is willing

to do so if the cost of the upgraded system can be recovered over the next five

consecutive years of winter weather, which can be classified as one of ‘mild’,

‘normal’, ‘cold’ and ‘severe cold’, occurring with probabilities of 0.2(1 ? x),

0.5(1 ? x), 0.3(1 ? x) and x, respectively.

The predicted annual heating costs (assume no inflation), with and without the

upgrade, are:

Type of winter weather Mild Normal Cold Severe cold

Annual costs without upgrade ￡420 ￡590 ￡720 ￡900

Annual costs with upgrade ￡358 ￡503 ￡612 ￡765

Probability 0.2(1 ? x) 0.5(1 ? x) 0.3(1 ? x) x

(a) Given x = 0.2, what is the maximum amount a risk-neutral landlord would be

willing to pay for an upgraded heating system?

(6 marks)

(b) If the upgraded heating system costs ￡600, what is the minimum value of x

such that installing the upgraded heating system becomes economically viable?

(7 marks)

5

ST2187 Business analytics, applied modelling and prediction

(c) Given x = 0.2, calculate and interpret the expected value of perfect

information, EVPI.

Hint: You may find it helpful to draw the decision tree for this problem.

(12 marks)

Reading for this question

Block 7 on the VLE covers decision-making under uncertainty using decision trees.

Approaching the question

(a) Given x = 0.2, the expected monetary value, EMV, of annual costs without the upgrade is:∑

x p(x) = 420× 0.16 + 590× 0.40 + 720× 0.24 + 900× 0.2 = ￡656.00

and so for five consecutive winters is:

￡656.00× 5 = ￡3,280.00.

Given x = 0.2, the expected monetary value, EMV, of annual costs with the upgrade is:∑

x p(x) = 358× 0.16 + 503× 0.40 + 612× 0.24 + 765× 0.2 = ￡558.36

and so for five consecutive winters is:

￡558.36× 5 = ￡2,791.80.

Hence the maximum amount a risk-neutral landlord would be willing to pay for an upgraded

heating system is:

￡3,280.00? ￡2,791.80 = ￡488.20.

(b) In terms of x, the expected monetary value, EMV, of annual costs without the upgrade is:∑

x p(x) = 5× (420× 0.2(1? x) + 590× 0.5(1? x) + 720× 0.3(1? x) + 900× x)

= 1,525x+ 2,975

while with the upgrade, the EMV is:∑

x p(x) = 5× (358× 0.2(1? x) + 503× 0.5(1? x) + 612× 0.3(1? x) + 765× x)

= 1,291.5x+ 2,533.50

So we require:

(1,525x+ 2,975)? (1,291.5x+ 2,533.50) > ￡600

which solves for:

x > 0.6788.

Hence the value of x must be at least 67.88% before the landlord decides it is economically

worthwhile to install upgraded heating system.

(c) From (a), the expected monetary value with no information is ￡3,280.00.

6

Examiners’ commentaries 2020

The decision tree is:

The expected monetary value with perfect information is ￡3,265.00.

Hence the expected value of perfect information is:

EVPI = ￡3,280.00? ￡3,265.00 = ￡15.00.

Question 3

A company is interested in explaining the variation in annual salaries of its

employees, in dollars, using the following variables:

Gender, coded as 1 = female, and 0 = male

Age, in years

Work experience with the company, in years

Amount of post-16 education, in years.

(a) The correlation matrix of the above variables is:

7

ST2187 Business analytics, applied modelling and prediction

What conclusions can be drawn from this correlation matrix?

(6 marks)

The results of a multiple regression, including an interaction between gender and

age are:

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

(5 marks)

(c) Assess the overall significance of the model, as well as the significance of the

individual regression coefficients.

(5 marks)

(d) According to this multiple regression model, how does age impact annual

salaries for males and females?

(4 marks)

(e) Calculate an approximate 99% prediction interval for the annual salary of a

26-year-old female, who has worked at the company for 8 years, with a total of 2

years of post-16 education. Use a t coefficient of 2.601.

(5 marks)

Reading for this question

Blocks 11 and 12 on the VLE cover regression analysis (estimating relationships and statistical

inference, respectively).

8

Examiners’ commentaries 2020

Approaching the question

(a) The dependent variable (annual salary) shows moderate correlation with each of the

candidate independent variables, albeit gender is categorical, suggesting performing a

multiple regression would be appropriate.

Among the independent variables the strongest correlation is only 0.269 and so we would

not expect multicollinearity to feature as a problem in a multiple regression.

(b) We have:

?Annual salary = ?6,583.78 + 9,465.53×Gender + 592.82×Age

+ 2,831.16× Experience + 8,182.46× Education

? 474.85×Gender×Age.

R2 = 0.7110 indicating that 71.10% of the variation in annual salary is explained by the

model, which is a fairly high percentage.

(c) Testing H0 : β1 = β2 = · · · = β5 = 0, the F -ratio is 97.42 with a p-value < 0.0001, hence the

test is highly significant and so we have very strong evidence that the model has significant

explanatory power. Testing H0 : βi = 0 for i = 1, 2, . . . , 5, all the variables are statistically

significant at the 5% significance level, except Gender with a p-value of 0.28 > 0.05. Note

that the interaction variable involving Gender (with Age) is significant.

(d) For females, the effect of an extra year on annual salary is:

592.82? 474.85 = 117.97

while for males the effect of an extra year on annual salary is 592.82.

Other things equal, females earn $474.85 less than males in a year.

(e) The prediction is:

?6,583.78 + 9,465.53× 1 + 592.82× 26 + 2,831.16× 8 + 8,182.46× 2? 474.85× 1× 26

which is $44,966.66. Hence an approximate 99% prediction interval is:

Y? ± t0.005, 198 × se ? 44,966.66± 2.601× 16,467.77

which gives ($2,133.99, $87,799.33).

Question 4

(a) Holt’s method, as an exponential smoothing model, assumes an additive trend.

Suppose instead that the model is changed to a multiplicative trend so that, for

example, if the current level estimate is 10, and the current trend estimate is

1.3 (i.e. the forecast increases by 30% per period), then the forecast next period

is 10 × 1.3, and for the following period is 10 × (1.3)2. Holt’s method equations

then become:

Lt = αYt + (1 ? α)(?) and Tt = β(#) + (1 ? β)Tt?1.

i. State the expressions for (?) and (#) in the above equations, briefly

explaining your answer.

(6 marks)

9

ST2187 Business analytics, applied modelling and prediction

ii. Assume you are working with monthly data, with December being month 12,

January being month 13 etc. Suppose L12 = 80 and T12 = 1.2 and you

observe Y13 = 90. Determine the forecast for Y15, assuming α = 0.3 and

β = 0.7 using a multiplicative trend.

(7 marks)

Reading for this question

Block 13 on the VLE covers time series analysis and forecasting.

Approaching the question

i. The first term is the product of the previous level and the previous trend:

Lt?1 × Tt?1.

The second term is the ratio of the current level to the previous level:

Lt

Lt?1

.

ii. We have:

L13 = α× Y13 + (1? α)× L12 × T12 = 0.3× 80 + 0.7× 90× 1.2 = 94

and:

T13 = β × L13

L12

+ (1? β)× T12 = 0.7× 94

80

+ 0.3× 1.2 = 1.18.

Therefore:

F15 = L13 × T 213 = 94× (1.18)2 = 132.11.

(b) Is it more appropriate to use an additive or a multiplicative model to forecast

seasonal data? Summarise the difference(s) between these two types of seasonal

models.

(5 marks)

Reading for this question

Block 13 on the VLE covers time series analysis and forecasting.

Approaching the question

Both can be useful, and it is hard to say that one is better than the other. The additive

model is relevant if you believe each season’s typical value is a certain amount above or

below the overall average. The multiplicative model is relevant if you believe each season’s

typical value is a certain percentage above or below the overall average.

(c) Let Yt be the sales in dollars during month t, and let Pt be the price in dollars

charged during month t. A regression of the form:

Yt = β0 + β1Yt?1 + β2Pt + εt

was run and was estimated to be:

Y?t = 972,545.98 + 0.6714 × Yt?1 ? 23,604.19 × Pt.

i. If the price during month 21 is $14, and sales in month 20 were $781,000,

what would you predict for sales in month 21?

(3 marks)

10

Examiners’ commentaries 2020

ii. The correlation coefficient between Yt and Yt?1, i.e. rYt,Yt?1 , is 0.864.

Comment on this value.

(2 marks)

iii. The correlation coefficient between Yt and Pt, i.e. rYt,Pt , is ?0.325. Comment

on this value.

(2 marks)

Reading for this question

Block 13 on the VLE covers time series analysis and forecasting.

Approaching the question

i. We have:

972,545.98 + 0.6714× 781,000? 23,604.19× 14 = $1,166,451

ii. There is fairly strong positive autocorrelation for sales – for example, high sales one

month tend to be followed by high sales the next month, which seems reasonable.

iii. There is weak-to-moderate negative correlation between sales and price, consistent with

the law of demand.

Question 5

(a) Suppose you are a financial analyst and your company runs many simulation

models to estimate the profitability of its projects. If you had to choose just two

measures of the distribution of any important output, such as net profit, to

report, which two would you choose? Why? What information would be missing

if you reported only these two measures? How could they be misleading?

(6 marks)

Reading for this question

Block 15 on the VLE covers Monte Carlo simulation models.

Approaching the question

The point here is that there is an entire distribution of any such output, and we are limited

to reporting only two of its measures. (Granted, this is unrealistic.) Anyway, you probably

want a measure of central tendency and a measure of variability. Two useful measures for a

financial analyst might be the median (half the time, net profit is above it; half the time, it

is below it) and the 5th percentile (this is nearly as bad as net profit will be).

(b) A country, with no free health service, offers a tax-free savings account for

medical expenses. At the start of the year you decide how much to pay into the

account. When medical expenses are incurred you may withdraw up to this

amount (with no tax paid) exclusively to pay for medical expenses (if the

amount invested exceeds your medical expenses, you lose this money). Any

medical expenses which exceed the amount you invested must be paid by you

using income taxed at 35%. Your annual salary is $85,000, and you assume

annual medical expenses are normally distributed with a mean of $1,700 and a

standard deviation of $350.

The following is the spreadsheet model which shows the results of one

simulation in the cell range A16:D16.

11

ST2187 Business analytics, applied modelling and prediction

i. For the simulation in the cell range A16:D16, explain how the value of

$53,861 in cell D16 would have been calculated (with reference to relevant

cells in the Excel spreadsheet model above).

(6 marks)

ii. Write down an appropriate Excel function for cell C16, i.e. to determine the

medical expenses above the amount invested. Explain your choice of function.

(5 marks)

iii. One thousand simulations were run and the distribution of outcomes

produced the following:

12

Examiners’ commentaries 2020

Explain why the maximum amount of money left to you is $54,340.

(4 marks)

iv. Based on the simulation results in iii., do you think investing $1,400 in the

tax-free savings account is appropriate? Or should you invest more? Or less?

Explain your answer.

(4 marks)

Reading for this question

Block 15 on the VLE covers Monte Carlo simulation models.

Approaching the question

i. Cell A16 returns a simulated value from N(1,700, (350)2), which is $1,879 denoting the

medical expenses of the individual.

The tax liability of 35% is levied on the difference between annual income and amount

invested:

($85,000? $1,400)× 0.35 = $29,260.

The medical expenses, $1,879, exceed the amount invested by:

$1,879? $1,400 = $479.

The amount of money left is:

($85,000? $1,400)× 0.65? $479 = $53,861.

ii. One possibility is =MAX(A16-B12,0), which returns the greater of ‘excess medical

expenses over amount invested’ and zero (when the amount invested is sufficient to cover

medical expenses).

iii. This represents the best case scenario when medical expenses do not exceed the amount

invested, i.e. do not exceed $1,400. Net income is then:

($85,000? $1,400)× 0.65? $0 = $54,340.

iv. Any reasonable comments accepted, taking into account the risk appetite of the

individual.