Rather than saying that this is a new piece, I prefer to say it’s a comprehensive chapter that combines several pieces of articles I released before. You can better understand the elements needed and the tips to create a NPV calculator made by Python.
In this Python Tutorial, I’ll walk you through how to use Flask, Python and the approach OOP to build a NPV calculator. You can definitely learn the codings, but also explore the knowledge of investment evaluation, and what eCommerce P&L elements matter.
Python libraries and Modules: Flask, requests, Python script, CSS script and HTML script
Knowledge Modules: investment evaluation (WACC, CAGR, NPV, IRR), 7 variable costs in eCommerce, eCommerce & digital marketing
- 4 Core Sections for an eCommerce Business Investment NPV Calculator
- Create an OOP – 15 Variable Number To Input for NPV Calculation</a
- 5 Calculation Formulas</a
- Use if/elif/else to tell users if the investment is worthy or not</a
- Other factors you should be aware that can affect the NPV result</a
- Full Python Script of eCommerce Business Investment NPV Calculator</a
NPV Calculator: 4 Core Sections for an eCommerce Business Investment Evaluation
The goal of a business is to earn profit. In the whole journey of a business, basically you will approach a segment of customers in one place by marketing to sell your products or services. At the end after a while of years, you aim to get back the investment and earn money.
NPV or net present value basically tells us if this business is worth investing in. So the eCommerce GMV, operation cost and capitalism cost matter. But please don’t think it might be complex. Basically these are the 3 core sections you need to handle and calculate the numbers.
- Market growth rate, Capitalism cost & investment cost
- Ecommerce product price and operation cost
- Gross profit from organic channels and partnerships
- Gross profit from paid media channels and partnership
And here we try to break down what specific metrics are in each section
Market growth rate, capitalism cost & investment cost
- WACC: Weighted Average cost of capital
- CAGR: Compound average growth rate
- Initial Outlay: Upfront investment before start
These 3 factors do vary from different industries, products and markets. In this piece, I’ll take smart home electronic products for example. Also, the initial outlay is just the investment you need to pay in advance before the project starts. This number size can affect the NPV at the end or force you to extend the investment windows for earning profit.
If you like to learn more details, please check out this article – NPV and IRR in eCommerce business evaluation
Ecommerce product price and operation cost
- SKU price or average order value
- COGS: cost of good sales
- Fulfillment cost
- Refund rate
- Payment processing cost
To define the cost of good sales, it depends on your financial model. In this article, I will put it as the landed cost, which includes the product production or sourcing cost, internationship shipping cost, and the import custom cost. Some parties would add the cost per sale as well. But I would put this into the paid media section.
Fore more details, please check out this article – 7 varible cost in eCommerce space
Gross profit from organic channels and partnerships
- Organic traffic per year
- Organic avg conversion rate
The definition of organic traffic very depends on the business status and the anchor you like to use. For example, if it’s a new business without any historical data, you could refer to your competitor’s web traffic or search engine keyword search volume. If the business has been running, you can refer to the web traffic from the last year. This approach can be applied to the conversion rate as well.
I’ll release another article regarding a bot for scraping converate rate benchmark data by sectors.
Gross profit from paid media channels and partnership
- Cost per click from the paid media channel
- Avg conversion rate
- Target to gain paid traffic per year
- Partnership commission cost
- Order% from partnerships out of total orders
Create an OOP – 15 Variable Number To Input for NPV Calculation
Here is the code sample for your reference. It’s a class and self object creation. Then, assigning the 15 variable number separately to the self object.
EBTDA level involves the fiat cost like manpower, equipment rent etc, I will suggest you to deduct from the present value if it’s necessary.
But It’s supposed to have the 16th variable number that can affect the final result of NPV, which is how many years to invest. I will add it in the flask script instead of putting it in the class. It’s because you don’t need to create a specific method for the number of years. It can be just a variable later you set in the flask script.
Regarding more details about Python OOP, please check out this article and you can learn how to connect your flask function script with the front end scripts such as html. It’s for the purpose to getting the data from user inputs – Python OOP
5 Core Calculation Formulas
The calculation formulas are vital because you like to generate a realistic and convincing result to guide you on the investment. It’s difficult to be accurate, but it’s totally feasible to generate a number to be more convincing and make sense.
eCommerce operation GMV and cost
Basically total GMV and cost consist of the total amount of organic and paid. So in the class oop, you need to create the methods upfront before writing down the codes of the flask script.
First thing first is the GMV formulate. It is very straightforward, basically the result is from the multiply among traffic, conversion rate and the AOV number respectively from organic and the paid media.
return self.organicTraffic * self.organicCVR * self.aov
return self.paidTraffic * self.paidCVR * self.aov
Second, it is the cost formula. It might vary case by case as it depends on what the fee structure of your refund, or which payment gateway you would use. And in the paid media, it also deals with the 3rd party commission cost as well. Or if you run an Amazon store, you also have referral fees in both organic and paid media.
return self.organicTraffic * self.organicCVR * (self.cogs + self.fulfillmentCost + self.refundRate * (self.fulfillmentCost + self.cogs) + self.aov * self.paymentRate)
return self.paidTraffic * self.paidCVR * (self.cogs + self.fulfillmentCost + self.aov * self.partnerCommissionRate * self.partnerOrderPercentage + self.refundRate * (self.fulfillmentCost + self.cogs) + self.aov * self.paymentRate) + self.paidCPC * self.paidTraffic
Present Value or PV
The PV formula is
EBTDA / (1 + WACC), but I will suggest how to handle and deduct those flat and tax costs at the NPV level. It’s because it varies by market, sectors, so it creates more difficulties for the users to find the number by using your calculator.
So below is the formula I use gross profit instead:
PV = gross profit / (1 + WACC)
For the WACC calculation, I would suggest you provide the list to the end users for reference. And I’ll release another article to elaborate. If you are interested in how to scrape the data, please check out this article – FMP bot for scraping the financials
Total amount of PV
Just now, we get the 1st year of PV based on the business historical and industrial data. And according to the length of year you are about to invest, you need a CAGR number to calculate the PV from each year and work out a total amount.
Lum sum of PV = PV1st + PV2nd + ………+ PVnyear * (1 + CAGR) ^ years
For more details regarding the CAGR calculation, please check out this article – NPV and IRR
Last but not least, the total amount of PV minus upfront investment is the NPV. For more accuracy, you could also minus the flat cost like 5 years’ manpower cost, equipment cost etc
NPV = Total amount of PV - Initial Outlay
Use if/elif/else to tell users if the investment is worthy or not
The value of the calculator is giving hints and a brief direction to the users, if the investment is worthy. The NPV result indeed varies based on the investment length. And the result must tell the user if the investment is earning money or losing money at the end. So here deals with the conditions. Below are the codings respectively for the total amount of PV and NPV.
Other factors you should be aware that can affect the NPV result
This approach has also considered the financial perspective of discounted cash flow. After all the money would depreciate after some years than the year you invested in the 1st place.
However, I assume if your SKU price or AOV might not change, it can be more accurate to calculate as there are some costs that might increase as well. Below is the list of variable costs you can further break down to calculate by year. There, you can mark up an incremental percentage.
- Payment processing fees
- Fulfillment cost
- Refund cost
Full Python Script of eCommerce Business Investment NPV Calculator
If you are interested in the full script of eCommerce NPV calculator, please subscribe to our newsletter by adding the message “Python Tutorial 36”. We would send you the script immediately to your mailbox.
I hope you enjoy reading Python Tutorial 36: Create a NPV Calculator in eCommerce Business Space. If you did, please support us by doing one of the things listed below, because it always helps out our channel.
- Support my channel through PayPal (paypal.me/Easy2digital)
- Subscribe to my channel and turn on the notification bell Easy2Digital Youtube channel.
- Follow and like my page Easy2Digital Facebook page
- Share the article to your social network with the hashtag #easy2digital
- Buy products with Easy2Digital 10% OFF Discount code (Easy2DigitalNewBuyers2021)
- You sign up for our weekly newsletter to receive Easy2Digital latest articles, videos, and discount code on Buyfromlo products and digital software
- Subscribe to our monthly membership through Patreon to enjoy exclusive benefits (www.patreon.com/louisludigital)