CapEx and Depreciation Modeling

1. CapEx vs. Opex: A Core Distinction

Before we build any schedules, we must be crystal clear on what counts as a capital expenditure. An investment in a new factory is treated very differently from the monthly electricity bill for that factory.

A capital expenditure (CapEx) is the use of funds to acquire, upgrade, and maintain physical assets such as property, buildings, an industrial plant, technology, or equipment. An operating expense (Opex), on the other hand, is an expense a business incurs through its normal business operations.

To grasp the practical implications for an FP&A analyst, please read the following sections from a comprehensive guide by PivotXL.

As the article emphasizes, the key FP&A rule of thumb is: If the benefit lasts beyond 12 months, it's likely CapEx. This decision has a profound impact on the financial statements:

• Income Statement: Opex is expensed immediately, reducing profit in the current period. CapEx is not. Its cost is spread over its "useful life" via depreciation.
• Balance Sheet: CapEx increases the value of assets (specifically, Property, Plant & Equipment or PP&E). Opex has no direct asset impact.
• Cash Flow Statement: Both CapEx and Opex result in cash outflows. However, CapEx is reported under Cash Flow from Investing, while Opex-related cash flows are part of Cash Flow from Operations.

2. Structuring the CapEx and PP&E Schedules

A robust financial model doesn't just have a single number for "CapEx." It details the planned investments and tracks the company's entire asset base over time. This is done with two core schedules: the CapEx Planning Schedule and the PP&E Roll-Forward.

The CapEx Planning Schedule

This schedule is the starting point—it's an input sheet where you list all planned capital projects. It acts as the "assumptions" driver for your fixed asset calculations.

A best-practice layout includes columns for:

• Asset Category (e.g., IT, Manufacturing, Vehicles)
• Project Name
• Start Date (when the asset is put into service)
• Cost
• Useful Life (in years)
• Depreciation Method

The PP&E Roll-Forward Schedule

This schedule provides a summary of all movements in the company's fixed asset base. It's the bridge from one period's Balance Sheet to the next and follows a simple, powerful formula:

• Beginning PP&E: The ending balance from the previous period.
• CapEx: The sum of all new investments from your CapEx planning schedule for the current period.
• Depreciation: The total depreciation expense for all assets (both old and new) for the current period.
• Ending PP&E: The final balance that will appear on the current period's Balance Sheet.

The following reading explains how these components fit together.

Now that we have the framework, we need to populate the most complex piece: the depreciation calculation.

3. Modeling Depreciation

Depreciation is the mechanism for allocating the cost of a tangible asset over its useful life. While there are several methods, our main focus will be on the one most commonly used in FP&A models.

To understand the different approaches, please review this resource from Wall Street Prep.

As you've read, the three main methods are:

1. Straight-Line: Spreads the cost evenly across each period. This is the simplest and most widely used method in corporate financial modeling due to its predictability. The formula is:
   $\text{Annual Depreciation}=\frac{(\text{Cost}-\text{Salvage Value})}{\text{Useful Life}}$
   For modeling purposes, Salvage Value is often assumed to be $0.

2. Accelerated (e.g., Double Declining Balance): Expenses a larger portion of the asset's value in the early years of its life. This is often used for tax purposes to get larger tax deductions sooner.

3. Units of Production: Bases depreciation on the asset's usage rather than the passage of time. This is common for industrial machinery where wear and tear is directly related to output.

For our three-statement model, we will build a straight-line depreciation schedule.

The Depreciation "Waterfall"

The main challenge in modeling depreciation is that in any given year, the total depreciation expense is the sum of depreciation from assets bought this year, last year, the year before, and so on. We need to track the depreciation for each "vintage" (or cohort) of CapEx. This is often called a "waterfall" or "corkscrew" schedule.

The image below, from the Wall Street Prep article, shows this concept in action.

Here's how to interpret and build this:

• Each row starting with "2021 Capex," "2022 Capex," etc., represents a new group of assets purchased in that year.
• The annual depreciation for that group is calculated (e.g., for 2021, $300k Capex / 5 years = $60k depreciation per year).
• That $60k is then spread across the next five years (2021 through 2025).
• The "Total Depreciation" for any year is the vertical sum of the depreciation expenses from all the different vintages active in that year. For example, Total Depreciation in 2023 is $60k (from 2021 Capex) + $61k (from 2022 Capex) + $62k (from 2023 Capex) = $184k.

This same logic is shown in the "Classic Depreciation Schedule" image below, which also helpfully points out where the totals link to the main financial statements.

4. Linking to the Three Financial Statements

You've built the schedules and done the calculations. The final, critical step is to link these outputs to your three-statement model.

Here is a summary of the key linkages:

1. Income Statement:

   • The Total Depreciation Expense from your depreciation waterfall flows into the Income Statement as an operating expense (or sometimes within COGS), reducing pre-tax income.

2. Cash Flow Statement:

   • Cash Flow from Operations (CFO): Start with Net Income, then add back the Total Depreciation Expense because it's a non-cash charge.
   • Cash Flow from Investing (CFI): The Total CapEx spend for the period is a subtraction (a use of cash).

3. Balance Sheet:

   • The Ending PP&E balance from your roll-forward schedule becomes the PP&E asset value on the Balance Sheet for that period. This ensures the Balance Sheet balances, as the changes in cash and retained earnings (from net income) are offset by the change in PP&E.

For a deeper dive into the Excel mechanics, you are encouraged to download and explore the practice files provided in section 17.14 of the PivotXL article.

Conclusion

Excellent work! You have now mastered the logic for building one of the most mechanically complex and important supporting schedules in a financial model. Understanding the flow of capital expenditures through the three financial statements is a cornerstone of financial analysis.

Key Takeaways:

• CapEx represents investments in long-term assets and is recorded on the Balance Sheet, while the cash outflow appears in the Investing section of the Cash Flow Statement.
• Depreciation is the non-cash expense that allocates the cost of CapEx over its useful life, impacting the Income Statement and being added back on the Cash Flow Statement.
• The PP&E roll-forward (Beg PP&E + CapEx - Depr = End PP&E) is the central schedule that connects these activities and drives the PP&E balance on the Balance Sheet.
• A "waterfall" schedule is the best practice for calculating total depreciation by summing the depreciation from different "vintages" of CapEx.

In our next lesson, we will build another key supporting schedule: a dynamic headcount schedule with salary escalations and variable benefit costs. This will drive the largest operating expense for many companies—Salaries and Wages.