BPP Coursework Cover Sheet

Please use the table below as your cover sheet for the 1^st page of the submission. The sheet should be before the cover/title page of your submission.

Mathematics and Business Finance

Coursework Assessment Brief

Submission mode: Turnitin online access

1.General Assessment Guidance

• Your summative assessment for this module is made up of this coursework

submission which accounts for 100% of the marks

• Please note late submissions will not be marked.
  • You are required to submit all elements of your assessment via Turnitin online access. Only submissions made via the specified mode will be accepted and hard copies or any other digital form of submissions (like via email or pen drive etc.) will not be accepted.
  • For coursework, the submission word limit is 2,500 words. You must comply with

the word count guidelines. You may submit LESS than 2,500 words but not more.

• Do not put your name or contact details anywhere on your submission. You should only put your student registration number (SRN) which will ensure your submission is recognised in the marking process.
• A total of 100 marks are available for this module assessment, and you are required to achieve minimum 40% to pass this module.
• You are required to use only Harvard Referencing System in your submission. Any content which is already published by other author(s) and is not referenced will be considered as a case of plagiarism.

You can find further information on Harvard Referencing in the online library on the VLE. You can use the following link to access this information: http://bpp.libguides.com/Home/StudySupport

• BPP University has a strict policy regarding authenticity of assessments. In proven instances of plagiarism or collusion, severe punishment will be imposed on offenders. You are advised to read the rules and regulations regarding plagiarism and collusion in the GARs and MOPP which are available on VLE in the Academic registry section.
• You should include a completed copy of the Assignment Cover sheet. Any

submission without this completed Assignment Cover sheet may be considered invalid and not marked.

2.Assessment Brief

You work as a junior accountant for Metro Ltd. Your line manager has been pleased with your work and is looking to assign you more responsibilities. He has asked you to complete a series of tasks to test what you can do.

All your solutions are to be done on one excel file. Please download the “MBF Summative data.xlsx” excel file from the summative assessment area of the Hub. Please complete your details on the first tab of the spreadsheet and then answer the questions. For the written tasks (Tasks 1, 2 and 6) please just type your answer into the relevant tab. For the remaining tasks (Tasks 3, 4, 5, 7 and 8) the data is already pre-loaded onto the relevant excel tab and you need to use Excel to complete your answer below the data given. Rename your excel file as “student number – MBF Summative.xlsx” e.g. “BP0123456 – MBF Summative.xlsx”. Upload this file via the link on the Hub before the submission deadline

a)

i)  Reno Ltd invests £150,000 at 2.5% compound interest for three years. What will be the value of the investment after three years?(1 mark)

ii) Mukesh Ltd has received £65,661 after investing for 6 years at an annual interest rate of 6.5%. What was the original amount they invested?(1 mark)

iii)   Alpha Ltd invests £45,000 and after 9 years it has received £62,300. The interest rate the bank applied to the deposit changed each year. Using the geometric mean method, calculate the average annual interest rate.(2 marks)

b)  Link Bank is deciding whether to grant a loan application to a new customer. As part of the analysis, calculate the percentage change for each of the headline numbers on the table below.

c) The government has released the latest retail price index which suggests inflation was 1.62%. A typical basket of food at the supermarket this week costs £52.67. Calculate the cost of that typical basket:

i)  a year ago

ii)  one month ago (assuming inflation occurs evenly throughout the year)

a)Kwik supermart has ordered the following supplies over the last year from various suppliers:

A new supplier has approached them offering them the same items at 11% less than the overall average price per unit they have paid in the previous year. What will be the average price per unit charged by the new supplier?(3 marks)

b)    Expand and simplify the following expression: (6a – 5b) (2a + 7b)

c)     Factorise the following expression 25a³b² +10a²b³-15ab

The data in Sheet “Task 3 Data” of the Excel file “MBF Summative data.xls” relates to the weight (in grams) of a sample of 80 items produced by a machine.

You are required to:

a)    Aggregate this data into a suitable frequency table using the COUNTIFS function and using an interval of 1.5 

b)    Plot a histogram of the data..

c)     Calculate the statistics below for the given items:

