What approach to use
Driver-based is the right default. Build an asset register on a supporting sheet, hardcode each acquisition in the month it occurs, and let the depreciation formula run automatically from those additions. Statistical rarely fits. Investment is lumpy and project-driven, making historical averages an unreliable guide. Use hardcoded when you already have a known investment plan and depreciation schedule, for example from an existing Excel budget or an accountant’s estimate.Where to forecast CAPEX
- Supporting sheet: right for most cases. Build one section per asset class, each with its own asset register, gross cost roll-forward, and accumulated depreciation roll-forward. The balance sheet references the net book value totals.
- Directly on the balance sheet: right for simple setups with a single asset class.
Forecasting approaches
- Driver-based
- Statistical
- Hardcoded
Set up the supporting sheetCreate one section per asset class: Furniture, Vehicles, Machinery, Intangible assets. Group by depreciation cadence, not just asset type. Two machinery items with different useful lives need separate groups, since the formula offset and divisor must match the useful life of every asset in the group. Within each group, add one row per asset and hardcode the acquisition cost in the month the asset is acquired. This is your asset register.Cost additions are the only rows with a cash effect. They flow to investing activities on the cash flow statement.If an asset is disposed of in the period, enter the detraction as a negative value in the same section. The depreciation formula will reduce automatically from that month onwards.DepreciationAdd a Depreciation row below the asset register. The formula takes the rolling sum of all cost additions within the useful life and divides by the number of months. For a 5-year asset class:When a new asset is added to the register, the depreciation row picks it up in the current period and runs for the full 60 months. Adjust the offset and divisor to match each asset class. Depreciation flows to the Depreciation expense line on the P&L as a non-cash charge, and to the accumulated depreciation balance on the balance sheet.This formula only handles depreciation on new CAPEX acquired in the forecast period. Existing assets already carry a depreciation waterfall from before the forecast starts, and that schedule is hard to reproduce in Francis. Don’t try to derive it. Instead, split depreciation expense into two parts: base the depreciation from existing CAPEX on a statistical approach or on the waterfall exported from your accounting system, and forecast depreciation from new CAPEX directly in Francis with the formula above.Net book valueNet book value is gross cost minus accumulated depreciation. The balance sheet row references both:
New investments are a cash outflow in investing activities. Depreciation is a non-cash expense and appears as an add-back in operating activities. Both must be modelled explicitly for the cash flow statement to balance.
FAQ
Should I track CAPEX and accumulated depreciation separately?
Should I track CAPEX and accumulated depreciation separately?
Yes. Since accumulated depreciation is a non-cash item, it shouldn’t be included in the cash flow statement. Separating the two lets you capture only CAPEX in the cash flow statement.
How do I handle an asset disposal?
How do I handle an asset disposal?
Model a disposal as a cost detraction in the period the asset is removed, and a depreciation detraction for the accumulated depreciation on that asset. If the disposal generates proceeds, those flow through investing activities on the cash flow statement.