In the world of finance and accounting, accuracy is not just important, it's everything. Ensuring the numbers in your workbooks align perfectly with your Cube data is crucial for reliable reporting and analysis. This guide will walk you through the process of adding validation and tie-out indicators to your spreadsheets, enhancing your confidence in the data you fetch and publish.
This article uses our allocation template as an example. You can check out our other templates here or apply the steps below with adjustments for your own spreadsheets.
Why add spreadsheet validation?
We recommend incorporating validation checks and tie-out indicators to frequently used templates to account for account changes over time. While Cube's Spreadsheet App validation function effectively identifies misspellings or incorrect placements in rows or columns, it can't flag if a newly added dimension member is missing in a range and, therefore, not included in a calculation. Using a combination of formulas and conditional formatting, we can create checks within our spreadsheets to watch for this. These checks are vital for ensuring that the total figures in your Cube reflect all the individual accounts correctly, even when new accounts are added or adjustments are made.
Visual tie-out indicators
Let's start by adding tie-out indicators to visually alert us to any issues in this spreadsheet. If an account is missing or misspelled, like in the image below, the total revenue won't match the value fetched from Cube. Adding a visual indicator will let you see that there's an issue at a glance while keeping the dynamic functionality we want in a Cube spreadsheet template.
1. Pinpoint key subtotals in your report
Typically, these include major groupings like total revenue, operating expenses, or cost of goods sold. In this example, we'll look at the cells that make up our revenue.
2. Apply conditional formatting
Next, we'll apply conditional formatting to the cell with the total for this group. Using a formula, we can highlight the cell if it doesn't match the total for the rest of the fetched dimensions from this group.
In our example, our revenue cell's conditional formatting formula is: =SUM(E$13:E$17)<>E$18
With this conditional formatting applied, the cell has a white background if the values match and a red background if they don't. This lets us quickly see if our values aren't tying out every time we fetch.
Learn more about using conditional formatting in your spreadsheets.
3. Use Format Paint to copy conditional formatting
Save time and use similar conditional formatting elsewhere in your report for checking tie-outs by using the Format Paint tool.
Validation checks
Many of our spreadsheet templates contain validation checks extending the visual tie-out indicators above into an overall report validation section that lets your team know whether this report is ready.
1. Add a formula to logic test the section you want to validate
The formula to build a report validation section is similar to the one we used in our conditional formatting above, but wrapped in an IF statement to produce the text we want to display in the cell.
=IF(SUM(E11:E14)=E15,"Valid","Missing Account")
Repeat this formula for each cell and group that needs to be validated.
2. Apply conditional formatting
Next, we'll add two conditional formatting rules to apply color formatting to the cells based on their text.
If a cell says "Missing Account," we've set it to red to stand out; if it says "Valid," it is green.
Learn more about using conditional formatting in your spreadsheets.
3. Use Format Paint to copy conditional formatting
Copy this conditional formatting to all validation cells, and you're all set!
By implementing these validation checks, you add an extra layer of accuracy and reliability to your financial reports. These simple yet effective steps can save significant time and prevent errors in your financial analysis, making your work with Cube and Excel more efficient and dependable.
Remember, these validation indicators are not just for error detection; they also serve as a constant reminder of the dynamic nature of financial data and the need for regular reviews and updates.