Early Retirement Extreme Forums

Trying to think of something tangible to show that I'm good at excel, something more than "I've used it before in these classes/certifications and you'll have to take me at my word."

Maybe printing out a brief sample project, but what would even be on it? Hmm....

pivot tables

You could use it to compare various sectors/stocks and do some calculations on them all to point out which may be better investments?

+1 to pivot tables ... it's what separates the wheat from the chaff. Bonus points if you can do it blindfolded or w/o a mouse.

Hm, maybe I could make a brief pivot table to print and show (with some graphs), and send the full file to them attached to the short thank you note post interview?

Fun news is, I've never heard of a pivot table, but I'm sure I can figure it out.

+1 to pivot tables. If you demonstrate you know pivot tables you demonstrate you know everything else.

Perfect. I'll make a pivot table. Now to figure out what data set to make it out of...hm

Another example that gets people's attention is the ability to use Excel to iteratively solve problems by converging on a solution. Numerical integration is a classic example, but it's useful in lots of applications. I've used it to solve for things like mechanism positions and withdrawal rates.

Pro interview tip: To take it one step further from simply demonstrating competence, try solving a sample problem related to the job you're applying for. That can check off three boxes and simultaneously show off your excel skills, your ability to condense and communicate findings, and your skill for the job.

Data Analysis and Solver Tool-Paks don't seem to be discussed much (so probably won't come up in interview?) but I find them useful for investing problems. Have you installed these Tool-Paks and used them (should be available/free even in student Excel versions)? Very easy to do and start playing with so you are at least familiar with them, if you are asked. Here is how I've used these features applied to investing:

Data Analysis Tool-Pak: This has various statistical functions, which you shouldn't cram for an interview unless you are already comfortable with statistical tests. However one easy and convenient feature is Histogram creation. Excel is not user-friendly for histograms, but if you install Data Analysis all you have to do is create a set of numbers to define your bins and then tell the histogram plotter where your data and bins are. I have used this when creating my own FireCalc using randomized returns, then creating a histogram to show the distribution of ending portfolio values.

Solver Tool-Pak: Set cells as input variables and Excel will use a solving engine to find values of the inputs that meet the results you define (output in other cells). I have used this on a sheet containing historical data of different stocks/ETFs, with inputs being the allocation % of each investment. Then using those % allocations, I calculated the resulting portfolio, its return and volatility, and resulting Sharpe ratio. I set Solver to vary the allocation to maximize the Sharpe ratio.*

For an advanced feature like Pivot Tables or Tool-Paks which you only get a chance to spend a couple of hours looking into before the interview, I wouldn't bring out a printout or steer the conversation in that direction. The interviewer could start digging deeper than your current depth on the topic. It's just good enough to know about these features if the interviewer brings up a problem that could be aided by them. In my interview for my current job, I studied a bit on an advanced measurement technique, but hadn't spent the many days in the lab needed to get comfortable with it. I wouldn't have brought it up, but the interviewer asked about it so I just described it in basic terms to show that I was aware of it, without getting myself in trouble saying that I could already do it. Evidently that was enough to meet their expectations and hire me!

*This was fun to play with and see what would have been possible in the past, but I wouldn't apply this type of optimization to real portfolios. I was optimizing based on daily data and short term correlations, which can get you in a lot bigger misalignment of past performance & future returns than the common way of portfolio construction using annual asset data over many decades.

I find the vlookup, sumif, countif, and array functions invaluable when dealing with big data sets.

With respect to historical performance, know how to calculate in Excel daily/monthly returns from cash flows, geometrically link monthly returns into quarterly returns, calculate annual/annualized returns following the global investment performance standards https://www.cfainstitute.org/ethics/cod ... index.aspx

With respect to forecasting/modeling, learn how to build Monte Carlo simulations in Excel VBA

These entries make me want to go and lie down for awhile. I told a friend once: "I've concluded that I only use about 10% of Excel's capabilities." His reply: "Don't flatter yourself."

I've revised that to 1%.