Workshop 1 - Accounting Arquitectures - Module 2
1 Introduction
I first provide a condensed review of the concepts and skills you acquired in the pre-requisite course. Read it carefully before the second session of the week. At the end there is a challenge, which is a set of questions you have to respond with your own words, and then use an LLM to check your understanding
In this course we will use the web version of PowerBI for all exercises during the course. This version is also called Power BI Service.
Why the web version? Because it runs on any operating system, is the environment where models are actually published, shared, refreshed and governed inside modern organizations, and is what your future employer will most likely give you access to. Compared to the Desktop Power BI version, the concepts are identical; the interface is quite different.
1.1 What business analytics is
Business Analytics (or Data Science for Business) is the intersection of statistics, mathematics, computer science and a business knowledge domain such as Accounting, Finance, Marketing or Operations. It covers the whole chain: data collection, data integration, data preparation, analysis, and finally the translation of results into decisions.
We classify business analytics in four levels, in increasing order of value and difficulty:
| Level | Question it answers | Typical techniques |
|---|---|---|
| Descriptive | What happened? | Data management, aggregation, visualization |
| Diagnostic | Why did it happen? | Drill-down, segmentation, ratio decomposition |
| Predictive | What will happen? | Statistical models, machine learning |
| Prescriptive | What should we do? | Optimization, scenario analysis, strategy |
Everything you did in the pre-requisite course - pivot tables, slicers, dashboards, DuPont ratios, Power BI reports - belongs mostly to descriptive and diagnostic analytics. That is the foundation. You cannot predict or prescribe on top of data you have not correctly described.
A dashboard that nobody uses to decide something is not analytics. It is decoration. Every visual you build in Part II must be tied to a management question.
1.2 The income statement as a calculation chain
Almost every profitability metric in this course is a step in one single chain. Memorize the chain, not the formulas.
\[S_t-COGS_t-SGAE_t-DEP_t = EBIT_t\]
\[NI_t = EBIT_t - IE_t - T_t\]
Where:
- \(S_t\) = Net Sales during period \(t\) (gross sales minus discounts and returns)
- \(COGS_t\) = Cost of Goods Sold during period \(t\)
- \(SGAE_t\) = Selling, General and Administrative Expenses
- \(DEP_t\) = Depreciation
- \(EBIT_t\) = Earnings Before Interest and Taxes (operating profit)
- \(IE_t\) = Interest (financial) expenses
- \(T_t\) = Income tax
Two intermediate results matter enormously in retail and distribution businesses:
\[GrossProfit_t = S_t - COGS_t\]
\[GrossProfitMargin_t = \frac{S_t - COGS_t}{S_t}\]
Gross profit margin isolates the product/pricing decision (what we charge versus what the product costs us), while EBIT margin adds the commercial and administrative decision (how much we spend to sell it). When margin falls, the first diagnostic question is always: did the price fall, did the discount rise, or did the cost rise?
1.3 Financial ratios and the DuPont decomposition
A financial ratio is simply one financial number divided by another so that the result becomes comparable across firms and across time.
Return on Assets measures how much net income the firm generates during period \(t\) for each $1.00 invested in total assets at the beginning of the period:
\[ROA_t=\frac{NI_t}{TA_{t-1}}\]
Multiplying by \(\frac{S_t}{S_t}\) (which changes nothing) we obtain the DuPont decomposition:
\[ROA_t=\left(\frac{NI_t}{S_t}\right)*\left(\frac{S_t}{TA_{t-1}}\right) = ProfitMargin_t \times AssetTurnover_t\]
- Profit Margin = how much profit each $1.00 of sales leaves behind → a pricing / differentiation lever.
- Asset Turnover = how many times the firm sells the value of its assets → an efficiency / low-cost lever.
A firm improves ROA by improving margin, improving turnover, or both. High margin + low turnover suggests a differentiation strategy (luxury, brands, specialty). Low margin + high turnover suggests a cost leadership strategy (discount retail, commodities).
The same logic of decomposing a ratio into interpretable drivers is what you will apply in Part II when you break gross margin into price, discount and unit cost.
Never judge a ratio in isolation. 8% profit margin is excellent in grocery retail and catastrophic in software. Always compare against an industry benchmark, a prior period, or a peer segment.
1.4 The two percentage traps
These two mistakes are the most frequent errors in business reporting, and both of them will cost you points in this course.
1.4.1 Trap 1: the arithmetic average of ratios
Consider a firm selling only two products:
| Product | Units Sold | Unit Price | Sales | Unit Cost | Cost | Gross profit | GP margin |
|---|---|---|---|---|---|---|---|
| A | 10,000 | $1.00 | $10,000 | $0.40 | $4,000 | $6,000 | 60% |
| B | 1,000 | $90.00 | $90,000 | $81.00 | $81,000 | $9,000 | 10% |
| Total | $100,000 | $85,000 | $15,000 | ? |
- Arithmetic average of the two margins: \((60\% + 10\%)/2 = 35\%\) ❌
- Weighted by sales: \(0.10(60\%) + 0.90(10\%) = 15\%\) ✅
- Total gross profit / total sales: \(15{,}000/100{,}000 = 15\%\) ✅
Rule: to average a ratio, never average the ratios. Sum all the numerators, sum all the denominators, then divide.
In Excel this is why you used a Calculated Field instead of dragging the ratio column into a pivot table. In Power BI this is why you write a measure
Gross Profit Margin = DIVIDE( [Gross Profit], [Net Sales] )and never AVERAGE(Sales[MarginColumn]). A measure recomputes the numerator and the denominator inside whatever filter context the visual imposes, which is exactly the weighted average you want.
1.4.2 Trap 2: the arithmetic average of growth rates
| Period | Investment value | Simple return (R) |
|---|---|---|
| 0 | $100 | NA |
| 1 | $50 | -0.50 |
| 2 | $75 | +0.50 |
The arithmetic average is \(0\%\), yet you ended with $75 after starting with $100. Simple returns are multiplicative, so they must be compounded:
\[HPR=\left(1+R_{1}\right)\left(1+R_{2}\right)\dots\left(1+R_{N}\right)-1 = (0.5)(1.5)-1=-25\%\]
\[\bar{R_{g}}=\sqrt[N]{\left(1+R_{1}\right)\dots\left(1+R_{N}\right)}-1 = \sqrt{0.75}-1=-13.4\%\]
The elegant alternative is the continuously compounded return, because logs are additive:
\[r_{t}=\log(V_{t})-\log(V_{t-1}) = \log\left(\frac{V_{t}}{V_{t-1}}\right)\]
\[hpr = r_1+r_2 = -0.6931+0.4054 = -0.2877 \quad\Rightarrow\quad HPR = e^{-0.2877}-1 = -25\%\]
\[\bar{r}=\frac{r_1+r_2}{2}=-0.14385 \quad\Rightarrow\quad \bar{R}=e^{-0.14385}-1=-13.4\%\]
Same answer as the geometric mean. Lower-case \(r\) = continuously compounded; upper-case \(R\) = simple. Use \(r\) for statistics and modelling; report \(R\) to management.
1.5 Data structures: wide versus long
1.5.1 Wide format
One row per entity; each period or category is a separate column.
| Firm | Rev_2020 | Rev_2021 | Rev_2022 | Rev_2023 |
|---|---|---|---|---|
| Apple | 274,515 | 365,817 | 394,328 | 383,285 |
| Microsoft | 143,015 | 168,088 | 198,270 | 211,915 |
| Tesla | 31,536 | 53,823 | 81,462 | 96,773 |
Comfortable to read, terrible to analyse: adding a year breaks the structure, and BI tools cannot group by a column name.
1.5.2 Long format (panel / tidy data)
One row per entity per period. Time becomes a column value, not a column name.
| FirmID | FirmName | Year | Revenue |
|---|---|---|---|
| AAPL | Apple | 2020 | 274,515 |
| AAPL | Apple | 2021 | 365,817 |
| MSFT | Microsoft | 2020 | 143,015 |
| MSFT | Microsoft | 2021 | 168,088 |
| TSLA | Tesla | 2020 | 31,536 |
| TSLA | Tesla | 2021 | 53,823 |
WIDE FORMAT LONG FORMAT
───────────────────────────── ──────────────────────────────────
Firm Rev_2020 Rev_2021 Firm Year Revenue
───────────────────────────── ──────────────────────────────────
Apple 274,515 365,817 → Apple 2020 274,515
Tesla 31,536 53,823 Apple 2021 365,817
Tesla 2020 31,536
Tesla 2021 53,823
───────────────────────────── ──────────────────────────────────
2 rows x 3 columns 4 rows x 3 columns
Going from wide to long is called unpivoting (or melting). In Power Query it is the Unpivot Columns transformation; in R it is tidyr::pivot_longer(). You will need this in Part II: the product cost file is deliberately delivered in wide format.
1.6 The Star database model
A Star Schema organizes analytical data as one central Fact Table surrounded by several Dimension Tables.
+─────────────────+
| DIM_TIME |
|─────────────────|
| DateID (PK) |
| Year / Quarter |
+────────+────────+
|
+─────────────+ | +──────────────────+
| DIM_CUSTOMER| | | DIM_PRODUCT |
|─────────────| | |──────────────────|
| CustomerID | | | ProductID (PK) |
| Name / City | | | ProductName |
| CountryCode +─────+─────+ Division |
| Channel | | +──────────────────+
+──────+──────+ |
| |
| +─────────────────────────────+
| | FACT_SALES |
+───|─────────────────────────────|
| CustomerID (FK) |
| ProductID (FK) |
| EmployeeID (FK) |
| OrderDate (FK) |
| Units / UnitPrice / Discount|
+─────────────────────────────+
Fact table - stores the numeric measurements of business events. One row = one event (one order line, one firm-year). Always in long format. Large. Contains foreign keys plus numeric measures.
Dimension table - stores the descriptive attributes that give context: Who? What? When? Where? Small, one row per entity, contains the primary key referenced by the fact table.
| Concept | Description | Excel analogy | Power BI analogy |
|---|---|---|---|
| Long format | One row per entity-period | The findata sheet |
Any fact table |
| Fact table | Numeric measures per event | Data sheet | Table on the many side |
| Dimension table | Descriptive attributes | Catalog sheet | Table on the one side |
| Foreign key | Column linking fact to dimension | The ticker in VLOOKUP |
The relationship line |
| Star schema | Facts surrounded by dimensions | Model behind pivot tables | The Model view |
The single most important mental shift from Excel to Power BI: you no longer bring the descriptive columns into the fact table with VLOOKUP. You declare a relationship once, and the filter flows automatically from the dimension to the fact. The relationship is the VLOOKUP.
1.7 The REA model
The REA model (McCarthy, 1982) describes any business process with three entity types and three relationship types.
- Resources - things of economic value the firm controls (Inventory, Cash).
- Events - the transactions that increase or decrease resources (Sale, Cash Receipt, Purchase).
- Agents - the people or organizations participating in events; each event has at least one internal agent (employee) and one external agent (customer, supplier).
And the relationships:
- Stockflow - links an Event to a Resource (inflow or outflow).
- Duality - links the two Events of an exchange (Sale ↔︎ Cash Receipt: we give goods, we receive money).
- Participation - links an Agent to an Event.
The mapping to the database model you implement is almost mechanical:
| REA entity | Star Schema counterpart |
|---|---|
| Event | Fact Table |
| Resource | Dimension Table |
| Agent | Dimension Table |
| Stockflow relationship | Foreign key (Fact → Resource dimension) |
| Participation relationship | Foreign key (Fact → Agent dimension) |
| Many-to-many relationship | Becomes its own table (e.g. OrderDetail) |
Remember the cardinality rule: look only at the maximum cardinalities. In a Power BI model you can only implement 1-to-1 and 1-to-N; an N-to-N relationship must be resolved by an intermediate table (which is why OrderDetail exists between Orders and Products).
1.8 Power BI vocabulary you must already own
1.8.1 Calculated column vs measure
This distinction is the most common source of wrong numbers, so it is worth stating precisely.
| Calculated column | Measure (calculated field) | |
|---|---|---|
| Computed | Row by row, when defined | On demand, when placed in a visual |
| Stored | Yes, in the table (uses memory) | No, only the formula |
| Context | Row context - sees the current row | Filter context - sees whatever the visual filters |
| Use it for | Row-level arithmetic, keys, lookups | Any aggregation, especially ratios |
| Typical example | NetSalesUSD = Units * Price * (1-Disc) / FX |
Margin = DIVIDE([GP],[Net Sales]) |
Rule of thumb: if the result must change when the user clicks a slicer, it must be a measure. If it is arithmetic that only needs the values of the current row, it is a calculated column.
1.8.2 The DAX functions you should already recognize
| Function | Purpose |
|---|---|
SUM, AVERAGE, MIN, MAX |
Basic aggregation over a column |
COUNT, COUNTROWS, DISTINCTCOUNT |
Counting rows / distinct values |
DIVIDE(num, den) |
Safe division (returns blank instead of an error) |
RELATED(Table[Column]) |
Brings a value from the one side - the DAX VLOOKUP |
CALCULATE(expr, filters) |
Evaluates an expression under a modified filter context |
YEAR, MONTH, FORMAT |
Date extraction and formatting |
SUMX, AVERAGEX |
Iterators: evaluate row by row, then aggregate |
2 CHALLENGE: Self-assessment
Answer the following questions in the following way: 1) respond with your OWN WORDS, then 2) Ask the question to any LLM and copy-paste the response, and 3) Write with your OWN WORDS WHAT YOU LEARNED
You have to mention which LLM YOU USED (Gemini, ChatGTP, Claude, etc)
QUESTIONS:
- Write the chain from Net Sales to Net Income, naming every subtraction.
- A firm has ROA of 6%, profit margin of 2%. What is its asset turnover? What strategy does that suggest?
- Division A has a 50% margin on $20,000 of sales; Division B has a 10% margin on $180,000. What is the company margin? Why is it not 30%?
- An investment returns +80% in year 1 and -50% in year 2. What is the average annual return?
- Why can a BI tool NOT group by year when the data is in wide format - the annual values are in columns?
- Give an example of one fact table and three dimension tables for a hotel booking business.
- In the REA (Resource-Events-Agents) model, which entity type becomes a fact table, and why?
- Your data model has an N-to-N relationship between Students and Courses. What must you build?
Margin = AVERAGE(Sales[LineMargin])in a matrix by country. Why is this wrong?- You need a column that concatenates
ProductIDandYearto serve as a key. What do you need to do: a Calculated column or a measure? Why?
Submit your Word document through Canvas
3 RUBRIC FOR GRADING THE WORKSHOP
For this Workshop, the grade will be based on COMPLETIONS of the CHALLENGE. If your OWN RESPONSES are not totally correct, you will get the FULL POINTS, as long as you completed the exercise with LLM responses and your REFLECTIONS on your LEARNING.