1. Range
2. The mean
3. The median
4. The mode
5. The standard deviation

d)  The production manager is concerned that the machine is not working properly as the items of output vary in weight. The item should ideally weigh 25 grams and a variance of 3 grams of each side is acceptable. Comment briefly if the production manager is right to be concerned. Use the statistical data calculated in part ‘c’ above to support your answer. Total: 17 marks

Tourists seeking a luxury tour on the ‘Palace on rail’ in India can opt for either the ‘Luxury service’ or the ‘Maharajah service’. The manager of the ‘Palace on rail’ has produced the following table to show the bookings she received last season:

The data is also provided in the “MBF Summative data.xlsx” file under the “Task 4 – Data” tab.

Required;

a)Produce a stacked bar chart to show the number of bookings by home country and type of booking

b)Produce a pie chart to show the number of bookings as a whole by home country. Ensure the data labels on the pie chart are expressed in percentage form to 2 decimal places

c)Interpret your results

A major car manufacturer has collated data on three of its main competitors – BMW, Cadillac and KIA. An extract of the data is provided in the “MBF Summative data.xlsx” file under the “Task 5 – Data” tab. Using this data, complete the following tasks: 

ai) Use the "COUNTIF" function to count the number of cars manufactured by Cadillac

ii) Use the "COUNTIFS" function to determine the number of Kia cars that are sold only in Russia.

(2 marks) bi) Using the VLOOKUP function, determine what the body type is for the 8-Series model manufactured by BMW

ii) Using the VLOOKUP function, determine what the production years were for the STS model manufactured by Cadillac

c) Apply a filter to the data set and select filters to show only Large MPV`s produced by Kia and display it separately.

(2 marks)

di) Copy all the main headings and the details for the first five Kia cars on the list. Transpose this data.

(1 mark)

Using this data, conduct the following search:

ii) Run a "HLOOKUP" search to determine the body type for the Kia Pride model

a) Explain 2 differences between bonds and shares.

b)Homeshare rents out various newly built properties to low-income families. It has recently rented a flat to a client for £9,000 per year.

A discount rate of 4% is used by Homeshare. Calculate the present value of this rental income assuming it is expected to continue in perpetuity and there will be:

i) No growth in annual rental income

ii) 1.5% growth in rental income. 

c)Explain what is meant Market Risk and by Specific risk. How can an investor reduce these risks?

Supera Ltd are looking to invest in a new 4 year project with an initial outlay of

£575,000. The project is in a line of business where Supera Ltd. has limited previous experience. All cash inflows occur at the end of each year.

i) Calculate the IRR of the project by discounting the cashflows by 4% and 8% and then applying the IRR formula – perform your workings on excel including formulas. A copy of the data above is provided in the “Task 7 - Data” tab of the Excel file “MBF Summative data.xlsx” . There is also a present value table on the last tab of the same excel file.

ii) Check your answer using the IRR function in excel 

iii)Supera Ltd has a set hurdle rate of 7.5% for projects. Assess whether or not it should accept the project based on your calculations and other information provided.

iv) Explain one advantage and one disadvantage of the IRR technique of investment appraisal.  (4 marks) 

Task 8

Goldie clothes are planning to expand their clothing business by opening a factory overseas. The initial investment will be £12,500,000. It is expected to generate net revenues of

£6,500,000 each year if the project goes ahead. Additional costs for the project will be

£3,000,000 per year. [Cashflows occur at the end of each year]

The company’s weighted average cost of capital is 11% and the project will have a lifetime of 5 years.

(a) Calculate the net present value (NPV) of the above proposal showing your workings in an excel spreadsheet including formulas. You should complete your answer on the “Task 8 – Data” tab of the “MBF Summative data.xlsx” excel file.

(b) Calculate the Payback period of the above proposal in years and months. Round up to the next month.

c) Describe two advantages of using the NPV method over the Payback method and conclude whether it is still worth using the Payback technique as part of the investment appraisal process.

3.Marking Guide

Assessment Marking Scheme (Student Version)

The assignment is marked out of 100. The following table shows the mark allocation and the approach required.