Daily Dose of Excel

I’m a big fan of the Toyota Production System. I was thinking that I should find a mutual fund that invests in lean companies, but I couldn’t find one. I searched for “best lean companies” to see if I could get a list, and found Who Are the Best Lean Companies at leanblog.org, a blog I read every day.

I took all of the companies in the top 10 for whom I could find historical data. I compared buying one share of each of those companies to buying one “share” of the S&P500 over the same period. The lean companies outperformed the index.

D9: =RATE(C1-B1,0,-B9,C9)*365
D11: =RATE(C1-B1,0,-B11,C11)*365

Are those formulas right? I’m trying to get an annual return.

I couldn’t stock-pick my way out of a paper bag (I’m looking at you Buckle; I’m looking at you Inacom), so don’t take investing advice from me.

Red wants to have a kind-of lottery for his students. He will award them prizes based on a random drawing, but wants to weight each student based on the number of assignments turned in. Normally, I would accomplish this by typing the name of each student in column A one time for every assignment he turned in. If John turned in three assignments, I'd type his name three times. In column B, I'd put a RAND() function and fill it down. Then I'd sort by column B. I'd get something that looks like this:

That's all well and good, but it's missing a few things. If I'm giving away three prizes, Sue wins them all unless I manually exclude her. But the worst part is that it happens too fast. Excel calculates so fast that it's not entertaining to calculate in front of a group (unless you're Charles Williams, of course). I wanted to come up with something that doesn't allow ties, calculates more slowly, and is generally more friendly. Here's my first stab

Behind the button, I have this code:

The user enters the information in columns A and B, up to 30 students. The code fills column I with one instance of each name for each assignment. Then for each name it fills in a random number between 0 and 999. For show, it fills each cell 50 times to make it look like it's really doing some work.

In column C, I have this array formula

=MAX((rNames=A2)*(rDraws))

and in column D, this

=IF(RANK(C2,$C$2:$C$31)>3,"",CHOOSE(RANK(C2,$C$2:$C$31),"1st Place","2nd Place","3rd Place"))

There's not a lot of error checking and far too many literals in the code, but it's a start.

Download Lotter.zip. Yes, it's 2003 format.

Recently, I had to submit a fixed width text file to a state tax agency in lieu of sending them physical copies of W-2 forms. Fixed width files contain a number of columns and each column starts at a particular position on the line. If the data in that column is shorter than the width of the column, the column is padded with spaces so that the next column starts in the correct position.

I had the data in Excel, I just needed to get it to a text file. As it happens, I only have one employee for this state, so it would probably have been just as quick to type it manually. But I think we all know why I didn't do that. The spec for the file is this:

Here's the monster formula in F1:

=A1&REPT(" ",A2-LEN(A1))&SUBSTITUTE(B1,"-","")&REPT(" ",B2-LEN(SUBSTITUTE(B1,"-","")))&C1&
   REPT(" ",C2-LEN(C1))&D1&REPT(" ",D2-LEN(D1))

The basic structure is cell value & repeat a space to fill. The exception is B2 where I had to remove the dashes from the social security number. If I had more than one row to do, I probably would have put the field lengths above the data. Then I could fill down column F. Finally I would copy F1 to a blank Notepad file, and there's my fixed-width text file.

Recently I got a workbook with this formula:

=F57+F52+F43+F35+F25+F16+F10+F8+F59+F61+F60

I'm sure you've seen a few formulas like this. I know I have. I may have even made one or two. There is a better way, however. Usually a formula like this means there are several lists in the column with subtotals and this formula returns a grand total. The problem is when you want to add a new list or item and include it in the total. It's not hard to add a +F62 to the above formula, but if you forget to do it, it's not always obvious that the formula is wrong.

I changed the above using the SUBTOTAL function (with 9 as the first argument because I'm summing). One really nice feature of SUBTOTAL is that it ignores any cells that have a SUBTOTAL function in them. I can SUBTOTAL the whole range, and as long as I've used SUBTOTALs within the range, I don't have to worry about double counting. If I add a new item in the middle, the SUBTOTAL range will adjust to accommodate.

Here's an example of the old way:

Here's the same example using SUBTOTAL:

The other day J-Walk posted How to Retire at Age 40:

If you were to take 20% of your annual income starting at age 20 and put it in a fund following the S&P 500 Index ($INX), that fund continued to grow at the long-term historical rate (12%) and you received a 4% raise each year, you could walk away from your job and live off the interest at age 41 matching your current salary -- or quit at 43 and be able to give yourself a 4% "raise" each year from the interest, which is probably the better plan because it combats inflation. Original Source

Then today, he posted Retirement Revisited which proves out the math.

I've been working on this also and wanted to post my results too. Instead of assuming that the S&P 500 returns 12%, I used actual returns and based it on my actual life. Sort of.

I started at Standard & Poor's - Indices S&P 500 and clicked on the "S&P 500 historical returns" link to download the Excel (xls) file. I don't remember how much I made when I was 20, but it wasn't much because I was still in college. In fact, I think I was a third-year freshman. I turned 20 in June of 1989 and let's assume I made $15,000 per year. I've hid some columns and rows, but here's the gist of it:

Column D is the monthly return for the S&P 500 for all 240 months between my 20th and 40th birthdays. Since I'm not 40 yet, the last 19 months are the average annual return of the index from June '89 to October '07 (7.98638%). That was computed thusly:

=RATE(221,250,0,-125150.57,,0.1)*12

The interest rate for 221 months when I start at zero, put $250 per month in, and end at $125,150.57. The $125k is what I get from this worksheet when I don't increase the monthly pay by 4% and is not shown in the screen shot.

Column N is just a counter showing the month. Column O is the monthly amount saved. The formula in O9 is

=$O$1/12*0.2*(1.04^(INT(N9/12)))

$O$1 is the annual salary at age 20. Divided by 12 months. Times 20% savings rate. Increased 4% every 12 months.

Column P shows the interest I earned for the period and column Q is the balance in my investment account.

P: =Q10*D9
Q: =Q10+O9+P9

I'll have $196k to live on. Let see if it will last.

Nope. If I live until 75, I'll be $3 million in debt. At age 40, I'm making about $33,000 per year and that's the standard I need to maintain. If I pull out 1/12 of that every month, I'll be broke sometime in September of 2017. Damn, the Huskers might be good by then and I won't have any money to go to the games.

Column B: =MONTHLYRETURNS!$O$1/12*(1.04^20)
Column C: =D3*MONTHLYRETURNS!$D$9 $D$9 is the average return of ~8% per year from the other sheet.
Column D: =D3-B4+C4

It's not prettied up, by you can download SP500Monthly.zip.

A couple of weeks ago I spent some time in the bible belt attending football games. Next week I'll be in Bush country (Austin, TX) for yet another football road trip. One of the more exciting aspects of these road trips is settling up on expenses when you get back. Depending on the group, this can take between six days and six months. Here's a spreadsheet I use to do it:

The formula in E3:I11 is

=((LEN($D3)-LEN(SUBSTITUTE($D3,E$2&";","")))/LEN($D3)*$B3)-IF($C3=E$2,$B3,0)

The first part determines how many times a certain person's initials appear in the 'Paid For' section and takes a proportionate share of the money. The second part subtracts the total expense if this person is the person who paid the bill. For it to work, you have to use the same number of characters for every person, which is why initials works so well. And don't forget to include that pesky semicolon, particularly after the last guy in the list.

The Hotel1 expense demonstrates some of the flexibility. Note that BD stayed in the room two nights, while TO and FS stayed all three nights. By repeating their initials, each pays his share proportionate to the number of nights he stayed.

I'm sure you could get rid of that $1 rounding error, but it's not worth the effort. Of course the initials, expenses, and amounts have been changed to protect the guilty, but don't go thinking that those initials are random.