|
Figure 4. Expenses Influence Chart
The Excel model is shown below.
Figure 5. Expenses Excel Model
The next figure shows the connections generated in Excel.
Figure 6. Excel generated Expenses Influence Model
Notice that Excel does not show the separate arrows that we have drawn in the
original influence chart. When the formula uses a range of cells (D2:D6), a box
over the range of cells is shown with a single arrow.
2. Uses of Influence Charts
If people draw an influence diagram before beginning to construct
their spreadsheet then they will build a better model
Australasian J. of Information Systems. 2011.
Influence charts are well recognized to be good for designing a spreadsheet
model. What is not so well recognized is that an automatically generated
influence chart is also very good for understanding a spreadsheet model, and
even for detecting errors.
In general, an influence chart is very good at showing the overall connections
in an Excel model. In addition to seeing an overview of the Excel model, users
can zoom in to look at details on any connection.
The Excel generated influence chart has another very important use. Potential
errors can be easily seen.
In a famous academic paper about GDP growth, a few rows of data were left
out of the average calculation. More information about this case can be found
here:
The spreadsheet error in Reinhart and Rogoff’s famous paper on debt
sustainability.
(This
URL
is
http://blogs.marketwatch.com/thetell/2013/04/16/the-spreadsheet-error-in-
reinhart-and-rogoffs-famous-paper-on-debt-sustainability/)
The spreadsheet used in the academic paper is shown below. It is not easy to
tell if the averages in the last row are correct.
Figure 7. An Excel Model about GDP Growth
The next figure shows the model with the automatically generated influence
chart.
It is now quite easy to see that the last few rows of data were left out of the
average calculation. The blue boxes (used for sum and average functions
which state a range of cells) do not cover the last few row.
It is obvious that row 25 to row 29 are not included.
Figure 8. Trace Precedents highlights Errors in GDP Growth Model
When all the rows are correctly included in the average, the influence chart
should appear as in the next figure.
This example shows how an influence chart can give clear clues about
possible errors.
In the later chapters, we will enhance the influence chart with additional
software that can be added to Excel (add-ins software). The enhanced
influence charts are easier to use, have more features, and are better at
indicating potential errors.
Figure 9. Trace Precedents for Corrected GDP Growth Model
3. An Under-utilized but Powerful Tool
While an automatically generated influence chart is very powerful for users,
most users do not know about this.
According to a study about MBA users (Baker, K. R., Foster-Johnson, L.,
Lawson, B., & Powell, S. G. (2006). A survey of MBA spreadsheet users.
Retrieved April, 30, 2010.), users do not make much use of technology in
checking their spreadsheets.
Only 24% make use of “formula auditing” toolbar that is used for generating
influence charts.
There are many new and easy to use add-in software programs that can help
users check their spreadsheet. Some are included in Excel (formula auditing),
and others may cost a few thousand dollars.
This book will explain how to use the formula auditing functions that are
originally in Excel.
It will show users how to get the advance software (Power Formula Auditing)
to easily generate informative influence charts, and illustrate the results from
this software, compared with the results from the basic auditing functions
provided in Excel.
4. Precedents and Dependents
These functions are essential for any spreadsheet user. If you want to
understand how your spreadsheet formulas are connecting all the cells, tracing
precedents and dependents is the best method in Excel.
A formula in a cell (X) may refer to another cell (Y). Then, Y is called the
precedent of X, and X is called the dependent of Y.
Excel provides an easy way to identify all precedents of a cell, or all
dependents of a cell.
Simply select a cell, and click the "Trace Precedents" or "Trace Dependents"
button. These buttons are organized under the “FORMULAS” menu in Excel.
The set of formula tracing buttons is called “formula auditing”, or “auditing
toolbar”.
Do'stlaringiz bilan baham: | 
