Main Power Formula Auditing The Power Tool For Every Excel User
You may be interested in Powered by Rec2Me
Most frequent terms
Power Formula Auditing: The Power Tool for every Excel User HC Chan Copyright © 2017 HC Chan All rights reserved. ISBN: Preface A journey of a thousand miles begins with a single step. If you have to step your way through a thousand miles, will you do it? What if you have a car or a plane for your journey of a thousand miles? Excel has this very useful ability (called formula auditing) to trace precedents and dependents, which draws arrows to connect cells. These arrows are very useful for understanding how cells use values from other cells. Cell connections have other powerful uses. For example, cell connections for an entire spreadsheet can show us how the spreadsheet is structured. Often, cell connection arrows can reveal potential errors. Excel’s formula auditing is, however, very tedious to use as it is limited to tracing one cell at a time, and one level at a time. You have to go step by step. This unique spreadsheet book shows how you can easily enhanced the tracing ability of Excel, by downloading Power Formula Auditing (Excel 2013). It is as if you can now zoom over a thousand miles, with a few clicks on the spreadsheet. This book goes beyond simple tracing of precedents and dependents. It shows you how you can quickly and easily gain an overview of your spreadsheet model, with just a few clicks of Power Formula Audit. The automatically generated cell connections allows easy understanding and error detection, as illustrated in the book. The overview for a spreadsheet is like a map of the mountains and rivers and with all the hiking trails. A normal spreadsheet is like a map without showing any hiking trails. Which map do you want to use for hiking? This book is meant for all types of Excel users. It is meant for both novice and expert users. Whether you create spreadsheets or you work with other people’s spreadsheets, this book will be very useful for you. This book is especially useful for people who check other people’s spreadsheets. If you are a spreadsheet teacher, a spreadsheet auditor ; or a manager who gets spreadsheet reports, this book is a must read. Even if you read only your own spreadsheets, this book gives you better and more efficient methods to look through your own spreadsheets. With Power Formula Auditing, you gain a new powerful perspective about spreadsheet models. Contents 1. Introduction to Influence Charts Example 1: Interest Calculation Example 2: A summation of expenses. 2. Uses of Influence Charts 3. An Under-utilized but Powerful Tool 4. Precedents and Dependents Trace precedents Trace dependents A quick display of immediate precedents Box arrow for a range of precedent cells 5. Wrong or Missing Connections: A Common Error Example 1: A payroll model Example 2: An economics model 6. An Enhanced Influence Chart: Power Formula Auditing 7. Tracing Precedents / Dependents for Multiple Cells 8. Examples of Overviews Example 1: A simplified balance sheet Example 2: A company’s consolidated balance sheet Example 3: A company’s statement of income Example 4: A Personal Budget Model 9. Special Cases for Trace Precedents and Trace Dependents Offset formula in Excel Vlookup & Hlookup formulas Formulas that cross worksheets 10. A Special Chapter for Spreadsheet Teachers 11. Influence Charts help What-If Analysis 12. Conclusion References Appendix A. How to add Power Formula Auditing to Excel? Appendix B. How to uninstall PowerFormulaAudit from Excel? Appendix C. PowerFormulaAudit Menu 1. Introduction to Influence Charts “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. 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-inreinhart-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”. Figure 10. Precedents / Dependents Tracing Buttons Trace precedents A simple Excel model is shown in the next figure. Figure 11. A Simple Excel Model Excel will draw the precedent arrows after a few clicks. Select (click on) cell D10. Then click “trace precedents” a few times. Each click traces the precedents one level further. Figure 12. A Simple Excel Model with Precedents Traced Note that the beginning of an arrow has a big dot. This is important to bear in mind when we look at many overlapping arrows. The end of an arrow has an arrow-head. An arrow goes from a precedent cell to a dependent cell. Tracing precedents is particularly useful when we want to know what other cells (values and formulas) affect the selected cell. For example, if cell D6 is the profit of a company, the precedents show the other items that have an effect on the profit value. Looking at arrow connections is much easier than looking at formulas in each cell, which can also be done by showing all formulas at once (“Show Formulas” button). Figure 13. A Simple Excel Model with Formulas Shown Trace dependents The “Trace Dependents” button produces similar results. For example, in the next figure, we select cell A4. Clicking “Trace Dependents” will find the cells dependent on A4, i.e., the cells with formulas that refer to A4. The first click will show the immediate dependents (in this case, only one arrow from A4 to C3). The second click will show the dependents of the immediate dependents (in this case, another arrow from C3 to D6). The “Trace Dependents” button is particularly useful when we want to know how the Excel model will be affected when we change the values in a cell. Tracing dependents show us all the cells that will be affected. Figure 14. An Example for Tracing Dependents A quick display of immediate precedents If we are interested in only the immediate precedents of a cell (i.e., we don’t want to trace the precedents of the precedents), there is a super quick method to visually show them. Simply double-click on a cell, its immediate precedents will be highlighted with colors that match their cell reference in the formula. An example is given below. By double-clicking on cell C7, the precedents are shown. Cell A4 is shaded blue to match the blue color of “A4” in the formula. Similarly, cell B4 is shaded red to match the red color of “B4” in the formula. Unfortunately, there is no equivalent method to highlight dependents. This technique is very fast and simple for tracing precedents of a single cell. For some complex tracing, we need to go back to the “Trace Precedents” and “Trace Dependents” buttons. Figure 15. Formula Highlighting Box arrow for a range of precedent cells Where cells are stated one by one in a formula, the arrows are shown separately. As we have seen earlier, where a formula states a range of cells, e.g., A1:A5, the arrows are organized into one arrow. An illustration is shown below. Figure 16. Precedent Arrows for Individually Specified Cells The above figure shows a formula where every precedent cell is explicitly stated. Tracing precedent shows an arrow from each precedent cell. Figure 17. Precedent Arrow for a Range of Cells In the above figure, the formula indicates a range of cells. Trace precedent shows the range of cells in a rectangle, and shows only one arrow from the range of cells. This box arrow will appear, e.g., in these formulas: =sum(A1:A10), =average(A1:10), =count(A1:B10). The box arrow can cover many rows and columns, as shown in the following figure. Figure 18. Precedent Arrow for a Multi-column Range of Cells More examples of “Trace Precedents” pattern of arrows will be given later. For example, what happens when a formula refers to a cell in another Excel worksheet? What happens in complex formulas that look up a table of data, such as vlookup? If we trace the dependents of Ben’s English mark, the following figure will appear. First select Ben’s English mark (click on it), then click on “Trace Dependents”. Figure 19. Trace Dependents Since Ben’s English mark is used by only one cell, there is only one arrow from there. The “box arrow” drawn by “Trace Precedents” will not appear when we trace dependents. It is a good point to bear in mind. Tracing precedents over an entire worksheet may not give an identical set of arrows, compared to tracing dependents. The next figure shows the result when we trace the dependents for every cell (click on “Trace Dependents” after selecting each cell). Notice that the pattern of arrows is quite different. Figure 20. Dependent Arrows do not have the box outline 5. Wrong or Missing Connections: A Common Error A wrong connection happens when a formula in a cell refers to a wrong cell. This is a very common error. But it is notoriously difficult to find just by looking at formulas one by one. With the precedent/dependent arrows, the error may become visually obvious. One wrong connection can have big consequences. Here are a few cases. $15M mistake in county books being corrected: Treasurer says spreadsheet error had no financial impact [Herald-Times, Bloomington, Ind.] (URL: http://www.tmcnet.com/usubmit/2009/11/21/4494996.htm) "The financial discrepancy came to light earlier this month during a county council work session. At that time, Smith described it as a reporting error that happened when two employees switched jobs, after which the new employee in her office began using a different spreadsheet that contained a broken data link. A single entry error compounded over the past few months to make the apparent discrepancy larger, she said at the time, eventually leaving the county's books showing $15 million more than the bank said it had." Trustee's Office mistake to cost taxpayers $12,500 (URL: http://archive.knoxnews.com/news/local/trustees-office-mistake-tocost-taxpayers-12500-ep-402350878-357355791.html) "He said the reports his office sent to KPMG didn't include one account, which threw off his department's financial statements. The total cash on hand for June 30 that the office should have reported was $128.9 million, he said, and not $122.7 million, which was what was sent to the auditor. It occurred when one account wasn't correctly linked into an Excel spreadsheet." Example 1: A payroll model For an example of how precedent arrows can give clues to errors, consider this spreadsheet (given in this article (Ferret Out Spreadsheet Errors, Use Excel’s tools to uncover and correct formula problems.by Mark G. Simkin, January 31, 2004, JournalofAccountary.com) The precedent picture generated from the spreadsheet is given below. This is generated by selecting the “total” cell (G14) and clicking “Trace Precedents” a few times. Irregularities in the blue dots indicate potential errors. A blue dot on a cell means that the cell is the beginning of one or more precedent arrows. Lack of a blue dot means that the cell is not used (for the set of precedent arrows shown). A missing blue dot may indicate a missing connection. Can you spot all the irregularities? Figure 21. A Model with Missing Connections Notice that in column C (regular hours), only one cell has a blue dot, indicating that this cell is used in some formula in other cell or cells. More importantly, the other cells in column C are not used, as they do not contain any blue dots. This pattern strongly suggests the presence of errors, and these cells should be checked thoroughly. The relevant portion is shown below. Figure 22. Missing Dots, Missing Connections In column D (overtime hours), there is also a cell without a blue dot, different from its neighbors. This also indicates a potential error. If you look harder, you can find that cell F7 also has a different arrow pattern compared to its neighbors. This example illustrates a very important use of the automatically generated influence chart. While the items are there, the connections are missing. The influence chart makes the missing connections visually obvious. Example 2: An economics model This economics model, shown earlier, is repeated below, to illustrate another arrow pattern that indicates potential errors. This is about the position of box arrows. The boxes miss out the intended rows (rows from 25 to 29). Figure 23. Excluded Rows in Faulty Range Cell Formulas 6. An Enhanced Influence Chart: Power Formula Auditing Excel’s tools for tracing precedents and dependents are very useful. They can be make even more useful with some additional programming. An enhanced tool for generating better influence charts can be downloaded here: Power Formula Auditing (Excel 2013). Power Formula Auditing has very good improvements over Excel’s tracing of precedents and dependents. The main improved features include: The ability to select many cells for tracing precedents and dependents. Excel’s trace precedents/dependents buttons are limited to only one selected cell. - The ability to get an entire overview with just 2 clicks. Simply select the entire spreadsheet, and click one button on the new toolbar. Currently, to get an overview may require a few hundred clicks, and most people will just not do it, and they will miss out on powerful influence charts. Power Formula Auditing is developed based on the original formula auditing functions. It is unlikely to change the appearance of spreadsheets. However, it is advisable to work on duplicate copies. Power Formula Auditing may be slow for very big models, e.g., a thousand cells. The functions may take some time to complete their jobs. Excel’s “Trace Precedents” avoids the time delay by limiting to only one selected cell. This software works for Windows Excel 2013 and 2010. It may not work for older versions of Excel. It is the product of spreadsheet research by the author and others at the National University of Singapore. (The URL to download is: https://drive.google.com/open?id=0B926LJ4H-JLIdHItcWE3WEJnNms) The software can be easily added to Excel 2013, as an add-in software. This is explained in Appendix A. Appendix B explains how it can be removed. 7. Tracing Precedents / Dependents for Multiple Cells Precedents for a single cell Excel’s “Trace Precedents” and “Trace Dependents” works only for one cell. Figure 24. Trace Precedents work for one cell. For example, in the above figure, 3 cells are selected (as indicated by the green border around B7:D7). “Trace Precedents” show the arrows only for one cell (B7). Multi-Precedents for a block of cells With Power Formula Auditing, we can select one or many cells for tracing precedents. The function/button is called Multi-Precedents Figure 25. Power Formula Audit Buttons It is a powerful extension of the “Trace Precedents” button in Excel. Using the same expense example, we can select B7:D7. One click of MultiPrecedents will draw precedents arrows for all the selected cells, as shown in the next figure. "Multi-Precedents" works on 1 cell, a block of cells, and even an entire worksheet. It helps to identify the important cells and their connections. Figure 26. Multi-Precedents work for many selected cells Multi-Precedents for an entire worksheet In addition to selecting a block of cells, we can also select the entire worksheet. Selection of an entire worksheet can be done by clicking on the square on the top left corner, above row 1 and to the left of column A, as shown in the following figure. Figure 27. Selection of an entire Worksheet Multi-Precedents can then be applied to the selection. As for “Trace Precedents”, each click of “Multi-Precedent” will show another level of Precedents. Remove Multi-Precedents The button to remove multi-precedent is labeled “Less-MultiPre” . Each click removes one level of the precedent arrows. Multi-Dependents The “Multi-Dependents” and “Less-MultiDep” buttons work in a similar way to the “Multi-Precedents” and “Less-MultiPre”. As example is shown below. We first select 3 cells, B2:D2, then we click on “Multi-Dependents.” The result is the drawing of 3 arrows from the selected cells. With Excel’s “Trace Dependents”, we have to select B7, click “Trace Dependents”, and repeat for C7, and D7. Figure 28. Multi-Dependents Example 8. Examples of Overviews Often, when we are faced with a spreadsheet, we have no idea how the spreadsheet is organized and where we can start. The following examples illustrated how an overview can be easily generated. An overview allows us to have a high level view, and identify where to zoom in for closer looks. Example 1: A simplified balance sheet The next figure shows a much simplified balance sheet. , which shows a standard format for a company’s balance sheet. This is how it normally looks without an influence chart. Figure 29. A Balance Sheet The next figure shows the same balance sheet with an influence chart drawn over. The figure is obtained in two clicks. One click selects the entire sheet. The second click is on “MultiPrecedent” button. The overview shows 4 components. Each component is a “group” of cells connected by arrows, and there are no arrows across groups. Figure 30. A Balance Sheet with Components Highlighted Example 2: A company’s consolidated balance sheet This example uses a consolidated balance sheet provided by a big corporation. The next figure shows how it looks like normally, without the influence chart. The subsequent figure, with the influence chart showing the components and precedent arrows, is produced in 2 steps: · Select the entire worksheet · Click the “Multi-Precedent” button The overview created by the blue precedent arrows shows 8 separate components, with two for each quarter. Each quarter has one component for assets and one component for liabilities. Figure 31. A Company consolidated balance sheet Figure 32. Quarterly Balance Sheet with Precedent Overview Example 3: A company’s statement of income This example is from the same corporation. It shows how a worksheet with one component can show separated components when only part of the worksheet is selected for analysis. The next few diagrams show the following: · The worksheet as it appears normally. · The entire worksheet with precedents shown (by clicking “MultiPrecedent” button” · The partial worksheet with only quarterly columns selected, and with a click on “Multi-Precedent” button”. It is quite clear that the quarterly data are connected through the annual total in the rightmost column. But without the rightmost column, the quarterly columns are separated components. A partial selection can also present a less cluttered view. Figure 33. Income Statement Figure 34. An Overview of the Income Statement Figure 35. A Quarterly Analysis of the Income Statement Sometimes, it is better to analyze only part of the worksheet. As in this case, the arrows and color boxes for each quarter are not messed up by another set of horizontal arrows for the annual totals. In general, it is good to try overviews or partial overviews, to allow us to view the Excel model with different perspectives. Example 4: A Personal Budget Model This example of a personal budget spreadsheet is from vertex42.com/ Figure 36. A Personal Budget Model With the Multi-Precedent button, we can get the following figure with precedent arrows with 2 clicks (select the entire worksheet and click on the Multi-Precedent button). This picture shows us the overall structure of the model. For example, there are many tabular structures and all arrows eventually point to the upper right corner. We see that sub-totals within each table are used in the total calculation, at the top right corner. We also see that budget-actual differences in each table are not used for the total difference. Figure 37. Precedents for Personal Budget Model 9. Special Cases for Trace Precedents and Trace Dependents For some types of formula, trace precedents and trace dependents may give results that are not so natural, intuitive or even misleading. Some examples are discussed in this chapter. Power Formula Auditing, which builds upon the original trace precedents and trace dependents, have the same limitations. Offset formula in Excel One example is the Offset formula. A explanation for this formula is at Microsoft support (How to use the Offset formula in Excel). The offset formula can be used to calculate, say, weekly data when we have daily data. The formula will, e.g., sum the first 7 days, and each subsequent 7 days. In the next figure, the top rows are the daily usage of 3 items (milk, sugar and butter). For clarity, only the milk entries have data. To get the weekly summation for week 1, we enter this formula into cell B8 =SUM(OFFSET($B3,0,(B$8-1)*7,1,7)). This formula can be copied and pasted for subsequent weekly summations. For example, after copy and paste, cell C8 will have this formula: =SUM(OFFSET($B3,0,(C$8-1)*7,1,7)) Without the offset formula, we have to design and type a different formula for each weekly summation. So the offset formula saves a lot of mental calculation and typing. The effortful method is illustrated in the bottom half of the figure. Cell B17 contains =SUM(B12:H12), and cell C17 contains =SUM(I12:O12). Note that we cannot just copy the formula from B17 and paste to C17. With this method, the precedent arrows show very clearly the daily cells that are being summed into the weekly cells (the precedent arrows in the bottom half of the figure). In contrast, the precedent arrows for the offset formula do not give much information. They show only the cells that are explicitly mentioned in the offset formula, but not the ultimate cells that are used in the summation. Tracing precedents become much harder with the offset formula. Cells that are used by the offset formula but not explicitly included in the formula will show up as stand-alone cells, as shown in the next figure. The result could be visually misleading, unless we are aware of it. Figure 38. Precedents for Offset Formula Vlookup & Hlookup formulas The Vlookup function allows us to search a table based on a value in the first column, and return a value from some other column along the same row as the search value. An illustration is given at Microsoft support (VLOOKUP function). The Hlookup function is a similar function which search based on the first row, instead of the first column. The next figure shows the trace-precedents arrows for vlookup and hlookup formulas. The arrows indicate the table used in the search, but does not pinpoint the cell value that is returned. For example, the vlookup formula returns the birthdate in cell B3. Trace-precedent does not go into the results for formula evaluations. Figure 39. Precedents for Vlookup /Hlookup Formula Formulas that cross worksheets In some complex situations, people organize their Excel model across a few worksheets. It is possible to trace precedents across worksheets. However, it is very difficult. Consider a very simple example where one worksheet is used for a company’s profit model for one year. Over a few years, there will be a few worksheets, one for each year. In addition, there is a total worksheet that sums number across the yearly worksheets. Consider this very much simplified model where the purpose is solely to see what happens when precedents are traced across worksheets. There are three worksheets: All-years, 2016 and 2017, as shown in the following figure. Notice the cross-worksheet formula (=‘2016’!C5+’2017’!C5). Figure 40. Multi-Worksheet Model What happen when we trace precedents from the All-years worksheet? We select cell C5, then we click on “Trace Precedents”. The result is shown in the next figure. Figure 41. Cross-worksheet Precedent Notice that the precedent arrow is a dash arrow and the beginning of the arrow shows a table, indicating a worksheet. We can identify the precedent by carefully double-clicking on the arrow. This will bring up a panel that shows the precedent cells, as shown in the next figure. Figure 42. Precedent Cells across Worksheets Double-clicking on a cell reference in the panel will bring us to that cell (in its own worksheet). Tracing across worksheets is usually very difficult. 10. A Special Chapter for Spreadsheet Teachers As teachers, when we ask students to develop spreadsheet solutions, how do we check the correctness of the answers? Checking other people’s spreadsheet model is well recognized as a difficult and tedious task. One way used by teachers to reduce the difficulty is to have a spreadsheet template where students must fill in formulas and values at specially designated cells. The student answers are then compared with the correct solution, e.g., we can overlay worksheets and check the corresponding values. Even then, a common problem is that students may simply enter the expected value instead of the correct formula. For example, a value of 10 may be entered instead of a formula, say, =A1*B2. The current recommended check is to turn formulas on and off, and visually check for values where formulas should be. (The fast way to toggle formulas on and off is ctrl+`) Power Formula Auditing is very suitable for doing this type of check: a value when a formula should be. The precedent arrows drawn by “Multi-Precedents” will show clues. There will be missing arrows, and /or the chain of arrows will be disconnected. The next figure shows a table to calculate yearly balance with different interest rates for each year. 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/enus/article/Introduction-to-What-If-Analysis-22bffa5f-e891-4acc-bf7ae4645c446fb4) 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." Proceedings of the Warm Up Workshop for ACM/IEEE ICSE 2010. ACM, 2009. Baker, John, and Stephen J. Sugden. "Spreadsheets in education–The first 25 years." Spreadsheets in Education (eJSiE) 1.1 (2007): 2. Bewig, Philip L. "How do you know your spreadsheet is right?." arXiv preprint arXiv:1301.5878 (2013). Bishop, Brian, and Kevin McDaid. "An empirical study of end-user behaviour in spreadsheet error detection & correction." arXiv preprint arXiv:0802.3479 (2008). Chan, H. C., C. Ying, and C. B. Peh. "Strategies and visualization tools for enhancing user auditing of spreadsheet models." Information and Software Technology 42.15 (2000): 1037-1043. Chan, Hock Chuan. "Spreadsheet visualization effects on error correction." SIGHCI 2004 Proceedings (2004): 1. Chan, Hock Chuan, Suparna Goswami, and Hee-Woong Kim. "An alternative fit through problem representation in cognitive fit theory." Journal of Database Management (JDM) 23.2 (2012): 22-43. Clermont, Markus. A scalable approach to spreadsheet visualization. na, 2003. Davis, J. Steve. "Tools for spreadsheet auditing." International Journal of Human-Computer Studies 45.4 (1996): 429-442. Galletta, Dennis F., et al. "Spreadsheet presentation and error detection: an experimental study." Journal of Management Information Systems 13.3 (1996): 45-63. Goswami, Suparna, Hock Chuan Chan, and Hee Woong Kim. "The role of visualization tools in spreadsheet error correction from a cognitive fit perspective." Journal of the Association for Information Systems 9.6 (2008): 321. Hendry, David G., and Thomas RG Green. "Creating, comprehending and explaining spreadsheets: a cognitive interpretation of what discretionary users think of the spreadsheet model." International Journal of Human-Computer Studies 40.6 (1994): 1033-1065. Hermans, Felienne, Martin Pinzger, and Arie Van Deursen. "Supporting professional spreadsheet users by generating leveled dataflow diagrams." Proceedings of the 33rd International Conference on Software Engineering. ACM, 2011. Igarashi, Takeo, et al. "Fluid visualization of spreadsheet structures." Visual Languages, 1998. Proceedings. 1998 IEEE Symposium on. IEEE, 1998. Jannach, Dietmar, et al. "Avoiding, finding and fixing spreadsheet errors–a survey of automated approaches for spreadsheet QA." Journal of Systems and Software 94 (2014): 129-150. Leitão, Roxanne, and Chris Roast. "Developing visualisations for spreadsheet formulae: towards increasing the accessibility of science, technology, engineering and maths subjects." McKeever, Ruth, and Kevin McDaid. "How do range names hinder novice spreadsheet debugging performance?."arXiv preprint arXiv:1009.2765 (2010). Middleton, M. "Decision Modeling using EXCEL." University of San Francisco (2003). Mittermeir, Roland, and Markus Clermont. "Finding high-level structures in spreadsheet programs." Reverse Engineering, 2002. Proceedings. Ninth Working Conference on. IEEE, 2002. O'Donnell, Peter. "The Use of Influence Diagrams in the Design of Spreadsheet Models: an experimental study." Australasian Journal of Information Systems. 9.1 (2001). Panko, Raymond R., and Ralph H. Sprague. "Hitting the wall: errors in developing and code inspecting a simple spreadsheet model." Decision Support Systems 22.4 (1998): 337-353. Panko, Raymond R., and Richard P. Halverson Jr. "An experiment in collaborative spreadsheet development." Journal of the Association for Information Systems 2.1 (2001): 4. Plane, Donald R. Management science: A spreadsheet approach for Windows. Course Technology Press, 1995. Powell, Stephen G., Kenneth R. Baker, and Barry Lawson. "An auditing protocol for spreadsheet models." Information & Management 45.5 (2008): 312-320. Roy, Sohon, and Felienne Hermans. "Dependence tracing techniques for spreadsheets: an investigation." Software Engineering Methods in Spreadsheets (2014): 1-4. Saariluoma, Pertti, and Jorma Sajaniemi. "Transforming verbal descriptions into mathematical formulas in spreadsheet calculation." International Journal of Human-Computer Studies 41.6 (1994): 915-948. Shiozawa, Hidekazu, Ken-ichi Okada, and Yutaka Matsushita. "3d interactive visualization for inter-cell dependencies of spreadsheets." Information Visualization, 1999.(Info Vis' 99) Proceedings. 1999 IEEE Symposium on. IEEE, 1999. Simkin, Mark G. "How to validate spreadsheets." Journal of Accountancy 164.5 (1987): 130. Teo, Thompson SH, and Margaret Tan. "Spreadsheet development and ‘whatif’analysis: quantitative versus qualitative errors." Accounting, Management and Information Technologies 9.3 (1999): 141-160. Teo, Thompson SH, and Joo Eng Lee-Partridge. "Effects of error factors and prior incremental practice on spreadsheet error detection: an experimental study." Omega 29.5 (2001): 445-456. Wetzel, Laura R., and Peter J. Whicker. "Quick Correct: A method to automatically evaluate student work in MS Excel spreadsheets." Spreadsheets in Education (eJSiE) 2.3 (2007): 1. Appendix A. How to add Power Formula Auditing to Excel? There are many “add-ins” software or applications. Excel has very detailed guides on how to add in an “add-ins” application. Power Formula Auditing is an “add-ins” application. The steps are as follow: · Download the Power Formula Auditing add-in software (PowerFormulaAudit_1.0.xlam) into any directory in your computer. · Add the software with the following steps: o Click the File tab, click Options, and then click the Add-Ins category. o In the Manage box, click Excel Add-ins, and then click Go. o The Add-Ins dialog box appears. o Browse to the directory where you save. PowerFormulaAudit_1.0.xlam. Select the file. o PowerFormulaAudit will show up in Add-Ins available box. · You may need to activate the add-in with the following steps: o Click the File tab, click Options, and then click the Add-Ins category. o In the Manage box, click Excel Add-ins, and then click Go. o The Add-Ins dialog box appears. o In the Add-Ins available box, select the check box next to the add-in that you want to activate, and then click OK. · Close Excel. Do not delete the file for PowerFormulaAudit. · You can use Excel as per normal. The commands from Power Formula Audit are in the menu for you to use with any Excel file. The first time a Power Formula Audit button is used in a file, Excel may prompt you to enable the macros. The addition steps are further illustrated with the following screenshots: Figure 45. File / Options /Add-ins Screenshot Figure 46. After clicking "Go" from the previous screenshot For more general and detailed information about add-ins and steps to add or remove add-ins to your spreadsheet, please see this link from Microsoft support (Add or remove add-ins). Appendix B. How to uninstall PowerFormulaAudit from Excel? The add-ins will be there until you remove it. A simple way to remove PowerFormulaAudit is to delete the PowerFormulaAudit file from the directory. If the file is moved to another directory, Excel will not be able to find the PowerFormulaAudit functions. Another way is to deactivate the add-in, which will also remove the menu for power formula audit. The steps are similar to the activation steps in Appendix A. The steps to deactivate are: · Click the File tab, click Options, and then click the Add-Ins category. · In the Manage box, click Excel Add-ins, and then click Go. · The Add-Ins dialog box appears. · In the Add-Ins available box, uncheck the check box next to the add-in that you want to deactivate, and then click OK. For more general and detailed information about add-ins and steps to add or remove add-ins to your spreadsheet, please see this link from Microsoft support (Add or remove add-ins). Appendix C. PowerFormulaAudit Menu The PowerFormulaAudit menu is shown below. Figure 47.. PowerFormulaAudit Menu PowerFormulaAudit has the following menu buttons, listed from left to right as shown in the above figure. Multi-Precedent Excel’s “Trace Precedents” works only when one cell is selected, MultiPrecedent works on any number of selected cells, e.g., a single cell, a row / column of cells, a block of cells, or even an entire worksheet. Where applicable, every click shows the next level of precedents. Since this function works on many selected cells at a time, the precedents may not be strictly one level up. The reason is that the selected cells may already have a few levels of precedents. In the case where an entire worksheet is selected, this function will draw all the precedent arrows in the worksheet. Multi-Dependent This works in the same way as Excel’s “Trace Dependents”, but allow many cells to be selected. Each click shows the next level of dependents. As in the case of multi-precedents, the function does not go strictly by one level of dependents. The reason is that the selected cells may already have a few levels of dependents. When an entire worksheet is selected, this function draws all the dependent arrows. As illustrated earlier, dependent arrows may look different from precedent arrows (particularly for box arrows). Less-MultiPre Every click reduces the arrows for the furthest level of precedents. Less-MultiDep Each click removes the arrows from the furthest level of dependents. Erase Arrows. This yellow “eraser” button removes all auditing arrows. PowerFormulaAudit has a few limits. · For very big models, e.g., a thousand cells, the functions may take some time to complete their job when the entire worksheet is selected. Excel’s “Trace Precedents” avoids the time delay by limiting to only one selected cell. · In rare cases, a formula may refer to an empty cell. For example, cell A3 has this formula “=A1”, and cell A1 is empty (i.e., cell A1 has no numeric value or string value, not even a space). Where an empty cell is the precedent of another cell, the dependent arrow will not be drawn with Multi-Dependent, e.g., when cell A1 is selected.. However, the arrow will be drawn with Multi-Precedent, e.g., when cell A3 is selected. ABOUT THE AUTHOR Dr. HC Chan has a PhD from the University of British Columbia. He has teaching and research experience with spreadsheets. He taught a course on decision modeling with spreadsheets, for a few years, at the National University of Singapore. He also provided specialized training about spreadsheet error detection to practitioners. His research about spreadsheet users has been published at international journals and conferences.