EXAMPLE INCISIVE.ONLINE SPREADSHEET...

What errors are in your Budget spreadsheet? If you started with a "free" one, then you should carefully check it out. Submit it to our online spreadsheet analysis service and you will likely find errors. See below for the errors that I found in the first couple minutes with this spreadsheet. This spreadsheet was submitted in response to the question: Does anyone have a good working excel template for an IT budget? This spreadsheet, 943301-Sample-IT-Budget.xls, is publicly available from the Spiceworks Finance Forum . A copy is also available here. Now, for the analysis.

In order to analyze this spreadsheet, it was submitted at: Incisive.Online spreadsheet analisys page. The following five reports were produced.

• 943301-Sample-IT-Budget_RiskAssessmentReport.pdf
• 943301-Sample-IT-Budget_RiskSummaryReport.pdf
• 943301-Sample-IT-Budget_CriticalErrorReport.pdf
• 943301-Sample-IT-Budget_HeatMap.xls
• 943301-Sample-IT-Budget_StructureMap.xls

The Risk Assessment Report shows:

• The Workbook Statistics section shows 480 formulas, but only 27 unique. This shows that the spreadsheet would be easier to review with a tool like Incisive Validate, which facilitates the process of reviewing formulas by grouping like formulas together. So, instead of reviewing 480 formulas, a reviewer could verify only 27 formulas and be done. With 4 sheets, this is a fairly small spreadsheet, and one that does not utilize names. It also shows no referenced external workbooks.
• The Sheet Statistics section shows the names of the sheets along with thier protected and hidden statuses as well as their formula counts. No surprises here.
• The External Workbooks section shows no externally referenced workbooks.
• The Excel Function Use section shows the counts of how many times an Excel function was used, and whether that use was in a non-array or array formula. Glancing at this list shows extensive use of the SUM function. The spreadsheet would benefit from the dependency highlighting available in Validate as well as in the structure view of Xcellerator. I'll show an example of an error that was found using dependency highlighting below:
  
  Above, the cell, G9 (the $77,800.00 in the subtotal column) is a SUM that should only include F8, but instead it sums down to F25, this range is highlighted in blue. This Error inflates the subtotal by $45,800.
  
  
• The Named Ranges section shows no surprises. Although this workbook does not utilize named ranges, sometimes workbooks will have names will have #REF! or external workbook references in them.
• Finally, the Unique Formulas section shows every unique (R1C1) formula in the workbook. Reviewing these formulas can provide insight into the structure of the spreadsheet. For example, the first seven formulas listed represent 80 percent of the total number of formulas in this spreadsheet. With a tool like Incisive Validate, you could review each of those formulas once, and achieve 80% coverage of this spreadsheet! This is a huge time saver in reviewing workbooks. Also of interest is the last 8 entries in this section - formulas that are only used once. Although these are often legitimate formulas, they are sometimes errors that have been created during updates to the spreadsheet. In Incisive Validate, there are navigation tools that help you find these unique formulas that only occur once, and often, Xcellerator will identify these as inconsistent formulas - error cells. This section shows an interesting variation in the formulas. There are two formulas that are equivalent, but different in this spreadsheet. One is a SUM of a product, and the other is just a product. See the image below taken from the report:
  
  
  
  

The Risk Summary Report has two main sections:

• The Workbook Statistics section shows 1,074 formulas, but only 101 unique. This shows that the spreadsheet would be easier to review with a tool like Incisive Validate, which facilitates the process of reviewing formulas by grouping like formulas together. So, instead of reviewing 1,074 formulas, a reviewer could verify only 101 formulas and be done. With 8 sheets, this is a fairly small spreadsheet, and one that does not utilize names. It also shows zero referenced workbooks.
• The Xcellerator Scan Results shows the number of results that each test found in a scan of Xcellerator. Xcellerator is a desktop tool that is available for purchase in the Incisive Store. Each submitted spreadsheet is scanned with an online version of Xcellerator running most of the available tests. The tests run are listed in this section with the tests with zero results listed last. In this example, I would begin by investigating the first test's results - the formula error test results.
  
  There are 1 result each for the "Inconsistent Formula" and "Formula fails to cover area" test. After investigating and correcting these errors, the rest of the spreadsheet should be thoroughly reviewed as well. For locations of some of these two most interesting test results, see the Critical Error Report.
  
  

The Critical Error Report, Heat Map, and Structure Map

• The Critical Error Report shows the location of some of the results identified in the Xcellerator Scan and summarized in the Risk Summary Report. In this case, it points to two cells of immediate concern: Inconsistent formula results at 'EMISSIONS'!H11 and 'EMISSIONS'!I11. Investigation into these cells with Xcellerator shows that all of row 11 may have errors.
  
  
  
  
• Even without Xcellerator these results can be further investigated using the Heat Map provided by the analysis service. This file has color coded results in a copy of your spreadsheet. When this analysis is done with Xcellerator, the test results can be filtered an navigated to using the built in navigation tools of Xcellerator. You can review your spreadsheet's errors by reading the comments inserted into each cell with a result. In addition to this, the Structure Map provided by the online analysis service can be used to view the structure of the spreadsheet. Boxes are drawn when possible around formulas that are the same (have the same R1C1).Below are some examples of errors shown in the Heat Map and the Structure Map analysis results:
  
  
  Find the same area below, shown in the Structure Map result
  
  
  
  Another error can be found in the Hardware Budget sheet. In this sheet, there are many cells referencing cells to the left. One cell though, varies. It is currently selected, with it's dependencies highlighted in blue. First, we have the Heat Map, and then the Structure Map.
  The Heat Map shows the error cell colored red.
  
  
  Find the same area below, shown in the Structure Map result. In this image, the formula that varies is identified in a different region.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

Take a moment to submit your budget spreadsheet to Incisive.Online's spreadsheet analysis service. It is available as a free trial. I'm sure you'll find errors similar to these that you'll want to correct as soon as possible.