Switcher

Excel + VBA application for project cost estimation and contract creation.

From Curiosity to Immediacy

During the Autumn of my first full-time job out of college, I was assisting our sales department with a web-based piece of software, Recurve, which was used to create energy audit reports and produce proposals for potential construction projects.

Having recently completed my undergraduate degree in economics, I was curious as to how the project costs were estimated “under the hood” based on observable input parameters, such as the SQFT of a building.

I spent Thanksgiving break deriving these production functions, which effectively contained coefficients reflecting the productivity for a given installation measure, along with the labor rate per hour, markups, and various capital costs.

A few months later, Recurve abandoned their software and left us with an immediate need to find a new software solution. I showed my manager the spreadsheet that I had been working on and he encouraged me to “run with it” with his full support.

The result was Switcher.

Connecting Sales and Production

Switcher became a vital piece of software that connected multiple departments in our company.

Auditors could use the application to import utility bill data and photos to create beautiful audit reports. Salespeople could input observable features of the building to estimate costs and create proposals and contracts.

Switcher also served as an efficient tool to “bridge the gap” in communication between sales and install departments by producing project detail forms and checklists for our construction managers.

Under the Hood

Switcher utilized Excel’s mathematical, logical and indexing-based functions for project pricing. Additionally, Switcher made extensive use of macros in Visual Basic in order to create proposals and audit reports.

The proposal macro exported a structured, formatted and emphasized Word document that included contract language and pricing for each line item.

The audit report (exported as a .pdf included macros for utility bill disaggregation, widgets to import and resize photos, and charts showing modeled energy-saving effects of various energy-efficiency upgrades.

UX/GUI

Switcher was most useful to auditors and salespeople who, uniquely and importantly, were often the same individual.

This fact framed many UX/design decisions, such as choosing to organize each worksheet around on a particular measure and having audit report page inputs side-by-side with pricing inputs on the same worksheet. This streamlined the experience and permitted a simple, accessible GUI.

For example, the HVAC system’s ductwork (an under-rated component for energy efficiency) had its own worksheet with report page parameters such as existing duct leakage along with a pricing calculator for new ductwork. The text for the report and proposal could be edited from this same worksheet by clicking a link to open a userform. Also, one could navigate among measures by using a drop-down menu.

Analyzing Production Functions

Initially, I had just derived the production functions, but now I was in the unique position of being a data analyst and programmer (along with having field experience).

Combining all three roles, I was able to humbly ask and answer the question- how well did our production functions match actual productivity?

Answering this question would allow us to estimate both the accuracy of our production functions and the actual profit margins for each line-item. The results would effectively model the productive capacity of the firm.

After completing my research, I shared the insights with our company President. This led to some changes in function parameters to better reflect reality. I also came to understand that each company tells a different story about prices. While my story was empirical, management’s story was about market constraints. In a Hegelian way, both stories were right.

Retired

I believe that Switcher was a success due to good timing and its cost of internalization relative to market competitors. Another factor was our company culture, including the support of management and the willingness of our sales and install teams to adopt it and provide valuable guidance on development.

Before I left our company in 2015, Switcher had also been leased to two other home performance contractors in California. I guess there was a market for it after all!

Switcher is now retired, as is Advanced Home Energy, so I’m sure it’s fine to share it with you.