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

27. 20 Industry Projects

27.2 E-commerce: Gross Sales, Returns and Net Sales

By Last updated:

Example first. Shraddha Bagale sells online through a fictional Amazon Now storefront and wants to see how much of the gross sales is lost to returns.

E-commerce project — in 30 seconds

What: A E-commerce tracker: one Table tblOrders, the calculated column Amount, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real E-commerce report: Gross Sales, Return %, Net Sales are the numbers a manager reads first.

Tiny example: From 6 rows: Gross Sales = ₹8,140, Return % = 33.3%, Net Sales = ₹4,543.

First look at the data (before → after):

E-commerce project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

OrderID Amount
AMN-6001 ₹1,499
AMN-6002 ₹1,398
AMN-6003 ₹1,047
AMN-6004 ₹899
AMN-6005 ₹2,199
AMN-6006 ₹1,098

Expected result — KPI cells (expected results)

KPI Formula Result
Gross Sales =SUM(tblOrders[Amount]) ₹8,140
Return % =COUNTIF(tblOrders[Status],"Returned")/ROWS(tblOrders[Status]) 33.3%
Net Sales =SUMIFS(tblOrders[Amount],tblOrders[Status],"Delivered") ₹4,543
Before table and formula as text

Before — Table tblOrders (fictional sample rows)

OrderID City Category Qty Price Status
AMN-6001 Pune Electronics 1 1,499 Delivered
AMN-6002 Kolhapur Fashion 2 699 Returned
AMN-6003 Pune Home 3 349 Delivered
AMN-6004 Sambhaji Nagar Fashion 1 899 Delivered
AMN-6005 Nashik Electronics 1 2,199 Returned
AMN-6006 Kolhapur Home 2 549 Delivered

Formula / click path

Amount: =[@Qty]*[@Price]
Gross Sales: =SUM(tblOrders[Amount])
Return %: =COUNTIF(tblOrders[Status],"Returned")/ROWS(tblOrders[Status])
Net Sales: =SUMIFS(tblOrders[Amount],tblOrders[Status],"Delivered")

Do it step by step:

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

The brief. Build an e-commerce sales sheet: Amount per order, gross sales, return % (returned orders ÷ all orders), net sales (delivered only), sales by city, a chart and a Category slicer.

Calculated column formulas:

Column Formula
Amount =[@Qty]*[@Price]

KPI cells (check your numbers):

KPI Formula Result
Gross Sales =SUM(tblOrders[Amount]) ₹8,140
Return % =COUNTIF(tblOrders[Status],"Returned")/ROWS(tblOrders[Status]) 33.3%
Net Sales =SUMIFS(tblOrders[Amount],tblOrders[Status],"Delivered") ₹4,543

PivotTable (expected):

City Sum of Amount
Kolhapur ₹2,496
Nashik ₹2,199
Pune ₹2,546
Sambhaji Nagar ₹899
Grand Total ₹8,140

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Bar › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Category 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 Ecommerce_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 Amount?
  2. Filter the Table on City = Pune (Data › Filter): what is Gross Sales for Pune alone?
  3. Add the Clustered Bar PivotChart and the Category slicer to your Dashboard sheet.
Answer

Pune (₹2,546). For Pune only (2 rows): Gross Sales = ₹2,546. The chart shows 4 bars, one per city.

Ravindra Bagale's Tip

Show Return % next to the rupee total. A percentage or ratio without the size behind it can make a small city look like a big problem.

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 Manchester (Medium): Total of D2:D11 where B is "North" and C is "Manchester".
  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 Olivia Brown (Medium): The Deals won for "Olivia Brown" (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 →