Leseprobe - HERDT

Transcrição

Leseprobe - HERDT

Excel für
Industriekaufleute
Holger Ludolph
1. Ausgabe, Juni 2010
Ein handlungsorientiertes
Arbeitsbuch
BS-EX2003-I
Inhalt
1
2
3
4
Informationen zum Umgang mit diesem Schülerbuch ..................................................................... 4
Vorgehensweise ................................................................................................................. ............... 4
Einführung......................................................................................................................................... 5
Grundlagen........................................................................................................................................ 6
Beschaffungsprozesse planen, steuern und kontrollieren ............................................ 11
1
2
3
4
5
6
7
Rabatt und Skonto .......................................................................................................................... 12
Bezugskalkulation............................................................................................................................ 15
Entscheidungswertetabelle............................................................................................................. 16
Lagerverwaltung................................................................................................................ .............. 18
Bestellpunktverfahren und Lageranalyse........................................................................................ 19
ABC-Analyse .................................................................................................................................... 23
Lagerkennzahlen ............................................................................................................................. 24
Leistungserstellungsprozesse planen, steuern und kontrollieren ................................. 27
1
2
3
4
5
Break-Even-Point............................................................................................................................. 28
Gewinnkalkulation .......................................................................................................................... 31
Optimale Losgröße .......................................................................................................................... 33
Make or buy? - Herstellen oder zukaufen?..................................................................................... 37
Optimale Bestellmenge................................................................................................................... 42
Absatzprozesse planen, steuern und kontrollieren ...................................................... 47
1
2
3
4
5
6
Rechnungserstellung....................................................................................................................... 48
Verzugszinsberechnung .......................................................................................................... ........ 53
Marktforschung............................................................................................................................... 57
Preispolitik................................................................................................................... .................... 60
Produktelimination ......................................................................................................................... 62
Distributionspolitik.......................................................................................................................... 64
Investitions- und Finanzierungsprozesse planen.......................................................... 67
1
2
3
4
Konsumentenkredit ........................................................................................................................ 68
Kapitaldienstberechnung ........................................................................................................ ........ 77
Leasing oder Kreditkauf?................................................................................................................. 83
Annuitätendarlehen ............................................................................................................. ........... 84
Personalwirtschaftliche Aufgaben wahrnehmen ......................................................... 89
1
Datenübersichten erstellen und bearbeiten................................................................................... 90
Index ........................................................................................................................ 103
© HERDT-Verlag
33
1
Informationen zum Umgang mit diesem Schülerbuch
Liebe Schülerinnen und Schüler,
in diesem Excel-Arbeitsbuch erwarten Sie vielfältige, sehr verschiedene Übungsaufgaben aus den
Bereichen Rechnungswesen und Betriebswirtschaftslehre, die kombiniert mit der Tabellenkalkulation gut geeignet sind, wirtschaftliche Problemstellungen zu lösen.
Einige davon sind so aufgebaut, dass diese auf Ihre eigene Lebensumwelt zutrifft, beispielsweise
den Kauf eines Autos oder später auch einer eigenen Wohnung oder gar eines eigenen Hauses und den damit verbundenen Kosten - und vor allem der Frage: Kann ich mir das leisten?
Andere Aufgaben beschäftigen sich mit Fragen, die in Ihrem Ausbildungsbetrieb zum Tagesgeschäft gehören, beispielsweise: Wie wird die Liquidität gesteuert? Lohnt sich eine Investition?
Wie viel soll produziert werden?
Dieses Buch soll Ihnen die Möglichkeit eröffnen und Sie dabei unterstützen, praxisorientierte
Aufgaben zu lösen, die Sie und auch Ihren Ausbildungsbetrieb in der Analyse von Daten voranbringen.
Seien Sie also kreativ. Sie können, aber müssen nicht immer dem in diesem Buch vorgeschlagenen
Lösungsweg folgen. Wenn Sie andere Möglichkeiten finden, sind diese genauso spannend und oft
auch genauso erfolgreich. Die Tabellenkalkulation ist sicherlich eine der besten Möglichkeiten,
betriebswirtschaftliche und auch persönliche Berechnungen abzubilden.
Die Dienstleistungsgesellschaft für Informatik, die in Deutschland für den Europäischen Computer
Führerschein (ECDL) zuständig ist, hat die vorliegenden Unterlagen mit dem ECDL Award 2009
ausgezeichnet. Diese sind damit sowohl zur Vorbereitung auf das Modul „Tabellenkalkulation“ als
auch für das Vertiefungszertifikat des ECDL Advanced geeignet.
2
Vorgehensweise
Zunächst werden Sie einige grundlegende Informationen zum Umgang mit der Tabellenkalkulation Microsoft Excel im Kapitel Grundlagen erhalten.
Danach begegnen Ihnen durchgehend zwei Modellunternehmen: die Magic Tyres AG, die Reifen
herstellt, und die Elli Pirelli OHG, die einen Reifenhandel mit Montageservice betreibt. In beiden
Unternehmen werden die Aufgaben von Auszubildenden bearbeitet: von Lisa Meier für die Elli
Pirelli OHG und von Yannick Frühauf für die Magic Tyres AG.
Darüber hinaus gibt es einige andere handelnde Akteure, die Ihnen an geeigneter Stelle vorgestellt werden.
Sie finden unterschiedlich hervorgehobene Textpassagen:

