Project: Building a Financial Model for Larsen & Toubro (L&T)

Problem Statement:

The primary challenge is to enable students to develop a practical understanding of financial modeling by
building a comprehensive model for Larsen & Toubro (L&T). Students will leverage historical data, perform
detailed analyses, and project future performance while applying valuation techniques. This project aims to
bridge the gap between theoretical knowledge and real-world financial modeling practices.

Objectives:

1.​ Equip students with hands-on experience in collecting, cleaning, and analyzing financial data.
2.​ Teach the creation of an integrated three-statement financial model.
3.​ Develop skills in financial forecasting, scenario planning, and sensitivity analysis.
4.​ Enable the application of valuation methodologies like DCF and Comparable Company Analysis.
5.​ Foster proficiency in creating dashboards and summarizing key financial insights.

Project Structure:

Phase 1: Data Collection & Setup

●​ Task: Collect historical financial data from reliable sources like L&T’s Annual Reports, Moneycontrol,
Screener, or Ticker Tape.
●​ Deliverables:
○​ A "Raw Data" sheet containing 5 years of historical financial statements (Income Statement,
Balance Sheet, and Cash Flow Statement).
 ○​ Cleaned and formatted data ready for analysis.
●​ Formatting Requirements:
○​ Maintain a structured format in Excel.
○​ Clearly label and organize data for seamless navigation.

Phase 2: Financial Model Construction

●​ Components:
1.​ Three-Statement Model:
■​ Income Statement
■​ Balance Sheet
■​ Cash Flow Statement
2.​ Projections (3–5 years):
■​ Revenue growth assumptions.
■​ Cost structure and operating expense forecasts.
■​ Capital Expenditure (CapEx) and financing plans.

Phase 3: Supporting Schedules

●​ Schedules:
○​ Asset & Depreciation Schedule: Calculate PPE with an appropriate depreciation methodology.
○​ Working Capital Schedule: Analyze receivables, payables, and inventory movements.
○​ Debt & Equity Modeling:
■​ Develop debt repayment schedules and calculate interest.
■​ Track equity changes, including share issuance and dividend payouts.

Phase 4: Valuation Techniques

●​ DCF Analysis:
○​ Calculate WACC.
○​ Forecast Free Cash Flows (FCFs) and discount them to present value.
○​ Perform sensitivity analysis on key variables (e.g., growth and discount rates).
 ●​ Comparable Company Analysis:
○​ Select peer companies.
○​ Benchmark L&T using valuation multiples (P/E, EV/EBITDA, P/B).

Phase 5: Scenario Planning & Sensitivity Analysis

●​ Evaluate performance under best-case, base-case, and worst-case scenarios.

●​ Perform sensitivity analysis on variables like interest rates, revenue growth, and margins.

Phase 6: Dashboard & Summary Overview

●​ Dashboard:
○​ Create graphs and charts to showcase:
■​ Revenue trends.
■​ Profitability ratios.
■​ Debt vs. equity trends.
■​ Valuation comparisons.
●​ Executive Summary:
○​ Highlight key findings, assumptions, and valuation insights.

Phase 7: Ratio Analysis

●​ Compute and interpret:

○​ Profitability Ratios: Gross Margin, Operating Margin, Net Margin, ROE, ROA.
○​ Liquidity Ratios: Current Ratio, Quick Ratio.
○​ Solvency Ratios: Debt-to-Equity, Interest Coverage.
○​ Efficiency Ratios: Asset Turnover, Working Capital Turnover.
Rubrics:

1.​ Data Collection & Formatting (10 Points): Proper extraction, organization, and structuring of raw financial
data from reliable sources.
2.​ Index Sheet & Overview (10 Points): Organized navigation and concise financial summary.
3.​ Three-Statement Model (20 Points): Accurate Income Statement, Balance Sheet, and Cash Flow
Statement with correct formulas.
4.​ Financial Projections (10 Points): Logical and well-supported revenue, cost, and capital expenditure
projections.
5.​ Supporting Schedules (10 Points): Detailed schedules for Asset & Depreciation, Working Capital, and
Debt/Equity with correct linkages to financial statements.
6.​ Valuation (10 Points): Correct WACC calculation, DCF modeling, and peer benchmarking.
7.​ Implied Share Price Calculation (20 Points): The implied value per share should be between 2800-4200.
8.​ Scenario Planning & Sensitivity Analysis (10 Points): Inclusion of multiple financial scenarios and
analysis of key variable sensitivity impact.
9.​ Ratio Analysis (10 Points): Correct computation and interpretation of key financial ratios.
10. Dashboard & Presentation (10 Points): Professional formatting, clear data visualization, and a
well-structured layout.

How to Make It Better?

 1.​ Enhanced Visualization:
○​ Use advanced Excel charting tools or integrate Power BI for dynamic visualizations.
2.​ Real-Time Updates:
○​ Include formulas or automation for real-time updates using Excel add-ins or APIs.
3.​ Detailed Assumption Sheet:
○​ Provide comprehensive documentation of assumptions and their rationale.
4.​ Peer Review:
○​ Organize peer feedback sessions to refine the model.
5.​ Interactive Elements:
○​ Use slicers and interactive dashboards for better user experience.
6.​ Industry Context:
○​ Incorporate industry trends and economic forecasts for more realistic projections.

Submission Guidelines:

●​ The Excel model (.xlsx format) must be submitted with all calculations and formulas intact.
●​ A separate sheet must document key assumptions and data sources.
●​ The Dashboard must visually summarize the model’s insights.
●​ Any missing calculations, incorrect data formatting, or weak justification of assumptions will result in
mark deductions.

How to ZIP a PDF file:

●​ Put all of the PDF documents you want to compress (or just one) into a new folder.
●​ Right click on that folder.
 ●​ Select the “Compress to ZIP file” option and then click “Compressed (Zipped) folder.”
●​ A new .ZIP file will be created that contains your document(s).

In order to submit the projects please follow the following steps:

1.​ Click on “ Your progress View details” after logging into your dashboard.​

2.​ Next, click on the tab for the specific child course for which you want to download the problem statement.
Then, scroll down to find the "Course Project" section.​
3.​ Now, click on the three dots on the right-hand side of the "Course Project" tab to select "Download
Problem Statement."​

4.​ Please follow the guidelines (screenshot is shared below) provided in the project to ensure correct
submissions. Then, click on "Upload Project Solution" to submit your work.​