ROZLICZENIE ROCZNE
UWAGI DO ĆWICZENIA:
Celem ćwiczenia jest poszerzenie, oraz zdobycie nowych umiejętności w posługiwaniu się arkuszem kalkulacyjnym MS Excel.
Treść ćwiczenia stanowi w niektórych miejscach uproszczenie rzeczywistych problemów, w tym momencie interesujące jest jednak wykorzystanie arkusza kalkulacyjnego do przykładowych obliczeń.
Trudność poszczególnych zadań jest stopniowana od zadań łatwych do wymagających chwili zastanowienia. Ponieważ kolejne zadania odnoszą się do wcześniejszych poleceń, poszczególne etapy należy wykonywać zgodnie z numeracją, nie pomijając żadnego.
Większość z poleceń można wykonać zarówno "ręcznie" jak i wykorzystując wbudowane funkcje arkusza MS Excel. Zalecane jest stosowanie narzędzi arkusza, gdyż znajomość między innymi tych funkcji będzie sprawdzana w pracach zaliczeniowych.
Zadania do wykonania:
1. Skopiować plik cwiczenie_1.xls do własnego katalogu na dysku H:
2. Otworzyć skopiowany plik w arkuszu kalkulacyjnym MS EXCEL.
3. Zmieniamy nazwę arkusza z Arkusz1 na Produkty.
4. Przed kolumnę A wstawić nową kolumnę i wypełnić ją kolejnymi liczbami od 1, aż do ilości produktów.
5. Dodać wiersz nagłówkowy:
| Lp. | Nazwa produktu | Cena hurtowa szt. netto | stawka VAT | Vat w cenie hurtowej | marża |
6. Sformatować komórki arkusza w następujący sposób:
pierwszy wiersz : zawijaj tekst, autodopasowanie, wyśrodkowanie
szerokość kolumn : ustawić tak, aby tekst w 1 wierszu był tylko raz 1 zawinięty
kolumna C,E - format liczby: waluta, 2 miejsca po przecinku
kolumna F - format liczby: %, 0 miejsc po przecinku
7. W kolumnie E wyliczyć wartość podatku Vat zawartego cenie hurtowej brutto.
8. Pomiędzy kolumny E i F wstawić nową kolumnę: Cena hurtowa szt. brutto i wyliczyć sumę ceny hurtowej netto i Vat.
9. W kolejnej wolnej kolumnie wyliczyć cenę detaliczną brutto (cena hurtowa brutto + marża) i zaokrąglić ją dokładnością do 0,05 zł.
10. Sformatować nowo otrzymaną kolumnę tak jak inne kolumny zawierające kwoty w zł.
11. Dodać 2 nowe kolumny przed kolumnę H i wyliczyć w nich kolejno: cenę detaliczną netto i podatek VAT zawarty w cenie detalicznej brutto (jako wartość wyjściową traktować cenę detaliczną brutto). Poprawić formatowanie kolumn.
12. Utworzyć nowy arkusz o nazwie: Zakupy i Sprzedaż
13. Do nowego arkusz skopiować kolumny A i B z arkusza Produkty
14. Nazwać kolejne kolumny w pierwszym wierszu: Styczeń, Luty .....Grudzień.
15. Skopiować do własnego katalogu na dysku H plik z danymi o zakupach - zakupy.prn.
16. Otworzyć skopiowany plik (plik zawiera dane w postaci pliku tekstowego) w nowym skoroszycie i skopiować dane do arkusza Zakupy i Sprzedaż.
17. Dodać 1 wiersz nagłówkowy : tytuł: ZAKUPY W SZTUKACH, połączyć komórki (kolumna A do N); tekst: wyśrodkowanie, pogrubienie, wysokość 12 pkt, do tabeli dodać ramki.
18. W kolumnie O wyliczyć roczną sumę zakupów dla poszczególnych produktów.
19. Analogicznie jak w pkt. 15-18 utworzyć tabelę z danymi o sprzedaży. Dane o sprzedaży - sprzedaz.txt (Tabelę utworzyć w tym samym arkuszu, poniżej tabeli z danymi o zakupach.)
20. W kolumnie Q wyliczyć stan magazynu na koniec roku.
21. Założyć nowy (trzeci) arkusz: Rozliczenia podatkowe.
22. W nowym arkuszu, w oparciu o dane zawarte w arkuszu Produkty i Zakupy oraz w arkuszu Sprzedaż, utworzyć tabele (każdy produkt w ujęciu miesięcznym): Zakupów netto w zł i Sprzedaży netto w zł.
23. Dla każdego miesiąca obliczyć następujące wartości:
| Suma zakupów netto (skumulowana) |
| Suma sprzedaży netto (skumulowana) |
| Dochód brutto (skumulowany, sprzedaż netto - zakupy netto) |
| Podatek dochodowy należny (skumulowany) |
| Podatek dochodowy zapłacony ( Suma należnych zaliczek za poprzednie miesiące) |
| Podatek dochodowy do zapłaty (należna zaliczka za miesiąc, czyli dodatnia różnica podatku należnego i zapłaconego) |
| Nadpłata podatku dochodowego (ujemna różnica należnego i zapłaconego) |
| Dochód netto (skumulowany) |
| Dochód netto (miesięcznie) |
| SKALA PODATKOWA |
| od | do | podatek |
| 2 727,16 | 0 % |
| 2 727,16 | 37 024,00 | 19 % |
| 37 024,00 | 74 048,00 | 30 % |
| 74 048,00 | | 40 % |
24. Poniżej rozliczenia podatku dochodowego, analogicznie jak w pkt.22. utworzyć 2 tabele zawierające: Podatek VAT naliczony (w zakupach) i Podatek VAT należny (ze sprzedaży).
25. Dla każdego miesiąca dodać zestawienie:
| Suma VAT 22 % |
| Suma VAT 14 % |
| Suma VAT 7 % |
| Suma VAT 0 % |
| Suma VAT zw |
26. Pod tymi tabelami, dla każdego miesiąca obliczyć:
| Vat naliczony (skumulowany, Vat zapłacony przy zakupie towarów w hurtowni + odprowadzony do US VAT do zapłaty z poprzednich miesięcy) |
| Vat należny (skumulowany, Vat zawarty w kwocie sprzedaży) |
| Vat do zapłaty (miesięcznie, dodatnia różnica należnego i zapłaconego) |
| Vat do zwrotu (skumulowany, ujemna różnica Vatu należnego i Vatu naliczonego) |
27. Skonstruować wykres (kolumnowy) dla dochodu netto skumulowanego i dochodu netto miesięcznego:
Oś X: Miesiąc
Oś Y: Kwota [zł]
Czcionki legendy i kategorii osi: 8pkt, Arial
(pozostałe formatowanie tzn: kolory, tło, cień itp. - wg uznania, czytelnie).
28. Zmienić typ wykresu na liniowy, zwiększyć grubość linii na wykresie, dodać znaczniki oraz zmienić ich kształt na wykresie, dodać główne linie siatki osi X.