Today was the first qualifier round for ModelOff, the World Financial Modeling Championships. I’ve posted my models for the three main questions below, with a few comments on each. Please note these are what I made during the test, with lots of time pressure, etc – I don’t know if they’re complete and correct (I hope so!), and I’m sure there are things I would have designed differently with a bit more time (some of which I’ve commented on below). But since I always find it interesting to see how other people tackle a challenge like this, I thought you might find it interesting to see how I do too.

# Data visualization challenge – chess notation

Part of the art of making a great data visualization is finding the right way to show a certain data set. If you work in Excel a lot, you might be used to ‘finding the right way’ meaning ‘choosing between a bar chart, line chart, and pie chart’ – but sometimes the data calls for a very different visualization, and in this post I’m going to share an example of that.

The data set I was trying to visualize was this:

1. e4 e5 2. f4 exf4 3. Bc4 Qh4+ 4. Kf1 b5?! 5. Bxb5 Nf6 6. Nf3 Qh6 7. d3 Nh5 8. Nh4 Qg5 9. Nf5 c6 10. g4? Nf6 11. Rg1! cxb5? 12. h4! Qg6 13. h5 Qg5 14. Qf3 Ng8 15. Bxf4 Qf6 16. Nc3 Bc5 17. Nd5 Qxb2 18. Bd6! Bxg1? 19. e5! Qxa1+ 20. Ke2 Na6 21. Nxg7+ Kd8 22. Qf6+! Nxf6 23. Be7# 1–0

Continue reading “Data visualization challenge – chess notation”

# Algorithms for conquering castles [Puzzle]

This week’s Riddler is about the value of perfect information in a simplified war game. There are 10 castles, worth 1, 2, 3, … , 9, and 10 points each, and two players each assign a certain number of troops to each castle. If one player has more troops at any given castle, they win the points for that castle, and the player with the most points wins the game.

The twist in this version is that one player gets a second-mover advantage, by getting to see the first player’s allocation before making their own. Clearly that information is very valuable, but the challenge is quantify exactly how much it’s worth. Specifically, if Player 1 has 100 soldiers, find the smallest k so that if Player 2 has k soldiers and knows Player 1’s distribution, they can always win the game.

Continue reading “Algorithms for conquering castles [Puzzle]”

# Friday puzzle post

This week’s Riddler is about a hypothetical gameshow, and knowing when to stop.

You’re on a game show, and you’re asked to sit down at a table covered with sealed envelopes. You are told that each envelope contains a check for an amount of money, each amount different from all the others, but you are given no other information about the distribution of amounts. (As far as you know, the biggest check on the table could be $1.06 or it could be $98,765,432,100.00.) You may pick an envelope, open it and read the amount of the check. You can then either keep that check, ending the game, or toss it away permanently and open another envelope. You can then keep that second check or toss it away and open a third envelope. And then you can keep the third check or throw it away and pick a fourth envelope. But that’s it — if you open a fourth envelope, you have to keep that check, no matter how paltry it is.

What strategy should you follow to maximize your chances of getting a nice payday?

# Presentation and data visualization in Excel (Part 1)

Most people I know who analyze data or build models and then present the results do the first part in Excel, but switch over to PowerPoint or something else to make the ‘client-ready’ output. A few years back, I switched to making the majority of my presentations directly in Excel.

This is a topic I’m very interested in, and I’m sure I’ll write more about it later (especially re: pros and cons of making your slides in Excel vs PPT), but for now I just want to share a few examples to give you a flavor of what’s possible in Excel.

Continue reading “Presentation and data visualization in Excel (Part 1)”

# Mapping divisions in the US Senate (part 2)

This is part 2 of my analysis of US Senate voting data. You can find part 1 here.

In part 1, we looked at each senator’s degree of alignment to their party, and how this changed over time. This time, I want to dive a little deeper into comparing senators to each other, separately from their party allegiance.

Continue reading “Mapping divisions in the US Senate (part 2)”

# Excel challenge solution: Mastermind

A few weeks back, I published an Excel challenge to score, and maybe solve, the game Mastermind in a spreadsheet. Today, I’m going to share some solutions to that challenge. To avoid repeating myself a lot, I’ll assume you’ve read the original challenge already – if you haven’t, I’d suggest you take a look at that first.

[The picture is my own Mastermind board, a particularly excellent gift from my sister-in-law which inspired this challenge.]