The user will need to get their input data from the labels in the shop, or if they already know the name of the shoe they may enter it and the system will reply whether it is available or not (N/A).
Data Input
Once the information has been collected I will key it into the spread sheet straight from the list given to me by the assistant who composed it. All updates made to the system when new stock arrives will be keyed in by Mr Ariel Sharon, and when a shoe is purchased or returned updates shall be made by the assistant at the till. Mr Ariel Sharon, the sales assistants, or the customer will key in the shoe's model name when the system is used.
The initial data entry and any updates will be verified by visually checking it once it has been entered. The tables containing the information about the stock will need their cells to be automatically validated and conditionally formatted, so the system will not accept negative numbers in the quantity cells and if the quantity is '0' then it shall automatically be formatted to be bold red so that Mr Ariel Sharon can easily recognise that they have run out of that particular shoe in that size.
The cell where the user is to input the shoe's name shall be validated by only allowing a text length of '20' letters, numbers can be input though as some model names include a few digits.
Data Flow
Updating Information regarding quantities and latest prices
Mr Ariel Sharon updates the prices of the shoes and the quantities whenever new stock arrives at the shop, however the quantities change quickly whenever a shoe is purchased or returned so the till would have to be linked to the stock sheets and the assistant at the till shall ensure that the quantity remaining is less (if it is a purchase) or has increased (if it is a return). The new information will obviously replace the old information.
Dealing with customer enquiries
A customer enters the name of the shoe he has chosen into the system, with the same spelling as the one in the label, and the details of the shoe (quantities and latest price) are given to the customer.
Data Manipulation
The raw shoes information will need to be keyed into the spreadsheet. Formulae will be entered into the spreadsheet to find the preferred shoe and display all of the relevant information.
The data is manipulated as follows:
- User enters exact name of shoe.
- Formulae are used to search for the name entered.
- Once the name has been found the relevant quantities and latest price are displayed on the user's screen.
- If the quantity is '0' then a red short sentence should appear apologising and stating that there is none of that size in stock.
Output
Once the user has typed in the name of the shoe and pressed 'Enter', the shoe's details will be displayed on the screen. A printer will not be used as there will be no printouts, the printouts would be more or less useless as quantity changes very quickly and prices can easily be remembered, Mr Ariel Sharon is not eager to buy a new printer anyway.
Backup / Security Strategy
There will be two files, one for the tables of the stock in ShoeShop©, and the other for the search pages. These two files, wherever they are stored have to be together as the search file refers to the stock file. The two files will be saved on the hard disk and must be backed up onto a floppy disk; a backup copy needs to be made at the end of each day.
In the search file there should be some password security so that people using the system cannot change any of the formulae. This can be done by protecting the worksheets so that nothing can be changed except the shoe name that the customer will input. If anyone attempts to make a change or unprotect the worksheet, Excel will ask for a password.
The stock file will be password protected so that only Mr Ariel Sharon and the assistants can open it. The sheets will not be protected though because there will already be a password protecting the whole file.
The passwords should be at least six characters and preferably not a common word such as "adidas" or "Reebok" that is easily guessed and nor should they be written down. It should be not too obvious but something Mr Ariel Sharon can memorise.
DESIGN
Initial Designs
An initial design for the system was drawn up as follows:
Initial design
The spreadsheet will search for the shoe based on the name that the user inputs on the entry form. The name of the shoe will be looked in the specified sports brand's table and from this the quantities available for each shoe size and the latest prices will be given. In the search file, there will be a "- Home -" sheet which will have a colourful user-friendly interface with the pictures of all six brands' logos, clicking on a logo takes you to its search page. There will also be a separate search sheet for each brand, each one searching the relevant sheet from the stock list file. The user shall input the shoe name and press 'Enter', if he would like to restart he may click on a simple button running a macro that deletes the contents of the shoe name cell, if he wishes to search another brand, he may click on its logo which will take him to the relevant sheet to perform the search. Once the user has finished he clicks on the 'end' button which takes him back to the home sheet.
User Feedback on Initial Designs
Mr Ariel Sharon said that he liked the idea of having a "- Home -" sheet with the brands' logos and the colourful user interface. This he said would encourage the customers to use the system and because the computer is located next to the front window, the sheet could also act as an advertisement for the shop and would possibly attract passers by to walk into the shop.
He also said that he liked the idea of having buttons with recorded macros assigned to them, stating that it makes it easy for any sort of user, young and old, to use the system. Mr Ariel Sharon said that he wanted a system which the customer can use without having to ask any of the assistants for help regarding its use, so clear comprehensible instructions should be added, these instructions will be dictated by Mr Ariel Sharon himself as he will be aware of how to use and modify the system.
Final Design
This is how each of the individual sports brands stock sheets will look. The other network sheets will differ only in the shoes' model names and colour scheme. FiLA will have a blue background and its stock table will have a light turquoise background where N I K E will be red and white, PUMA will be lime and light green, Reebok will be tan and orange and UMBRO will be sky blue and pale blue.
Breakdown of solution into sub-tasks
The implementation of the final design will need to be broken down into more manageable sub-tasks:
-
Create a spreadsheet file with the name "STOCK-ShoeShop©".
- Set up row and column headings and enter data for each of the shoes on each sports brands stock table. Name and colour worksheet tabs and cells.
- Add data validations. Conditionally format the quantity cells.
-
Enter the formulae to calculate the total number of shoes in each size and total number of shoes in stock from each brand.
- Password-protect the stock file so that it can only be opened when a password is entered.
-
Only after creating the stock file, create another spreadsheet file with the name "SEARCH-ShoeShop©".
-
Rename "sheet1" as "- Home -". Add heading of "ShoeShop©" and insert images of sports brands logos. Make an attractive brief introduction to the system using "word art".
- Enter formulae which equal the stock remaining in the stock file, next to each logo.
- Create search sheets, one for each brand. Enter appropriate formulae.
- Smarten up sheets and link them to each other by assigning the logos macros which open specific sheets.
- Protect all cells in this file except for the one which requires input from the customer, and add a password.
Task 1-2: Create stock file, set up row and column headings and enter data (Design Sheet 1)
As I said earlier, the stock file will be called "STOCK-ShoeShop©" and will be the first file to be created. Once it is created a heading will need to go at the top of each stock sheet; this will be the particular brand's logo which will be obtained from the internet. This heading will be followed by eight horizontal column headings:
Once all of the stock sheets have been made; sheets and cells will be coloured with the appropriate colours, similar to the contrast of those in each brand's logo, for example, the sheet tab and the cells in adidas will mostly be black and white. The sheet names will be as follows:
"adidas®"
"FiLA®"
"N I K E®"
"PUMA®"
"Reebok®"
"UMBRO®"
Task 3-4: Add validations and conditional formatting, enter formulae (Design Sheet 1)
There will only be one validation rule in the stock tables, so the cells in under the quantity and price headings must be positive numbers as it will be impossible to have negative quantity or price values.
I will also conditionally format the quantity cells so that when they are '0' (i.e. out-of-stock) they should be formatted as bold red. This is to carry out Mr Ariel Sharon's request of making any out-of-stock shoes noticeable so that more stock can be ordered.
The formulae in the worksheets, very simple ones, for each sports brand will be very similar to each other similar but the cell references will obviously differ.
The formulae in this table can be applied to the information entered in the stock tables, which will give the total stock remaining, and the total for a specific size.
This table and formulae can then be copied across to other worksheets to calculate totals for other sports brands.
Task 5: Password protect stock file
The spreadsheet file, "STOCK-ShoeShop©", will be protected with a password. This means that no body can open the stock list file without the password which has been chosen by Mr Ariel Sharon. When it has been opened by inputting the password, the cells can be modified or deleted as the cells have not been protected; Mr Ariel Sharon is aware of this and says that one password is enough for the file.
Task 6-7: Create search file, "- Home -" sheet, insert heading and logo images (Design Sheet 2)
After creating the stock file and saving it, I will have to create the other spreadsheet file and name it "SEARCH-ShoeShop©". I will also rename "sheet1" in the new file as "- Home -" which will be the starting sheet for the system's users. Once this is done I will insert the large heading of "ShoeShop©", and will also obtain the logos from the relevant sports brand's web pages on the internet and insert them into the sheet along with the brief word art message.
Task 8: Enter formulae next to each logo
The formulae entered will simply equal the "STOCK REMAINING" in the stock file:
Next to the adidas logo - ='[STOCK-ShoeShop©.xls]adidas®'!$E$3
Next to the FiLA logo - ='[STOCK-ShoeShop©.xls]FiLA®'!$E$3
Next to the N I K E logo - ='[STOCK-ShoeShop©.xls]N I K E®'!$E$3
Next to the PUMA logo - ='[STOCK-ShoeShop©.xls]PUMA®'!$E$3
Next to the Reebok logo - ='[STOCK-ShoeShop©.xls]Reebok®'!$E$3
Next to the UMBRO logo - ='[STOCK-ShoeShop©.xls]UMBRO®'!$E$3
"E3" being the cell reference to the "STOCK REMAINING". Similar formulaes will be put next to the other logos, but with different sheet references.
Task 9-10: Create Search sheets and enter formulae, smarten up sheets, assign logos macros (Design Sheet 3)
After finishing off the home sheet, I will make six additional sheets with the following names:
"SEARCH-adidas®"
"SEARCH-FiLA®"
"SEARCH-N I K E®"
"SEARCH-PUMA®"
"SEARCH-Reebok®"
"SEARCH-UMBRO®"
As it can be seen there will be a search sheet for each brand. The worksheets for each brand will be similar but formulae will differ as each sheet will be for a different search.
At the top of each search sheet there will be the logo of the brand being searched, and next to it will be a simple instruction on how to search shoes from another brand. The logos in design sheet 2 will be listed on the left of the displayed information concerning the quantities and prices, they will be command buttons with macros assigned to them, these macros open the relevant search sheet when the logos are clicked on. I will need to give names to each of the cells containing input values. This will make them easier to refer to in my formulae. They will be called ModelName_adidas, ModelName_FiLA, ModelName_NIKE, ModelName_PUMA, ModelName_Reebok and ModelName_UMBRO respectively. Next to the input cell there will be a 'restart' button which simply deletes the contents of the input cell (ModelName).
At the bottom next to "TOTAL Qty." there will be another command button, 'END' which simply takes the user back to the home sheet. The user keys in the shoe's name in cell "E6".
The formulae in the search sheets (though in this table only the adidas search sheet ones are shown) are as follows:
*The VLOOKUP formulae in the search sheets all refer to the stock file, and the relevant stock tables.
The formulae in the table above can be applied to the other brands' search sheets in the search file to display the quantities available and the latest price. This table and formulae can be copied across to other worksheets, with slight adjustments to table references, to display the information for the remaining sports brands.
I will conditionally format the cells where the apology should appear in when there is no stock so that the apology only shows when something is entered into 'E6' which is where the user enters the model name. When the sentence appears it will be in bold dark red to alert the user and make it clear, using the colour language, that the shoe is not available. The cells where the value of quantity available will be formatted to have a white background and font, I will conditionally format them so that only when there is a value above 0 the quantity will show, otherwise, only the apologetic sentence will appear.
I will also add a validation rule to the input cell so that it only accepts text lengths which are under 20 letters; this text length has been specified by Mr Ariel Sharon.
Task 11: Protect all cells in search file except those which require input from the customer, and add a password
As a final touch, the spreadsheet will be protected with exception of the cells in which the user will need to enter the shoe's model name. This means that the user cannot change anything else without a password.
Test Plan
A test plan needs to be devised to make sure that the system does exactly that it is supposed to do. Where possible I have used extreme data to test all scenarios to the maximum.
IMPLEMENT
Finished Design
The finished design has been implemented in Microsoft Excel.
Stock Sheets
There are six worksheets for each of the sports brands. The design has just been slightly altered in the final implementation by making minor modifications to the layout.
The adidas® sheet in the stock file
The user interface shown above is the screen (of the sheet adidas®) from the stock file that will be seen by the assistants or Mr Ariel Sharon.
The same sheet, adidas®, as the above but with the formulae is shown below:
The simple formulae for the adidas® worksheet
The totals and the stock remaining are calculated using the SUM function. The formulae for the other 5 sports brands are the same.
Home Sheet (search file)
A customer can click on any of the logos in the home sheet, when this is done the user will be taken to the relevant search sheet. The design has been slightly altered in the final implementation by removing the gridlines and row and column headings. The scroll bars have also been removed; this is to make things less complicated for the user.
The "- Home -" worksheet, exactly how the user sees it
The formulae for the screen in the previous page are shown below (I had to change the column widths temporarily to fit the formulae into the page):
The "- Home -" sheet formulae
The macros attached to the logos simply open the specified search sheet for the particular sports brand. The codes for each macro are similar except that they open different sheets; I have recorded the macro so that it also clears the contents of the input cell (ModelName) in case there is already text from the last user. The code for the macro attached to the adidas logo is shown below:
The search_adidas macro
Search Sheets (search file)
A customer can enter their preferred shoe's name on the data entry screen as shown below. The design has been slightly altered by removing the row and column headings and making minor changes to the layout:
The "SEARCH-adidas®" worksheet, exactly how the user sees it
In this example, the user has entered 'tennis77' for the shoe's model name. They then press 'Enter' and the system returns the latest price, £14.99, and the information regarding the shoe's availability. If they wish to search for another shoe they may click on restart and input another shoe name, or, if the shoe is from a different brand, they may click on the brand's logo and key in the shoes name in the 'ModelName' cell and press enter.
The shoe's model name is found in the stock file and displayed in the search sheet using the VLOOKUP function. The price and quantities for sizes 6, 7, 8, 9, 10, 11 are displayed in cells E8, E10, E12, E14, E16, E18, E20.
The formulae for the screen on the previous page (SEARCH-adidas®) are shown below (I had to change the column widths temporarily to fit the formulae into the page):
The "SEARCH-adidas®" sheet formulae
The macro attached to the 'Restart' button simply deletes the contents of the input cell. The code for this is shown below:
The delete_model macro
The other button, 'END', runs another macro which opens the home sheet. The code for this is shown below:
The home macro
There are other macros assigned to each brand's logo in the search file, their codes are all similar to the search adidas macro shown on page 21.
Diagram showing processes involved
This shows how the system has been implemented:
Process diagram continued…
Test Results
This section shows the results of the test performed following the test plan in the Design section.
Results for tests involving stock file
Test 1: Test stock file password protection
Test 1: Result as expected
Test 2: Test validation for stock tables
Test 2: Result as expected
The error message was also displayed when abc was entered.
Test 3: Test Validation for prices
Test 3: Result as expected
The error message was also displayed when -1 and 0 were entered.
Test 4: Test conditional formatting for stock tables
Test 4: Result as expected
Test 5: Test SUM formulae to see if they give correct totals
Test 5: Result as expected
Test 6: Test the other SUM function to see if correct total of stock remaining is shown
Test 6: Result as expected
Results for tests involving – Home – sheet
Test 7: Test to see if cells in home sheet can be edited without a password
This test involved clicking on different cells on the home worksheet. It was not possible to click in any cell. None of the cells could be changed and buttons still worked. A password was not asked for, as expected – instead, nothing happened.
Test 8: Test to see if the formulae next to each logo equal the stock remaining in the stock file
Test 8: Result as expected
Test 9: Test to see if the macros assigned to each logo work
This test involved clicking on the logos shown in the home sheet. As expected, relevant search sheet was displayed when one of the logos was clicked on; I clicked on the adidas logo and was taken to the adidas sheet.
Results for tests involving search sheets
Test 10: Test validation for input cell (ModelName_FiLA)
Test 10: Result as expected
Test 11: Test macro assigned to the 'Restart' button
This test involved entering text into the input cell and then clicking the restart button. As expected, the contents of the input cell were deleted.
Test 12: Test display of latest price
Test 12: Result as expected
Test 13: Test display of quantity in size 6
Test 13: Result as expected
Test 14: Test display of quantity in size 7
Test 14: Result as expected
Test 15: Test display of quantity in size 8
Test 15: Result as expected
Test 16: Test display of quantity in size 9
Test 16: Result as expected
Test 17: Test display of quantity in size 10
Test 17: Result as expected
Test 18: Test display of quantity in size 11 / apology sentence. This test also ensured that the conditional formatting works in the search sheets
Test 18: Result as expected
Test 19: Test macros assigned to the logo buttons on the left of search sheets
This test involved opening the FiLA search sheet and clicking on the adidas logo on the left. The system successfully opened the adidas search sheet as expected.
Test 20: Test macro assigned to the 'END' button
The macro assigned to the END button shown on the diagram above successfully opened the home sheet as expected.
Test 21: Test to see if cells outside 'E6' (Data Input) in the search sheets can be edited without a password
This test involved clicking on different cells on the search worksheets. It was not possible to click in any cell except cell E6. Only cell E6 could be changed and buttons still worked. A password was not asked for, as expected – instead, nothing happened.
Test 22: Test time taken to find and display price and availability
This test was done using a stopwatch and took less than a second to return the details of the relevant shoe to the search data entry screen.
EVALUATE
Evaluation of initial Objectives
The system has a friendly user interface and Mr Ariel Sharon found it extremely easy to use.
A customer or sales assistant can enter the model name of the shoe that they want to check the availability of.
The system caters for all six sports brands. The current stock list and prices have been entered into the spreadsheets using the list provided by one of the assistants and Mr Ariel Sharon. One problem is that these stock lists containing the names of the shows in stock will change quite frequently and Mr Ariel Sharon will have to update the data on the spreadsheet. If new shoes arrive at the shop they have to be added to the stock list, this means some formulae need to be changed, for example the VLOOKUP formulae need to be changed in the search sheets to expand the table being searched or looked up.
It searches for the shoe and clearly displays the quantity remaining in all sizes and the latest price of the shoe in less than one second. It also gives the total quantity remaining for all the sizes added up. If there are no shoes remaining then the system automatically displays an apology sentence and therefore giving a clear answer to the user's enquiry.
The system handles all valid quantity values entered and rejects invalid user entries. Also, the system did not involve any additional hardware or software costs.
User feedback on solution
Mr Ariel Sharon was very pleased with the spreadsheet; however he foresaw some problems in using it as the exact spelling of the preferred shoe's model name has to be entered by the user and some customers may not be aware of the correct spelling or may not be too familiar with key boards and so there is a strong possibility that they input the correct shoe name but with a different spelling and the system would show N/A (not available). He agreed that it would have been extremely complicated to include a search feature which brings a result, even if it is not exact, based on the information input, it would also be time-consuming to keep up to date.
Further suggestions for improvements
The system would be even more useful if it included an easy facility to add new shoes and update formulae. It would have also been useful if the system would also lookup and display the shoe's picture and more details concerning the shoe.
When the user presses the 'Restart' button the screen flicks briefly and the cell selection is changed before selecting the input cell. I think this is a slight problem from the macro I recorded but it would be possible to write a macro preventing this so that all unnecessary movements of the screen or selections are invisible.