44
Graue Kästen beinhalten Informationen zur Theorie.
Menüpfade werden in Großbuchstaben dargestellt.
Schaltflächen sind durch Kursivschrift gekennzeichnet.
© HERDT-Verlag
3
Einführung
Eine Tabellenkalkulation dient in erster Linie dazu, große Zahlenmengen in jeglicher arithmetischer Form zu bearbeiten. Von der einfachen Addition über statistische Auswertungen und deren
grafische Darstellung bis hin zu komplexen „Was wäre wenn“ -Analysen lässt sich fast alles ermitteln.
Im Englischen bezeichnet „calculate“ den Einsatz von Formeln, daher stammt auch der Name
„Kalkulationsprogramm“ .
In einer Tabellenkalkulation lassen sich rechnerische Aufgabenstellungen übersichtlich und leicht
änderbar darstellen, Alternativberechnungen schnell durchführen, Szenarien erstellen und berechnen. Ein Großteil des gesamten kaufmännischen Schriftverkehrs lässt sich durchführen und
viele weitere Aufgaben, die mit Sammlung, Sortierung und Auswertung von Daten zu tun haben.
Der große Vorteil liegt dabei in den leicht anzupassenden Ergebnissen: Statt den Taschenrechner
immer wieder erneut bemühen zu müssen, wenn sich beispielsweise der Zinssatz einer Kapitalanlage ändert, reicht es aus, diesen einen Wert zu ändern - und den Rest erledigt Excel für Sie.
Ausgangslage mit 3,25 %:
Zinssatz
Kapital
Zinsertrag p. a.
3,25 %
10.000,00 €
325,00 €
20.000,00 €
650,00 €
30.000,00 €
975,00 €
40.000,00 €
1.300,00 €
50.000,00 €
1.625,00 €
40.000,00 €
1.500,00 €
50.000,00 €
1.875,00 €
Und nun mit einem veränderten Zinssatz von 3,75 %:
Zinssatz
Kapital
Zinsertrag p. a.
3,75 %
10.000,00 €
375,00 €
20.000,00 €
750,00 €
30.000,00 €
1.125,00 €
Diese Automatisierung spart also viel wertvolle Zeit!
Excel bietet über 400 (!) Funktionen an. In diesem Buch werden nur ca. 5 % behandelt. Aber auch
mit diesen lassen sich bereits beeindruckende Ergebnisse erzielen. Diese geben bereits einen
guten Einblick in die „Mächtigkeit“ des Werkzeugs Excel und machen „Lust auf mehr“ in der
Anwendung, Umsetzung und Lösung kaufmännischer Aufgaben- und Problemstellungen.
© HERDT-Verlag
55
Zusätzliche Aufgaben
1
Kapitalanlage
2
OHG - Gewinnverteilung
1
Kapitalanlage
Kapitalanlagen sind umgewandeltes Geldvermögen: Der Kapitalanleger kann sein verfügbares
Geld, das er in bar oder auf seinem Girokonto zur Verfügung hat, zum Beispiel bei einer Bank
gewinnbringend anlegen. Dabei gilt die Grundregel: Je länger er das Geld zur Verfügung stellt,
desto mehr Zinsen (= Kapitalertrag) wird er dafür erhalten.
Wünschenswert ist dabei für den Anleger, einen möglichst hohen Kapitalertrag bei schnellstmöglicher Verfügbarkeit (= hohe Liquidität) und gleichzeitig geringstem Risiko zu erhalten. Dass
dies schwerlich möglich ist, veranschaulicht das „Magische Dreieck der Vermögensanlage“:
Sicherheit
(Risiko)
Kapitalertrag
(Rentabilität)
Liquidität
(Anlagedauer)
Beispiele:

