The Scenario
You are the Senior Financial Analyst for Bulldog Inc. It is Dec 31, 2026. The CFO is preparing for a board meeting and needs to verify our asset health. They require a dynamic report that allows them to "slice and dice" the data without calling IT.
Your Mission:
Use the dataset below to answer audit questions. You must explicitly define the Filters, Columns, Rows, and Values for each analysis.
Fixed Asset Register
150+ Assets Generated | Ref Date: 12/31/2026
| Asset ID | Purchase Date | Asset Group | Sub-Category | Asset Details (Model) | Orig. Cost ($) | Salvage Value ($) | Useful Life (Yrs) | Years Remaining |
|---|
Simulation 1: The "Drill Through" Audit
Concept: Don't hunt for data. Double Click a number in a Pivot Table to auto-generate a sheet with the source records.
Try it: Double Click the 'Vehicles' Total Cost
| Row Labels | Sum of Cost |
|---|---|
| IT Infrastructure | $125,000 |
| Vehicles | $840,000 |
| Grand Total | $965,000 |
Detailed transactions for Vehicles:
| Asset ID | Sub-Category | Model | Cost |
|---|---|---|---|
| BD-1042 | Executive | Tesla Model S | $85,000 |
| BD-1045 | Logistics | Ford F-150 | $45,000 |
Simulation 2: The Power of Grouping
Concept: Raw dates are messy. Pivot Tables group them automatically. Notice how the individual dates below map to the Quarters on the right.
Pivot with Raw Dates
Group dates into Quarters
Pivot Grouped by Quarter (Clean)
Replacement Budgeting
How much money do we need to replace assets expiring in < 2 years?
Depreciation Expense
=(Cost-Salvage)/Life. Refresh. Drag to Values.
Calculate Annual Depreciation and sum by Group.
The Audit (Drill Through)
How do you see specific rows for Heavy Equipment?
Cost Distribution (%)
What % of total capital is in each Group?
Price Brackets
Group assets into cost buckets of $25,000.
Value Retention
Create Calculated Field "Retention %" (Salvage / Cost).
Dashboarding (Slicers)
Create buttons to filter by Asset Group and Sub-Category.
Pro Tip: Mass Report Generation
The "Hidden" Feature: What if the CEO wants 5 separate tabs—one for each Asset Group? Instead of copying & pasting 5 times, use Show Report Filter Pages.
Menu Path: PivotTable Analyze > Options > Show Report Filter Pages...
The Analyst's Choice
The CEO is walking into the meeting in 5 minutes. They want ONE final insight that we haven't discussed yet.
Come up with a unique business question, design the Pivot Table & Chart, and explain why it matters.