gavinshearer.com

« September 2008 | Main | November 2008 »

October 24, 2008

Under-Appreciated Excel Feature Of The Week: Goal Seek

This is cross-posted from the Mac Mojo blog.

Last month's post about Data Tables generated a surprising amount of e-mail from readers. Lots of people, it seems, are interested in learning how to get more out of Excel.

In that spirit, I thought I'd take some time to discuss and showcase a companion feature to Data Tables, called Goal Seek.
As I wrote about Data Tables:

Put plainly, Data Tables let you do "what if..." analyses in Excel. They let you see how the results of a formula change as its underlying variables change. Data Tables let you see an entire range of solutions, rather than just one single answer.

In that post, we explored Data Tables through the frame of taking out a large loan. The feature let us see how our monthly payment would vary if we changed the length of the loan or its interest rate. Our end product was a matrix of numbers that lets us see, clearly, how rates and terms affected our monthly payment.

However, there are a number of times when we're not looking for a table of results, but instead are concerned with optimizing around a single variable. For instance, if I go out to buy a car, I have a certain amount of money - say, $350 - that I can afford to put toward a payment each month. In cases like these, I'd much prefer not to look at a matrix - instead, I'd like to just know how much I can get for my money.

This is where you use Goal Seek.

Like Data Tables, Goal Seek lets you do "what if..." analysis, but it will solve for a specific, single answer. Here, I'm really only interested in one question: given that I have $350 a month to spend, how much car can I afford?

I'll walk through an example to show how it's done.

We first need to set up our spreadsheet. Just as we did with Data Tables, we will make a few assumptions about our loan. (If you want to grab the finished spreadsheet from the Data Tables exercise (download here), it will save you a bit of time.)

Just to plug in some numbers, let's assume that we're borrowing $25,000 for 4 years at 7% interest.  If we set up our spreadsheet as follows and use the Excel PMT function (=PMT(B4/12,B3*12,B2)), we will get a monthly payment of ... $598.66.

A payment of $598.66 is quite a bit more than the $350 I have to spend every month. So now it's time to figure out how much car I can actually afford.

Go to the "Tools" menu and select "Goal Seek..." (it's about 2/3 of the way down the list). The Goal seek dialog will appear:

There are three variables to worry about, here: set cell, to value, and by changing cell. The nice thing is that they work as a sentence:

In other words: you're asking Excel to change the value of cell Z until cell X is equal to some value, Y.

With that in mind, filling out Goal Seek is pretty easy. On our spreadsheet, we want to set cell B5 ("Monthly Payment") equal to the value of our monthly payment ($350). And we want Excel to do that by changing cell B2 ("Amount To Be Borrowed").

Setting Goal Seek to these values, we get:

It's worth noting that "to value" is set to minus 350 (-$350). This is because a monthly payment is a cash outflow - it's money you're giving away each month. This is how the Excel PMT function thinks about monthly payments, and, since we're relying on the PMT function to do all our heavy lifting in the math department, we need to make sure we're speaking a language the function understands. (It's a quirk, but an important one.)

Click OK, and Excel will crunch the numbers. Goal Seek will come back and let you know if it found a solution:

Click OK again, and this dialog will vanish. You'll find yourself back out at your workbook, which should now contain the answer to our question:

Looks like I can afford to borrow $14,616 for my new car  - which means I'm looking less at a new Mini Cooper, and more at a shiny Toyota Yaris. Pretty cool, huh?

So that's Goal Seek - yet another under-appreciated Excel feature. As you might imagine, it's a pretty powerful (and profoundly useful) tool. Personally, I use it all the time for situations like this (which seem to crop up in business pretty regularly).

If you'd like to see my spreadsheet, I've attached it to this post - just click here.

Best of luck!

Posted by Gavin Shearer. Last updated October 24, 2008 10:24 AM.
Posted to Apple | MSFT. Permalink for this entry.

October 22, 2008

Space Mountain 2 Artwork & Video

Disney and More is up with an artwork-and-video-heavy post about Space Mountain at Disneyland Paris - its history, design, construction and evolution. They even sport some cool - and, as memory serves, prety darn accurate - simulated ride-through videos of the coaster in action.

