Thursday 8 August 2019

Short Interlude: Creating a Doll Database

Although I am intending in this project to sell the artwork I create.  I am going to practice how to create a database of customers here and a database of Sales figures to demonstrate how these databases would work in conjunction with with website.

For practice I am going to sell 20 different types of dolls at different prices.  Also I need a database of my customers details so  I began by creating this customer database:

To create this I followed the following actions:

Open Excel
Open Blank Sheet
Start adding the titles in row two of what I would like to fill in in each column.
I then started to add the details for each column until my table was filled with the correct data.
As I wanted to be able to filter and add formulas to this and to contain this on my spreadsheet I made this into a table so I selected from a row one to 12 all of my data and clicked on table.  This created a table around this

I added a title and changed the colour by using the colour filter and I merged and centred the text using this on the top menu. 

I now had my customer database as above.  I could now investigate that database by applying a filter.  For instance I could find out how many of my customers lived in London so I would go to Filter in the top menu and then click on the arrow over the address column and then deselect all and just type in London and the excel sheet would only show me customers in London like this:


I then created a sales database - it looks like this:

My sales database included the following information:

Product ID
Quantity in Stock
Sales to Date
Current Sales
Reorder Quantity

I completed the same process for this as my customer database however on this one I wanted the database to add up the totals and to complete a sum so that I could know how much stock to reorder.
I also again wanted to be able to filter this so her is an example of a filter - say I just want to know how many products were just £10 I can apply a filter like this:

I also on this chart added up the totals just by going to Autosum in the top left hand corner and completing sum and did this manually by creating a sum and using insert formula: =SUM(C3:C22)
To work out my reorder quantities I used the sum - =SUM(C3:C22-E3:E22) and this told me how many dolls I had left in stock or needed. 

I then also created pie charts so that I could then show this information in a more visual form to investors, employees etc. 

Although this database is fictional it does show how data can be organised for business purposes and how useful this would be to the business to ensure that things ran smoothly.  Obviously this does rely on human input however if this was attached to a website much of this would be automate as sales were made.

I am going to play about with this further and I will look at how to attach a database to my own site for future use!

No comments:

Post a Comment