Projekt: ANALIZATOR WYDAJNOŚCI I RENTOWNOŚCI SERWISU KOMPUTEROWEGO
Excel / LibreOffice Calc
Poziom zaawansowany
Czas: 3–4 godziny
1. Cel zadania
Twoim zadaniem jest stworzenie rozbudowanego arkusza kalkulacyjnego do analizy pracy serwisu komputerowego. Arkusz ma automatycznie obliczać przychody, koszty, zyski, premie pracowników oraz wskazywać najbardziej rentowne usługi.
Projekt powinien wykorzystywać zaawansowane formuły, funkcje warunkowe, wyszukiwanie danych, formatowanie warunkowe, tabele przestawne i wykresy.
2. Dane wejściowe
Utwórz arkusz o nazwie Zlecenia i wprowadź poniższe dane.
| ID | Pracownik | Usługa | Liczba godz. | Stawka za godz. (zł) | Części (zł) | Cena usługi (zł) | Status |
|---|---|---|---|---|---|---|---|
| 1001 | Kowalski | Diagnostyka | 1 | 80 | 0 | 150 | Zakończone |
| 1002 | Nowak | Wymiana SSD | 2 | 90 | 250 | 480 | Zakończone |
| 1003 | Wiśniewski | Instalacja systemu | 2 | 85 | 30 | 280 | Zakończone |
| 1004 | Kowalski | Czyszczenie PC | 1,5 | 80 | 20 | 220 | Zakończone |
| 1005 | Nowak | Modernizacja RAM | 1 | 90 | 180 | 300 | W trakcie |
| 1006 | Wiśniewski | Diagnostyka | 2 | 85 | 0 | 150 | Zakończone |
| 1007 | Kowalski | Wymiana zasilacza | 1,5 | 80 | 210 | 390 | Zakończone |
| 1008 | Nowak | Instalacja systemu | 2,5 | 90 | 40 | 320 | Anulowane |
| 1009 | Wiśniewski | Czyszczenie PC | 1 | 85 | 15 | 220 | Zakończone |
| 1010 | Kowalski | Modernizacja RAM | 1,5 | 80 | 200 | 340 | W trakcie |
| 1011 | Nowak | Diagnostyka | 1 | 90 | 0 | 150 | Zakończone |
| 1012 | Wiśniewski | Wymiana SSD | 2 | 85 | 270 | 500 | Zakończone |
| 1013 | Kowalski | Instalacja systemu | 2 | 80 | 35 | 290 | Zakończone |
| 1014 | Nowak | Czyszczenie PC | 1,5 | 90 | 25 | 240 | Zakończone |
| 1015 | Wiśniewski | Wymiana zasilacza | 2 | 85 | 230 | 410 | W trakcie |
Wprowadź nagłówki w wierszu 1, a dane w wierszach 2–16.
3. Część I – obliczenia finansowe
W arkuszu Zlecenia dodaj kolejne kolumny:
- I – koszt robocizny,
- J – koszt całkowity,
- K – zysk na zleceniu,
- L – marża procentowa,
- M – ocena rentowności,
- N – premia pracownika.
Wykonaj poniższe zadania:
- Oblicz koszt robocizny jako iloczyn liczby godzin i stawki godzinowej.
- Oblicz koszt całkowity jako sumę kosztu robocizny i kosztu części.
- Oblicz zysk jako różnicę między ceną usługi a kosztem całkowitym.
- Oblicz marżę procentową jako stosunek zysku do ceny usługi.
- Przypisz ocenę rentowności:
- marża co najmniej 40% – „Wysoka”,
- marża od 20% do poniżej 40% – „Średnia”,
- marża poniżej 20% – „Niska”.
- Przyznaj premię w wysokości 10% zysku, jeśli zlecenie jest zakończone i zysk wynosi co najmniej 100 zł. W pozostałych przypadkach premia wynosi 0 zł.
Wymaganie: wykorzystaj funkcje JEŻELI, ORAZ oraz podstawowe działania matematyczne. Formuły wpisz w pierwszym wierszu danych i skopiuj do końca tabeli.
4. Część II – funkcje zaawansowane
Utwórz arkusz Raport i wykonaj następujące obliczenia.
Zadanie 1. Podsumowanie finansowe
Oblicz:
- łączny przychód z zakończonych zleceń,
- łączny koszt zakończonych zleceń,
- łączny zysk,
- średnią marżę,
- liczbę zakończonych zleceń.
Zadanie 2. Analiza pracowników
Przygotuj tabelę pokazującą dla każdego pracownika:
- liczbę zakończonych zleceń,
- sumę wypracowanego zysku,
- sumę przyznanych premii,
- średnią wartość zlecenia.
Użyj funkcji LICZ.WARUNKI, SUMA.WARUNKÓW i ŚREDNIA.WARUNKÓW.
Zadanie 3. Wyszukiwanie danych
Przygotuj pole, w którym po wpisaniu ID zlecenia automatycznie wyświetlą się pracownik, usługa, status i zysk.
Wykorzystaj WYSZUKAJ.X albo połączenie INDEKS i PODAJ.POZYCJĘ.
Zadanie 4. Filtrowanie
Utwórz dodatkową tabelę wyświetlającą wyłącznie zakończone zlecenia, których zysk przekracza 100 zł.
Wykorzystaj funkcję FILTRUJ, jeżeli jest dostępna w używanej wersji arkusza. W przeciwnym razie użyj filtra zaawansowanego.
5. Część III – analiza usług
Utwórz arkusz Usługi i przygotuj zestawienie dla każdego rodzaju usługi.
Oblicz:
- liczbę wszystkich zleceń,
- liczbę zakończonych zleceń,
- sumę przychodów z zakończonych zleceń,
- sumę zysków,
- średnią marżę,
- udział danej usługi w całkowitym zysku serwisu.
Następnie:
- Posortuj usługi od najbardziej do najmniej rentownej.
- Wskaż usługę generującą największy zysk.
- Wskaż usługę o najniższej średniej marży.
- Zastosuj formatowanie warunkowe, aby wyróżnić trzy najbardziej rentowne usługi.
- Przygotuj tabelę przestawną pokazującą zysk według pracownika i usługi.
6. Część IV – panel menedżerski
Utwórz arkusz Dashboard, który będzie stanowił czytelny panel zarządzania serwisem.
Łączny przychód
Formuła
Tylko zakończone zlecenia
Łączny zysk
Formuła
Po odjęciu kosztów
Zakończone zlecenia
Formuła
Liczba zleceń
Średnia marża
Formuła
Wartość procentowa
Dodaj co najmniej trzy wykresy:
- wykres kolumnowy – zysk według pracowników,
- wykres kołowy – udział usług w zysku,
- wykres słupkowy – przychody według rodzaju usługi.
Panel powinien być czytelny, estetyczny i aktualizować się automatycznie po zmianie danych w arkuszu Zlecenia.
7. Część V – kontrola jakości danych
Wykonaj dodatkowe zadania:
- Zastosuj listę rozwijaną dla kolumny
Status, zawierającą wartości:Zakończone,W trakcie,Anulowane. - Zablokuj możliwość wpisania ujemnej liczby godzin, ujemnej ceny usługi i ujemnego kosztu części.
- Wyróżnij czerwonym kolorem zlecenia anulowane.
- Wyróżnij żółtym kolorem zlecenia, które są w trakcie realizacji.
- Zabezpiecz komórki zawierające formuły przed przypadkową edycją.
- Dodaj komunikat ostrzegawczy, jeśli cena usługi jest niższa niż koszt całkowity.
8. Część VI – Zadanie dla uczniów, którzy ukończą podstawową część projektu.
- A. Symulacja podwyżki: oblicz, jak zmieni się zysk, jeśli ceny wszystkich usług wzrosną o 8%, a koszty pozostaną bez zmian.
- B. Ranking pracowników: utwórz automatyczny ranking według łącznego zysku z zakończonych zleceń.
- C. Analiza progu rentowności: wyznacz minimalną cenę usługi zapewniającą marżę 30%, przy niezmienionych kosztach.
- D. Analiza scenariuszy: porównaj zysk przy wzroście kosztów części o 5%, 10% i 15%.
- E. Automatyczny raport: przygotuj komunikat, który wskazuje najbardziej rentowną usługę i pracownika z największym zyskiem.



