I need Some Advice From My Readers - Excel Topics For Class

I'm teaching the investments class this spring, and it's been a couple of years. I'm trying to add a few things to the class, and have pestered my colleagues at Unknown University (and other schools) for some advice. So, I thought I'd use y'all likewise to see what suggestions you might have.

Here's my goal: I want to embed more Excel assignments in my class, since Finance Majors can't have too much Excel exposure. So, I'm trying to add some assignments that expose them to the following concepts (note- those in bold type have been suggested by readers)

• Data Tables
• Pivot Tables & Pivot Charts
  
• IF (and Nested IF) statements
• Macros and basic VBA
  
• Solver and Goal Seek
• Regression Analysis
• Conditional Formatting
  
• Using some of the auditing tools
• Keyboard shortcuts
• VLOOKUP/INDEX/MATCH
  

My goal is to get them comfortable with at least some concepts that can be used to signal to potential employers that they're at least a cut above the typical student. This way, they can have samples of the output they've produced and (if they're smart) copies of the underlying spreadsheets on their flash drive and laptops in their back pockets for interviews. It's no magic bullet, but I figure it can;t hurt.

Some of the projects they might be doing could include (note: I might not get to all of this, but it's good to have aspirations):

• Building pro-forma statement-driven cash flow valuation models
• Profiling industry ratios (taken from Compustat) using Pivot Tables
• Calculating "justified" price multiples using regressions of multiples on industry fundamentals
  
• Estimating betas
• Calculating a variance-covariance matrix
  
• Calculating portfolio weights that yield efficient frontiers using solver (and possibly, some basic VBA)
• Calculating tracking error
• Performance attribution
• Technical analysis/indicators (i.e. moving averages, etc...)
• Describing statistical properties (skewness, kurtosis, etc...) of return distributions
• An event study
  

I've lifted some ideas from Benninga's Financial Modeling book, and also read Craig Holden's text.

So, here's what I'm looking for - can you suggest any additions to the list as far as Excel topics they should cover or projects I can assign? We cover only the equity side of things (no derivatives or fixed income, since they get those in other classes).

Please sound off in the comments.

Spreadsheets, Spreadsheet, and More Spreadsheets.

Yesterday, I thought I was coming down with something - I had a sore throat when I went to bed, and I woke up this morning feeling kind of blah. So, I thought I'd muddle through my classes (unfortunately, it's my long teaching day), and then come home and go to bed. By the end of the day, I felt like I'd been beaten with a stick - sore and feeling heavy-limbed all over.

But, it was the Unknown Daughter's birthday, so we had festivities first.

Then, I thought I'd put in a little work on before going to bed. Big mistake.

I started working on some spreadsheet models for my Fixed Income class at about 9 (just for an hour or so, I thought). Before I realized it, it's 3 a.m., and I've stayed up too late once again.

So far, I've made two spreadsheet models. One calculates duration and convexity for any combination of coupon, maturity, frequency, and yield, along with some graphs. The other calculates the average life of a mortgage pass-through based on various prepayment assumptions (multiples of PSA). While they're not pretty (I'm not exactly a wizard at formatting), I'm pretty happy with them, because they both use fairly complicated (for me) nested IF statements.

Next up will be a model for a sequential-pay CMO. That should be fun.

I'll end up eventually turning all the models into video tutorials (probably over the break), and will assign them for the students to replicate the next time I teach the class.

My basic approach to teaching is that if they can't calculate it, they don't understand it. So hopefully these will help.