3 TI

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.

IDPracownikUsługaLiczba godz.Stawka za godz. (zł)Części (zł)Cena usługi (zł)Status
1001KowalskiDiagnostyka1800150Zakończone
1002NowakWymiana SSD290250480Zakończone
1003WiśniewskiInstalacja systemu28530280Zakończone
1004KowalskiCzyszczenie PC1,58020220Zakończone
1005NowakModernizacja RAM190180300W trakcie
1006WiśniewskiDiagnostyka2850150Zakończone
1007KowalskiWymiana zasilacza1,580210390Zakończone
1008NowakInstalacja systemu2,59040320Anulowane
1009WiśniewskiCzyszczenie PC18515220Zakończone
1010KowalskiModernizacja RAM1,580200340W trakcie
1011NowakDiagnostyka1900150Zakończone
1012WiśniewskiWymiana SSD285270500Zakończone
1013KowalskiInstalacja systemu28035290Zakończone
1014NowakCzyszczenie PC1,59025240Zakończone
1015WiśniewskiWymiana zasilacza285230410W 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:

  1. Oblicz koszt robocizny jako iloczyn liczby godzin i stawki godzinowej.
  2. Oblicz koszt całkowity jako sumę kosztu robocizny i kosztu części.
  3. Oblicz zysk jako różnicę między ceną usługi a kosztem całkowitym.
  4. Oblicz marżę procentową jako stosunek zysku do ceny usługi.
  5. 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”.
  6. 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:

  1. Posortuj usługi od najbardziej do najmniej rentownej.
  2. Wskaż usługę generującą największy zysk.
  3. Wskaż usługę o najniższej średniej marży.
  4. Zastosuj formatowanie warunkowe, aby wyróżnić trzy najbardziej rentowne usługi.
  5. 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:

  1. Zastosuj listę rozwijaną dla kolumny Status, zawierającą wartości: Zakończone, W trakcie, Anulowane.
  2. Zablokuj możliwość wpisania ujemnej liczby godzin, ujemnej ceny usługi i ujemnego kosztu części.
  3. Wyróżnij czerwonym kolorem zlecenia anulowane.
  4. Wyróżnij żółtym kolorem zlecenia, które są w trakcie realizacji.
  5. Zabezpiecz komórki zawierające formuły przed przypadkową edycją.
  6. 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.

Możliwość komentowania została wyłączona.