All,

Please find attached a PDF file that contains instructions for your individual assignment. It is very important that you read the instructions carefully and answer only the questions that are asked. To facilitate consistency in grading I am also attaching a template Excel spreadsheet where you should record your answers and identification details. You should send this completed spreadsheet to Canvas by Monday midnight London time (BST) March 18, 2024. Please do not leave this to the last minute. I recommend you run the screen on Monday March 18 and submit that day. Do not start working on Monday March 18. Prepare ahead of time.

You will be required to generate a screen in FactSet to answer the questions asked. Please also send the SUMMARY file of your screen as well via Canvas to allow for partial credit. This is not the output file which comes from the full report (just the syntax summarizing your data grab)!

Please RUN the screen on Monday March 18, 2024. This will ensure a unique answer to the question as FactSet updates data daily. Of course, you are free to prepare the screens starting immediately (and you should do so). Do NOT leave it until Monday March 18 to start your screening process. We will run the screen at multiple times around March 18 to cover intra-day data updates that may change the relative ranking of securities.

You have access to FactSet tutorials on how to use the screening feature and I will discuss some of this in class as well.

The files that you submit MUST use a consistent naming convention as described below (use the attached example answer template):

SVFM (CA06) 2024: INDIVIDUAL ASSIGNMENT

CHEAP + HIGH QUALITY COMPANIES

Using FactSet you need to identify the top 2 securities, in both the Consumer Discretionary and

Health Care sectors, using a CHEAP + HIGH QUALITY strategy as described below (using the ‘S&P

1500 GICS’ as the starting universe). You are required to find the two cheapest and highest quality

securities in each sector using multiples measures of quality (equally weighted average of

standardized measures of accruals, leverage, profitability, risk, and beta) and multiple measures of

value (equally weighted average of standardized measures of B/P, E/P, CF/EV, and Sales/EV). This

means your answer should be 2 sets of 2 firms (2 in Consumer Discretionary and 2 in Health Care).

You need to average the composite quality and valuation metrics.

In addition to answering the questions above you will also need to save your ‘screens’ to a local

folder and then submit the SUMMARY of the respective screens on TurnItIn (via Canvas) using the

firstname_lastname_safm_2024.xls convention as specified on email. You can also upload an

excel file that contains your calculations to be considered for partial credit.

Instructions are provided below for this exercise. You will need to login to FactSet and make use

of the UNIVERSAL SCREENING feature in FactSet. You will need to use universe criteria and also

extract the relevant financial statement and market data for this exercise.

For all raw data attributes extracted from FactSet (except for current market capitalization, GICS

sector name) please ensure you use FactSet recommended data sources. This means that your

variables from FactSet will start with ‘FF_’ for FactSet Fundamentals.

We will be grading your assignment based on the answers provided in the Excel template file

(please enter your answers in the YELLOW cells). Also please send along your SAVED FactSet

screens so we can entertain partial credit. It is VERY important that you RUN your screens only on

Monday March 18, 2024. Of course, you prepare your materials/screen ahead of time, just run it

on that date to ensure you are looking at the most recent financial statement and market data

from FactSet.

DUE DATE FOR ASSIGNMENT IS MIDNIGHT GMT MONDAY MARCH 18, 2024. IT IS STRONGLY

ADVISED YOU RUN THE SCREEN ON MARCH 18, 2024, and SUBMIT THAT NIGHT OR EARLY THE

NEXT MORNING.

Universe Criteria : ‘S&P 1500 GICS’ stocks only that have current market capitalization greater than

USD10B and belong to the Consumer Discretionary or Health Care sectors.

Start with the S&P 1500 universe of stocks. You will need to use the relevant index from within the

‘S&P 1500 GICS’ index. Ensure that you include stocks from both the Consumer Discretionary and

Health Care sectors. Use ‘GICS Sector Name’ to identify these sector groupings (‘FG_GICS_SECTOR’).

Equivalently, use the drop-down menu inside the S&P US GICS index and select only the relevant

sectors from the S&P1500 list. For the current market capitalization criteria please ensure you use

Market Value (’FG_MKT_VALUE’). Also ensure that you are extracting financial statement data from

annual reports in local currency units. For the current year just use ‘latest fiscal year’ and for

previous years use the required lag. Please also note that 10B means 10,000,000,000 so you will

need to pay attention to reporting units.

For the below data requirements please request ‘annual’ reporting basis and ‘latest fiscal year’ when

extracting financial statement data (i.e., ‘ANN_R,0’ should appear in the FactSet syntax).

MEASURING QUALITY

Accrual Measure

ACC = (Operating Cash Flows – Income)/ Average Total Assets

For Operating cash Flows use the Net Cash from Operating Activities from FactSet Fundamentals

(‘FF_OPER_CF’). For Income use Net Income as reported on the statement of cash flows

(‘FF_NET_INC_CF’). For total assets use Total Assets (‘FF_ASSETS’). Remember that you need to

compute an average of total assets over the most recent fiscal year.

Leverage Measure

LEV = -DEBT/ASSETS

For DEBT use the total debt field (‘FF_DEBT’) and for assets please see above. But do not use

average total assets for this calculation. Use year-end values.

Profitability Measure

GPOA = Gross Income / Average Total Assets

For Gross Income use ‘FF_GROSS_INC’. For average total assets please see above.

Risk Measures

GPOA_VOL = – Standard deviation of the most recent FIVE years of GPOA

