ALLSHIFT/docs/05-business-economics/social-cost-benefit-analysis.md

# Social Cost-Benefit Analysis

A robust SCBA addresses several fundamental questions:
*   **What is the purpose of the proposed intervention?** Clarifying the objective helps set the stage for a targeted analysis.
*   **What are the available alternatives?** This relates to the concept of _opportunity cost_, whereby resources used for one project could have been allocated to another. An analysis of alternatives includes the _base case_ or _zero alternative_: what happens if no intervention is made.
*   **Who are the stakeholders?** Identifying all relevant stakeholders is crucial, as the distribution of benefits and costs may vary significantly among groups. Excluding stakeholders can lead to biased or incomplete analyses.
*   **What are the costs and benefits over time?** All future costs and benefits must be discounted to their present value to allow meaningful comparison (see next module). This includes both direct financial effects and indirect or social impacts, such as improved air quality or reduced travel time.
*   **What are the present values of these costs and benefits?** Discounting future impacts to present value is essential for comparing long-term projects fairly.
*   **What is the net present value (NPV) of each alternative?** The NPV helps determine whether the benefits exceed the costs over the life of the project (see next module).
*   **What recommendations can be derived from the analysis?** This includes determining whether the intervention creates a net social benefit and whether it is preferable to other alternatives.
# 3.3 Using Excel for Discounting
While the above process can be completed manually, spreadsheet software such as Microsoft Excel provides built-in functions to streamline the calculation. This becomes especially useful when analyzing long-term projects, some of which may span 50 years or more, where manual calculations would be impractical and error-prone.
We will illustrate the use of Excel for computing the NPV with the following example:
Imagine a small wind farm is going to be constructed. An initial investment of 10 million euros is needed. After the construction period, it will be operated for 15 years. The income from electricity production is 3 million euros per year. Assume that the discount rate is 5 percent. What is the NPV?
To compute the NPV:
1. Enter all costs and benefits into a spreadsheet, organized by year.
2. Apply the discount factor for each year using the formula 1\\(1+r)t
3. Multiply each year’s net benefit (benefit – cost) by its corresponding discount factor to obtain the present value.
4. Sum all present values to obtain the total NPV.
You can also use the NPV() command, i.e., = NPV(discount rate, series of Benefit/Cost that require discounting). The NPV() function automates the discounting of future values, though it assumes the initial investment occurs at the beginning of the time horizon and may need to be adjusted depending on project structure.
**Steps to Calculate NPV in Excel**
1. _Set Up Your Data Table_
Create a spreadsheet like this:

| A | B | C |
| ---| ---| --- |
| Year | Benefits | Costs |
| 0 |  | 10.000.000 |
| 1 | 3.000.000 |  |
| 2 | 3.000.000 |  |
| ... | ... |  |
| 15 | 3.000.000 |  |

Note: Row 3 will be Year 0, Row 4 Year 1, and so on.
1. _Use the NPV Function in Excel_
In Excel, the formula to calculate NPV is:
\=NPV(discount\_rate, value1, \[value2\], ...)
Note: the NPV function assumes cash flows start in period 1, so the initial investment in period 0 must be added separately.
**In our case:**
*   Discount rate is in cell G3 \*\*\*\*(5%)
*   Cash flows from years 1 to 15 are in cells B3:B17
*   Initial investment is in cell C2 \*\*\*\*(10.000.000)
Then, the full Excel formula is:
\=NPV(G3, B3:B17) - C2
This will return the Net Present Value of the project: €21.138.974,11 (see Figure 3.1).
_Figure 3.1 Calculation of NPV in Excel_
_Explanation_
*   NPV(G3, B3:B17) calculates the present value of the future income (€3 million per year for 15 years).
*   C2 subtracts the initial investment (€–10 million in Year 0).
*   Since Year 0's cash flow is not discounted, it must be added separately to the result of NPV().
To get the column for PV (D) you put the formula for PV in Year 1 in cell D3:
\= B3 / (1 + $G$3)^A3
$G$3 locks the discount rate cell so it doesn’t shift as you drag the formula down. A3 and B3 reference the year and cash flow dynamically. To drag down the formula you click on the cell (D3) with the formula. This will highlight the cell and show a small square in the bottom-right corner (called the "fill handle"). Hover over the bottom-right corner of the cell (the small square). Your cursor will change to a small black cross (✛). Click and drag down the fill handle as far as needed (i.e., down to row 17 for 15 years). Excel will copy the formula, automatically updating relative references (like A2 → A3, A4, etc.).
In this example, the project results in a positive NPV, indicating it is financially viable under the given assumptions.
**Costs**
*   **CAPEX**: €2.5M (PV, electrolyzer, storage, fuel cell, battery).
*   **OPEX**: €150k/year (maintenance, replacements, AI operation).
*   **Training costs**: Staff to manage AI system.
**Benefits**
*   **Financial**: Gas savings (€50k/year), electricity cost reductions (€100k/year), potential grid participation revenue (€50k/year).
*   **Environmental**: 2 million kg CO₂ avoided over 15 years.
*   **Social**: Improved healthcare reliability, job creation, innovation leadership.
*   **Resilience**: Island-mode operation during outages → critical in healthcare.

| Category | Description | Estimated Benefit (€ / Qualitative) |
| ---| ---| --- |
| AI Model Development (RL & ML) | Develop and train intelligent algorithms to optimize hospital energy use | Reduces overall energy costs by ~10–15% annually (~€5,000 savings/year) |
| Data Acquisition & Integration | Collect and clean hospital or synthetic data for model training | Improved decision-making accuracy; forms dataset for future expansion |
| Software Infrastructure & Tools | Cloud computing and simulation environments | Enables scalable AI testing at low ongoing cost |
| Workshops & Training | Training staff or students to understand and use the AI tools | Builds in-house knowledge and reduces dependency on external support |
| Program Coordination & Management | Planning, communication, and reporting | Ensures project delivery on time and within scope |
| Public Relations & Outreach | Materials to communicate project impact | Enhances hospital image, potential for green funding or publicity value (~€2,000) |
| Testing & Validation | System testing in a simulated or limited real setting | Validates feasibility, increasing future funding chances (~€5,000+ potential grants) |
| Contingency & Maintenance | Minor software updates and troubleshooting | Extends system usability and ensures long-term reliability |

**RISKS**

| Risk | Description | Mitigation / Solution |
| ---| ---| --- |
| System Reliability | Failures in AI control, hydrogen storage, or power conversion could disrupt hospital energy supply. | Use redundant AI controllers and backup systems (battery or diesel). Implement real-time monitoring, predictive maintenance, and fail-safe protocols. |
| Data Quality & Availability | Incomplete or poor-quality hospital energy data reduces AI accuracy and decision reliability. | Establish continuous data validation, standardized data collection, and backup data storage. Use synthetic or historical datasets to train and test models. |
| Regulatory & Safety Compliance | Strict EU and Dutch regulations on hydrogen safety and AI autonomy in healthcare. | Engage early with regulatory bodies, follow ISO and IEC hydrogen standards, and obtain required certifications. Conduct regular safety audits and staff training. |
| Environmental & Resource Risks | High water use for electrolysis or unsustainable material sourcing. | Implement water recycling for electrolysis, prioritize certified green materials, and monitor life-cycle environmental impact. |

# Untitled