Build a CapEx Planning Template in Excel with Claude AI

This course gives you a Claude prompt that turns a list of proposed capital projects into a structured CapEx planning template in Excel. The output tracks each project's estimated cost, approval status, spend phasing across months, and the resulting depreciation impact — all in one workbook. Paste your project list, get a board-ready CapEx plan back.

The Completed CapEx Planning Template

Below is what Claude produces. Each project occupies one row with its category, total cost, approval status, and estimated annual depreciation. A spend-phasing section (not shown in full here) breaks each project's cost across the months when cash actually leaves the business.

A

B

C

D

E

F

G

1

Project

Prepare Your Project List for Claude

Claude builds the CapEx template from a project list you supply. Each project needs six pieces of information. You don't need final numbers — estimates are fine at the planning stage — but every field should have a value.

A

B

C

D

E

F

1

Project Name

Assign a category to each project

Common categories include Facilities, Equipment, Vehicles, IT/Software, and Leasehold Improvements. Categories make it easy to filter and summarise — Claude uses them to build SUMIFS-based subtotals.

02

Estimate spend timing, not just total cost

Capital projects rarely pay out in a single lump sum. Break each project into the months or quarters when cash will actually be spent. This feeds the spend-phasing section and connects directly to your cash flow dashboard.

03

Include salvage values and useful lives

Even rough estimates matter. Claude calculates an indicative annual depreciation for each project so leadership can see the ongoing P&L impact, not just the cash outlay. Write 0 for salvage if the asset will be fully written off.

04

Set the approval status for each project

Use four stages: Proposed, Submitted, Approved, Rejected. Claude creates a data-validation dropdown and filters the totals by status so you can show the board both committed and pipeline CapEx.

Tip: If you manage CapEx across multiple entities, build each entity's plan separately and consolidate later using the multi-entity consolidation course.

The Claude Prompt for CapEx Planning

Paste this prompt into Claude with your project list. Claude returns a structured template with formulas, not static numbers — so you can update project costs and timings as estimates are refined.

Prompt — paste into Claude

I need a capital expenditure (CapEx) planning template in Excel. Below is my list of proposed and approved projects. PROJECT LIST (columns: Project Name, Category, Estimated Cost, Salvage Value, Useful Life in years, Spend Timing, Approval Status): [Paste your project data here] Requirements: - Create a project register table with columns: Project Name, Category, Estimated Cost, Salvage Value, Useful Life, Annual Depreciation (straight-line), Approval Status. - The Approval Status column should use data validation with four options: Proposed, Submitted, Approved, Rejected. - Add conditional formatting: green for Approved, blue for Submitted, yellow for Proposed, red for Rejected. - Include summary rows using SUMIFS: • Total Approved CapEx (sum of Estimated Cost where Status = Approved) • Total Pipeline CapEx (Submitted + Proposed) • Total Annual Depreciation (Approved projects only) • Breakdown by Category (Approved only) - Create a second section: Monthly Spend Phasing. For each project, distribute the Estimated Cost across the months I've specified in the Spend Timing column. Add a monthly total row across all projects. - Do NOT invent project costs, categories, or timing I haven't provided. - Do NOT change my approval statuses or reclassify any project. - Format the output as tab-separated tables I can paste directly into Excel.

From CapEx Plan to Full Financial Picture

A CapEx plan on its own answers "what are we buying and when?" But finance teams need to connect that answer to three follow-up questions: What's the cash impact? (answered by the spend-phasing section and your cash flow dashboard), What's the P&L impact? (answered by the depreciation column and the depreciation schedule), and How does it fit the overall plan? (answered by folding CapEx into your rolling forecast).

CapEx Categories at a Glance

A

B

C

1

Frequently Asked Questions

What is the difference between CapEx and OpEx in Excel planning?

CapEx (capital expenditure) is spending on long-term assets that get capitalised on the balance sheet and depreciated over time. OpEx (operating expenditure) is day-to-day spending that hits the income statement immediately. This template handles CapEx only — each project is tracked as an asset with a useful life and depreciation schedule.

Can Claude phase CapEx spending across multiple months?

Yes. The prompt asks you to provide a spend schedule per project — for example, 30% in Q1 and 70% in Q2. Claude distributes the total project cost across the months you specify, giving you a monthly cash outflow forecast.

How does this template connect to depreciation?

The template includes an estimated annual depreciation column for each project based on cost, salvage value, and useful life. For the full depreciation schedule with monthly granularity and multiple methods, use it alongside the fixed asset depreciation course.

Should I include projects that haven't been approved yet?

Yes. The template includes an Approval Status column so you can track proposed, submitted, approved, and rejected projects in one view. This gives leadership a complete picture of the CapEx pipeline, not just committed spend.

Related Excel Finance Courses

Browse all Excel-focused finance courses on the AI for Excel hub page.

What the finished CapEx planning template looks like