Loading…
Loading…
What you will build: Tenure, base, overtime, gross, tax rate, net pay and bonus columns, a payroll summary, a headcount PivotTable and the top six salaries highlighted.
Human resources
You run payroll for a 60-person company. For the June run you need each person’s tenure, monthly pay including overtime (paid at 1.5× the hourly rate, where hourly = salary ÷ 2080), tax from a bracket table, net pay and the annual bonus from their rating. Every rate lives in a cell so HR can change it without touching formulas.
Premium includes all 12 projects, every advanced lesson and certificates. Already Premium? Sign in to open it.
Tenure, base, overtime, gross, tax rate, net pay and bonus columns, a payroll summary, a headcount PivotTable and the top six salaries highlighted. Every step is checked automatically, with hints when you get stuck.
Type Years in H1 and fill H2:H61 with completed years from Start date to the report date in Q1.
10 points
I1 Monthly base, I2:I61: salary ÷ 12. J1 Overtime pay, J2:J61: overtime hours × salary ÷ 2080 × the multiplier in Q2.
15 points
Type Gross in K1 and fill K2:K61 with base + overtime.
10 points
L1 Tax rate, L2:L61: look up each gross in the bracket table P5:Q8 (approximate match). M1 Net pay, M2:M61: gross × (1 − tax rate).
20 points
Type Bonus in N1 and fill N2:N61 with salary × the bonus % for each rating (table S5:T9).
10 points
Q12: total monthly gross. Q13: average annual salary in Sales. Q14: how many people have 5 or more years of service.
15 points
Insert a PivotTable from A1:N61 at P17 that counts Emp ID by Department.
10 points
Highlight the top 6 salaries in E2:E61.
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 · Sales
Sales Dashboard
9 steps · 150 rows of data · 172 formulas
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Rep | Region | Product | Units | Unit price | Revenue | Summary | |
| 2 | SO-2001 | 1/2/2026 | Ben | North | Phone | 5 | $599 | 2995 | Total revenue | |
| 3 | SO-2002 | 1/2/2026 | Ana | North | Tablet | 3 | $399 | 1197 | Orders | |
| 4 | SO-2003 | 1/3/2026 | Cara | South | Phone | 5 | $599 | 2995 | Average order | |
| 5 | SO-2004 | 1/4/2026 | Dev | South | Tablet | 2 | $399 | 798 | Top rep | |
| 6 | SO-2005 | 1/4/2026 | Ben | North | Laptop | 3 | $899 | 2697 | ||
| 7 | SO-2006 | 1/8/2026 | Cara | South | Laptop | 1 | $899 | 899 | ||
| 8 | SO-2007 | 1/9/2026 | Cara | South | Monitor | 4 | $229 | 916 | ||
| 9 | SO-2008 | 1/10/2026 | Cara | South | Phone | 1 | $599 | 599 | ||
| 10 | SO-2009 | 1/11/2026 | Eli | East | Headset | 5 | $79 | 395 | ||
| 11 | SO-2010 | 1/13/2026 | Eli | East | Tablet | 2 | $399 | 798 |
Monthly revenue
Sales
Sales Dashboard
9 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