MCD2080 Week 11 Extra Exercise Page 1 of 4 Tutor’s Name ______________________ Tutorial Day ______________________ Tutorial Time ______________________ MCD2080 – Extra Exercise Week 11 General Instructions The exercise is a take home assessment (open book) and must be completed at the specified period. This test is worth 2% of the trimester’s assessment. Use Excel and teaching material to do the exercise. There is one questions. Write answers in the spaces provided in this booklet or plain paper and upload answers on Moodle link provided. Answer all questions. Hints If a question asks you to explain your answer, most (if not all) of the marks will be awarded for the explanation of how the answer was obtained. Marks are awarded according to the quality of your answers, not for the amount written. STUDENT ID _________________________________________ SURNAME _________________________________________ OTHER NAMES (in full) _________________________________________ SIGNATURE _________________________________________ Office Use Only Question 1 TOTAL Out of 24.5 24.5 Mark MCD2080 Week 11 Extra Exercise Page 2 of 4 Exercise about multiple linear regression application: Use “Electricity Production.xlsx” data The Data is about Australian production of electricity from Quarter 1 2010 to Quarter 3 2018. The data contains quarterly electricity production in million KWH (m.KWH). Use the data to answer the following questions. Question 1 [1 + 2 + 1 + 2.5 + 2 + 3 + 1.5 + 3.5 + 4 + 4 = 24.5 marks] a) (i). Use Excel to construct a line graph of the electricity production over time. Exhibit 1: < upload the table on Moodle > (ii). State and discuss any three of the time series components that is obvious in the data shown in Exhibit 1. b) (i). Using Excel, estimate a model of a linear trend and quarterly seasonal dummies. Use quarter 4 as the base quarter. To do that we need to define the following variables: Y variable: elect_prod: = Australian quarterly production of electricity (million KWH). X variables: Time: number of quarters since Q1 2010 to Q3 2018. Qrtr1: = 1 if the quarter is from January to March and 0 otherwise. Qrtr2: = 1 if the quarter is from April to June and 0 otherwise. Qrtr3: = 1 if the quarter is from July to September and 0 otherwise. Qrtr4: = 1 if the quarter is from October to December and 0 otherwise. Exhibit 2: < upload the table on Moodle > (ii) State and interpret the coefficient of the intercept. (iii) State and interpret the coefficient of time. MCD2080 Week 11 Extra Exercise Page 3 of 4 (iv) State and interpret the estimated coefficient for the months of January to March. (v) From the above estimated model (Exhibit 2), state the quarter with the second highest Electricity production. Interpret this value. (vi) Carry out a hypothesis test to determine if the electricity production of the months of July to September is different to that in the electricity production of the months of October to December. Use a 1% level of significance and show all the steps. (vii) Using the model in Exhibit 2, predict the Australia electricity production for Quarter 1 in 2019. To do so: First, write down the value for the “Time” variable. [Hint: Time = 1 in Qtr1 2010] Second, write down the value for each of the Quarterly dummy variables for Quarter 1 2019. Third, write down the equation substituting these quarterly dummy values in the model (equation) to predict the electricity production for Quarter 1 2019. MCD2080 Week 11 Extra Exercise Page 4 of 4 (viii) Lastly, we need to evaluate the accuracy of the above prediction, in part (v). The electricity production over the period between quarter 1 2010 to quarter 3 2018 is 12,421.62 m.KWH. With that in mind, discuss any two ways we can evaluate this model.
