Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.3 Sales: Target vs Actual by Sales Rep

By Last updated:

Example first. Ruhi Bagale leads a fictional field sales team of six reps and wants the monthly target review on one page.

Sales project — in 30 seconds

What: A Sales tracker: one Table tblReps, the calculated column Achievement, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Sales report: Total Actual, Overall Achievement, Reps at Target are the numbers a manager reads first.

Tiny example: From 6 rows: Total Actual = ₹22,64,000, Overall Achievement = 98.4%, Reps at Target = 3.

First look at the data (before → after):

Sales project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

Rep Achievement
Shahrukh 109.0%
Amir 89.3%
Salman 103.0%
Zoya 85.1%
Ravina 110.3%
Raja 92.0%

Expected result — KPI cells (expected results)

KPI Formula Result
Total Actual =SUM(tblReps[Actual]) ₹22,64,000
Overall Achievement =SUM(tblReps[Actual])/SUM(tblReps[Target]) 98.4%
Reps at Target =COUNTIF(tblReps[Achievement],">=1") 3
Before table and formula as text

Before — Table tblReps (fictional sample rows)

Rep City Target Actual
Shahrukh Pune 5,00,000 5,45,000
Amir Pune 4,50,000 4,02,000
Salman Nashik 4,00,000 4,12,000
Zoya Nagpur 3,50,000 2,98,000
Ravina Solapur 3,00,000 3,31,000
Raja Kolhapur 3,00,000 2,76,000

Formula / click path

Achievement: =[@Actual]/[@Target]
Total Actual: =SUM(tblReps[Actual])
Overall Achievement: =SUM(tblReps[Actual])/SUM(tblReps[Target])
Reps at Target: =COUNTIF(tblReps[Achievement],">=1")

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblReps in Table Design › Properties › Table Name.
  2. Add the column Achievement with =[@Actual]/[@Target]; the Table fills it down.
  3. On a KPI sheet type =SUM(tblReps[Actual]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and Actual in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Rep.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a target-versus-actual sheet: achievement % per rep, total actual, overall achievement (SUM ÷ SUM, never an average of percentages), how many reps hit target, sales by city, a chart and a Rep slicer.

Calculated column formulas:

Column Formula
Achievement =[@Actual]/[@Target]

KPI cells (check your numbers):

KPI Formula Result
Total Actual =SUM(tblReps[Actual]) ₹22,64,000
Overall Achievement =SUM(tblReps[Actual])/SUM(tblReps[Target]) 98.4%
Reps at Target =COUNTIF(tblReps[Achievement],">=1") 3

PivotTable (expected):

City Sum of Actual
Kolhapur ₹2,76,000
Nagpur ₹2,98,000
Nashik ₹4,12,000
Pune ₹9,47,000
Solapur ₹3,31,000
Grand Total ₹22,64,000

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Rep and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Sales_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of Actual?
  2. Filter the Table on City = Pune (Data › Filter): what is Total Actual for Pune alone?
  3. Add the Clustered Column PivotChart and the Rep slicer to your Dashboard sheet.
Answer

Pune (₹9,47,000). For Pune only (2 rows): Total Actual = ₹9,47,000. The chart shows 5 bars, one per city.

Ravindra Bagale's Tip

Keep the calculated column Achievement inside the Table. New rows then get the formula automatically, and the PivotTable only needs Refresh.

Loading practice tasks...

  1. Total deals won (Easy): Add up Deals won in D2:D11.
  2. Average avg deal value (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in North (Easy): How many rows have Region "North"?
  4. Largest deals won (Easy): The largest value in D2:D11.
  5. Deals won for North (Medium): Total of D2:D11 where B is "North".
  6. North in Bristol (Medium): Total of D2:D11 where B is "North" and C is "Bristol".
  7. Best in North (Medium): Largest E2:E11 value where B is "North".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Ishaan Verma (Medium): The Deals won for "Ishaan Verma" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of North (Hard): Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →