The equity corkscrew closes the loop your paper linkage called tie #1. Net income isn't "made of cash". It goes into retained earnings, and only the dividend and buyback decisions move cash. Build it in M rows 101 to 110, with the header in 101:
| M row | Line | Formula in F (fill right) | Note |
|---|---|---|---|
| 102 | Retained earnings, opening | =E40 | Daisy chain via the BS, as in the PP&E schedule |
| 103 | Net income | =F21 | From the IS |
| 104 | Dividends | =MAX(F103,0)*A!F61 | Payout % of NI, floored so no dividends come out of losses |
| 105 | Buybacks | =A!F50 | The ACTIVE buyback row: a decision, not a ratio |
| 106 | Retained earnings, closing | =F102+F103-F104-F105 | |
| 107 | Shares outstanding, opening (cr) | =E109 | E109 is typed history: 25.0 |
| 108 | Shares bought back (cr) | =IF(A!F62>0,F105/A!F62,0) | ₹ cr ÷ ₹/share = crore shares |
| 109 | Shares outstanding, closing (cr) | =F107-F108 | Type 25.0 in C109:E109 first |
| 110 | EPS (₹), memo | =F21/F109 | ₹ cr ÷ cr shares = ₹/share; fill C:J (=C21/C109) |
Link the BS: F40 =F106, filled right. Share capital in row 39 stays flat at ₹50 cr, which is 25 crore shares × ₹2 face value.
Advertisement
What does the schedule simplify about buybacks and dividends?
Name three simplifications out loud, because a good modeler documents the difference between the model and the law. (1) In India, an actual buyback extinguishes the shares and technically reduces share capital by face value. It routes the premium against reserves (securities premium first) and requires a capital-redemption reserve transfer under sections 68-69 of the Companies Act. Economically it all comes to "equity down by the buyback amount," which is what row 105-into-106 books. Say so in the README instead of pretending the one-line version is the statute. (2) In the US, repurchased shares usually persist as treasury stock, a negative line within equity. That's how Home Depot's decades of buybacks pushed total equity below zero while the business prospered. To model it, add a "Treasury stock (negative)" corkscrew row to the equity block and leave retained earnings whole. Same economics, different geography on the statement. (3) Then there's dividend timing. Row 104 pays out of the same year's income (declared = paid). Real Indian companies pay a final dividend after year-end, and a payable would time-shift it. That's immaterial here, though it's material for a bank model.
Worked example: the Base case check
Checkpoint 4 (Base). FY26: dividends 31.2, closing RE 479.9 (= 407.0 + 104.2 − 31.2). Net income arrives through the interest links in the debt schedule, so if your F21 is still zero, revisit this checkpoint after phase 7. FY30 closing RE: 869.2. Shares stay at 25.0 crore in Base, which has no buybacks. In Bull they fall once FY28 arrives, at 75/600 = 0.125 crore shares retired per year.