Assignment 2: Using spreadsheet Applications for Data Analysis
Due: Week 13 – Week of April 3 (Before class)
In this assignment you will familiarize yourself with spreadsheet applications (Excel) and their use in data interpretation. You can use basic MS Excel functions such as (SUM; AVERAGE; COUNTIF; MEDIAN; MODE) as well as charts; PivotTables; PivotChart to analyze the data provided to you.
The assignment will be graded based on the accuracy and completion of the allocated tasks as well as the details/effort/usefulness in creating charts and reports.
Submission should be done before the deadline using the Assignment link on Sakai. You are required to submit one MS Excel file and one MS Word file (with the well-formatted report on your excel findings answering the posed queries) per group through the course website. The Word file must include a title page specifying the names of the group members.
Use the following convention to name the files that you submit (MBAB5P09_Section#_GroupName_ DataAnalysis). For instance, group J will name the file as: 5P09Section2_GroupJ_DataAnalysis.
Choose one team member for uploading the files. Verify that your assignment files are submitted properly through the course website. Late submissions and wrong files will automatically receive a grade of zero
The assignment will be graded on a relative scale.
Based on the accuracy and completion of the allocated tasks as well as the details/effort/extra analysis/usefulness of analysis in creating charts and reports.
(Accuracy in analysis, Explanation of assumptions and results, Extra effort put in the assignment with charts and additional consideration, attention to detail in analysis, formatting and presentation of report, Clarity in report, Depth in report)
Case: PittsStop – Too Much Information
You have just landed the job of intern for the vice president of operations for The Pitt Stop Restaurants, a national chain of full-service, casual-themed restaurants. During your first week on the job, Suzanne Graham, your boss, has asked you to provide an analysis of how well the company’s restaurants are performing. Specifically, she would like to know which units and regions are performing extremely well, which are performing moderately well, and which are underperforming. Her goal is to identify where to spend time and focus efforts to improve the overall health of the company.
- Create a formal presentation of your findings from the data provided to you. Review the data file TooMuchInformation_Data.xlsx and determine how best to analyze and interpret the data. With the data that is provided, using a Pivot Tables and Pivot Charts, prepare a report that summarizes:
- Performance of restaurants according to geographical area
- Should underperforming restaurants be closed or sold?
- Should high-performing restaurants be expanded to accommodate more seats?
- Should the company spend more or less on advertising?
- In which markets should the advertising budget be adjusted?
- How are The Pitt Stop Restaurants performing compared to the competition?
- How are units of like size performing relative to each other?
- Review quality of the data and give examples from the data showing the kind of information the company might be able to use to gain a better understanding of its customers and performance. Include the types of data quality issues the company can anticipate and the strategies it can use to help avoid such issues. Develop a report for executives discussing the importance of high quality information and how low quality information can affect business.
Note: Use separate worksheet to do the analysis needed for each query. Label each worksheet appropriately to indicate the query you are analyzing.