Select Page
Microsoft Excel, a staple in the toolkit of professionals across a myriad of industries, is often perceived as a simple spreadsheet program, ideal for organizing data and performing basic calculations. However, Excel is far more versatile than it appears at first glance, boasting powerful functionalities that can be harnessed to execute complex tasks such as running Monte Carlo simulations.

Monte Carlo simulation is a computational technique used to understand the impact of risk and uncertainty in financial, project management, cost, and other forecasting models. It might sound intimidating, but in essence, it's simply a method for exploring the likelihood of different outcomes. With Excel, this process becomes more accessible, enabling us to comprehend, model, and predict scenarios that involve a level of risk or uncertainty.

Let's dive deeper into how Monte Carlo simulations work in Excel:

## Random Number Generation

At the core of Monte Carlo simulations are random numbers. Excel provides us with functions like RAND() and RANDBETWEEN() that generate random numbers for our simulations. These functions can simulate the uncertainty of variables in our model, such as fluctuations in interest rates, stock prices, or manufacturing costs.

## Model Construction

Building your model is the next step. This involves defining a mathematical relationship between your variables. For instance, if you're forecasting sales, your model might include variables like advertising spend, price, and sales volume, each associated with a certain degree of uncertainty. Excel's formula functionality can be utilized to create these relationships.

## Simulation Execution

Once the model is established, you can run the simulation. This is done by recalculating the spreadsheet, which generates new random values for each of your variables. Each recalculation represents a single iteration of your model, and each iteration provides a different potential outcome. You can achieve this in Excel either by using iterative calculations or by creating a loop using VBA (Visual Basic for Applications) programming.

## Result Analysis

After running several thousand iterations (the more, the better), you can analyze the results. This can involve looking at histograms, summary statistics, or other visualizations of your output data. Excel's data analysis and visualization tools, such as pivot tables and charts, can be very helpful in this stage.

## Sample Excel Model

The Excel file we are using as a sample serves as an insurance model, where it determines the life insurance premium using the personal details of a client. It takes into account various parameters, including age, gender, and policy amount, among other factors, as its inputs.

As previously stated, for a Monte Carlo simulation to be performed, the model must be executed numerous times with randomly generated input values. Naturally, for effective analysis using tools like a histogram chart, it's crucial to store both the input and output values.

In this Monte Carlo simulation, we've designated the policy amount, also referred to as the face amount, as the random variable. In the following screenshot, you can see how different Face Amount values generate different Total Premium numbers.

Running multiple instances of a workbook and storing results requires a VBA code like the following:

```Sub RunSimulation()
' Variables
Dim simulationTable As Range
Set simulationTable = Worksheets("Term Life").Range("s_SimulationTable")

Dim n As Long
n = simulationTable.Rows.Count

Dim resultsArray() As Double
ReDim resultsArray(n, 2)

Dim randomFaceAmt As Double

' Turn off screen update and calculations to improve perfomance
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

' Initially Calculate model to get a new random face amount
Calculate

' Calculate and write results to simulation range (o_simulationTable) n times
For i = 0 To n - 1
randomFaceAmt = Range("s_RandomFaceAmount")
Range("i_FaceAmount") = randomFaceAmt
Calculate
' Store simulation values in an array
resultsArray(i, 0) = randomFaceAmt
Next i

' Send simulation values to the range
simulationTable.Value = resultsArray

' Turn on screen update and calculations
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub```

The underlying code operates by conducting a simulation that repeats for 'n' times, corresponding to the number of rows within the simulation table range. During each cycle, it generates a random face amount, performs computations based on this amount, and records both the face amount and the total premium in an array. Upon completion, this array is then allocated to a range within the worksheet, facilitating subsequent analysis or visualization.

## How to handle simulations in SpreadsheetWeb

SpreadsheetWeb emulates the multi-run capability of VBA through its Custom Actions module. A Custom Action consists of a set of precompiled C# code that can be executed in conjunction with various SpreadsheetWeb events, such as calculating, saving, or printing.

Leveraging Custom Actions, you can construct and execute intricate calculation processes, establish actions that can export or import data from external applications or databases, and utilize APIs, including SpreadsheetWeb's proprietary ones.

### Creating a custom action

Here is a sequential list of steps required to create a custom action.

1. You need to generate a DLL file. Thus, start by creating a new C# Class Library in Visual Studio or your preferred IDE.
• Custom Action
• Pagos.Foundation.system
• Pagos.Designer.Domain.Interfaces
• Pagos.Hub.Sdk
• Pagos.Foundation.System
3. The available custom action hooks within the Designer are exposed in the external interfaces. By creating a class that implements one or more of these interfaces, you can trigger execution of custom code at the following target events.
Most common ones are BeforeCalculate and AfterCalculate which allows you to perform your actions before or after the calculation request has been sent to the calculation engine.

