The objective of this project is to explore applying data analytics tools to address a business...

 

Transcribed Image Text:

The objective of this project is to explore applying data analytics tools to address a business question/problem, and communicate insights derived from your interpretation of your analytics results. Please review the 'Introduction to the Analytics Mindset' page in the Week 10 Module for an overview of data analytics, its impact, and the 4 steps/phases of the analytics mindset. This project covers steps 3 (apply appropriate data analytics techniques) and step 4 (interpret and share the results with stakeholders). There are two parts to the project. The following is the case information and project requirements for Part 1: Background Information: A global big box retailer, Big Deals (BD), has asked your project team to assist in optimizing their product line and revenue stream. BD was one of the first major players in its industry and has grown into a market leader, typically with at least 50% of the market share. BD offers products in the following categories: Audio: car stereos and receivers Cameras: point and shoot, single-lens reflex (SLR) and video Computers: desktops, monitors, notebooks and tablets TV: LCD, LED, plasma and projection Video players: Blu-ray and DVD BD operates in most countries around the world including the following countries: Argentina, Australia, Brazil, Canada, Chile, China, Colombia, Egypt, France, Germany, India, Italy, Japan, Mexico, the Netherlands, Pakistan, Philippines, Russia, South Korea, Spain, Switzerland, United Kingdom, United States and Venezuela. BD is headquartered in the United States and management gathers competitive sales information by product type at the domestic level in the US and then collectively for all countries. The US is BD's largest market by about six fold. Below is an overview of global and US sales for the last three years. Global US 2017 sales $1,660,149,506 336,684,098 2018 sales $ 1,657,576,955 336,273,102 (Decrease)/ increase (0.15)% (0.12)% 2019 sales $ 1,537,691,760 363,796,103 (Decrease)/ increase (7.23)% 8.18% With the entry of numerous on-line retailers, BD is losing market share and its margins are shrinking. BD would like you to analyze their situation and recommend a strategy to get them back on track. Over the next few weeks, your project team works closely to interview key company personnel, analyze operations and gather data (both corporate and competitive) for further analysis. At the conclusion of the Analytics mindset case studies - Big Deals © 2018 Emst & Young Foundation (US). All Rights Reserved. SCORE No. MM4216 1 assessment, you came up with a series of questions to ask to gain insights from company data (this represents Step 1 of the analytics mindset). The questions are listed below: 1. How do the sales of each product compare with forecasts? 2. How do the sales of each product compare with prior year sales? 3. How do the sales of each product compare with the competition? 4. How do the sales of each product compare with other product sales? 5. Are there any poorly performing products in high-performing countries? 6. If there is a poorly performing product in a high-performing country, how is this product performing compared with the sales in other countries and the competition? Required: Use the BigDeals Sales Data.xlsx file provided in your assignment link. Use Microsoft Excel analytics tools (i.e. pivot tables, pivot charts, etc.) to gain insights and prepare responses for each of the six questions identified. Show the analysis/analyses for each question in separate worksheets. It is up to you to decide which data points to use to address each question. You do not have to use every data point on your project. The following is a simple example of a pivot table and chart, and related response to Question 1 based on the 2019 sales data alone. S C Ove KD 112 www. op Mantan Pom TF Revers SU T 20 21 T 23 and Sa ISTENIA 1300182287 (1860 1 151458 U 31/17 TIPOL T 1 S 121815 1423 3259 149952 121314 27 100% 1 1 17012 GU 3407792 150 01 120 317 12941517 117 131501448 1306046727 175004 1 S 112515 31209 224 2014 SA 342 14/5 2450455186 2554 T 48 Z 31542 110227 34115066 213653254 PivoTable Fields Analytical mindset case studies - Big Deals Ⓒ 2016 Emst & Young Foundation (US). All Rights Reserved. SCORE No. MM4216 ADRE Ma Sample response: Based on a comparison of sales to sales forecasts for the year 2019 (see Sales to Forecast worksheet), only tablets are meeting or exceeding sales forecasts. All other products fall short of their forecasts. However, the most concerning deviation appears in DVD players, which are significantly missing sales forecasts. 2 Prepare your responses to each question identified above in a Microsoft Word document, using complete sentences and appropriate references to the analysis (analyses) that support your answers. Provide a brief summary of the insights you've gained from your analyses and recommend a response strategy for BD. Submit your Word document and accompanying Excel file containing your analyses on Canvas. Your project will be graded based on the appropriateness of your analyses in addressing each question, and on drawing the appropriate conclusions based on the analytics results. The objective of this project is to explore applying data analytics tools to address a business question/problem, and communicate insights derived from your interpretation of your analytics results. Please review the 'Introduction to the Analytics Mindset' page in the Week 10 Module for an overview of data analytics, its impact, and the 4 steps/phases of the analytics mindset. This project covers steps 3 (apply appropriate data analytics techniques) and step 4 (interpret and share the results with stakeholders). There are two parts to the project. The following is the case information and project requirements for Part 1: Background Information: A global big box retailer, Big Deals (BD), has asked your project team to assist in optimizing their product line and revenue stream. BD was one of the first major players in its industry and has grown into a market leader, typically with at least 50% of the market share. BD offers products in the following categories: Audio: car stereos and receivers Cameras: point and shoot, single-lens reflex (SLR) and video Computers: desktops, monitors, notebooks and tablets TV: LCD, LED, plasma and projection Video players: Blu-ray and DVD BD operates in most countries around the world including the following countries: Argentina, Australia, Brazil, Canada, Chile, China, Colombia, Egypt, France, Germany, India, Italy, Japan, Mexico, the Netherlands, Pakistan, Philippines, Russia, South Korea, Spain, Switzerland, United Kingdom, United States and Venezuela. BD is headquartered in the United States and management gathers competitive sales information by product type at the domestic level in the US and then collectively for all countries. The US is BD's largest market by about six fold. Below is an overview of global and US sales for the last three years. Global US 2017 sales $1,660,149,506 336,684,098 2018 sales $ 1,657,576,955 336,273,102 (Decrease)/ increase (0.15)% (0.12)% 2019 sales $ 1,537,691,760 363,796,103 (Decrease)/ increase (7.23)% 8.18% With the entry of numerous on-line retailers, BD is losing market share and its margins are shrinking. BD would like you to analyze their situation and recommend a strategy to get them back on track. Over the next few weeks, your project team works closely to interview key company personnel, analyze operations and gather data (both corporate and competitive) for further analysis. At the conclusion of the Analytics mindset case studies - Big Deals © 2018 Emst & Young Foundation (US). All Rights Reserved. SCORE No. MM4216 1 assessment, you came up with a series of questions to ask to gain insights from company data (this represents Step 1 of the analytics mindset). The questions are listed below: 1. How do the sales of each product compare with forecasts? 2. How do the sales of each product compare with prior year sales? 3. How do the sales of each product compare with the competition? 4. How do the sales of each product compare with other product sales? 5. Are there any poorly performing products in high-performing countries? 6. If there is a poorly performing product in a high-performing country, how is this product performing compared with the sales in other countries and the competition? Required: Use the BigDeals Sales Data.xlsx file provided in your assignment link. Use Microsoft Excel analytics tools (i.e. pivot tables, pivot charts, etc.) to gain insights and prepare responses for each of the six questions identified. Show the analysis/analyses for each question in separate worksheets. It is up to you to decide which data points to use to address each question. You do not have to use every data point on your project. The following is a simple example of a pivot table and chart, and related response to Question 1 based on the 2019 sales data alone. S C Ove KD 112 www. op Mantan Pom TF Revers SU T 20 21 T 23 and Sa ISTENIA 1300182287 (1860 1 151458 U 31/17 TIPOL T 1 S 121815 1423 3259 149952 121314 27 100% 1 1 17012 GU 3407792 150 01 120 317 12941517 117 131501448 1306046727 175004 1 S 112515 31209 224 2014 SA 342 14/5 2450455186 2554 T 48 Z 31542 110227 34115066 213653254 PivoTable Fields Analytical mindset case studies - Big Deals Ⓒ 2016 Emst & Young Foundation (US). All Rights Reserved. SCORE No. MM4216 ADRE Ma Sample response: Based on a comparison of sales to sales forecasts for the year 2019 (see Sales to Forecast worksheet), only tablets are meeting or exceeding sales forecasts. All other products fall short of their forecasts. However, the most concerning deviation appears in DVD players, which are significantly missing sales forecasts. 2 Prepare your responses to each question identified above in a Microsoft Word document, using complete sentences and appropriate references to the analysis (analyses) that support your answers. Provide a brief summary of the insights you've gained from your analyses and recommend a response strategy for BD. Submit your Word document and accompanying Excel file containing your analyses on Canvas. Your project will be graded based on the appropriateness of your analyses in addressing each question, and on drawing the appropriate conclusions based on the analytics results.