Instruction file and Excel Workbook has been attached. Please read the instructions throughly.

Marketing Research

Data Analysis Assignment

Requirements

You are responsible for an analysis of the profitability of the specific industry identified in your Section’s Data Set. Your Data Set can be found within your associated Evaluations folder, under Data Analysis Assignment.

For this assignment you are required to:

1. Organize the data in various ways as necessary to understand the trends. Be sure to create various worksheets to showcase your analysis at each stage. More details on how to Organize and Analyze can be found below.

2. Analyze the data. Your analysis should outline the trends that are occurring and identify any relationships found in the data.

3. Identify which category/segment you consider to be most successful, and provide rationale based on the data.

4. Determine what the better predictor of success on Gross Sales.

5. Provide a forecast for the next year which will include what you think the gross sales will be, and what Company will be the leader in the market, based on your analysis.

This assignment will be completed individually.

Step 1: Organizing and analyzing

The first step is to organize the data, so that you can analyze it and identify trends. You will organize the data using an Excel workbook; remember as noted above your Data Set is available on eConestoga under Evaluations > Data Analysis Assignment.

To help in your analysis, consider the following tips:

1. You will want to use separate worksheets to organize the data into various segments. Be sure to include the following:

   1. 1. 1. 1. Gross Sales by Segment

            2. Gross Profit by Segment

            3. Overall Market Share of the Industry as a whole

            4. Sales and Market Share by Segment

            5. Market and Segments

            6. Analyzing if there are any trends in the App Ratings for each restaurant

            7. Which App Rating is a better predictor at receiving the most Gross Sales *Hint this is a Pivot Table

            8. Forecast for the next year what the Sales and Market Share may be for each Restaurant

2. Calculate the market share of each Restaurant, and the segment share of each segnment. This should be done for each year.

3. Calculate the relevant trends; this will include growth/decline of sales, of profit, of market share and of segment share. Trends must be calculated year by year (e.g. how much did sales increase year over year). Note:

   1. Trends based on $ (eg sales) are best indicated by % change, not by $ value (eg $32,600 sales to $33,000 is a 1.2% increase).

   2. Trends based on % (eg market share, segment share) are best indicated by point change (eg difference from one year to the next; 32.6 % share to 33.0% share is a .4 increase).

   3. Analyze trends overall (profit, sales), and also by genre and studio.

4. Be sure to use pivot tables to help you identify trends. For example, using pivot tables to evaluate the relationship between App Ratings and Sales when determining which is the better predictor of success.

Step 2: Preparing the Report

The report should be no more than 7 pages in length, excluding your appendices (that means 4 pages of insightful comments). Please ensure that your name, the title of the project and the date are clearly visible on the Executive Summary.

This assignment will consist of 2 submissions:

1. Your report, as a PowerPoint deck. No pdfs will be accepted.

2. A copy of the Excel file that includes all your organization and manipulation of the data.

NOTE: Assignments must be submitted in both ways, by the beginning of class on the due date. No assignments will be accepted after the due date/time.

REPORT FORMAT

1. Executive Summary1 (1 page)

2. Summary of Key Trends (max 3 pages)

   1. 1. 1. 1. 1. This will be a visual representation of the trends, with some explanatory/descriptive comments.

1. Conclusions (2 page)

This will identify what segment you think is most successful and provide rationale for that. Think about what trends you are seeing, are there any occurrences in the marketplace that could have resulted in these trends – this is where you need to sell me on your rationale.

1. Forecast2 (1 page)

2. Appendices3

1The Executive Summary should provide a one page visual overview of the key points you have made throughout the report, and should summarize your conclusions and forecast. This should also include typical cover page information, like student names, student numbers, title of report, instructor name, due date. This should be professional in appearance, and attractively designed.

2The forecast should be comprehensive and estimate what you think gross sales will be.

3The appendices will include anything you think would be useful to support the Summary of Key Trends and Conclusions. There must be at least 2 pivot tables included in the appendices, and at least 3 trend calculations. For example, you might include a graph of the change in Gross Sales, year over year, in the Summary of Key Trends. Then, an Appendix would include the relevant data table showing the Gross Sales, by year, and the % change.

Additional Comments

1. You are expected to use a clear and accurate writing style. Errors (including both spelling and grammatical) will be noted and the grade for the report will be reduced for excessive errors.

1. Do not include an assessment of any information about these movies other than what is provided in the data set. Do not do any secondary research. The data is made up and does not represent the real movie industry, so secondary research and personal opinions will not be relevant.