Kauf eines Bundesschatzbriefes zu 4,5 %: Risiko = 0 bei einer Anlagedauer von sieben Jahren
und bei einem eher mittelmäßigen Kapitalertrag

Kauf von Aktien, die einer starken Kursschwankung unterliegen: Anlagedauer eher kurz,
Risiko eher hoch. Es sind hohe Gewinne, aber auch hohe Verluste möglich.

Anlage des Geldes auf einem „normalen Sparbuch“: Anlagedauer kurz- bis langfristig,
Risiko = 0. Der Kapitalertrag ist meist schlechter als die Inflationsrate!
Letztlich hängt es von der Risikobereitschaft des Anlegers ab, ob er sein Geld gewagter anlegt, um
mehr Ertrag zu erzielen, oder doch lieber entspannt bleiben möchte und sein Geld zwar weniger
ertragbringend, dafür aber lieber sicher anlegt.
Viele Banken bieten neben dem klassischen Sparbuch längst attraktivere Anlageformen an. Eine
davon ist das sogenannte Geldmarktkonto. Andere nennen es „Zins&Cash“, wieder andere „ExtraKonto“. Dieses Geld kann jederzeit abgehoben werden, ist also „täglich verfügbar“, bietet mehr
Zinsen als ein Sparbuch (meist nur ein Prozent) und ist sicher, da die Bank mit ihrem guten Namen
dahintersteht.
Übungsaufgabe
Karl Kork, Geschäftsführer der Elli Pirelli OHG, möchte 2.000,00 € aus Finanzmitteln der OHG
anlegen. Er hat die folgenden beiden Angebote gefunden, die die Bedingungen des magischen
Dreiecks recht gut verbinden: schnelle Verfügbarkeit des Geldes, angemessener Zinsertrag und
kein Risiko.
22
© HERDT-Verlag
Kapitalanlage
1
Links das Angebot einer örtlichen Sparkasse - rechts das einer Internetbank:
Beide bieten 3,25 % Zinsen, es gibt jedoch einen Unterschied: Die Sparkasse zahlt die Zinsen monatlich, die Internetbank nur jährlich.
Damit verbunden fällt der Zinsvorteil in barer Münze bei der Sparkasse
etwas höher als bei der Internetbank aus. Ausgehend von einer Anlagesumme von 2.000,00 € liegt dieser bei 0,98 €.
Die Tabelle zeigt in den Zellen B21 und C21 das Endergebnis am Ende des
Jahres. Damit kann jederzeit abgelesen werden, wie viel Zinsen ausbezahlt würden, wenn das Geld beispielsweise bereits Ende Juli abgehoben
würde: Bei der Internetbank wären dies 37,92 € und bei der Sparkasse
38,23 €.
Tipps zur Vorgehensweise
X
Legen Sie zunächst ein neues Arbeitsblatt mit dem Namen „Kapitalanlage“ an.
X
Erstellen Sie das abgebildete Lösungsschema; tragen Sie aber noch nicht die Werte in die
Zellen B10 bis C21 und C6 ein, denn diese werden ausgerechnet.
Tipp
Die Zellen B8 und C8 wurden verbunden.
Neben den richtigen Berechnungen ist es wichtig, das Ergebnis auch optisch gut darzustellen: Hier
sollen die Spaltenbreiten bei den vergleichbaren Spalten „Internetbank“ und „Sparkasse“ gleich sein.
X
Setzen Sie zunächst einen Doppelklick mit der linken Maustaste zwischen die Spaltenköpfe
von B und C und legen Sie damit die optimale Spaltenbreite für das Wort „Internetbank“ fest.
X
Um diese Spaltenbreite für das Wort „Sparkasse“ zu übernehmen, muss zunächst die
Breite der Spalte B9 ermittelt werden. Folgen
Sie dazu dem Menüpfad FORMAT - SPALTE BREITE.
X
Klicken Sie anschließend in C9, wiederholen Sie den Vorgang und tippen Sie manuell die
Spaltenbreite ein.
Hinweise

