The Camelot Restaurant Workbook
Sheet1 – Front Cover This sheet acts as a front cover for the form and has directional buttons for all the other forms on the Spreadsheet system. I shall create buttons for each of the sheets and create macros for each of the buttons so that when they are clicked they take you to the sheet. It will be very basic with just the buttons and a title saying “The Camelot restaurant”
Sunday (I have left out Monday, Tuesday, Wednesday, etc, as they are exactly the same) I will create a Title at the top of the page that tells the manager what day of the week it is. I will then create the Data capture part of the form, I have decided to use the Data Form that is available in Excel to do this as it creates a ready made form and I will not have to design it. The data entered into this form will be displayed on a table. I will also have another table at the side that will take information and this will be used in the formulas for the system.
Waitress Rota and Chef Rota – These will be in the form of a table, the Rota’s will use the data gathered from the bookings to work out how many chefs and waitresses are needed each and every hour. I will also create a formula so that it works out how many chefs and waitresses are needed to set up in the morning.
V-Lookup – This sheet will have the V-Lookups that are required for some of the formula’s for the system, the main V-Lookups will be for determining how many chefs and waitresses are needed. There will be a title at the top of each sheet, with the text of either Chef Rota or Waitress Rota.
Data Entry and Validation
Entering Bookings-Bookings are entered many times a day, there are quite a few Validation rules on entering bookings. The first is the time for the booking, a valid time will be between 12:00 and 22:30, and the reason being is that this is the time the restaurant is open. The next validation rule is that the number of guests allowed is from 1 to 50, the reason being that the restaurant cannot accommodate more than fifty on each level. Another Validation rule is that no more than 150 quests are allowed to dine in more than an hour, the main reason for this being that the cooking area is not big enough to produce more meals. Any booking with over 6 guest must leave a telephone number, the reason being that we must be able to contact the guests just in case.
Macros
These are all the different macros that I use in my spreadsheet:
ColourPrintSunday (Would also include ColourPrintMonday, ColourPrintTuesday, etc.)
Range("B11:E63").Select
Selection.Interior.ColorIndex = 7
Selection.PrintOut Copies:=1, Collate:=True
Selection.Interior.ColorIndex = xlNone
Weekdays (All days of the week and includes Waitress Rota and Chef Rota and V-Lookups)
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Saturday").Select
To go to a different worksheet change the Saturday to the name of the other worksheet
TransferDatatoList
Dim rowx As Long
Range("C3:C6").Select
Selection.Copy
Range("B11").Select
If Range("B12").Value <> "" Then
Selection.End(xlDown).Select
rowx = Selection.Row + 1
Cells(rowx, 2).Select
Else
Range("B12").Select
End If
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("B8").Select
Range("B12:E62").Select
Selection.Sort Key1:=Range("B12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C3").Select
End Sub
Security
As the people who will be using it may have to change the formula’s as they may have changed to a new menu where it takes longer or quicker for a meal to cook I decided not to lock out the formulas. I have used instead a program called access administrator that requires a password to get onto the spreadsheet therefore stopping customers from entering a booking when they come into a restaurant.
Test Strategy
My test strategy will include the following:
- Testing to check that each of my constructed macro’s work even under varying circumstances;
- Testing to check that the formula’s in my system work correctly;
- Testing if my Validation that I have created on my system stops people from entering the wrong type of information into the table;
- End user testing to check if the new system meets the requirements that the end user has specified.
Test Plan
Implementation
I had to make quite a few changes to my system, the most important change was that capture form, I was originally going to use the Data form on the excel sheet but I was unable to create any validation rules on the form. I decide to create my own form on the sheet, this way I would be able to use the Validation checks on the cells that the form was on. The only problem with this is that I had to find a way of taking the data from the new form and sticking it onto the table, I solved this by creating a long macro “TransferDataToList”, this Macro is shown under the macro’s sub-heading. I also created another sheet called Graph and this had a graph of how busy it is for the week.
Sunday (and the all the other days of the week)
In cell B3-I wrote the heading Time, B4-Number of Guests, B5-Phone Number and B6-Name, I then created a spinner for the number of Guests in C4 by selecting the Spinner button from the Form toolbar.
I created the headings for the table, these were the same as above but in cells B11, C11, D11 and E11, the table runs all the way down to cells B62:E62, in cell B63 ‘End of Table’ is entered.
I then created the validation for the sheet (see Validation) I done this by going to Validation in the data menu, after my testing however I found more problems with the system and so I had to create much more implementation. Two other formulas’ I created for Validation were in cell B8 and B9, in B8 the formula:
“=IF(C4>6,"Phone Number must be entered"," ")”
This should have made sure that any booking with over 6 guests left a phone number, but I later realised during testing that there must be something that should stop it from being entered into the table, in case the person taking the booking doesn’t see the warning. So I created a macro to stop this, it is shown in the commentary on Testing. In B9 the formula:
“=IF(SUM(I71:J71)>150,"Overbooked-Too many People in between 12:00 and 14:00"," ")”
This was for the validation rule that guests over 150 in two hours means the restaurant is too full, there are other formulae like this one for between 13:00 and 15:00, 14:00 and 16:00, etc. There was a problem with this though and it was that there was not enough room on the screen to create more warnings, so I decided to scrap this idea, as it would cause too much inconvenience to recreate the whole sheet.
The table to the left of the booking form captures the data and processes it into information of how many customers there are in each hour. I created 10 columns from I10 to R10, in the cells I11 to R11 there are the times from 12:00 to 22:00, for example in I11 “12:00-12:59” and in J11 “13:00-13:59”, etc, etc. Then in the cell underneath I created a formula that counts how many people there are in each time period, the formula will be:
“=IF(AND($B12>0.4999,$B12<0.540972222222222),$C12," ")”
All of the cells underneath have the same formula except that the reference of B12 will change to B13, the 0.499 and 0.540972222222222 are the numerical figures for 12:00 and 12:59. The cells B12, B13, etc are bookings in the table and this formula takes how many people there are for the booking if the booking is between 12:00 and 12:59. The other cells in this data capture table all have slightly different formula because they check cells they check changing, the cells to the side will have different formulas with the figures (the numerical figures which are for time) changing. There will then be equations that will give the total amount of people in each time period, these will be displayed at the top of the columns in cells I11, J11 up to R11. These equations are mostly in the form of: “=Sum(C5:G7)”
Chef Rota
I started this sheet by creating a title in the top left-hand side of the Sheet in A1, I then wrote all of the days of the week on row 2, and wrote Time in cell A2. In the column of A starting from A3 I wrote down the times in one-hour periods from when the restaurant opens to when it shuts (12:00 to 22:00). Then in each of the cells in between I wrote the formulae:
=VLOOKUP (Wednesday!M10,'VLookup table'!B4:C7,2)
The formulae for each cell is basically the same as the one above but Wednesday!M10 would be changed for what day and time the cell was looking up.
Waitress Rota
This is exactly the same set-up as the Chef Rota except the formulae in each cell is changed to the V-Lookup for the Waitresses, basically the B4:C7 would be changed to F4:G10.
V-Lookup
This sheet was the simplest to create but I had to alter the tables a few times to try and find what the right amount of chefs and waitresses are needed depending on how busy it will be. The two tables are shown in the printouts. The figures that I came up with for the Chef Rota are that for 0-13 customers an hour 1 chef is needed, for 13-30 customers an hour 2 chefs are needed. For 30-50 customers an hour 3 chefs are needed and 4 chefs are needed if there are more than 50 customers an hour.
Commentary of Testing
Test 1- Worked OK, only problem was that I had two templates saved, one was a prototype and had was not complete, fixed this by deleting prototype template
Test 2- worked OK, no problems
Test 3- did not work properly, the warning came up for that there should be a phone number but nothing stopped the booking from being entered. I cured this by creating a sub macro that only allows the button (Enter Details) to be pressed if there is less than 6 guests, with or without number but if there are more than 6 guests it can only be pressed if there is a phone number.
New Sub Macro:
Sub TransferDatatoList()
Dim noofpeople As Integer
noofpeople = ActiveSheet.Range("C4").Value
If noofpeople < 6 Then
Call TransferDatatoList1
Else
If Len(ActiveSheet.Range("C5").Value) > 0 Then Call TransferDatatoList1
End If
Test 4- did not work properly, if the cell is edited than the validation works and you must enter the correct type of data. However if the previous booking is deleted by pressing the delete button when the cell is selected but not being edited, then all the information can be deleted and a blank booking is entered into the table. However I was pretty lucky because when the booking was entered the empty booking went to bottom of the table and you can not see it.
Test 5-Worked perfectly
Test 6-Did not work as expected but still to satisfactory level, as the time is the first thing that is edited (when you double click on the cell), the validation that I created worked for this but if it was not clicked on at all then it didn’t work. Decided not to change it as it would cause the system to be too complex, it already took the system about 5 seconds for it to be entered into the table after the enter Booking button was pressed, and more macros would make this wait even longer.
Test 7-Same as above, could not fix the problem because of the same reason as above.
Test 8-Worked perfectly
Test 9-Worked perfectly
Test 10-Worked perfectly
Test 11-Did not work at all, the macro for Clear table was not fixed to the button, instead the Colour Print Sunday was fixed onto the button instead. The reason for this is that I copied the button from Print table button but forget to put the macro on the button
Test 12-Worked perfectly
Test 13-Worked perfectly
Test 14-Worked perfectly
Evaluation
Have I solved all of the problems of the manual system?
The main problems of the manual system were that the system was very scruffy, it was hard to read the bookings and some bookings would get misplaced. It also did not have any other useful features and the number of staff needed was up to manager to decide, if there were too few staff it became very stressful, this was very de-motivating for staff and it made people want to leave. I know this personally because I work there and the worst thing that can happen if you are working is not enough people working or it is too busy. If there are too many staff then the company is paying out more money to wages then it has to and will lose profit. So I solved this problem of the manual system with the two rota’s, these used formulae and although the V-Lookups aren’t accurate they will be once the system has been in place for a few weeks. This is because if the figures in the V-Lookups means its slightly too busy then the figures can be lowered and if the formula means its too easy it can be raised. I have solved the problem of it being scruffy and unreadable as the font is Times New Roman and it can be read by everyone, as it is the normal font that everyone uses.
Have I done all of the tasks that I set out to do?
Yes and No, I have solved some of the problems that I talked about in the Introduction and Specification. Firstly the booking form takes down the name, time, number of guests and the Phone number (but only if the booking is for over 6 guests) of the booking, the only thing that I did not do was if the booking was smoking or non-smoking. The reason for this is that the Camelot restaurant does not allow any smoking in the restaurant on Weekends but does in some certain areas on Weekdays. So when I tried to create a system or smoking on weekdays I would have had to create a separate system for the weekends and it would make the system much more complicated then it needed to be. I also completed the task of that there must be different sheets for all the days of the week and I also created a series of folders with the different months of the year so that the weekly files could be saved in them. This would be useful for the restaurant, as they would be able to take booking for a year’s time, this is useful because customers always want to book for days like Christmas and Easter, these days are usually totally booked up months before. I also solved the problem of how many chefs and waitresses are needed depending on how busy it is that day and/or time, these are shown clearly in the two rota tables. The next part of my Specification I did not do, this was that to make sure that the restaurant is not overbooked, the reason why I did not do this was mainly because of the lack of space on the sheet. Finally to test that my system worked properly I tested it on the manager at my work that is dyslexic; he read over the User manual and was able to use the system with only a little difficulty. I am confident though that after a week or two using the system he will be able to use it flawlessly.
Another way of evaluating my system is to look and check if it matches up to my performance indicators that I created in my design. It did not take more than 30 seconds to enter a booking, it took much less time, only about 15 seconds, but it does depend on the phone call as the customer may take long to say all the booking information. I will not be able to check how accurate it is as it will take a while for the V-Lookup figures to be tweaked until they are perfect and also it depends on how good the chefs are. Also a computer can not predict whether they are in a working mood or not so there are flaws with my system but these are flaws that have been with the old system as well. I have already found out that a dyslexic person can work
Problems with the new system?
There were a few problems with this system and quite a lot of improvements could be made to the system. The first problem was the data capture form itself it had not enough Validation and if I wanted to create the perfect validation checks I would have had to learnt Visual Basic much better. Incorrect bookings would be able to slip through into the table and this could really muck the system up, for example say that a booking for 7 is accidentally entered into the table as 17 then extra staff would be employed and the company would make a loss. Also as I did not do two parts of my specification there are problems, when people arrive at the restaurant on weekdays they may not have a choice to sit in smoking if it is full. Also non-smokers may have to sit in smoking if non-smoking is full, this will not be good for the Camelot’s reputation. The part of the specification was that the system would say when the restaurant is overbooked but as I did not do this then guests may have to wait in the bar for a while before they get there table, this again is not good for the restaurants reputation.
Overall I would say that my system was a partial success, the next version of this system will be much more improved now that I know were I went wrong. It is much better than the original system and accomplishes 90% of the specification, the other 10% are were most of the flaws were so I did not include them into my system
