I'm using Excel and R to do this. I'm new to R.
I have a dataset of energy savings arising from capital investments supported by government grants. I've done a multiple linear regression of energy savings against grant size, total investment cost, and several dummies about investment type. The data is far from normal and looks like it needs transformation.
I did a Box-Cox test on y (ie energy savings), resulting in lambda of -0.14, so I transformed y using a natural log. Then, thinking the explanatory variables also needed transforming, I followed this (where it speaks about transformations of predictors), and the lambdas of cost and grant are 2.25 and 1.75 respectively (both have significant p-values), suggesting a X^2 transformation. Additionally (ie as an alternative), I did log transformations on both grant and cost as I had a hunch that would normalise them.
My questions are as follows.
Question 1: Using transformed explanatory variables
So, looking solely at the adjusted R^2 and not removing any non-significant predictors, the adj-R^2 values are as follows.
- model 1 (no transformation): 0.5596
- model 2 (y = ln(y)): 0.6070
- model 3 (y=ln(y), x=x^2 where not dummy): 0.5478
- model 4 (y=ln(y), x=ln(x) where not dummy): 0.6809
I think I should use the ln transformation (ie model 4), but the box.tidwell lambdas recommended a x^2 transformation.
Am I justified using a ln transformation on the explanatory variables despite what the box.tidwell test says? Why? This page suggests I can compare models in R with anova(fit1, fit2), but I don't really know how to evaluate the output (the smaller RSS is better?).
Question 2: Addressing multicollinearity
While doing all this, I realised I had forgotten about multicollinearity. Grant size and investment cost are highly correlated. I want to use the model to estimate energy savings for various grant sizes, so I think it's okay that they are correlated. But I was wondering, can I transform the grant column to be something like 'percentage granted'? I suspect that would remove the correlation, but might do something weird because it would be a constrained value (ie always < 1). Or maybe it could make it a dummy or something?
How can I deal with the multicollinearity of grant size and project cost? Is it just a matter of trying different transformations and seeing how they affect the fit?
Thanks so much.