ECON F 412 / FIN F 313 – Group Assignment Problem Statement
The entire task is to be done in ONE Excel file. Max. group size 5 members.
Once you have finalised the company fill this Google form (ONLY one member from each group
must fill the Google form): [Link]
ALL DATA used for completing the task must be provided (in separate sheets as per the task)
Make different sheets in the Excel file and rename it accordingly.
Show all the working, steps etc. keep the formulae etc. so that it can be evaluated.
The first sheet must contain the Names and IDs of all contributors (even if it is a single-member group).
Ensure accuracy in mentioning the ID number.
Only one person, must submit the assignment.
I will share a Google link for uploading the assignment file. Do not share the link to Google drive or
share assignment via email.
Select an Indian listed company preferably from the mid-Cap segment. Give one or two line justification
to support the selection.
It is your responsibility to check beforehand the data is available for all tasks for the company your
group has selected, the company is currently listed, and that you will not face any issues with analysis
later.
Marks will be deducted if the above instructions are not followed while assignment submission.
This assignment is open-ended and marks will be awarded on relative basis depending upon the
approach and method used for the analysis, coverage of the task, presentation of the results, and
discussion of results.
Part I: Past two-year analysis – Fundamental financials of the company (35% of total
assignment weight)
In Part I, you are required to analyze the firm's financial (fundamental) performance as reported in the
annual reports for the past two years. Use the below-given variables as the benchmark for the analysis.
At the end of Part 1, provide a comparative analysis of the firm with the overall industry metrics for the
below-given (appropriate) variables. Give particular emphasis on various relative valuation ratios and
other ratios as given below.
Evaluation criteria (FY 23 and 24 or FY 24 and 25 whichever is available): Prepare a table
keeping variables in the first column followed by FYs.
a. Industry classification
Age of the firm
b. Last two years’:
P/E ratio
EBITDA/Total Assets
Enterprise Value/EBITDA
Intangible assets/Total assets
Dividend yield
Total liabilities/Total Assets
Sources of funding for the company’s projects and funding (financing) patterns over the past
two years
Long-term debt/Total Assets
Annual Capex/Total Assets
Total asset turnover and return on assets
Growth rate in sales
Profit margins
Return on Equity (as measured using the DuPont system)
Free cash flows
Summarise: As per your findings, does the firm’s fundamental financials perform better or worse than
the industry average.
Part II: Price movement analysis (30% of total assignment weight)– either considering the latest
price, historical price, or data for the past one year, depending on what is asked:
Lifetime high and low
Current price’s % discount (premium) to lifetime high
The average volume of shares traded daily over the past 52 weeks (keep the data period
consistent)
Weekly historical returns over the past one year
Dividend payment history over the past two years – which types of dividends are paid
History of stock split over the past two years
Share repurchase over the past two years
Mergers and acquisition, any other significant corporate action (past two years)
Market reaction to earnings announcement for past four quarters (year ending FY 24 or FY
25)
Part III. Portfolio analysis (35% of total assignment weight): Data period – last 3 years ending either
current price or any recent date.
a. Construct a portfolio consisting of five stocks. Assume a 30% investment in the primary company
(the company you have chosen) and value-weighted for the remaining 4. Select stocks from different
sectors, preferably having MCAP of at least Rs 5000 crore when buying. Portfolio construction date:
01 April 2022. No need to rebalance the portfolio thereafter.
Use portfolio evaluation metrics such as Sharpe ratio, Treynor ratio etc. and compare the portfolio’s
performance over the last three years with a benchmark index. Explain the reasons behind the deviation
between index returns and your portfolio’s returns. For the original portfolio, analyze portfolio
performance individually for each year (take beginning and ending prices for the year).
b. Evaluate the portfolio’s performance versus the single stock (primary company) and analyse whether
the individual stock has a better or worse risk-reward pattern than the portfolio. Use risk and return
statistics to elaborate. Does diversification provide benefits?
c. Prepare a correlation matrix consisting of stocks selected for constructing the portfolio. Consider all
the five stocks for the portfolio. Construct a portfolio that (a) mimics the Sharpe ratio of Nifty or Sensex;
(b) minimum variance portfolio.
Deadline – 30th November (EoD)
***************