What is the restaurant with the worst annual sales

Assignment Help Management Information Sys

Reference no: EM131846594

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.

Attachment:- Restaurant Attributes.xlsx

Reference no: EM131846594

Questions Cloud

Discuss financial implications of decision made by a company : Current Event Presentation Assignment: The economic, ethical, or financial implications of a decision made by a company and A global issue that affects business

Find the probability that the number : Find the probability that the number that say they would feel secure is (a) exactly five, (b) more than five, and (c) at most five

Difference between the on-the-job anxiety : Reject the null hypothesis that there is no difference between the on-the-job anxiety of FIRST and THIRD shift police officers based on a one-tailed negative

Find the z-score corresponding to a sample mean : c. Find the z-score corresponding to a sample mean of 66 inches for a sample of 25 females.

What is the restaurant with the worst annual sales : ITECH1005: Business Information Systems - Calculate the number of years each restaurant is operating in the Restaurant Age - What is the total number of seats

How do you prioritize your positions : How do you prioritize your positions when attempting a negotiation? Why is it important to do this?

Define the types of prostitution : Select three types of prostitution. Create a 6- to 8-slide PowerPoint presentation (including a title and reference slide) addressing the following.

What is the probability of a matching birthday : What is the probability of a matching birthday in any randomly selected group of 25 people(consider only the standard 365 days)

Excel descriptive statistics file : Data was collected on how much 8 customers recently spent for lunch at Benny's Café; see the Excel descriptive statistics file below.

Reviews

len1846594

2/3/2018 2:23:27 AM

Report (in Microsoft Word) • Appropriate Introduction • reasonable discussions of observations of the result and recommendations, incuding • restaurants, areas and regions which are performing extremely well and/or poorly (if any) and what might be done to improve; and • whether Festive Restaurants should spend more or less on advertising (e.g. in certain areas or regions)? • the types of data quality issues the company might be experiencing from analysis of the provided dataset and how they might be overcome. • Appropriate use of graphs and tables • Report formatting well done and professional in tone 2 10 2 2 8 6 2 /32

len1846594

2/3/2018 2:23:14 AM

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? 1 1 1 1 1 1 1 1

len1846594

2/3/2018 2:22:59 AM

Answers to 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? 1 1 1 1 1 1

len1846594

2/3/2018 2:22:35 AM

Pivot Table Worksheet • For each question an understandable pivot table is provided, with meaningful titles and added background colour to “Best” and “Worst” values. Pivot Table Worksheet • For each required question an appropriate format chart is provided with a clear indication of the best/worst results.

len1846594

2/3/2018 2:22:24 AM

• VLOOKUP • Lookup tables (3) created • Lookup function is used correctly for each table: a. Restaurant size (by sales) b. Taxes Payable on annual sales ($) – in $A currency format c. Total Cost of Sales – including advertising ($) – in $A currency format • Calculate the following correctly: • Restaurant Age (years) – correct to one decimal point • Profit ($) – in $A currency format • Sales per Seat ($)– in $A currency format • Sales per square metre ($)– in $A currency format • Profit per Seat ($)– in $A currency format • Profit per square metre ($)– in $A currency format • Calculate Sum, Mean, Median, Maximum, Minimum, Range and Standard Deviation for each column: Summaries for each region, area and restaurant size: a. Total Annual Sales – in $A currency format b. Total number of Seats c. Average Annual Sales– in $A currency format d. Average Restaurant Age (years) – correct to one decimal point e. Total number of Restaurants f. Total number of advertising Restaurants • The report tables needs to be sorted by ' Total Annual Sales’ column in descending order. 3 2 4 8 1 1 1 1 1 1 6 3 3 3 3 3 3 3 /50

len1846594

2/3/2018 2:21:52 AM

This assignment is worth 30% of the total marks in this course and will be marked on: • your demonstrated understanding of the problems; • completeness of your tasks; • exploration of the features in MS-Excel; • quality of the final product • format of the workbook and reportto be readable and professional looking. Check the grading criteria and make sure you have covered each item on which you will be graded. Assignment checkpoints: During your lab classes in weeks 7 to 10 you may be asked by your tutors to show evidence of your consistent work on your assignment. In particular, weeks 8 and 9 are assigned as checkpoints. It is expected that by the first checkpoint, you will have answered majority of the questions and made your preliminary observations and recommendation in writing the report and the memo

len1846594

2/3/2018 2:21:44 AM

There are numerous Microsoft Excel facilities and functions that may be useful in this project. While planning your workbook, and especially the observations, you may wish to review the tutorials on the related topics (e.g. Excel functions, absolute and relative cell referencing, formatting, pivot tables, chartings and managing workbooks, as well as, on the analysis of Excel data and drawing recommendations). You may also need to do some research to find instructions on how to use functions such as SUM, COUNT, AVERAGE, SUMIF, SUMIFS, COUNTIF, COUNTIFS, LOOKUP, etc. There is no ‘ideal’ or ‘best’ way of doing this project. You’ll be assessed on your correct use of Excel, your insight of the data, on your ability to make observations from analysing the results and visualising the charts, and your ability to link your observations to business issues in a professionally prepared report.

len1846594

2/3/2018 2:21:19 AM

Your assignment needs to be submitted as a zip file electronically (through Moodle). The zip file needs to includethree files: your assignment coversheet file (if required by your lecturer), the Lastname_StudentID.xlsx file,Lastname_StudentID_Report.docxfile. If you have any problem with creating a zip file, or in uploading the zip file onto Moodle, email your tutor without delay – extensions will not be provided for any such delay in submission. Name the submission zip file as: itech1005-27_assign_LastName_StudentID.zip. Note: Lastname_StudentID are to be your own last name and student ID. Provide your tutor with a backup copy of your assignment on a CD or USB (make sure to collect them back, after marks are released). This CD or USB needs to be inclearly labelled enclosed envelope. Check the course schedule for assignment due date. A10% per day deduction applies for late submissions until the cut-off date.

len1846594

2/3/2018 2:21:04 AM

Due date: Refer to the course description Submit the individual work via Moodlebefore the due date. Cutoff date: One week after the due date. Any submission after the due date will receive a deduction of 10% per day, this includes weekends. Marks: This assignment is worth 30% of the total assessment. Extensions: An extension will only be considered with supporting documentation from a health professional and if the problem/illness occurred within the week prior to the due date. If an extension is granted the extension will then equal the number of days specified on the doctor's certificate, with a maximum limit of one week. Authorship: This assignment is an individual assignment and it shall be completed by the individual student ONLY. The final submission must be identifiably the work of the individual. Breaches of this requirement will result in an assignment not being accepted for assessment and may result in the offending student or students being required to present before the Disciplinary Committee.

Write a Review

Required(*) Message

Management Information Sys Questions & Answers

How many of the apps listed on the website do you have

Do some brief Internet research to see the information that "Pokemon Go" captures when using the software. What are your thoughts about providing access to this information being gathered by the developers? After reading all this, are you more or les..

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

User Account

All Pages