Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
| Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
| modul:mathe:ma4:thema:statistik:aufgaben:pivot-training [2026/03/02 10:52] – kmaurizi | modul:mathe:ma4:thema:statistik:aufgaben:pivot-training [2026/03/02 11:13] (aktuell) – kmaurizi | ||
|---|---|---|---|
| Zeile 1: | Zeile 1: | ||
| + | |||
| ====== LU01.A04 - Pivot Training ====== | ====== LU01.A04 - Pivot Training ====== | ||
| Zeile 21: | Zeile 22: | ||
| ===== Aufgaben ===== | ===== Aufgaben ===== | ||
| - | **Beginner** | + | **Beginner**\\ |
| - | 1. Summarize Sales by Region - Create a pivot showing total sales per region. | + | 1. Summarize Sales by Region - Create a pivot showing total sales per region.\\ |
| - | 2. Count Transactions by Month - Group by month and count the number of transactions. | + | 2. Count Transactions by Month - Group by month and count the number of transactions.\\ |
| - | 3. Total Expenses by Category - Show total spend by expense category. | + | 3. Total Expenses by Category - Show total spend by expense category.\\ |
| - | 4. Filter by a Specific Value - Show sales by product for only the ' | + | 4. Filter by a Specific Value - Show sales by product for only the ' |
| - | + | \\ | |
| - | **Intermediate** | + | **Intermediate**\\ |
| - | 5. Trial Balance Summarization - Show net balances for each account. | + | 5. Trial Balance Summarization - Show net balances for each account.\\ |
| - | 6. Group Accounts by Statement Type - Show totals by Statement type. | + | 6. Group Accounts by Statement Type - Show totals by Statement type.\\ |
| - | 7. Year-over-Year Revenue Comparison - Compare each year's revenue side by side. | + | 7. Year-over-Year Revenue Comparison - Compare each year's revenue side by side.\\ |
| - | 8. Expense Breakdown by Department - Department in rows, Expense Category in columns. | + | 8. Expense Breakdown by Department - Department in rows, Expense Category in columns.\\ |
| - | + | \\ | |
| - | **Advanced** | + | **Advanced**\\ |
| - | 9. Multi-Level P&L Statement - Statement → Line Item hierarchy, grouped by month. | + | 9. Multi-Level P&L Statement - Statement → Line Item hierarchy, grouped by month.\\ |
| - | 10. Balance Sheet as of Month-End - Show as-of balances for each month-end. | + | 10. Balance Sheet as of Month-End - Show as-of balances for each month-end.\\ |
| - | 11. Retained Earnings Calculation - Display retained earnings in a pivot. | + | 11. Retained Earnings Calculation - Display retained earnings in a pivot.\\ |
| - | 12. Margin Analysis by Product - Calculate Gross Margin % per product. | + | 12. Margin Analysis by Product - Calculate Gross Margin % per product.\\ |
| - | 13. Variance Analysis - Show variance amount and variance % in a pivot table. | + | 13. Variance Analysis - Show variance amount and variance % in a pivot table.\\ |
| - | + | \\ | |
| - | **Expert** | + | **Expert**\\ |
| - | 14. Custom Sorting of P&L Lines - Apply a custom order in the pivot. | + | 14. Custom Sorting of P&L Lines - Apply a custom order in the pivot.\\ |
| - | 15. Multi-Dimensional Cube Simulation - Slice and dice data by different combinations of dimensions. | + | 15. Multi-Dimensional Cube Simulation - Slice and dice data by different combinations of dimensions.\\ |
| ===== Abgabe ===== | ===== Abgabe ===== | ||
| Zeile 48: | Zeile 49: | ||
| * Laden Sie das File auf Moodle hoch | * Laden Sie das File auf Moodle hoch | ||
| + | |||
| + | <WRAP center round info 80%> | ||
| + | ===== Begriffe (Erklärungen) ===== | ||
| + | |||
| + | ^ Begriff (EN) ^ Deutsch (kurz) ^ Bedeutung im Excel / Pivot ^ | ||
| + | | **P&L** (Profit & Loss) | Erfolgsrechnung / GuV | Bericht über **Ertrag und Aufwand** über eine Periode (zeigt Gewinn/ | ||
| + | | **Balance Sheet** | Bilanz | Bericht über **Vermögen, | ||
| + | | **Revenue** | Umsatz / Erlös | Einnahmen aus Verkäufen (Ertrag). | | ||
| + | | **COGS** (Cost of Goods Sold) | Wareneinsatz / Herstellkosten | Kosten, die **direkt** für verkaufte Produkte anfallen (z.B. Material/ | ||
| + | | **Gross Profit** | Bruttogewinn | Grobe Rechnung: **Revenue – COGS**. | | ||
| + | | **Operating Expenses** | Betriebskosten (OPEX) | Laufende Kosten des Betriebs (z.B. Marketing, Administration, | ||
| + | | **Net Income** | Nettogewinn | Grobe Rechnung: **Gross Profit – Operating Expenses** (in echt können noch weitere Posten dazugehören). | | ||
| + | | **Department** | Abteilung | Organisatorische Einheit (z.B. Sales, Marketing, Operations). | | ||
| + | | **Category** | Kategorie | Art der Buchung/ | ||
| + | | **Statement** | Abschluss/ | ||
| + | | **Statement Line Item** | Position im Report | Konkrete Zeile/ | ||
| + | | **Amount** | Betrag | Zahlenwert der Buchung/ | ||
| + | | **Transaction ID** | Transaktions-ID | Eindeutige Nummer pro Datensatz/ | ||
| + | | **Date** | Datum | Datum der Buchung/ | ||
| + | | **Region** | Region | Gebiet (z.B. East, North, South, West) für Auswertungen nach Standort/ | ||
| + | | **Product** | Produkt | Produktgruppe (z.B. Product A/B/C) für Auswertungen nach Produkt. | | ||
| + | |||
| + | ===== Zusammenhänge (vereinfacht, | ||
| + | * **Gross Profit = Revenue – COGS** | ||
| + | * **Net Income = Gross Profit – Operating Expenses** | ||
| + | |||
| + | <WRAP round box 80%> | ||
| + | **Hinweis: | ||
| + | Im Pivot-Training reicht das vereinfachte Modell. | ||
| + | </ | ||
| + | </ | ||
| <WRAP center round tip 100%> | <WRAP center round tip 100%> | ||
| + | ====== Lösungshinweise ====== | ||
| ===== Aufgabe 1 – Sales nach Region (gelöst im Workbook, nur nachvollziehen) ===== | ===== Aufgabe 1 – Sales nach Region (gelöst im Workbook, nur nachvollziehen) ===== | ||