Leveraging Excel to Enhance Stock Plan Recordkeeping

Most companies use a third-party recordkeeping system to manage their equity plans. These platforms often are highly tailored to the needs of equity plan administrators and include customized functionality and calculations that are specific to stock compensation (e.g., statutory limit calculations such as the $25,000 limit, financial reporting calculations, mobility tax calculations).

In an ideal world, these tools would be fully customized to meet your every data and reporting need. In practice, however, even with all the tailored functionality, most companies have unique reporting needs that aren’t met by their recordkeeping system. If you manage equity plans, it’s likely that, at some point, you will rely on Excel to supplement the reporting available in your stock plan recordkeeping system.

This article explores the ways in which Excel can be utilized to complement a third-party recordkeeping system, helping you to optimize your data management strategies.

Data Comparisons

A couple of decades ago, when I was a newbie to stock plan administration, I was once handed two reports that should have matched but didn’t. I remember sitting down with a ruler to compare both reports line-by-line to find the discrepancies. Even 20 years ago, this was an inefficient way to solve that problem. A better solution is to export both reports in a format that can be opened in Excel and have Excel compare the data for you.

For example, let’s say there’s a discrepancy between your expense reports for this quarter and last quarter. Using Excel to compare the two reports would allow you to find the discrepancy a lot faster than my line-by-line comparison method.

Data Integration

One of Excel's key strengths lies in its ability to integrate data from various sources, such as different departments or systems. Using Excel's lookup functions, you can easily combine data from different reports or different systems for comprehensive analysis.

Let’s say you want to analyze ESPP participation by various employee demographics, such as compensation level, department, or age. ESPP participation rates are stored in your payroll or stock plan recordkeeping systems but the demographic data is in your company’s HR system. With Excel, you can combine the data from all three systems into a single report that gives you important insights into who is and isn’t participating in your ESPP. This data can inform a strategy to grow participation in your ESPP.

Customized Reports

An obvious use of Excel is to create customized reports. A tip that I learned from Elizabeth Dodge of Equity Plan Solutions is to add all your custom formulas in columns at the end of the worksheet (i.e., to the right of the data you exported). This allows you to easily refresh the data when you want to update the report without having to recreate your formulas. It can be a real time-saver for reports that you have to reproduce regularly.

I change the font color or shading of the columns with my formulas, so I know not to overwrite those columns when copying in new data. If I have a particularly complex report, I add a worksheet to my Excel file with step-by-step instructions on how to update it (including the reports I run in my recordkeeping system to create it).

Excel also includes all the data visualization functionality that exists in PowerPoint. Not only can you add your own calculations and combine data from different reports, but you can then turn that data into visually appealing charts and graphs that automatically update when you refresh the data in your Excel file.

Scenario Analysis and Forecasting:

Excel is a great tool for scenario analysis and forecasting, allowing you to model different business scenarios with a range of variables. When it comes to equity programs, no one likes surprises. From financial effects and cash flow to share usage, forecasting potential outcomes is a key part of managing an equity plan. In Excel you can create a custom report that you can use again and again, easily refreshing the data and updating the assumptions used in your forecast.

My Favorite Excel Formulas

Xlookup: This is the key to a lot of the Excel functionality I discuss above. Xlookup enables you to combine data from two different worksheets using criteria that you specify (such as an employee ID or award number). I like it because the syntax is straightforward—significantly easier to use than either Vlookup or Index/Match—and you can build error-handling right into the formula, rather than having to nest it in a IfError formula.

All the Mathmatical Ifs: I use SumIfs, CountIfs, and AverageIfs a lot. I even occasionally use MaxIfs and MinIfs. They are all my friends. Because you know what I don’t like: pivot tables. I find pivot tables awkward to use and hard to audit. Plus, they don’t automatically update when your data changes. SumIfs, CountIfs, et. al., produce the same result, automatically update when my data changes, and are more transparent. And they are easier to use, in my opinion.

If: This formula evaluates an expression and returns specified results if the expression is true or if it is false. It can be used to compare two values and flag any discrepancies.  

IfError: Speaking of error handling, IfError is another formula that I use frequently. For formulas that don’t accommodate error handling, nesting the formula in an IfError statement allows you to specify what the result should be if the formula produces an error. For example, you could instruct Excel to return 0, a blank cell, or your own error message.

Text Formulas: These are formulas that allow you to manipulate text. They can be used to extract the left most or right most characters of a cell, find the position of a specified character in a text string, combine data from separate cells, and more.

Expand Your Excel Skills

Want to know how to do more with Excel? The NASPP course “Excel Essentials for Equity Compensation” explains the functions and formulas that are most helpful in managing stock plans and demonstrates how to deploy them using real-life scenarios. The course is available now on demand.