Acerca de
Online Database for an eCommerce Startup
Overview
From keeping track of a few online sneaker sales on an Excel sheet, my business partner quickly realized a business opportunity that would lead to HypeOutfit today. HypeOutfit, as the name states, sells clothing and shoes that are “hype,” in other words, we target audiences in Taiwan who are looking for high-end fashion streetwear that could only be purchased overseas, and find ways to bring it to customers.
While my business partner runs most of the daily operations in Taiwan, I have built a database on Google Sheets to track our sales, purchases, and inventory; it also allows us to analyze our financial performances. Last year, despite COVID-19, HypeOutfit successfully reached 1.2 million New Taiwan dollars in revenue, with a 61% gross profit margin. This has been an ongoing project for me and there is a lot of room for improvement, including a better system for tracking information, omitting unnecessary columns that my business partner does not see fit, and building a CRM system.
Sales and Buyers
When we receive an order online, order information gets input into this sheet--this includes order date, sales price, product name, size, color, and payment information.
Some key points to highlight here:
​
- ProductID acts as a Primary Key, it is what we use as our SKUs.
- Order details such as item name, size, color, and brand are all kept here to confirm the orders.
- Customer information such as BuyerName and SocialMedia is tracked for CRM and marketing purposes.
​
​
On this same sheet, I added a GrossProfit column as a KPI for every sale we make.
The formula for GrossProfit is simply ( SalePrice - Cost ).
To save time and minimize error, all costs in the Cost column are programmed with a sumifs( ) function to reflect directly from the cost in the Purchase Record table (which we will see next).
Here's an example:
=sumifs(PurchaseRecord!$P:$P,PurchaseRecord!$A:$A, A2)
This is where the ProductID (our primary keys) comes in handy!
If the ProductID in this current table (Sales and Buyers) matched the ProductID in the next table (Purchase Record), then the cost here on the Sales and Buyers will be whatever the cost is on the Purchase Record.
​
​
On the GrossProfit column, I also utilized the conditional format rules on Google Sheet to use the colors blue and red and KPIs;
if the color shows blue, that shows that we gained profit; if the color shows red, that means we lost money on that sale order.
Purchase Record
After receiving an order, we go ahead and source items from the suppliers (stores overseas or other regional suppliers). Information will be inputted into this sheet, including the brand, name, size, color, cost, date, etc.
Some key points to highlight here:
​
- ProductID acts as a Foreign Key, reflecting the PK from the previous table, which also helped us in programming the item cost automatically into the Sales and Buyers table.
- Product Brand, SupplierName, ProductSize, and ProductColor are all built using the data validation function on the spreadsheet--this allows a drop-down selection for each row. By doing so, it eliminates manual errors that would affect analysis purposes later on.
Inventory System Improvements
Our current inventory system is still in the developing phase.
The first table reflects the old system that we used. Again, I used a condition formatting in colors to indicate our stock levels.
In the new system, we incorporated Categories to further analyze our inventory. This was deemed needed because we needed to understand the rate of product turnovers--this could be useful in understanding what our clients favor more and what types of products we do not want to over-purchase and take up inventory.
Inventory Dashboard on Power BI
As you can see, multiple filters are installed on this dashboard. This allows users to quickly view the existing inventory based on product brands and categories.
