Assignment Details and Instructions. 

You have recently been employed by Marks & Spencer, a major UK high street retailer, as part of the development of a business analytics function within the company. Marks & Spencer are aware of the concepts of ‘big data’ and ‘analytics’ but they are not particularly sure how it can benefit their business.

You need to demonstrate to the company’s regional directors, how analytics can help them to better understand their business and facilitate informed decision-making.

You have been provided with sales data, in a CSV file, for the trading period 01/01/20 to 31/03/20 inclusive. The sales data is from nine separate M&S stores in the UK. Daily sales are recorded at the departmental level: Food, Homeware and Clothing & Footwear. 

Additional information is also available: 

• Store code

• The size of each department in each store to aid comparative analysis

• Running of major sales promotions in a particular department for each trading day. 

• Daily capacity of food distribution centres in revenue terms, together with distribution costs to each store per £1 of food sales revenue (food sales revenue is used as a proxy for the amount of food actually transported).    

You have been asked to perform some analyses on this data set and develop analytics functionality for the use of the regional directors. Specifically:

1. Inspect and clean the dataset plus comment upon the overall validity of the sales data.

2. Develop and demonstrate an Excel-based dashboard to summarise the main points of interest from the data set and allow further detailed data enquiries.

3. Utilise Monte Carlo simulation to perform a risk analysis of profitability levels in the Aberdeen Homeware department (Note: the analysis should assume overall gross profit margins between 60% to 70% on revenue sales. The department also makes a daily contribution to overheads of £4,250. Use non-promotion sales only for your simulation). 

4. Use optimisation processes to minimise the transportation costs of food from the M&S northern distribution centres (Note: the distribution centres must be able to deliver, at least, the equivalent of maximum non-promotion day sales to each store).    

5. Demonstrate a critical awareness of the impact of the data.

Further Details

Students will be penalised if they copy material or re-use the work of others. Your work must be written in your own words.  Please refer to the university’s regulations for more details on plagiarism (http://www.celt.mmu.ac.uk/plagiarism/index.php).

Additional Information

1. The tasks will be performed and presented in Excel 2016 or 2019.

2. Do not get too technical. Think about your ‘audience’; what will they want to know? What will lose their attention? Remember the KISS principle: Keep It Sophistically Simple.  

3. Use notes on cards when you recording your presentation if you are nervous and/or you have English as a second language. In the latter case, you are not being judged on your standard of English but your ability to get the salient messages across.

4. Please note that the screen recorded may not optimally display your dashboard. In general, this can be solved with scaling although overly complex dashboards will suffer.

5. Please also note that the unit is Windows OS based, as is the presentation. Be careful if using a Mac OS therefore in terms of compatibility and functionality. 

6. No research outside the analysis of the data provided is expected. No reference needs to be made to academic literature and no ‘extra’ research about M&S and its operating environment is required.