Finance: Price to Earnings Multiple for the FTSE 100 and its drivers.

#1
Hi All - I have built a very simple model.
I have developed a regression model in MS EXCEL to predict if the FTSE 100 is fairly, under, or overvalued.

Forecasting EPS:
Dependent variable
- Trailing 12 months EPS (£)
Independent Variables - Brent Crude Price ($), Bloomberg Industrials Metal Index, US leading indicator Index, and EUR/GBP FX

Fundamental justified P/E ratio:
Dependent Variable:
P/E ratio (I have taken the close price of the FTSE 100/ FTSE 100 best estimate EPS)
Independent variables: Brent Crude ($), UK 10 Year Gilts (yields), Vol Index (Using the Stoxx 500 vix), UK Consumer Confidence Index
Note: The independent variables have been lagged by a range of 3-6 months. Monthly Data used throughout. All data points obtained from Bloomberg.
I have run each regression:

Results are attached in screen shots.

I created the prediction model where you can input your own forecasts for each variable like Brent, Consumer confidence etc and this is multiplied by the regression coefficients from the regression results. This will split out a FV for the FTSE 100. This is compared with the current FTSE 100 Index - signalling under, over, or fairly valued.


QUESTIONS:

I want to improve my modelling skills. This isn't about improving the adj R2 or adding additional independent variables, but I want some advice on things I would need to consider before publishing this model to my internal team - basically show them I have considered some of the typical thing you would look at before running a regression model or when working with time series?
I am early in my journey and over time I want to be able to do more work like this and apply some of the skills I learn from here.
So..
1) What are the typical issues when dealing with financial time series data that I can apply?
2) Someone mentioned that Brent Crude price at not spot prices, but instead are futures contract and posed a question on how does this model take into account for this? TBH - i didn't think about it and my answer was I have lagged the data by 6 months.
3) Would you suggest tests that i would need to consider and if so, what would it be and what would I need to look for?


I would really appreciate feedback on this - and things I can do to better the model or even improve my data analytics skills.

Thank You
 

Attachments