I am to produce a ‘template’ of a working system that can be used for any month, and that will record ticket sales for both monthly productions.
In order to be able to react to the rapidly changing theatre market I plan to computerise the ticketing system for all monthly production sales. By implementing this method management reports will be produced and will provide quick, accurate and flexible management information.
The details of the ticket sales for the two productions are:
Other related data which will be in Appendix B includes:
- Ticket prices
- Profits
- Expenses
A management report that will be provided will include:
- Sales data for:
- Each production
- Both Productions together
- Total income for:
- Each production
- Both Productions together
- An appropriate ‘message’ providing which production that has the best ticket sales/income.
- Actual and expected profits displayed in actual amounts and by percentage of totals together with an appropriate ‘message’ on how these two compare.
- An overall ‘Net Profit’ taking into account the company’s expenses.
The Grand Theatre has produced an expected income for both productions. They are as follows:
The Grand Theatre has also drawn up a list of expenses for both productions. They are as follows:
The Grand Theatre will be looking for a spreadsheet that will be simple to use and one that makes processing easy to do. Processing that will be carried out will include: Calculating the number of tickets sold
Output that will be required will be included in the management report. This will be in the form of reports and graphs.
Calculations
I will be using the following formulas and lookups to aid in designing the spreadsheet:
- IF Statement
- VLookups
- =SUM
Complex Features
During the process of the spreadsheet I will be using a number of complex features. They include:
-
Macros – these will help navigate through the spreadsheet with buttons.
-
Drop down lists - these lists display all of the available choices to the user so that they can click on their preference.
-
Lookups - searches for a value in the left-most column of a table, and then returns a value in the same row from a column you specify in the table.
-
Protection - Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when the cell is selected.
-
Data Validation - This gives a customised error message if the wrong data is entered into a cell e.g. a wrong code. Restricts incorrect data input.
-
Linking Sheets - It can be convenient to link information from one sheet to another. In this way, data that is used by both sheets can be updated merely by updating the source sheet.
-
Sorting – When using a spreadsheet it is not unusual to want to change the order of the information. Generally speaking you will not want to sort the entire spreadsheet
-
Named cells - Named cells can be used like bookmarks in your workbook, and named cells can also be referenced in formulas.
There will be five sheets used in my spreadsheet:
- Menu page
- Marriage of Roberto
- Pirates of Portstewart
- Totals/Reports for both productions
- Data/Lookups for named cells/ranges
The Menu page will have buttons linking to each sheet using Macros. When a user clicks on one of these buttons they should be taken to the correct page.
The two productions will have information such as number of tickets for adults, children, students and senior citizens. The seating area will be chosen with the use of a drop down list. The payment method will also be used as a drop down list.
The forth page will be the Totals/Reports. In this page will be my overall income and profit as well as the Best Production sales and whether or not they exceeded the expected income or not.
The fifth page will be the Data/Lookups where I will have the Appendix B – Ticket Prices as well as Expected Income and Expenses. These can be used to name cells and ranges for use with Lookups and Drop down Lists.
As well as all this I will have to do a number of Graphs which will consist of bar charts and pie charts. They will give a better view of which production made the most income.