## Application of @RISK’s Monte Carlo Distribution in Excel for Renewable Energy Risk Modeling

When building a manufacturing facility, a reliable and resilient power source is key. As social and governmental pressure to decarbonize the manufacturing sector intensifies, more companies are moving away from fossil fuel-powered plants – for example, a 2024 article in Automotive Manufacturing Solutions notes that major automobile manufacturers in Europe, Asia, and North America “continue to innovate and adopt green energy practices,” such as plants powered in whole or in part by solar, wind, and hydroelectric energy.

In addition to addressing sustainability, renewable energy can also result in more cost-effective operation in a time of global energy supply turbulence. A March 2024 article in the Engineering Science & Technology Journal observes that “[t]he integration of renewable energy into the manufacturing sector is not just a step towards mitigating environmental impact but also a strategic move that opens up a wealth of opportunities for innovation, competitiveness, and growth.”

However, making the switch to sustainable energy sources comes with its own set of risk factors. In a recent Lumivero webinar, “Illuminating Probabilistic Risk in Renewable Energy,” energy industry consultant Manuel Carmona walked attendees through modeling methods that can help evaluate the different types of operational and financial risks for renewable energy products in manufacturing. In this case study, we’ll discuss the highlights of Carmona’s presentation, define and describe how to use Monte Carlo simulation, and present the risk modeling examples used to make better decisions in the field of renewable energy.

## Key Points

- In solar or wind energy projects, one of the key uncertainties is the amount of sunlight (solar irradiance) or wind the panels and turbines will process. The performance and reliability of other equipment, such as inverters or sensors, may also introduce uncertainties, often treated as random variables.
- The financial viability of renewable energy projects can be influenced by market conditions and electricity demand. Other financial variables include the cost of equipment, financing terms, electricity prices, and government incentives.
- Construction timelines for renewable energy projects can be subject to delays due to various reasons.
- Government policies and regulations in the renewable energy sector can change over time and introduce uncertainties.
- @RISK makes it possible to quickly model the probability of disruptions, the potential power output, and the potential financial impact (positive or negative) of many other aspects of renewable energy projects in manufacturing.

*""[@RISK] significantly enhances decision-making by enabling experts, practitioners, and professionals to swiftly construct probabilistic models using Monte Carlo simulation, effectively analyzing risk, exposure, and the impact of uncertainties in every business decision.""*

Manuel Carmona, MBA, PMI-RMP®

Manuel Carmona, MBA, PMI-RMP®

## Using Monte Carlo Simulation to Determine the Feasibility of Construction of a Renewable Energy Plant for a Manufacturing Company

### Background

Manuel Carmona is a certified Project Management Institute Risk Management Professional (PMI-RMP) with more than 25 years of experience in managing projects within the energy and technology sectors. As a trainer with EdyTraining, he has helped manufacturers utilize @RISK and XLSTAT to run Monte Carlo simulations using Excel – simulations that can be used for various types of probabilistic analysis.

Probabilistic analyses can be used to answer a wide range of questions raised at the outset of a renewable energy project, including:

- Which project has the highest chance of success and should receive funding?
- What is the likelihood that a project will be completed on time and on budget?
- What are our chances of meeting a deadline?
- How realistic are the contingencies for a cost estimation?
- What is the expected net present value (NPV) of a project?
- What is the effect of implementing a certain mitigation policy in a project?
- What is the likelihood that the project costs will exceed budget?
- What steps or phases of the project are likely to cause my total cost to go over?
- Where in the project is it most reasonable to mitigate?

To generate these analyses, Carmona recommends building models using @RISK by Lumivero, a probabilistic risk analysis tool that lets you create Monte Carlo simulations while using Excel spreadsheets.

### What Is Monte Carlo Simulation?

Monte Carlo simulation is a statistical analysis technique first developed by scientists working on the Manhattan Project during World War II. It’s used to create probabilistic forecasts that account for risk and random chance within complex systems. Finance, meteorology, insurance and defense are just a few of the industry sectors that make use of Monte Carlo simulations to inform decision making.

