M&A Financial Analysis and Investment Advice
Budget: ₹600 – ₹1,500 INR
Financial modelling using excel
Q2. KUNAL & Co. intends to take over DIVIJ & Co. using a combination of equity and loan financing. Based on the data provided for both companies, perform the following analyses:
i. Calculate the Proforma Earnings Per Share (EPS) and Compare and comment on the above three Proforma EPS values to determine which option is most beneficial for KUNAL & Co., based on the Accretion or Dilution in EPS of the merged entity under the following scenarios:
a) When 100% new equity shares are issued to finance the acquisition.
b) When 50% new equity shares are issued.
c) When no new equity shares are issued.
ii. Loan Financing Evaluation: Suppose KUNAL & Co. considers financing 80% of the acquisition cost through a loan at 15% interest rate. The offer price to shareholders of DIVIJ & Co. is ₹30 per share, which includes a ₹5 premium. The loan repayment will be made in three instalments: ¼ in the first year, ¼ in the second year, and the remaining balance in the third year (starting from the next year). Evaluate whether taking this loan option is a financially suitable choice for KUNAL & Co.
[7+8=15 Marks]
Q3. Nitya and Naman—two finance enthusiasts who believe in “invest now, regret later”—have gathered the price data of three international stock indices. They’ve decided to invest USD 1 million, split 50:50 between any two stocks of their choice. However, there’s a small twist: their bank has forbidden them from investing in more than two stocks. So, diversification dreams have to wait!
Now, in their moment of confusion, they turn to their long-forgotten friend (you)—their partner-in-survival from the Financial Modelling Using Excel course. They remember that you’ve now made it big as a business tycoon, and to sweeten the deal, they’ve promised you 25% of their profits for the next five years (because friendship has its benefits).
Your task, as the financially wise and Excel-powered hero, is to help them decide: Which two stocks should Nitya and Naman select for investment? Your decision should be based on analysis using the Historical Simulation Method to calculate for both
• 99% 1-day Value at Risk (VaR)
• 99% 1-day Expected Shortfall (ES)/C-VaR
You must provide two separate investment recommendations — one based on the minimum Value at Risk (VaR) and another based on the minimum Expected Shortfall (ES) while staying within the investment constraint as You’re not just picking stocks—you’re protecting friendship, fortune, and financial sanity!
[5+5=10 Marks]
Q2. KUNAL & Co. intends to take over DIVIJ & Co. using a combination of equity and loan financing. Based on the data provided for both companies, perform the following analyses:
i. Calculate the Proforma Earnings Per Share (EPS) and Compare and comment on the above three Proforma EPS values to determine which option is most beneficial for KUNAL & Co., based on the Accretion or Dilution in EPS of the merged entity under the following scenarios:
a) When 100% new equity shares are issued to finance the acquisition.
b) When 50% new equity shares are issued.
c) When no new equity shares are issued.
ii. Loan Financing Evaluation: Suppose KUNAL & Co. considers financing 80% of the acquisition cost through a loan at 15% interest rate. The offer price to shareholders of DIVIJ & Co. is ₹30 per share, which includes a ₹5 premium. The loan repayment will be made in three instalments: ¼ in the first year, ¼ in the second year, and the remaining balance in the third year (starting from the next year). Evaluate whether taking this loan option is a financially suitable choice for KUNAL & Co.
[7+8=15 Marks]
Q3. Nitya and Naman—two finance enthusiasts who believe in “invest now, regret later”—have gathered the price data of three international stock indices. They’ve decided to invest USD 1 million, split 50:50 between any two stocks of their choice. However, there’s a small twist: their bank has forbidden them from investing in more than two stocks. So, diversification dreams have to wait!
Now, in their moment of confusion, they turn to their long-forgotten friend (you)—their partner-in-survival from the Financial Modelling Using Excel course. They remember that you’ve now made it big as a business tycoon, and to sweeten the deal, they’ve promised you 25% of their profits for the next five years (because friendship has its benefits).
Your task, as the financially wise and Excel-powered hero, is to help them decide: Which two stocks should Nitya and Naman select for investment? Your decision should be based on analysis using the Historical Simulation Method to calculate for both
• 99% 1-day Value at Risk (VaR)
• 99% 1-day Expected Shortfall (ES)/C-VaR
You must provide two separate investment recommendations — one based on the minimum Value at Risk (VaR) and another based on the minimum Expected Shortfall (ES) while staying within the investment constraint as You’re not just picking stocks—you’re protecting friendship, fortune, and financial sanity!
[5+5=10 Marks]
Related categories:
Accounting
Excel
Financial Research
Finance
Financial Analysis
Data Visualization
Data Analysis
Financial Modeling