Excel für kaufmännische Schulen - HERDT

Transcrição

Excel für kaufmännische
Schulen
Holger Ludolph
1. Ausgabe, April 2012
Ein handlungsorientiertes
Arbeitsbuch
mit Excel 2010
BS-EX2010-KFM
4
Grundlagen
Wenn Excel gestartet wird, öffnet sich automatisch ein neues Tabellenblatt. Ein Arbeitsblatt ist
wie folgt aufgebaut – vorausgesetzt, die Grundeinstellungen von Excel wurden nicht verändert.
Register Datei mit wichtigen Befehlen
wie z. B. Öffnen, Speichern, Drucken
Symbolleiste zum schnellen
Starten von Befehlen
Register
Menüband mit Werkzeugen
und Befehlen
Menüband einund ausblenden
Gruppen, z. B. die
Gruppe Schriftart
Minisymbolleiste mit
den aktuellen Befehlen
Kontextmenü mit
neuen Einfügeoptionen
Einträge im
Kontextmenü
Ansichtssteuerung
zur schnellen
Layoutkontrolle

Um Excel mitzuteilen, dass eine Berechnung erfolgen soll, muss immer zunächst das Gleichheitszeichen = eingegeben werden.

Sowohl in der Zelle wie auch in der Bearbeitungsleiste werden die Eingaben angezeigt.
Tipp
Nutzen Sie für Berechnungen die Zahlentastatur (auch Ziffernblock genannt), da es hier
einfacher ist, die Zahlen einzutippen, vergleichbar mit einer Kasse im Supermarkt. Auch
die Rechenzeichen sind hier vorhanden.
Übungsaufgabe
X
66
Übernehmen Sie nun die nebenstehenden Eingaben in Ihr Tabellenblatt und schließen Sie den
Vorgang mit der Ü-Taste ab.
© HERDT-Verlag
Tipps

Statt die Buchstaben und Ziffern der Formel einzutippen, kann dies mithilfe der
Pfeiltasten erfolgen. Steuern Sie nach der Eingabe des Gleichheitszeichens die Zelle
A2 an, geben Sie Rechenzeichen ein, steuern Sie B2 an und drücken Sie die Entertaste im Ziffernblock oder die Ü-Taste.1

Der Vorteil dieser Vorgehensweise über die Pfeiltasten ist, dass Tippfehler gar nicht
erst entstehen können.

Zudem ist diese Vorgehensweise durchaus bequem: Sie müssen nicht zur Maus
greifen und schonen Ihr Handgelenk.
Nun soll in der Formel nicht mehr multipliziert, sondern dividiert
werden. Dafür bietet Excel zwei Möglichkeiten an:

Direkte Zellbearbeitung: Klicken Sie doppelt in die Zelle. Mit den
Pfeiltasten können Sie innerhalb der Formel navigieren und
Änderungen vornehmen.

