ASSIGNMENT - MODELLING ELEMENT (DRA)
Part A
The task of this part is to model, or analyse, a decision (or decisions) using:
Monte Carlo simulation or multi-criteria decision analysis
The purpose of your model or analysis should be to enable the decision to be made more effectively, so that the outcome is better than it would otherwise have been.
It is important that your decision should be a realistic one. As far as possible, the data (costs, demands, probabilities, criteria weights, etc) you use should be genuine. Where this is not possible, you should explain how you would obtain the information.
You should discuss your proposed model with your course tutor. Your course tutor will be marking the assignment, so you must get his approval. (This is to reduce the likelihood of misdirected work.)
You should hand in a written account of your model or analysis. This should include (not necessarily in this order):
* Details of the decision maker(s) and the decision(s).
* Details of the recommendations you would make to the decision maker.
* An explanation/description of your model or analysis. This should make it clear how the model works. You should also submit all relevant spreadsheet files.
* The source of any information (costs, probabilities, etc) incorporated in the model or analysis.
* An evaluation of the model or analysis. This should include a critical analysis of any assumptions, and comments on the usefulness of your analysis.
* References to books, articles, etc that you make use of.
To pass, you will need to submit everything listed above - and it should obviously all make good sense! Also, other things being equal, we will give more credit to ambitious projects than to very simple ones. To get a good mark, you should
- do everything listed above, and
- pay particular attention to the evaluation of your model, the analysis of its assumptions, and the source of the information it incorporates.
Some suggestions
These are suggestions only: we are prepared to be very flexible regarding what is acceptable, but please check with us before going ahead if you are in any doubt.
* Potential business decisions: e.g. on your proposed dot.com or other business.
* Business decisions: only viable if you work for a business or have good access to information from a business.
* Personal decisions: e.g. on purchases or rental of a car or a house, on choices of courses or careers, on investments, and so on. It may be appropriate to build a model that will be helpful for any person having to make these decisions, or you may just be considering a single decision maker.
The body of your report should not materially exceed 1,500 words (excluding references, diagrams and appendices) but you may add to that additional information in the form of appendices and tables.
The intended learning outcomes of this part of the assignment are:
- Compute, using software if appropriate, and sensibly interpret the basic statistical models listed in the syllabus outline.
- Use appropriate methods to analyse a variety of data types and business decisions.
- Use appropriate Concepts and frameworks for decision making and problem solving.
- Improved literature review skills.
- Improved written communication skills.
Your report should:
- Be written in a plain style, using subheadings and lists where appropriate.
- Be properly referenced acknowledging all the sources you have used, and only the sources you have used, in the body of the text and at the end in a section headed ‘References'.
- Include evidence of locating and reading sources beyond the suggested initial reading.
Part B
The task is to model, or analyse, the following case study:
A manufacturer wants to analyse the relationship between sales and the amount spent on advertising on television, in magazines, and on the radio. The table below shows the amount spent on these three types of advertising and the sales for each of ten randomly selected sales months. All figures are in millions of pounds.
|
Month
|
Television Advertising
Expenditure
|
Magazines Advertising
Expenditure
|
Radio Advertising Expenditure
|
Sales
|
|
1
|
8.2
|
4.3
|
6.0
|
361
|
|
2
|
6.2
|
4.1
|
4.8
|
340
|
|
3
|
9.7
|
5.7
|
6.4
|
379
|
|
4
|
9.1
|
3.1
|
6.0
|
369
|
|
5
|
10.4
|
2.6
|
5.1
|
366
|
|
6
|
9.0
|
3.7
|
5.0
|
365
|
|
7
|
9.1
|
4.0
|
6.0
|
373
|
|
8
|
10.7
|
4.3
|
6.3
|
379
|
|
9
|
9.4
|
4.2
|
5.4
|
362
|
|
10
|
10.3
|
6.1
|
5.9
|
388
|
You are required to analyse the data above using the Microsoft Excel software. For parts (c-e) do not use the standardised Excel functions - instead you should use Excel to apply the relevant formulas learnt in the course. Specifically you should answer the following questions:
(a) Use Excel to construct a histogram to show the distribution of sales. (You will need to choose suitable class intervals.)
(b) Discuss whether this distribution is normal.
(c) Calculate the quartiles of this distribution, and its inter-quartile range.
(d) Calculate the values of both Kendall's and Pearson's correlation coefficients between Sales and Television Advertising Expenditure. Explain what a correlation coefficient means in general terms.
(e) Calculate the standard deviation of Sales, Television Advertising Expenditure, Magazine Advertising Expenditure, and Radio Advertising Expenditure.
Now do a regression analysis using the Excel Regression Tool with Sales as the dependent variable, and Television Advertising Expenditure, Magazine Advertising Expenditure, and Radio Advertising Expenditure as the independent variables.
(f) Suppose a new advertising campaign is proposed with a budget of £11m allocated to Television Advertising, £6.5m allocated to Magazine Advertising, and £7m allocated to Radio Advertising. What will be the expected sales from this new campaign?
(g) What conclusions can be drawn from the regression analysis about the impact of Television Advertising Expenditure and Magazine Advertising Expenditure on Sales? (You may find the regression coefficients in the results table helpful).
(h) What does the R Square value indicate about the regression?
(i) Explain the meaning of the numbers in the p-value column. Which of the three results are significant at the 5% significance level?
(j) Discuss the benefits and difficulties of using multiple regression (you can refer to the results obtained in this example if you wish).
(k) Perform any other further analysis you might find appropriate to enhance your conclusions/analysis (for instance you might want to run single regressions and compare your results with your original output etc).
The body of your report should not materially exceed 1,500 words (excluding references, diagrams and appendices) but you may add to that additional information in the form of appendices and tables.
The intended learning outcomes of this part of the assignment are:
- Compute, using software if appropriate, and sensibly interpret the basic statistical models listed in the syllabus outline.
- Use appropriate methods to analyse a variety of data types and business decisions.
- Use appropriate Concepts and frameworks for decision making and problem solving.
- Improved literature review skills.
- Improved written communication skills.