He wanted to know where people were sitting for simple marketing techniques and possible expansion in future, the seating plan was to be button operated to save large amounts of time entering letters into cells. The spreadsheet shall greatly increase the ease and speed of Mr Chapman's job, it also allows him to show how many more seats he has to sell before he makes the desired profit or not.
Alternative strategies for solving the problem
Non-ICT solution
A non-ICT solution would be to collect the tickets as you go into the performance, these tickets would then be counted at the end of each show and the amounts would be added on a calculator. This total could then be added to the end of night till receipts for programme sales, confectionery and bar sales, this would be the total profit which would then be taken away from the price of the show and fixed costs with a calculator to crate the Show Profit. The downfall of this method is that it takes a long time, it is easy to make mistakes and you must pay people to do it.
ICT solution
Another ICT solution would be to do a word processed document, word processed documents tables can do calculations but there are no specified cells and all data would be entered manually this would take a long time and all the formula's would have to re-entered for each show. There would be no seating plan or buttons for ease of use and the show prices would have to entered manually instead of from drop down menu's
Reasons for selecting the chosen approach
The spreadsheet I used (Excel) overcomes all of the problems that Mr Chapman was having, I could also use buttons to enter data into a seating plan to make the interface very user-friendly for the person operating the system. This solution also used formulas that I could fix so that they could only changed with a password. Excel allowed me to use colours and a very easy on the eye design. I also selected the approach because I am an experienced Excel user; this allowed me to complete the job promptly and without major problems.
Details of the calculations
> The Spreadsheet will greatly increase the accuracy of the results.
> More user-friendly
> Simplicity
> Quick Answer
> Easy to read results
Information Given
Fixed cost £500
Programme Sales £0.60 a programme, 45% of customers buy a programme
Confectionary Sales £0.85 a child and £0.45 per adult/concession
Bar Profit £1.10 an adult, £0.70 a concession
Front Stalls Rear Stalls
Balcony
Question 1)
Musical Theatre (amateur)
Front Stalls sold out 50% Adult 50%Child
Rear Stalls 3 complete rows sold to children, plus 10 adults and 10 concessions
Question 2)
How many seats need to be sold before a profit is achieved
The objectives of the solution
1) Create a spreadsheet to work out if a show was going to make a profit or loss using basic statistics from previous shows.
2) Make the Spreadsheet easy to use using data validation drop down lists and macro buttons to select the desired type of ticket.
3) Colour coded to show which data can be edited by the person operating the spreadsheet and which data is fixed to avoid formulas to be edited.
4) The spreadsheet needs to be quicker and easier to do than make all the calculations by hand, and to a greater degree of accuracy.
5) The spreadsheet may be altered by anyone Mr Chapman trusts with his desired password that locks any important formulas
6) To show seating plan that can be easily read and understood for future projects and marketing schemes to promote the theatre
7) To be able to be updated in the future and not have to be totally re-written.
Chapter 2: Analysis
Hardware & Software
Hardware
Reasons for choice of hardware
Software
Reasons for choice of software
Input
Data requirements
Data collection
Data entry
Error checking
Data manipulation/processing
(only include the ones you have used!)
Output
System Flowcharts
Backup & security
Chapter 3: Design
Initial design
Customer feedback
Final design
Test plan
Chapter 4: Implementation
Solution being developed
Finished solution
Spreadsheet showing data
Spreadsheet showing formulas
Macro code
Testing
Chapter 5: Evaluation
Comparison of solution with original objectives
Customer feedback
Response to customer feedback
Suggestions for further enhancements