TRANSPORT ECONOMICS Assignment 1: Demand forecasting Instructions: The assignment assesses your understanding of quantitative transport demand forecasting techniques. Assignment 1 is worth 20 marks. This assignment is an individual assignment. Thus, you need to finish the assignment independently. The due date is by 23:59 PM on the last day of Week 6 (9 April 2017). Submission extensions are not normally granted, except for other unexpected reasons. If you need an extension you must apply electronically to the course coordinator via email before the submission due date. In the application you should specify a date on which you plan to submit the assignment. This date should be within one week from the posted submission date. Any requests for extensions exceeding seven days must be submitted via RMIT's special consideration process. In the absence of a granted extension, late submissions will be automatically penalized by 10% per working day up to a maximum of 10 working days. Problem description: The data set in the spread sheet below contains: data about the annual quantity of total Victoria road freight during the period fiscal year 1 to 30. This variable is the dependent variable, and is denoted as Y. data for the same number of years on three other variables (X1, X2, X3) that are associated with the dependent variable, and are proposed to be relevant in forecasting future values of the dependent variable. You are required to use the Microsoft Excel package (the data analysis section) to finish the specific tasks below. 1 Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Average Estimated annual total weekly Retail Total road transport fuel earnings turnover freight (Y) index (X1) (X2) (X3) 13,780 14,540 15,740 15,900 17,130 18,400 19,460 19,040 18,760 19,850 20,330 21,970 23,290 24,220 25,910 27,290 28,620 29,210 30,740 32,270 33,970 35,190 36,430 37,650 39,310 38,570 39,200 40,200 41,730 42,590 38.6 40.7 44.0 48.7 52.8 54.2 58.0 62.1 63.6 65.3 67.1 68.9 71.0 71.9 71.5 70.0 74.1 78.9 79.3 81.2 82.0 84.8 90.3 91.6 95.9 95.0 95.1 97.0 100.0 102.0 323 344 366 387 408 431 466 488 504 511 529 552 563 572 598 602 614 637 674 723 761 790 815 837 862 886 958 961 998 1046 $13,154.4 $13,787.6 $15,430.2 $17,127.5 $18,438.9 $19,849.8 $21,064.7 $20,849.4 $21,727.5 $22,513.1 $23,144.8 $24,705.9 $28,285.0 $27,645.9 $29,146.4 $31,714.3 $34,417.1 $35,405.2 $39,305.4 $41,975.1 $45,744.2 $47,263.5 $48,574.4 $51,661.3 $54,538.7 $57,593.3 $59,996.5 $62,754.9 $64,156.1 $64,388.3 a. Source: BITRE (2014) Road freight estimates update Information sheet Table 2.3 Estimates of national road freight: Victoria (billion tkm) p. 6 b. Current value of GDP per year obtained from ABS Catalogue 5206.001 Key aggregates by summing in each fiscal year the four quarterly values at 2 current prices for GDP. Series type Trend; Data type DERIVED. c. Source: ABS Catalogue 6401.03 CPI Index numbers by group by capital city accessed November 26 2014. Base 2011-12 = 100.0 d. Source: Australian Bureau of Statistics Catalogue 6302.001 Average Weekly Earnings e. Source: Australian Burea of Statistics Time Series Workbook 6291.0.55.001 Labour Force, Australia, Detailed - Electronic Delivery Table 02. Labour force status by State, Capital city / Balance of state and Sex. Figures in Table are average of monthly figures for each year. f. Data from ABS Catalogue 8501.03.0 - Retail Trade, Australia, by State. Annual totals obtained by adding monthly reported values. g. Source: 1986, 1991, 1993, 1994 from ABS Household Estimates Australia 1986, 1991-1994 Cataogue #3229.0. 1994/5 to 2011/12 data from ABS Catalogue 4130.0DO.003201112 Table 18 VIC Households Selected Household Characteristics. Data for 2006 and 2011 number of Victorian households from Profile ID taken from ABS Census of Population and Housing: http://profile.id.com.au/australia/households?WebID=110 accessed July 17 2014. Required tasks: Task 1: Input the data set into Excel. (2 marks) Task 2: Present a scatter plot including all variables (dependent and independent variables), and briefly comment on their linear trends (movement over time). (2 marks) Task 3: Draw scatterplots (add linear trend lines) between the dependent variable and each of the independent variables and briefly comment on them. (4 marks) Task 4: Run the linear regression function in Excel between the dependent variable and each independent variable. Analyse and comment on the outputs based on the adjusted R2 values and the coefficients. (4 marks) 3 Task 5: Run the following multivariate linear regression models: Dependent variable Independent variables Model 1 Y X1, X2 Model 2 Y X1, X3 Model 3 Y X2, X3 Model 4 Y X1, X2, X3 Report the outputs and give the estimated equations, conduct the analysis as in Task 4, and then select the best forecasting equation from those generated in Tasks 4 and 5 based on the adjusted R2 values. (4 marks) Task 6: Forecast the values of the dependent variable using the model chosen in Task 5 for Year 31 to 35 and briefly explain the results of these forecasts (the forecast independent variables are given below; use the relevant values to forecast the Y values). (4 marks) Year 31 32 33 34 35 Dependent variable Y Independent variables X1 X2 X3 104.1 1095 $65,593.3 105.8 1124 $74,396.5 106.9 1183 $76,754.9 108.1 1203 $79,156.1 109.7 1245 $81,388.3 4