Die Formel in der Zelle C6 ist eine einfache Subtraktion.

In Zelle B11 benötigt diese Formel eine Ergänzung. Neben der Ursprungsformel
muss nun auch der Zinsertrag des jeweils vorhergehenden Monats addiert werden.
© HERDT-Verlag
In Zelle B10 wird es spannender: = $C$3*$C$4/$A$21.
Im Klartext: 2.000,00 € * 3,25 % : 12.
Damit wird der Zinsertrag des ersten Monats ermittelt. Es handelt sich um
absolute Bezüge.
33
X
Ergänzen Sie die Formel entsprechend und kopieren Sie sie abwärts bis B21.
Hinweise

X
Die Formel in Zelle C10 entspricht der aus B10.
Beachten Sie in C11: Bei der Sparkasse muss nicht nur der Zinsertrag des vorhergehenden Monats addiert, sondern zusätzlich auch noch verzinst werden.
Zunächst kann die Formel aus B11 nach C11 kopiert werden.
Tipp
Hier bietet sich die direkte Zellbearbeitung (Doppelklick in die Zelle) in C11 zur weiteren Bearbeitung an: = C10+$C$3*$C$4/$A$21.
Die Formel muss nun ergänzt werden:
X
Multiplizieren Sie den Zinsertrag aus Zelle C10 (5,42 €) mit dem Zinssatz.
Zusatzaufgabe
Ermitteln Sie, wie hoch der Zinsertrag wäre, wenn Sie nicht nur 2.000,00 €, sondern 15.000,00 €
zur Verfügung hätten und die Bank 4,75 % Zinsen zahlen würde.
Vergleich des Zinsertrags
Anlagebetrag:
Zinssatz:
15.000,00 €
4,75 %
Zinsvorteil:
X
44
15,72 €
Speichern Sie die Datei.
© HERDT-Verlag
2
2
Die Gewinnverteilung einer offenen Handelsgesellschaft basiert auf den gesetzlichen Grundlagen
des Handelsgesetzbuches:
§ 121
(1) Von dem Jahresgewinn gebührt jedem Gesellschafter zunächst ein Anteil in Höhe von vier vom
Hundert seines Kapitalanteils. Reicht der Jahresgewinn hierzu nicht aus, so bestimmen sich die
Anteile nach einem entsprechend niedrigeren Satz.
(3) Derjenige Teil des Jahresgewinns, welcher die nach den Absätzen 1 und 2 zu berechnenden
Gewinnanteile übersteigt, sowie der Verlust eines Geschäftsjahrs wird unter die Gesellschafter
nach Köpfen verteilt.
X
Überlegen Sie, welche drei Möglichkeiten der Gewinnverteilung möglich sind.
Übungsaufgabe
Die Elli Pirelli OHG hat drei Gesellschafter. Neben der Geschäftsführerin Elli Pirelli sind Lisa Meier
und Karl Kork beteiligt. Elli Pirelli hat Kapital in Höhe von 360.000,00 €, Karl Kork von 510.000,00 €
und Lisa Meier von 200.000,00 € eingebracht.
Um nicht jedes Jahr erneut - je nach Gewinn oder Verlust - eine neue Berechnung vornehmen zu
müssen, beschließen die drei, eine Excel-Tabelle zu erstellen, die alle Fälle der Gewinn-, aber auch
die der Verlustverteilung mit einschließt.