(If you're interested in Space Mountain 2 (I confess to being a bit obsessed), be sure to spend the 45 minutes checking out the BBC "Shoot For The Moon" documentary.)

Posted by Gavin Shearer. Last updated October 22, 2008 10:00 AM.
Posted to Disney. Permalink for this entry.

October 18, 2008

Goofy's Sky School

With the Preview Center opening Monday, it's turned into a big news week for Disney's California Adventure. This morning's LA Times Travel Blog has a blurb (and artwork) about the coming transformation of "Mulholland Madness" into "Goofy's Sky School":

Gone is the giant foldout map of Southern California that always seemed like a futile attempt to hide the fact that the coaster was nothing more than a wild-mouse ride found at any freeway entertainment center. Gone too is the Magic Mountain-esque, slapdash, Caltrans maintenance-yard theme involving little more than a plastering of roadwork signs.

The new back story follows Goofy’s attempts to teach a group of novice pilots how to fly. Naturally, things go awry. The reworked ride is expected to open in 2010.

"Mulholland" is one of the "tells" about DCA origins - an off-the-shelf, built on the cheap ride with minimal themeing. The fact that they're tackling this eyesore head-on - and with a bit more creativity than I'd have expected - is encouraging.

Posted by Gavin Shearer. Last updated October 18, 2008 7:56 AM.
Posted to Disney. Permalink for this entry.

"McCain Loses Hastily Convened Fourth Presidential Debate With Lifesize Cardboard Obama"

I know this is everywhere right now, but I think it's pretty damn funny:

In a Manhattan conference room this afternoon, Senator John McCain (R.-Ariz.) conducted and lost a hastily arranged rematch of last night's Presidential debate, this time to a cardboard cutout of the Democratic Presidential candidate, Senator Barack Obama (D.-Ill.).

..."Cardboard Obama was a more formidable opponent than expected," stated a surprised David Gregory, also on MSNBC. "Like Barack Obama, Cardboard Obama was unflappable: while neither real Obama nor Cardboard Obama returned any of John McCain's withering criticism, by the half hour mark, both had seemed to get under McCain's skin."

Enjoy.

Posted by Gavin Shearer. Last updated October 18, 2008 7:51 AM.
Posted to Politics. Permalink for this entry.

I May Love Roller Coasters, But...

I clearly don't love them as much as Bob Urmanic:

The Elyria man, who turns 71 in November, apparently has that in abundance. On Sunday, he took his 1,000th ride this year on Cedar Point’s 310-foot, 93-mph Millennium Force, a mammoth ride consistently voted among the best thrill rides in the world by ride and theme park polls and publications.

Look, when I was about 12, I rode the "Galaxi" at the Seattle Center something like 50 times in one night - an experience I credit for hooking me on coasters forever.

But 84 times in one day on Millennium Force? I'm not sure I could take that. And the dude is 71(!).

Well done, sir. Well done indeed.

Posted by Gavin Shearer. Last updated October 18, 2008 7:41 AM.
Posted to Misc. Permalink for this entry.

October 16, 2008

Meet Nate Silver, Statistics God

As anyone close to me will tell you (read: Elaine), I've been consumed with the Presidential election. Part of this consumption involves checking FiveThirtyEight.com on an regular (read: hourly) basis. Run by statistics god Nate Silver (you've probably seen him on Countdown or Colbert in the last week or so), FiveThirtyEight weights various state and national polls, applies Monte Carlo simulation, and then uses the results to forecast how they think the election will turn out.

(At the moment, he's giving Obama a 95.1% chance to win the election, and is forecasting 353.9 Electoral Votes for 'Bama, and 184.1 for McCain.)

New York Magazine has an interview/profile of Silver, and it's a must-read:

Silver’s site now gets about 600,000 visits daily. And as more and more people started wondering who he was, in May, Silver decided to unmask himself. To most people, the fact that Poblano turned out to be a guy named Nate Silver meant nothing. But to anyone who follows baseball seriously, this was like finding out that a guy anonymously running a high-fashion Website turned out to be Howard Cosell. At his day job, Silver works for Baseball Prospectus, a loosely organized think tank that, in the last ten years, has revolutionized the interpretation of baseball stats. Furthermore, Silver himself invented a system called PECOTA, an algorithm for predicting future performance by baseball players and teams. (It stands for “player empirical comparison and optimization test algorithm,” but is named, with a wink, after the mediocre Kansas City Royals infielder Bill Pecota.) Baseball Prospectus has a reputation in sports-media circles for being unfailingly rigorous, occasionally arrogant, and almost always correct.

95.1%, huh? Fingers crossed.

Posted by Gavin Shearer. Last updated October 16, 2008 11:59 AM.
Posted to Politics. Permalink for this entry.

More On The DCA Preview Center

The Orange County Register is up with an in-depth story about the new Preview Center at Disney's California Adventure. Lots of photos, lots of copy; here's a bit about what's coming to Paradise Pier:

