Eigene inhaltliche Anpassungen

Bevor Sie auf einem Blatt Änderungen durchführen können, muss der Blattschutz für dieses Blatt entfernt werden. Gehen Sie dazu auf das Register „Überprüfen“ und klicken auf „Blattschutz aufheben“. Das Blattschutzkennwort lautet "0000". Bei Blättern ohne Schutz steht an Stelle von „Blattschutz aufheben“ der Menupunkt „Blatt schützen“.

Bei größeren Änderungsvorhaben auf mehreren Blättern haben wir zu ihrer Arbeitserleichterung ein Makro vorbereitet, welches den Blattschutz auf allen Blättern mit wenigen Klicks entfernt. Dazu gehen Sie bitte auf das Register "Entwicklungstools" (Register "Entwicklungstools" einblenden), klicken in der Gruppe "Code" auf Makros und wählen das Makro "Modus_Bearbeitung". In der sich öffnenden Dialogbox geben Sie das Passwort "0815" ein und klicken auf OK.

Achtung: Ohne den Blattschutz besteht die Gefahr, dass Sie wichtige Zellen bzw. Formeln löschen. Machen Sie vorher eine Sicherheitskopie!

Falls Sie beim anschließenden Versuch Zeilen oder Spalten einzufügen die Meldung bekommen „Objekte können nicht über das Blatt hinaus verschoben werden", lesen Sie bitte hier weiter.

Dies ist problemlos möglich. Es müssen lediglich 4 kleine Anpassungen gemacht werden. Denken Sie bitte daran, bei den Blättern auf denen Änderungen durchgeführt werden sollen, den Blattschutz vorher zu entfernen.

Vorgehensweise:

1.Auf dem Blatt "Annahmen" müssen entsprechende, zusätzliche Eingabeoptionen geschaffen werden. Dazu können Sie einfach unterhalb des rot markierten Bereiches leere Zeilen einfügen (siehe Screenshot) und die beiden Zeilen 1:1 mit copy&paste duplizieren (hier nicht gezeigt).

Zum Vergrößern Bild anklicken

2.Auf dem Blatt "Finanzierung" muss zunächst die weitere Runde verlinkt werden. Bspw. beim gezeichneten Kapital in die Zellen F43 und G43 (siehe 1 in Screenshot). Die Zeilennummern können ggf. bei ihnen abweichen und sind hier nur beispielhaft zu verstehen.

Zum Vergrößern Bild anklicken

3.Anschließend muss die Berechnungsformel in Zeile 42 angepasst bzw. erweitert werden (siehe 2 in Screenshot). In der orange-markierten Zelle (L42) lautete die alte Formel:

=WENN(UND($E42=1;L$4=$D6);$E$88;MIN(L88;-L39))+WENN($F42=L4;$G42*L6;0) Diese ist zu um den roten, neuen Teil zu ergänzen:

=WENN(UND($E42=1;L$4=$D6);$E$88;MIN(L88;-L39))+WENN($F42=L4;$G42*L6;0)+WENN($F43=L4;$G43*L6;0). Die Formel ist nach Änderung durch die gesamte Zeile zu kopieren.

4.die Schritte 2 und 3 müssen dann analog für die Kapitalrücklage wiederholt werden (nicht als Screenshot gezeigt).

Testen Sie nach Einbau die Funktionalität mit leicht wieder erkennbaren Werten und Daten (so wie im Screenshot bspw. mit 111 und 222).

Ausgangssituation

Die automatisierte Planung von Bestandsänderungen über die Zielgröße „% vom Nettoumsatz“ in der Auslieferungs-/Standardversion des Excel-Finanzplan-Tools stellt lediglich einen einfachen Ansatz dar, um Vorräte in der Planung zu berücksichtigen. Grundsätzlich gibt es viele weitere Möglichkeiten die Vorratsplanung in Excel abzubilden. Im Folgenden stellen wir ihnen einen Planungsansatz vor, der ihnen mit nur wenigen Anpassungen eine erheblich höhere Flexibilität im Rahmen der Vorrats- bzw. Bestandsplanung ermöglicht.

Der Kern des Ansatzes ist dabei, die automatische Berechnung der Zielgröße Lagerbestand (Zeile 128 auf Blatt „Kosten“) durch manuelle Inputwerte zu ersetzen. Auf diese Weise können Sie auch unabhängig vom Umsatz und sehr flexibel ihre Vorräte planen.

Zur Verdeutlichung haben wir ein einfaches Beispiel in einem ansonsten „leeren“ Modell erstellt, so dass die Auswirkungen schnell nachvollzogen und geprüft werden können. Wir verzichten dabei bewusst auf Zahlungsziele, MwSt. etc., sondern haben nur einzelne Umsätze und Materialkosten geplant. Falls Sie selbst in ihrer EFT-Datei Änderungen machen wollen, denken Sie daran ggf. vorher den Blattschutz zu entfernen (PW „0000“).

Annahmen Umsätze:

Zum Vergrößern Bild anklicken

Für die Materialkosten haben wir der Einfachheit halber eine Materialeinsatzquote i.H.v. 50% angenommen (nicht im Screenshot sichtbar). Sie können ihre eigenen Material- bzw. Wareneinsatzkosten natürlich völlig unabhängig und individuell für jeden Monat planen.

Erforderliche Modelländerung:

