Excel is a great tool for decision makers. Let’s take a look at some of the ways it can be used.
Learn from the past
Slice and dice your historical data to find patterns and sweet spots. This is a lot of what data mining is doing, and you can do it easily in Excel. For example, in the class Insights from Data using Excel, we applied PivotTables to actual historical bank data. We quickly found that entrepreneurs are more than twice as likely to default on their loans as the general population. These types of insights help decision makers work smarter and focus limited resources.
Assess the present
This is where the beancounters come in. Static charts and dashboards. This is what most Excel classes focus on, automating repetitive tasks and making pretty charts. There are plenty of places you can learn this. We don’t teach it.
Predict the future
Plan ahead. You can use simple arithmetic to write a short formula to look ahead to the next year, including expected in-flows and out-flows and interest. Now replicate that formula out to as many years as you like. For example, you could look at saving for retirement, saving for your baby’s future college education or spending during retirement. Now harness Excel’s interactivity to play “what-if” with your options. (How much earlier could I retire if I saved more each year?) You could even work the problem backwards with GoalSeek. (How much do I need to save each month to get college tuition in 18 years?)
Plan for risk
Predicting is hard because of the unknowns. Things like interest rates fluctuate randomly. Events happen or don’t happen randomly. Excel’s RAND function can model both types of uncertainty. See examples of possible outcomes. Then set up a Monte Carlo analysis using data tables to run through multiple outcomes and collect statistics. Find out, for example, what’s the probability that your retirement funds last to age 100.
Check back
I’ll be discussing these techniques in depth in the coming months.
