Archive for December, 2006

Personal finance post round-up

Personal finance

I’m fighting off a cold and have family coming into town today for a few days, so bear with me while I try to put together a useful post. In the meantime, check out the following highlights around the personal finance blogosphere:

  1. : Five Cent Nickel shows how to cut down on unwanted junkmail, phone calls, and solicitations. I plan on doing a couple of these later this week.
  2. : Jim reminds us to consider inflation when calculating not only salary projections but also in investment projections as well. Now, the question of what rate is the appropriate rate to use is still up for grabs.
  3. If you’ve ever wondered , JD over at GetRichSlowly explains. His is a very high quality blog, so the amount of time and effort he puts into it shouldn’t be surprising at all.
  4. Finally, last but definitely not least, George gives a thorough explanation of . Though everyone always talks about not overpaying for a stock, seldom do you ever actually get to see someone’s method for calculating a stock’s intrinsic value step-by-step, so here’s your chance!

Holy cow: musical scores of Mozart’s complete works now available for free online!

Current events, Tips for saving money

To commemorate the 250th anniversary of Mozart’s birth, the has created an online database of 24,000 of his works (in sheet music form), all available for download, for free. Obviously, it’s for personal use only, etc. and all the standard legalese applies.

The site and database, called the , is searchable in German and English, by KV, type of work, key of music, and editor. Here’s an .

Seriously, don’t you love it when the Internet facilitates sort of thing? The site’s been experiencing unbelievable traffic, so if you can’t connect immediately, try a bit later. I wasn’t able to access their servers at all until this afternoon. But, once I did, it took me no time at all to find the — a.k.a. Twinkle, Twinkle Little Star/The Alphabet Song, for example. Pretty simple search interface, and quick results. The sheet music is in JPEG format, so you have to save the images first before printing them (at least as far as I can tell). But it’s really a small inconvenience to pay given the incredible access they’re offering!

Using Excel’s conditional formatting: changing colors and fonts to highlight data

Corporate finance, Excel function tutorials, Excel spreadsheets (.xls)

I wanted to show one other thing helpful thing that Excel can do when you need to highlight data called . (Note: this tutorial is image heavy!)

Conditional formatting basically allows you to use colors, font styles, and borders to highlight cells that meet certain conditions. Unlike IF and other Excel functions, conditional formatting is found as a pull-down menu item in Excel rather than a function that you can type into a cell.

With the same salesman example I’ve been using in the previous tutorials on using the IF function, let’s look at how this would work. First, here’s the raw data, unformatted:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

Bonus 12%
Minimum amount 100000
Maximum amount 200000
Sales Bonus
Salesman A 87925 No bonus
Salesman B 100000 12000
Salesman C 145000 17400
Salesman D 200750 No bonus
Salesman E 178650 21438
Salesman F 99555 No bonus
Salesman G 147000 17640
Salesman H 213450 No bonus
Salesman I 122680 14721.6
Salesman J 92500 No bonus

Ok, now back to the tutorial.

Suppose you wanted to highlight all the sales that were above the minimum amount of $100K green, and any that were below the minimum amount red. How would you do that?

Read the rest of this entry »

Weekly T-bill update

T-bills

Not much new to report this week: T-bill rates stayed close to unchanged from last week’s auction. Below is the updated graph of Investment Rates for each T-bill (click to enlarge):


Again, the data for this chart of T-bill APRs is pulled each week from the , in case you’re curious.

How to use nested IF statements in Excel with AND, OR, NOT

Corporate finance, Excel function tutorials

Following up on last week’s , here’s one on how to use Excel’s IF statement when you have multiple conditions to meet.

The original question I received from a reader, which prompted this series, was about how to use the IF statement in Excel when you had two conditions that had to be met. For example, when sales fell between a minimum and maximum number. Let’s take a look at our example again. Note that I’ve added the maximum amount of $200K into cell B3:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

Bonus 12%
Minimum amount 100000
Maximum amount 200000
Sales Bonus
Salesman A 87925
Salesman B 100000
Salesman C 145000
Salesman D 200750
Salesman E 178650
Salesman F 99555
Salesman G 147000
Salesman H 213450
Salesman I 122680
Salesman J 92500

Ok, now back to the tutorial.

Now, let’s suppose sales have to be greater than or equal to $100K and less than $200K for a salesman to receive a 12% commission rather than just be greater than $100K, as in our introductory example. How would you write that in “Excel-speak”?

Read the rest of this entry »