The Mickey Wheel will replace the park’s current attraction, The Sun Wheel. The sun face will be replaced by a vintage “pie-cut eyed” Mickey face. Different vintage Disney characters will be featured on each gondola.

The Boardwalk games will be rethemed with characters from classic Disney films. The games include “Casey at the Bat,” based on a segment of “Make Mine Music;” “Dumbo Bucket Brigade,” from the movie “Dumbo;” “Goofy About Fishin’,” based on the Goofy cartoon “How to Fish;” and “Bullseye Stallion Stampede,” from the Pixar Film “Toy Story.”

The goal of these attractions as well as the other new additions to Disney’s California Adventure is to “bring more Disney richness through more characters,” Weis said.

The center itself opens to the public on Monday, the 20th.

Posted by Gavin Shearer. Last updated October 16, 2008 9:06 AM.
Posted to Disney. Permalink for this entry.

October 11, 2008

"The New Age Of Frugality"

BusinessWeek's cover story this week is called "The New Age Of Frugality", and it's all about the changing habits of American consumers (podcast here):

Ingram and Behre are harbingers of a dawning Age of Frugality. People who overconsumed during the past decade are now rejecting extravagant lifestyles. They're spending less, and more wisely. Some are getting their finances in order. Others are fearful of losing their jobs, shocked by investment losses, or hunkering down amid the general uncertainty.

I have to say, this describes everyone I'm talking to right now. Elaine and I have been making some changes in our lifestyles (that Disneyland trip will likely be our last for some time), and I've been interested to hear how many of my peeps are doing the same. Rather than dinners out, friends are suggesting dinners in (or potlucks); people are riding the bus, skipping the morning Starbucks, and so on.

Suddenly, frugality is cool.

My friend Brian and I got in to a long discussion about what these changes, if they broaden and stick, will do to the larger economy. Our consensus was that mass-market, low-cost necessities providers like Wal-Mart and Costco are going to be fine, as are firms like Apple that make good-quality, differentiated products. However, a lot of other firms - American car companies, for example - are in for a rough freakin' ride.

The next few years are going to be really, really strange, I think.

What are y'all doing?

Posted by Gavin Shearer. Last updated October 11, 2008 4:27 PM.
Posted to Cool. Permalink for this entry.

October 10, 2008

Sketches Of DCA's Future

The Orange County Register has a great story (and, more importantly, an embedded slideshow) about the re-imagineering of Disney's California Adventure.

(Personally, I love what they're doing with the Orange Stinger.)

Posted by Gavin Shearer. Last updated October 10, 2008 6:17 PM.
Posted to Disney. Permalink for this entry.

October 7, 2008

DCA's Preview Center Opens Oct. 20

Al Lutz is up with a column this morning about the changes coming to California Adventure. The new DCA "Preview Center" opens to the public on October 20, and here's what we can expect:

On the upper level that used to be the theater seating, you will immediately be drawn to a detailed model under glass of the completed makeover of Paradise Pier. The cheap and modern minimalist decor approved by Paul Pressler and Michael Eisner back in the late 1990's is gone, and the ultra themed Victorian look of Midway Mania has swept from one end of the pier to the other. Around the large Pier model are some smaller models of architectural mock-ups of the remade midway games, with explanations of how Imagineers use these types of models to fine tune the scale and overall look of a facility during the design phase.

The back wall of this space is dedicated to some artwork and concept sketches of the new Midway Mania ride that opened in June. Although this attraction was budgeted and approved back in 2005 by Matt Ouimet, the look and feel of the facility is so radically different from the cheap DCA appearance that preceded it that WDI is lumping this ride in with the "new" DCA offerings coming from the billion dollar makeover budget.

I'm a little bummed that Elaine and I couldn't see this in person last month, but it's still damn exciting.

Posted by Gavin Shearer. Last updated October 7, 2008 6:25 AM.
Posted to Disney. Permalink for this entry.

October 5, 2008

"Shoot For The Moon"

I've been spending part of my weekend savoring a fantastic, 1995 BBC documentary called "Shoot For The Moon", about the creation of "Space Mountain: Mission 2" at Disneyland Paris. If you're interested in Disney park attractions, roller coasters, or Imagineering in general, it's a must-see.

It runs about an hour, and is available in five parts on YouTube: one, two, three, four, five.

Posted by Gavin Shearer. Last updated October 5, 2008 8:54 AM.
Posted to Disney | Entertainment. Permalink for this entry.