Aim: To analyse a set of data, and write a memo, identifying and explaining your insights into the operation of Todd Restaurants.
Learning Objectives:
In the process of this assessment task you will:
A) Learn to plan, schedule and execute project tasks with a view to improve your personal productivity;
B) Gain an understanding of some typical issues related to the operation of a medium size business;
C) Use the functionality of Microsoft Excel 2007/2010 to manipulate the sales and marketing data, analyse it and visualise it in tabular and chart form; and write a brief memo of your business observations and recommendations.
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.
A) Review the data that Joshua has provided you from the data warehouse in Data.Xlsx file. Rename the spreadsheet as: Lastname_Firstname.xlsx.
B) 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’)
• Calculate the number of years each restaurant is operating in the ‘Store Age (years)’ column. (you need to use a formula that calculates (‘Data Collection Date’ – ‘Store Opening Date’)/365))
• Calculate Total, Mean, Median, Maximum, Minimum, Range and Standard Deviation for all relevant columns, at the end of the data in the ‘Information’ worksheet.
C) Using the Information worksheet, Joshuaneeds to have a summary reportfor each region. The report should includethe following:
i) Total Annual Sales
ii) Total number of Seats
iii) Average Annual Sales
iv) Average Store Age (years)
v) Total number of Restaurants
vi) 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.
D) Using the Information worksheet, Joshua also needs to have some analyses report for the following questions:
- Which region has the highest ‘Annual Store Sales’ for ‘Really Big’ size restaurants
- Which region has the lowest ‘Annual Store Sales’ for ‘Really Big’ size restaurants
- What is the total number of seats for ‘Medium’ size restaurants in the highest ‘Annual Store Sales’ region?
- What is the total number of seats for ‘Large’ size restaurants in the lowest ‘Annual Store Sales’ region?
- What is the best annual sale restaurant at the worst region?
- What is the worst annual sale restaurant at the best region?
- What is the lowest age of the restaurant in the highest ‘Annual Store Sales’ region?
- What is the highest age of the restaurant in the lowest ‘Annual Store Sales’ region?
E) Prepare a Report in Microsoft Word that includes:
i) An introduction
ii) The results from the Excel work sheet. You need to follow the exact questions sequence and copy and paste the necessary analyses (summary tables and graphs) from the excel file into the report.
iii) Discussion of your observations and recommendations.In your discussion include also which units and regions are performing extremely well, which are performing moderately well, and which are underperforming.