Assignment 1. Aims: To analyse a set of data (in Microsoft Excel), and write a brief report (in…
Assignment
1. Aims:
To analyse a set of data (in Microsoft Excel), and write a brief report (in Microsoft Word), identifying and explaining your insights into the operation of Todd Restaurants.
2. Learning Objectives:
In the process of this assessment task you will:
Plan, schedule and execute project tasks with a view to improve your personal productivity;
Gain awareness of some typical issues related to the operation of a small-to-medium size business;
Use the functionality of Microsoft Excel to manipulate data, analyse it and visualise it in tabular and chart form; and
Use the functionality of Microsoft Word to write a brief report of your business observations and recommendations.
3. Case Background:
Feastive Restaurants offer a national chain of full-service, casual-themed restaurants across Australia. You have been offered the job of vice president of operations for Feastive Restaurants. During your first week on the job, David Feast, 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.
4. Assignment Instructions:
The CEO asks you to identify where to spend time and focus efforts to improve the overall health of the company.
1. Review and use the data that Mr Feast has provided you from the Festive restaurants data warehouse in the itech1005_2017-27_assign_Data.xlsx file. Rename the spreadsheet as: Lastname_StudentID.xlsx.
2. Use the Information worksheet for your calculations and analysis to provide results for each of the following tasks:
Use 'Restaurant Size (by Sales)' column in 'Information' worksheet to categorise each restaurant size as 'Small', 'Medium', 'Large' or 'Huge' according to their 'Annual Sales' by using the following table. (You need to use VLOOKUP function for this; and the table needs to be stored in the 'Information' worksheet:)
Annual Sales Levels
Size Label
$0
Small
$1,000,000
Medium
$2,000,000
Large
$3,000,000
Huge
Calculate the number of years each restaurant is operating in the 'Restaurant Age (years)' column. Hint: you need to use a formula that calculates (the current date – 'Restaurant Opening Date')/365.25.
Calculate the 'Taxes payable on annual sales' by each restaurant for annual sales using the following information. All restaurants pay a base tax rate of 15% based upon sales and in addition some regions require additional taxes, as the table underneath indicates, and in one case the additional rate depends on the value of sales. Hint: a similar method might be used as for restaurant size but VLOOKUP only works on ordered/sorted lists while other formulae will work for unsorted lists - requires some research
For example a small restaurant in Victoria with $1000 sales must pay $200 total taxes.
Region
Regional Business Tax Rate
Victoria Small – Medium 5%
Large – Huge 7.5%
NSW
7%
Queensland
5%
SA
6%
WA
0
NT
6%
TAS
5%
ACT
7.5%
Calculate 'Cost of Sales ($)' on 'Information' worksheet. Use 'Advertising (% sales)' column in 'Information' worksheet to calculate actual advertising costs for each restaurant with all additional costs of sales calculated using the following table. The percentages applied for costs of sales (aside from advertising costs, which each restaurant decides for itself) are independent of region:
Area
Total Cost of Sales (not including advertising) as Percentage of Sales
City
60%
Metro
52%
Country
56%
For example, a city restaurant with $1000 sales has $600 cost of sales in addition to any advertising costs.
Calculate 'Profit ($)' for each restaurant on the 'Information' worksheet. Profit is a basic calculation of: sales – cost of sales - advertising costs – taxes.
Calculate 'Sales per Seat ($)', 'Sales per square metre ($)', 'Profit per Seat ($)', and 'Profit per square metre ($)' in relevant columns on the 'Information' worksheet.
Calculate the Sum (i.e.Total), Mean, Median, Maximum, Minimum, Range and Standard Deviation for all relevant columns, at the bottom of the dataset on the 'Information' worksheet.
3. Using the 'Information' worksheet, Mr Feast needs to have summary tables for each of (i) region, (ii) area and (iii) restaurant size. The summaries should include the 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 summary tables need to be formatted appropriately.
4. Using the Information worksheet, Mr Feast also needs to have some analyses report for the following specific questions:
a. Which region has the highest 'Annual Sales' for 'Large' size restaurants?
b. Which region has the lowest 'Annual Sales' for 'Large' size restaurants?
c. Which region has the best 'Average Profit' for 'Small' size restaurants?
d. Which region has the worst 'Average Profit' for 'Small' size restaurants?
e. Which area has the lowest 'Profit per square metre' for 'Medium' size restaurants?
f. Which area has the highest 'Profit per square metre' for 'Medium' size restaurants?
g. What is the total number of seats for 'Medium' size restaurants in the region with highest total 'Annual Sales'?
h. What is the average floorspace for 'Huge' size restaurants in the region with highest 'Annual Sales'?
i. What is the restaurant with the best 'Annual Sales' at the worst region?
j. What is the restaurant with the worst 'Annual Sales' in the best region?
k. What is the restaurant with the lowest 'Age' in the highest 'Annual Sales' area?
l. What is the restaurant with the highest 'Age' in the lowest 'Annual Sales' area?
m. What is the most profitable restaurant with no advertising expenditure?
n. What is the least profitable restaurant with no advertising expenditure?
You should use Pivot Tables for these analyses – include these on the 'Pivot Tables' worksheet. Provide your answers on the 'Specific Questions Summary' worksheet using cell referencing to the results from your pivot tables.
Note: Create as many pivot tables as needed to show your results but be sure to use filtering and/or sorting where needed to get exact results.
Important: Be sure to follow the instructions for the pivots tables at the top of the 'Pivot tables' worksheet.
For each of the paired questions a-b, c-d, e-f, provide a chart with clear indication of the best/worst results. You can show both best and worst in a same chart – include these three charts (i.e. graphs) on the 'Charts' worksheet.
Note: Create as many additional charts as needed to demonstrate your results for the specific questions and the report (see below).
Important: be sure to follow the instructions for charts at the top of the 'Charts' worksheet.
5. Prepare a Report in Microsoft Word that includes:
An introduction
The results from the Excel worksheets. You need to follow the exact question sequence and copy and paste the necessary analyses (pivot tables, summary tables and graphs) from the Excel file into the report.
Discussion of your observations and your recommendations for Festive Restaurants. In your report include discussion of:
i. restaurants, areas and regions which are performing extremely well and/or poorly (if any) and what might be done to improve; and
ii. whether Festive Restaurants should spend more or less on advertising (e.g. in certain areas or regions)?
iii. the types of data quality issues the company might be experiencing from analysis of the provided dataset and how they might be overcome.
Note: you are looking for distinctive features or patterns in the data you have created in order to report meaningfully to Mr Feast. For example, you might consider how restaurants of like-size are performing relative to each other.