Excel for financial analysis

4.2.1 Choosing your tool

You need one spreadsheet program, and any of these three works for the entire program:

If you already pay for Microsoft 365, use Excel, since it is what you'll meet professionally. Otherwise Google Sheets is the default free choice, with LibreOffice kept as the offline backup. Every formula below works in all three, and section 4.2.5 flags the shortcut differences. Spend no more than ten minutes choosing. Skills are 95% portable across them, and that portability matters, because your competence should never be hostage to a license.

While you are in settings, fix the number format. Indian filings quote lakh (1,00,000) and crore (1,00,00,000), and the custom number format #,##,##0 renders Indian grouping in all three tools. Keep US sheets in standard grouping with $m or $bn in the header, and always label units. A sheet where nobody knows whether 4,521 is rupees, lakh, or crore is a defect, however neat it looks.

4.2.2 Layout discipline: inputs → calculations → outputs

One habit beats every other in financial modeling, and it costs nothing to adopt on day one:

1. Inputs live in one place. Every assumption, such as a growth rate, a price or a tax rate, is typed into one cell, in a clearly marked input block or input sheet. Banking supplies the color convention: inputs in blue font, formulas in black, links pulled from other sheets in green. Set it now and it will be second nature by Phase 3.
2. Calculations reference inputs, and never contain them. =B4*(1+$B$2) is auditable, because changing B2 updates everything downstream. =B4*1.12 hides a buried assumption. Six months on, nobody, including you, will know there's a 12% in there, and updating it means hunting every cell. "No hardcodes inside formulas" is rule one of professional modeling and the most common amateur failure.
3. Outputs are labeled and isolated. Answer cells sit apart, formatted so a reader finds them in seconds.
4. The 60-second audit test. A stranger opening your sheet should trace any number to its origin within a minute. If they can't, restructure. That's the standard. Beauty doesn't come into it.

Add two supporting habits. Never merge cells, because merging breaks selection and sorting and can break formulas too. And build time across columns and items down rows, so years march left to right and line items stack vertically, as in almost every professional model on earth.

4.2.3 References: relative, absolute, mixed: and F4

When you copy a formula, cell references shift with it unless you anchor them with $:

Press F4 and it cycles a reference through all four forms (B2 → $B$2 → B$2 → $B2) in Excel, Google Sheets, and modern LibreOffice Calc. Older Calc uses Shift+F4. Get it into your fingers today, because you'll press it thousands of times. Mixed references make two-way sensitivity tables possible, and you build one below.

4.2.4 The core function kit

You need perhaps a dozen functions to do 90% of analyst work. Here they are, demonstrated on the demo watchlist, a table you'll reuse throughout. ROCE is return on capital employed and D/E is debt-to-equity. The figures are illustrative, modeled on roughly FY25 scale, and pulling the real ones is part of your mini-project.

Which Excel functions do analysts use most?

Arithmetic and aggregation. =SUM(C2:C7) returns 206, =AVERAGE(C2:C7) returns 34.33 and =MAX(C2:C7) returns 64. =COUNTA(A2:A7) returns 6, because it counts non-empty cells, while COUNT counts only numbers.

Logic. =IF(C2>=20,"Pass","Check") reads as "if TCS's ROCE is at least 20, return Pass, else Check". Conditions combine with AND/OR: =IF(AND(C2>=20,D2<=0.5),"Quality screen pass","Check"). Run down the table and Titan alone returns "Check", because its illustrative D/E of 0.7 trips the leverage test. That case teaches you something. Titan's borrowing includes jewellery-trade gold-on-lease arrangements, a nuance a screen flattens, so screens start inquiries and never settle them. =IFERROR(formula, "n/a") wraps any formula and returns your fallback instead of an ugly #N/A or #DIV/0!.

Conditional aggregation. =COUNTIF(C2:C7,">=30") returns 4, for TCS, Asian Paints, ITC and Pidilite. =SUMIFS(C2:C7,D2:D7,"<=0.1") sums ROCE only for the low-leverage names, giving 64+31+37+30+19 = 181. Watch the syntax here, because it trips people up. SUMIFS takes the sum range first, then pairs of criteria range and criteria.

INDEX-MATCH vs XLOOKUP vs VLOOKUP: which should you use?

Lookups, the analyst's daily bread. You constantly need to fetch the value for X from some table.

