To analyse a set of data, and write a memo, identifying and explaining your insights into the operation of Todd Restaurants.
Case Background:
ToddRestaurantsoffer national chain of full-service, casual-themed restaurants in Australia. You have been offered the job of vice president of operations for Todd Restaurants. During your first week on the job, Joshua Todd, your boss and CEO of the company, has asked you to provide an analysis of how well the company's restaurants are performing. Specifically, he would like to know which units and regions are performing extremely well, which are performing moderately well, and which are underperforming.
Assignment Instructions
The CEOasksyou to identify where to spend time and focus efforts to improve the overall health of the company.
1. Review the data that Joshua has provided you from the data warehouse in Data.Xlsx file. Rename the spreadsheet as: Lastname_Firstname.xlsx.
2. Use the Informationworksheet for your calculations and analysis to provide results for each of the following tasks:
- Use 'Restaurant Size by Sales' column in 'Information' worksheet to categorize each restaurant size as 'Small', 'Medium', 'Large' or 'Really Big' according to their 'Annual Store Sales'by using the following table. (You need to use VLOOKUP function for this; and the table needs to be stored in a new worksheet: 'Size Table')
|
Annual Sales Levels
|
Size Label
|
|
Less than $1,000,000
|
Small
|
|
$1,000,000 and more
|
Medium
|
|
$2,000,000 and more
|
Large
|
|
$3,000,000 and more
|
Really Big
|
- Determine the number of years each restaurant is operating in the 'Store Age (years)' column. (you need to use a formula that Determines ('Data Collection Date' - 'Store Opening Date')/365))
- Determine Total, Mean, Median, Maximum, Minimum, Range and Standard Deviation for all relevant columns, at the end of the data in the 'Information' worksheet.
3. Using the Information worksheet, Joshuaneeds to have a summary reportfor each region. The report should includethe following:
a. Total Annual Sales
b. Total number of Seats
c. Average Annual Sales
d. Average Store Age (years)
e. Total number of Restaurants
f. Total number of advertising Restaurants
- Use the table in the 'Summary Report' worksheet for your calculations. For this report you should not use any Pivot Table analyses. Instead you need to use functions such as SUMIF, AVERAGEIF, COUNTIFS, etc...
- The report table needs to be sorted by ' Total Annual Sales' column in descending order.
- All the cells in the report table need to be formatted appropriately.
4. Using the Information worksheet, Joshua also needs to have some analyses report for the following questions:
a. Which region has the highest 'Annual Store Sales' for 'Really Big' size restaurants
b. Which region has the lowest 'Annual Store Sales' for 'Really Big' size restaurants
c. What is the total number of seats for 'Medium' size restaurants in the highest 'Annual Store Sales' region?
d. What is the total number of seats for 'Large' size restaurants in the lowest 'Annual Store Sales' region?
e. What is the best annual sale restaurant at the worst region?
f. What is the worst annual sale restaurant at the best region?
g. What is the lowest age of the restaurant in the highest 'Annual Store Sales' region?
h. What is the highest age of the restaurant in the lowest 'Annual Store Sales' region?