Explaining insights into the operation of todd restaurants

Assignment Help Management Information Sys

Reference no: EM131845555

Business Information Systems Assignment

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.

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.

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.

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.

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:- Assignment Files.rar

Reference no: EM131845555

Questions Cloud

Balance of powers type behavior : What proves this and why would such a liberalist region take such a strong realist approach?

Explain what each section of code is doing-checking the room : Explain what each section of the code is doing-checking the room, then checking the possible directions in the room.

Nature of elite-led independence movements : What was the nature of elite-led independence movements in the 3rd World? What was the nature of peasant-based independence movements in the 3rd World?

The best location for the warriors to relocate : Based on the given information, the best location for the Warriors to relocate to is with a total weighted score of ?

Explaining insights into the operation of todd restaurants : ITECH1005: Business Information Systems Assignment. write a brief report identifying and explaining your insights into the operation of Todd Restaurants

Concept of comparative advantage : How were modernization theory and the concept of comparative advantage used to reinforce the status quo relationship between the 1st and 3rd World?

Create a function to play your schools fight song using midi : Create a function to play your school's fight song using MIDI. Take any one of those songs and translate it into MIDI notes, and write a function to play it.

Create and meet the party expectations : Create and meet the party's expectations, stress the benefits of creating mutual trust, establish credibility, keep promises, develop a good reputation

Propagating national security interests : The primary goal of this Assignment is to evaluate how communication of foreign policy interests can be influenced by different theoretical perspectives.

Reviews

len1845555

2/2/2018 6:06:22 AM

Submit the individual work via Moodle before 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.

2/2/2018 6:06:12 AM

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. Note: Create as many additional charts as needed to demonstrate your results for the specific questions and the report (see below). 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 and format of the workbook and report to be readable and professional looking. Check the grading criteria and make sure you have covered each item on which you will be graded.

2/2/2018 6:05:55 AM

Assignment Submission - Your assignment needs to be submitted as a zip file electronically (through Moodle). The zip file needs to include three files: your assignment coversheet file (if required by your lecturer), the Lastname_StudentID.xlsx file, Lastname_StudentID_Report.docx file. 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_2017-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 in clearly labelled enclosed envelope. Check the course schedule for assignment due date. A 10% per day deduction applies for late submissions until the cut-off date.

2/2/2018 6:05:34 AM

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. At the second checkpoint, it is expected that you will have made a significant advancement in your assignment. Failure to meet these checkpoint requirements will affect your performance which may adversely impact on your final mark.

2/2/2018 6:03:25 AM

Submission requirements not observed– penalties - Spreadsheet file is not renamed as: Lastname_StudentID.xlsx, Word file is not named as: Lastname_StudentID.docx, Assignment is not submitted as a zip file with all required files included and with name: itech1005_2017-27_assign_LastName_StudentID.zip.

Write a Review

Required(*) Message

User Account

All Pages