The aim of my project is to make the task of the allocation of seats in a theatre in a much more efficient manner than writing down on a piece of paper,

Input E3

Data requirements

The requirements are the show that is being shown, whether is being performed by amateurs or professionals, the amount of adults/children/concessions that are watching the performance, the fixed costs and their respective percentages. These will then be used in the grand total to show whether Mr. Brown has made a profit or a loss.

Data collection

The requirements which Mr. Brown requested were that the spreadsheet be easy to modify in case of any change in price for any calculation. That it is easy to understand from "buttons" and that the seating be colour coded to understand easily where people sit. He asked that the sheet be easy enough to use so that he could modify it quickly and easily so that he could do so while a customer is talking to him on the phone or in person.

Data entry

All data will be entered manually using the keyboard to type in new prices, and the mouse will be used to control drop down menus, such as where people can sit, there is the option of a keyboard, or the use of the macro button which will automatically change the box selected with the c, a, n, which relates to whether the person sitting there is an adult, child or concession, and the spreadsheet will change according to the input.

Error checking

There are several aspects of error checking in my sheet. The use of drop down menus prevents the user from incorrectly typing in the wrong data. Also the use of cell protection on the cells which contain the data for the calculations, such as the prices for the various shows, are protected with cell protection which prevents the user from incorrectly editing the data inside the cells. In the event that the user wants to change the data in the situation of a change in cost to put on a show, or some other change in price or projected profit, the user can easily switch off the cell protection, edit the data, and then switch cell protection back on.

Data manipulation/processing E4/E5

Output E6

The output will mainly be via the screen, as this sheet will be used by Mr. Brown to keep track of profit and loss. Mr. Brown will likely keep a copy of the success/loss of each show on his hard disk if he wanted to look at it a later date which can be done easily using Excels “save as” feature. Mr. Brown can also print out the spreadsheet to keep a safe copy of the information, or to store it for future reference, but it is usually easier to save it to a floppy disk. Output via a laser printer would probably be of better quality and faster, but costs are a lot higher, a normal ink jet printer for Mr. Brown would easily suffice.

        The visual appearance of a model is important as it will contribute to Mr. Browns understanding and

ease of use when entering data. I will implement a formula for the sheet so that when Mr. Brown enters in “c, a, or n” it will automatically change colour to a pre-determined format (e.g. a=red, c=green n=blue)so that it will be easy for Mr. Brown to understand. The borders function in Excel will allow me to put bolder borders around important areas of the spread sheet, for example I will put one around the area that will contain the prices of shows, which will contribute to the overall visual quality, which important to Mr. Browns usage of it.

System Flowcharts E6

Use Model

Backup & security ?

Although there is nothing confidential about the spreadsheet, it is wise to keep a backup copy of the data in the case of a virus attack, crash, or human error. I have recommended Mr. Brown to backup his work on a floppy disk every week or so, he could also do this with a CD RW, which store a lot more data, but cost a lot more and would probably be a lot more complex for Mr. Brown to understand. I also said that if he had a printer to print out the data every so often so that the data would not always be required to be digital.  

---

                                                Chapter 3: Design

Initial design ?

See following page.

Sub-tasks D

The easiest way to complete a complex task such as this is to break it down in to several sub-tasks, allowing me to undertake smaller, more manageable tasks.

These are

• The creation of the initial design
• Verification with Mr. Brown to check if this is what he wanted, and if things require to be changed make notes on what they are, and how to correct them.
• Use notes made in the previous sub-task to create Final Design.

1. Chose a layout in accordance to Mr. Brown’s recommendations.
2. Indicate where formulas will go,
3. Indicate position of macro (buttons in this case) and their appropriate names.
4. Indicate possible areas for visual effects such as bold titles, colour, etc.

• From the final design:

1. Begin creation of seating area, which will allow Mr. Brown to input where people are sitting and will allow the spreadsheet to calculate the income from various seating arrangements.
2. Create areas for input of pricing of seats, different costs for different performances and fixed costs.
3. Use the above data to create the formulas which will be used for the calculation.
4. Implement drop down menus.
5. Record macros.
6. Test the spreadsheet.
7. Correct errors.