• INDEX-MATCH works in every spreadsheet ever made: =INDEX($C$2:$C$7, MATCH("Titan", $A$2:$A$7, 0)) → 25. Read it from the inside out. MATCH("Titan", $A$2:$A$7, 0) finds Titan's position in the name column, which is 4, the fourth row of the range. The final 0 means exact match, so always use 0. Leave it out and you invite silent approximate matches, a classic source of wrong numbers. Then INDEX($C$2:$C$7, 4) returns the 4th item of the ROCE column, which is 25.
• XLOOKUP is modern, cleaner, and now in all three tools: =XLOOKUP("Titan", $A$2:$A$7, $C$2:$C$7, "not found") → 25. Its arguments are the lookup value, the column to search, the column to return, and an optional if-not-found message. Exact match is the safe default. It can look left as easily as right, and it won't break if someone inserts columns.
• Why not VLOOKUP? You'll meet =VLOOKUP("Titan",A2:D7,3,FALSE) everywhere, so know it, but prefer the two above. VLOOKUP counts columns by number, so inserting a column leaves the 3 silently pointing at wrong data. It cannot look left of the lookup column, and it defaults to approximate match if you forget FALSE. INDEX-MATCH and XLOOKUP have none of these traps.

That is the kit. You'll add EOMONTH/EDATE for date arithmetic naturally in Phase 3. Excel's What-If Data Table duplicates what the mixed-reference grid below does portably.

4.2.5 Keyboard fluency: the starter fifteen

Mouse-driven work is 3–5× slower than keyboard-driven work, and Phase 8's timed 60-minute teardowns will collect that difference, because every minute spent mousing is a minute not spent thinking. Learn these fifteen this week. The drill is the no-mouse hour, in which you rebuild build-along 1 start to finish without touching the mouse.

On a Mac, Cmd generally replaces Ctrl. F4 becomes Cmd+T in Excel for Mac. Minor variants exist across versions, so learn your tool's set cold.

4.2.6 Build-along 1: the compound-interest table

Open a blank workbook. Name the file analyst-toolkit and the sheet compounding. You're rebuilding, as a machine, what you did by hand in the bootcamp, which was ₹1,00,000 invested at 12% a year for 30 years.

Step 1: the input block, in blue font.

Step 2: the calculation table. Put the headers Year | Opening | Interest | Closing in row 8. Then:

• A9: 1 · A10: =A9+1, filled down to year 30. Select A10, set the range with Ctrl+Shift+Down, then press Ctrl+D.
• B9, the year-1 opening: =$B$3, absolute, because it is the input.
• C9, the interest: =B9*$B$4, opening balance times the rate. You anchor the rate because every row uses the same input cell.
• D9, the closing: =B9+C9.
• B10, the year-2 opening: =D9, last year's closing. It's relative on purpose, so each row chains to the one above.
• Select B10:D10, fill down to year 30.

Your first five rows should read (₹):

Step 3: the check cell, a habit for life. Every model carries at least one independent cross-check. Here it is the closed form from the bootcamp, FV = P × (1 + r)^n. Put =$B$3*(1+$B$4)^A9 in F9 and fill it down. In G9, =D9-F9 must show 0, apart from rounding dust. Year 30 reads ₹29,95,992 both ways. If the columns disagree, a formula is wrong. And the sheet caught the error by itself. Check cells exist for that.

How do mixed references build a sensitivity grid?

Step 4: a two-way sensitivity grid, where mixed references pay off. Below the table, put rates across the top and horizons down the side.

In C45, type one formula: =$B$3*(1+C$44)^$B45. Here the principal is fully anchored. Its rate is anchored to row 44, so it slides across columns but never down. Years are anchored to column B, so they slide down rows but never across. Copy C45 to the whole grid. The results:

Look at the bottom row for a moment. It's the bootcamp's lesson made visible. At 30 years, the gap between 8% and 14% comes to five times the money, though it looks like "6 points" on paper. One correctly anchored formula produced all twelve numbers. That is what mixed references are for.

Step 5: the no-mouse hour. Delete the calculation table but keep the inputs. Rebuild it entirely by keyboard. Then change the principal to ₹5,00,000 and watch everything flow. If any cell fails to update, you've got a hardcode somewhere. Find it and fix it.

4.2.7 Build-along 2: a mini common-size income statement

A common-size statement expresses every P&L line as a percentage of revenue. It's the fastest x-ray in analysis. Absolute rupees turn into structure, showing where each ₹100 of sales goes, and companies of different sizes become comparable. Phase 2 builds a whole method on this, and today you build the skeleton.

Open a new sheet called common-size. Enter this P&L for Hindustan Unilever, in ₹ crore. The figures are illustrative, modeled on ~FY24 scale; pull the actual annual report.

In C2, enter =B2/$B$2, formatted as a percentage, and fill down C2:C14. The $B$2 anchor does the work, because every line divides by the same revenue cell. Your results: revenue 100.0%, materials 50.0%, employees 5.0%, A&P 9.0%, other 13.0%, EBITDA 23.0%, D&A 2.0%, EBIT 21.0%, other income 1.5%, finance costs 0.5%, PBT 22.0%, tax 5.5%, PAT 16.5%.

Now read it like an analyst for thirty seconds. Half of every sales rupee buys materials. Nine paise are spent telling you about the soap, and that A&P line is a moat under construction, so hold the thought until Phase 4. And 16.5 paise reach shareholders. Whether these are good numbers is Phase 2's question, but you can now produce the x-ray on demand, and the guided annual-report read has you run one on real filing numbers.