Financial Mathematics and Business Statistics: Coursework
This coursework tests your basic financial mathematics and statistical modelling skills, using spreadsheet software (Excel – formulae, financial maths, graphical features, Data Analysis and Solver tools) as well as your awareness of the reality of how financial products work. Your answers are to be presented in an essay/report format, for which you will use a word processor. In writing your report, please:
· state and explain all assumptions, on which your answers are based;
· clearly indicate your answer/recommendations
· support any answers with the appropriate calculations to arrive at the answer
· include selected printouts of formulae underlying computed values. Despite the fact that you will be submitting the Excel file as well, your report is a stand-alone document, meaning a reader should not be required to look at the Excel file to understand your analysis, findings and recommendations
· please note that adequate usage of the excel calculations in the report is important. This means that the key data/findings needs to be included in the report and appropriate referencing needs to be done, i.e. the relevant cell/table/range in the relevant tab of the excel file mentioned at the point of the report when it should be consulted.
The report will have a maximum of 10 pages (including any Appendixes; penalties will be applied for longer submissions – you are required to develop your judgement on what is and isn’t important). Ten percent of the total mark is allowed for quality of the presentation and these marks are distributed among the questions.
Deadline: The coursework is to be submitted on Moodle no later than 5.00 pm on Monday 17th March 2014. You will need to submit a Word document with the report (see instructions above) and an Excel file with the calculations.
This coursework is your own (individual) work. Any student found guilty of plagiarism will be penalised. Standard penalties for late submissions are applicable.
Question 1: (15%)
Due to the weakness in the market, most companies had to face in the last few years, the management team at Dreamcatcher, a games’ retailer has been reviewing its stock ordering system and has managed to negotiate a reduction in ordering costs with their main supplier from £280 to £190. Despite selling different games, their similar size and packaging allows games to be managed as a single product. The expectations regarding next year’s state of the economy are as follows:
Considering that the average carrying cost per unit is £1.45:
a) Make a recommendation to the board of Dreamcatcher, as to how often it should order and what level or stock should be ordered each time;
b) Assess the savings achieved by the recent renegotiation with the main supplier; and
c) Assess the impact of this recommendation at the different levels of demand.
Question 2: (20%)
A company that manufactures electrical appliances is looking at one of its lines (microwave ovens), where it offers three different levels of specification: Cheap which sells for £50, Average which sells for £100 and Premium which sells for £250. The production of each oven goes through four different stages and you have been provided with the following data table:
Cost per hour
2 ½ hours
The marketing department has also conducted market research and believes demand for each of the models is limited to 2,000 units of the basic model, 1,200 of the medium and 700 of the luxury. You are required to:
a) Formulate this problem as a linear program and use Excel’s Solver to arrive at a solution, identifying what is the maximum profit the company can achieve in the microwave ovens product line, under current conditions.
b) How would your answer change if the following happened:
1. Maximum demand for Premium model was 1,000; OR
2. Maximum available Machining hours were 20,000.
c) Make a recommendation of the production mix and marketing plan for the company.
Question 3: (25%)
The majority of banks, when making decisions on mortgage applications, will look at two indicators: salary and borrowing as a percentage of purchase price. On the first indicator, banks are normally willing to lend 2.5 times one’s salary or 3.25 times joint salary in a joint mortgage application, while currently most banks will lend up to 75% of the property price on their best rate with penalties for higher percentages. John and Julia are getting married and decided to buy a flat to move into once they do. You have been given the following data:
· John’s current salary is £39,000 p.a. and Julia’s is £37,500 p.a. plus a bonus likely to be around £5,000 (based on previous 3 years experience);
· Both have jobs where they partly telecommute, so on average each works from home 2 days a week;
· Their total savings at the moment are £25,000;
· John owns a flat which he would sell, and has been advised that he should be able to sell it for £150,000. The mortgage outstanding on this flat is £112,000;
· John and Julia are planning to apply for a 25 year mortgage;
· The average price of flats in the area they would like to move into is as follows: studios £150,000; 1-bedroom £220,000; 2-bedroom £325,000; 3-bedroom £450,000; 4-bedroom £600,000
· Having contacted a financial adviser, he has identified the following as the best available mortgage rates:
§ Repayment fixed rate for 2-years of 3.69%. After that period, the rate reverts to the bank’s standard variable rate, which currently is 5.7%;
§ repayment fixed rate for 5-years of 4.29%. After that period, the rate reverts to the bank’s standard variable rate, which currently is 5.7%;
§ interest only mortgage at 5% for the life of the loan. In this instance, you would be required to create an investment fund, which pays an interest rate of 3.9% to cover the repayment of the mortgage.
§ All the rates above are for loans of up to 75% of the property value. There is an increase of 1.5%age points if borrowing is up to 90% of the property value.
a) What is the maximum John and Julia can borrow while taking advantage of the bank’s best mortgage rate;
b) The amount you advise them to borrow, given their financial and professional situation;
c) Which is the best mortgage that John and Julia can take out (assume they take out the amount you recommended in b);
d) Whether that advice would change if interest rates went up or down by up to three percentage points.
Question 4: (15%)
A statistician is trying to find whether there is a relationship between the number of hours of study and exam results, or whether exam results are random. Looking at two different subjects (quantitative methods and accounting), the first part of his study was to generate random numbers of study hours and exam grades, which will be compared to the actual results once the exams are taken and marked. The data randomly generated is given in the table below:
QM Study Hours
QM Exam Grade
Accting. Study Hours
Accting. Exam Grade
a) Summarise the distribution of expected grades for both exams, according to the data given. Discuss the key characteristics of the data.
b) Construct a 95% confidence interval for the expected exam marks for each of the subjects. Is there a significant difference between them?
c) Would you expect the actual exam results to show a pattern similar to the calculations above? Discuss why/why not and whether you believe the type of exam and questions (numerical, essay-type, multiple choice, etc.) will have an influence in the outcomes.
Question 5: (15%)
Garnett plc. has seen sales in one of its product lines decline over the last two years. The production is currently subcontracted and any changes require a six month notice, so the management of Garnett has to decide now what to do for their most important advertising and sales period, which starts in September every year. The two identified options are:
§ Option A – Invest £4million to make small changes to the product design and manufacturing process, which will generate increased cash flows in the short term;
§ Option B – completely redesign the product and production process, which will have a longer lasting effect on cash flows, but will require an investment of £15million.
Garnett’s required rate of return on investments is 10.25% and the estimated cash flows for the two options are as follows (in ‘000s):
a) Discuss and compare the different types of investment appraisal methods Garnett can use, including a discussion of the advantages and disadvantages of each.
b) If Garnett had a rule that all investment projects need to payback within 3 years, what project would be chosen? Comment.
c) Make a recommendation as to which project should be undertaken.
d) If Garnett believes there is an opportunity to start exporting its product line to another country once sales are finished in its home country (i.e. from year 5), and it thinks it will be able to generate cash flows of £660,000 in the first year, £1,540,000 in the second and £2,300,000 in the subsequent four years, would your answer to part c) change? How?