Często Używane Wzory

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Często Używane Wzory.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Często sama znajomość Excela okazuje się niewystarczająca i konieczna jest znajomość podstawowych wzorów matematycznych.

Wiele razy już przekonałem się nawet osoba po studiach ekonomicznych postawiona przed problemem obliczenia ceny bez VAT mając dane: cenę z VAT wynoszącą 100zł i VAT 23%, ze zdziwieniem stwierdzała, że nie jest tego w stanie zrobić.

Poniżej przedstawiam rozwiązanie problemów z jakimi najczęściej może spotkać się przeciętny pracownik lub kandydat do pracy na stanowisko gdzie wymagana jest znajomość programu Excel.

Procenty

Wzrost procentowy obliczamy z następującego wzoru:

Wzrost % = wartość nowa / wartość stara - 1

Wzór ten zastosowano w poniższym przykładzie do obliczenia wzrostu który wynosi 25%.

Ten sam wzór można także zapisać w poniższej postaci:

Wzrost % = (wartość nowa – wartość stara) / wartość stara

Czasami bywa, że procentowa zmiana z roku na rok bywa obliczana „od drugiej strony”, w poniższym przykładzie możemy powiedzieć, że sprzedaż w 2013 roku była o 20% mniejsza niż w 2014. Odbiorcy takiego komunikatu nie zastanawiając się długo zapamiętują, że różnica wynosi 20%, gdy tak naprawdę jak to obliczyliśmy w przykładzie 1 (ten przykład i poprzedni mają te same dane) wynosi ona 25%.

Jest to celowe wprowadzanie w błąd odbiorcy i nie zalecam takiego podejścia, chyba że ktoś zajmuje się polityką i musi przyjąć obowiązujące w tej branży standardy.

Aby obliczyć udział procentowy np. danego produktu w całości sprzedaży dzielimy sprzedaż tego produktu przez całość sprzedaży.

Udział procentowy = sprzedaż A / całkowita sprzedaż

Do zwiększenia danej wartości o procent np. zwiększenie ceny o 23% podatku VAT służy poniższy wzór:

Wartość z VAT = Wartość bez VAT * (1 + procent zmiany)

Analogicznie wygląda zmniejszanie o dany procent, z tą różnicą że zamiast znaku plus jest minus:

Nowa wartość = Stara wartość * (1 - procent zmiany)

Czasami procent o który mamy coś zmniejszyć podawany jest z minusem (zmniejsz o -20%), co teoretycznie jest błędem (dwukrotny minus powinien dawać plus), ale niestety jest popularne i należy to rozumieć jako zmniejszenie o 20%. W takim przypadku używamy takiego samego wzoru jak przy zwiększaniu o procent (minus już jest przy %).

Nowa wartość = Stara wartość * (1 + procent zmiany)

Zupełnie czym innym jest zmniejszenie o procent a czy innym ‘cofnięcie’ procentu, który został dodany.

W poniższym przypadku z ceny zawierającej VAT wynoszący 23% chcielibyśmy obliczyć cenę bez VATu.

Użyjemy poniższego wzoru. Do dodawania określonego procentu mnożyliśmy przez 1+procent, do cofnięcia tej operacji podzielimy przez 1+procent (działaniem odwrotnym do mnożenia jest dzielenie).

Wartość bez VAT = Wartość z VAT / (1 + procent zmiany)

Jak widać w powyższym przykładzie udało się uzyskać oryginalną wartość ceny bez VAT pokazaną dwa przykłady wyżej.

Wiele osób zadaje pytanie dlaczego działaniem odwrotnym do zwiększenia o jakiś procent nie jest zmniejszenie o ten sam procent.

Prześledźmy poniższy przykład w którym cena została zmieniona dwukrotnie.

Cena początkowa wynosiła 100zł, została ona powiększona o 10% i po tej zmianie wynosiła 110 (10% ze 100 to 10, 10+100=110).

Cena po pierwszej zmianie została obniżona o 10% i ostatecznie wyniosła 99zł (10% z 110 to 11. 110 -11 to 99).

Jak widać cena nie wróciła do swojej oryginalnej wielkości. Im procent zmiany byłby większy tym większa byłaby także różnica pomiędzy ceną końcową i początkową.

W poniższym przykładzie wartość pewnej inwestycji wzrasta o 10% co roku, chcielibyśmy obliczyć po ilu latach wartość ta podwoi się.

W pierwsze z zielonych pól wprowadzamy wzór na wzrost o procent i przeciągamy go do poniższych komórek. Na podwojenie się tej kwoty wcale nie musimy oczekiwać aż dziesięciu lat. Kwota będzie już prawie dwa razy większa w 7 roku a w 8 znacznie przekroczy swoją dwukrotność. Dzieje się tak ponieważ procent w drugim i każdym kolejnym roku inwestycji obliczany jest nie od pierwotnej kwoty ale od kwoty już zwiększonej.

Taki sam wynik możemy uzyskać znacznie szybciej posługując się wzorem na procent składany:

Kwota końcowa = kwota początkowa * (1 + oprocentowanie) ^ ilość okresów

