Startup Clothing Business Excel Database

Credits: Image from Martin Tolovski in LinkedIn

As my wife was beginning to embark on a journey of creating her new business, I was busy trying to figure out the easiest, simplest and most efficient way to create a mini-database for the up and coming Saba clothing business.

Yes… Excel is not the most efficient tool I admit.
But for a beginner in Data Analysis, and a business that doesn’t require much data to begin with, I decided to settle on Excel and VBA in my toolkit after much research.

Structure

There are 7 sheets in this database:

Products add new items to sell.
Stock add new sizes to your existing items. Used to check what items you have in stock.
New Order! add new orders.
Orders a table that shows all the orders placed.
Client Data a table that shows all the clients’ data that ordered from you before.
Data Sets a few tables to help with data validation in the Orders table.
Analysis a few examples for analyzing your data.

Products

In this sheet, you will be able to checkout the items you produce, or produced before, as well as add more items to your store.

You enter the information of the new product then click on the Add Product. A VBA code is used to add a new row with the items information.

Note: You can click on Hide Help/Show Help to hide/show the yellow help section.

Stocks

In this sheet, you will check and/or add how many sizes you have for each item in stock.

You enter the information of an existing product then click on the Add Stock button. A VBA code is used to add/remove from the sizes for the selected item in store.

Also, whenever you add a new order to the orders table (more on that later), it will automatically deduct the number in the corresponding size of the item.

Note: You can click on Hide Help/Show Help to hide/show the yellow help section.

New Orders!

This is where the magic happens! In this sheet, you will add new orders.

A VBA code is used to check every data entry. Its purpose is to create checkpoints to keep you from entering the wrong data.

Phone Number: Each dataset needs a unique identifier, for the clients in this database, it’s their phone number. You can use this to check if a client exists, and if they don’t, you can add a new one.

Add New Client: In this section, you can add information for a new client.

Select Product: In this section, you will select the item you want to add to the new order. You can use the grey boxes to filter out your existing items by id, name, collection, or year. Simply fill out the required data and click on the corresponding box.
Any filtered data will appear in the Products Browse section.

Product Specs: In this section, you will add more information to the selected item that will be placed in the order. You can add a sale to a specific order if you want by setting the Sale to Yes.

• Sales Added This will deduct the amount entered in this cell from the final cost. If Sale is set to No, any data entered here is ignored.
  Cost = 500, Sales Added = 100 -> Final Cost = 400.
• Sales Perc This will deduct the percentage entered in this cell from the final cost. If Sale is set to No, any data entered here is ignored.
  Cost = 500, Sales Perc = 50% -> Final Cost = 250.

Note: Please check the Orders Table section for more information

Note: You can click on Hide Help/Show Help to hide/show the yellow help section.

Orders Table

This is where all the orders are stored. This is a regular Excel table, where you can still manually add new rows with information there (not recommended but it can be faster when editting).

Note: There’s no automated way of removing a certain record from the table, you need to do it manually. This is better for this scale as VBA code can’t be undone, which is not good if you accidentally deleted a record you didn’t want to.

These are the columns in the table:

• Date Order date.
• order_id A unique identifer for each order. This is automatically calculated. This is the Primary Key for this Table!
• Name Client name.
• client_id A unique identifer for each client. This is automatically referenced from the Client Data table.
  Note: You can check any other client data from the Client Data table.
• Completion Order status (if it is ordered, or being prepared, or delivered, etc…).
  Note: This is a Data-validated column, where you can pick up the status from a drop-down menu. You can change the menu choices from the Data Sets sheet, and then make sure they are read by the Data Validation menu from the Data tab.
• Transaction This is a binary column that is multiplied to the final cost. It’s used to zero out the final cost of orders that where rejected or changed instead of deleting the row.
  Note: This is an automated column that is calculated from the Completion status column. You can change how it’s calculated by going to the Data Sets sheet and changing what each status equates to.
• Order Name of the product that was ordered.
• product_id A unique identifer for each product. This is automatically calculated from the Products table.
• Type Type of order. This was used to distinguish the real orders from the prototype (sample) ones.
  Note: This is a Data-validated column, where you can pick up the status from a drop-down menu. You can change the menu choices from the Data Sets sheet, and then make sure they are read by the Data Validation menu from the Data tab.
• Size Order size.
  Note: This is also a Data-validated column, but it’s not fit to be modified yet as other sheets specifically rely on these sizes.
• Cost Initial cost of the product. This is automatically calculated from the Products table. This is calculated in EGP by default.
• Sales Add The amount to be deducted if a sale is applied. This is calculated in EGP by default.
• Sales Perc The percentage to be deducted if a sale is applied.
• Final Cost The Final cost after sale. This is automatically set to 0 if a status in the Completion column corresponds to a 0 in the Transaction column. This is calculated in EGP by default.
  For Example: All records with the Completion column set to Rejected are reduced to 0.

Client Data

This sheet shows all your clients data. This is separated from the Orders table as to not have redundant data in one table following Data Normalization rules.

Note: All data in this table is fake. No personal information is shared here!

• id A unique identifer for each client. This is a first Primary Key for this Table!
• Name Client Name.
• Phone No. Client Phone number. This is what is used to identify the client in the New Order! table. This is a second Primary Key for this Table!
• Country Client’s country of residence.
• City Client’s city of residence.

Data Sets

This sheet controls the drop-down menu choices of the Data Validated cells in the New Order! sheet.

First Table: Controls which Completion Status corresponds to 0 or 1 for caluclating the Final Cost of an order.

Second Table: A Yes-No for whether to add a Sale or not.

Third Table: Types of the Product.

Fourth Table: Product Sizes.
Note: This is not a customizable column at the moment as it might mess with other calculation in other sheets.

Analysis

This final sheet has some examples for PivotTables and charts created from the data in the 2 tables.

Note: This section doesn’t reflect any analysis skills, but rather an exemplary sheet for what we can create with the data.

Conclusion

It was a fun challenge at the beginning of my path to create a database for a real client solving a real problem (my wife though, but still counts).

Using Excel for this type of tasks has its advantages like:

• The ease of use and familiarity for me the designer as well as the user.
• VBA is a simple coding language especially for inexperienced people. Once you get the basics of how it works, setting up some automations can be an easy task.
• You can set up quick relations between the data using Expressions and Data Validation.
• It’s a one stop shop where I can create tables, add automations, and create graphs and insights from the data.

But, as every data analyst have told me, Excel is not an ideal tool for creating Databases:

• Scaling this project up adding so much products, clients, or orders is not ideal for Excel.
• Even though VBA was simple to use as I mentioned, creating these automations in VBA required so much code, and problems anticipation. Each time you need to add a column, or a data check, you need to add code in several places.
• Data Validation is done manually, so anytime you need to add anything, you must make sure it’s included.
• Can’t create key relationships between columns in different tables (unless you use Power Query).
• Adding more columns and/or tables is not dynamic.

If you arrived here, thanks a lot for taking the time to read my article.