Lab 04 - Projects, revenue and service

Lab 04 - Projects, revenue and service

Time: 45 minutes Where: Excel (web recommended), files in OneDrive\Školenie Copilot Goal: get answers for the management meeting from data without writing formulas. Chapter: Copilot in Excel

Info

Open the Excel files from OneDrive (not downloaded to disk), otherwise Copilot is not available.

Task 4.1 – Projects over budget (15 min)

Open Zakazky_2025_2026.xlsx and in the Copilot pane:

  1. First overview:
Summarize this data: number of projects by status and division, total budget and actual cost.
  1. Calculated columns:
Add a column "Estimate at completion" = Actual cost / (Completion / 100) for projects with completion above 0, and a column "Variance %" = Estimate / Budget - 1.
  1. Highlighting:
Highlight in red the rows where Variance % is higher than 10 %.
  1. A manager’s question:
Which projects with status "V realizácii" (in progress) have the largest variance? Give the top 5 with project manager and branch.

Check that the top 5 includes ZK-2026-007 Smart križovatky Novohrad and ZK-2026-014 D1 WIM Prešov.

Task 4.2 – Revenue by branch (15 min)

Open Trzby_mesacne_pobocky_2024-2026.xlsx:

Create a PivotTable: revenue by branch (rows) and year (columns) on a new sheet.

Which branch grows fastest and which slowest? Compare January–September 2026 with the same period of 2025 in percent.

Create a line chart of monthly revenue of the Košice and Kraków branches for the whole period.

How does the margin (Revenue - Cost) / Revenue of Košice develop in 2026 compared to 2025? What does it mean?

Task 4.3 – Service and SLA, including data cleanup (15 min)

Open Servisne_tikety_Q3_2026.xlsx:

  1. Check the data first:
Check data quality: are there duplicates, inconsistent region names or empty values? Suggest how to fix them.
  1. Let Copilot unify the region names (Kosice, KOŠICE, B. Bystrica…) – e.g. with a helper column “Region (clean)”.
  2. Analysis:
What percentage of closed tickets met the SLA in each region? What are the 3 most frequent causes of failures in Košice? Show it in a chart.

How does August differ from July and September? Compare failure causes by month.
  1. Bonus – advanced analysis: if Agent Mode or Python analysis is available:
Do an advanced analysis: break down resolution time by priority and region, find the technicians with the highest share of missed SLA and propose 3 measures to improve the SLA in Košice.

Check

  • Projects: I have the Estimate and Variance columns and rows above 10 % are highlighted
  • Revenue: Kraków grows fastest (approx. +30 %), Košice grows but its margin dropped in 2026 from approx. 23 % to approx. 10 %; I have a PivotTable and a chart
  • Service: SLA Košice ≈ 80 %, other regions above 92 %; in August the most frequent cause is “Prehriatie / klimatické vplyvy” (overheating / weather)
  • I verified at least one number myself (filter or sum)