Scope of Work Spreadsheet: Beta V3
Adding automations to create a simpler scope of work spreadsheet...
Over the last year that I’ve been posting here on substack I’ve made it a habit to share my progress building our custom scope of work spreadsheet aka our rehab estimator.
Throughout the years it’s become more and more accurate and more and more user friendly. I’ve recently made some serious upgrades and have added more automations and efficiencies that are absolutely game changer.
The purpose of this post is to share how I’ve developed this “business application” if you will as well as hopefully inspiring my readers to build similar tools for their businesses to maximize efficiency and ultimately more predictability.
Old Process
The biggest sticking point I had with my scope of work spreadsheet was that it wasn’t mobile friendly.
I would need to manually go into my template spreadsheet on my computer, fill out all the necessary fields and click the create button for a copy to be made and sent to the spreadsheet to the necessary folder. With the formulas and the template duplication automation I was able to take the creation of our rehab estimates from 10 minutes down to 2-3 minutes. I also had an automation setup that would create a photos folder for a property once we scheduled a walkthrough date using Google Calendar. This added to the efficiency of adding photos to our drive but it was always an extra step having to go onto my phone, search for the address in google drive and then add the photos into the folder.
I felt that I could accomplish creating a scope of work spreadsheet, and adding photos at the same time essentially killing two birds with one stone and potentially more than two birds because there were multiple other tasks I could accomplish…
*Enter Webform…*
Updates
Here are the updates I’ve made
Design/Branding of the Spreadsheet
Webform Form submission
Mobile Friendly
Create Scope of Work
Create Photo Folder
Send Information to Slack Channel
Estimate Vs Actual Dashboard
The Webform was really a genius idea to bring a ton of things together. It was customizable, easy to build (kinda), allowed for data to be entered easily and could have an attachment feature to send photos to our drive.
I wanted to add more design/branding to the spreadsheet so I make it look more fancy to make it more professional looking.
I also wanted to add another layer of functionality which was when the spreadsheet is created I didn’t want it to share all the hidden sheets that control the formulas because these spreadsheets we share with lenders and I don’t want them being able to duplicate my hard work aka I wanted to keep it as proprietary as possible.
Webform Walkthrough
For the purpose of this write up I’m sharing what the webform looks like on my computer but the reason why I built it to begin with was to be able to create renovation estimates out in the field and not have to wait until I was in front of my computer.
The first step is opening up the Webapp I’ve created and typing in the address of the property and the unit and sq ft information. I felt the Google Maps was necessary so that it made the webform more visually appealing and ensured that when the address was entered you could confirm that it was the right location by looking at the map.
Once that is filled out the next step is filling out the drop down menus and check boxes for the work that needs to be done and entering in the necessary units for things like roof, siding, and windows.
After that I add the photos of the property to the file attachment and press upload.
This is where the magic happens…
Once I’ve uploaded the webform a few things happen.
A Photos folder named after the address is created in the correct Parent Folder
A copy of Scope of Work spreadsheet is created with all the entered data
The Address, Link to Photo Folder and Link to Scope of Work spreadsheet is sent to a specific Slack Channel via a slack chat bot I named Jebi-Wan Kenobi.
This part seems like it isn’t that important but in reality it’s one of the most important features because it serves as a review process. I wanted to make sure that I could get the links to the spreadsheet and folder sent to a slack channel to make it easy to review the photos and spreadsheet and make adjustments where necessary. This is especially important if you are not the one that’s manually filling out the webform and you have someone else doing it. This to me is the perfect way to do a quality control check as well as centralize the information so when it comes to making offers it’s nice and easy to go to the specific slack channel and find the information you need as opposed to sifting through Google Drive to find it.
This whole process reduced the creation of Rehab Estimates down to little 1 minute from 2-3 minutes and allows me to only focus on reviewing the spreadsheet and adjust the areas that need to be adjusted manually if needed and be able to make offers a lot faster.
I already have more things I’ll be adding to the form and expect that in the next month or so I’ll be taking this thing to even higher levels by adding more drop downs and more options that’ll add more automations.
Here are some ideas and things I’ll be discussing in future posts as it relates to this workflow…
Adding dropdown for average interior cosmetics labor costs based on drop down menu (Light, Medium, Heavy)
Automate the underwriting process by estimated renovation costs in underwriting spreadsheet and automating the offer process further
Create marketing emails for our deals once we get them under contract using the Scope of Work spreadsheet data