You can check dedicated help pages to learn about remaining events: Custom Actions in Designer
4. In our example, we have a separate method to generate random normal distributed numbers to avoid additional API calls to get values from Excel.
5. The rest of the code uses BeforeCalculation event to set the generated face amount before the calculation and make the model be calculated with it.
6. Beware that all named ranges in your code should be in the User Interface of your application. You can hide them if you do not want them exposed to the end-users.
```using Pagos.Designer.Definitions.Enums;
using Pagos.Designer.Domain.Interfaces.Calculations;
using Pagos.Designer.Domain.Interfaces.CustomHooks;
using Pagos.Foundation.System;
using Pagos.Foundation.System.Request;

public class CustomAction : IBeforeCalculation
{
public ResponseBase<ResponseAction> BeforeCalculation(INamedRangesCache namedRangesCache)
{
#region parameters
var inputRanges = new List<string>
{
"i_state",
"i_plan",
"i_period",
"i_IssueAge",
"i_Sex",
"i_Class",
"i_Mode",
"i_ChildrenRiderAmount",
"i_AccidentalDeath",
};
var faceAmountName = "i_FaceAmount";
var minFaceAmount = 15000d;
var maxFaceAmount = 250000d;
var diff = maxFaceAmount - minFaceAmount;
var rand = new Random();
var simulationTable = namedRangesCache.GetNamedRange("s_SimulationTable");
var n = simulationTable.Value.Length; // simulation count
#endregion

#region inputs
// set selected values
var inputsBatch = new List<List<RangeReference>>();

// generate random face amounts with GenerateNumbers method
for (int i = 0; i < n; i++)
{
var inputsSingle = GenerateInputsWithDefaults(inputRanges, namedRangesCache);
new RangeReference
{
Reference = faceAmountName,
Value = new[] { new[] { new CellValue {
Type = ValueType.Auto,
Value = GenerateNumbers(rand, diff / 2, diff / 6).ToString() }
} }
});
}
#endregion

"9112075248184511012468844564250653393323772843",
"8853748384374686797745310012555844672959900440584433",
"pagos_hub_api")
{
UseInstanceCache = false
};
#endregion

#region request
// create a request for SpreadsheetWeb API connection
var request = new ApiRequest<ApplicationMultipleCalculationRequest>
{
Request = new ApplicationMultipleCalculationRequest
{
WorkspaceId = Guid.Parse("b2d55c54-e2be-4b89-9c51-ca582606f584"),
ApplicationId = Guid.Parse("f060dfa7-8128-4ea2-9c9e-1580a50272df")
}
};

request.Request.Inputs = new Dictionary<int, IUnifiedCalculationInput>();

for (int i = 0; i < n; i++)
{
i, new UnifiedCalculationInput
{
Calculation = new CalculationInput
{
Inputs = new List<IRangeReference>(inputsBatch[i].ToArray()),
Outputs = new List<string> { faceAmountName, totalPremiumName }
}
});
}

request.Request.RecordInfos = new Dictionary<int, IRecordInformation>();
#endregion

try
{
#region response
// establish API connection and get responses

if (response.IsError && response.Messages.Count > 0)
{
File.AppendAllText("c://Exception.txt", "\r\nResponse Error:\t" +response.Messages[0].Message);
}
else
{
for (int i = 0; i < n; i++)
{
simulationTable.Value[i][0].Value = response.Response.Outputs[i].Calculation.Outputs[0].Value[0][0].Value;
simulationTable.Value[i][1].Value = response.Response.Outputs[i].Calculation.Outputs[1].Value[0][0].Value;
}
}

// update modified named ranges
namedRangesCache.UpdateNamedRange(simulationTable);
#endregion
}
catch (Exception ex)
{
File.AppendAllText("c://Exception.txt", string.Format("\r\nSomething bad happened!\r\nException Message:{0}", ex));
}
return new ResponseBase<ResponseAction>
{
Response = ResponseAction.Continue
};
}

static List<RangeReference> GenerateInputsWithDefaults(List<string> namedRanges, INamedRangesCache namedRangesCache)
{
var inputs = new List<RangeReference>();
foreach (var range in namedRanges)
{
new RangeReference
{
Reference = range,
Value = new[]
{
new[]
{
new CellValue
{
Type = ValueType.Auto,
Value = namedRangesCache.GetNamedRange(range).Value[0][0].Value
}
}
}
});
}
return inputs;
}
static double GenerateNumbers(Random rand, double mean, double stdDev)
{
double u1 = 1.0 - rand.NextDouble(); //uniform(0,1] random doubles
double u2 = 1.0 - rand.NextDouble();
double randStdNormal = Math.Sqrt(-2.0 * Math.Log(u1)) * Math.Sin(2.0 * Math.PI * u2); //random normal(0,1)
double randNormal = mean + stdDev * randStdNormal; //random normal(mean,stdDev^2)
return randNormal;
}
}```
7. Once you are set, build the DLL file.