Financial mathematics and statistics modelling
Really strict instructions from my lecturer so PLEASE read carefully the first page instructions because if there are not respected, the work won’t even be marked.
Requirements for Excel:
-5 pages of Excel on 1 document
-cells must be filled with functions, not numbers handwritten.
-use of 1st year Undergraduate functions

Requirements for Word report:
-the reader should not be required to look at the Excel file to understand the analysis and recommendations
-can include graphs and Excel screenshots
-no more than 10 pages
May have forgotten some things but please, this is my last chance to save my uni year !!!
Financial Mathematics and Business Statistics: Individual 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.

Deadline: The coursework is to be submitted on Moodle no later than 8.00pm on Monday 14th March 2016. You will need to submit a Word document with the report (see instructions above) and an Excel file with the calculations. If in doubt on how to submit both files on Moodle, please contact Dilip Parmar in the course office allowing sufficient time for him to clarify any doubts you have in advance of the submission deadline.

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!

Notes:
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: (25%)

Kyle Dier is looking to buy his first home and through savings and a gift from his parents he has managed to raise £80,000. Kyle has identified two possible properties, a one bedroom flat in the centre of town that costs £250,000 and a three-bedroom house outside of town that costs £400,000 and he’s decided to ask for your help identifying the best mortgage offers he can choose.

Required:

a) Identify (and clearly indicate them in your submission) a minimum of three lenders Kyle could borrow the money from and discuss the mortgage alternatives available and the respective terms and applicable interest rates;
b) For the most favourable rate for each type of mortgage, calculate the monthly payment Kyle would need to make for each of the properties he’s looking to possibly buy if he chooses to take out a 20-year mortgage;
c) Kyle believes it is highly likely that we will receive £50,000 in two years’ time and if he does, he will use the money to make a partial payment of the mortgage. For both options in b) and considering the payments he will have done by then as well as the one-off partial payment, calculate the monthly payments for the remaining 18 years of the mortgage.
d) Make a recommendation on the property Kyle should buy purely on financial terms and identify what other factors should be taken in consideration when making the final decision.

Question 2: (10%)

Due to the continued low level of sales Toys For You, a wholesaler of toys and games has experienced in the last three years, the management has been conducting a review of its stock ordering system. The management of the company has asked each of the analysts in its finance department, to look at one of its product lines, and you were asked to look at board games. Having made your research, you have determined that it is uncertain when the recovery will start, so you have established the following demand scenarios for the next 12 months:

Scenario Crisis Continues Slow Recovery Medium Recovery Fast Recovery
Probability 10% 30% 40% 20%
Demand 50,000 65,000 85,000 100,000

Considering the cost per order is £50 and the average carrying cost per unit is £1.50:

a) Determine the Economic Order Quantity given the data above.
b) Produce sensitivity analysis assuming a change of up to 10% up or down on each of the factors individually and on all factors simultaneously.
c) Make a final recommendation to the board of the company, as to the number of units it should include in each order.
Question 3: (20%)

A company that manufactures electrical appliances is looking at one of its lines (washing machines), where it offers three different levels of specification: Basic which sells for £250, Medium which sells for £450 and Luxury which sells for £700. The production of each machine goes through four different stages and you have been provided with the following data table:

ProcessMachine Basic Medium Luxury Cost per hour Max. Available
Forming 1.5 hours 3 hours 7 hours £5.50 6,500 hours
Machining 5 hours 8 hours 14 hours £7.50 18,000 hours
Assembly 2.5 hours 4.5 hours 8 hours £9.50 8,500 hours
Testing 2 hours 2 hours 2 ½ hours £12.00 5,200 hours

The marketing department has also done some market research and believes demand for each of the models is limited to 1,450 units of the basic model, 900 of the medium and 750 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 washing machine product line.
b) How would your answer change if the following happened:
a. Maximum demand for Medium model was 1,000; OR
b. Maximum available Assembly hours were 9,500.
c) Write a report with a recommended production and marketing plan for the company.

Question 4: (15%)

Given the data included in the excel file “Assignment FM&BS 1516 Data”:

a) Summarise the distribution of family income and house price and comment on the results?
b) Is there evidence that the average house price is £500,000? Justify
c) Based on this sample, provide a 90% and a 99% confidence interval for the Family income and explain and justify the difference between the two intervals.
d) Would you expect to find a relationship between the two variables under study? If so, what type of relationship would you expect?
e) Does this data provide evidence to support your answer to part d)?

Question 5: (20%)

The management of Make More plc, a manufacturer of low cost electronic products, has seen a significant increase in the company’s activity over the last two years.

The company now has to decide whether to undertake a potential investment to make three additional products using a new technology. In order to undertake this investment, Make More is looking to lease a new facility for the next six years, after which it is expected that the technology will be obsolete. The forecasted cash flows put together by the management of the business are as follows:

? Sales of the three products in year one will be 50,000 units at an average price of £55;
? Sales volume is expected to grow at 20% a year for the first three years, and decrease at 25% a year for the remainder of the project;
? Cost of sales will be 65% of sales;
? Selling and administrative expenses specific to this project will be £450,000;
? The lease rent will be £150,000;
? In order to manufacture the three products, Make More is going to buy equipment worth £1m. This equipment is expected to have a scrap value of £150,000 in six years time;
? Equipment will be depreciated using straight line depreciation (Note: depreciation is not a cash flow, but since it is a cost, it affects the tax calculation and tax paid is a cash flow);
? Corporate tax rate is 20%;
? The current cost of capital of Make More is 15%, but this project is considered to be riskier and the cost of capital for companies with comparable levels of risk is 20% higher.

Required:

a) Discuss and compare the different types of investment appraisal methods Make More can use, including a discussion of the advantages and disadvantages of each.
b) If Make More had a rule that all investment projects need to payback within 2 years, would this project be accepted or rejected? Comment.
c) Using NPV and IRR, make a recommendation on whether Make More should go ahead with the investment.