Financial Mathematics and Business Statistics: Coursework
Instructions
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;
• no evidence of use of excel will result in a fail mark for this element of the coursework component of your mark;
• support any answers with the appropriate calculations to arrive at the answer;
• include selected screens of formulae underlying computed values. Failure to demonstrate you have created appropriate formulations on excel will be severely penalised. 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.
Also, please note that late submissions will be penalised, no matter how small and irrespective of computer or internet crashes or any other last minute unexpected problem, so make sure you plan your submission allowing enough time to overcome any last minute problems. This includes keeping up-to-date back-ups of your work!
Question 1:
Dreamcatcher, a games' retailer has been showing good performance since it was created five years ago and its owners are now looking at if and how to grow the business. Having done an analysis of the industry they are in and their own strengths and weaknesses, they have identified three different possible courses of action for the business and three possible scenarios for how the market will evolve. The possible courses of action and the different market scenarios as well as the probabilities associated with each are summarised in the following table (profit in £000):
Project / Scenario Pessimistic Average Optimistic
International Expansion 7,500 13,800 21,900
Domestic Expansion 8,600 14,000 19,500
No Change 11,000 12,300 16,700
Probability 25% 55% 20%
Required:
a) Conduct a Decision Analysis of the three alternative actions Dreamcatcher may choose;
b) Make a recommendation of what you think Dreamcatcher should do including your arguments for that decision.
Question 2:
A company that manufactures amplifiers is looking at one of its best selling lines, one which includes three different levels of specification: Cheap which sells for £75, Average which sells for £150 and Premium which sells for £375. The production of each amplifier goes through four different stages and you have been provided with the following data table:
ProcessMachine Cheap Average Premium Cost per hour Max. Available
Forming 0.5 hours 0.75 hours 2.25 hours £4.50 2,250 hours
Machining 1.75 hours 3.5 hours 9 hours £6.25 8,750 hours
Assembly 0.25 hours 0.75 hours 2 hours £7.75 3,250 hours
Testing 0.25 hours 0.5 hours 1.5 hours £10.00 2,375 hours
The marketing department has also conducted market research and believes demand for each of the models is limited to 3,000 units of the basic model, 2,400 of the medium and 1,400 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 this line of amplifiers, under current conditions.
b) How would your answer change if the following happened:
1. Maximum available Machining hours were 10,500; OR
2. The company was able to charge £200 for the Average amplifier.
c) Make a recommendation of the production mix and marketing plan for the company.
Question 3:
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. The penalty will be 1.5%age points if borrowing is up to 90% of the property value. Adam and Eve are getting married and decided to buy a flat to move into once they do. You have been given the following data:
• Adam's current salary is £49,000 p.a. and Eve's is £42,500 p.a. plus a bonus likely to be around £7,500 (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 £45,000;
• Adam owns a flat which he would sell, and has been advised that he should be able to sell it for £185,000. The mortgage outstanding on this flat is £124,000;
• Adam and Eve 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 £220,000; 1-bedroom £330,000; 2-bedroom £440,000; 3-bedroom £575,000; 4-bedroom £750,000
Required:
a) By consulting the website of a bank in one of the -big four- UK banking groups (clearly indicate in your report the website link and date consulted):
a. Explain the term Offset Mortgage;
b. Identify the lowest rate the bank is currently charging for an Offset mortgage; and
c. Identify the lowest rate, application fees (if applicable) and respective period the bank is charging for a fixed rate mortgage;
b) Calculate the amount you advise Adam and Eve to borrow and which size flat to buy, given their financial and professional situation;
c) Using the two rate identified in a) calculate which is the best mortgage that Adam and Eve 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:
The table below represents data for the profits, sales, size and number of product lines sold by the 20 branches of a retailing company. You have been asked to analyse the data, using the Data Analysis tool in Excel, and make recommendations, including answering the following questions.
a) Summarise the distribution of profits of the twenty branches and comment on the results?
b) Is there evidence that the average number of lines stocked per store is significantly different from 78?
c) If you divide the branches in two groups with, one of branches with sales above £150,000, and the other with sales below that value, is there a significant difference between the profits of the groups?
d) Based on this sample, provide a 99% confidence interval, and comment on the outcome, for the profits of the twenty branches.
e) Is there evidence of association between the profit and the other variables?
Profit (£000s) Sales (£000s) Size (000s sq. ft.) Lines
42.13 748.82 6.0 150
6.32 140.78 1.4 75
38.47 702.11 5.0 170
-0.32 41.54 1.0 75
3.65 96.85 1.2 75
7.77 166.93 1.5 75
4.31 109.05 1.3 75
4.53 263.92 1.1 80
-2.69 50.84 1.1 75
3.22 90.08 1.2 75
9.03 190.59 1.4 80
-2.59 91.75 1.2 75
6.39 141.57 1.4 80
24.39 377.04 3.5 160
13.92 198.69 1.5 100
2.13 62.78 1.3 75
17.48 265.28 2.1 110
7.21 91.80 1.3 85
15.62 231.60 2.5 120
33.61 548.31 4.5 200
Question 5:
My Velo, a manufacturer of bicycles, is planning on building a new factory and it has identified an ideal location for which it has paid £50,000 to acquire the option to buy this land.
The board has identified the following cash flows if it chooses to go ahead with the investment in building the factory:
• Required investment in acquiring the land and building the factory is £15million, which will be spent 80% now and 20% at the end of this year;
• Construction of the factory and all additional infrastructure will take one year. Sales are expected to be £14million in year 2 (first year of sales), and grow 10% in each of the subsequent three years after which they will stabilise;
• Cost of Sales is expected to start at 50% of sales and reduce by 5 percentage points in each of the subsequent two years, after which they will remain unchanged;
• Administration and Selling costs will be 10% of sales;
• The company expects it will need to refurbish the factory every four year at a cost of £2million;
• My Velo expects it would be able to sell the factory for £6million at the end of 10 years
• The cost of capital of My Velo is 8.75%
Required:
a) Discuss and compare the different types of investment appraisal methods My Velo can use, including a discussion of the advantages and disadvantages of each.
b) If My Velo had a rule that all investment projects need to payback within 3 years, would this project be accepted or rejected? Comment.
c) Using NPV and IRR, and assuming My Velo expects to run this factory for 10 years, at which point it will decide whether to sell it or continue operating, make a recommendation on whether My Velo should go ahead with the investment, including on whether it should sell or continue to operate the factory.