Assignment

Your coursework consists of the following four tasks based on Cocoa-Delight case study and data supplied in moodle as resit course work data.

The Case Study

Rawchoc is a chocolate and bakery company based in the UK. They have been operating very successfully in the snacks industry for many years. Catering for consumers' needs and demand is the key to their success and sustainability. They collect consumer data on a regular basis and analyse it to identify the growing trend in snacks consumption.

Recently they have identified that the British population is increasingly choosing healthier snacks with low sugar content. They have also established that this is strongly correlated with an Olympic spirit. To exploit this new trend Rawchoc is planning to launch a new product following the success of ‘Team GB' at the Brazil Summer Olympics. Rawchoc's innovation and product design team plan to market a chocolate bar for keeping weight under control, with average calories of 197 per unit.

Rawchoc has hired you for your analytical insights. Your job is to collate and analyse a range of data to recommend a competitive product for the healthy snack food market.

• For data see Excel file in "Resit Coursework" folder

Task 1:

Using the food table in the dataset provided, prepare a list of alternative snacks i.e. direct competitors to the new product. You are required to:

1. Sort, list & format the dataset into appropriate categories related to food groups e.g. chocolate-based snacks, baked food, healthier snacks etc.

2. Prepare appropriate graphs displaying & comparing calories, of the categories.

3. Calculate summary statistics such as the mean, median and standard deviations of the categories.

4. Give a brief critical discussion of the summary statistics from part 3.

5. What advice would you give for weight-loss purposes - which snacks have to be chosen? Is the chocolate bar going to be competitive in the market?

Task 2:

Rawchoc is now interested in trying to estimate how sales are influenced by the price of their products. To do this, the company randomly chooses 10 UK cities and offers the candy bar at different prices and run a regression analysis on price and sales data.

City             Price (£)        Sales
London          1.46             98
Birmingham    2.55             87
Manchester     2.80            56
Derby             2.10            80
Edinburgh       3.80            51
Glasgow         4.10            38
Cardiff            3.00            72
Belfast            4.60            31
Portsmouth     3.10            55
Bristol             3.50            45

1. Using sales as the dependent variable, conduct linear regression analysis*.

2. Generate the scatterplot.

3. Write the regression equation using the results of the excel table.

4. Determine & interpret the slope and the intercept.

5. Determine what percentage of the total variation in candy bar sales is explained by prices?

*[Perform, using Excel, Data Analysis Pack, Regression function, the regression analysis.]

Task 3:

A manager in RawChoc needs to determine the relationship between the company's advertising expenditure and its sales revenue. If the relationship is a strong one, then the company will be able to forecast the sales revenue for a given advertising expenditure. The following data had been collected over the last nine years.

Using the table below; Answer the following questions:

Using the table above answer the following questions:

1. Using sales as the dependent variable, conduct linear regression analysis*.

2. Generate the scatterplot of the data

3. Write the regression equation that best fits the data.

4. Determine & interpret the slope and the intercept. Give your answer to 2 decimal places.

5. Using your analysis estimate the sales revenue when advertising is:

a. £12,000
b. £16,500

6. Determine what percentage of the total variation in sales is explained by advertising expenditure?

*[Perform, using Excel, Data Analysis Pack, Regression function, the regression analysis.] 

Task 4:

Write a summary report based on the results of the three tasks.

Guidelines for completing task:

You are to follow the procedures, techniques and Excel based & long hand formula taught in seminars to answer questions related to categorising, tabling, central tendency, variance and regression analysis calculations. You will also need to perform web-based research to identify sugar content of chocolate-based snacks item. Theories delivered in lectures will help you to answer descriptive questions. If you need further support please contact your seminar tutors.

Attachment:- Data.rar