(symbol ^ oznacza potęgę)

W kolejnym przykładzie na procent składany pewna osoba zastanawia się czy gdyby dziś wpłaciła 1000 zł na lokatę o stałym oprocentowaniu 5% i corocznej kapitalizacji odsetek, czy dzięki temu po 200 latach pra pra pra prawnukowie tej osoby byliby milionerami.

Po zastosowaniu powyżej opisanego wzoru do takich danych na powyższe pytanie uzyskujemy odpowiedź twierdzącą. Na koncie po 200 latach pojawi się kwota ponad 17 milionów złotych.

Jeżeli zdecydujemy się na obliczenie tego samego zadania korzystając z 200 wzorów na wzrost o procent, zauważamy że przyrosty w końcowym etapie lokaty są nieporównywalnie większe od tych z okresów początkowych, w ciągu kilku ostatnich lat wartość lokaty wzrastała co roku o prawie milion zł.

… … …

Takie zachowanie się wartości w kolejnych okresach jest typowe dla funkcji wykładniczych, czyli takich w których zmienna, w tym przypadku ilość lat, znajduje się w potędze.

Czy zatem wszyscy powinniśmy pospieszyć do banków z zamiarem założenia takich lokat? Gdybyśmy chcieli potraktować te obliczenia nie jako ciekawostkę ale na poważnie, należałoby uwzględnić jeszcze inflację, ryzyko bankructwa banku, ryzyko dewaluacji waluty lokaty, czy nawet zmiany ustroju, nacjonalizacji lokat bankowych, bankructwa państwa lub wojny. Po uwzględnieniu tych czynników bardziej rozsądnym sposobem zainwestowania 1000zł wydaje się przeznaczenie go na podwyższenie swoich kwalifikacji zawodowych, np. naukę Excela i VBA :-).

Marża

Obliczanie marży dla wielu osób stanowi duży problem ponieważ myślą o dodawaniu marży jak o dodawaniu procentu do danej kwoty.

W rzeczywistości marża nie jest procentem ‘narzutu’ na koszt, ale stanowi procent jakim jest zysk w ostatecznej cenie produktu bądź usługi.

Mając dany koszt i procent marży, cenę obliczamy z następującego wzoru:

Cena = koszt / (1- marża %)

Marża musi być mniejsza niż 100% ponieważ nie można sprzedając coś zarabiać na tym 100% lub więcej, z każdą działalnością gospodarczą związane są jakieś koszty.

Marża może natomiast być ujemna, firma wtedy sprzedaje swoje towary bądź usługi poniżej swoich kosztów i traci na każdej transakcji.

Wbrew temu co mogłoby się wydawać wcale nie jest to rzadkie zjawisko, w niektórych branżach np. przy sprzedaży drukarek, normą jest sprzedaż ich poniżej kosztów, ich producenci pokrywają te straty z nawiązką sprzedając usługi serwisowe i tonery/tusze z wysoką marżą. Strategia taka służy w uproszczeniu ‘złapaniu klientów’.

Aby obliczyć marżę znając koszt i cenę posłużymy się poniższym wzorem:

Marża % = (cena – koszt) / cena

Powyższy wzór możemy także zapisać jako: Marża % = 1 – koszt / cena

Natomiast jeżeli brakującym elementem jest koszt użyjemy poniższego wzoru, to ten sam jakiego używamy do obniżania danej kwoty o procent.

Innymi słowy obniżamy cenę o wielkość marży i pozostaje nam koszt.

Koszt = cena * (1 – marża)

Marża średnia ważona sprzedażą

Do obliczenia średniej marży nie możemy posłużyć się zwykłą średnią, musimy obliczyć ją korzystając ze średniej ważonej, gdzie wagami są wielkości sprzedaży.

Możemy to zrobić na 3 sposoby.

W pierwszym ze wzorów mnożymy każdą z marż procentowych przez odpowiadającą jej sprzedaż, wyniki sumujemy i sumę dzielimy przez sumę sprzedaży.

Ten wzór daje nam pełną kontrolę nad sposobem obliczania i powala zrozumieć na czy polega średnia ważona.

W drugim ze wzorów posłużymy się funkcją SUMA.ILOCZYNÓW, która sumuje i mnoży marże i sprzedaże, jej wynik musimy jeszcze podzielić przez sumę sprzedaży.

Funkcja ta jest dokładnie opisana w lekcji ‘Funkcje Podstawy’.

Trzeci sposób jest dla wielu najprostszy ale wymaga utworzenia dodatkowej kolumny z marżą kwotową. Wartości w kolumnie K uzyskujemy dzięki pomnożeniu każdej z marż procentowych przez sprzedaż.

Aby obliczyć średnią marżę % wystarczy podzielić sumę marży przez sumę sprzedaży.

Sugeruję wykasować zawartość wszystkich zielonych pól w arkuszu ćwiczeń do tej lekcji i wprowadzić wzory bez zaglądania do powyższego opisu. Jeżeli uda Ci się wprowadzić je wszystkie poprawnie opanowałeś ten materiał, jeżeli nie zawsze można zacząć od nowa i tak aż do skutku.