What is an Excel Dashboard?
A dashboard is usually a single pageview of reports comprising of charts, tables and other summaries of data that provide a high-level visual overview of your business.
Job Costing
Urban Landscapes
Challenge
This client had an existing job costing google sheet that they used to create estimates for jobs. They were looking to improve consistency in their cost estimates and to reduce the time required to manually reset calculations for each time a job included different items.
They also had a number of other requirements
-
a view of the job across a timeline / gantt chart
-
the ability track and then report on labour allocated to jobs
-
simple and quick reports to track materials and other items for suppliers
How we help with Dashboards?
Solution
We used their existing Google Sheet as a platform for the job costing solution. This decision was made due to obvious sharing & operating online benefits Google Sheets was providing the business.
Using google scripts we developed a sidebar to add to their existing sheet to allow the users to select scope items to be included to a job. The sidebar also managed the removal of items and the clearing of the job costing if required.
Once the scope items were set to the estimate sheet a series of reports were developed to achieve the output requirements for the project. These were also delivered through the use of google scripts. The reports included:
-
custom format for import a 3rd party system
-
gantt chart showing a schedule of activity for the job
-
supplier and quantity list of materials
-
totals for project, materials, labour, subcontractor and deliveries
-
chart breakdown of materials, labour, subcontractor and deliveries
Another useful component of the solution was the addition of another custom sidebar that allowed the foreman to capture and record labour allocated for the job while on site using an iPad device.
This data was then used to run the labour allocation report to assess the estimate vs actuals for the labour forecast on the job.
Outcome & Benefits
The key benefits for this project for this client were:
-
time saved by a consistent and accurate job costing method
-
centralised list of job items controlled by the administration team
-
simple and easy method to measure the job costing for schedule and labour
-
job costing sheets immediately available to everyone in the team through the G-Suite platform
The other benefits included improved usability of the job costing process. With all the complexity in the process now scripted via google script other team members are able to create and modify job costings.
