If you could instantly visualize your financial future…

crystalball…what would you want to see? I recently asked this of an Excel workshop I was teaching. There was one stipulation—nothing speculative, like, “Will I get a raise next year?” or “Will the Dow hit 20,000?” They had just seen an example of saving for a child’s college education, showing progress year-by-year for each plan they tried.

Here are some of the questions they came up with.

  • If I invest $200,000 now at 6%, how much will I have in 10 years?
  • If I pay an extra $100 a month to principal, how soon will I pay off the loan?
  • How much am I spending and for what?
  • How can I save to make a down payment on a house in two years?
  • What profit percentage do I have to make to earn $3000 per month on the jewelry I make?
  • If I make $2000 a month at a 45% commission, how much will I have at year-end?
  • Can I afford to buy a more expensive house, even though I’m retiring in a year?

This was a little two-hour workshop, in which I taught them five basic skills:

  • Play “what-if” to see how your plans or assumptions change the answer
  • Get Excel to write simple formulas for you
  • Name input cells to make the formulas play nice
  • AutoFill formulas to build a sophisticated model from one or two simple formulas
  • Use Goal Seek to work the problem backwards

I asked the question because I wanted to see what other skills they would need to learn to help them answer the financial questions that interested them. I was surprised that the five skills were all they needed! In fact, all of these can all be done using only addition, subtraction, and multiplication.

Some of the questions were even simpler. Tracking your spending can be done just by entering your expenditures and their categories in a spreadsheet. You can then use sort and filter to see which categories are costing you the most. Yes, it takes discipline, but the techniques are easy. One simple formula can keep track of your balance.

In future posts, I’ll show you how to answer these questions.

Now, my question to you is, what would you ask? Do you have a financial question that you think can’t be answered using the simple techniques? Please leave your most challenging question as a comment.

What nine things should everyone know how to do with a spreadsheet?

Businessman holding a laptopMacworld recently ran an article, “Nine things everyone should know how to do with a spreadsheet,” by Rob Griffiths. Here’s the list:

  1. Format numbers
  2. Merge cells
  3. Use functions
  4. Distinguish between relative and absolute references
  5. Name cell references
  6. Extract data from ranges
  7. Perform logical tests
  8. Mix text and formula results
  9. Use conditional formatting

I thought about my own top nine before I read the article, or even looked at the list. My focus is on visualizing your financial future and exploring your options. Even so, I was surprised how different my list was. I’ve used everything on Griffith’s list and certainly found it all valuable, but here’s my list for those wishing to explore their financial future.

 

  1. Play what-if

This is barely a skill. Griffiths is assuming that you know how to do this. It’s just entering a number in a cell. The key is to see what that does to the answer. This hardly seems worth mentioning, except that so many people seem to think that all you do with Excel is to dump a bunch of numbers into it and produce a static chart or slide. Explore! See what drives the outcome. See how to get where you want to be.

  1. Write formulas

This is also implicit in Griffith’s list. Even if the word “formula” brings up scary images of high school algebra, everyone should be able to get Excel to write a formula for them by typing “=” and clicking on the cells you want to use in the calculation. It’s amazing what you can do without ever entering a cell reference (if you name your inputs). Learn how to build more sophisticated formulas and you’ll be unstoppable.

  1. Name cells

Griffiths has this one on his list as a means to keep formulas easier to read. That’s nice, but it’s on my list for a very different reason. If you name your inputs they automatically become absolute references and you don’t have to bother with all the dollar signs. This makes writing formulas to AutoFill a cinch. Also, I’m lazy, so I prefer to use Create Names, which uses the input labels. By the way, Griffiths looked at how to do everything on his list in Apple’s Numbers as well as Google’s Sheets. Numbers doesn’t support naming cells. Too bad. I’m a Mac user, but that’s a show-stopper for me.

  1. AutoFill formulas

The best part of writing formulas is being able to replicate them to fill an entire spreadsheet. Construct simple formulas that build on each other, AutoFill them down the page and get complex results. The secret to doing this is to name your inputs to make them play nice and then click on cells in the same of previous row to create a formula. Grab the corner and pull it down to fill in everything else. This is the technique that makes students’ jaws drop in my classes. Some big Excel reference books don’t even mention it.

  1. Use Goal Seek

Work the problem backwards. Your projected savings won’t get you to your goal? Tell Goal Seek to find the amount you need to save to get there.

  1. Use functions

This definitely belongs on any list, and it’s in both of ours. Whatever you want to do—statistics, finance, text handling, and more—Excel has a function for you. Griffiths mentions IF, HLOOKUP, and VLOOKUP. The IF function is especially powerful. It lets you make what happens on the spreadsheet respond to whatever is happening anywhere else on the spreadsheet. If you’re doing loan payments, check out PMT, IPMT, and PPMT.

  1. Create charts

You don’t need to know all the kooky chart options, but sometimes a simple chart helps you see what’s going on, year-by-year.

  1. Sort and filter

In most of the problems I deal with, you don’t have a lot of data (eg. How can I save enough for an $80,000 college education in 18 years?). But sometimes you’re keeping track of a lot of information and you need to make sense of it. Make a table of it, and then use the tabs at the top of each column to reorder or focus it all, to figure out what you have and what matters.

  1. Build data tables

This is a somewhat advanced, somewhat confusing technique, but it’s great for sensitivity analysis. What really matters to the answer? You can also use it to turn any spreadsheet into a Monte Carlo model for risk assessment.

 

 

This is clearly a very subjective list. What are your nine favorite Excel techniques?