Speed Loop in Action

As a motorcycle safety instructor, I tell new riders that learning to ride a motorcycle is like showering. One shower does not keep you fresh and clean the rest of your life, just like taking one motorcycle class does not give you everything you need to stay safe on a motorcycle. You need to practice regularly and take classes regularly to stay “fresh” with your skills. The same can be applied Excel. Taking one class or building one spreadsheet does not mean your skills are as fresh and up to date as they can or should be. As a CPA, I’m required to keep fresh with continuing education. This is how I learned about Excel University’s Speed Loop® process.

The Speed Loop got me thinking about my spreadsheets differently. The Speed Loop is a formalized method to upgrade recurring-use Excel workbooks. First, you identify the manual steps it takes to prepare the workbook each recurring period. Next, you build in some error checking to prevent and/or detect them. Then you begin automating as many of the manual steps as possible. I was able use these concepts to reduce the time it took to prepare my financial dashboards from five days to 60 minutes. The key takeaway from the Speed Loop was that building reports is a process and not just a deliverable.

My Problem

I needed to provide financials for a new group as part of a large reorganization for the project-based company I support.

There were three major hurdles.

1. Because of the volume of data, the financial data was segmented by type of work as opposed to individual project transactions.
2. There was a gap in how corporate presented financial content and how the field consumed financial content. Corporate looked at the categories of work while the field looked at financials by specific project.
3. The categories of work tracked at the corporate level was less specific and different than the new organization wanted to use. Corporate grouped the work into 18 categories, while the new organization wanted to track the work by 46 unique categories.

How the Speed Loop Helped

My superpower is thinking outside the box, so I tend to not follow instructions so well. Even though I did not follow all the Speed Loop steps perfectly, the steps I did implement were very helpful. Here is the summary.

I documented all the steps I took to create my dashboards from getting the data that I needed to preparing my Power Point slides with screenshots of my dashboards. The biggest issue to resolve was getting the financials to show our unique program nicknames as opposed to using the nicknames that came out of the system of record.

The first thing I did was build a mapping table based on an article that Jeff Lenning published in the Journal of Accountancy, “The Power of Mapping.” I needed to map the 18 categories that Corporate’s system financials used into my 46 unique program names. The example below from Jeff’s article shows how 6 categories (column PerTB) map to 3 categories (column PerBSheet):

I tried mapping one field from the system financials to our unique program name but ended up needing to use 4 attributes from the system financials (MWC, MAT, Program, Current Year Org). I concatenated these 4 fields that the system financials used into a unique ID (System Financials Unique ID) and then assigned a corresponding unique program nickname to that.

Note: if you are not familiar with concatenation, check out this post and also this one.

This allowed us to roll up all the various concatenated fields into one line on the financials under the unique program name as shown below.

This mapping allowed us so much power in tracking program attributes that we added attributes that had been previously tracked in various offline Excel workbooks such as Unit Definition, Completion Definition, Regulatory Measure, Regulatory Description, Commitments, and Description of Work.

Then we just kept getting wacky with the cheese wiz in discovering new ways to use the mapping tool. Next, we needed to track the units of work that needed to be completed, i.e., complete 500 units for $15,000. Just tracking the $15,000 was not enough. We added a Sub-Nickname column to break apart programs even further. For example, in the above picture all of column G would roll up to one number in the financials, but in a different view, column H would show the four different programs under DO Reliability –49 and how those programs were tracking against planned units that needed to be completed.

The last thing we did with the mapping tool was to add exception reporting. I had another team build a query to compare the four fields that we concatenated from the system of record to our mapping tool. If there was a concatenation that was not in our mapping tool, that record was included in a column labeled Missing. Then, we would add that missing data to our mapping tool so that everything would tie to Corporate’s financials.

I do not want to gloss over the last sentence. This was the cherry on top. We were able to compare our organizational financials to corporate’s financials and the numbers tied to within .0002%. If the numbers didn’t tie, we would go to the mapping tool and find what was missing. It was usually as simple as a Program being changed from Reliability to Electric Reliability.

In the end, the process of refreshing the financial data was automated using off-the-shelf Microsoft tools, mainly Power Query and Power BI. It took 6 months to build and work out the kinks and has saved 40 eight-hour days (320 hours) of work since its completion in addition to all the other benefits previously discussed.

Success Story

There are many benefits of having a robust and automated process for the financials. Here is one type of success story. Sometimes, program managers would challenge a number presented on my financial dashboard. For example, if a program manager had a different variance between plan and actual. I would immediately panic, afraid that I did something wrong. When I investigated the disconnect, it turned out that my dashboard was right–because I had focused on building a process. The financials that were sent to the field team with the specific project financials were also correct. The issue was that the corporate financials included specific work that was labeled incorrectly in the system of record. As a result, it wasn’t included when the specific project financials were sent to the program manager. The program manager was able to see this and was able to immediately correct the mislabeled work. Everything tied. AND the underlying data in the system of record was corrected so that data quality was improved and more accurate going forward. So far, the dashboard is 37 to zero in defending challenges, but who’s counting?

Conclusion

Now, every report/dashboard I build is a process. I learned Power Query and Power BI as I went through each step in the process. I watched several of the hour-long training videos until I had a basic understanding of the tool, and then I was able to search YouTube for the specific solutions as I ran into issues. Since this effort, I have followed the same process for all my recurring-use workbooks. These are the steps I take:

• Automate manual steps
• Anticipate errors throughout the process
• Continuously train to use more of capabilities of these Microsoft tools
• Welcome challenge—stress testing the process is an excellent way to build a robust system

Additional Resources

If you’d like to learn more about the Excel topics discussed, here are links with additional information:

• Concatenation
• Mapping
• Speed Loop
• Power Query

Have any questions? Contact us to get in touch with post author Larry Peck.