In my previous post, I showed you how to do sensitivity analysis with Excel’s data tables. However the functionality allow us to do sensitivity on 2 variables only. Today I’m following up with a way to overcome this limitation.
As an example, an investment might be sensitive to many variables [...] Continue Reading…
Excel has a built-in functionality for sensitivity analysis, hidden under the unclear name of “Data tables” (“tables” in Excel 97-2003).
Two options are available in using these “Data tables”:
Evaluate the impact of one variable on one or more resulting elements (with 2 possible layouts)
Evaluate the impact of two variable on [...] Continue Reading…
Create A Modular Design For Your Model
Don’t put everything on a single worksheet. Separate your model’s components on multiple worksheets, i.e. Revenues, COGS, Other Costs, Financing, Amortization etc…
Keep Your Sheet Names As Short As Possible
It will help keep formulas legible and you’ll see more sheet tabs at once which [...] Continue Reading…
You probably have at one time or another faced an Excel crash, while working on an important project with a tight schedule for example. Autosave is certainly a useful feature and document recovery tool has improved a great deal with time.
However, as always, it’s better to rely on [...] Continue Reading…
If you don’t know about data validation yet, I suggest you read the following document before getting familiar with this method.
http://www.contextures.com/xlDataVal01.html
Data validation is certainly one of the best tools to perform what-if analysis, along with sensitivity tables. There many types of sensitivity analysis that can be performed with data [...] Continue Reading…
Learning shortcuts is a great way to increase productivity. Over the years, I found that not only do they allowed me to work faster but they also helped reduce fatigue associated with repetitive tasks, thus reducing errors.
However, learning hundreds of shortcuts may seem like a burden. The best way [...] Continue Reading…
In my previous post, I showed you how to improve the clarity of your models by identifying inputs.
However, you might sometimes be having to work on someone else’s model and therefore having no idea of its structure. In such cases, you’ll find that the GO TO functionality can be [...] Continue Reading…
One of the most common mistake in model development is the lack of clear distinction between inputs and calculations. Even worse are inputs hidden within formulas.
Let’s look at the following simple example which computes the present value of two distinct scenarios.
Without looking at each cell, one can only guess [...] Continue Reading…
The Financial Modeler
Quick tip: Make your formulas legible
Embedded Excel functions is certainly a great feature, but it often makes formulas really long and hard to read.
You can easily make your formulas more legible by simply pressing ALT-ENTER while editing it to force a line feed. See for yourself the result of doing so.
Note that you need [...] Continue Reading…