Data & Reports
First, I want to say thank you. If you're like me, a lot of things are pulling at your attention, thank you for spending some of it with me.
The weather is warming up here in the Pacific Northwest, every evening I go for a walk there's a mix of bugs chittering, dogs barking far off, and someone is always grilling chicken.
tip
Keep data separate from reports.
Here’s why:
- If your report format changes, or you need to start tracking something additional, you can use formulas to create them dynamically. Filtering information is always going to be easier than changing an entire report.
- You only see the actual information you need, but can add context for clarity later.
- By keeping them separate, it reduces the size of your reports to being able to fit on one screen. Instead of scrolling into oblivion.
- Finally, this is how spreadsheets were designed, to look up data from one place and summarize it in another. When you combine data and reports in the same sheet, the formulas can get really complicated and your spreadsheet can get incredibly difficult to update and use.
Some of the formulas that I use most often are:
Almost every report that I create uses these formulas. If you start with them (I've linked to some of the best Excel teachers today), you'll be miles ahead of most.
If you get stuck, my email is blog@danielphayward.com
project highlight
Today's project is a material tracker, which you can download the excel version or the google sheets version. This is a simple version of the concept I explained above. The idea here is that you have a list of cost codes on one page and related transactions on another.
Let's pretend that you have budgeted 20,000 yards of dirt because you're building another new neighborhood right next to my house and you don’t like building houses on a sledding hill. On the report page you would create a table that includes a budgeted amount of dirt with a unique ID (cost code) that will match a different column in the daily data that you enter.
Once the project is underway and you have a bunch of trucking tickets, with chicken scratch from truck drivers all named Sal (or is it Sam?) you’ll enter all the tickets into their new home on the data page.
For the data page, you'd include a few necessary columns, and some that might be useful later. Bonus Tip: Each row should be related, and each cell should have a single piece of information.
- date
- amount of dirt delivered
- ticket number
- cost code
- description
- material
It then becomes very easy to include on the report page how much is left remaining for each cost code, and this you can have way before you have gotten any invoices. If you've used 80% of the dirt but aren't done yet, you can figure out why and get out ahead of problems.
With these pieces of info, if you know what each material cost is you can have a pretty good estimate of what invoices you'll receive. When you get an invoice that includes ticket numbers, you can verify that you match. You aren't just relying on the supplier/subcontractor’s information.
If you’re interested in reading further, here’s the addendum.
(un)related reading
- A city with no laws and 100x the density of New York
- Why it's hard to innovate Construction by Brian Potter
- Salary Negotiation (long read, not be mobile friendly)