Capex & Depreciation Models in Excel for FP&A| Chapter 17

📘 This post is part of the ebook: Excel for FP&A – Free E-Book

📑 Read the full Table of Contents: Excel for FP&A – Free E-Book

⬅️ Previous Chapter: Revenue, COGS & Margin Modeling | Chapter 16

Introduction

Capex & Depreciation Models are a critical part of advanced FP&A financial modeling.
In this chapter, we build Capex & Depreciation Models in Excel that clearly link capital spending to depreciation, fixed asset roll-forwards, and cash flow forecasting. Well-designed Capex & Depreciation Models help FP&A teams understand the long-term impact of investment decisions on profitability, liquidity, and balance sheet health. Unlike static schedules, modern Capex & Depreciation Models are driver-based, scenario-ready, and fully integrated with rolling forecasts.

• Driver-based
• Forecast-ready
• Fully integrated with P&L, balance sheet, and cash flow
• Scenario-capable

You will learn how FP&A professionals use Excel to turn Capex from a budgeting afterthought into a strategic planning lever.

17.1 Why Capex & Depreciation Models Matters in FP&A

Capex is not an expense.

This single misunderstanding causes more FP&A errors than almost any other concept.

Capex affects financial statements in three different ways:

1. Cash Flow – Immediate cash outflow when assets are purchased
2. Balance Sheet – Increase in fixed assets (PP&E or capitalized intangibles)
3. P&L – Gradual recognition via depreciation or amortization

A model that captures only one of these dimensions is incomplete.

FP&A Reality Check

Many companies:

• Approve Capex annually
• Track spend loosely
• Reconcile depreciation manually
• Discover cash issues too late

A proper Capex model connects investment timing to financial outcomes.

📌 FP&A Principle
Profitability without Capex discipline eventually destroys cash.

17.2 Capex & Depreciation Models vs Opex: A Critical Distinction

Before building models, FP&A must clearly distinguish Capex from operating expenses.

Capex Characteristics

• Long-term benefit (multi-year)
• Capitalized on the balance sheet
• Expensed gradually through depreciation

Opex Characteristics

• Short-term benefit
• Expensed immediately
• Hits EBITDA directly

Example	Capex	Opex
Factory equipment	✔
ERP system (capitalized)	✔
Office rent		✔
Salaries		✔

FP&A Rule of Thumb

If the benefit lasts beyond 12 months, it likely belongs in Capex.

17.3 Core Components of Capex & Depreciation Models

A professional Capex framework in Excel includes five tightly linked components:

1. Capex planning schedule
2. Asset roll-forward
3. Depreciation calculation
4. P&L impact
5. Cash flow impact

These components must update automatically as assumptions change.

17.4 Capex & Depreciation Models Planning Schedule in Excel

The Capex schedule is where planning begins.

Best-Practice Capex Planning Table

Asset Category	Project Name	Start Month	Cost	Useful Life (Years)	Depreciation Method
IT	ERP Upgrade	Apr-2026	6,000,000	5	Straight-line
Manufacturing	New Machine	Jul-2026	12,000,000	8	Straight-line
Office	Furniture	Jan-2026	1,200,000	5	Straight-line

FP&A Design Rule

Capex schedules should be:

• Monthly (not annual)
• Timing-aware
• Scenario-driven

Best-Practice Capex Planning Table :

Column	Purpose
Asset Category	Groups Capex by function (IT, Manufacturing, Office)
Project Name	Business-readable investment name
Start Month	Timing of cash spend & service start
Capex Cost	Total capitalized amount
Useful Life (Years)	Depreciation duration
Depreciation Method	Straight-line (extensible later)

📂 File: Ch17_Capex_Planning.xlsx
Purpose: Centralized Capex planning with monthly timing and asset attributes.

17.5 Capex & Depreciation Models Timing and Cash Flow Impact

Capex hits cash when the asset is purchased, not when depreciation begins.

This distinction is critical for liquidity planning.

Monthly Capex Cash Flow Logic

Month	Capex Spend
Jan	0
Feb	0
Mar	0
Apr	6,000,000
May	0
Jul	12,000,000

Excel Formula Example

=IF(Month = Capex_Start_Month, Capex_Cost, 0)

This logic feeds directly into:

• Investing cash flow
• Ending cash balance
• Liquidity runway analysis

📌 FP&A Insight
Most cash crises are caused by Capex timing, not operating losses.

17.6 Fixed Asset Roll-Forward Schedule

A roll-forward schedule tracks how assets evolve over time.

Core Fixed Asset Roll-Forward Formula

Ending Fixed Assets
= Beginning Fixed Assets

• Capex Additions
  – Asset Disposals
  – Accumulated Depreciation

Monthly Roll-Forward Table

Month	Beginning PP&E	Additions	Depreciation
Jan	50,000,000	0	1,200,000
Feb	48,800,000	0	1,200,000
Apr	47,600,000	6,000,000	1,300,000

Column	Purpose
Month	Monthly time series
Beginning PP&E	Prior month ending balance
Capex Additions	Driven by Capex schedule
Depreciation	Monthly depreciation expense
Ending PP&E	Automatically calculated

Core Roll-Forward= Beginning PP&E + Capex Additions − Depreciation

Rolling Logic :

• Beginning PP&E for each month references the prior month’s Ending PP&E
• Capex additions flow in only when scheduled
• Depreciation flows consistently from the depreciation schedule

Example:

• January Ending PP&E → February Beginning PP&E
• April includes a 6,000,000 Capex addition
• Depreciation increases to reflect new assets

Best Practice

• One formula copied across months
• No month-specific logic
• Additions driven by Capex schedule

📂 File: Ch17_Fixed_Asset_Rollforward.xlsx
Purpose: Automated PP&E movement tracking.

17.7 Capex & Depreciation Models in Excel

Depreciation spreads Capex over its useful life.

Monthly layout

One formula copied across rows

Easy to extend into:

• Fixed asset roll-forward
• P&L depreciation expense
• Cash flow add-back
• Scenario-based Capex timing

Straight-Line Depreciation Formula

Annual Depreciation
= Capex Cost ÷ Useful Life

Monthly Depreciation
= Annual Depreciation ÷ 12

Example

Capex Cost = 6,000,000
Useful Life = 5 years

Partial-Year Depreciation Logic

Depreciation starts when the asset is placed into service, not when approved.

Excel example:

=IF(Month >= Service_Start_Month,
    Capex_Cost / Useful_Life / 12,
    0)

File📂 Ch17_Depreciation_Schedule.xlsx

📌 FP&A Principle
Depreciation follows usage, not approval.

17.8 Accumulated Capex & Depreciation Models Roll-Forward

Accumulated depreciation lives on the balance sheet.

Roll-Forward Formula

Ending Accumulated Depreciation
= Beginning Accumulated Depreciation

• Current Period Depreciation

Example

Month	Beginning Acc. Dep	Depreciation	Ending Acc. Dep
Jan	20,000,000	1,200,000	21,200,000
Feb	21,200,000	1,200,000	22,400,000

Net PP&E = Gross PP&E – Accumulated Depreciation

17.9 Linking Capex & Depreciation Models to the P&L

Capex affects the P&L only through depreciation (and amortization).

P&L Integration

Line Item	Source
Depreciation Expense	Depreciation schedule
EBITDA	Excludes depreciation
EBIT	Includes depreciation

📌 FP&A Insight
Capex does not affect EBITDA—but it absolutely affects cash.

This is why EBITDA-only thinking is dangerous in capital-intensive businesses.

17.10 Capex & Depreciation Models and Cash Flow Statement Integration

Capex appears in Investing Activities.

Cash Flow Treatment

Cash Flow Section	Line Item
Operating	Depreciation (add-back)
Investing	Capex spend
Financing	Debt funding (if applicable)

Example

Item	Amount
Capex Spend	-6,000,000
Annual Depreciation	1,200,000
Monthly Depreciation	100,000

