URGENT: Q1 & Q2 Performance Report Needed ASAP

Sarah Jenkins <sjenkins@ledgercapital.com>

To: Junior Analyst Team

Jan 20, 2026

8:42 AM

Hi Team,

I know it’s late, but the partners just asked for a full breakdown of our Crypto trading activity for the first half of 2025. I need this dashboard on my desk before the morning meeting.

I’ve attached the raw transaction logs. It’s a mess—just a dump from the trading engine. Please clean it up and build a dashboard that answers the requirements below.

Please handle these tasks strictly in order:

Phase 1 Fix The Data

Do not skip

Format as Table

Convert the raw range to an official Excel Table (Ctrl+T). Name it "CryptoData".

Missing Values

We have Units and Price, but no total. Add a "Gross_Value" column to the source data.

Formula: = [Units] * [Price_Per_Unit]

Phase 2 The Dashboard

Create a new sheet named "Dashboard" for these items:

Task 3: Portfolio

Show Total Units held for each Asset.

Task 4: Volume

Show Count of Transactions per Exchange.

⚠️ Warning: Do not Sum!

Task 5: Monthly Trends

Show Sum of Gross Value broken down by Month.

Hint: Drag Date to Rows > Right-click > Group

Phase 3 Deep Dive (Advanced)

Task 6: Net Cost Analysis

I need to see "Net Cost" (Gross Value + Fees). Do not add columns to source data. Use a Calculated Field.

PivotTable Analyze > Fields, Items & Sets > Calculated Field

Task 7: Market Share

Create a table of Exchange vs Gross Value. Display the numbers as % of Column Total.

Phase 4 Visuals & Formatting

Task 8: Slicers. Add interactive buttons for "Type" and "Exchange".
Task 9: Heat Map. Create a table of Fees by Asset. Apply Red-White-Green Conditional Formatting to the values.
Task 10: Export Ready Layout.
Create a PivotTable with Exchange and then Asset in the Rows area. Change the Report Layout to Tabular Form and remove Subtotals.

How to: Click inside PivotTable > Design Tab > Report Layout > Show in Tabular Form.

Phase 5 Final Audit & Submission

Task 11: Audit Drill-Down

The fees for BTC in March look suspiciously high. Locate that cell in your Monthly PivotTable and Double-Click it to generate an audit list on a new sheet.

Pro Tip: Double-clicking a value field in a PivotTable automatically creates a new worksheet containing all the raw data rows that make up that specific number.

Task 12: Save & Submit

Save your final file as an Excel Workbook (.xlsx). Do not stay in CSV format or you will lose your dashboards.

Reply to this email when you are done, then upload your .xlsx file to Canvas.

Thanks,

Sarah

Portfolio Manager, Ledger Capital