Lösen Sie diese Aufgabe in Gruppenarbeit.
Die Aufgabe wird Schritt für Schritt weiterentwickelt.
Tipps zur Vorgehensweise
X
Benennen Sie zunächst drei weitere Arbeitsblätter in „OHG Fall1“, „OHG Fall2“ und
„OHG Fall3“ um.
X
Erstellen Sie das abgebildete Lösungsschema.
X
Lassen Sie den Wert in Zelle C5 zunächst noch leer.
X
Achten Sie auf die Rahmenformatierungen und im Folgenden auf kopierfähige Formeln.
X
Ermitteln Sie die Summen in den Zellen B11 bis E11.
X
Finden Sie eine Formel für die Gewinnverteilung nach % in den Zellen C8 bis C10. Sie werden
erkennen, dass mehr als nur 4 % zu verteilen sind.
© HERDT-Verlag
55
Wie müssen die Formeln in den Zellen D8 bis D9 lauten, um den Restgewinn nach Köpfen verteilen zu können?
Dies sollte Ihr Ergebnis sein:
X
Kopieren Sie diese Tabelle in das Arbeitsblatt „OHG Fall2“.
X
Ändern Sie den Gewinn von 60.000,00 € auf 40.000,00 €.
Diese Lösung ist zwar durchaus richtig - aber nur im Ergebnis: Denn nun gilt es, einen tatsächlichen Verteilungssatz in der Zelle C5 zu ermitteln und mit diesem weiterzurechnen.

Dazu benötigen Sie eine Wenn-Dann-Sonst-Funktion, die entscheidet, ob der gesetzliche oder
der niedrigere Verteilungssatz zur Anwendung kommt.

Überlegen Sie zunächst, welche Werte Sie dividieren müssen, um den realen Verteilungssatz
zu ermitteln, und fügen Sie diese dann in die Wenn-Dann-Sonst-Funktion ein.

Als Vergleichswert für die Entscheidung, welcher Satz zur Anwendung kommt, dient der Wert
von 42.800,00 €, da dieser der 4%igen Verteilung entspricht: 1.070.000,00 € * 4,00% =
42.800,00 €.
X
Passen Sie die Formeln in C8 bis C10 entsprechend an.
X
Kopieren Sie diese Tabelle in das Arbeitsblatt „OHG Fall3“.
66
© HERDT-Verlag
2
Hat die OHG einen Verlust erwirtschaftet, sieht das Ergebnis wie folgt aus:
X
Löschen Sie die Formel in C5.
X
Überlegen Sie, wie die Wenn-Dann-Funktion lauten muss, wenn nur ein Verlust vorliegt, der
tatsächliche Verteilungssatz also 0 % sein muss.
Tipp
Ergänzen Sie diese Funktion, indem Sie eine weitere Wenn-Dann-Sonst-Funktion
einfügen (diese entspricht dann der Sonst-Bedingung aus der Ursprungsfunktion):
= WENN(_____;WENN(________________)).
X
Überprüfen Sie, ob der Gewinn in C3 kleiner als B11 * 4 % ist (sinnvoll ist diese neue Berechnungsmethode, da sich die Kapitalanteile ändern können). Wenn ja, ermitteln Sie den tatsächlichen Verteilungssatz (wie oben). Falls dies nicht der Fall ist (Gewinn > Kapitalanteile*4 %), rechnen Sie mit 4 %.
X
Erstellen Sie abschließend ein Kreisdiagramm (Gewinn: 80.000,00 €).
Gewinn- und Verlustverteilung der Elli Pirelli OHG
20.400,00 € ;
25,50%
26.800,00 € ;
33,50%
32.800,00 € ;
41,00%
Elli Pirelli
Tipp
X
Karl Kork
Lisa Meier
Mit rechtem Mausklick können Sie das Dialogfenster Datenpunkt formatieren öffnen und
die Kreissegmente formatieren. Auf der Registerkarte Datenbeschriftung wählen Sie
Wert bzw. Prozentsatz aus. Klicken Sie dann mit der linken Maustaste doppelt auf die %Zahlen, um die Nachkommastellen zu formatieren.
Speichern Sie die Datei.
© HERDT-Verlag
77