Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1272to1276
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Summenprodukt / Solver

Summenprodukt / Solver
Dierk
Liebes Forum,
ich habe ein Problem:
Upload: https://www.herber.de/bbs/user/81284.xlsx
Ich plane mit 2 verschiedenen Parametern den Umsatz pro Abteilung und Tag.
Vorgegeben ist das Gesamtziel. Im Bsp. 650,0
Davon abhängig hat jede Abteilung ein vorgegebenes Ziel. Zeile (F5:J5). Die Summe ergibt wieder 100%.
Dann folgt das vorgegeben Umsatzziel pro Tag: (B5:B8). Die Summe ergibt wieder 650.
Um jetzt nicht "stumpf" das Tagesziel x Abteilungsgewichtung (F5:J5) zu errechnen, soll auf Basis der Vergangheit noch die tatsächliche Umsatzverteilung der Abteilung berücksichtigt (F11:J14))werden.
Also eine Kombination aus "Anteil Abteilung" und "Historie).
Meine Idee war der Solver oder die Formel "summenprodukt(). Mit beiden Varianten erhalte ich keine Lösung. Die Datei füge ich exemplarisch bei.
Schon jetzt vielen Dank
Dierk
Abteilung 1 2 3 4 5
Ausgangsbasis
Gesamtsplit Umsatz pro Tag Tag Tagesziel/Jahresziel anteilig am Gesamtziel, am Jahresende muss Abt. 1 10% des Ziels erreicht haben
15,38% 100 1 Verteilung Tag 1-N 10% 50% 15% 5% 20% 100%
30,77% 200 2
46,15% 300 3
7,69% 50 4
100,00% 650 Umsatzgewichtung pro Tag aus Historie
1 Tag 5% 40% 25% 15% 15% 100%
2 Tag 15% 40% 10% 15% 20% 100%
3 Tag 45% 15% 20% 10% 10% 100%
4 Tag 35% 5% 45% 5% 10% 100%
100% 100% 100% 45% 55%
1 Gewichteter Verteilungsschlüssel 28% 28%
2 Gewichteter Verteilungsschlüssel 0%
3 Gewichteter Verteilungsschlüssel 0% 0%
4 Gewichteter Verteilungsschlüssel 0% 0%
0,0%
1 Umsatz 0 28 0 0 0 28
2 Umsatz 0 0 0 0 0 0
3 Umsatz 0 0 0 0 0 0
4 Umsatz 0 0 0 0 0 0
Umsatz 0 28 0 0 0 28
AW: Summenprodukt / Solver
06.08.2012 13:55:35
Jack
Also so richtig erschliesst sich mir zumindest das Problem nicht ganz.
1. Wenn du uns die Zellen Beschreibst, solltest du nicht 5 Zeilen darüber einfügen =)
2.
Also wir haben Gesamtziel (Plan) 650 in Zelle B10 (warum ist diese Zelle errechnet, wenn sie die Vorgabe ist?)
Die Abteilungsvorgabe in F5-J5 sind auch Prozentangaben und beziehen sich worauf?
Und vielleicht bin ich bissl borniert aber ich finde die Tabelle ziemlich unstrukturiert.
Grüße
AW: Summenprodukt / Solver
06.08.2012 14:04:10
Dierk
Hi, werte B10 einfach als gegeben bzw. wie von mir beschrieben.
Mag sein, dass Du die Tabelle anders aufgebaut hättest.
F5:J5 ist einfach ein Split vom Tagesziel.
F5: Die Abteilung 1 muss am Ende es Jahres das anteilige Ziel von 10% am Gesamtziel erreicht haben, sprich 65.
Anzeige
AW: Summenprodukt / Solver
06.08.2012 14:24:55
Jack
Okay.
Ich Fasse kurz zusammen und versuche das Problem zu erfassen.
1. Es gibt einen Planumsatz (p.a.) in Zelle B10
2. Es gibt Istumsätze (p.a.)in Euro in B5-B8 (jeweils nach Abteilungen oder nach Tag?)
3. Was ist HFB
4. Was ist ein Split vom Tagesziel? der Zu erreichende Anteil am Tagesumsatz? Oder Der Istanteil am Jahressoll?
5. Vielleicht kannst du die Tabelle ja nochmal so hochladen, das die Spalten und Zeilen klar definiert sind.
und
Versuch bitte mal das Problem so zu beschreiben, dass jemand der NICHT in deiner Aufgabenstellung hängt, das Problem umreissen kann.
Grüße
Anzeige
AW: Summenprodukt / Solver
06.08.2012 15:02:23
Dierk
Hallo,
zu Deinen Fragen:
1: richtig
2: Vorgegeben nach Abteilung.
3: = Abteilung
4: Ist ein Hilfsmittel aus der Vergangenheit. Beispiel: Am 1.9.11 hat die Abteilung 1 5% vom Gesamttagesumsatz erzielt. Diese Gewichtung soll berücksichtigt werden.
5: Lade ich neu hoch.
https://www.herber.de/bbs/user/81292.xlsx
Gruß
AW: Summenprodukt / Solver
06.08.2012 15:30:21
Jack
Okay-.-
Also ich hab die Datei mal versucht so anzulegen damit ich sie verstehe
schau sie dir bitte an und trage ein was ich nicht kapiert hab.
So können wir uns vielleicht deinem Problem nähern
https://www.herber.de/bbs/user/81293.xlsx
Grüße
Anzeige
AW: Summenprodukt / Solver
06.08.2012 16:00:49
Dierk
Hallo,
ich versucher es gleich noch einmal. File Upload Server gibt mir eine Fehlermeldung.
Gruß Dierk
AW: Summenprodukt / Solver
06.08.2012 16:07:26
Dierk
Hi,
angeblich ist das Dateiformat nicht korrekt, aber die Endung xlsx paßt ja.
Ich versucher es weiterhin.
Dierk
AW: Summenprodukt / Solver
06.08.2012 16:17:51
Jack
Jupp
geht klar.
kann aber nicht versprechen das ich dann später noch da bin..
im Zweifel dann morgen
AW: Summenprodukt / Solver
06.08.2012 18:54:51
JAck
Das ist die von Anfang..?
Nicht die (meine) überarbeitete
AW: Summenprodukt / Solver
06.08.2012 19:07:55
Dallwitz
Hi, mein Fehler. Wenn ich z. Hause bin, lade ich deine Datei hoch.
Anzeige
AW: Summenprodukt / Solver
06.08.2012 19:13:14
JAck
kein problem
=)
AW: Summenprodukt / Solver
06.08.2012 21:27:47
Dierk
Guten Abend,
ich bekomme immer als Meldung ungültiges Dateiformat, obwohl die Datei doch die Endung .xlsx hat.
Entweder "spinnt" der Server oder es ist anderes Problem.
Ich versuche es dann morgen noch einmal. Wäre schön, wenn Du eine Lösung findet könntest.
Danke
Dierk
AW: Summenprodukt / Solver
07.08.2012 00:30:37
Matze,Matthias
Meide im Dateinamen ä ö ü , nimm 123.xlsx dann klappt das und die größe kleiner 300KB
AW: Summenprodukt / Solver
07.08.2012 07:03:56
Dierk
Guten Morgen,
vielen Dank für den Tipp.
Liebe Grüße
Dierk
https://www.herber.de/bbs/user/81302.xls
Anzeige
AW: Summenprodukt / Solver
07.08.2012 10:02:56
Jack
https://www.herber.de/bbs/user/81310.xls
So Dierk
Ich hab es mal nach meinem Gutdünken angepasst.
Und sofern ich das richtig verstanden habe ( und somit richtig gelöst)
hast du mit summenprodukt auch eine adäquate möglichkeit gewählt nur etwas falsch verknüpft
Ich hab 2 Wege vorgegeben
die orangene Markierung ist der Direkte Weg ohne zwischentabelle mithilfe des Summenproduktes
die blaue Markierung ist eine Zwischenrechnung die die absolute Verteilung ausgibt. und über diesen umweg in der Grünen Markierung berechnet wird.
Hoffe das ist es?
Grüße
Anzeige
AW: Summenprodukt / Solver
07.08.2012 20:04:11
Dierk
Hallo,
vielen Dank für diesen ersten Entwurf. Ich denke wir nähern uns schrittweise dem Ziel.
Ganz stimmt das Ergebnis noch nicht.
Im Erfolgsfall hat die Abteilung 1 am Ende als Planwert (Tag 1 bis Tag 4) ein Ziel von 227,50.
0,35 x 650 = 227,50
Die Herausforderung liegt im Bereich C32:F35.
Ich suche eine Kombination die beinhaltet:
A. Das Tagesziel für den Tag 1 von 100 (Gesamt) wird erreicht
B. Die Historie wird in Kombination mit dem Jahresziel der Abteilung (z.B. für Abt. A= 35%) erfüllt.
C. Für das ganze JAhr wird der Planwert 650 erreicht.
Ich habe noch einmal diese Vorstellung in der Formel angepasst. Natürlich erhalte ich eine Fehlermeldung.
Gruß
Dierk
https://www.herber.de/bbs/user/81320.xls
Anzeige
AW: Summenprodukt / Solver
08.08.2012 15:38:00
Jack
Hallo DIERK
Im Erfolgsfall hat die Abteilung 1 am Ende als Planwert (Tag 1 bis Tag 4) ein Ziel von 227,50.
0,35 x 650 = 227,50
Der Erfolgswert (Ziel) ist ja in Zelle C25 und wird händig eingegeben
Der Erfolgswert (IST) wird in Zelle C28 aus den Tagesumsätzen und der Historie errechnet.
Und wird relativ ausgegeben. DH. mit den Umsätzen IST
Im Beispiel :
Tag 1 : 5% von 100€ = 5,00€
Tag 2 : 10% von 200€ =20,00€
Tag 3 : 15% von 300€ =45,00€
Tag 4 : 15% von 50€ = 7,50€
Summe =77,50€
Und diese Summe entspricht 11,92% von 650€
Also ist die Zielerfüllung gescheitert (die war ja 35% vom GesamtjahresZiel)
Die Herausforderung liegt im Bereich C32:F35.
Ich suche eine Kombination die beinhaltet:
A. Das Tagesziel für den Tag 1 von 100 (Gesamt) wird erreicht

