S1 2023 ACCTG/FINANCE 701 1
ACCOUNTING / FINANCE 701: RESEARCH METHODS IN
ACCOUNTING & FINANCE
DATA ANALYSIS ASSIGNMENT
DUE 5PM, THURSDAY, 4 MAY 2023
General instructions:
• This is an individual assignment.
• The assignment will be marked out of 25 marks and is worth 25% of your overall
grade for this course.
• Please submit your assignment online through Canvas by the due date as a PDF
document.
• Proper referencing (APA style or other referencing style used by a major
Accounting or Finance journal) must be used if you use external sources.
• Late submissions will lose 5 marks for each day they are late.
Background:
This assignment involves quantitative data analysis. The purpose of the assignment is to
help you familiarise yourself with techniques and tools commonly used for data
manipulation and quantitative analysis in accounting and finance research.
The theme of this assignment is based on a study by Graham and Leary (2018), which
investigates corporate cash holdings. It may be helpful to refer to this study for context,
although this is not explicitly required in order to complete this assignment. The full
reference for the study is given below and a link is also provided through the Week 2
reading list on Canvas:
Graham, J. R., & Leary, M. T. (2018). The evolution of corporate cash. The
Review of Financial Studies, 31(11), 4288-4344.
You are provided with two datasets (both CSV file and SAS Data Set versions of the data
are provided on Canvas):
1. “fundamentals” containing firm fundamentals/financial reporting data
2. “market” containing stock return and other trading information.
The source of the data is CRSP and Compustat. The data is provided at the yearly level.
It covers 70 years from 1950 to 2019. In total, there are 156,823 firm-year
observations. Both of the datasets cover the same time period and the same firms. The
variables included in the data are defined in Table 1 below.
S1 2023 ACCTG/FINANCE 701 2
Table 1 Variables and definitions
Variable Dataset Definition
Age fundamentals The firm’s age, estimated as the difference
between the current year and the year in which
the firm’s stock first traded.
Assets fundamentals The firm's total assets (in $ million)
Cash fundamentals A firm's cash plus short-term investments (in $
million)
CompanyName fundamentals The name of the firm
CurAssets fundamentals A firm’s cash and other assets that are
expected to be realised in cash or used in the
production of revenue within the next 12
months
CurLiabilities fundamentals A firm’s liabilities due within one year, including
the current portion of long-term debt
Dividends fundamentals A firm’s total amount of dividends (other than
stock dividends) declared on the
common/ordinary capital of the company,
based on the current year's net income
EBIT fundamentals A firm's earnings before interest and taxes
(EBIT) (in $ million)
EBITDA fundamentals A firm's earnings before interest, taxes,
depreciation and amortisation (EBITDA) (in $
million)
Equity fundamentals The firm's book value of assets less book value
of liabilities (in $ million)
FirmID both A unique identification number assigned to
each firm
Liabilities fundamentals The firm's total liabilities (in $ million)
LTDebt fundamentals A firm's long-term debt (in $ million)
MarketCap market The market value of the firm’s equity (in $
million)
NetIncome fundamentals A firm's net income (in $ million)
Return market The return on the firm's stock over the year
Revenue fundamentals A firm's total revenue (in $ million)
SICIndustry fundamentals SIC Industry group to which the firm belongs
Year both The year
S1 2023 ACCTG/FINANCE 701 3
Required statistical software:
To do this assignment, you will need to use statistical software. You are free to use
whichever software package you like, but the recommended software is either SAS or
Stata. (Excel is unlikely to be sufficient to complete all parts of the assignment). SAS
and Stata are installed on many university computers. You can also access them through
your personal computer through FlexIT (see instructions provided previously).
Detailed instructions:
You need to use the two datasets provided to perform a series of data manipulation and
analysis steps as detailed below. Most (but not all) of the steps have required output
that you need to include in your assignment submission as indicated. Checks are
provided in many of the steps so you can make sure your analysis is correct.
In your submission, make sure that you include all the required output under each step.
You should also include any code you use in your analysis as an appendix. Your
assignment should be submitted as a pdf file.
Steps to follow:
1. Merge/join the two datasets together by firm and year using the identifier,
“FirmID”, and “Year”.
Required output to include in submission: None.
Check: The merged dataset should have 156,823 observations and 19 variables.
(0 marks)
2. Create six new variables called “CashRatio”, “DARatio”, “ROA”, “DivPayer”,
“LnAssets” and “LnAge” as follows:
CashRatio =
ℎ
→ This is the firm’s cash holdings as a proportion of assets.
DARatio =
→ This shows a firm’s long-term debt as a proportion of total assets.
ROA =
→ This is the EBITDA return on assets ratio.
DivPayer = {
1 if Dividends > 0
0 otherwise
→ This is a dummy variable equal to 1 if a firm is a dividend payer (has
declared dividends greater than zero during the year), and zero otherwise.
LnAssets = ln( ∗ 1,000,000)
→ This is the natural logarithm of a firm’s total Assets.
S1 2023 ACCTG/FINANCE 701 4
LnAge = ln()
→ This is the natural logarithm of a firm’s age in years.
Required output to include in submission: None
Check: The value of the above variables for the firm with FirmID 4115 in year
2005 should be:
Variable
Value for firm with
FirmID 4115 in year
2005
CashRatio 0.189052864
DARatio 0.053987156
DivPayer 1
LnAssets 25.49025721
LnAge 4.110873864
ROA 0.177876069
(0 marks)
3. Produce a table showing summary statistics for the variables: Age, Assets, Cash,
EBITDA, Equity, Liabilities, LTDebt, MarketCap, NetIncome, Return, Revenue,
CashRatio, DARatio, DivPayer and ROA. You should include the following
statistics:
- Number of observations
- Mean
- Standard deviation
- Minimum
- Maximum
Required output to include in submission: A table displaying the summary
statistics as indicated above.
Check: Below is an excerpt from the results:
(1 mark)
4. Produce a correlation matrix for the variables: Age, MarketCap, Return,
CashRatio, DARatio and ROA.
Required output to include in submission: A table/matrix that displays the
correlation coefficients and an indication of the statistical significance for each
pair of variables (eg p-values).
Check: Below is an excerpt from the results:
S1 2023 ACCTG/FINANCE 701 5
(1 mark)
5. Carry out a t-test for the difference in means to test the following null hypothesis:
H0: There is no difference in the cash ratio between firms that pay a dividend and
firms that don’t pay a dividend.
Assume unequal variances between the two groups when carrying out the t test.
Required output to include in submission: Display the results of your t test
(including difference in mean cash ratio between the two groups and statistical
significance) in a table. Write one sentence summarising your conclusion in
relation to the null hypothesis.
Check: The difference in the means between the two groups should be 0.1013.
(2 marks)
6. Produce a line chart that shows how the annual cash ratio varies over the sample
period for firms as a whole. The chart should show two series:
a. The equally weighted average cash ratio across all firms by year.
b. The average cash ratio by year, value weighted across firms according to
firms’ market capitalisation each year.
A hypothetical example demonstrating the calculations for a single year with 3
firms is shown below:
Equally weighted average =
0.10+0.25+0.45
3
= 0.267
Value weighted average =
100
500
× 0.10 +
150
500
× 0.25 +
250
500
× 0.45 = 0.32
Required output to include in submission: A chart showing the two series as
described above. Note: you can create the chart in SAS/Stata directly if you like.
However, if you prefer you can also export the required data to a csv file, open it
in Excel and then create the chart in Excel.
Check: A plot of the equally weighted series is shown below:
Firm CashRatio MarketCap
A 0.10 100
B 0.25 150
C 0.45 250
Total 500
S1 2023 ACCTG/FINANCE 701 6
(2 marks)
7. Calculate a new variable called “LagReturn”. This is the return on a firm's stock
over the previous year.
Required output to include in submission: None
Important: The LagReturn variable should only have a value for observations
where there is a return available for the previous year for the same firm.
Therefore:
• the LagReturn variable will have a missing value for the observation
corresponding to the first year for each firm
• if, for a given observation, there is no data available for the previous year
for that firm, then the LagReturn variable will have a missing value for
that observation.
Check: The value of LagReturn for the firm with FirmID 5540 in year 2002 should
be 0.04469. The dataset should have 15,964 observations with missing values for
LagReturn.
(0 marks)
8. Run the following regressions:
Model 1: ℎ, = 0 + 1, + ,
Model 2: ℎ, = 0 + 1, + 2, + 3, + 4, +
5, + ,
Model 3: ℎ, = 0 + 1, + 2, + 3, + 4, +
5, + + ,
Model 4: ℎ, = 0 + 1, + 2, + 3, + 4, +
5, + + ,
Model 5: ℎ, = 0 + 1, + 2, + 3, + 4, +
5, + + + ,
Required output to include in submission:
A single table displaying the results of each of the above regression models. The
table should show the following for each model:
- Coefficient estimates. (Note: for models that include fixed effects,
coefficient estimates for the fixed effects don’t need to be shown in the
table. Instead, simply indicate whether or not the fixed effects are
included in a given model. The value for the intercept also does not need
to be shown in the table.)
0.00
0.05
0.10
0.15
0.20
0.25
0.30
19
50
19
55
19
60
19
65
19
70
19
75
19
80
19
85
19
90
19
95
20
00
20
05
20
10
20
15
ewCashRatio
S1 2023 ACCTG/FINANCE 701 7
- Standard errors or t-statistics for the coefficient estimates.
- Indication of the statistical significance of each coefficient estimate (eg
stars).
- R-squared.
Interpret the coefficient estimates of:
- 3 and 4 in Model 3.
Describe what the purpose is of including industry and year fixed effects.
Check: The coefficient estimate of 1 should be in -0.01525 Model 1 and the
coefficient estimate of 5 should be 0.00887 in Model 5.
(4 marks)
9. Come up with your own research question related to the topic of corporate cash
holdings and carry out some quantitative analysis to answer your research
question.
This question is a chance for you to apply the quantitative analysis skills and
techniques covered in this course to carry out your own small piece of research.
You can choose any research question you like related to the above topic. You
then need to design and carry out some quantitative analysis to answer this
research question.
If you wish to add more data, you can, but this is NOT required. You can get full
marks by using only the data provided for the assignment.
You will be graded based on the quality of your analysis and how well you present
and interpret it. As a guide for how much work is required, your answer for this
question should be no longer than 5 pages.
Required output to include in submission:
Your answer should include the following sections:
Introduction/Research question
Decide on a research question related to the above topic. Describe your research
question and include in your description an appropriate null hypothesis and
alternative hypothesis. Explain the purpose of your research question (ie why it is
an interesting and relevant question).
Data and Methodology
Clearly describe your approach to the analysis and the methodology you are
applying, including statistical tests, econometric models etc as appropriate.
Provide some information about the data (eg descriptive statistics).
Results/Conclusion
Present the results of your analysis and interpret these results in the context of
your research question. Provide a conclusion to answer your research question.
Explain whether or not the null hypothesis can be rejected in favour of the
alternative hypothesis. Provide some discussion of limitations of your research
and/or potential opportunities for further research where relevant.
Note: Presentation is important for this question. You should pay attention to
factors such as structure and formatting, presenting important information clearly
and use of tables/charts where appropriate. Don’t forget to reference if you refer
to external sources.
S1 2023 ACCTG/FINANCE 701 8
(15 marks)
Detailed marking guide
For questions 1-8, you will get full marks if you get the right answer. No marks are
awarded for presentation, so you can just copy and paste your outputs from Stata/SAS.
However, make sure it is clear and don’t forget to provide the short sentence answers
where needed.
10 marks (total for questions 1-8)
For question 9, the below marking guide will apply.
Category Criteria Excellent Good Fair/Poor
Presentation
3 marks
Clear and logical
structure and
flow
Written with a
coherent structure
that flows logically
and is easy for the
reader to follow.
Generally coherent
with some areas
that could be
improved to help the
reader follow more
easily.
Unstructured and
does not flow
logically, making it
difficult for the
reader to follow.
High quality
formatting
Clean and tidy
formatting
appropriate for a
‘final draft’ of a
research study. This
means that the
formatting style is
applied consistently
throughout the
paper, there are
appropriate
headings and
tables/charts are
cleanly formatted
with only necessary
information shown.
Mainly clean and
tidy formatting
appropriate for a
‘final draft’ of a
research study. May
be some
inconsistencies.
Style and format not
of the required
standard. May be
some copying and
pasting directly from
SAS/Stata outputs
rather than
information being
presented in
formatted tables.
Analysis and
write-up
12 marks
Well thought-
out research
question with
clear hypothesis
and null
hypothesis
Purpose of research
question and why it
is of interest is
explained. Clear
hypothesis and null
hypothesis provided.
Some description of
research question
together with
hypothesis and null
hypothesis provided.
Hypothesis and null
hypothesis unclear
and/or no purpose
for research
question provided.
Methodology is
appropriate and
well explained
Methodology
(including data,
econometric
techniques etc) is
appropriate for the
research question
being analysed and
is clearly explained.
Methodology is
satisfactory for
answering the
research question
and explanation is
provided.
Methodology is not
adequate to answer
the research
question and little or
no explanation is
provided.
Results are
correctly and
clearly
interpreted in
the context of
Results are clearly
presented,
interpreted
correctly and
discussed. Results
Results are
presented and
interpreted
correctly. Results
are used to answer
Limited or incorrect
interpretation of the
results and what
they mean for the
null hypothesis.
S1 2023 ACCTG/FINANCE 701 9
the research
question
are used to answer
the research
question and
determine whether
the null hypothesis
can be rejected.
Limitations and/or
opportunities for
further research
are explained.
the research
question and
determine whether
the null hypothesis
can be rejected.
Limited explanation
of limitations and/or
opportunities for
further research is
provided.
Little or no
explanation of
limitations and/or
opportunities for
further research is
provided.