Excel für kaufmännische Schulen - HERDT
Transcrição
Excel für kaufmännische Schulen - HERDT
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 Beschaffungsprozesse planen, steuern und kontrollieren 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 Beschaffungsprozesse planen, steuern und kontrollieren Ü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