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.
Our Service Charter
-
Excellent Quality / 100% Plagiarism-Free
We employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers. -
Free Revisions
We keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed. -
Confidentiality / 100% No Disclosure
We make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service. -
Money Back Guarantee
If the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit. -
24/7 Customer Support
We have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.