Power Formula Auditing: The Power Tool for every Excel User

Figure 43. Yearly Interest Calculation

---

Suppose that instead of using the proper formula, a value of 10 is entered for
the interest earned in the first year. In the next figure, the top half shows the
table properly constructed while the bottom half shows the wrongly done table.
The missing arrow on the first row is quite obvious. (This figure is drawn by
selecting the table and clicking on “Multi-Precedents)
Figure 44. Precedent Patterns for Value versus Formula
More advance templates include specially hidden formulas or macros (small
programs in Excel VBA) that auto-check the answers.
An example of hidden checking formulas is from here: Quick Correct: 
A
method to automatically evaluate student work in MS Excel spreadsheets
.
An example of macro checks is from here: 
Tale of Two Cities (and two
hurricanes): New Orleans
. The spreadsheet provided to students has built-in
macros that check for correctness. In the following picture, "click to check
work" activates a macro to check the student work. Developing the macro is
not easy. It requires the teacher to know programming with VBA for Excel. Not
many spreadsheet teachers know VBA so well.
However, asking students to fill in Excel templates is like asking students to
fill in the blanks in an essay. It does not allow for creativity.
What if we allow students to design their spreadsheet answers without the

---

strict constraint and guidance of a template? That will require much effort from
the teacher to check through each differently designed solution.
That's where Power Formula Auditing comes in. Power Formula Auditing
allows us to easily have an overview of the spreadsheet as well as look into
connection details. This is done with a few button clicks and do not require
any VBA knowledge. Power Formula Auditing can be very helpful for
spreadsheet teachers.

---

11. Influence Charts help What-If Analysis
An influence chart is very useful if we want to do what-if analyses.
In general, a what-if analysis is when we change the value of a certain cell and
see what happens to another cell. Suppose we have a profit model, and we
want to see how a price change will increase our profit. For example, if price
is set at $11, what will be the profit?
Alternatively, using goal-seeking, which is also under the what-if analysis
menu, allows us to specify a value for a goal cell, and check what value is
needed for a precedent cell. For example, if we want the profit to be $1
million, what should be the price?
In a complex model, it may not be clear what cells can be changed or set with
goals for a what-if analysis. The influence chart plays an important role as it
will show us the input cells where we can change, or the output cells where
we can set a goal.
More information about what-if analysis can be found at Microsoft support
(
Introduction to What-If Analysis
). (URL: https://support.office.com/en-
us/article/Introduction-to-What-If-Analysis-22bffa5f-e891-4acc-bf7a-
e4645c446fb4)
Excel has another function similar to what-if analysis. This is the “Watch
Window” button under Formulas/Formula Auditing. “Watch Window” allows
us to select various cells to “watch” while we are editing the spreadsheet. The
watch window display the cell location, formula and value.
An influence chart will be very useful. It will help us to trace and watch the
dependent cells.

---

12. Conclusion
Influence charts are very important for looking at Excel models. Influence
charts, in providing an overview, helps us to understand Excel models.
It is easy and quick to automatically generate an influence charts from an Excel
model. Since the influence chart is generated from the model, errors in the
model will be reflected in the influence chart.
By looking at unusual or unexpected patterns in the influence charts, we can
find potential errors in the Excel models.
A word of caution is needed though. An influence chart can provide many
helpful ways to understand and check spreadsheet models. However, expert
domain knowledge is still needed for a thorough understanding of complex
spreadsheet models. Engineering knowledge is needed to understand
engineering spreadsheet models, and financial knowledge is needed to
understand financial spreadsheet models.

---

References
Ayalew, Yirsaw. "A visualization-based approach for improving spreadsheet
quality."