The computer will run Ubuntu Linux Hardy Heron, which is a free operating system, and OpenOffice Calc comes already installed. This seems to be a very good idea, because it is saving Mr. Burnquest roughly £340.
Data required
The customers will have to give the staff information, if they want to know, for example, the price of a particular skateboard deck.
- The product number (via scanning of a barcode), or
This will allow the staff to search the database for said product, and find out information about it. An invoice or receipt can also be printed off, if the customer wishes to buy the product.
Data collection
The information on each product sold by the store will be kept on the computer, in the spreadsheet, so when customers either phone or come in to the store to ask for information, the staff can give them said information. Also, the customers could fill out a search form on the company website, allowing them to search for information on any product with ease, from their own home. It is important that the stock information is updated regularly, if not connected to the spreadsheet of data.
Data input
When the a customer, or staff, wants information on an item sold by the store, a member of staff will use the keyboard to search for the information given to them (e.g. the item name). Once the information is found, all of it will be displayed to the member of staff that requested it; pricing, stock, brand .etc.
Data flow diagrams
The amount of stock will change with every purchase, so it will be doe automatically. This means that Mr. Burnquest doesn't have to sit down and count stock every day, to update the spreadsheet.
Customer enquiries
When a customer wants to know information on a product, a member of staff will search the spreadsheet for the products information.
Output methods
If a customer phones the store for item information, then only a monitor would be necessary, as only the member of staff needs to see the information to pass it onto the customer. If a projector would be used, it would display everything that the staff member sees. Some information is private, so this isn't a good idea, and it would just cost more. Customers can ask for a print out, of a receipt .etc, which is a good idea, but it can get expensive, due to printing costs.
Output method justification
The decided output will be via a monitor, because only the staff needs to enter and view the product information directly from the computer.
Backup and security
The data from the computer will be backed up onto several other machines, which will be locked, so nobody can view them without the master password. This will greatly reduce the chances of the file being lost, or stolen, since there are multiple copies. Also, Mr. Burnquest will keep a copy on a USB memory stick, which he keeps safely locked up in his house.
The only person that can alter the file, since it will be password protected, will be Mr. Burnquest. If someone tries to edit the spreadsheet, then they will get an error, and a password will be asked for. The password will have to be very secure, and something that Mr. Burnquest can memorise, and not write down. Also, the file will be “Read only” (or 644CHMOD), and only a computer admin can alter the permissions of the spreadsheet.
Design
Initial designs
Home page #1:
The logo will be in a green font colour, in size 18 Century Gothic, whilst the slogan will be in a black font colour, size 16 Century Gothic. The text navigation will be blue, in size 10 Tahoma. The image navigation will consist of 4 images, each representing (and linking to) each product page of the site. The copyright information will be black, in size 8 Arial.
Home page #2:
The logo will be in a green font colour, in size 20 Tahoma. The slogan will be in a black font colour, size 16 Tahoma. The text navigation will be blue, in size 10 Arial. The image navigation will consist of 4 images, each representing (and linking to) each product page of the site. The copyright information will be black, in size 8 Arial.
Home page #3:
The logo will be in a green font colour, in size 30 Arial. The slogan will be in a black font colour, size 16 Arial. The text navigation will be black, in size 10 Arial. The image navigation will consist of 4 images, each representing (and linking to) each product page of the site. The copyright information will be black, in size 8 Arial.
Input page #1:
The logo will be in a green font colour, in size 18 Century Gothic, whilst the slogan will be in a black font colour, size 16 Century Gothic. The text navigation will be blue, in size 10 Tahoma. The product name/price/quantity, buy and currency will be black in size 10 Century Gothic. The copyright information will be black, in size 8 Arial. The same template will be used on all 4 product pages.
Input page #2:
The logo will be in a green font colour, in size 20 Tahoma. The slogan will be in a black font colour, size 16 Tahoma. The product name/price/quantity, buy and currency will be black in size 10 Tahoma. The copyright information will be black, in size 8 Arial.
Input page #3:
The logo will be in a green font colour, in size 30 Arial. The slogan will be in a black font colour, size 16 Arial. The product name/price/quantity, buy and currency will be black in size 12 Arial. The copyright information will be black, in size 8 Arial.
Contact page #1 (Email):
The logo will be in a green font colour, in size 30 Arial. The slogan will be in a black font colour, size 16 Arial. The employee email details (along with their name and position) will be black in size 12 Arial. The copyright information will be black, in size 8 Arial.
Contact page #2 (Phone):
The logo will be in a green font colour, in size 30 Arial. The slogan will be in a black font colour, size 16 Arial. The employee telephone details (along with their name and position) will be black in size 12 Arial. The copyright information will be black, in size 8 Arial.
Output page #1 (Invoice):
The logo will be in a green font colour, in size 30 Arial. The slogan will be in a black font colour, size 16 Arial. The items that are currently in the checkout will be listed (along with their prices/quantities/names) in Arial, black, size 10. The shipping details will be in black, Arial size 10. The copyright information will be black, in size 8 Arial.
Output page #2 (Receipt):
The logo will be in a green font colour, in size 30 Arial. The slogan will be in a black font colour, size 16 Arial. The checkout (displaying sub-totals of prices, quantities and also a list of item names) will be black in Arial size 10. The copyright information will be black, in size 8 Arial.
User feedback
Person #1:
“I liked home page #2. It is simple, and well lay out. I also think that a large space allocated to image navigation makes everything easier to use. I did think that home pages #1+3 were more cluttered, and laid out very poorly.
I also favored input page #2, as it matched the style of home page #2, which means it is well laid out, and easy to use. I also like the use of product images and tick boxes.
Overall I like the simplicity of the initial designs, and I look forward to seeing the final system.”
Person #2:
“I like home page #3. It is very easy to use, and the image navigation is pure genius. It is similar to home page #2, but I like the arrangement of the sections on the page.
Also, I liked input page #2. I like the idea of moving the navigation menu around on each page, just to spice the site up. Again, it is well laid out and very easy to use.
Output page #2 is nice, but I think that the information will be a little cluttered, if the person buys a large amount of items.”
Person #3:
“For the home page, I liked home page #3. It is simple, and I like the way it is laid out. I do think that it would be hard to pull off, without it looking busy and cluttered.
I like input page #3, because it fits in well with home page #3 (my favorite of the 3 home pages). It also fits in well with the output pages.
All in all, I think it will be difficult to achieve, but if done well, it will look fantastic.”
Final designs
Home page:
On the home page, I decided to stay along the lines of home page #2. The logo will be green, in Century Gothic size 18. The slogan will be black, Century Gothic size 16. This, the copyright information (black, Arial size 10) and the text navigation menu (black text, Tahoma size 12), will be displayed on every page. This will be to keep a consistent layout.
The image navigation will contain 4 images, each linking to the 4 product pages of the site.
Input page:
The product details will be in black, size 12 Century Gothic.
On the input page, a product image is included, and a LOOKUP function is used to get the product information. Users can then tick the buy box, and continue on to the checkout where it will be added to the list.
Output page:
On the output page, the checkout list is cell linked from the receipt page. Using an IF statement, the items will not appear on the list if the item isn't ticked as “buy”. The total price and checkout list will be in black, size 12 Tahoma.
Subtasks
- Allocate an area for customers to input their personal data.
- Users must only be allowed to alter the information that they input.
- Customers must be able to view a list of all products and the related information about said product, if they so wish.
- Have a sheet containing all related information of a product.
- Devise a navigation menu that allows the user to make their way around the system with ease.
Tests
Implement
Page #1; Home
This is the home page. From here, the user can navigate to the different sections of the website by clicking on one of the links. As you can see, I have decided to revamp the design; it now contains soft colours, and the layout is more professional.
Page #2; Input
This is on of the many input pages, from which the user can select how much of said item they want to buy. The price, quantity, product image and name are all given to the user.
Page #3; Invoice
On this page, the products selected by the user are displayed, and the user can also input their personal details for shipping/payment.
Note that, since the invoice page is quite long, I have taken a screenshot of the important part.
Page #4; ReceiptOn the receipt page, the items that the customer has bought are listed, along with prices, totals, sub totals and quantity figures. There is a link that will take the user to the invoice page, show above.
Note that the receipt page is also quite lengthy, so I have a screenshot of the important part.
Tests
Test #1; Hyperlinks (Passed)
Below is a link that should take the user from the home page to a product page:
It takes us to the correct page:
Test #2; IF Statements (Passed)
This is the correct IF statement to display (or not) the product information:
=IF(G26="Girl",VLOOKUP(G26,Decks,2,0)*Decks_Girl!H19,"")
Test #3; VLOOKUP Functions (Passed)
This is the correct VLOOKUP function to pull the product information to the product page:
=VLOOKUP(A34,Decks,2,0)*H19
Test #4; Data Validation (Passed)
A drop-down box for item quantity should appear:
Test #5; SUM Statements (Failed)
The SUM statement should add up cells J31 -> J34:
Instead of adding up cells J31 -> J34, it is adding up cells G31 -> G34. I changed the SUM statement from:
=SUM(G31:G34)
To:
=SUM(J31:J34)
Test #6; Cell Linking (Failed)
The cell Checkout!J14 should be linked:
Instead, it is linking cell Contact!J14. To correct this, I changed it from:
=Contact!J14
To:
=Checkout!J14
Test #7; Product Details (Passed)
The product details from the VLOOKUP table Decks for Girl should be displayed:
We can double check this by looking at the table:
Test #8; Layout Consistency (Passed)
To show layout consistency, we can go from one page to another, and only see a change to the content:
Test #9; Spelling/Grammar Errors (Passed)
If we run a spell check, we should get no errors:
Test #10; Security (Passed)
The spreadsheet should be set to Read-Only, for maximum security:
Evaluation
Spreadsheet requirements
- The spreadsheet must be easy to use, and quick to find information, so that customers aren't waiting around for too long.
I think that I have achieved this requirement, because an easy-to-read interface, coupled with hyperlinks, make it very quick to find what you need.
- Information about the number of each item left in stock must be given.
This was actually something that I didn’t manage to provide, due to my lacking knowledge in spreadsheet creation. I was, however, able to create a way of buying multiples of each item from the buy page.
- The system must be able to hold a large amount of data, incase new items are added.
This is never a problem for any spreadsheet, since a spreadsheet can hold far greater data than anyone could actually provide on a particular subject.
- New hardware or software should not have to be bought; all that will be needed is OpenOffice Calc (or Microsoft Excel if preferred by the user) and a computer to run it on.
When I made the spreadsheet, I didn’t buy any extra products; I just used OpenOffice Calc.
- The system must have a user-friendly interface, and a company logo atop each page.
The overall look of the spreadsheet was soft and friendly, with a logo atop each page.
- The system must be able to invoice the user’s bank account, and print off a receipt as proof of purchase.
At the bottom of the receipt page, the user can print off a receipt for proof of purchase if they so wish.
- The important areas of the spreadsheet must be protected in some way, and the whole system must be “Read-only” (or 644CHMOD).
The spreadsheet has been assigned a CHMOD of 644 (“Read-only” for Windows users).
- The system must be easy to backup, and the backup folder must be secured.
To backup the system, simply copy and paste it into a folder. Then CHMOD the folder to 644 (“Read-only”).
- The system must contain a navigation column, to direct users around with ease.
Yes, the spreadsheet actually contains two navigation menus to help the user navigate the system.
Critical comment
To improve the spreadsheet, I could spend more time on the design; at the moment, the colours are dull and boring, although the actually layout is very fitting. For example, I could create custom colours to brighten up the spreadsheet.
User feedback; letter
Dear Steven Burnett,
I am writing to you with my thoughts regarding the spreadsheet of which you made for SkateUK. I asked you to create an easy-to-use, simple spreadsheet for my rapidly expanding shop, and you have excelled my expectations.
I found the layout to be professionally designed, and very easy to navigate. My employees are now very quick to respond to customer queries. The way that you implemented the shopping cart is ingenious.
Although I do appreciate your work for SkateUK, I would like to add this: next time you create a spreadsheet, create a way of editing the stock number. It is still a real pain for me to add/remove stock from the system, although things aren’t so bad now.
Thank you,
My response
I have taken Mr. Burnquests’ feedback into account; next time, I will spend more time on creating new ways of managing stocks, and less time on the design of the system. After all, there is no point of having a pretty system if it doesn’t do anything.