Das kann man ja nicht sagen weil es ja keine Tageszielvorgabe (Plan) gibt
C. Für das ganze JAhr wird der Planwert 650 erreicht.

Dies steht in Zelle D12
B. Die Historie wird in Kombination mit dem Jahresziel der Abteilung (z.B. für Abt. A= 35%) erfüllt.

eine Abfrage ob das Ziel erfüllt ist (35% Plan -11,92% IST) kann analog der Formel in D12 angepasst werden.
Grüße
Anzeige
AW: Summenprodukt / Solver
08.08.2012 16:29:22
Dierk
Guten Tag Jack,
schwierig so über einen Chat. Ich denke wir reden noch ein bisschen aneinander vorbei. Tut mir leid, dass ich micht nicht verständlicher ausdrücke.
Noch einmal zu A:
Doch eine Tagesvorgabe gibt. Die soll sich im ersten Wurf an t-1 an der Historie orientieren. Sprich plane ich heute den 09.08.2013 müsste ich nachschauen, welchen Umsatz habe ich der Abteilung 1 am 9.8.12 erzielt. Diese Logik habe ich versucht in C17:F20 abzubilden.
Ich brauche im Planungsprozess also 3 Erfolge:
AA: Generell wird der Wert 650 erreicht und
AB: Pro Abteilungwird am Jahresende (rein planerisch) z.B. 35% von 650 erreicht. Ich denke an diesem Punkt haben wir aneinander vorbeigeret.
AC: Das Gesamtziel pro Tag wird im Planungsprozess ebenfalls erreicht. Z.B. 100 für Tag 1.
Gruß
Dierk
Anzeige
AW: Summenprodukt / Solver
09.08.2012 10:07:08
Jack
Hallo Dierk
kein Problem das ist nunmal so mit dem Formulieren von Problemstellungen.
Vielleicht hilft es sozusagen wenn du dein Problem für dich nochmal formulierst. Und eine klare Aufgabenstellung daraus entwickelst. Das hat mir zumindest immer geholfen. Das Problem ist doch, dass wir alle in unserer kleinen Welt leben =)
Doch eine Tagesvorgabe gibt. Die soll sich im ersten Wurf an t-1 an der Historie orientieren. Sprich plane ich heute den 09.08.2013 müsste ich nachschauen, welchen Umsatz habe ich der Abteilung 1 am 9.8.12 erzielt. Diese Logik habe ich versucht in C17:F20 abzubilden. 

Sollen die denn absolut oder relativ sein? Aktuell sind sie ja relativ.
Und das es Jahresübergreifend sein soll, erschliesst sich mir grad eben erst =)
Also gehen wir dakor dass
AA: Generell wird der Wert 650 erreicht und 

erfüllt ist (in Zelle D12)?
Und, wie möchtest du
AB: Pro Abteilungwird am Jahresende (rein planerisch) z.B. 35% von 650 erreicht. Ich denke an diesem Punkt haben wir aneinander vorbeigeret.

denn abbilden? Da meine Logik mir sagt, wir haben einen soll-zustand und können den maximal mit dem ist abgleichen und dazu brauchen wir vergleichbare Werte. Das war dass was wir in C25:F25 bzw C28:F28 gemacht haben.
Momentan versteh ich bei dir nämlich das du die IST werte-Absolut mit dem Soll zustand-relativ bewerten willst.
AC: Das Gesamtziel pro Tag wird im Planungsprozess ebenfalls erreicht. Z.B. 100 für Tag 1.

Und das Gesamtziel Pro Tag ergibt sich dann woraus? aus dem Vorjahresumsatz des gleichen Tages oder aus dem Mittelwert der Vorjahresumsätze der gleichen Jahre (also 2.9.11 & 2.9.12) ?
Grüße
AW: Summenprodukt / Solver
09.08.2012 16:17:34
Dierk
Hi, ich versuche es einfach noch einmal.
So richtig habe ich keine Idee mehr.
Der Tagesumsatz einer Abteilung wird von 3 Faktoren beeinflusst.
Bzw. läuft der Planungsprozess wie folgt:
1. Es wird ein Gesamtziel vorgegeben, hier 650
2. Es wird eine % Verteilung pro Abteilung vorgegeben. 35% als für Abt. A vom Gesamtziel (650)
3. Es wird ein Gesamttagesziel definiert. Hier der Bereich D7:D10. Z.B. für Tag 1 15%
Früher wurde jetzt einfach jede Abteilung für den Tag 1 mit dem JAhresziel der Abteilung pro Tag geplant. Also, 0,35 x 100 (D7 x C 25).
Jetzt käme das neue. Alles bleibt so bestehen, aber zusätzlich wird pro Tag noch der Bereich C17:F17) berücksichtigt/gewichtet. Die bestehende Vorgehensweise wird also erweitert, so dass am Ende das Ziel pro Tag 1 (Tag 1 = 100) aber auch das Gesamtziel der Abteilung am Ende des Jahres (Abt A = 35%) wird auch noch erreicht.
Und hier liegt mein Problem :-)
Alles ist nur ein Plan. Weder Soll noch Ist spielen eine Rolle.
VG
Dierk
AW: Summenprodukt / Solver
09.08.2012 16:50:17
Jack
Mit anderen Worten, relevant ist das Jahresziel, bzw. die Zielerreichung (35% des JZ)
Und jetzt möchtest du ein Tagesziel einbinden das sich am Jahresziel Orientiert..?
Es scheint mir eher ein logischer Dissenz in der Aufgabenstellung
Und ich weiß immer noch nicht welche Zahl du errechnen willst..?
Das einzige was ich mir vorstellen kann :
Jahresziel 2011 = 650
Abt. A Zielerreichung 35%
Abt A Zielerreichung Tag 1 5% + 2 Tag 3% .. bis Tag 365 und die müssen in der Summe 100% Ergeben
jetzt erhöhst du das Jahresziel 2012 bei gleicher Zielerreichung 35%
und wilst wissen wie hoch die Zielerreichung je Tag sein müssen.
AW: Summenprodukt / Solver
09.08.2012 21:09:04
Dierk
Guten Abend, mag sein das einen logischen Dissens gibt, deshalb soll das Thema ja mathematisch gelöst werden.
Ich bin nahezu ratlos, in meiner letzten Mail habe ich doch die 3 Anforderungen beschrieben.
Vielleicht ist noch ein Konten drin.
Einigen wir uns daraus dass im Beispiel das Jahr 4 Tage hat und wir das Jahr 2013 planen.
Fast alle Informationen sind Planwerte für 2013. Nur C17:F19 sind Istwerte aus 2012.
Die Planung soll verfeinert/optimiert werden und zwar auch Tagesniveau. Berücksichtige ich die Informationen aus C17:F19 nicht, wird wie oben beschrieben der Tag 1 für Abt. A wie folgt geplant: 100 (D7) ist ja ebenfalls fix und vorgegeben: 100 x 0,35. Das macht man für jede Abteilungen und jeden Tag. Zieht man die Spaltensumme für die Abteilung A ist das Ziel logischerweise erreicht. Nicht das Ziel im Ist, sondern der Planwert ist korrekt verteilt.
Ich bleibe weiter im Plan und möchte das jetzt einfach verfeinern. Und deshalb habe ich die IStwerte aus der Vergangenheit um einfach die Verteilung pro Tag zu optimieren. Die Zeilensumme ist ja bekanntlich ebenfalls vorgegeben. Tag 1 = 100 und wenn ich das für alle Tage und Abteilungen durchziehe, ist die Budgetvorgabe von 35% für A erreicht. Das muss am Ende als Summe einfach rauskommen. Das Ist im Jahr 2013 spielt heute und in dieser Planung keine Rolle.
Hilft Dir das?
Gruß
Dierk
AW: Summenprodukt / Solver
10.08.2012 09:17:32
Jack
Hallo Dierk
Ich denk am Wochenende mal in Ruhe drüber nach =)
Zwischen der Arbeit ist das wahrscheinlich auch eher schwierig =)
Grüße
AW: Summenprodukt / Solver
12.08.2012 10:22:03
Dierk
Hallo Jack,
Danke
Dierk

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige