Example of Advanced Smartsheet Reporting & Interconnectivity

Nick Burrus

Hello,

I have built a series of Smartsheet that collects data from 8 different sources and combines it into one easily accessible Dashboard that is interlinked with other Dashboards. We use forms to collect responses and store them in 6 Data Files. In addition we have a special Key File for each project, that tracks that project's billables automatically. 

In the end we now have a completely automated system that is hands off, and relies off of employee end of day reporting. Supervisors can see profitability that pulls in references from a secret wage table. There are over 150 different full reference values generating cell links automatically. 

Basically, let me show you what is going on  

Note: All names are fake names from my favorite book series. 

The Hub: 

This hub is the homepage of our Dashboard system and is the default home page for all employees. Here employees can find important information and links. This is still under construction and I am still working on it. But for now it meets my needs:

The Forms:

We have a variety of forms to collect information. Each form collects different information relating to a workflow step. These forms write to a Data File regarding it's step. Here is one of the data entry forms:

Data File

Here is an example of one of our multiple data files. This data file has our employee names pixelated. But, basically everything reported each day is recorded. We record Project Name, how long they worked (down to the minute). We then use LENS formulas to figure out what the heck the duration is because Smartsheet can't do time math, I hacked it. This produces a value for for multiplication. The weight uses the shift value to determine what a person would've done if they had spent a full shift doing said thing. This puts everyone on equal ground. 

You can see a variety of references on this. We reference the checkboxes so we can hide/show data, the projects for filtering, along with the weighted numbers. In addition we track the amount of "boxes" done which helps us determine our averages for our key files. 

Key File:

We have "Key Files" that go along with our "Data Files". These are hugely interconnected. They reference each other back and forth. They have a symbiotic relationship and they depend upon one another to work. We have a three-way symbiotic relationship with Key, Data, and Dashboards. 

This is an example of one of our many Key Files. Each Project has its own. This breaks down the steps with the amount of time we allot for each step. This also has our average wages for the said step. This generates a list of what our profit for said step should be based upon their percentage of alotted time. We put the value of the box in manually. Everything else updates on it's own including the averages (clips/pages/documents).

Team Files:

One of the bread and butters of all of this interconnectivity is a mini reporting/dashboard in Smartsheet. This one will show all of our employee names, along with their average in the past 30 days. We can then break down the total they did, amount of days, along with their profit. We can also see if they hit our revenue goal of 60% profit or not. Along with this we use the new RANK EQ to determine the top performers. We pull in our "Reported Issues" along with this.

We use a secret "Wage" table, which I am not sharing for reasons. These are pulled into these calculations. When an employee gets a raise it is counted against their profitability. So one employee on this list makers almost double what others do, and their margins are still amazing. It helps us determine if someone is worthy of a raise when they ask for one, or if anyone is struggling and costing us money. We have a negative on here, who is an intern with a learning disability. He's part of a special program for a year long training so we can make him profitable

Other Dashboards

We have other Dashboards that show how everyone is doing from step broken down with details that are automatically generated. I can't share some of our "secret" ones we use for performance weights. But here's one of the ones I can share:

Conclusion: 

I hope you guys can see what we're pulling off in Smartsheet. Smartsheet has not shown any examples near this, that we feel. We basically were able to replicate everything we wanted so far. We're pushing hard by requesting features and being vocal about what would make our lives easier. 

I feel like I have mastered Smartsheet and am eagerly waiting for new features to pursue and push this even further. 

I hope this provides some insight of what can be done!

Mike Wilday

You should probably blur out those employee names on the copy of Key Team. Don't want to expose anyone, let alone someone with disabilities. 

Nick Burrus

Those are all names from my favorite book series I replaced the names throughout the example images. 

Mike Wilday

/phew! 

Mike Wilday

That's a very elaborate process. Nicely put together. 

Nick Burrus

Recently I made some more updates to this.

• Now tracks the amount of errors a person makes based upon a confidential error report module available to employees.
• Now has an automatically adjusting expectation and profit ratio formula.
• Use of algebra linear formulas to calculate weights for all employees and make them on equal ground as per their wages.
• Let's us accurately predict who is performing better then others.
• Let's us view stats live and on a scale of how a person improve or decreased over time.

Terri Pavlovic

This is a great example of the power of Smartsheet. Thanks for sharing.

Nick Burrus

Thank you @Terri Pavlovic !

Samuel Courtney

This is awesome! Thanks for sharing. I keep looking for more examples of elaborate and complex smartsheet set ups. This gives me inspiration to keep building and keep being creative! Great Job!