Auf dem Blatt „Kosten“ in Zeile 128 (Zielgröße Lagerbestand (netto)) ersetzen wir nun die Formeln ab Spalte J durch einfache Input-Zellen in die wir dann beliebige Werte (oder auch Formeln) eintragen können. Weisen Sie diesen neuen Eingabezellen die Zellenformatvorlage „Annahme“ zu, damit diese entsprechend formatiert werden und jeder Modellnutzer sofort erkennt, dass es sich von nun an um Eingabezellen handelt.

In unserem Beispiel gehen wir davon aus, dass nur einmal pro Quartal Material bestellt und auf Lager gelegt wird, d.h. in Spalte J (Januar) wird für die Monate Feb. bis Apr. Material eingekauft (siehe Formel im Screenshot). Daraus resultiert die Bestandserhöhung um EUR 1.550 (50% des Umsatzes in diesem Zeitraum). Dies ist nun unsere neue Zielgröße. Sie müssen dort aber keine Formel einfügen, sondern können auch einfach beliebige Absolutwerte einsetzen (natürlich auch für jeden Monat unterschiedliche Werte). Die Zeile 127 mit der ursprünglichen Bezugsgröße (Umsatz) hat von nun an keine Funktion mehr. Ignorieren Sie diese einfach.

Für den Monat Feb. wird als Zielwert 0 (Null) Euro eingegeben. Das Modell nimmt nun die 500 € Material/Waren im Februar (50% von 1.000) komplett aus dem Lager, was Sie auch an der Verminderung im Bilanzkonto unten im Screenshot erkennen. Gleiches gilt für März etc.

Zum Vergrößern Bild anklicken

Schauen wir auf die Liquidität (Blatt „Cashflow“), sieht man im folgenden Screenshot, dass korrekterweise Auszahlungen für Material nur in den genannten Monaten, also quartalsweise in diesem Beispiel anfallen. Beachten Sie dabei, dass bspw. im ersten Monat (Januar) nicht nur die 1.550 € Bestandsaufbau, sondern auch noch die 400 EUR vom aktuellen Monat (50% von 800 Umsatz) hinzukommen. Das gilt analog für die weiteren Zahlungen.

Zum Vergrößern Bild anklicken

Ein Blick auf die GuV (Blatt „GuV") zeigt, dass auch dort der Ausweis korrekt ist. Sowohl die Bestandsveränderungen, wie auch der Materialaufwand bzw. Wareneinsatz werden korrekt abgebildet.

Zum Vergrößern Bild anklicken

Das Prinzip sollte nun deutlich geworden sein. Selbstverständlich können Sie natürlich beliebige Variationen umsetzen wie bspw. halbjährliche Bestellungen (also alle 6 Monate) oder unregelmäßige Intervalle, einen höheren Lagerbestand in bestimmten Monaten aufbauen etc. In jedem Monat den gleichen festen Lagerbestand vorgeben etc.

Unterstützung bei individuellen Anpassungen

Sollten noch detailliertere Bestandsplanungen, z.B. für einzelne Produkte oder Produktgruppen erforderlich sein, muss das Excel-Finanzplan-Tool individuell angepasst werden, so dass im Extremfall für jedes Produkt eine separate Bestandsplanung möglich wird. Da das EFT offen, transparent und flexibel ist, können Sie eigene Änderungen oder Umsetzungen selbstverständlich jederzeit angehen. Sofern Sie dabei Unterstützung benötigen, übernehmen wir derartige Modifizierungen gerne für Sie. Kontaktieren Sie uns einfach mit ihren Wünschen.

Erfahrungsgemäß reicht es aus für Banken und Investoren das erste Planjahr monatlich darzustellen, Jahr 2 und 3 dann auf Quartalsbasis, während für die letzten beiden Jahre (Jahr 4 und 5) eine jährliche Aggregation genügt. Dies macht die Übersichten auch einfacher les- und ausdruckbar als ein „Zahlenfriedhof“ über 60 Monate.

Bestimmte Investoren bzw. Geschäftsmodelle machen es aber hin und wieder erforderlich, auch spätere Jahre in den Übersichten 02 bis 04 (also GuV, Cashflow und Bilanz) monatlich darzustellen. Da im EFT grundsätzliche alle Berechnungen und Daten durchgehend auf Monatsbasis vorliegen (siehe Blatt "GuV+CF+Bilanz") ist dies kein großes Problem. Wir zeigen ihnen am Beispiel der Übersicht 03 ("Cashflow") wie das ganz einfach mit wenig Aufwand zu bewerkstelligen ist.

Ziel: Liquiditätsübersicht ("Cashflow" => Übersicht 03) im Jahr 2 von quartalsweiser Darstellung auf monatlich erweitern

Gehen Sie dazu folgendermaßen vor:

1.Blattschutz entfernen (PW lautet „0000“)

2.Spalte X durch klicken auf X markieren (geht auch mit Spalte Y oder Z) und 8 neue, zusätzliche Spalten einfügen (z.B. mit Tastenbefehl [STRG] + [+])

3.J6 bis (zur letzten Zeile, hier flüssige Mittel am Periodenende) U62 (aus dem ersten Jahr, nicht Summenspalte V) selektieren und mit [STRG] + [C] kopieren und anschließend bei W7 mit [STRG] + [V] alles wieder einfügen.

4.Formel in Zelle W7 anpassen => =U8+1 (damit die Zeitleiste oben wieder korrekt arbeitet)

Das war es schon. Es darf keine Fehlermeldung bei der Aggregationskontrolle auftauchen (Zelle I64 auf "Cashflow" bzw. "Übersicht 03"). Für geübte Excel-Nutzer ist für diese Anpassung ein Zeitaufwand von ca. 5 min. zu veranschlagen.

Antwort