Customer Database in Excel

My website which was created as an assignment for E-commerce required a database to hold product information, stock quantity, customer details, and product revenues. To manage all of the information I manage to create a small database in Excel which contain three tables. This post will explain the process of creating each table, the information included in each table, and the features used to design and format the excel database.

Table 1 - Table 1 contains five columns and ten rows of data. Heeding in the table represents Product No, Product Name, Unit Price, Stock, and Units Sold. Product No. is generated automatically as is driving on formula, Product Name provides a description for all of the items which are available on my website, whereas Units Price provides price for these items. The stock column provides information on stock holding level and Units Sold tracking the sold quantity.

Table 1 uses the following formulas:

="WP"&ROW($A2)-1 - The formula used in Product No. column. The row function provides the row number in a spreadsheet and is incremented by one every time when the formula is dragged down. "WP" is a shortcut for wooden products and is concatenated with the row function which results in a unique ID number for example WP1.

=CEILING(RAND()*100,1)+100 - The formula used in the Stock column, is generating a random number for each row. RAND() function create a number between 0 and 1 which then is multiplied by 100 to get a full number, CEILING( ,1) function is used to round the number to 1 provided by the rand function for example 105.54 = 106

=CEILING(D2*0.3,1) - this formula is used in the Units Sold column to generate the quantity sold. The function is using a stock column and taking 30% of stock depending on the stock holding, then CEILING( ,1) it is used to round the decimal number to the nearest 1

Table 2 - Contain four columns and ten rows. Heeding in the table includes customer information Customer Number, Customer Name & Surname, Customer Phone number, and Customer Address. Only one formula was used to create a unique customer number the remaining data was generated by the website which generate fake names, surnames, phone numbers, and addresses (British name generator, 2022).

="CNU"&ROW(G2)-1 - The formula used in the Customer Number column. The row function provides the row number in a spreadsheet and is incremented by one every time when the formula is dragged down. "CNU" is a shortcut for customer numbers and is concatenated with the row function which results in a unique ID number for example CNU1.

Table 3 - Provide selected information from table 1 and table 2, including four columns and ten rows. Heeding contain Customer Number, Product Name, Units purchased, and Total Pay. Table 3 uses the primary key from table 2 and the foreign key is a Product Name from table 1 which allows connection between the two tables.

Table 3 is using following formulas to pull information from tables 1 & 2:

=CEILING(INDEX($E$2:$E$11,MATCH($B16,$B$2:$B$11,0))*0.1,1) - INDEX() & MATCH() are lookup functions that provide the result based on the criteria specified in the formula, in that example function is looking for a number of units sold of the product from row 1 where the looking criteria are the product name. The result provided by the index function is multiplied by 10% to provide the units purchased by the specific customer.

=INDEX(Product[Unit Price],MATCH($B16,Product[Product Name],0))*C16 - Formula is using INDEX() & MATCH() function to find the product price in table 1 and then multiplied this by the total units purchased.

Combining the database from three tables allows me to make a comparison and visualization the purchase done by the customer. With that data, I was able to see how to match what stock was sold, which product is performing the best, or which one provide me the best profit and drive my sales.

Reference:

British name generator, British name generator. RandomNameGenerator. Available at: https://www.random-name-generator.com/united-kingdom?gender=&n=3&s=714 [Accessed September 3, 2022].