Bivariate Regression mit Excel?

Wissenschaft Wirtschaftswissenschaften
1

(wow, jetzt bin ich aber stolz, dass ich das Wort schon ohne größere Fehler in den Betreff schreiben konnte…)

Liebe BWL-Experten,

meine Statistikvorlesungen sind schon einige Jahrhunderte her (kommt mir jedenfalls so vor)- vielleicht ist jemand von euch noch näher am Thema und kann mir weiterhelfen.

Ich möchte für rund 1000 Filialen des Unternehmens den Umsatz anhand der Variablen „Anzahl der Haushalte“ und „Kaufkraft pro Einwohner“ erklären. Beide Daten liegen uns von der Ges. f. Konsumforschung vor - unseren Umsatz pro Filiale kennen wir zufälligerweise auch ;=). Für jede Filiale ist anhand von PLZ-Gebieten ein Einzugsbereich definiert, auf dessen Basis die Daten in einer Exceltabelle aufbereitet werden.

Wenn mich meine dunkle Erinnerung an BWL-Vorlesungen nicht ganz täuscht, suche ich nach einer Gerade, die durch die Parameter

Umsatz = a + bx + cy

bestimmt ist (wobei eben x und y die beiden für jede Filiale gegebenen Größen sind).

Die Varianz dieser Regressionsgeraden als eine Zahl zwischen 0 und 1 gibt mir die Zuverlässigkeit dieser Geraden an - je höher der Wert, desto sicherer läßt sich der Umsatz durch diese beiden Parameter erklären.

Mit der Standardabweichung kann ich einen Korridor um den erwarteten Wert bilden, innerhalb derer sich ein Großteil der Filialen wiederfindet. Niederlassungen, die darunter liegen gelten als Underperformer, die Filialleitungen mit Leistungen darüber bekommen zu Weihnachten eine Tafel Schokolade extra.

Soweit so gut - das habe ich verstanden (wenn ich es denn richtig verstanden habe). Bloß: Wie berechne ich diese Gleichung? Ich hege ja noch die stille Hoffnung, dass das mit Excel zu vereinfachen ist.

Kann hier jemand helfen?

Gespannt auf eure fachkundigen Erklärungen

Rainer

2

Rückfrage (aber leider keine Antwort)
Hallo Rainer,

Liebe BWL-Experten,

Bin ich nicht, kann’s ja aber trotzdem mal versuchen.

Wenn mich meine dunkle Erinnerung an BWL-Vorlesungen nicht
ganz täuscht, suche ich nach einer Gerade, die durch die
Parameter

Umsatz = a + bx + cy

bestimmt ist (wobei eben x und y die beiden für jede Filiale
gegebenen Größen sind).

Schauen wir mal, wenn ich dich richtig verstehe sind die GRößen wie folgt definiert:
Umsatz Umsatz (gegeben)
b „Haushaltskoeffizient“ (zu bestimmen)
x Anzahl der Haushalte eines Fillialgebietes (gegeben)
c „Kaufkraftkoeffizient“ (zu bestimmen)
y Kaufkraft pro Einwohner eines Fillialgebietes (gegeben)

Frage: Was ist a? Darunter kann ich mir nichts vorstellen. Das einzige was mir einfallen würde, wäre eine Art „Grundumsatz“ pro Filliale, aber das ist in diesem Fall nicht gegeben: wenn x=0 (keine Haushalte, z.B. am Nordpol), dann muss doch auch der Umsatz Null sein, oder?

Soweit so gut - das habe ich verstanden (wenn ich es denn
richtig verstanden habe). Bloß: Wie berechne ich diese
Gleichung? Ich hege ja noch die stille Hoffnung, dass das mit
Excel zu vereinfachen ist.

Bei konstantem c sieht die Gleichung so aus:
U=bx+const.
Du kannst nun aus den Werten für das konstante c eine passende Gerade berechnen (Methode der kleinsten Quadrate).
Wie das in Excel am einfachsten geht, kann ich Dir leider nicht sagen.

Grüße,

Anwar

3

Jetzt mit Antwort

Bei konstantem c sieht die Gleichung so aus:
U=bx+const.
Du kannst nun aus den Werten für das konstante c eine passende
Gerade berechnen (Methode der kleinsten Quadrate).
Wie das in Excel am einfachsten geht, kann ich Dir leider
nicht sagen.

Hab’ ich gerade mal rausgesucht:
Die Funktion heißt RGP und ist recht einfach zu benutzen, einfach mal in der Hilfe unter RGP nachschauen.
Bleibt wahrscheinlich das Problem, dass Du keine übereinstimmenden Werte für c bzw. b hast. Da musst Du vielleicht approximieren.

Aber bestimmt kommt gleich jemand und verrät Dir eine bessere Lösung.

Gruß,

Anwar

4

Das wäre schon mal die erste Variable.
Hallo Anwar,

erst einmal herzlichen Dank für deine beiden Hinweise. Werde mich also mal mit der Funktion RGB beschäftigen.

