Excel Simulation

[Tknee]

I don't know how useful this actually is as I'm not convinced my modelling assumptions are appropriate, but I thought I would share this just the same. The attached file is a zipped folder containing a macro-enabled excel file which uses a discrete event simulation to see resulting scores and QPs.

Modeling Decisions:
Time between goals for the entire alliance is a random number taken from the exponential distribution which is usually appropriate for arrival events includeing soccer goals (though Breakaway may be different due to multiple balls). I allow for different mean times between goals when scoring on the opponents goals and scoring on one's own goals to reflect that you are only allowed one robot in the defensive zone. When an alliance is winning by a certain amount or losing by a certain amount, the alliance may switch to scoring for the opponents. They may return to scoring for themselves if the defecit is reduced. Every time a team switches targets they are penalized a set up time in seconds (to reflect traversing the field).

For simplicity, I've assumed a penalty free match (though I suppose I could also model penalties using a poisson process). I also assume hanging is deterministic, that is the number of points an alliance receives from hanging is known before the match and occurs regardless of score. Outside of switching based on differences in score, strategies are static and independent of time.

Code & Use
The code was canabalized from a school project and written pretty ad hoc so it's not very pretty or well documented, but feel free to make changes.

Choice of parameters are in cells B4 to I5. They are pretty self explanatory. If you don't want switching just set the switch number to something high. If you want to see a 6 Vs 0 set the switch number to a negative number.

The random numbers are chosen from streams so that the results are reproducable. To get different results change the seeds cells B8 to C9.

There are two buttons, one simulates a single match. The other simulates 1000 matches and displays the average scores and average QPs from those matches.

Conclusions
Again, the simplifying assumptions may not be accurate and I'm actually not sure what parameters are appropriate. But I've found that the with the new seeding algorithm, it doesn't appear to be profitable for a winning team to switch sides (the time it takes to switch is better spent on continuing to score for yourself). I included the old QP formula for comparison sake (where it was profitable to switch).

[EgROS]

This is a very impressive and insightful program! Thanks for posting!!

When in the right hands, excel can do so many incredible things.