Bearbeiten in der Bearbeitungsleiste: Klicken Sie einmal auf die
Zelle. Die Formel wird in der Bearbeitungsleiste angezeigt. Auch
hier lässt sich die Formel verändern.
Dies ist zwar schon eine Hilfe, aber vielfach sollen viele Zahlenwerte, wie beispielsweise hier fünf
Werte, miteinander arithmetisch verbunden werden. Wenn es sich um dieselbe Formel handelt,
kann diese auch kopiert werden.
X
Klicken Sie auf die Formel in Zelle C1.
X
Wählen Sie nun im Register Start die Schaltfläche Kopieren
X
Markieren Sie die Zellen, in die die Formel eingefügt werden soll: Klicken Sie C2 an, halten Sie
die linke Maustaste gedrückt und ziehen Sie diese bis C5 …
aus.
Ü-Taste drücken
oder Einfügen im Register Start anklicken,
oder die rechte Maustaste betätigen und im Kontextmenü Einfügen anklicken.
Tipp
Noch einfacher können Sie kopieren, indem Sie die Zelle mit der Formel an der unteren
rechten Ecke anklicken und das kleine schwarze Pluszeichen nach unten ziehen. Dieser
Vorgang wird als Autoausfüllen bezeichnet.
Die wichtigsten Mauszeiger in der Übersicht:
Markiert Zellen innerhalb des Arbeitsblattes und ist die Standardform des Mauszeigers.
Kopiert Formeln oder Werte – Autoausfüllfunktion.
Dient zum Verschieben eines ausgewählten Bereichs – nimmt diese Form auch bei
Auswahl von Registern und Kontextmenüs an.
1
Sie können wahlweise die Enter- oder die Returntaste nutzen: Es wird immer der Vorgang in der Zelle, z. B.
das Erstellen einer Formel, dadurch abgeschlossen.
© HERDT-Verlag
77
Relative Bezüge
Im obigen Beispiel haben Sie relative Bezüge erstellt: Beim Kopieren wurde von Excel automatisch erkannt, dass die Formel in der darunterliegenden Zelle jeweils eine Zeile weiter springen
soll.
Absolute Bezüge
Möchten Sie, dass immer auf eine bestimmte Zelle zugegriffen werden soll, wird ein absoluter
Bezug benötigt.
X
Löschen Sie die Zellen A2 bis A5 (markieren und _-Taste drücken).
X
Ändern Sie die Formel in C1 =$A$1*B1 und kopieren Sie diese bis C5. Sie haben einen absoluten Bezug für die Zelle A1 erstellt.
Tipp
Statt der Eingabe der $-Zeichen genügt das Drücken der Funktionstaste $, nachdem der
Cursor vor das A gesetzt wurde.
In diesem Fall haben Sie nicht nur einen absoluten Bezug erstellt, sondern einen gemischten
Bezug, da auf die Zelle B1 nach wie vor relativ zugegriffen wird.
Wenn-Dann-Sonst-Bedingung
Häufig ist es erforderlich, zwischen verschiedenen Bedingungen zu wählen.
Beispiel:
Zum Bestehen einer Prüfung sind 75 % der Punkte erforderlich. Ist dies der Fall, soll in Zelle B1
„Bestanden“ stehen, ansonsten „Durchgefallen“.
Excel vergleicht den Wert in Zelle A2 mit der Bedingung, die Sie in Zelle B2 festlegen, hier
„größer und gleich“.
Neben den Wörtern „Bestanden“ und „Durchgefallen“ können natürlich auch Zahlen in der
Klammer stehen – es bedarf dann keiner Anführungszeichen. Die Semikola stehen für die
Wörter „Dann“ und „Sonst“.
In den folgenden Aufgaben wird diese Funktion noch häufig eingesetzt.
88
© HERDT-Verlag
Speichern
Bevor Sie speichern, legen Sie über das Register Datei - Optionen einen neuen Ordner an, in
dem auch die kommenden Übungsaufgaben gespeichert werden sollen.
Der Ort des Standardspeicherordners ist abhängig von Ihrem System, der Name nicht.
X
Klicken Sie nun in der Symbolleiste auf das Diskettensymbol
– der Standardspeicherordner
öffnet sich – und vergeben Sie den Namen „Uebungsaufgaben“.
Tipp
Sie können zusätzliche Informationen vergeben. Hier wurde beispielsweise

