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.
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:
