You are required to complete a number of short answer questions and upload your answers in a word file (include assignment cover sheet as provided under this assignment). The assignment requires you to use Excel (or any other statistical software) to answer the questions and create the charts etc. For this assignment, you should copy all your charts and comments to a Word document and upload that Word file for grading. Clearly label all questions and charts, as well as your answers.
General guidelines for submission:
· This assignment is worth 20% of the available marks for this subject.
· The assignment will be marked out of 60 and converted to a mark out of 20.
· The assignment should be produced in Microsoft Word and an electronic version needs to be submitted by the due date. You must complete and include the assignment coversheet provided.
· You may use Microsoft Excel or any statistical software. Please embed or copy and paste any relevant outputs into the Microsoft Word document. It may be useful to use Excel add-ins for your calculations.
· Answer all 3 questions and clearly show your workings (i.e., how you developed your answer). If your lecturer cannot readily understand your calculation steps and assumptions, they may be unable to award you full marks for your answers.
· This is an individual assignment. Students who are found to have copied another student’s work or shared their work with other students will be subject to heavy penalties. (Note: Refer to Kaplan Business School requirements for academic integrity).
Feedback: Comments and a mark will be returned to you within two week of submission.
Question 1 (20 marks = 5 + 3 + 1 + 6 + 1+ 4)
Excel files ExchangeRates-Monthly–7-1969to12-2009.xls and ExchangeRates-Monthly–1-2010to8-2015.xls contain monthly data on the Australian dollar (AU$) exchange rates against major currencies for the period July, 1969 to August, 2015. (source: Reserve Bank of Australia, http://www.rba.gov.au/statistics/historical-data.html#exchange-rates ).
a) Use Excel to draw a line graph of the exchange rate between United States dollar (US$)/AU$. What does this graph reveal? Describe how the exchange rate has changed. (Hint: consider periods (i) from 1969 to 1973/74, (ii) from 1973/74 to 1985, (iii)1985 to 2011 and (iv) 2011 to present.)
b) Calculate monthly change in the exchange rate (Let be the exchange rate in month t. calculate change in exchange rate as ). Draw a line graph for the monthly change in exchange rate. What does this graph reveal?
c) Find Maximum and minimum of monthly change in exchange rate.
d) Create 19 bins for drawing a histogram for the monthly changes in exchange rate. Make the class limits for the first bin -0.22 to -0.20 and those for the last bin 0.14 to 0.16. Use Excel to create the histogram. Describe the histogram. Is it positively skewed? Negatively skewed? Symmetric?
e) Find the mean and standard deviation of monthly change in exchange rate. Report your answers correct to 5 decimal places.
f) What proportion of observations lies within 2 standard deviations of the mean (Hint: you may use excel countif function)? Is this proportion consistent with what you would expect from the normal distribution (Hint: Empirical rule)?
Question 2 (20 marks = 2 + 2 + 3 + 3 + 2 + 2 + 2 + 4)
Five years ago it was established that the mean time to travel between from a particular suburb to city is 30 minutes. You want to test whether the mean time has changed. To investigate this hypothesis you plan to take a sample of 20 travel times.
a) Specify the null and alternative hypotheses.
b) What is the test statistic that will be used to test the null hypothesis? What is its distribution when the null hypothesis is true?
c) Assuming a 2% significance level, what is the decision rule for rejecting null hypothesis?
d) The file TravelTimes.xls contains 10 different samples each of size 20, labelled 0, 1, 2,…,9. Choose the sample that corresponds to the last digit of your student number. Find (i) the sample mean, (ii) the sample standard deviation, and (iii) the value of the test statistic specified in part (b).
e) What is your test decision and conclusion about the travel time?
f) What is the p-value of the test?
g) Suppose the current mean travel time (population) is ? = 34 minutes. Did the test lead you to the correct decision? If not, did it make a type I or a type II error?
h) The 10 different samples in the file TravelTimes.xls can be treated as one big sample of size n = 200. Assuming a 1% significance level, what is the decision rule for rejecting null hypothesis? What is your conclusion?
Question 3 (20 marks = 3+ 3 + 2 + 4 + 4 + 2 + 2)
An event organiser would like to predict the number of ‘portaloo’ lavatories required for outdoor entertainment events based on the volume of food to be consumed at the event. The organiser has collected the data given in GB513Assignment-Q3.xls.
a) Plot a scatter diagram for this data. Describe (briefly) the relationship between the volume of food consumed and number of portaloos.
b) Assuming a linear relationship, calculate the regression equation for this data. Please show your calculations or insert your summary output from Excel.
c) Interpret the estimated Y intercept, 0 b , and the estimated slope, 1 b .
d) Perform residual analyses and determine whether the sample data meet the regression assumptions of equal variance (homoscedasticity) and normality.
e) Is the amount of food consumed a statistically significant predictor of portaloos? Please explain your answer.
f) Comment on the goodness of fit of this model.
g) Predict the number of lavatories required if the volume of food to be consumed is 800 kg.
GB 513 Data Analysis Assignment and Data Files
Please find assignment and data files here.