Capital Expenditure Planning and Evaluation

Capital Expenditure (CapEx) Planning and Evaluation Framework

Author: Senior Financial Architect & Operations Lead
Target Audience: CFO, FP&A Director, Capital Allocation Committee
Version: 4.2-INSTITUTIONAL

1. Workbook Architecture & Tab Breakdown

The model is built with strict segregation of duties between inputs, calculation engines, and executive reporting. It enforces circular-reference prevention and utilizes dynamic array functions.

[01_Inputs_Assumptions] ---> [02_Master_CapEx_Ledger] ---> [03_DCF_Evaluation_Engine] ---> [05_Executive_Dashboard]
                                     |
                                     +-------------------> [04_Portfolio_Optimization]

2. Master Data Table (Markdown Format)

Note: Monetary values in USD Millions. Depreciation calculated on a straight-line basis.

3. Explicit Formula Glossary

The following formulas form the computational backbone of the model, referencing the Master Ledger (02_Master_CapEx_Ledger!A2:O16) and Assumptions (01_Inputs_Assumptions!B2:B10).

Financial Valuation Formulas

• Net Present Value (NPV):
  =NPV(Assumptions_WACC, C10:L10) + C9
  Explanation: Discounts future net cash flows (C10:L10) by the weighted average cost of capital (Assumptions_WACC), adding initial cash outflow (C9, expressed as a negative) to determine absolute value creation.
• Internal Rate of Return (IRR):
  =IRR(C9:L10)
  Explanation: Calculates the exact discount rate where the Net Present Value of all cash flows (initial outlay through year 10) equals zero.
• Discounted Payback Period:
  =MIN(IF(Cumulative_Discounted_CF >= 0, ROW(Period))-ROW(Start_Period))
  Explanation: Evaluates the exact fractional year when the cumulative present value of cash inflows surpasses the initial capital expenditure.

Aggregation & Lookup Formulas

• Conditional Capital Summation:
  =SUMIF('02_Master_CapEx_Ledger'!G:G, "Approved", '02_Master_CapEx_Ledger'!F:F)
  Explanation: Aggregates requested budget sums (F:F) strictly for projects where the approval status equals "Approved" (G:G).
• Multi-Criteria Division Analysis:
  =SUMIFS('02_Master_CapEx_Ledger'!F:F, '02_Master_CapEx_Ledger'!C:C, "Manufacturing", '02_Master_CapEx_Ledger'!G:G, "Approved")
  Explanation: Computes total capital deployed to the Manufacturing division for approved initiatives only.
• Dynamic Asset Lookup:
  =XLOOKUP(E14, '02_Master_CapEx_Ledger'!A:A, '02_Master_CapEx_Ledger'!B:B, "Project Not Found", 0, 1)
  Explanation: Searches for Project ID in E14, scans the Master Ledger column A, and returns the Project Name from column B with exact match logic.
• Error-Wrapped Risk Index:
  =IFERROR(AVERAGEIFS('02_Master_CapEx_Ledger'!N:N, '02_Master_CapEx_Ledger'!C:C, D4), 0)
  Explanation: Calculates average risk scores by division, cleanly defaulting to 0 if division groupings yield null arrays or missing inputs.

4. Data Validation & Conditional Formatting Rules

To maintain absolute data integrity across institutional stakeholders, strict input constraints and dynamic visual alerts are applied.

Data Validation Rules

1. Approval Status (Column G):
   • Validation Type: List
   • Source Range: Approved, Under Review, Rejected, Deferred
   • Error Alert: Stop style. "Invalid status. Select a valid workflow state from the master governance list."
2. Asset Class (Column D):
   • Validation Type: List
   • Source Range: Intangible, Machinery, Real Estate, Vehicles, Equipment, Infrastructure, Software
3. Risk Score (Column N):
   • Validation Type: Whole Number
   • Data Criteria: Between 1 and 5
   • Error Alert: Warning style. "Risk score must be an integer between 1 (Low) and 5 (Extreme)."

Conditional Formatting Rules

1. High-Risk / Low-Return Alert (Row Highlighting):
   • Target Range: 02_Master_CapEx_Ledger!A2:O16
   • Rule Formula: =AND($N2>=4, $L2<0)
   • Formatting: Soft Red Fill (#FADBD8), Dark Red Text (#78281F) – Highlights high-risk projects destroying enterprise value.
2. Approved & Value-Additive Status (Status Column):
   • Target Range: 02_Master_CapEx_Ledger!G2:G16
   • Rule Formula: =$G2="Approved"
   • Formatting: Soft Green Fill (#D4EFDF), Dark Green Text (#145A32).
3. Capital Budget Variance Tracker:
   • Target Range: Variance Columns in 05_Executive_Dashboard
   • Rule Type: Format cells based on value (> 0 for Over Budget; < 0 for Under Budget).
   • Formatting: Traffic light scale (Red for unfavorable budget overruns, Green for favorable savings).

5. Executive Summary KPI Cards

This table constitutes the primary block of the 05_Executive_Dashboard tab, aggregating portfolio health metrics for C-Suite review.