Section Learning Objectives
Understanding PP&E Roll-Forward
The PP&E Roll-Forward tracks how Property, Plant & Equipment changes over time.
It links the Balance Sheet (Net PP&E) to the Cash Flow Statement (Capex, Depreciation).
🏭 Gross PP&E Roll-Forward
Beginning Gross PP&E
+ Capital Expenditures
− Disposals (at cost)
Tracks the total cost of all assets owned
📉 Accumulated Depreciation Roll-Forward
Beginning Accum. Depr.
+ Depreciation Expense
− Depreciation on Disposals
Tracks total depreciation taken on assets
✅ Net PP&E Calculation
This is the book value shown on the Balance Sheet
Complete PP&E Roll-Forward Schedule
PP&E Roll-Forward Schedule - TCS
5-Year Forecast (₹ in crores)
| Line Item | FY2025A | FY2026E | FY2027E | FY2028E | FY2029E | FY2030E |
|---|---|---|---|---|---|---|
| GROSS PP&E | ||||||
| Beginning Balance | 3,630,000 | 3,993,000 | 4,392,300 | 4,831,530 | 5,314,683 | 5,846,151 |
| + Capital Expenditures | 363,000 | 399,300 | 439,230 | 483,153 | 531,468 | 584,615 |
| - Disposals | 0 | 0 | 0 | 0 | 0 | 0 |
| = Ending Gross PP&E | 3,993,000 | 4,392,300 | 4,831,530 | 5,314,683 | 5,846,151 | 6,430,766 |
| ACCUMULATED DEPRECIATION | ||||||
| Beginning Balance | 1,878,000 | 2,095,800 | 2,335,380 | 2,597,823 | 2,884,436 | 3,196,956 |
| + Depreciation Expense | 217,800 | 239,580 | 262,443 | 286,613 | 312,520 | 340,266 |
| - Depr. on Disposals | 0 | 0 | 0 | 0 | 0 | 0 |
| = Ending Accum. Depr. | 2,095,800 | 2,335,380 | 2,597,823 | 2,884,436 | 3,196,956 | 3,537,222 |
| NET PP&E | ||||||
| Net PP&E | 1,897,200 | 2,056,920 | 2,233,707 | 2,430,247 | 2,649,195 | 2,893,544 |
Excel Formulas for PP&E Schedule
📝 Key Excel Formulas
Beginning = Prior Year Ending Gross PP&E
Ending = Beginning + Capex - Disposals
Beginning = Prior Year Ending Accum. Depr.
Ending = Beginning + Depreciation - Depr. on Disposals
= Ending Gross PP&E - Ending Accum. Depreciation
Don't forget to link Beginning Balance to Prior Year Ending!
Beginning Balance 2026E = Ending Balance 2025A
This creates the "roll-forward" that connects all years together.
Quality Checks for PP&E Schedule
✅ Built-in Error Checks
=IF(Net_PPE < 0, "ERROR: Negative Net PP&E", "OK")
=IF(Accum_Depr > Gross_PPE, "ERROR: Depr. exceeds assets", "OK")
=IF(Depreciation/Gross_PPE > 0.15, "CHECK: High depreciation rate", "OK")
=IF(Beginning_Gross = Prior_Ending_Gross, "OK", "ERROR: Check roll-forward")
Create a "Checks" section at the bottom of your PP&E schedule with all these error checks. Use conditional formatting to highlight errors in red. All checks should show "OK" in a correctly built model.
Hands-On: Build PP&E Roll-Forward
Download the PP&E Roll-Forward template file to get started:
⬇️ Download PP&E Roll-Forward Template (CSV)
Instructions: Open the CSV file in Excel and save it as an Excel workbook (.xlsx) before you start.
📋 Step 2: Understand the Template Structure
When you open the template, you'll see this structure. The blue highlighted cells contain data you'll enter or calculate:
| Row | A (Line Item) | B (2025A) | C (2026E) | D (2027E) | E (2028E) | F (2029E) | G (2030E) | |
|---|---|---|---|---|---|---|---|---|
| 1 | PP&E Roll-Forward Schedule - TCS | |||||||
| 2 | (All values in ₹ crores) | |||||||
| 3 | ||||||||
| 4 | Line Item | 2025A | 2026E | 2027E | 2028E | 2029E | 2030E | |
| 5 | GROSS PP&E | |||||||
| 6 | Beginning Gross PP&E | 3,630,000 | (link) | (link) | (link) | (link) | (link) | |
| 7 | + Capital Expenditures | 363,000 | (formula) | (formula) | (formula) | (formula) | (formula) | |
| 8 | - Disposals | 0 | 0 | 0 | 0 | 0 | 0 | |
| 9 | = Ending Gross PP&E | 3,993,000 | (formula) | (formula) | (formula) | (formula) | (formula) | |
| 10 | ||||||||
| 11 | ACCUMULATED DEPRECIATION | |||||||
| 12 | Beginning Accum. Depr. | 1,878,000 | (link) | (link) | (link) | (link) | (link) | |
| 13 | + Depreciation Expense | 217,800 | (formula) | (formula) | (formula) | (formula) | (formula) | |
| 14 | - Depr. on Disposals | 0 | 0 | 0 | 0 | 0 | 0 | |
| 15 | = Ending Accum. Depr. | 2,095,800 | (formula) | (formula) | (formula) | (formula) | (formula) | |
| 16 | ||||||||
| 17 | NET PP&E | |||||||
| 18 | Net PP&E | 1,897,200 | (formula) | (formula) | (formula) | (formula) | (formula) | |
Key: Yellow cells = formulas you'll create | Blue cells = final results | White cells = given data
📝 Step 3: Enter the Formulas (Follow Along)
Task 3.1: Link Beginning Balances
The Beginning Balance of each year must equal the Ending Balance of the previous year. This creates the "roll-forward" connection.
=B9What this does: Takes the Ending Gross PP&E from 2025A (cell B9) as the beginning balance for 2026E.
Repeat for other years:
- Cell D6:
=C9(links to 2026E ending) - Cell E6:
=D9(links to 2027E ending) - Cell F6:
=E9(links to 2028E ending) - Cell G6:
=F9(links to 2029E ending)
=B15Repeat pattern: D12=
=C15, E12==D15, F12==E15, G12==F15
Task 3.2: Calculate Capital Expenditures
For this exercise, Capex is 10% of Revenue. Revenue data is provided at the bottom of the template.
=C26*10%Revenue is in row 26 (see Reference Data section at bottom of template):
- Cell C7:
=C26*10%→ 2026E Capex - Cell D7:
=D26*10%→ 2027E Capex - Cell E7:
=E26*10%→ 2028E Capex - Cell F7:
=F26*10%→ 2029E Capex - Cell G7:
=G26*10%→ 2030E Capex
💡 Pro Tip: Use absolute reference for the percentage: =C26*$B$27 where B27 contains "10%" (Capex Rate in Reference Data)
Task 3.3: Calculate Ending Gross PP&E
Ending Gross PP&E = Beginning + Capex - Disposals
=C6+C7-C8Copy this formula across to columns D, E, F, G
Expected result for 2026E: 4,392,300
Task 3.4: Calculate Depreciation Expense
For this exercise, Depreciation is 5.5% of Beginning Gross PP&E.
=C6*5.5%Copy across to D13, E13, F13, G13
Expected 2026E result: 239,580 (= 4,392,300 × 5.5%)
Task 3.5: Calculate Ending Accumulated Depreciation
Ending Accum. Depr. = Beginning + Depreciation - Depr. on Disposals
=C12+C13-C14Copy this formula across to columns D, E, F, G
Expected result for 2026E: 2,335,380
Task 3.6: Calculate Net PP&E
=C9-C15Copy this formula across to columns D, E, F, G
Expected result for 2026E: 2,056,920
📋 Formula Quick Reference Card
Copy these formulas directly into your Excel file:
| Cell | Description | Formula |
|---|---|---|
| C6 | Beginning Gross PP&E 2026E | =B9 |
| C7 | Capex 2026E (10% of Revenue) | =C26*10% |
| C9 | Ending Gross PP&E 2026E | =C6+C7-C8 |
| C12 | Beginning Accum. Depr. 2026E | =B15 |
| C13 | Depreciation 2026E (5.5%) | =C6*5.5% |
| C15 | Ending Accum. Depr. 2026E | =C12+C13-C14 |
| C18 | Net PP&E 2026E | =C9-C15 |
💡 After entering formulas in column C: Select cells C6:C18 and drag the fill handle (small square at bottom-right) across to column G to copy all formulas for years 2027E-2030E.
✅ Step 4: Verify Your Results
Compare your completed schedule with these expected values:
| Line Item | 2025A | 2026E | 2027E | 2028E | 2029E | 2030E |
|---|---|---|---|---|---|---|
| GROSS PP&E | ||||||
| Beginning Gross PP&E | 3,630,000 | 3,993,000 | 4,392,300 | 4,831,530 | 5,314,683 | 5,846,151 |
| + Capital Expenditures | 363,000 | 399,300 | 439,230 | 483,153 | 531,468 | 584,615 |
| - Disposals | 0 | 0 | 0 | 0 | 0 | 0 |
| = Ending Gross PP&E | 3,993,000 | 4,392,300 | 4,831,530 | 5,314,683 | 5,846,151 | 6,430,766 |
| ACCUMULATED DEPRECIATION | ||||||
| Beginning Accum. Depr. | 1,878,000 | 2,095,800 | 2,335,380 | 2,597,823 | 2,884,436 | 3,196,956 |
| + Depreciation Expense | 217,800 | 239,580 | 262,443 | 286,613 | 312,520 | 340,266 |
| - Depr. on Disposals | 0 | 0 | 0 | 0 | 0 | 0 |
| = Ending Accum. Depr. | 2,095,800 | 2,335,380 | 2,597,823 | 2,884,436 | 3,196,956 | 3,537,222 |
| NET PP&E | ||||||
| Net PP&E | 1,897,200 | 2,056,920 | 2,233,707 | 2,430,247 | 2,649,195 | 2,893,544 |
If your 2026E Net PP&E = 2,056,920, your formulas are correct!
If not, check that:
• Beginning balances link to prior year ending balances
• Capex = Revenue × 10%
• Depreciation = Beginning Gross PP&E × 5.5%
🔍 Step 5: Add Quality Checks
Add these error-checking formulas to validate your model:
| Check | Cell | Formula | Expected Result |
|---|---|---|---|
| Net PP&E ≥ 0 | C20 | =IF(C18>=0,"OK","ERROR") | OK |
| Accum. Depr. ≤ Gross PP&E | C21 | =IF(C15<=C9,"OK","ERROR") | OK |
| Beginning = Prior Ending | C22 | =IF(C6=B9,"OK","ERROR") | OK |
If any check shows "ERROR", there's a problem with your formulas. Double-check your cell references and formula logic.
| #REF! Error | → Cell reference is invalid. Check that you're referencing the correct cells. |
| #VALUE! Error | → Formula contains text instead of numbers. Check your cell references. |
| Wrong Results | → Verify Beginning Balance links to Prior Year Ending, not the same year. |
| Negative Net PP&E | → Accumulated Depreciation exceeds Gross PP&E. Check your depreciation rate. |
Key Takeaways
- Gross PP&E Roll-Forward: Beg + Capex - Disposals = End
- Accum. Depr. Roll-Forward: Beg + Depr - Depr on Disposals = End
- Net PP&E = Gross PP&E - Accumulated Depreciation
- Beginning Balance must link to Prior Year Ending
- Always include error checks to catch formula mistakes