Arrow-Tip #57 Creating Metrics to Illustrate KPIs Part II
In Arrow-Tip #56 we discussed creating metrics to illustrate Safety and Quality KPIs for my client company Gulf Coast Welding (GCW). In this Arrow-Tip we will cover Profitability and Revenue KPIs.
KPI: Profitability
Metric 1: Average Labor Cost pe Hour– Average cost of labor at GCW includes burden and overhead loaded on top of hourly wages. we calculated this metric on a yearly rolling average since GCW’s business tends to have some seasonal highs and lows.
Why we chose it: The most significant fixed cost for GCW is labor or payroll. However, burden and overhead are the real variable since their value per labor hour goes down when the shop is loaded with the optimum amount of business (enough hours to keep most shop employees billable, but not working excessive overtime).
Data collection: Data for this metric is generated by GCW’s proprietary accounting system.
Metric 2: Average Indirect Man Hours per week – This is time spent cleaning up the shop, maintaining equipment or otherwise not working on a billable job.
Why we chose it: Some indirect is a good investment in maintaining and improving GCW operations, but too much obviously causes too big of a load on the burden and overhead rate.
Data collection: Hours for each job or indirect cost center are captured by the GCW Plant Superintendent manually on time cards. The time cards are handed over to the Office Manager who inputs them into a Microsoft Excel spreadsheet. Indirect hours along with hours for jobs where current hours exceed those in the quoted estimate are added together for the week.
KPI: Revenue
Metric: Pipeline Value– This metric is a measure of the potential revenue for jobs currently quoted, but not closed weighted by probability of closing depending on where they are in the sales cycle. Wow – that is a mouthful! Never fear – I’ll be releasing an e-book on creating meaningful revenue forecasts using Outlook and Excel later this summer.
Why we chose it: Steel fabrication in the Texas Gulf Coast is a highly cyclical business. In the good years, you really don’t have to do much in the way of sales other than have a great reputation and a standing place on some key approved vendor lists. But in the bad years, keeping a steady flow of inquiries coming in can be a real challenge that requires outside sales efforts. GCW has never had an outside sales force so some of their key employees are working to fill that gap and the pipeline helps assure that enough time is allocated to sales prospecting activities.
Data Collection: The Vice President at GCW responsible for handling inquiries inputs new requests for proposal or quotation onto a quotation log in Microsoft Excel. This VP assigns a percentage of the sales cycle (0% Budget Estimate, 10% Funded RFQ, 50% Short List, or 80% Verbal Confirmation) to the inquiry, a projected close date and an estimated revenue for the job. Obviously if a project is earlier in the sales cycle (pre quote), the estimate is very rough as opposed to after a quote has been prepared. The sum of the products of projected revenue and sales cycle precentage for jobs projected to close within the next 12 months is calculated in the quotation log spreadsheet.
Next in the Using SharePoint to Communicate KPI Dashboards series, we will cover publishing KPI metrics using Microsoft Excel.
Comments
No comments yet.