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
resultsArray(i, 1) = Range("o_TotalPremium")
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.
- You need to generate a DLL file. Thus, start by creating a new C# Class Library in Visual Studio or your preferred IDE.
- Add references to requisite SpreadsheetWeb Custom Action and SpreadsheetWEB API assemblies:
- Custom Action
- Pagos.Spreadsheet.Domain
- Pagos.Foundation.system
- Pagos.Designer.Domain.Interfaces
- SpreadsheetWeb Hub API
- Pagos.Hub.Sdk
- Pagos.Foundation.System
- Custom Action
- 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 - In our example, we have a separate method to generate random normal distributed numbers to avoid additional API calls to get values from Excel.
- 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.
- 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; using Pagos.Spreadsheet.Domain.Models; using Pagos.Spreadsheet.Domain.Requests; using Pagos.Spreadsheet.Interfaces.Models; using Pagos.Hub.Sdk.Adapter; using ValueType = Pagos.Spreadsheet.Interfaces.Definitions.ValueType; 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", "i_WaiverPremium" }; var faceAmountName = "i_FaceAmount"; var totalPremiumName = "o_TotalPremium"; 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); inputsSingle.Add( new RangeReference { Reference = faceAmountName, Value = new[] { new[] { new CellValue { Type = ValueType.Auto, Value = GenerateNumbers(rand, diff / 2, diff / 6).ToString() } } } }); inputsBatch.Add(inputsSingle); } #endregion #region adapter // create an adapter for SpreadsheetWeb API connection to your server var adapter = new HubApiAdapter( "https://hub.spreadsheetweb.com/identity", "https://hub.spreadsheetweb.com/api", "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++) { request.Request.Inputs.Add( 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 var response = adapter.Calculations.CalculateMultiple(request).Result; 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) { inputs.Add( 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; } } - Once you are set, build the DLL file.
Adding custom action to a SpreadsheetWeb application
The steps outlined below demonstrate how to incorporate a custom action into a SpreadsheetWeb application.
- Open your application in Designer mode.
- Proceed to Custom Action module.
- In the next screen, click Add Custom Action. After giving your custom action a name, select or drag-and-drop the *.dll compiled assembly file into the Custom Action File Once the file upload is completed, press Create Custom Action.
- After creating, you can assign the custom action into a button. Edit a button and check Events box.
- Do not forget to add a chart and select the histogram named range to display the simulation results in your app.
- Once you’re done, remember to press the Submit button to save your changes. You can now click on Preview in the dial menu or the right-hand properties menu to see a preview of the application.
Excel's computational power combined with its flexibility makes it a valuable tool for running Monte Carlo simulations. By following the steps outlined in this article, you can harness the capabilities of Excel to model and analyze complex systems effectively. Whether you are working in finance, engineering, project management, or any other field that requires probabilistic analysis, Monte Carlo simulations in Excel can provide valuable insights for decision-making and risk assessment.
