Business Information Systems

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.                                                                                                                                                                         Order Now

Due date:	Refer to the course description Submit the individual work via Moodle before 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.

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 file. Rename the spreadsheet as:

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’)/

• 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:
4. Total Annual Sales
5. Total number of Seats
6. Average Annual Sales
7. Average Store Age (years)
8. Total number of Restaurants
9. 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:

1. Which region has the highest ‘Annual Sales’ for ‘Large’ size restaurants?
2. Which region has the lowest ‘Annual Sales’ for ‘Large’ size restaurants?
3. Which region has the best ‘Average Profit’ for ‘Small’ size restaurants?
4. Which region has the worst ‘Average Profit’ for ‘Small’ size restaurants?
5. Which area has the lowest ‘Profit per square metre' for ‘Medium’ size restaurants?
6. Which area has the highest ‘Profit per square metre' for ‘Medium’ size restaurants?
7. What is the total number of seats for ‘Medium’ size restaurants in the region with highest total ‘Annual Sales’?
8. What is the average floorspace for ‘Huge’ size restaurants in the region with highest ‘Annual Sales’?
9. What is the restaurant with the best 'Annual Sales' at the worst region?
10. What is the restaurant with the worst 'Annual Sales' in the best region?
11. What is the restaurant with the lowest 'Age' in the highest ‘Annual Sales’ area?
12. What is the restaurant with the highest 'Age' in the lowest ‘Annual Sales’ area?
13. What is the most profitable restaurant with no advertising expenditure?
14. 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:

1. restaurants, areas and regions which are performing extremely well and/or poorly (if any) and what might be done to improve; and
2. 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.

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.

• Save your report as docx.

The following link provides you with some useful guidelines for writing a Business Memo:

5. Assignment Submission

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.

6. 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 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, or online help sites or user groups).

The data may appear to be all ‘good’ but it will need to be checked for correctness, completeness and consistency – these issues (and possibly others) will also need to be addressed in your report to Mr Feast.

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.

7. 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 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.

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.

Requirements:	Mark
·      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
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.	28 6	/34
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? 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 1 1 1 1 1 1	/14
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
Submission requirements not observed– penalties Spreadsheet file is not renamed as: Word file is not named as: Assignment is not submitted as a zip file with all required files included and with name: itech1005_2017-	2 2 2	/6
Late submission (deduction  10% per day)
Total marks students	/130
Contribution to final mark	30%	%