E-Commerce Management: P&L + KPIs Dashboard & Scenarios Spreadsheet

Leggi questo articolo in italiano | Leia este artigo em Português

Recently, while I was reviewing and organizing some personal files, I came across this e-commerce management spreadsheet - that is also useful for simulating alternative scenarios - which I created back there at the beginning of 2013 while I was still working as a Commercial Manager for VTEX in Brazil.

At the time, I decided to create this document basically to work as a Dashboard that I would present to some retailers, especially to those "new entrants" in the online sales world, mainly seeking to guide them, helping them to know a little bit about the numbers involved in an e-commerce operation, as well as some of the market's basic KPIs. A few years later, this "tool" was again useful to me, helping me to track some metrics while I was working as an e-commerce manager for an operation here in Italy.

In these last few days, I decided to give an overall upgrade in the document, adding two new languages, English and Italian, in addition to the original Brazilian Portuguese version, so I could share it with my network here on LinkedIn as well as with some other e-commerce professionals out there.

And to give a hand, especially for those a little less familiar with these metrics and KPIs, I'm including below some screenshots of the Dashboard with a few remarks and explanations about the formulas I used. The complete document is available on my Google Drive for copying or downloading:

https://vpribeiro.com/redir/ecommerce-management-scenarios-simulation-and-kpis-dashboard-worksheet.html

Happy planning folks ;-)

Fields highlighted in yellow are the data entry fields.

I recommend starting with the CPV calculator (previously misnamed CPC calculator) to estimate the Average Order Value (AOV), the Conversion Rate and the Average CPV of your e-commerce operation. Get the information about the number of Unique Visitors, Sales, and Monthly Orders from your Web Analytics tool and/or from your e-commerce platform sales reports.

If you are starting a new project, you can do the reverse calculation, for instance, asking yourself how many orders do you have to sell in a given month to have a Total Sales of $X with an estimated Conversion Rate of Y% and an AOV of $Z.

CPV Calculator: Understanding the Formulas

Average Order Value (AOV)

• AOV = Total Sales / Number of Orders
• AOV = $ 100,000.00 / 400
• AOV = $ 250.00

Conversion Rate (CONV RATE)

• CONV RATE = Approved Orders / Unique Visitors
• CONV RATE = 400 / 40,000
• CONV RATE = 0.01 = 1.00%

Cost Per Visitor (CPV)

• CPV = Investment in Marketing / Unique Visitors
• CPV = $ 10,000 / 40,000
• CPV = $ 0.25

Margin Calculation: Understanding the Formulas

Selling Price [AOV]

• SP = COGS x (1 + Markup / 100)
• SP = $ 83.33 x (1 + 200/100)
• SP = $ 83.33 x 3
• SP = $ 250.00 (math rounding)

Cost of Goods Sold [COGS]

• COGS = SP / (1 + Markup / 100)
• COGS = $ 250.00 / (1 + 200/100)
• COGS = $ 250.00 / 3
• COGS = $ 83.33

Gross Margin [MRGN]

• MRGN = SP - COGS
• MRGN = $ 250.00 - $ 83.33
• MRGN = $ 166.67

Markup [MUP]

• MUP = [(SP / COGS) - 1] x 100
• MUP = [($ 250.00 / $ 83.33) - 1] x 100
• MUP = [3-1] x 100
• MUP = 200 (%)

Markdown [MDN]

• MDN = (MRGN / SP) × 100
• MDN = ($ 166.67 / $ 250.00) x 100
• MDN = 66.67 (%)

ROI Calculation: Understanding the Formulas

Cost per Visitor [CPV]

Average Cost per Visitor calculated over Digital Marketing campaigns. As we saw earlier in this article, the calculated average CPV is $ 0.25.

Cost per Acquisition [CPA]

• CPA = CPC / CONV RATE
• CPA = $ 0.25 / 1.00%
• CPA = $ 25.00

Conversion Rate [CONV RATE]

• CONV RATE = Approved Orders / Unique Visitors
• CONV RATE = 1.40%

Return on Investment [ROI]

• ROI = SP / CPA
• ROI = $ 250.00 / $ 25.00
• ROI = 10

Operational Costs Calculation

Note: The light gray values to the left of the yellow squares reserved for data entry represent the minimum and maximum limits of the market's averages. This is not to say that there are no online stores that operate outside these pre-established average limits.

Technology [Platform, Integrations etc.]

Average Monthly Maintenance Cost of the Commerce Platform, Integrations and other Solutions used in the operation.

Operation [Fulfillment + Delivery]

• Fulfillment: Storage, Picking, Packing, Handling, Supplies, etc.
• Delivery: Shipping Direct Costs + Subsidies (Total or Partial), Returns, and Taxes

Financial [Financial Costs + Taxes + Fraud + Anti-Fraud Analysis]

• Financial Cost: Cards Fees, Pre-Capture of Installments, Bank Slip Fee, Gateway Costs, etc.
• Taxes: VAT, etc.
• Fraud: Chargeback Card, etc.
• Anti-Fraud Analysis: Average Costs of Anti-Fraud Analysis & Tools

Investment in Marketing (MKT)

• Campaigns SEM, SSM, DEM, Programmatic, etc.

Understanding the Formulas

• MKT = (1 / ROI) x 100
• MKT = (1/10) x 100
• MKT = 10%

or

• MKT = (CPA / AOV) × 100
• MKT = ($ 25.00 / $ 250.00) x 100
• MKT = 10%

Contribution Margin

• CM = (SP - COGS - Operational Costs - MKT)

Break-Even Calculation | Unique Visitors

General Expenses: Rent, Water, Light, Telephone and Internet bills, HR, Office Supplies, ERP, etc.

Break-even: The minimum monthly sales amount required to pay for General Expenses.

Understanding the Formulas

Break-even

• Break-even = Qty of Monthly Orders (for Break-even) x AOV
• Break-even = 298 x $ 250.00
• Break-even = $ 74,500.00

Note: small difference due to rounding of decimals

Monthly Orders (for Break-even)

• Monthly Orders = General Expenses / Contribution Margin
• Monthly Orders = $ 15,000.00 / 50.42
• Monthly Orders = 297.5

Note: small difference due to rounding of decimals

Number of Unique Visitors (for Break-even)

• Unique Visitors = Monthly Orders / Conversion Rate
• Unique Visitors = 298 / 1.00%
• Unique Visitors = 29,752

Note: small difference due to rounding of decimals