Additional Assignment
Weight: 30%
Due date: Refer to Course 27th September 2014
All students are required to submit this assignment electronically via Moodle prior to the due date/time. You should also be prepared to demonstrate progress on your assignment to your tutor during labs.
You need to use Microsoft Excel for the tasks in the following Project.
Aims
To analyse a set of data, and write a memo, identifying and explaining your insights into the operation of Sam’s Restaurants.
Learning Objectives
In the process of this assessment task you will:
• learn to plan, schedule and execute project tasks with a view to improve your personal productivity;
• gain an understanding of some typical issues related to the operation of a medium size business;
• 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:
Sam’s Restaurants offer national chain of full-service, casual-themed restaurants in Australia. You have been offered the job of vice president of operations for Sam’s Restaurants. During your first week on the job, Joshua Sam’s, 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 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 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 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 appropriately 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
• 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.
3. Using the Information worksheet, Joshua needs to have a summary report for each region. The report 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 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?
• You should use Pivot Tables for these analyses. Provide your answers in the Solution Report worksheet using cell referencing to the results from your pivot tables.
• Create as many pivot tables as needed to show your results.
• You can include best and worst in the same pivot tables.
• Be sure to use filtering and/or sorting where needed to get exact results. For all worsts selling products be sure the amount is greater than 0.
• Make sure to show the results of your pivot table analysis by cells reference in the Solution Report worksheet.
• For each question provide a chart with clear indication of the best/worst results. (You can show both best and worst in a same chart).
5. Prepare a Report in Microsoft Word that includes:
• An introduction
• 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.
• 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.
• Save your report as Lastname_FirstnameReport.docx.
Assignment Submission
Your assignment needs to be submitted as a zip file electronically (through Moodle). The zip file needs to include your assignment coversheet file, Lastname_FirstnameVital.xlsx file, Lastname_FirstnameReport.docx file, (ITECH5005 Students Only: Lastname_FirstnameMemo.docx file) . If you have any problem with uploading, email your tutor right away.
Provide your tutor with a backup copy of your assignment in a CD or USB (make sure to collect them back, after marks are released). This needs to be in clearly labelled enclosed envelope containing your coversheet.
Check the course schedule for assignment due date. A 10% per day deduction applies for late submissions. After three days, no late assignments will be accepted.
Some Hints
The questions posed to you in this assignment range from easy, through medium to challenging. However, it is expected that all students will succeed in all the tasks. Some of the tasks are very similar to those covered in tutorials and the associated exercises. Some of the challenging tasks may rely on insight that requires self-directed study (e.g. using help facilities in Excel to explore in depth some of the topics covered in the labs).
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, chartings and managing large worksheets, as well as, on the analysis of Excel data and drawing recommendations). You may also need to search the internet to find instructions on how to use functions such as SUMIF, SUMIFS, COUNTIF, COUNTIFS, etc.
There is no ‘ideal’ or ‘best’ way of doing this project. You’ll be assessed on 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.
Assignment Assessment
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 the workbook to be readable and professional looking.
• A large portion of the marks awarded for this assignment are determined by the quality of your report and memo.
• 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 5 to 10 you may be asked by your tutors to show evidence of your consistent work on your assignment. In particular, weeks 6 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.