Power Formula Auditing: The Power Tool for every Excel User

“More often than not just one person in a company has the
knowledge of how the financial spreadsheet models are
constructed. Other people are unable to understand and therefore
check the analysis. The potential for errors is massive.”
The Telegraph. 2015.
This book focuses on one main topic: the influence chart. An influence chart is
a very powerful tool. An influence chart provides an overview of your Excel
model. It shows all the items (the values and formulas) and how these are
connected to one another.
An influence chart is so important that experts recommend that we draw an
influence chart before we enter the values and formulas to develop an Excel
model.
As we all know, most people don’t do that. We simply enter the values and
formulas and design the model as we type.
Furthermore, the connections among items are not obvious in an Excel model.
In fact, the connections are very well hidden.
Even when we are handed an influence chart for an Excel model, we don’t
know whether the Excel model really follows the influence chart.
This book shows how we can easily and quickly draw an influence chart from
an Excel model, with the help of Power Formula Auditing. The automatically
generated influence chart shows how the Excel model is 
really
organized.
An influence chart has many important uses. It provides an overview of the

---

Excel model. This enables us to understand the model at a high level, to know
how various components are connected.
A good influence chart will also give visual clues of the components in an
Excel model.
In addition, an influence chart is very useful for identifying errors. Some errors
are very obvious once the influence chart is drawn. These will be illustrated in
the later chapters.
Excel has some basic, and tedious, functions to draw an influence chart from
an Excel model. This book illustrates how to use these functions. In addition,
this book will illustrate a special-purpose add-in software that gives enhanced
influence charts, and with less effort.
Let us consider a few simple examples of influence charts.

---

Example 1: Interest Calculation
This is a very simple example for interest calculation. It calculates the interest
earned after one year, given the deposit amount and the interest rate.
Figure 1. Interest Calculation Influence Chart
Note that the influence chart shows the connections, but usually not the actual
values.
This next figure shows how the Excel model looks like. The model includes
the actual values, e.g., 2% for the interest rate. However, the connections are
not show visually. The connections are “hidden” within the formula for interest
earned.
Looking at the formula for cell D2 (=C4*C5/100), we know that cell D2 is
linked to cell C4 and C5. (A cell is a single rectangle in a spreadsheet table.)
Figure 2. Interest Calculation Excel Model

---

For a single formula and a few cells, finding the connections is not difficult.
But when there are many formulas and cells, finding the connections is very
tedious.
However, with Excel, we have the ability to generate a good influence chart
with only a few clicks. The next figure shows the result. Note that the
connections are now shown visually with arrows.
Figure 3. Excel generated Interest Calculation Influence Model

---

Example 2: A summation of expenses.
This model is about monthly expenses. The total expense is the sum of rent,
food, entertainment, clothing and transport expenses. The influence chart is
shown below.