X
unter Autoren „Lisa Meier“ eingetragen und
als Titel „BWL Anwendungen“ ergänzt.
Wollen Sie die Datei später unter einem anderen Namen ablegen, tun Sie dies mit dem Befehl
Speichern unter im Register Datei.
Soll die Datei Anwendern zur Verfügung gestellt werden, die mit Excel
2003 arbeiten, muss der Dateityp geändert werden (zunächst
aufrufen).
Wählen Sie in diesem Fall die Speicherung als Excel 97-2003-Arbeitsmappe.
Hinweis
Legen Sie unbedingt für jede neue Aufgabe ein neues Arbeitsblatt innerhalb der Datei
„Uebungsaufgaben“ an und benennen Sie diese Arbeitsblätter dann, wenn Sie mit
einer Aufgabe neu beginnen, entsprechend um. So wird Ihre Arbeitsmappe im Laufe
der Bearbeitung aussehen:
X
Zum Umbenennen der Arbeitsblätter klicken Sie mit der
rechten Maustaste auf den Reiter Tabelle1.
X
Wählen Sie dann im Kontextmenü den Eintrag Umbenennen.
X
Vergeben Sie den Namen „RabattundSkonto“:
Hinweis
.
Die weiteren Arbeitsblätter werden später benannt.
© HERDT-Verlag
99
Tipp
X
Leere Arbeitsblätter erzeugen Sie mit einem Klick auf
Tabellenblatt einfügen.
Markieren Sie nun alle bisher eingetragenen Werte
und löschen Sie diese mit einem Klick auf die Schaltfläche Löschen im Register Start.
Mit diesem Befehl löschen Sie nicht nur Daten, sondern
auch alle Formeln.
X
Speichern Sie abschließend die Datei mit einem Klick auf das Diskettensymbol.
X
Beginnen Sie die Bearbeitung der nun folgenden Übungsaufgaben in diesem Arbeitsblatt.
Hinweis Beachten Sie bitte: In Excel 2010 ist die voreingestellte Schriftart Calibri. Zur besseren
Lesbarkeit werden hier aber alle Tabellen und deren Inhalte in der Schriftart Arial dargestellt. Damit Sie nicht bei jeder Aufgabe diese Schriftart erneut einstellen müssen,
gehen Sie wie folgt vor:
10
10
X
Klicken Sie auf das Register Datei und dann auf Optionen
X
Wählen Sie nun die Schriftart Arial aus und bestätigen Sie mit OK.
.
© HERDT-Verlag
Beschaffungsprozesse planen,
steuern und kontrollieren
1
Rabatt und Skonto
2
Bezugskalkulation
3
Entscheidungswertetabelle
4
Lagerverwaltung
5
Bestellpunktverfahren und Lageranalyse
6
ABC-Analyse
7
Lagerkennzahlen
Beschaffungsprozesse planen, steuern und kontrollieren
1
Rabatt und Skonto
Skonto und Rabatt dienen verschiedenen Zwecken: Zunächst kann einem Käufer seitens des
Verkäufers Rabatt – also ein Preisnachlass – angeboten werden. Dies wird der Verkäufer beispielsweise vorab tun, um dem Kunden ein besseres Angebot als bei der Konkurrenz anbieten zu
können, oder aber nachträglich, am Ende des Jahres, wenn der Käufer eine bestimmte Kaufsumme erreicht hat (Bonus). Dieser Rabattsatz ist in der Regel gestaffelt nach dem Umsatz.
Gewährt der Verkäufer Rabatt in Höhe von 5 % auf eine Kaufsumme von 100,00 € netto (also
ohne Umsatzsteuer), so sind als Grundpreis nur noch 95,00 € zu zahlen – zu denen dann erst die
Umsatzsteuer hinzugerechnet wird.
Skonto, als ein weiteres Instrument der Preisgestaltung, gewährt der Verkäufer auf die Rechnungssumme inklusive der Umsatzsteuer. Dies wird in der Zahlungsbedingung auf der Rechnung
oder in einem separaten Vertrag geregelt. Eine mögliche Zahlungsbedingung könnte lauten: „Die
Rechnung ist zahlbar innerhalb von fünf Tagen unter Abzug von 2 % Skonto oder 20 Tage netto.“ 1

Überlegen Sie, warum der Verkäufer Skonto gewährt?

Ist es immer lohnenswert, den Skontoabzug in Anspruch zu nehmen?
Was wird sich der Verkäufer überlegt haben, wenn er eine Frist setzt (in diesem Beispiel von
20 Tagen), bis die Rechnung ohne Abzug spätestens bezahlt sein soll? Denken Sie dabei auch
an die Käufersicht.
Hinweis
Viele Kunden gehen davon aus, dass es normal sei, Skonto in Anspruch zu nehmen,
auch wenn der Verkäufer es auf der Rechnung nicht ausdrücklich erwähnt. Dies ist
ein Irrglaube: Wer dies tut, betrügt den Verkäufer, indem er sich nach § 812 BGB
ungerechtfertigt bereichert, und wird den Abzug später nachzahlen müssen.
Übungsaufgabe
Die Elli Pirelli OHG ist seit langer Zeit Kunde bei der Magic Tyres AG. Aufgrund der guten Geschäftsbeziehungen räumt die Magic Tyres AG der Elli Pirelli OHG bei jedem Kauf ab 5.000,00 €
einen Rabatt von 3 %, ab einem Kauf von 15.000,00 € einen Rabatt von 5 % ein. Auch die Zahlung
unter Inanspruchnahme von Skonto ist möglich. Die Zahlungsbedingung lautet: „Zahlbar innerhalb
von zehn Tagen unter Abzug von 2,5 % Skonto oder binnen 30 Tagen ohne Abzug.“
Die OHG beschließt einen Kauf im Wert von 17.000,00 € netto. Sie geht davon aus, dass die Ware
bis dahin umgesetzt wird und die Zahlung in jedem Fall innerhalb der Frist von 30 Tagen möglich
ist, möchte aber gerne den Skontoabzug in Anspruch nehmen. Das Bankkonto weist nur ein Guthaben von 8.000,00 € auf. Ein Kredit in Höhe von 20.000,00 € ist eingeräumt, eine Kontoüberziehung also möglich. Zinssatz des Bankkredits: 13,75 %.
Lisa Meier soll kalkulieren, ob sich der Skontoabzug unter Inanspruchnahme eines kurzfristigen
Bankkredits lohnt.
Funktionen und Anwendungen
9 Wenn-Dann-Sonst-Bedingung
9 Zellformatierungen
9 Benutzerdefinierte Formatierung
1
Der Begriff „netto“ ist verwirrend: Dieser bedeutet nicht, dass der Rechnungsbetrag ohne die Umsatzsteuer zu zahlen ist. Gemeint ist lediglich ohne Abzug (also kein Skonto).
12
12
© HERDT-Verlag
Rabatt und Skonto
1
Tipps zur Vorgehensweise
X
Erstellen Sie das abgebildete Lösungsschema.
X
Geben Sie in der Zelle C3 lediglich 17000 ein und
klicken Sie dann im Register Start auf
, um die
Formatierung in Euro einzustellen.
X
Um einen Rahmen zu erstellen, wählen Sie den gewünschten Bereich aus (hier die Zellen A3 bis C7), klicken auf die
Schaltfläche Rahmenlinien und wählen Alle Rahmenlinien.
Danach wählen Sie Dicke Rahmenlinien, um einen dickeren
Rahmen um den gesamten Bereich zu erstellen.
X
Um die Zelle C7 mit einer Füllfarbe
zu hinterlegen, klicken Sie auf Füllfarbe und wählen die gewünschte
Farbe aus.
X
Abschließend reduzieren Sie die Zeile 2, indem Sie mit der Maus zwischen die Zeilen 2 und 3
klicken, die linke Maustaste gedrückt halten und die Maus nach oben schieben.
Tipps