Net Cash Impact ≠ Depreciation

Key formulas

• Operating Cash Flow (Depreciation add-back):

=Capex_Inputs!B4

• Investing Cash Flow (Capex):

=Capex_Inputs!B2

Net Cash Impact = Depreciation Add-back + Capex Spend

📂 File: Ch17_CashFlow_Capex_Link.xlsx
Purpose: Full linkage between Capex, depreciation, and cash flow.

17.11 Scenario Planning for Capex & Depreciation Models

Capex decisions are inherently uncertain.

FP&A must test scenarios such as:

• Delayed projects
• Reduced investment
• Accelerated expansion

Scenario-Based Capex Logic

=CHOOSE(Scenario_ID,
        Base_Capex,
        Upside_Capex,
        Downside_Capex)

Scenario	Capex
Base	20,000,000
Upside	30,000,000
Downside	12,000,000

This automatically updates:

• Depreciation
• Fixed assets
• Cash flow
• Liquidity runway

Depreciation (Annual)

Purpose:
Calculates annual straight-line depreciation.

Formula

Annual Depreciation = Selected Capex ÷ Useful Life

With values

=20000000 ÷ 5

Monthly Depreciation :

Purpose:
Spreads annual depreciation evenly across months.

Formula

Monthly Depreciation = Annual Depreciation ÷ 12

With values

=4000000 ÷ 12

Cash Outflow :

Purpose:
Represents the actual cash impact of Capex (investing cash flow).

Formula

Cash Outflow = Selected Capex

File📂 Ch17_Capex_Scenario_Model.xlsx

📌 FP&A Insight
Capex scenarios are cash scenarios.

17.12 Governance and Controls for Capex & Depreciation Models

Capex forecasting fails without discipline.

Strong FP&A Governance Includes

• Approval thresholds
• Project ownership
• Spend vs plan tracking
• Post-investment review
• Forecast vs actual reconciliation

Best Practice

Never allow Capex models to become:

• Static
• Orphaned
• Disconnected from cash planning

17.13 Common FP&A Mistakes in Capex & Depreciation Models

Avoid these traps:

❌ Treating Capex as an expense
❌ Ignoring depreciation timing
❌ Modeling Capex annually instead of monthly
❌ Forgetting cash flow impact
❌ Hardcoding depreciation

📌 Reminder
Capex modeling is balance-sheet-first thinking.

17.14 Practice Files:

📂 Ch17_Capex_Planning.xlsx
📂 Ch17_Fixed_Asset_Rollforward.xlsx
📂 Ch17_Depreciation_Schedule.xlsx
📂 Ch17_CashFlow_Capex_Link.xlsx
📂 Ch17_Capex_Scenario_Model.xlsx

17.15 Summary

Capex, depreciation, and roll-forward schedules sit at the intersection of strategy, accounting, and liquidity.

A strong FP&A Capex model:

• Separates cash, accounting, and planning logic
• Tracks assets over their full life cycle
• Integrates seamlessly with P&L and cash flow
• Supports scenario-based decision-making

FP&A is not just about managing costs—it is about allocating capital wisely.

The companies that win long term are not those with the highest EBITDA, but those that invest at the right time, in the right assets, with full visibility into cash and risk.

Pivotxl Capex, Depreciation & Asset Lifecycle Modeling

Manual Capex and depreciation modeling breaks down faster than almost any other FP&A process.

As organizations scale, FP&A teams face growing complexity in planning capital investments, tracking fixed assets, and forecasting depreciation and cash flow impact across departments and scenarios. What begins as a simple Capex schedule in Excel quickly becomes difficult to maintain, reconcile, and explain to leadership.

Common challenges include:

• Multiple Capex projects with different start dates and useful lives
• Frequent changes to investment timing and approval status
• Manual depreciation calculations and partial-year adjustments

PivotXL transforms Excel into a dynamic, driver-based Capex and depreciation planning platform—without replacing the models FP&A teams already trust.