Iterative Macro for Monte Carlo Simulation

[davidwwyche]

I am trying to develop an iterative macros to create a Monte Carlo-like simulation.

The example I am starting with is a fantasy football simulation, but I plan to use a similar model for work (allocating volumes to specific customer-supplier relationships to model profitability sensitivity).

My constraints are this:

12 teams

11 weeks

X number of seasons.

A) Teams can only play each other once per season

B) Teams must play 1 and only 1 team each week

My input appends Teams-Opponents-Weeks-Seasons.  So for a 10 season simulation, my dataset is 14,520 records (teams cannot paly themselves).  Which looks like this (I am coding combinations of teams, opponents, seasons, and weeks accordingly so I can remove in the macro):

I have attached my macro which shows how I am randomly selecting a record and removing any records conflicting with my constraints.

When I run through my final workflow, duplicate records (switching teams and opponents satisfying the condition that if team a is playing team b, then team b must also play team a), I am still left with null values, like the below (cross tabbed with team # across the top):

I should have exactly enough rows to randomly select all matchups necessary and satisfy my conditions, so I can't figure out why I am creating null matchups.

Any help appreciated.

[DavidSta]

Hi @davidwwyche,

can you maybe share your complete workflow including the data, otherwise it might be hard for the community to help.

Best regards,

David

[Adrian_T]

Hey @davidwwyche,

I would suggest taking a look at the out of the box Prescriptive Analytics tools that runs the Monte Carlo Simulation:

• Simulation Sampling Tool: Allows random draws for given variables; you can sample from existing data for a variable directly, estimate best fitting parametric distribution, specify or “draw” the parametric distribution
• Simulation Scoring Tool: Provides predicted values given uncertain predictors, and provides measures associated with the uncertainty surrounding a model’s predictions
• Simulation Summary Tool: Delivers an interactive dashboard and a standard report of the simulation results

You can find these tools here:

[davidwwyche]

Attached is the sample set generation workflow (just appending inputs to arrive at all possible outcomes, by week).  

I just run that through the macro I attached earlier