Excel's Magic 8 Ball: Using What-If Data Tables to Shake Up Your Financial Predictions
Part 1: Excel's What-If Analysis Data Table is Excel's Magic 8 Ball, making financial predictions a child's play.
This is the first in a 4 part series on Excel’s What-If analysis tools.
Want to get the most out of this newsletter? Download the sample data so that you can follow along!
Today I am going to share some insights that will change how you use Excel, particularly when it comes to scenario analysis and planning. Have you ever wished for a way to simplify your complicated Excel formulas or projections? If so, let's dive right in and unlock the potential of Excel’s What-If Analysis Data Table.
Single point analysis
Lets say your company is looking to invest into some major equipment. You're sitting there, crunching numbers, wondering how much this equipment is going to cost you. The equipment itself is around $500,000. You're thinking about the interest rate, the years to finance, and all these variables start to become overwhelming. Excel gives us a very simple formula to put these variables into a single table and pump out an answer. We will use the PMT function to figure out the Monthly cost:
Step 1: Create the following Table
Step 2: Enter your assumptions on equipment financing costs
Step 3: Enter the formulas in the cells as follows:
You can now see what this equipment will cost you. You can change any of the inputs in order to see how they impact the financing costs.
Looking at variable scenarios
What if you want to look at a scenario where one of those inputs might change. For example, equipment financing is often at a variable interest rate. Given how much this number can fluctuate it would be good to see all scenarios associated with this change. It could be tedious and time-consuming to manually adjust your calculation each time. This is where Excel's What-If Analysis Data Table shines. It lets you simulate different values for your variables and see how they affect your outcomes.
The process is simple. You build your model, create a data table with various interest rates, and Excel does the rest.
Step 1: Create a data table next to the one above. It should show various interest rates in the column with the financing cost outputs in one row. This is what it will look like:
Step 2: Highlight the the table. In this case E3:I12
Step 3: Go to Data > Forecast > What-if Analysis > Data Table
Step 4: Leave the row section blank. For the column section click on cell B7 (where the interest rate is input). Hit OK.
Boom! Excel has created a Data Table that allows you to see how your Monthly costs could vary over time. This will allow you to plan accordingly.
Two variable Scenarios
But what if you want to consider more than one variable? Say, you want to know how different interest rates and different loan terms would impact your monthly cost specifically. This is where the two-variable data table comes in. You can set up your table, input the formula, and voilà! Excel presents you with a range of outcomes based on your inputs.
Step 1: Create a new data table. Enter interest variations in one column. This time enter different loan terms in the rows.
Step 2: Enter the formula =B11 into cell E3. This is the value we want to solve for across two variables.
Step 3: Highlight the entire table. In this case E3:M12
Step 4: Go to Data > Forecast > What-if Analysis > Data Table
Step 5: For the row section select cell B6 (where the loan term is input). For the column section click on cell B7 (where the interest rate is input). Hit OK
Excel has now created a 2 variable Data Table that provides you how your monthly cost will change over a variation of interest rates and loan terms.
Exploring Excel’s What-If Analysis Data Table can be a real game-changer, especially when you're dealing with projections and scenario analyses. It's like having a personal assistant that helps you see the future. Be sure to try them out as soon as possible. I’ve made this easy for you - be sure to download the sample data sheet at the top of this newsletter. That way you can learn and practice at the same time!
One more thing before I wrap up. Keep in mind that data tables can slow down your spreadsheet because they recalculate every time you make changes. To speed things up, you can change the calculation option to "Automatic Except for Data Tables." This way, your data table won’t recalculate until you press the F9 key. A neat little trick, right?
I hope this newsletter has given you a fresh perspective on how to use Excel more effectively. Remember, it's not about working harder; it's about working smarter. And Excel’s What-If Analysis Data Table is a tool that helps you do just that. Be on the lookout for next weeks newsletter where we will cover another What-if tool - Scenario Manager.
I hope you enjoyed this newsletter. There are a few other ways I can help you master Excel:
Do you have any burning questions about Excel that you want answered? Send your question to hello@learnexcel.live. I will address it in a future newsletter.
Do you or your organization need a little spreadsheet help? Book a 1 on 1 with me here.
Want to learn more Excel Shortcuts? I have created something special (and free) for you: The Best Damn Excel Shortcut Sheet
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you like these tips & tricks please share this newsletter with a friend or two...