28th November 2003

Epsom Playhouse

High street 56

Epsom

KT17 9HW

Dear Michael Pittas

I have reviewed your design and I am overall pleased with the result of the first draft. There are, however some things in which I wish you to add:

• A more understandable layout, so that I can easily find the piece of information I am looking for without having to search the whole sheet.
• The fixed cost for the shows are in red so that I know where they are.
• Boxes beside each of the seating separations that contain the individual prices of each class of person sitting there.

With this I believe that the spreadsheet model should be able to be used without hassle and worry of doing something wrong.

Yours,

Mr. Brown

---

Final design ?

See following page.

Test plan F

---

Chapter 4: Implementation

Solution being corrected

The highlighted cells have too small a space to display the information inside them, so Excel will display #######, indicating that the cell has to be resized. To do this we simply hold the shift button and click on the AF and AG bars and drag them to the appropriate size.

The highlighted cells have incorrect decimal places, which means that the pence in the price will not be displayed correctly. To correct this we simply click on the "increase decimal" button on the toolbar until we have the correct amount of decimal places.

In the picture above, the formula in cell AJ29 is incorrectly configured so that the formula adds an incorrect cell, (coloured green) AK21, instead of AJ21. To correct this we must edit the numbers in the formula from

=SUM(AJ26,AK21,AJ16) to =SUM(AJ26,AJ21,AJ16), this will then rectify the problem.

 

The selected cell has is not showing its value as money in the format £xx.xx, instead it is showing it as a standard number with decimals. To rectify this we can simply click on the money symbol in the toolbar,

which will convert the number to the appropriate output.

         

Finished solution H

Spreadsheet showing data

See following page.

Spreadsheet showing formulas

See following page.

Macro code

Sub Button2_Click()

Selection = ""

End Sub

Sub Button3_Click()

Selection = "a"

End Sub

Sub Button4_Click()

Selection = "c"

End Sub

Sub Button5_Click()

Selection = "n"

End Sub

Testing F

 

---

Chapter 5: Evaluation H

Comparison of solution with original objectives

---

28th November 2003

Epsom Playhouse

High street 56

Epsom

KT17 9HW

Dear Michael Pittas

Thank you for sending me your spreadsheet I have evaluated your sheet and have been testing it and have found to be an extremely useful asset in helping the calculation of costs and profits from my theatre. If find it extremely user friendly as it allow me to select from drop down menus the show and whether it is professional or amateur. The buttons to quickly and simply input seating arrangements is very useful than putting it in manually. I am pleased to see that you have used a tool to protect the data in sheet from being accidentally edited, however, I would like your to implement a button (like the adult, child, concession button) which will quickly turn off cell protection.

Yours sincerely,

Mr Brown

---

Response to customer feedback H

Mr. Brown suggested the introduction of a macro for quickly turning off cell protection. This could easily be implemented by simply click “record new macro” and simply clicking “unprotect worksheet”. We could then save it as a button on the toolbar which would allow Mr. Brown to use it as easily as the “child, adult, concession, clear selected” buttons.

Suggestions for further enhancements H

Although the spreadsheet can be used as a simple guide to how much profit or loss Mr. Brown has achieved, it probably would be impractical to use in a real situation. The spreadsheet cannot change prices for each individual person, if a concession paid a different price than another than the whole spreadsheet would be inaccurate for calculating it, as there would be no way of implementing that on the spot. To do so would probably require imputing the price in manually, which Mr. Brown would be unable to do so on the spot. Also, if Mr. Brown were to introduce a new show into his theatres he would be unable to implement it into his spreadsheet, and thus may have to try adding it to the fixed costs cell instead, and leaving the drop-lists in a position where the show costs zero.

        Overall the spreadsheet would probably be useful as a extremely rough guide to how much Mr. Brown had made or lost, but may be slightly impractical for anything more exact.