The table below sets out the monthly total return, market capitalization, and research spending for ten firms over the four months from January 2020 to April 2020 inclusive. Each figure was observed at the end of the corresponding month.

	Monthly Return (%)
	Firm1	Firm2	Firm3	Firm4	Firm5	Firm6	Firm7	Firm8	Firm9	Firm10
Jan	9%	-9%	-3%	5%	10%	-3%	5%	10%	5%	-10%
Feb	-8%	10%	-6%	-7%	1%	-2%	-7%	6%	4%	9%
Mar	-3%	-10%	-1%	-7%	2%	0%	-5%	-7%	6%	10%
Apr	10%	-10%	-4%	4%	9%	8%	-2%	2%	4%	4%
	Market Capitalization ($ bn)
	Firm1	Firm2	Firm3	Firm4	Firm5	Firm6	Firm7	Firm8	Firm9	Firm10
Jan	306	255	505	693	104	147	310	432	546	612
Feb	282	281	475	644	105	144	288	458	568	667
Mar	273	252	470	599	107	144	274	426	602	734
Apr	300	227	451	623	117	156	268	434	626	763
	Research Spending (%)
	Firm1	Firm2	Firm3	Firm4	Firm5	Firm6	Firm7	Firm8	Firm9	Firm10
Jan	0%	11%	5%	0%	2%	17%	13%	7%	2%	3%
Feb	1%	17%	4%	4%	3%	20%	7%	20%	7%	0%
Mar	12%	1%	9%	4%	10%	15%	14%	3%	8%	4%
Apr	18%	0%	13%	2%	0%	14%	1%	16%	4%	18%

1. Calculate the value-weighted average returns in April 2020 for each of the quintile portfolios formed by sorting on lagged Research Spending. Use lagged market capitalization for calculating value-weighted returns. (Hint: There are five quintile portfolios, each containing one fifth of all the stocks.)

2. Calculate the value-weighted quintile hedge portfolio returns (aka factor portfolio returns equal to Quintile 5 minus Quintile 1 returns) in February, March, and April 2020.

3. Briefly explain why portfolios need to be sorted on lagged Research Spending to test return predictability.

(Plz use excel if possible! )

ATTACHMENT:

The table below sets out the monthly total return, market capitalization, and research spending for ten firms over the four months from January 2020 to April 2020 inclusive. Each figure was observed at the end of the corresponding month. Jan Feb Mar Apr Firm1 9% -8% -3% 10% Firm2 -9% 10% -10% -10% Firm3 -3% -6% -1% -4% Monthly Return (%) Firm4 Firm5 Firm6 | Firm 7 5% 10% -3% 5% -7% 1% -2% -7% -7% 2% 0% -5% 4% 9% 8% -2% Firm8 10% 6% -7% 2% Firm 9 5% 4% 6% 4% Firm 10 -10% 9% 10% 4% Jan Feb Mar | Apr Market Capitalization ($bn) Firm1 Firm2 Firm3 Firm4 | Firm5 Firm6 Firm7 306 255505693104147310 282 281 475 644 105 144 288 273252470599 107 144 274 300 2 27 451 623 117 156 268 Firm8 432 4 58 426 434 Firm 9 546 568 602 626 Firm 10 612 667 734 763 Jan Feb Mar Apr Firm1 0% 1% 12% 18% Firm2 11% 17% 1% 0% Firm3 5% 4% 9% 13% Research Spending (%) Firm4 Firm5 Firm 6 Firm7 0% 2% 17% 13% 4% 3% 20% 7% 4% 10% 15% 14% 2% 0% 14% 1% Firm8 7% 20% 3% 16% Firm 9 2% 7% 8% 4% Firm 10 3% 0% 4% 18% a) Calculate the value-weighted average returns in April 2020 for each of the quintile portfolios formed by sorting on lagged Research Spending. Use lagged market capitalization for calculating value-weighted returns. (Hint: There are five quintile portfolios, each containing one fifth of all the stocks.) b) Calculate the value-weighted quintile hedge portfolio returns (aka factor portfolio returns equal to Quintile 5 minus Quintile 1 returns) in February, March, and April 2020. 6) Briefly escaleta wty portfolios need t c) Briefly explain why portfolios need to be sorted on lagged Research Spending to test return predictability. The table below sets out the monthly total return, market capitalization, and research spending for ten firms over the four months from January 2020 to April 2020 inclusive. Each figure was observed at the end of the corresponding month. Jan Feb Mar Apr Firm1 9% -8% -3% 10% Firm2 -9% 10% -10% -10% Firm3 -3% -6% -1% -4% Monthly Return (%) Firm4 Firm5 Firm6 | Firm 7 5% 10% -3% 5% -7% 1% -2% -7% -7% 2% 0% -5% 4% 9% 8% -2% Firm8 10% 6% -7% 2% Firm 9 5% 4% 6% 4% Firm 10 -10% 9% 10% 4% Jan Feb Mar | Apr Market Capitalization ($bn) Firm1 Firm2 Firm3 Firm4 | Firm5 Firm6 Firm7 306 255505693104147310 282 281 475 644 105 144 288 273252470599 107 144 274 300 2 27 451 623 117 156 268 Firm8 432 4 58 426 434 Firm 9 546 568 602 626 Firm 10 612 667 734 763 Jan Feb Mar Apr Firm1 0% 1% 12% 18% Firm2 11% 17% 1% 0% Firm3 5% 4% 9% 13% Research Spending (%) Firm4 Firm5 Firm 6 Firm7 0% 2% 17% 13% 4% 3% 20% 7% 4% 10% 15% 14% 2% 0% 14% 1% Firm8 7% 20% 3% 16% Firm 9 2% 7% 8% 4% Firm 10 3% 0% 4% 18% a) Calculate the value-weighted average returns in April 2020 for each of the quintile portfolios formed by sorting on lagged Research Spending. Use lagged market capitalization for calculating value-weighted returns. (Hint: There are five quintile portfolios, each containing one fifth of all the stocks.) b) Calculate the value-weighted quintile hedge portfolio returns (aka factor portfolio returns equal to Quintile 5 minus Quintile 1 returns) in February, March, and April 2020. 6) Briefly escaleta wty portfolios need t c) Briefly explain why portfolios need to be sorted on lagged Research Spending to test return predictability