Loading…
Loading…
What you will build: A Revenue column and named range, a KPI block, monthly revenue with a line chart, a ranked rep leaderboard with the top two highlighted, and a PivotTable by region.
Sales
You run sales operations for a six-person team. The VP wants a half-year dashboard from 150 orders: total revenue, revenue per month, a rep leaderboard and a regional breakdown, all driven by formulas so it updates when new orders arrive.
Premium includes all 12 projects, every advanced lesson and certificates. Already Premium? Sign in to open it.
A Revenue column and named range, a KPI block, monthly revenue with a line chart, a ranked rep leaderboard with the top two highlighted, and a PivotTable by region. Every step is checked automatically, with hints when you get stuck.
Type Revenue in H1 and fill H2:H151 with Units × Unit price.
15 points
Name H2:H151 Revenue with the Name Box, then put the total in K2 with =SUM(Revenue).
15 points
K3: number of orders (count the Order column). K4: average order value, using K2 and K3.
10 points
N2:N7: revenue for each month. The month start dates are in M2:M7. Use SUMIFS on the Date column with ">="&M2 and "<"&EDATE(M2,1), and the Revenue name as the sum range.
20 points
Q2:Q7: revenue for each rep in P2:P7 (SUMIF with the Revenue name). R2:R7: each rep’s rank, highest = 1.
20 points
K5: the name of the rep with the highest revenue. Use INDEX with XMATCH (or XLOOKUP) on the leaderboard.
10 points
Highlight the top 2 revenues in Q2:Q7.
10 points
Insert a PivotTable of the Sum of Revenue by Region from A1:H151, placed at J10.
15 points
Insert a line chart of M1:N7 titled Monthly revenue.
10 points
New to any of these? Each one is a free or Premium lesson you can do first.
FluentExcel project · Real estate
Real Estate Analysis
10 steps · 60 rows of data · 66 formulas
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Listing | Neighborhood | Type | Beds | SqFt | Price | Days on market | Price/SqFt | Summary | |
| 2 | RE-1029 | Downtown | House | 4 | 2540 | $1,235,000 | 108 | 486.2204724 | Total value | |
| 3 | RE-1001 | Downtown | Condo | 5 | 2900 | $1,185,000 | 9 | 408.6206897 | Average price | |
| 4 | RE-1054 | Downtown | Townhouse | 4 | 2740 | $1,137,000 | 90 | 414.9635037 | Condos listed | |
| 5 | RE-1019 | Downtown | Condo | 5 | 2740 | $1,135,000 | 140 | 414.2335766 | Avg price Riverside | |
| 6 | RE-1002 | Downtown | House | 4 | 2380 | $1,131,000 | 143 | 475.210084 | Highest price | |
| 7 | RE-1049 | Hillcrest | House | 5 | 3000 | $1,130,000 | 133 | 376.6666667 | Neighborhood of highest | |
| 8 | RE-1025 | Lakeside | Townhouse | 5 | 2710 | $1,111,000 | 24 | 409.9630996 | ||
| 9 | RE-1022 | Hillcrest | Condo | 5 | 3130 | $1,080,000 | 123 | 345.0479233 | ||
| 10 | RE-1007 | Downtown | Condo | 5 | 2850 | $1,072,000 | 148 | 376.1403509 | ||
| 11 | RE-1009 | Downtown | Townhouse | 5 | 2580 | $1,030,000 | 7 | 399.2248062 |
Real estate
Real Estate Analysis
10 steps · 3 h
FluentExcel project · Operations
Inventory Management
9 steps · 80 rows of data · 327 formulas
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SKU | Item | Category | On hand | Reorder point | Unit cost | Supplier | Item (clean) | Stock value | Status |
| 2 | INV-1001 | USB-C cable Lite | Cables | 25 | 19 | $32.72 | S05 | USB-C cable Lite | 818 | OK |
| 3 | INV-1002 | USB stick Lite | Storage | 119 | 16 | $52.37 | S03 | USB stick Lite | 6232.03 | OK |
| 4 | INV-1003 | Earbuds Max | Audio | 25 | 30 | $52.76 | S02 | Earbuds Max | 1319 | Reorder |
| 5 | INV-1004 | Ethernet cable Mini | Cables | 61 | 32 | $58.76 | S04 | Ethernet cable Mini | 3584.36 | OK |
| 6 | INV-1005 | Hard drive Lite | Storage | 26 | 35 | $20.65 | S03 | Hard drive Lite | 536.9 | Reorder |
| 7 | INV-1006 | Hard drive Plus | Storage | 109 | 30 | $14.22 | S02 | Hard drive Plus | 1549.98 | OK |
| 8 | INV-1007 | USB stick Lite | Storage | 0 | 37 | $36.93 | S03 | USB stick Lite | 0 | Reorder |
| 9 | INV-1008 | Headphones Pro | Audio | 24 | 11 | $40.87 | S01 | Headphones Pro | 980.88 | OK |
| 10 | INV-1009 | Camera bag Lite | Cases | 88 | 15 | $35.09 | S02 | Camera bag Lite | 3087.92 | OK |
| 11 | INV-1010 | Ethernet cable Lite | Cables | 2 | 36 | $11.98 | S04 | Ethernet cable Lite | 23.96 | Reorder |
Stock value
Operations
Inventory Management
9 steps · 2.5 h
FluentExcel project · Human resources
HR and Payroll
8 steps · 60 rows of data · 423 formulas
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Emp ID | Name | Department | Start date | Annual salary | Overtime hrs | Rating | Years | Monthly base | Overtime pay |
| 2 | E1001 | Ana Nair | Sales | 12/23/2018 | $82,000 | 4 | 1 | 7 | 6833.333333 | 236.5384615 |
| 3 | E1002 | Ben Novak | Engineering | 8/20/2022 | $100,010 | 20 | 4 | 3 | 8334.166667 | 1442.451923 |
| 4 | E1003 | Cara Haddad | Operations | 4/1/2016 | $61,020 | 20 | 3 | 10 | 5085 | 880.0961538 |
| 5 | E1004 | Dev Haddad | Sales | 6/27/2017 | $50,030 | 8 | 4 | 9 | 4169.166667 | 288.6346154 |
| 6 | E1005 | Eli Lopez | Engineering | 10/21/2022 | $118,040 | 0 | 5 | 3 | 9836.666667 | 0 |
| 7 | E1006 | Fay Kim | Engineering | 4/22/2024 | $87,050 | 4 | 2 | 2 | 7254.166667 | 251.1057692 |
| 8 | E1007 | Gus Okafor | Operations | 7/22/2020 | $64,060 | 0 | 5 | 5 | 5338.333333 | 0 |
| 9 | E1008 | Hana Kim | Support | 12/25/2016 | $47,070 | 0 | 5 | 9 | 3922.5 | 0 |
| 10 | E1009 | Ivan Okafor | Operations | 6/9/2019 | $48,080 | 0 | 4 | 7 | 4006.666667 | 0 |
| 11 | E1010 | Jade Smith | Support | 10/9/2023 | $40,090 | 0 | 4 | 2 | 3340.833333 | 0 |
Human resources
HR and Payroll
8 steps · 3 h