Das „a“ in der Gleichung steht da, weil es zu einer generischen Geradengleichung gehört. Mal angenommen, man zeichnete eine Gerade durch die Punktwolke, die sich im 3D- Koordinatensystem aus Haushalt, Kaufkraft und Umsatz ergibt, dann wird diese Gerade wahrscheinlich einen positiven Achsenabschnitt auf der Umsatzachse haben, obwohl es deiner Erklärung nach theoretisch unsinnig wäre. Diese Konstante gehört bei Geraden halt dazu.

Das „c“, also die Kaufkraft einfach konstant zu lassen, ist eine Näherung, geht aber noch an der Realität vorbei, denn unserer Erfahrung nach ist die regional gegebene Kaufkraft der Parameter mit der höchsten Erklärungskraft.

Immerhin habe ich aus deiner Erklärung schon mal einen Weg zu einer Regression mit einer Variablen gefunden - vielleicht kann mir noch jemand zeigen, wie ich die zweite Variable im Modell einführe.

Also - ganz herzlichen Dank, die Weihnachtstage sind gerettet, ich werde über der RGB-Funktion in der Excel-Hilfe brüten. (Nein, ganz so workoholic bin ich nicht, es wird eher ein guter Vorsatz fürs neue Jahr.)

Ciao

Rainer

5

…und auch die zweite, oder?
Hallo Rainer

erst einmal herzlichen Dank für deine beiden Hinweise. Werde
mich also mal mit der Funktion RGB beschäftigen.

Ja, die erscheint mir recht mächtig zu sein. Wundert mich, dass ich über die nicht schon mal eher gestolpert bin…

Das „a“ in der Gleichung steht da, weil es zu einer
generischen Geradengleichung gehört.

Das ist klar (bin kein BWL-Experte, aber für Mathe reicht’s noch!).

Mal angenommen, man
zeichnete eine Gerade durch die Punktwolke, die sich im 3D-
Koordinatensystem aus Haushalt, Kaufkraft und Umsatz ergibt,
dann wird diese Gerade wahrscheinlich einen positiven
Achsenabschnitt auf der Umsatzachse haben, obwohl es deiner
Erklärung nach theoretisch unsinnig wäre. Diese Konstante
gehört bei Geraden halt dazu.

Wahrscheinlich hast Du da mehr Erfahrung. Sagen wir mal mit dem „a“ hältst du den Achsenabschnitt offen, was mir zwar nicht ganz einleuchten will, aber dass macht ja nichts (im „Idealfall“ is a ja ganz einfach gleich Null und meine (Vorstellungs)Welt ist gerettet.

Das „c“, also die Kaufkraft einfach konstant zu lassen, ist
eine Näherung, geht aber noch an der Realität vorbei, denn
unserer Erfahrung nach ist die regional gegebene Kaufkraft der
Parameter mit der höchsten Erklärungskraft.

Da hast Du mich falsch verstanden. Ich meinte das so: Du fasst alle jeweils gleichen Fillialen (also z.B. alle Fillialen mit der Kaufkraft K1, K2, K3…) zusammen und bildest daraus die lineare Regression. Theoretisch, d.h. wenn die von Dir angenommene Gleichung die Realität gut beschreibt müsste bei allen Gruppen (K1,K2,K3…) ungefähr das Gleiche b herauskommen.
Dann gruppierst Du um, so dass alle Fillialen mit der Gleichen Haushaltsanzahl zusammenstehen und machst da das gleiche. Hiermit kannst Du nun mehrere c’s berechnen.
Dann bist Du fertig.

Immerhin habe ich aus deiner Erklärung schon mal einen Weg zu
einer Regression mit einer Variablen gefunden - vielleicht
kann mir noch jemand zeigen, wie ich die zweite Variable im
Modell einführe.

Ist gerade geschehen.

Also - ganz herzlichen Dank, die Weihnachtstage sind gerettet,
ich werde über der RGB-Funktion in der Excel-Hilfe brüten.
(Nein, ganz so workoholic bin ich nicht, es wird eher ein
guter Vorsatz fürs neue Jahr.)

Na, manchmal macht’s ja auch fast Spaß ein wenig am PC zu tüfteln… :smile:

Grüße,

Anwar

PS: Wenn das jetzt nicht ganz klar geworden ist, frag ruhig nochmal nach, dann mach ich ein Beispiel in Excel und schick es Dir zu.

6

Und die Dritte, vierte, und so weiter.
Vielen Dank, Anwar, das hilft sehr, ich bin schon am Tüfteln. So kann ich meinen Chef nach Neujahr mit einer passenden Analyse überraschen.

Thx nochmals

Rainer

7

Hi,

ich weiß, Deine Frage ist schon ein Jahr her :smile:
Also das mit Excel weiß ich nicht, aber definitiv und sehr einfach geht das mit SPSS. Da gibt es unter Analysieren einerseits die einfache Variante Varianz und Standdardabweichungen zu berechen. Und dann kannst Du über lingeare Regression alle norwendigen Tests durchführen um die Signifikantionsniveaus zu bestimmen.

Gruß Susanne

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]