Microsoft Excel is an amazingly powerful tool. People across the world, from housewives to small business owners to engineers to students use this data analysis software on a daily basis to store, calculate and manage their data.
While many of the things that Excel is good at can be done with pen, paper, and a good calculator, most of these tasks would take much, much longer when done the old fashioned way versus when using a good spreadsheet application like MS Excel.
In general, Excel is used to:
a. store data; in other words, it is used as a database
b. perform simple calculations taking into account vast amounts of data points in a way that links them by arranging them into cells that refer to each other
c. carry out statistical functions that require complex formulas too tedious to work out by hand
d. execute what-if scenarios
In fact, one of the most powerful things you can do in Excel that cannot be easily done with pen and paper is the what-if scenario. Simply put, a what-if scenario allows you to create a simple or complex numerical model and then plug in different starting (or independent) variables to find out their effect on some ending (or dependent) variable.
To construct a good what-if scenario, at a minimum you need the following:
a. something you want to predict or know the outcome of. This is called a dependent variable and it must be expressible in numerical form as a variable.
b. one or more types of data points whose changing values have a direct effect upon the thing you are trying to predict. These are called independent variables.
c. a known or supposed causal relationship between the independent and dependent variables. In Excel, this would take the form of a mathematical formula or set of formulas.
While the number and type of what-if scenarios that can be constructed in Excel is virtually limitless, here is an overview of three of the most common ways to go about it:
1. Construct a spreadsheet that links multiple cells together to create a single scenario:
You can, of course, set up a single scenario in Excel and see what it predicts given certain values entered for the independent variables. In this case, in order to view different outcomes based upon different sets of independent variables, you would have to keep track of the independent-dependent variable sets in a log file. For example, you might record: “When the interest rate is 6.7%, my total annual interest payments in Year 1 would be $12,434. But, if the interest rate were to drop to 6.2%, my total payments in Year 1 would be….”
2. Reproduce multiple copies of a single scenario and enter different sets of values:
Once you have constructed a single scenario, it might be beneficial for you to copy/paste that scenario multiple times in the same spreadsheet. That way, you can plug in different sets of independent variables into each instance of the model and then compare their respective outcomes visually. The only drawback with this approach is that if you decide to tweak the model, you will have to tweak all iterations (or copies) of it.
3. Use Goal Seek:
Excel has a wonderful feature called Goal Seek (see the Help section). This is useful when you want to answer the question: “If I want (a given) value as an outcome, what needs to be the value of the independent variable to make that the case?” The only limitation of using goal seek is that it will only give you the value of one independent variable at a time. In other words, if both “interest rate” and “loan term” variables are used to calculate the dependent variable of “monthly payment amount,” goal seek will tell you one or the other but not both (at the same time).
What-if scenarios are one of the most powerful features of MS Excel because they allow you to tinker with different future scenarios. They allow you to make better decisions by helping you to manage risk, predict future values of some important variable, or find your strongest points of leverage in a given business, scientific, or engineering situation.
John Abrams a Microsoft Office expert has been working in the technology industry for the last 5 years. As a technical expert, he has written technical blogs, white papers, and reviews for many websites such as
Office.com/setup
