Rather than an ordinary database program people prefer spreadsheets because it’s good for storing and retrieving. It can do calculations very fast and efficient. Excel also with the use of formulas is better than word processor or a database program.
My end user would use the spreadsheet that I will design. My end user would be the shop owner who is using this as a super calculator and would help them get good outcomes from the spreadsheet.
The end user requirements should be:
Fast data search
Accurate calculations
Easy to use (user friendly)
Everything sorted out by using the Sort function
Get good results and easy to understand them
To get from one sheet to another sheet I will be using macro buttons. First I will record a macro and play it back by using a button. This will help the user get around the spreadsheet very fast and quick.
For my project I have used Excel and the Flexi data. Before I have already mentioned why excel is the best program for creating this system. Flexi data was used to get the customer’s data. I could have used excel for this but I think that filling in the data in the format of a form is better. When you enter the data in flexi data you have to fill in a form. Then I copied and pasted into excel.
I need quite a few different types of data. For example the most common used would be text. It will be used in the stock, customer names, and other customer details. The most important one however would be codes and serial no. for my stock. These codes represent the type of game that it is. So a sports game would start with 3 and every time a customer is looking for a sports game you would look in the 3 column and find the game the customer needs. The serial number however is an individual number given to each game in stock.
The problem that excel solves is that it holds all the stock information. It calculates the prices for the rented out games and it would also calculate the fine charges for the late games brought back. Customers would be fined £1.00 for every night they keep the game Overdue. On sheet 2 there is all the information about the business and how the whole week has gone showing results and profits earned by late charges.
There are lots of copies for one game and since they all have to be returned one day by the customer there is a serial number to see which customer took out which copy. For this to be a serial number I highlighted the whole column and went to format-cells-type-numbers up to 0 decimal places. Another format of data used was date. This is very important when the game should be returned so there should be a record of when the game is took out. Very important and easy to do in excel as you can just format the cells to –date.
I will use a few formulas for my project in excel. These are the following important formulas.
Sum
Randbetween
Vlook up
Validation of data
Graphs etc.
The few mathematics I would be using in my project are simple maths such as subtracting adding and averages. So far this is what I have planned but as the spreadsheet gets underway then I will have more to add.
I won’t be using too many random values but I have used unique data. Some data might be identical (game titles) but when it comes to the serial number it’s unique. I have mentioned before why I chose this because the end user would know which game was taken out by which member. I have used 4 different worksheets. On each worksheet there is important data except for the first one.
Worksheet one would be the title page for the shop and a bit of design. Worksheet 2 however would be far more important and that’s where the whole economical information about the shop is found. It shows all the information on which what member rented out games on which day. It will show some graphs and results on how the week has gone past. It will practically be the most useful page to look at when it comes to sales. Worksheet 3 would be all the stock. This page would also tell you some information about the games that are rented out from the shop. It has a code and serial (unique) number for each game in stock. Finally is the worksheet 4 where there is all the information about every customer who registered with the Shop. So if any customer has a late fee to pay or hasn’t returned a due in game then the shop owner would have to go through this:
Flowchart next page-----------→>>>>>>>>>>>
No
Yes
In this part of the project I have now created my spreadsheet here I will show what changes and testing were done to the spreadsheet and how I managed to get my final implement. The main change that I gave to my spreadsheet was unique stock names. As I mentioned in my analysis that I will keep the stock name the same but a unique serial number. However I found out that when Vlookup was used to get data from one sheet to the other the data was not shown uniquely.
Below is an example of what happened.
A very important part of the spreadsheet is validation. This was also part of my testing. To test my spreadsheet I tied to enter an anomaly data into a validated cell and see what message appears. How ever the most use of validation in my spreadsheet was a dropdown list. Here is what I had done.
Finally my main part of implementation is the testing and user responses.
I tested my spreadsheet as mentioned above by using validation.
Below are a couple of more examples of testing.
Testing continued testing if a macro works.
That button took me to this screen so my test was successful.
