Excel Assignment for Semester 1, 2016

DUE DATE: 23rd May 2016

Objective:

When you use Excel it is rather like a trick – you don’t know what is happening behind the answer; therefore we get you to do manual calculations for your tutorial work. The manual calculations show you why and how your software arrives at a particular value. In the business world you will not be calculating manually. You will make use of the computer. Therefore you need experience in using Excel. We have chosen Excel (instead of Minitab or SPSS) because students have access to Excel (either in the university’s labs and/or on the home computer).

Information and Instructions to Students

You will work with Excel (using ‘Data Analysis’ and the toolbar functions, ¦x) and copy and paste your output onto a word document. Whatever version of Excel you are working with you will be able to access ¦x and ‘Data Analysis’. However, some questions may require the use of a later version of Excel. For these questions you will need Excel 2010 as found in the labs. If using Excel 2007 click on F1 and type ‘Load Data Analysis’ in the search box and select ‘Load the Analysis Toolpak’ and follow the directions. If you are using Excel 2010 go to file – click on options – click on add-ins – highlight ‘Analysis Tookpak’ – click on ‘go’. Tick the square next to ‘Analysis Tookpak’. Click ok. To locate Data Analysis click on Data in the main menu. ¦x and ‘Data Analysis’ are the same for all versions of Excel.

The lab assignment is worth 10% and comprises six questions. The assignment is a self-directed piece of work. Help on using Excel is available from Robert Williams or your local lecturer only after you have shown evidence that you have attempted to use Excel. We cannot check your assignment before you hand it in. Instructions for using Excel are given in your text.

Submission: You submit your (Word doc) assignment via BlackBoard (see Assessment for the submission link) on the date and time as per the Unit Program. Late assignments will not be accepted under any circumstances. Please be aware of this and start your assignment early.

The cover page to your assignment should include your name, student ID, unit name, tutorial day and time and the words ‘Assignment Using Excel’. Underline your surname. Keep a backup copy of your assignment. You must include a typed copy of the two columns of data (complete with your added student ID numbers).

Failure to comply with this instruction will incur a 1 mark penalty.

Assignment

Travel expenses paid by companies can increase or decrease dramatically when there are changes in the daily rates* for hotel rooms. The following data shows typical daily rates for hotels in 19 Australian towns during 2011 and 2009.

*The average daily rate (ADR) is a metric widely used in the hospitality industry to indicate the average realised room rental per day. It is one of the key indicators to measure the operating performance of a hotel.

Hotel Rate in 2011 Hotel Rate in 2009
123 101
119 98
98 67
67 65
88 82
111 76
95 75
75 73
74 97
112 92
88 87
93 65
82 92
51 64
59 69
71 68
67 76
82 87
93 83

Open an Excel spread sheet (you may use Excel 2003, Excel 2007 or Excel 2010) and enter the data which is shown above. You will now make your data unique by taking the last 4 digits of your student identification number and putting the first two digits at the end of the ‘2011’ data and the last two digits at the end of the ‘2009’ data. For example: If the last 4 digits of your student number are 1234 then put 12 under the value ‘93’ in the ‘2011’ column and 34 under the value ‘83’ in the ‘2009’ column. There will now be a total of 40 hotel rates for 20 Australian towns.

Question 1: 1 mark

Use fx to find the modes and variance for the ‘2011’. Write down the name of the function to verify that you used Excel. For example if you were asked to find the mean you would show the function
Show a table that displays all the summary statistics for the ‘’2009’ data and highlight the values for the mean, standard deviation and standard error. What is the standard error? Calculate the standard error manually to verify Excel’s result.

Question 2: 2 marks

Display a histogram for ‘2009’ using class widths of 5. Describe the shape of the distribution in terms of skewness and kurtosis. What are the values for the skew and for the kurtosis?
Display an ogive* for ‘2009’ and estimate the proportion of hotel rates that exceed $80 per day.

* When copying your Excel output to ‘word’ include the ‘bin-frequency-

cumulative % table.

Question 3: 1 mark

We wish to randomly select ten hotels. Which Excel technique will you use? Randomly sample the ten hotels.

Question 4: 1 mark

A survey undertaken in May 2011 showed three company executives stayed at the Meriton Hotel, four at the Hilton, two at the Paramount, four at the Abbey, five at the Esplanade and two at the Oaks. Show two different ways of displaying this information to best advantage (neither of these displays must be a frequency distribution table).

Question 5: 3 marks

Hotel rates are normally distributed with a mean of $98 and a standard deviation of $15. What is the probability that the rate exceeds $105? Use an appropriate Excel function (¦x) to determine the probability. Write down the formula shown in the formula bar.

The commission rate commercial airlines pay travel agents has been declining for several years. If 90% of travel agents charge customers fees when purchasing an airline ticket find the probability that in a randomly selected sample of 100 travel agencies 96 or more charge ticket fees. Write down the formula shown in the formula bar.

Statistics have been maintained for mishandled bags per 1,000 airline passengers. In 2009 an airline had 3.21 mishandled bags per 1,000 passengers. What is the probability that in the next 1,000 passengers the airline will have at least one mishandled bag? Use an appropriate Excel function (¦x) to determine the probability. Write down the formula shown in the formula bar.

Question 6: 2 marks

Can we conclude at the 10% significance level that there is evidence of a difference in the mean daily hotel rate in 2011 and 2009? Highlight the relevant critical value or p-value and the test statistic to aid you in interpreting the data.

Why Choose US

6+ years experience on custom writing
80% Return Client
Urgent 2 Hrs Delivery
Your Privacy Guaranteed
Unlimited Free Revisions

find the cost of your paper
Is this question part of your assignment?
Place order
Posted on May 18, 2016Author TutorCategories Question, Questions