$9$:$15$ C

Back WRDS Project$\_$ACC $315\_2024$

$2.$ Data Transforming

In the merged table, create a column "Holiday" with value of $1$ if that quarter includes the month of December. $(5$ pts$)$ Hint:

If a fiscal quarter ends at the end of a month, extract the calendar month from the date by using the MONTH function.

Then use the IF function to return value of $1$ if the value of month meets certain condition.

Question: Answer with words $(10$ pts$)$

Companies' fiscal quarter ends usually fall at the end of a month, but it is not always the case. If we consider the possibility that the fiscal quarter could end on any day, how would it complicate the data transforming process of identifying a holiday quarter? And how would you adjust your data transforming strategy to better identify a holiday quarter? You don't have to give an exclusive list of all possibilities, but please answer by giving at least one specific example that is different from the usual case. You don't need to actually do it in Excel.

Data Analyzing $($Data & Answer with Words$)$

$3\mathrm{.}1$ Based on the industry level ratios table, use Pivot table to summarize data so that you can answer the following questions. $(14$ pts$)$ Which industry$($industries$)$ is$($are$)$:

a$.$ valued by the stock market the highest?

b$.$ the most profitable?

c$.$ the least likely to suffer from solvency issues?

d$.$ the least likely to suffer from liquidity issues?

e$.$ operated most efficiently?

f$.$ most R&D intensive?

g$.$ most heavily invested in advertising?

Hint:. Also, there are two variables for a$-$e $($except for R&D and advertising$),$ and they may not always lead to the same conclusion. So$,$ if you find a question hard to answer due to this reason, don't force yourself to pick one industry but give an explanation based on your data.

Would you rather use only one variable for each dimension so that it gives a clearer picture? Why or why not?

$3\mathrm{.}2$ Based on the firm level ratios table, calculate the correlation coefficient of the two variables of the same type for variables ae$.$ For example: gross profit margin and net profit margin as measures of profitability. Combined with $3\mathrm{.}1,$ in which circumstances do you think this coefficient information could be useful? $(10$ pts$)$

$3\mathrm{.}3$ Based on the merged table, choose and design appropriate data summary and visualizations to help evaluate the company using the industry ratios as benchmark. Please apply the principles for high$-$quality visualizations: simplicity, emphasis, ethical presentation. $(21$ pts$)$

Please evaluate from at least three different aspects $($ratios$).$

Please don't over oloes not actually