On the 5th of March 2009, the Bank of England (BoE) lowered its main interest rate to 0.5%, 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, HSBC’s Lifetime Tra

Carlos Ribeiro 1

CASS BUSINESS SCHOOL

BS1003 - FINANCIAL MATHEMATICS & BUSINESS STATISTICS

REFERRAL COURSEWORK ASSIGNMENT (July 2014)

Quantitative Methods: Resit 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

 support any answers with the appropriate calculations to arrive at the answer

 include selected printouts of formulae underlying computed values. 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 by 5.00 pm on Friday 15th

August 2014. You will need to submit a Word document with the report (see

instructions above) and an Excel file with the calculations.

Notes:

This coursework is your own (individual) work. Any student found guilty of

plagiarism will be penalised. Standard penalties for late submissions are applicable.

Carlos Ribeiro 2

Question 1: (10%)

A retailer knows the annual demand for one of its product is 500,000 units, the ordering

costs are £40 per order and the average carrying cost per unit is 110 pence. You are

required to:

1. Determine the Economic Order Quantity, the number of orders per year and the reorder period given the data above.

2. Produce sensitivity analysis assuming a change of up to 7.5% up or down on each of the factors individually and on all factors simultaneously.

3. Make a final recommendation to the board of the company, as to the number of units it should include in each order.

Carlos Ribeiro 3

Question 2: (20%)

On the 5th of March 2009, the Bank of England (BoE) lowered its main interest rate to

0.5%, 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, HSBC’s Lifetime

Tracker Standard mortgage rate stands at 4.39%. Avery Bradley is about to buy a house

in the countryside which costs £500,000 and is taking out a 20-year repayment mortgage

for 75% of the acquisition value of the house. Avery is nevertheless worried about the

recent news that interest rates might be about to increase and has asked you to assess his

ability to pay, in case the BoE does indeed increase its interest rate, as his maximum

monthly payment cannot exceed £2,600. You are required to:

1. Calculate the monthly payment Avery will need to meet under current conditions. 2. Assess the effect on Avery’s monthly payments for each quarter percentage point

increase in the BoE rate, up to 4%age points and identify at which rate Avery

would no longer be able to make the monthly payment.

3. Determine the shortest length (to the nearest month) of mortgage Avery could take out if he wanted his monthly payment to be exactly the maximum he could

afford (based on current mortgage assumptions).

Question 3: (25%)

The sales manager for an electronics retailer is trying to determine the sales mix that

maximises the profit for one of its product lines. This product line is sold in three levels

of content: Basic, Medium and High, which sell for £90, £140 and £220 respectively. The

different levels of content of the product use the following inputs:

Product Basic Medium High Max. Available

Direct Material 5 units 8 units 15 units 40,000 units

Labour 2 ½ hours 4 hours 5.5 hours 25,000 hours

Packaging 2 units 4 units 5 units 22,000 units

Other materials 1 ½ units 2 ¼ units 2 units 20,000 units

The costs for the inputs are £15 per hour for Labour and £5, £2 and £1 per unit for Direct

Material, Packaging and Other Materials, respectively. The company is also faced with a

maximum demand of 5,000 units for the basic model, 4,000 for the medium and 3,000 for

the high.

1. 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.

2. How would your answer change if the following happened: a. Price for the Premium model could be increased to£250; b. Maximum available Direct Material could be increased to 50,000; OR c. Labour cost was increased to £20 per hour.

3. Explain the reasons for the different answers in part 2c above. 4. Write a report with a recommended production and marketing plan for the

company.

Carlos Ribeiro 4

Question 4: (10%)

Allen plc., a sports apparel manufacturer with a cost of capital of 11.25%, is looking to

reduce costs in its manufacturing activities and is considering opening a new facture in

one of two countries. Allen is able to raise £2,100,000, which it believes will be enough

to build and equip the factory in either country, so it needs to choose between the two

investments, which are expected to generate the following cash flows:

Year Country A

(in £’000)

Country B

(in £’000)

1 300 800

2 550 800

3 1,250 800

4 2,300 1,700

Make a recommendation to Allen, plc. as to which project it should implement,

including an indication of whether that decision should be different in case the cost of

capital changes in the near future.

Question 5: (25%)

The table on the next page 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.

1. Summarise the distribution of profits of the twenty branches and comment on the results?

2. Is there evidence that the average number of lines stocked per store is significantly different from 78?

3. 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?

4. Based on this sample, provide a 99% confidence interval, and comment on the outcome, for the profits of the twenty branches.

5. Is there evidence of association between the profit and the other variables? 6. Develop three regression models to predict the profit based upon each of the

other factors (variables) individually. Which of these is best? What are the

limitations of your best model? How can you improve this analysis?

Carlos Ribeiro 5

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