Provide ChatGPT with your project list, approval rules, spend timing, and accounting assumptions, then use it to draft an auditable CapEx register, phasing schedule, and status summary. Keep cash timing, approval status, capitalisation treatment, and depreciation timing as separate controls rather than assuming they are the same thing.
I need a capital expenditure planning template in Excel. MODEL SETTINGS - Planning period: [e.g. Jan–Dec 2027 / fiscal year dates] - Currency / units: [e.g. GBP] - Approval statuses allowed: Proposed | Submitted | Approved | Rejected - "Approved" means: [approved budget / authorised project / committed spend — define] - Accounting framework / internal capitalisation policy: [state it or "not supplied"] PROJECT LIST Columns: Project ID | Project Name | Category | Estimated Project Cost | Capitalisable Basis (if known) | Residual/Salvage Value | Useful Life (years) | Expected In-Service / Available-for-Use Date | Spend Timing | Approval Status [Paste project rows] Rules: 1. Do NOT decide whether a cost qualifies for capitalisation. If Capitalisable Basis or accounting treatment is unknown, flag "Accounting treatment to confirm". 2. Do NOT treat Approval Status as proof that spend is contractually committed unless I explicitly define Approved that way. 3. Keep these concepts separate: - Estimated Project Cost - Capitalisable Basis - Cash / spend phasing - Approval Status - In-Service / Available-for-Use Date - Depreciation start 4. Create an Excel Table named CapExRegister. 5. Use data validation for Approval Status with only: Proposed, Submitted, Approved, Rejected. 6. Add conditional formatting by status. 7. If Capitalisable Basis, residual value and useful life are supplied and straight-line is my approved planning method, calculate ESTIMATED FULL-YEAR depreciation: =(CapitalisableBasis-ResidualValue)/UsefulLife This is a planning estimate only; do not treat it as current-period depreciation. 8. Do not start period depreciation merely because cash was spent or the project was approved. Use the supplied in-service / available-for-use date and accounting policy. 9. If depreciation inputs are missing, return "INPUT NEEDED" instead of assuming zero. 10. Add control checks: - Estimated Cost >= 0 - Capitalisable Basis >= Residual Value - Useful Life > 0 when depreciation is calculated - every status is from the approved list - Project ID is unique 11. Summary outputs: - Approved project cost - Submitted + Proposed pipeline - Rejected project cost shown separately - Approved estimated full-year depreciation only where inputs are complete - Approved project cost by Category 12. Do not invent costs, dates, categories, useful lives, residual values, capitalisation decisions or approval statuses. OUTPUT 1. CapExRegister layout 2. Exact Excel formulas / structured references 3. Data-validation and conditional-formatting instructions 4. Summary formulas 5. Validation / exception list Use tab-separated tables suitable for Excel.
Paste your project list where indicated. Works in ChatGPT.
I need a spend-phasing section added to my CapEx plan. PHASING BASIS - Period/month headers: [e.g. Jan–Dec 2027 or Jul 2027–Jun 2028] - Amount being phased: [Estimated Project Cost / approved budget / committed cash spend] - Timing basis: [cash payment / invoice / accrual / other — state explicitly] PROJECT LIST Project ID | Project Name | Amount to Phase | Spend Timing [Paste rows, e.g. P001 | Warehouse roof | 320000 | 50% Q2, 50% Q3 P002 | CNC machine | 185000 | 100% Aug P003 | ERP upgrade | 450000 | 25% Q1, 25% Q2, 50% Q3] Requirements: 1. Build one row per Project ID across the exact 12 month headers I provide. 2. If exact monthly amounts or percentages are supplied, use them exactly. 3. If only quarterly timing is supplied, do NOT invent a monthly split. Keep the amount in separate "Unallocated within Qx" columns/rows. 4. Do not allocate spend outside the planning period without flagging it. 5. For every project calculate: Allocated Monthly Spend + Unallocated Quarterly Spend + Outside-Period Spend (if any) = Amount to Phase 6. Add a PHASING CHECK: = Amount to Phase - Allocated - Unallocated - OutsidePeriod It must equal 0 within the stated rounding tolerance. 7. If supplied percentages do not total 100%, flag the difference instead of normalising them. 8. Add monthly allocated totals and quarter-unallocated totals. 9. Add cumulative ALLOCATED spend only; do not mix unallocated quarterly amounts into a monthly cumulative cash curve. 10. Do not smooth quarterly amounts unless I explicitly tell you to. Output exact formulas and a tab-separated table suitable for Excel.
Use monthly percentages when you need exact cash timing. If you provide only a quarterly percentage, keep it unallocated within that quarter until you define the monthly split.
I need a category-by-approval-status summary from my Excel Table named CapExRegister. Required CapExRegister columns: Project ID | Category | Estimated Project Cost | Approval Status [Paste sample rows if needed] Summary layout: Category | Approved | Submitted | Proposed | Rejected | Grand Total Requirements: 1. Use structured-reference SUMIFS formulas against CapExRegister. 2. Do not hard-code category names inside every formula; reference the row's Category cell. 3. Do not infer that Approved = committed unless my definition of Approved explicitly says so. 4. Grand Total must equal the sum of the four status columns for each category. 5. Add a totals row and a control: Summary Grand Total = SUM(CapExRegister[Estimated Project Cost]). 6. If blank/invalid statuses exist, show an "Unclassified / invalid status" control amount rather than silently excluding them. 7. Keep rejected projects visible but separate from active pipeline. Provide the exact Excel formulas and tab-separated summary layout.
Run this after building the register — the SUMIFS formulas reference your project data directly.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Project | Category | Cost | Salvage | Useful Life | Status |
| 2 | Warehouse roof | Facilities | £320,000 | £0 | 20 yrs | TBC |
| 3 | CNC machine | Equipment | £185,000 | £15,000 | 10 yrs | TBC |
| 4 | ERP upgrade | IT / Software | £450,000 | £0 | 7 yrs | TBC |
| 5 | Raw total | £955,000 |
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Project | Category | Cost | Salvage | Life | Status | Est. Full-Year Depr. |
| 2 | Warehouse roof | Facilities | £320,000 | £0 | 20 | Approved | £16,000 |
| 3 | CNC machine | Equipment | £185,000 | £15,000 | 10 | Approved | £17,000 |
| 4 | ERP upgrade | IT / Software | £450,000 | £0 | 7 | Proposed | £64,286 |
| 5 | Approved CapEx | £505,000 | £33,000 | ||||
| 6 | Pipeline CapEx | £450,000 | Proposed + Submitted |
Estimated full-year straight-line depreciation: =(CapitalisableBasis-ResidualValue)/UsefulLife The visual assumes Capitalisable Basis = shown Cost for these examples. Do not make that assumption in a real workbook unless accounting policy confirms it. Warehouse roof: (320,000−0)/20 = £16,000 CNC machine: (185,000−15,000)/10 = £17,000 ERP upgrade: (450,000−0)/7 = £64,285.71 ≈ £64,286 Approved CapEx = £320,000 + £185,000 = £505,000 Approved estimated full-year depreciation = £16,000 + £17,000 = £33,000 This is not the current-period depreciation charge unless the in-service date and depreciation convention support a full year. ✓
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Project | Cost | Spend Timing | Monthly Allocation? |
| 2 | Warehouse roof | £320,000 | 50% Q2, 50% Q3 | Unknown |
| 3 | CNC machine | £185,000 | 100% Aug | Exact |
| 4 | ERP upgrade | £450,000 | 25% Q1, 25% Q2, 50% Q3 | Unknown by month |
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Project | Apr | May | Jun | Jul | Aug | Sep | Allocated | Unallocated |
| 2 | Warehouse roof | £0 | £0 | £0 | £0 | £0 | £0 | £0 | £320,000 (Q2/Q3) |
| 3 | CNC machine | £0 | £0 | £0 | £0 | £185,000 | £0 | £185,000 | £0 |
| 4 | ERP upgrade | £0 | £0 | £0 | £0 | £0 | £0 | £0 | £450,000 (Q1/Q2/Q3) |
| 5 | Monthly allocated total | £0 | £0 | £0 | £0 | £185,000 | £0 | £185,000 | £770,000 |
CNC machine: "100% Aug" → £185,000 in Aug ✓ Warehouse roof: 50% Q2 = £160,000 50% Q3 = £160,000 But month-level timing is still unknown. ERP upgrade: 25% Q1 = £112,500 25% Q2 = £112,500 50% Q3 = £225,000 Again: do not invent monthly dates. Allocated now: £185,000 Still unallocated within quarters: £770,000 Total to phase: £955,000 Control: £955,000 − £185,000 − £770,000 = £0 ✓ Do not include the £770,000 in a monthly cumulative cash curve until month timing is known.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Project | Status | Cost |
| 2 | Facilities | Warehouse roof | Approved | £320,000 |
| 3 | Equipment | CNC machine | Approved | £185,000 |
| 4 | IT / Software | ERP upgrade | Proposed | £450,000 |
| 5 | IT / Software | Network refresh | Submitted | £90,000 |
| 6 | Facilities | Office fit-out | Rejected | £120,000 |
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Category | Approved | Submitted | Proposed | Rejected | Grand Total |
| 2 | Facilities | £320,000 | £0 | £0 | £120,000 | £440,000 |
| 3 | Equipment | £185,000 | £0 | £0 | £0 | £185,000 |
| 4 | IT / Software | £0 | £90,000 | £450,000 | £0 | £540,000 |
| 5 | Total | £505,000 | £90,000 | £450,000 | £120,000 | £1,165,000 |
Approved project cost = £505,000
Active pipeline = Submitted + Proposed
= £90,000 + £450,000
= £540,000
Rejected = £120,000
Grand project universe = £1,165,000 ✓
Structured-reference SUMIFS keeps the matrix live as register rows/statuses change.
Add a control for blank/invalid statuses so nothing disappears silently. ✓
Provide your organisation's project categories or approved category list. Do not ask ChatGPT to decide whether a project qualifies as CapEx from its name alone; accounting classification follows your policy and the underlying facts.
Provide capitalisable basis, residual value, useful life and expected in-service date only when supported by your accounting policy or asset assumptions. Use 0 residual value only when that is an intentional assumption, not as a default invented by ChatGPT.
Use the controlled status list Proposed, Submitted, Approved, Rejected unless your own governance uses different states. Define what "Approved" means so it is not confused with legally committed spend.
Use structured-reference SUMIFS formulas so status totals update with the Excel Table. Show Approved, active pipeline and Rejected separately, plus a control for blank or invalid statuses.
Include Project ID, Project Name, the exact amount to phase, and the timing basis. Project ID prevents duplicate names from being confused; depreciation data is not needed for cash phasing.
State the timing you actually know and whether the schedule represents cash payment, invoice/accrual timing, or another basis. Do not convert quarter-level timing into monthly cash timing without evidence.
Run the Phase prompt. Exact monthly percentages can populate the 12-month matrix directly. Quarter-only percentages should remain visibly unallocated within that quarter until you provide a monthly split.
Before using the schedule in a cash forecast, verify each project's Allocated + Unallocated + Outside-Period amount equals the amount to phase. Only month-allocated spend should feed a monthly cash curve.
Verify that your main project register doesn't have duplicate/misspelled categories (e.g., "IT" vs "Information Tech").
Provide the exact Excel Table name and column headers. A small sample is enough to design the formulas, but totals must reference the complete CapExRegister table.
Use structured-reference SUMIFS formulas that reference the row category and status header, plus a control for blank/invalid statuses.
Place the summary on a separate tab and verify the grand total ties back to CapExRegister. Because formulas reference the Excel Table, valid status/category changes will recalculate the summary.