Accounting Question

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