BS1003 Fundamentals of Finance
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 severelypenalised. 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;
Question 1:
On the 4th of August 2016, the Bank of England (BoE) lowered its main interest rate to 0.25%, the lowest on record since the Bank has published rates in 1970, which still remains unchanged. As a consequence of this low level of rates, Virgin Money’s Lifetime Tracker Standard mortgage rate stands at 3.9%. Jeff Green is about to buy a house in the countryside which costs £650,000 and is taking out a 25-year repayment mortgage for 75% of the acquisition value of the house. Jeff has asked you to assess his ability to pay, in case the BoE increases its interest rate, as his maximum monthly payment cannot exceed £3,200. You are required to:
- Calculate the monthly payment John will need to meet under current conditions.
- Assess the effect on John’s monthly payments for each quarter percentage point increase in the BoE rate, up to 4% and identify at which rate Jeff would no longer be able to make the monthly payment.
- Determine the shortest length (to the nearest month) of mortgage Jeff could take out if he wanted his monthly payment to be exactly the maximum he could afford (based on current mortgage assumptions).
The marketing department has also done some 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:
- 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.
- How would your answer change if the following happened:
- Maximum demand for Premium model was 1,000; OR
- Maximum available Machining hours were 20,000.
- Write a report with a recommended production and marketing plan for the company
Answer:
Question 1
The objective of the given task is to assess the instalment amount that Jeff would have to pay for the mortgage that he has assumed for his house.
- In the given scenario, the monthly payment for the loan needs to be computed for John. The various input data is summarised as follows.
Cost of house = £650,000
Loan amount taken = 0.75* £650,000 = £ 487,500
Applicable interest rate = 3.9% p.a. or (3.9/12)% per month
Time period of loan = 25 years or 25*12 = 300 months
The monthly instalment for Jeff can be computed using the PMT function in excel which provides the answer as £ 2,546.36.
2) The impact of increasing interest rate is highlighted as follows.
It is apparent that if there is an increase by 3% or 4%, then the monthly instalment cannot be afforded by Jeff.
3) Now the monthly payment = £ 3,200
Loan amount taken = 0.75* £650,000 = £ 487,500
Applicable interest rate = 3.9% p.a. or (3.9/12)% per month
Time period of loan needs to be determined using the PMT and Goal seek function in excel.
The time period in months is 211 months approximately.
Question 2
- Formulation of linear programming problem
The aim is to formulate a linear programming problem to determine the optimum quantities of each of the product so that the company can maximize their profit.
The given information and data can be summarized below.
- The three different levels of specifications of microwave oven is highlighted below.
Cheap: sells for £ 50, Average: sells for £100, Premium: sells for £250
- The production process of the microwave comprises four different stages and the relevant information about these stages is highlighted below.
|
Process/Machine |
Cheap (hours) |
Average (hours) |
Premium (hours) |
Cost per hour (£) |
Max. Available($) |
|
Forming |
1 |
1.5 |
3 |
4.50 |
4500 |
|
Machining |
3.5 |
7 |
13 |
6.25 |
17500 |
|
Assembly |
0.5 |
1.5 |
3.5 |
7.75 |
6500 |
|
Testing |
0.5 |
1 |
2.5 |
10 |
4750 |
- Demand for each of the three models is listed below.
Basic model = 2000 units, Medium model =1200 units, Luxury model = 700 units
Step 1: cost of each of the microwave
|
Process/Machine |
Cheap (hours) |
Cost per hour (£) |
Cost (£) |
|
Forming |
1 |
4.50 |
=1*4.5 =4.5 |
|
Machining |
3.5 |
6.25 |
=3.5*6.25 = 21.875 |
|
Assembly |
0.5 |
7.75 |
=0.5*7.75 =3.875 |
|
Testing |
0.5 |
10 |
=0.5*10 =5 |
|
Total |
35.25 | ||
|
Process/Machine |
Average (hours) |
Cost per hour (£) |
Cost (£) |
|
Forming |
1.5 |
4.50 |
=1.5*4.5 =6.75 |
|
Machining |
7 |
6.25 |
=7*6.25 = 43.75 |
|
Assembly |
1.5 |
7.75 |
=1.5*7.75 =11.625 |
|
Testing |
1 |
10 |
=1*10 =10 |
|
Total |
72.125 | ||
|
Process/Machine |
Premium (hours) |
Cost per hour (£) |
Cost (£) |
|
Forming |
3 |
4.50 |
=3*4.5 =13.5 |
|
Machining |
13 |
6.25 |
=13*6.25 = 81.25 |
|
Assembly |
3.5 |
7.75 |
=3.5*7.75 =27.125 |
|
Testing |
2.5 |
10 |
=2.5*10 =25 |
|
Total |
146.875 | ||
Therefore, per unit manufacturing cost for each of the microwave is computed and is shown below.
|
|
Cheap |
Average |
Premium |
|
Cost (£) |
35.25 |
72.125 |
146.875 |
Step 2: Per unit profit from the sale of the microwave is computed with the help of formula shown below.
|
|
Cheap |
Average |
Premium |
|
Unit Sell (£) |
50 |
100 |
250 |
|
Unit Cost (£) |
35.25 |
72.125 |
146.875 |
|
Unit Profit (£) |
14.75 |
27.875 |
103.125 |
Assumption: Let the quantity of cheap microwave is x, the quantity of medium microwave is y, the quantity of premium microwave is z.
Subject to constraints
Formulates linear programing problem has been solved through excel solver and the model is represented below.
Therefore, the optimum quantity of each of the microwave is highlighted below.
The quantity of cheap microwave (x) is 2000 and for medium microwave (y) is 200 and premium microwave (z) is 700. Further, the maximum profit = £ 107,262.50
(2) (a) Maximum demand for premium model = 1000
The constraint for demand for premium model would change and other factors remain the same.
Optimum quantity: Cheap = 1285, average =0 and premium = 1000, maximum profit = £122,078.75
(b) Maximum available hours for Machining process = 20,000
The constraint for machining hours would change and other factors remain the same.
Optimum quantity: Cheap = 600, average =1200 and premium = 700, maximum profit = £114,487.50
3) For profit maximising, it is apparent that 2000 units of cheap, 200 units of medium and 700 units of luxury needs to be produced and sold. It is evident that 100% of capacity for cheap and luxury has been used but the same is not true for medium. As a result, efforts need to be made to phase out the medium product and enhance capacity in luxury segment preferably and if the same is not possible, then cheap variant should be looked at. Further, the marketing of the product also needs to be done in a manner so that there are two variants of the product addressing customers with different needs.
Question 3
1) The objective is to highlight the expected grades distribution for each of the subjects.
Subject – QM
The relevant summary statistics are indicated below.
|
It is apparent based on the above that the given distribution is not normal since there is presence of positive skew which is apparent from the summary statistics along with the underlying histogram which highlights a longer right tail as compared to the left tail. Further, the shape of the histogram does not seem to fit in a bell curve. |
It is apparent based on the above that the given distribution is not normal since there is presence of positive skew which is apparent from the summary statistics along with the underlying histogram which highlights a longer right tail as compared to the left tail. Further, the shape of the histogram does not seem to fit in a bell curve.
|
2) The respective 95% confidence intervals for the two subjects are indicated below.
It is apparent that the difference between the above variables is not significant considering the fact that the two confidence intervals tend to overlap as indicated above. The difference between the variables would have been significant if the confidence intervals did not overlap.
- c) The relevant output for testing whether the study hours is significant or not for QM subject is shown below.
It is apparent that the p value associated with the slope of QM study hours is 0.35 which is greater than 0.05 and hence it implies that the study hours is not a significant variable for determination of exam grades.
The relevant output for testing whether the study hours is significant or not for acting subject is shown below.
It is apparent that the p value associated with the slope of acting study hours is 0.35 which is greater than 0.05 and hence it implies that the study hours is not a significant variable for determination of exam grades.
- d) Since the p value corresponding to slope was lower for QM subject as compared to the acting subject, hence the superior model would be the one based on QM model. A marginally better model can be compared by breaking the model into two groups. This is apparent from the fact that for one group i.e. more than 20 hours group, the R2value has improved to 0.1 as compared to the earlier level of 0.03-0.05.
- e) These results may not be replicated for the students who would actually sit in the exam. This is because the underlying range of study hours could be potentially outside the range of hours which has been used for the given study. Also, the personal attributes related to learning may be different for the actual students who would attend the exam and hence this would also influence the marks scored in different subjects. Further, considering the low R2, the given regression model should not be used for estimating the marks as there would be high degree of error since the relevant independent factors have not been captured which could explain the marks in each of the subjects.
Question 4
- The relevant formula for Economic Order Quantity (EOQ)
- Product sensitivity analysis by assuming a change of 7.5% up or down
For D: Increase =
Decrease =
For S: Increase =
Decrease =
For H: Increase =
Decrease =
For all factors (D, S, H) Increase =
Decrease=
- c) The number of units that should be included in the EOQ would be 4264 units since it represents the base case and also with regards to deviation from the base case it is possible that the change in various factors may cancel the impact of each other.
Question 5
In the given case, Allen is looking to invest £2,100,000 for opening a new facture so as to reduce the manufacturing costs. In this regards, he has the option of two investments both of which would require the same amount of investment. Hence, the given issue is that of capital budgeting whereby appropriate tools need to be deployed in order to analyse the two investments in the wake of the projected cash inflow. The appropriate tool to be used in order to decide on the superior investment is NET PRESENT VALUE or NPV. This involves finding the sum of the present values of the expected cash flows from the investments during the useful life with the underlying discount factor being the cost of capital.
The various assumptions in carrying the NPV analysis on the given projects are indicated as follows.
- It is assumed that the cash inflows from the investment would occur at the end of the respective years and not during the year. As a result, the cash flows discounting becomes simple and thereby manageable.
- Since NPV assumed post tax cash flows, hence it is assumed that the given cash flows regarding the project are all post tax only.
- Also, it has been assumed that the cost of capital for the project remains the same throughout the project life and does not witness any change.
The NPV of country A is estimated as follows.
It is apparent that NPV of the investment in country A is £ 1,023,401
The NPV of country B is estimated as follows.
It is apparent that NPV of the investment in country B is £ 956,313
Recommendation
Considering that NPV of investments in both countries leads to a positive, it implies that both the investments are feasible. However, the better investment would be one that would generate a higher amount of NPV since this amounts to wealth generation for the shareholders. It is apparent that the preferable investment would be in country A owing to higher NPV being generated in comparison to the investment made in country B.
Buy BS1003 Fundamentals of Finance Answers Online
Talk to our expert to get the help with BS1003 Fundamentals of Finance Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