Bei Zellen, in die eine Prozentzahl eingegeben wird, diesen Wert bereits bei der
Eingabe mit % eingeben: 3,25 %. Wird die Zelle erst nach der Eingabe auf % formatiert, ist das Ergebnis 325 %.

Excel rechnet bei der %-Formatierung automatisch * 100 (3,25 * 100 = 325).

Mithilfe der %-Formatierung nimmt Excel dem Anwender das Rechnen mit 100
immer ab – wie wenn beim Taschenrechner die Prozenttaste gedrückt wird.

Wenn Sie das Minuszeichen vor dem Wort „Rabatt“ bzw. das Pluszeichen vor
„Umsatzsteuer“ eingeben, müssen Sie ein Leerzeichen einfügen. Sie erhalten sonst
diese Fehlermeldung: #Name?

Um ein negatives Ergebnis in der Zelle C4 zu erzielen, ergänzen Sie einfach die
obige Formel durch *-1 oder geben als Prozentzahl -5 % ein und belassen es bei der
obigen Formel.

Die Nachkommastellen erzielen Sie mit einem Klick auf Dezimalstelle hinzufügen
Dies gilt auch in diesem Fall in der Zelle C4 =C3*B4. Teilen Sie nicht durch 100 – das
erledigt Excel für Sie.
.
X
Fügen Sie in Zelle B4 eine Wenn-Dann-Sonst-Funktion ein, die den Rabattsatz in Abhängigkeit
des Nettowarenwerts ermittelt.
X
Nehmen Sie dann die entsprechenden Berechnungen in den Zellen der Spalte C vor und
formatieren Sie die Tabelle.
So lautet das Ergebnis:
© HERDT-Verlag
13
13
X
Überprüfen Sie Ihr Ergebnis (und damit die Wenn-Dann-Sonst-Bedingung), indem Sie den
Nettowarenwert auf 9.000,00 € bzw. 2.500,00 € verändern.
X
Setzen Sie den Nettowarenwert zurück auf 17.000,00 €.
Um zu kalkulieren, ob sich der Skontoabzug lohnt, muss die Tabelle ergänzt werden.
Hinweis Um den Skontoabzug in Anspruch zu nehmen, muss die Zahlung spätestens nach 10
Tagen erfolgen. Da die Ware bis zum 30sten Tag umgesetzt sein soll und der Kredit dann
zurückgezahlt werden kann, wird eine Kreditfinanzierung über 20 Tage benötigt.
X
Übernehmen Sie zunächst die gegebenen Werte.
X
Berechnen Sie dann in der Spalte C die fehlenden
Werte.
Kommen Sie auf das richtige Ergebnis von 82,03 €
Sollzinsen?2
Tipp
X
Um die Zellen C15 und C16 zu schraffieren, da hier keine Daten eingetragen
werden sollen, klicken Sie mit rechts in
die Zelle, wählen im Kontextmenü Zellen
formatieren und dann im Register Ausfüllen das gewünschte Musterformat.
Abschließend wird der Finanzierungsgewinn
ermittelt – also überprüft, ob es sich lohnt,
Skonto unter Zuhilfenahme einer Kreditfinanzierung in Anspruch zu nehmen.
Im Ergebnis spart die Elli Pirelli OHG 398,44 €, ohne Kreditzinsen sogar 480,46 €. Es lohnt sich also,
den Skontoabzug in Anspruch zu nehmen.
Zusatzaufgabe
Angenommen, es gelingt der Elli Pirelli OHG nicht, wie vorgesehen den Bankkredit am 30sten Tag
zurückzuzahlen. Nach wie vielen weiteren Tagen ist dann der Finanzierungsgewinn verbraucht
und schmälert dann aufgrund der Kreditkosten (Sollzinszahlung) den Gewinn?
2
Die Überweisung erfolgt am 10ten Tag. Sollzinsberechnung: Grundlage 360 Tage.
14
14
© HERDT-Verlag
Bezugskalkulation
Rabatt
und Skonto
X
21
Ergänzen Sie Ihre Tabelle.
Tipp
Die täglichen Kreditkosten betragen 4,10 €.
Diese ermitteln Sie, indem Sie die Sollzinsen durch die Laufzeit dividieren.
Nach insgesamt 97 Tagen wäre der Finanzierungsgewinn verbraucht; diese schließen aber die 20
bereits vergangenen Tage mit ein. Die Lösung lautet also 77 Tage.
Damit in den Zellen C26 und C30 das
Wort „Tagen“ nach dem Ergebnis steht
und Excel dennoch diese Zellen weiter
zum Berechnen nutzen kann, müssen
diese Zellen benutzerdefiniert formatiert werden.
Hinweis
Die Eingabe der Anführungszeichen ist zwingend erforderlich. Excel erkennt diese
Werte sonst als Text und damit kann nicht gerechnet werden. Sie erhalten ohne die
Anführungszeichen diese Fehlermeldung:
X
Formatieren Sie die Zelle wie gezeigt.
X
Speichern Sie die Datei.
2
Bezugskalkulation
Da es in der Regel nicht nur eine Bezugsmöglichkeit eines Produktes gibt, ist es natürlich interessant zu wissen, welches der vorliegenden Angebote am günstigsten ist. Dabei spielen Rabatte und
Skontoabzugsmöglichkeiten in Form der Zahlungsbedingungen eine genauso wichtige Rolle wie
die Lieferbedingungen: Wird die Ware kostenlos gebracht oder entstehen dafür zusätzliche
Kosten? Wer trägt die Versicherungskosten für die Fracht?
© HERDT-Verlag
15
15
Übungsaufgabe
Der Elli Pirelli OHG liegen neben dem Angebot der Magic Tyres AG noch zwei weitere Angebote
vor. Lisa Meier soll untersuchen, welches der drei Angebote das finanziell günstigste ist.
Funktionen und Anwendungen
9 Wenn-Dann-Sonst-Bedingung
9 Mehrfachauswahl
Tipps zur Vorgehensweise
X
Legen Sie zunächst ein neues Arbeitsblatt mit dem Namen „Bezugskalkulation“ an.
X
Erstellen Sie das abgebildete Lösungsschema.
X
Wählen Sie als Schriftart Arial und als Schriftgröße 12.
Tipp
Um dem gesamten Tabellenblatt eine Schriftart und -größe auf einmal zuzuordnen,
klicken Sie zwischen A und 1. Das gesamte Arbeitsblatt wird markiert. Wählen Sie dann
die gewünschte Schriftart, Schriftgröße und weitere Textmerkmale (z. B. kursiv, Farben
etc.) aus.
Da die Möglichkeit des Autoausfüllens nicht zum gewünschten Ergebnis führt, bietet sich hier
an, die Formeln sinnvoll zu kopieren:
Formel erstellen, dann kopieren und die S-Taste drücken und per Mausklick in der gewünschten Zielzelle wieder einfügen.
Dies ist eine Möglichkeit des sinnvollen Einsatzes der Mehrfachauswahl.
X Ergänzen Sie wie beschrieben die weiteren offenen Zellen.
Dies ist das Ergebnis:
Die quantitative Entscheidung
(also wer ist der günstigste Anbieter?) liegt auf der Hand: Es
ist die SchrilleRille KG.
X
16
16
Speichern Sie die Datei.
© HERDT-Verlag