Finance · Intermediate
Turn three years of a Kenyan manufacturer's sales into a Power BI report that shows the CFO where profit is made and lost.
Baraka Manufacturing is a fictional Kenyan maker of cooking oil, flour, soap, detergent, juice and tissue. It sells to supermarkets, distributors, small shops, government buyers and hotels in four Kenyan cities and three export markets. Every month the CFO receives a spreadsheet of sales and profit that takes days to compile and cannot be filtered. She wants an interactive Power BI report instead: sales, discounts, cost and profit by segment, market, product and month, compared with last year and with the sales plan. You have four CSV files from the finance system.
Your role
Financial Analyst, FP&A team, Baraka Manufacturing
Finance cannot quickly say which segments, products and markets drive profit, how much revenue is given away in discounts, or whether the business is ahead of last year and of plan. Next year's budget depends on a reliable, self-service view of performance.
Load all four CSV files into Power BI Desktop with Get data, then Text/CSV. In Power Query, check each column's type: month as a date, money and units as whole numbers, names as text. Checkpoint: baraka-sales has 6,264 rows and baraka-targets has 180.
Create a date table in DAX with CALENDAR covering 2023 to 2025, add year and month columns, and mark it as a date table. Build a small segment table (one row per segment). Relate sales and targets to the date table and the segment table, and sales to products and markets. Check every relationship is one-to-many from the dimension to the fact table.
Synthetic data. Baraka Manufacturing is fictional; the data was generated to follow the layout of Microsoft's Financial Sample workbook, re-set in Kenya. All money is in whole Kenyan shillings (KES). The data covers January 2023 to December 2025. Four CSV files: • baraka-sales.csv: 6,264 rows × 13 columns, the fact table. One row is the sales of one product to one segment in one market in one month, at one discount band. Gross sales are units × sale price; discounts are taken off gross sales to give sales; profit is sales minus cost of goods sold (COGS). • baraka-products.csv: 6 rows, one per product, with category, pack size, standard cost, list price and launch year. • baraka-markets.csv: 7 rows, one per market (four Kenyan cities and three export cities), with country, region and whether it is domestic or export. • baraka-targets.csv: 180 rows, the sales plan for each segment in each month. Relate the sales table to products on product and to markets on market. The targets table is planned by month and segment, so it needs a segment table and your date table to connect to sales; building those is part of the project. There is no separate date table: you create one in DAX. The data is already clean, so the focus is modelling, measures and design.
How to do this project
The scenario
Objectives
Tasks and business questions
Dataset and data dictionarybaraka_salesOne row per month, segment, market, product and discount band, with units, prices, discounts, cost and profit in KES. · 6,264 rows
Write explicit measures: Total Sales, Gross Sales, Total Discounts, Discount %, Total COGS, Total Profit, Profit Margin, Units Sold, then Sales Last Year (SAMEPERIODLASTYEAR), Sales Growth %, Sales YTD (TOTALYTD), Target Sales and Variance to Target.
Page 1: KPI cards for sales, profit, margin and variance to target, a monthly sales and profit trend with last year alongside, and slicers for year, segment and market. Page 2: profit and margin by segment, product and discount band. Page 3: markets, comparing domestic and export, and actual against target by month.
Write one page for the CFO: three findings and three recommendations, each tied to a chart in your report. Submit the .pbix file, a screenshot of each page and the note.
| Column | Type | Description | Example |
|---|---|---|---|
sale_id | integer | Unique row number. | 1 |
month | date | First day of the month the sales belong to. | 2023-01-01 |
segment | text | Customer group: Supermarkets, Distributors, Small shops, Government, or Hotels and restaurants. | Supermarkets |
market | text | City the goods were sold in; relates to baraka-markets. | Nairobi |
product | text | Product name; relates to baraka-products. | Baraka Cooking Oil 1L |
discount_band | text | Discount given: None (0%), Low (3%), Medium (7%) or High (15%) off gross sales. | Medium |
units_sold | integer | Units sold. | 849 |
sale_price_kes | integer | Price per unit before discount, KES. | 347 |
gross_sales_kes | integer | units_sold × sale_price_kes, before discount. | 294603 |
discounts_kes | integer | Value of the discount given, KES. | 20622 |
sales_kes | integer | Gross sales minus discounts: what the customer paid. | 273981 |
cogs_kes | integer | Cost of goods sold: what it cost to make the units, KES. | 193572 |
profit_kes | integer | sales_kes minus cogs_kes. Negative means a loss. | 80409 |
A Power BI file (.pbix) with the star schema, the date table and your measure librarybaraka_productsOne row per product with category, pack size, standard cost, list price and launch year. · 6 rows
| Column | Type | Description | Example |
|---|---|---|---|
product | text | Product name; the key. | Baraka Mango Juice 1L |
category | text | Product category. | Beverages |
pack_size | text | Size of one unit. | 1 litre |
standard_cost_kes | integer | Planned cost to make one unit in 2023, KES. Actual cost in the sales table rises over time and is higher for exports. | 104 |
list_price_kes | integer | Standard selling price per unit, KES. Segments pay slightly different prices. | 179 |
launched | integer | Year the product was launched. | 2022 |
baraka_marketsOne row per market city with country, region and whether it is domestic or export. · 7 rows
| Column | Type | Description | Example |
|---|---|---|---|
market | text | Market city; the key. | Kampala |
country | text | Country of the market. | Uganda |
region | text | Kenyan region, or Export for markets outside Kenya. | Export |
market_type | text | Domestic or Export. | Export |
baraka_targetsThe sales target for each segment in each month, KES. · 180 rows
| Column | Type | Description | Example |
|---|---|---|---|
month | date | First day of the planned month. | 2024-01-01 |
segment | text | Customer segment the target is for. | Distributors |
target_sales_kes | integer | Planned sales for that segment and month, KES, rounded to the nearest thousand. | 1854000 |
The model is a clean star schema with correct relationships and a marked date table. Measures are explicit DAX measures, not implicit sums, and the prior-year and plan comparisons are correct. The report is easy to read and the recommendations follow from the numbers in it.
Recommended tools
Skills covered