F-Score: An Excel Tutorial

Market Basics: Combining Fundamental & Technical Analysis to increase certainty

In last week’s instalment, the Piotroski Score was discussed as value investment tool to assess the fundamentals of a company. We will now walk you through a step-by-step Excel tutorial, with in-depth explanations to fully touch upon each row of your F-score spreadsheet.

(Spreadsheet by Jonathan Meyers, Analyst at Bearclaw Investments)

The first task will be to recreate this initial spreadsheet layout. One can find all information needed to fill the cells on sources such as Yahoo Finance under the Financials section or on a Bloomberg Terminal, which can (in most cases) be accessed at your local university. For EUR students, they can be located on the 3rd floor of the Polak Building.

So let’s start reviewing some of the rows, starting with net income and total assets. Although these should be clear to most of you, in short, the former covers a profit realised by a company over a certain year, while the latter illustrates the total worth of assets owned by a company at a given point in time.

The operating cash flow, frequently labelled ‘cash flow from operations’, illustrates the cash the company is able to generate solely from its core operations. It is a strong indicator as to whether the firm is able to finance current operations, or if it needs external support.

Similar to total assets, the current assets component should be common terminology for most of you. If this is not the case, www.investopedia.com is an widely renowned and accredited source to grasp a quick understanding unknown terminology.

The 9th row in the spreadsheet indicates whether the company has issued new shares. This is crucial to investment decisions, as stock issuance decreases current share prices and thus largely shapes investor’s decisions. If a company has issued shares, the investor should closely analyse the underlying reasons. Is the underlying project a through effort to expand operations, or is it sparked by profit-maximising behaviour from managers or executives?

Gross profit is largely determined by cost of goods sold (COGS), as it describes the profit made following deduction of costs associated with providing the relevant service or product. It is computed by subtracting the COGS from operating revenues. In short, COGS, also referred as cost of sales, refers to direct labor and direct materials used in order to produce the products.

(Spreadsheet by Jonathan Meyers, Analyst at Bearclaw Investments)

The second part of your spreadsheet will display F-score results and indicate which areas the company might need to strengthen in order to warrant an investment.

A cell will appear green if the firm has gone through improvements compared to the previous year. At AMSA, we consider a score of 7 or above as a healthy long position indicator. A score below 3 should signal to go short on the company, as the stock price is likely to fall based on fundamental performance. As these are just indicators, high (or low) scores do not always hit the mark. To maximise your chances of making sound investment decisions, we will explain ways to make this score assessment even more reliable, merely by using different ratios and technical indicators.

If you are looking for a straightforward way to locate on which level the score is influenced, use the IF function in the cells to the right of the Details header. What you will do is use =IF(Cells of previous table: “This year” – ‘’Previous year” >0;1;0). Copy this across every row and link the second table to the first one. The last row should contain the =SUM function to compute how many cells of the 9 indicators obtained a better score than the year before. This will be you final F score.

As the Pietroski score indicates performance in fundamentals, we at AMSA like to combine it with the PE (price / earnings) ratio. Unfortunately, there is no generalisable figure that will objectively indicate whether a share has a low PE or a high one. One will have to compare it to industry averages and historical figures. If the company in your watch list scores lower than average, it may well be undervalued.

One could further personalise their F-Score by adding a row to assess the PE score compared to the industry which, if below, could give one more point out of 10 now.

Combine Technicals with Fundamentals

For investors which prefer the possibility of aiming for short-term profits, technical analysis may be a better choice. Nonetheless, stock prices are often heavily influenced by external events, which in turn largely shape the performance chart and lead the indicators to give the investor a false signal. Therefore, at AMSA, as we are take a more long-term orientation and try to reach sustainable positions in companies in which we have full confidence for the future, we only take positions where fundamental results and technical indicators overlap. In doing so, the long-term technicals form a back-up for when macroeconomic events influence the chart in the opposite direction and we can maintain confidence for returns.

Combine PE with RSI

The Relative Strength Index can be activated in Yahoo Finance on the full-screen chart. The formula is 100- (100/(1+ ave gain/ave loss)). This momentum oscillator will indicate to the technical investor whether the stock is currently being oversold or overbought. Most traders adopt a 14 periods long RSI.

If your F-score scores a 7 or above, your RSI find itself situated in the <30 range, and the PE ratio is below industry average, one could be confident in taking a long position in that stock. Having said this, one should always closely follow the news to remain informed about current economic conditions and the latest information about the company.

Combine Volume analysis, RSI, PE and the F-score

Another add-in to further enhance the analysis would be volume analysis. If, on a particular chart the share price seems to be surging, and the bullish volume seems to be decreasing simultaneously, we can expect the bears to take over and the share price to drop in coming periods. In that case, a solely technical indicator would trigger a short position. If one wants to confirm this short position, we would advise to look at for an overbought RSI of >70, a higher than average PE ratio and very low F-scores.