Paper , Order, or Assignment Requirements

Problem description:

The date 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. Source:

Table 2.3 Estimates of national road freight: Victoria (billion tkm) p. 6

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

35,190 84.8 790 36,430 90.3 815 37,650 91.6 837 39,310 95.9 862 38,570 95.0 886 39,200 95.1 958 40,200 97.0 961 41,730 100.0 998 42,590 102.0 1046

$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

BITRE (2014) Road freight estimates update Information sheet

2

current prices for GDP. Series type Trend; Data type DERIVED.

6401. Source: ABS Catalogue 6401.03 CPI Index numbers by group by capital city accessed November 26 2014. Base 2011-12 = 100.0
6402. Source: Australian Bureau of Statistics Catalogue 6302.001 Average Weekly Earnings
6403. 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.
6404. Data from ABS Catalogue 8501.03.0 – Retail Trade, Australia, by State. Annual totals obtained by adding monthly reported values.
6405. 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: https://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 trends (movement over time). (2 marks)

Task 3: Draw scatter plots 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)

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

Dependent variable

Independent variables

Y

X1

X2

X3

31

104.1

1095

$65,593.3

32

105.8

1124

$74,396.5

33

106.9

1183

$76,754.9

34

108.1

1203

$79,156.1

35

109.7

1245

$81,388.3