Use your measure of GPOA from above (requires extracting SIX years of data including the current

fiscal year). You then need to compute a standard deviation of your FIVE years of GPOA (requiring at

least 3 years of GPOA). Do the standard deviation calculation in excel after extracting the relevant

data attributes as this will ensure a consistent calculation and treatment of missing observations.

BAB = – BETA

For BETA use the pre-computed rolling five year estimation from Factset Global data source

(‘P_BETA_PR’) and remember to select the five year window. BAB is Bet Against Beta.

Please pay attention to the signs in the formulae above as they are signed to be INCREASING in

higher quality.

MEASURING VALUE (i.e., CHEAPNESS)

B/P = (TOTAL SHAREHOLDER’S EQUITY)/MCAP

For TOTAL SHAREHOLDER’S EQUITY use ‘FF_SHLDRS_EQ’. For MCAP (current market capitalization),

use the Market Value of Company option from FactSet Reference data, i.e.,

‘FREF_MARKET_VALUE_COMPANY’ inclusive of non-traded shares.

E/P = (OPERATING INCOME)/MCAP

For OPERATING INCOME use ‘FF_OPER_INC’. For MCAP (current market capitalization), use the

Market Value of Company option from FactSet Reference data, i.e.,

‘FREF_MARKET_VALUE_COMPANY’ inclusive of non-traded shares.

CASH FLOW/EV

For CASH FLOW use the pre-defined free cash flow variable from FactSet Fundamentals

(FF_FREE_CF’).

SALES/EV

For SALES use consolidated global sales from FactSet Fundamentals ‘FF_SALES’.

EV is Total Enterprise Value and it is equal to the sum of (i) current market capitalization (use the

current market capitalization (‘FG_MKT_VALUE’) that you used in the universe filter.), (ii) total debt

(both short term and long term, i.e., ‘FF_DEBT’), (iii) preferred stock (FF_PFD_STK’), and (iv)

accumulated minority interest (‘FF_MIN_INT_ACCUM’).

Your task is to extract all 5 quality and all 4 value measures defined above and then rank each

security based on each measure (i.e., 9 separate ranks). You then need to standardize these ranks

(i.e., convert them to a mean zero and unit standard deviation scale). You then need to average the

nine ranks at equal weight (this gives equal importance to value and quality measures). You select

the 2 stocks that are highest on these combined ranks within the Consumer Discretionary and Health

Care sectors.

Please note that you MUST use Excel to convert measures to ranks and then combine the ranks

together (this will ensure consistency in the embedded formulae). The RANK.AVG() function in Excel

will be useful for this purpose. Please pay attention to any “#N/A” values in the FactSet output file.

You will need to convert these values to a RANK of ‘0’ as you have no information for these

measures for some firms. Please also pay attention to the ordering of the RANK.AVG function in

Excel: the default is to give higher ranks to lower raw attributes.

ADDITIONAL INSTRUCTIONS

The purpose of these additional instructions is to help ensure consistency in answers across

students. There are many subtle choices that at first glance appear innocuous but, perhaps

unfortunately, can generate different solutions. These additional instructions relate to Excel (i.e.,

you use FactSet to extract the relevant information to compute credit risk and equity valuation

measures, calculations are performed in Excel).

First, obtain the raw data attributes as instructed. Remember to use FactSet Recommended

sources.

Second, exclude inactive, secondary and non-equity securities from the ‘Actions’ toggle on the top

right of the Company Screening page. This should not really matter once you select S&P 1500

constituent firms.

Third, whenever you need to sum variables use the ‘+’ operator. An intended consequence of this

choice is that a firm must have non-missing data for all elements.

Fourth, for the GPOA_VOL variable you first need to compute GPOA over five years and then

compute a measure of standard deviation. Please use the STDEV.P function in excel for this

calculation.

Fifth, you need to convert the quality and valuation measures into STANDARDIZED RANKS. This will

require several steps. First, use the syntax =IF(ISNA(CELLREF),””,CELLREF). This converts any ‘#N/A’

record from the FactSet file you upload into Excel. CELLREF is simple the Excel cell reference.

Second, use the syntax =IFERROR(RANK.AVG(CELL, $RANGE,1),””). This converts your metric to a

rank. The ‘1’ sorts in ascending order (i.e., highest score gets a rank of N which is the number of

non-missing observations, and the lowest score gets a rank of 1). The ISERROR function is necessary

to correctly treat any ‘#N/A’ observation from earlier. $RANGE is the entire range of data for your

score that you wish to convert to ranks and CELL is the specific row to be ranked. Third, you demean

the rank using the syntax =IF(CELL=””,0,(CELL-AVERAGE(RANGE))). The IF statement here is essential

as it converts any missing ranks to a ZERO (this will ensure missing observations end up with a ZERO

standardized score. Here RANGE refers to the cell array containing the ranked scores and CELL is the

specific row containing the ranked score. Fourth, you standardize using the following syntax:

=CELL/(STDEV.P(RANGE). Here CELL is the demeaned rank score from the third step, and RANGE is

the same as in the third step.

Student Number

Stream (i.e., CA06A or CA06B)

Date Screen Run

Top 2 securities that pass VALUE + QUALITY SCREEN for Consumer Discretionary Sector

Top 2 securities that pass VALUE + QUALITY SCREEN for Health Care

Monday March 18, 2024 : MAKE SURE THIS IS TRUE!!!

TICKER 1

TICKER 2

TICKER 1

TICKER 2