Undergraduate Modular Scheme Open Book Examination
Module Name Managing Data
Module Code BMA4003-20 Level 4
Submission Deadline Exam paper release date and time: Sunday 9th June 2024 at 23:59
Submission cut-off date and time: Monday 10th June 2024 at 23:59
Word Count 150 per question (150 x 4 = 600 words ±10%)
Instructions for candidates:
? Attempt ALL FOUR questions using information given therein.
? Each question carries 25 marks.
? All questions are compulsory.
? Students need to show key workings and brief explanation in order to get full marks for each question. Presenting only the result for each requirement without detailed workings would not be enough for you to get full mark. This means you need to show your workings (and Excel formula) in an Excel spreadsheet and the answers need to be presented in a separate Word document.
? Students should use Excel to answer the questions. You need to upload the Excel workings on the “Supporting Excel Spreadsheet” link but the final submission must be in a single MS Word document on the “Assignment 2 – 24 exam (Word – Final submission)” link.
? The word limit maximum 150 words is applied to each question (e.g., total word count is 600+/-10%).
Question 1
Arla Foods is an international cooperative based in Viby, Denmark, and it’s the largest producer of dairy products in Scandinavia and the UK.
Following an audit at the end of the last financial year, they disclosed information regarding the yearly sales of their major product (Nido) as well as online advertising cost for 7 years. They have also taken an inventory of the growth rate of sales revenue of all products for 7 years.
You have been requested as an analyst to evaluate the data as part of the Audit team, using your expertise in the subject, answer question A to D and make a recommendation to Arla Foods based on the information given.
Table: Sales per online advertising cost and number of products per shelf life
Year Online advertising cost Sales Revenues of Nido Growth rate of all products sales
(x, £000) (y, £000)
2018 1.7 368 13%
2019 1.5 340 26%
2020 2.8 665 21%
2021 5 954 16%
2022 1.3 331 -27%
2023 2.2 556 26%
2024 1.3 376 6%
Requirements
A. A. What is a simple linear regression and list three characteristics of the linear relationship. [4 marks]
B. Graph the simple linear regression using the data set above (sales revenue on Y axis and advertising cost on X axis), including the linear regression line, equation on the graph to visually represent the relationship between the variables and the coefficient of determination to measure the goodness of fit. [10 marks]
C. What is the difference between geometric and simple mean. What is the average growth rate or geometric mean of sales of all products after seven years using the data above. [8 marks]
D. Explain the relationship between the advertising cost and sales revenue and make a recommendation to Arla Foods on whether to spend more (£5,000) on online advertising cost based on the information given and results from B. [3 marks]
[Total 25 marks]
Question 2
Saba is a student at Bath Spa University taking the managing data module; she is looking to support her aunt who is looking to set up the first Café for hot drinks on the high street at Birmingham. She has requested your help as a brilliant colleague taking the module as well in establishing the Café.
Requirements
A. The first thing required is to fix a menu and understand the most popular hot drink type amongst shoppers on the high street. Explain to Saba the best data sources to get this information and list three methods that she can use to collect the information. [4 marks]
B. Saba collected the following data using one of the suggested methods. She reproduced the raw data into the table:
Drink type No. of votes
Americano 35
Latte 32
Hot Chocolate 7
Cappuccino 31
Espresso 15
Caffe Mocha 33
Long Black 78
Decaf 45
British tea 14
i. Create a chart to show the number of votes for each hot drink type [4marks]
ii. What is the most popular hot drink? And how much is the relative frequency of the most popular hot drink? [2marks]
C. In addition to the hot drink shop, Saba lives with her father, Amir who is a farmer. Amir has 40 hectares for growing barley and Wheat and has to decide on how much of each to grow.
The cost per hectare for barley is £50 and for Wheat is £20. Amir has budgeted £500.
Barley requires 1 man-day per hectare and wheat require 2 man-days per hectare. There are 40 man-days available. The profit on barley is £100 per hectare and on wheat is £150 per hectare.
i) Formulate the problem as a linear programming problem and graph it, identifying the extreme points (in your graph, can you have variable for barley on the X axis and variable for wheat on the Y axis). [10marks]
ii) Find the number of hectares of each crop that Amir needs to sow to maximise profits and convert the final answers to the nearest whole numbers [5marks]
Question 3
Ruby is a part time customer service operator at a call Centre in Leeds; she is looking to buy a 2-bedroom house worth £300,000 with her partner within the next couple of years and is looking to use money saved over the next 5 years in her savings account to pay 10% of deposit on the house.
She has requested your service as a well-informed data analyst to help estimate how much she would have saved at the end of 5 years if she opens a savings account at HSBC or Barclays. Please use the information provided to assist Ruby in projecting her financial situation.
Ruby is looking to save £ 6,000 annually and will be transferring this amount to her savings account at the end of each year. Currently she earns £ 1000 per month after tax and her major expense monthly is due to transportation to work as her partner covers all other bills with his salary.
Her average cost of transportation is highly dependent on the mode and combination of transportation (bus or train) she has used over the month. You have carried out a survey on 400 respondents to understand the monthly cost of transportation per miles of travel for workers in Leeds to help you estimate her travel cost.
Amount spent on travel per distance
Distance to Workplace (miles)
Amount spent, £ 0.1 to 2.50 2.51 to 6.0 Over 6.0
Below 120 0 0 7
121 – 150 0 22 25
151 – 190 0 85 3
191 – 240 29 75 4
241 – 280 46 47 0
281 – 310 34 8 2
Above 310 7 6 0
Requirements
A. What is the probability for Ruby to spend over £ 190 monthly on transport based on the information in the table if she lives 7 miles away from work?. [4 marks]
B. What is the frequency of respondents living over 2.51 to 6 miles away from their workplace? [3 marks]
C. As to the category of Amount Spent, £, what is the modal group? [3 marks]
D. The savings account at HSBC pays a simple interest rate of 3.5% per annum, Ruby intends to keep all interest in her savings account and is able to save £6000 as planned for the next 5 years, starting from the end of the year 2024., what will be the balance in Olivia’s savings account on Dec 31, 2029? [5 marks]
E. The savings account at Barclays pays a compounding interest rate of 4.5% per annum. Ruby intends to keep all interest in her savings account and is able to save £6000 as planned for the next 5 years, starting from the end of the year 2024. How much will be the balance on Ruby’s savings account on Dec 31, 2029 [6 marks]
F. Based on answers from D and E, what bank will you advise Ruby to open an account with and will she be able to pay the full 10% deposit on the house at the end of 5 years if the valuation on the house does not change? [4 marks]
[Total 25 marks]
Question 4
William has recently been appointed as a analyst in one of the largest brokerage houses in London’s financial district. His company – Rubricks Capital – is currently recruiting financial advisors for the experienced share trading clients and amateur investors. Due to the lucrative salaries and career prospects, the company receives a significantly high number of applications for the advertised posts. Consequently, the HR of Rubricks Capital recently sent out a knowledge test to the applicants after the initial CV screening – in order to interview only the best of the brightest candidates. At this stage of selection, the company invites the highest 30% (the opposite is the lowest 70%) scoring back for the next face-to-face interview. Historical data reveal that the test scores are normally distributed with a mean of 62 with standard deviation of 24.
Requirements
A. Using the normal probability function, what score (to the nearest whole number) must a candidate achieve in order to secure an interview? Show your workings. [3 marks]
B. Rubricks Capital offers free buffet lunch for candidates attending the interview. As a HR analyst, Stan needs to estimate the amount of vegetables to order for the interview day. Sampling suggests that the amount of vegetables taken by each candidate (measured in grams) distributes as the below diagram. Note that each box on the horizontal or x axis indicates 12 grams. For example, 8 (at the starting point of the red line) indicates 80 grams while 16 (the end point of the red line) indicates 160 grams.
i. What is the distribution as demonstrated in the chart above and what is the probability density function of the distribution in the chart? [5 marks]
ii. What is the mean and standard deviation of this distribution, respectively?
[5 marks]
iii. What is the probability of a candidate taking vegetable between 100 – 150 grams? [5 marks]
C. Explain the characteristics of standard normal distribution and normal distribution. [3.5 + 3.5 = 7 marks]
[Total 25 marks]
[Grand Total: 100 marks]
Looking for answers ?