Powered by software such as @RISK, Monte Carlo simulation can quickly generate thousands of simulations using random numbers that account for a wide range of variables, generating many different outcomes along with the probability of their occurrence.

### Advantages of Monte Carlo Simulation Over Other Techniques

Creating probabilistic analysis models with a Monte Carlo add-in for Microsoft Excel is typically a simple process that generates a complete range of possible values as opposed to traditional deterministic modelling techniques.

Most analysts use single-point estimates (also known as mean values or most likely values) for their estimations, then perform a series of best- and worst-case scenario calculations using formulas to determine the impact of a specific variable on a project.

For example, an analyst might begin their calculations by setting the cost of building an energy plant as high as estimates indicate it will go, generate an output, and then work in increments to gradually define potential impacts of a project. Manually adjusting the parameters for each calculation allows for refinement of the outcomes, but it cannot produce a complete range of potential outcomes.

With Monte Carlo simulation, analysts can develop comprehensive risk analyses more quickly – analyses that project risk into the future to determine whether an investment is worth making. These analyses can also be adjusted to model many different types of risk or uncertainty including cost assessments across the life of a project.

### @RISK – Monte Carlo Simulation in Excel Spreadsheets

With @RISK, project managers can build models and run thousands of simulations under different scenarios – allowing them to quickly model probabilities across a wide range of variables. Plus, the interface allows for rapid generation of graphics that help stakeholders visualize findings. Options include tornado graphs showing advanced sensitivity analysis, stress testing, scatter plots, and more.

Carmona notes that because @RISK integrates with Microsoft Excel, creating a probabilistic analysis is as simple as selecting any other Excel pre-programmed function – making the creation of models a straightforward process. By integrating these various uncertainties into a comprehensive @RISK model, project managers can perform Monte Carlo simulations, running thousands of iterations to assess a project's financial performance under different conditions and scenarios.

This approach provides valuable insights to project stakeholders into the range of possible outcomes, the probability of meeting certain financial targets, and the identification of critical risk factors that may significantly impact the project's success and objectives.

## The Project – A Hybrid Energy Plant for a Manufacturing Facility

Carmona demonstrated how @RISK could be used to analyze uncertainties and costs for building a renewable power plant for a manufacturing facility. The model plant would utilize solar panels and wind turbines to generate energy and would need to reliably produce eight to 12 megawatts (mWh) of energy per day.

For the purposes of this exercise, Carmona assumed that the plant was well-sited and that its solar panels and turbines were appropriately sized. The first question to answer was: based on probabilistic analysis, how much power would the plant usually generate in a given day?

## Resource Uncertainty Assessment

To begin answering this question, it was necessary to develop models that incorporated different types of uncertainty. The analysis began by looking at the solar plant. Three variables that could impact energy generation include:

- Solar irradiation (the amount of sunlight reaching the panels)
- Cloud cover
- Air temperature

Using power output data from the solar panel manufacturer and weather data for the city in which the plant was to be built (Madrid, Spain), Carmona used @RISK to generate a distribution curve for power output based on solar irradiation. On a completely cloudless day, the plant could be expected to produce 12–13 mWh of power during daylight hours. Given typical weather conditions at the site, what would the power output of the plant most likely be?

Carmona used the @RISK add-in to simulate a dynamic environment with cloud cover that changed throughout the day with Monte Carlo simulation using random variables. Before running the simulation, he defined the cloud cover using a standard distribution. This required some adjustment to ensure that the model did not generate cloud cover values greater than 100%.

Cloud cover was not the only variable to account for, however. Temperature impact the power output of a solar cell as well – the higher the temperatures, the lower the output. While a perfectly cloudless day should result in maximum power output, an exceptionally hot day can actually impair generation. The model therefore needed to account for temperature correction.

## Advanced Techniques and Strategies for Using Monte Carlo Simulation

