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.
Question 1:
With the reduction in sales driven by the financial crisis which have not fully recovered yet, Office Trade, a wholesaler of office equipment 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 each of its products, and you were asked to look at book shelving units. 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
|
20%
|
40%
|
35%
|
5%
|
|
Demand
|
25,000
|
35,000
|
50,000
|
75,000
|
Considering the cost per order is £30 and the average carrying cost per unit is £2:
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 2: A car manufacturer is looking to reorganise and increase the efficiency of its manufacturing operations, and is currently looking at an engine assembly line, which makes three different engines (1.8, 2.0 and 2.5 litre). The prices charged for the engines are £2,500, £2,850 and £3,750 respectively, while the inputs required to make each of the engines are listed in the table below:
|
Product
|
Basic
|
Medium
|
High
|
Max. Available
|
|
Aluminium
|
10 units
|
11 units
|
12 units
|
52,000
|
|
Other Metals
|
5 units
|
7 units
|
6 units
|
30,000
|
|
Other Materials
|
6 units
|
4 units
|
9 units
|
28,000
|
|
Labour
|
2 hours
|
2 ¼ hours
|
2 ½ hours
|
10,000
|
The costs for the inputs are £25 per hour for Labour and £100, £120 and £35 per unit for Aluminium, Other Metals and Other Materials, respectively. There is also a maximum daily demand for the engines, which is 4,000 1.8litre, 3,500 2.0litre and 2,000 2.5litre.
Formulate this problem as a linear program and use Excel's Solver to arrive at a solution. Write a short report describing your procedure, justify your formulation and give a recommendation to the firm on the best daily production mix.
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. John and Julia are getting married and decided to buy a flat to move into once they do and are looking to take on a 25-year mortgage. 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 plans to 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;
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 at the end of January, he has identified the following as the best available mortgage rates:
- Repayment fixed rate for 2-years of 1.89%. After that period, the rate reverts to the bank's standard variable rate, which currently is 3.69%;
- repayment fixed rate for 5-years of 2.34%. After that period, the rate reverts to the bank's standard variable rate, which currently is 3.69%;
- 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.
Assess:
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 to 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:
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 Garnett has to decide now what to do for their most important advertising period, which is in September every year. The options it has identified are:
Option A - Invest £1million 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 £7million.
Garnett's required rate of return on investments is 12.5% and the estimated cash flows for the two options are as follows (in '000s):
|
Year
|
Option A
|
Option B
|
|
1
|
1,000
|
300
|
|
2
|
1,500
|
1,500
|
|
3
|
1,750
|
3,000
|
|
4
|
500
|
3,000
|
|
5
|
|
3,000
|
|
6
|
|
3,000
|
|
7
|
|
2,700
|
|
8
|
|
2,000
|
|
9
|
|
1,500
|
|
10
|
|
1,000
|
Required:
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 £250,000 in the first year, £750,000 in the second and £1,250,000 in the subsequent four years, would your answer to part c) change? How? (Note: production can't be further increased in the future if option B is chosen now)
Question 5:
The table below represents data for the profits, sales, average shop 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 the following:
a) Summarise the distribution of profits of the twenty branches and comment on the results, including identification of any particularly good or poorly performing branches.
b) Identify whether there is evidence that the average number of lines stocked per branch is significantly different from 150.
c) Identify whether there is a significant difference between the profits of two groups of branches, split by the level of sales, with the threshold being £600,000.
d) Based on this sample, provide a 98% confidence interval for the profits of the twenty branches and comment on the outcome.
|
Profit (£000s)
|
Sales (£000s)
|
Size (000s sq. ft.)
|
Lines
|
|
77.5
|
613.9
|
3.2
|
80
|
|
91
|
217.4
|
4.3
|
200
|
|
20.7
|
900.9
|
3.1
|
164
|
|
40.8
|
673.4
|
1.5
|
150
|
|
45.8
|
424.7
|
3.2
|
69
|
|
41.1
|
542.2
|
1.8
|
128
|
|
47.5
|
564.6
|
2.5
|
75
|
|
80.4
|
662.1
|
3.1
|
182
|
|
16.5
|
583.6
|
4.2
|
126
|
|
22.3
|
720.2
|
0.6
|
164
|
|
40.8
|
881.5
|
1.8
|
145
|
|
68.1
|
227.7
|
0.8
|
130
|
|
17.7
|
807.4
|
3.8
|
154
|
|
66.2
|
656.4
|
0.3
|
124
|
|
31.3
|
632.8
|
2.3
|
142
|
|
15
|
548.5
|
5
|
178
|
|
67.8
|
533.6
|
1.5
|
173
|
|
55
|
147.5
|
1.7
|
199
|
|
8.6
|
311.4
|
3.8
|
98
|
|
16.5
|
450.1
|
4.6
|
148
|