COMM5000 Excel Instructions: Final Report Modeling
Contents
1 Data Preparation 2
2 Run the Regression (Data Analysis Toolpak) 2
3 Interpreting Output 2
4 Simulate a Prediction 2
5 Confidence and Prediction Intervals 2
6 Final Checklist 2
1
1 Data Preparation
• Calculate Age: =2018 - [Year Built]
• Log Price: =LN([Market Price])
• Create Dummy Variables:
– =IF([State] = "VIC", 1, 0) for State VIC
– Repeat for State QLD, Structure Brick Veneer, etc.
2 Run the Regression (Data Analysis Toolpak)
• Go to Data > Data Analysis > Regression
• Set Y Range as Log(Price), and X Range as your predictors
• Check Labels, Confidence Level = 95%, and specify Output Range
3 Interpreting Output
• Use coefficients to interpret marginal effects
• Approximate percentage change: 100× coefficient
• Review Adjusted R-squared and F-test p-value
4 Simulate a Prediction
1. Input new values for a scenario (e.g., 3 beds, 2 baths, 80 sqm car area)
2. Multiply each input value by its respective coefficient: =Coeff * Value
3. Sum results and add intercept: =SUMPRODUCT(inputs, coefficients) + Intercept
4. Convert to price: =EXP(predicted log price)
5 Confidence and Prediction Intervals
• Use CONFIDENCE.T(0.05, StandardError, n)
• Add/subtract from predicted log price to form CI
• Add residual standard error to CI bounds to form PI
• Exponentiate bounds: =EXP(lower), =EXP(upper)
6 Final Checklist
Dummy variables created
Log(Price) calculated
Regression run and interpreted
Prediction scenario simulated
CI and PI computed and converted to price scale
Workbook is clear and formulas traceable
2

学霸联盟