Capital Budgeting Exercise

1. System Overview & Objectives

Purpose

This enterprise-grade Capital Budgeting Exercise model evaluates, scores, and tracks capital expenditure (CapEx) proposals across divisions. It calculates core financial metrics—including Net Present Value (NPV), Internal Rate of Return (IRR), Payback Period, and Profitability Index (PI)—to optimize capital allocation, enforce budget constraints, and audit multi-year cash flow projections.

Operational Scope

• Entities: Corporate Finance, Operations, IT, R&D, Facilities, and Supply Chain.
• Financial Horizon: Years 0 (initial outlay) through 5 (operating cash flows).
• Control Limits: Hard cap on total requested outlay vs. approved capital pool; mandatory IRR threshold (WACC + spread).

Update Cadence

• Monthly: Actual spend vs. budget reconciliation, schedule variance tracking.
• Quarterly: Portfolio review, hurdle rate recalibration, NPV recalculation under revised macroeconomic assumptions.
• Annually: Post-audit execution review, variance analysis on projected cash inflows.

---

2. Master Data Spreadsheet Table (PRIMARY EXPORT SHEET)

---

3. Pre-Built Formulas & Calculation Logic

Assuming data resides on sheet MasterData with row 1 as headers and rows 2 to 15 as data entries.

---

4. Data Dictionary & Dropdown Validation Rules

---

5. Summary KPI Dashboard

---

6. Setup & Export Guide for Excel & Google Sheets

Step 1: Initialize the Worksheets

1. Open a new blank workbook in Microsoft Excel or Google Sheets.
2. Create two tabs: rename the first tab MasterData and the second tab Dashboard.

Step 2: Import Master Data

1. Copy the Markdown table data from Section 2 (excluding calculation columns P through S if calculating via formulas, or including them if utilizing pre-computed values).
2. Paste the data starting at cell A1 on the MasterData worksheet.
3. Format columns E as Percentage (0.0%), and columns F through K as Currency ($#,##0).

Step 3: Apply Data Validation

1. Highlight MasterData!C2:C15 ($\text{Division}$). Go to Data > Data Validation, select List of items, and input: Operations, IT, R&D, Supply Chain, Facilities, Corporate.
2. Highlight MasterData!D2:D15 ($\text{Asset Category}$). Add list: Machinery & Equipment, Software & Systems, Real Estate & Facilities, Fleet Vehicles, Intellectual Property.
3. Highlight MasterData!I2:I15 ($\text{Risk Profile}$). Add list: Low, Medium, High.
4. Highlight MasterData!N2:N15 ($\text{Approval Status}$). Add list: Approved, Under Review, Rejected, Deferred.

Step 4: Insert Dynamic Formulas

1. In cell P1 of MasterData, type NPV. In cell P2, paste =NPV(E2, F2:J2) + D2 and drag down to row 15.
2. In cell Q1, type IRR. In cell Q2, paste =IRR(C2:J2) and drag down to row 15.
3. In cell R1, type Profitability_Index. In cell R2, paste =(NPV(E2, F2:J2) - D2) / ABS(D2) and drag down to row 15.

Step 5: Build Dashboard & Conditional Formatting

1. Navigate to the Dashboard tab and set up the KPI summary table headers matching Section 5.
2. Insert the exact formulas from Section 3 into the respective metric cells.
3. Highlight the KPI metric cells, select Conditional Formatting, and apply rules based on the thresholds defined in Section 5 (e.g., Green for favorable thresholds, Red for budget overruns or low IRR).
4. Save workbook as .xlsx (Excel) or ensure changes are synced (Google Sheets) for executive presentation.