Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.19 IT Services: Utilisation and Billing

By Last updated:

Example first. Raja runs delivery for a fictional IT services firm with teams in three cities and wants utilisation and billing for the month.

IT Services project — in 30 seconds

What: A IT Services tracker: one Table tblProjects, the calculated column Utilisation, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real IT Services report: Total Revenue, Overall Utilisation, Projects below 75% are the numbers a manager reads first.

Tiny example: From 6 rows: Total Revenue = ₹1,17,60,000, Overall Utilisation = 79.1%, Projects below 75% = 2.

First look at the data (before → after):

IT Services project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated columns

Project Utilisation Revenue
PRJ-A 88.8% ₹25,56,000
PRJ-B 61.3% ₹14,70,000
PRJ-C 86.8% ₹20,00,000
PRJ-D 63.3% ₹10,64,000
PRJ-E 94.4% ₹30,20,000
PRJ-F 76.4% ₹16,50,000

Expected result — KPI cells (expected results)

KPI Formula Result
Total Revenue =SUM(tblProjects[Revenue]) ₹1,17,60,000
Overall Utilisation =SUM(tblProjects[BilledHours])/SUM(tblProjects[AvailableHours]) 79.1%
Projects below 75% =COUNTIF(tblProjects[Utilisation],"<0.75") 2
Before table and formula as text

Before — Table tblProjects (fictional sample rows)

Project City BilledHours AvailableHours Rate
PRJ-A Pune 1,420 1,600 1,800
PRJ-B Pune 980 1,600 1,500
PRJ-C Nagpur 1,250 1,440 1,600
PRJ-D Nashik 760 1,200 1,400
PRJ-E Pune 1,510 1,600 2,000
PRJ-F Nagpur 1,100 1,440 1,500

Formula / click path

Utilisation: =[@BilledHours]/[@AvailableHours]
Revenue: =[@BilledHours]*[@Rate]
Total Revenue: =SUM(tblProjects[Revenue])
Overall Utilisation: =SUM(tblProjects[BilledHours])/SUM(tblProjects[AvailableHours])
Projects below 75%: =COUNTIF(tblProjects[Utilisation],"<0.75")

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblProjects in Table Design › Properties › Table Name.
  2. Add the column Utilisation with =[@BilledHours]/[@AvailableHours]; the Table fills it down. Add the other calculated columns from the table below the same way.
  3. On a KPI sheet type =SUM(tblProjects[Revenue]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and Revenue in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Project.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a delivery sheet: utilisation and revenue per project, total revenue, overall utilisation (billed ÷ available hours), projects below 75% utilisation, revenue by city, a chart and a Project slicer.

Calculated column formulas:

Column Formula
Utilisation =[@BilledHours]/[@AvailableHours]
Revenue =[@BilledHours]*[@Rate]

KPI cells (check your numbers):

KPI Formula Result
Total Revenue =SUM(tblProjects[Revenue]) ₹1,17,60,000
Overall Utilisation =SUM(tblProjects[BilledHours])/SUM(tblProjects[AvailableHours]) 79.1%
Projects below 75% =COUNTIF(tblProjects[Utilisation],"<0.75") 2

PivotTable (expected):

City Sum of Revenue
Nagpur ₹36,50,000
Nashik ₹10,64,000
Pune ₹70,46,000
Grand Total ₹1,17,60,000

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Project and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template IT_Services_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of Revenue?
  2. Filter the Table on City = Pune (Data › Filter): what is Total Revenue for Pune alone?
  3. Add the Clustered Column PivotChart and the Project slicer to your Dashboard sheet.
Answer

Pune (₹70,46,000). For Pune only (3 rows): Total Revenue = ₹70,46,000. The chart shows 3 bars, one per city.

Ravindra Bagale's Tip

Keep the calculated column Utilisation inside the Table. New rows then get the formula automatically, and the PivotTable only needs Refresh.

Loading practice tasks...

  1. Total billable hours (Easy): Add up Billable hours in D2:D11.
  2. Average rate per hour (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cloud (Easy): How many rows have Practice "Cloud"?
  4. Largest billable hours (Easy): The largest value in D2:D11.
  5. Billable hours for Cloud (Medium): Total of D2:D11 where B is "Cloud".
  6. Cloud in Denver (Medium): Total of D2:D11 where B is "Cloud" and C is "Denver".
  7. Best in Cloud (Medium): Largest E2:E11 value where B is "Cloud".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Aarav Shah (Medium): The Billable hours for "Aarav Shah" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Cloud (Hard): Share of D2:D11 that is "Cloud", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →