1 TIP – cieniasy

Do nauki po wolnym weekendzie – będą kartkówki co tydzień !!

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.

Zadanie projektowe: „Optymalizacja budżetu wycieczki klasowej”

Klasa planuje 3-dniową wycieczkę dla 28 uczniów i 2 opiekunów. Dysponuje budżetem 24 000 zł.

Należy zaplanować wycieczkę tak, aby:

  • zmieścić się w budżecie,
  • zapewnić transport,
  • zakwaterowanie,
  • wyżywienie,
  • co najmniej 3 atrakcje,
  • pozostawić minimum 500 zł rezerwy.

Dane

ElementKoszt
Autokar4 800 zł
Nocleg – osoba/noc120 zł
Śniadanie – osoba25 zł
Obiad – osoba40 zł
Kolacja – osoba30 zł
Muzeum25 zł/os.
Park nauki35 zł/os.
Centrum technologii40 zł/os.
Rejs45 zł/os.
Gra miejska20 zł/os.

Polecenia

1. Obliczenia matematyczne

  1. Oblicz koszt zakwaterowania całej grupy na 2 noce.
  2. Oblicz koszt wyżywienia jednej osoby przez 3 dni, zakładając:
    • 3 śniadania,
    • 3 obiady,
    • 2 kolacje.
  3. Oblicz koszt wyżywienia całej grupy.
  4. Oblicz koszt każdej atrakcji dla całej grupy.
  5. Ustal, ile pieniędzy pozostanie po opłaceniu transportu, noclegów i wyżywienia.

2. Analiza wariantów

Przygotuj co najmniej 4 różne warianty wycieczki, wybierając po 3 atrakcje.

Dla każdego wariantu oblicz:

  • koszt atrakcji,
  • koszt całej wycieczki,
  • koszt przypadający na jednego uczestnika,
  • pozostałą rezerwę.

3. Excel

W arkuszu kalkulacyjnym utwórz tabelę zawierającą:

WariantAtrakcjeTransportNoclegiWyżywienieAtrakcjeRazemKoszt/os.Rezerwa

Zastosuj:

  • formuły,
  • adresowanie względne i bezwzględne,
  • funkcję SUMA,
  • formatowanie warunkowe,
  • sortowanie wariantów według kosztu.

4. Zadanie algorytmiczne

Zapisz w postaci listy kroków algorytm, który dla dowolnej liczby uczestników:

oblicza całkowity koszt wycieczki i sprawdza, czy wybrany wariant mieści się w określonym budżecie.

Algorytm powinien zawierać co najmniej jeden warunek:

JEŻELI koszt całkowity ≤ budżet, wyświetl „WYCIECZKA MOŻLIWA”, w przeciwnym przypadku „PRZEKROCZONO BUDŻET”.

odpowiedzi

arkusz RAPORT

Utwórz arkusz Raport.

Zadanie 1. Podsumowanie finansowe

Przyjmij następujący układ komórek:

KomórkaOpisFormuła
B2Łączny przychód=SUMA.WARUNKÓW(Zlecenia!G2:G16;Zlecenia!H2:H16;"Zakończone")
B3Łączny koszt=SUMA.WARUNKÓW(Zlecenia!J2:J16;Zlecenia!H2:H16;"Zakończone")
B4Łączny zysk=SUMA.WARUNKÓW(Zlecenia!K2:K16;Zlecenia!H2:H16;"Zakończone")
B5Średnia marża=ŚREDNIA.WARUNKÓW(Zlecenia!L2:L16;Zlecenia!H2:H16;"Zakończone")
B6Liczba zakończonych zleceń=LICZ.JEŻELI(Zlecenia!H2:H16;"Zakończone")

Prawidłowe wyniki

Łączny przychód

3 070 zł

Łączny koszt

2 335 zł

Łączny zysk

735 zł

Średnia marża

26,97%

Zakończone zlecenia

11

Uwaga: średnia marża 26,97% jest średnią arytmetyczną marż poszczególnych zakończonych zleceń. Marża liczona od łącznego przychodu i zysku wynosi 23,94%.

Zadanie 2. Analiza pracowników

W arkuszu Raport przygotuj tabelę:

PracownikLiczba zleceńŁączny zyskPremieŚrednia wartość zlecenia
Kowalski4305 zł0 zł262,50 zł
Nowak3190 zł0 zł290 zł
Wiśniewski4240 zł12 zł287,50 zł

Zakładamy, że nazwiska znajdują się w komórkach A10:A12.

Formuły dla pierwszego pracownika (wiersz 10):

Liczba zakończonych zleceń:

=LICZ.WARUNKI(Zlecenia!B$2:B$16;A10;Zlecenia!H$2:H$16;"Zakończone")

Łączny zysk:

=SUMA.WARUNKÓW(Zlecenia!K$2:K$16;Zlecenia!B$2:B$16;A10;Zlecenia!H$2:H$16;"Zakończone")

Łączne premie:

=SUMA.WARUNKÓW(Zlecenia!N$2:N$16;Zlecenia!B$2:B$16;A10)

Średnia wartość zlecenia:

=ŚREDNIA.WARUNKÓW(Zlecenia!G$2:G$16;Zlecenia!B$2:B$16;A10;Zlecenia!H$2:H$16;"Zakończone")

Przeciągnij formuły do wiersza 12.

Zadanie 3. Wyszukiwanie danych

Przygotuj pole wyszukiwania:

  • B2 – wpisywany numer ID,
  • B3 – pracownik,
  • B4 – usługa,
  • B5 – status,
  • B6 – zysk.

Przykładowo wpisz w B2 numer 1009.

Formuła dla pracownika:

=WYSZUKAJ.X(B2;Zlecenia!A2:A16;Zlecenia!B2:B16;"Brak danych")

Formuła dla usługi:

=WYSZUKAJ.X(B2;Zlecenia!A2:A16;Zlecenia!C2:C16;"Brak danych")

Formuła dla statusu:

=WYSZUKAJ.X(B2;Zlecenia!A2:A16;Zlecenia!H2:H16;"Brak danych")

Formuła dla zysku:

=WYSZUKAJ.X(B2;Zlecenia!A2:A16;Zlecenia!K2:K16;"Brak danych")

Przykładowy wynik wyszukiwania ID 1009

PoleWynik
PracownikWiśniewski
UsługaCzyszczenie PC
StatusZakończone
Zysk120 zł

ecenia!H$2:H$16;”Zakończone”)

Zysk:

=SUMA.WARUNKÓW(Zlecenia!K$2:K$16;Zlecenia!C$2:C$16;A2;Zlecenia!H$2:H$16;”Zakończone”)

Średnia marża:

=ŚREDNIA.WARUNKÓW(Zlecenia!L$2:L$16;Zlecenia!C$2:C$16;A2;Zlecenia!H$2:H$16;”Zakończone”)

Udział w zysku:

=E2/SUMA($E$2:$E$6)

Skopiuj formuły do wiersza 6.

Wnioski z analizy

  1. Najbardziej zyskowną usługą jest czyszczenie PC – 280 zł zysku.
  2. Najniższą średnią marżę ma wymiana SSD – 11,21%.
  3. Czyszczenie PC odpowiada za 38,10% całego zysku serwisu.
  4. Instalacja systemu zajmuje drugie miejsce pod względem wygenerowanego zysku – 175 zł.

Tabela przestawna

Utwórz tabelę przestawną na podstawie danych z arkusza Zlecenia.

Ustaw pola:

  • Wiersze: Pracownik.
  • Kolumny: Usługa.
  • Wartości: Zysk – suma.
  • Filtr: Status – wybierz Zakończone.

W ten sposób otrzymasz analizę zysków poszczególnych pracowników z podziałem na rodzaje usług.

5. Część IV – panel menedżerski DASHBOARD

Arkusz Dashboard powinien prezentować najważniejsze informacje w postaci dużych wartości liczbowych i wykresów.

Wskaźniki KPI

Wstaw następujące formuły:

WskaźnikFormuła
Przychód=Raport!B2
Koszty=Raport!B3
Zysk=Raport!B4
Średnia marża=Raport!B5
Liczba zleceń=Raport!B6

Wykres 1 – zysk według pracowników

Wykorzystaj dane z tabeli pracowników w arkuszu Raport.

Zysk według pracowników

Zysk z zakończonych zleceń

0 zł85 zł170 zł255 zł340 złKowalskiNowakWiśniewski

Wykres 2 – udział usług w zysku

Udział usług w zysku

Procentowy udział poszczególnych usług w zysku serwisu

Czyszczenie PC

Diagnostyka

Instalacja systemu

Wymiana SSD

Wymiana zasilacza

Wykres 3 – przychody według usług

Przychody według usług

Przychody wyłącznie z zakończonych zleceń

Diagnostyka

Przychód450 zł

0 zł300 zł600 zł900 zł1,2 tys. złDiagnostykaWymiana SSDInstalacja systemuCzyszczenie PCWymiana zasilacza

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