Ordering PCBs from fabricators in PCB Design - Real Business Scenario

Scenario Mode

👤 Your Role: You are a procurement analyst at an electronics manufacturing company.

📋 Request: Your manager wants a report to track PCB orders from different fabricators, including order quantities, costs, and delivery times, to optimize future orders.

📊 Data: You have data on PCB orders including fabricator name, order date, quantity ordered, cost per unit, total cost, and delivery date.

🎯 Deliverable: Create a dashboard showing total quantity ordered, total cost, average delivery time by fabricator, and a trend of monthly orders.

Sample Data

Fabricator	Order Date	Quantity	Cost per Unit ($)	Total Cost ($)	Delivery Date
FabTech	2024-01-10	100	5.00	500.00	2024-01-20
PCBWorks	2024-01-15	150	4.80	720.00	2024-01-25
FabTech	2024-02-05	200	4.90	980.00	2024-02-15
PCBWorks	2024-02-10	120	4.85	582.00	2024-02-20
ElectroFab	2024-02-12	80	5.10	408.00	2024-02-22
FabTech	2024-03-01	150	4.95	742.50	2024-03-10
PCBWorks	2024-03-05	100	4.90	490.00	2024-03-15
ElectroFab	2024-03-08	90	5.05	454.50	2024-03-18

1

Step 1: Import the PCB orders data into your BI tool.

Load the table with columns: Fabricator, Order Date, Quantity, Cost per Unit, Total Cost, Delivery Date.

Expected Result

Data table with 8 rows loaded correctly.

2

Step 2: Create a calculated column 'Delivery Time (days)' to find days between Order Date and Delivery Date.

Delivery Time (days) = DATEDIFF([Order Date], [Delivery Date], DAY)

Expected Result

New column showing delivery times: e.g., 10, 10, 10, 10, 10, 9, 10, 10 days.

3

Step 3: Create a measure 'Total Quantity' summing all quantities per fabricator.

Total Quantity = SUM('Orders'[Quantity])

Expected Result

Total quantities: FabTech=450, PCBWorks=370, ElectroFab=170.

4

Step 4: Create a measure 'Total Cost' summing all total costs per fabricator.

Total Cost = SUM('Orders'[Total Cost])

Expected Result

Total costs: FabTech=$2222.50, PCBWorks=$1792.00, ElectroFab=$862.50.

5

Step 5: Create a measure 'Average Delivery Time' calculating average delivery days per fabricator.

Average Delivery Time = AVERAGE('Orders'[Delivery Time (days)])

Expected Result

Average delivery times: FabTech=9.67 days, PCBWorks=10 days, ElectroFab=10 days.

6

Step 6: Create a line chart showing monthly total quantity ordered across all fabricators.

Use 'Order Date' grouped by month on X-axis, 'Total Quantity' measure on Y-axis.

Expected Result

Line chart showing quantities: Jan=250, Feb=400, Mar=340.

7

Step 7: Create a summary table visual with Fabricator, Total Quantity, Total Cost, and Average Delivery Time.

Add columns and measures: Fabricator, Total Quantity, Total Cost, Average Delivery Time.

Expected Result

Table showing each fabricator's totals and averages as calculated.

Final Result

---------------------------------------------
| Fabricator | Total Qty | Total Cost | Avg Delivery |
|------------|-----------|------------|--------------|
| FabTech    | 450       | $2222.50   | 9.67 days    |
| PCBWorks   | 370       | $1792.00   | 10 days      |
| ElectroFab | 170       | $862.50    | 10 days      |
---------------------------------------------

Monthly Orders Trend (Quantity):
Jan: 250
Feb: 400
Mar: 340

(Line chart with months on X-axis and quantity on Y-axis)

✓FabTech has the highest total quantity ordered and total cost.

✓Average delivery times are consistent around 10 days for all fabricators.

✓February had the highest monthly order quantity.

✓PCBWorks and ElectroFab have similar delivery times but lower order volumes.

Bonus Challenge

Add a KPI to highlight fabricators with delivery times longer than 10 days and suggest prioritizing those with faster delivery.

Show Hint

Create a measure to flag orders with Delivery Time > 10 and use conditional formatting or a filter in your dashboard.