Since @RISK utilizes the Latin hypercube method, a statistical sampling technique that cuts down on computer processing time, Carmona was able to quickly run 100,000 Monte Carlo simulations. A plot of the results was generated, resulting in a probability distribution curve for the daily production of electricity. The simulation projects that on 47.9% of days, energy production would be within the desired range between 11 and 13 mWh.

## Probability of Equipment Performance and Reliability Analysis

The next variable to account for was equipment. Power generated by renewable energy means still needs to travel from where it is generated to the facility it needs to power, and sometimes external equipment, such as electrical transformers, may fail. There are also other types of failure events to consider such as damage to solar panels from hailstorms, module soiling, and vegetation overgrowth. The next stage of the probabilistic analysis process was to model for these types of external failure events.

Carmona allowed for four failure events – one that happens every 250 days of operation, one that happens every 500 days, 750 days, or 1,000 days. In every instance, a failure event means that the plant does not provide any activity for the rest of the day.

Using @RISK, Carmona ran a second series of simulations that accounted for external failure events, then generated a new distribution graph to show how these new variables could further impact the probability that the plant would produce enough power on a given day. The red bars indicate the original simulation; the blue the second simulation. This second visualization clearly shows that failure events were likely to reduce the probability of the plant producing enough energy by 3%.

The model shows that on most days the solar plant would be able to produce around 9 MWh of power on most days.

## Modeling Combined Wind and Solar Energy Using Random Variables

So far, the probabilistic models indicated that the solar plant would not be able to produce enough electricity to meet demand on most days. However, adding wind generation would allow the factory to charge storage overnight when the plant was not producing.

Modeling the power outputs for the wind plants followed a similar process to modeling solar output. This process included gathering weather data about average wind speeds in the area where the plant was to be built and manufacturer data about how much power the turbines generate at a given wind speed. The chart below shows what percentage of output the wind turbines generate (Y-axis) given a specific wind speed (X-axis). Note that faster wind speeds will actually hinder power generation after a certain point, just as a day that is sunny but also very hot will reduce the effectiveness of solar panels.

After running simulations for the wind plant, Carmona was able to demonstrate that the combination of both generation methods – wind and solar – had a high probability of meeting electricity demand on most days.

That accounts for the actual power generation issues. What about the costs of operating and maintaining the plant?

## Using @RISK Results to Determine Net Present Value (NPV)

Carmona decided to conduct a NPV analysis comparing the lifetime cost of operating the renewable energy plant with performance-monitoring software involved. Without the monitoring software, the plant performance would be lower than with the monitoring software. With the software, the plant would produce approximately 6% more energy. Does licensing and operating the monitoring software result in actual saving over time?

The table below was used to generate a 13-year forecast that also accounted for estimated plant inspection and maintenance costs, which would take place every three to four years.

Then, Monte Carlo analysis was performed to generate an average NPV. This showed that the average NPV of the plant without the monitoring software would be €134,000, while the average NPV with the monitoring software would be approximately €169,000 over the same period.

The result: running the plant with monitoring software would result in an average savings of €35,300.

What about the risks and costs involved with building the plant? Fortunately, DecisionTools Suite’s ScheduleRiskAnalysis allows project managers to assess time and cost uncertainty. The program can import details of projects that have been scheduled in either of two popular project management tools: Microsoft Project or Primavera P6 from Oracle. Project managers can use @RISK to import their project schedules into Excel and carry out Monte Carlo simulation to determine the impact of construction delays or cost overruns.

## One Program, Endless Solutions

For renewable energy projects, @RISK empowers project managers and decision makers to make informed choices by generating Monte Carlo distributions in Excel. From determining power output to evaluating the value of investing in add-ons like monitoring software, @RISK can help you develop robust probabilistic analyses for a variety of risks and provide clear visualizations of results.

## Get Started with @RISK in Microsoft Excel

Find out how you can generate better risk analyses for your renewable energy projects – or any other projects – within Microsoft Excel. Request a free trial of @RISK today. You can also watch the full webinar on-demand!

**SOURCES**

Energy Risk Modelling, Roy Nersesian, Ed. Palisade.

Manuel Carmona, Edytraining Ltd.