The Ticket Box - designing a new computer system.

Proposal - The Excel™ System:

The Ticket Box Requirements:

The system using Microsoft Excel™ will need to consist of four main sheets, which the customer will see. The customer will need a presentable interface, which is fairly easy to use. As we are unaware of the customer’s knowledge or experience with Microsoft Excel™ or computers, the system should be very simple to use and be fairly self-explanatory. To avoid limitations we will be purchasing a new computer system, as The Ticket Box have no previous computer equipment.
Customers will need the system to do these various tasks:

• Select all events available and the date they wish to visit the venue, they should clearly be able to see ticket availability in the various seating arraignments in the arena, with easily viewable prices on the date of the event being prices vary on peak days.
• Process their order by entering address and payment details.
• To be able to view and print the receipt as proof of purchase and to ensure all required information is correct. If not they can quickly contact the company via phone.
• Ensuring their data is logged safely and is not viewable to other customers or persons using the system.

Staff will need the system to:

• Process all the pending orders and be able to print out their address once payment has been received, so the tickets can be shipped.
• Add current events and enter seat availability for each section of the venue as well as allocating the price for each seating section.
• Easily search through the current event list easily to update price changes or availability changes due to non-successfully processed orders.
• Add/Remove dates to show when events will be held, as well as selecting one of the available peak categories (ranging from extreme peak to off-peak).

• Need to process all the information and the orders into the overall storage system.

Administrators will need the system to:

• Update special offers.
• Change the rates for delivery.
• Change special offers, bulk orders, change child discounts and rates changes for varying peak dates. This will all need to be automatically updated instantly to be in ready use for the next customer.
• Able to view all the processed and pending orders.  

The Ticket Box Requirements - Continued:

Administrators will need the system to:

• Show profit made for the company with easy access to profit graphs.
• Update staff and update the system and its user interface.

Why an Excel System?

        After looking at the task in hand many calculations will need to be made to create such a system that can handle these various tasks.  The ideal software to create this would be Microsoft Excel™, it can quickly calculate equations over a variation of spreadsheets this will help to calculate order prices and discounts. Macros can be used to transfer information across sheets which will help me create an easy user interface with the various buttons which will run a macro we pressed to carry out the required task instantly. For creating the nice and easily presentable user interface Microsoft Excel™ offers borders, shading, picture inclusion, various fonts, colours, drop down bars, buttons, tick boxes, radio buttons, graphs (and much more). Overall the Microsoft Excel™ system will allow all the tasks (mentioned above) to quickly and easily be completed straight forwardly without any currently known limitations.

        The Microsoft Excel™ system is a great improvement from the old paper system, which was in use. The old system was a much slower pressurised way of processing orders. If a single mistake was made, the administrators and staff would need to make lots of changes and recalculations which is seemed to be a very hard and stressful method, they may also need to need to refund customers due to mistakes such as double bookings. This system will be quick, easy and accurate and also stress free. If a single mistake is inputted into the Microsoft Excel™ it will quickly re-calculate everything. Using a computer system may also save a lot of office space, without the need to keep paper records, filing cabinets and paper stocks will not need to be stored. This system will allow administrators and staff to work in a relaxed environment.

        Overall the new Microsoft Excel™ system will be able to quickly book orders, create logs and can show various profits for the company. The system will make life easier for every aspect and department of the company.

Improvements:

        This new system will improve the current situation in many ways. Administrators and Staff will be able to work much quicker and because the new system is less stressful it means that they will be able to work quicker and will be much more efficient for the company.  Once an Administrator makes an error, Microsoft Excel™ will be automatically able to show feedback for the Administrator and notify him of the error. With the old system errors may not be known until two people show up for the same seat. This will prevent administrators carrying on to create graphs with errors and will help them solve the problem as it arises.

        This system will also improve live for the staff, previous paper slips may be lost, but with this new system it can give a confirmation to show that the information was successfully inputted, this can be done by using simple macros.

        This system will finally also help the customers instead of ringing and waiting for a customer service advisor to become available they can view all prices and discounts. It will reduce errors by service advisors getting confused and giving them the wrong prices only to find out, once their credit card statement is received. The company in general will be improved and greatly profit from the requirement of fewer staffs, less office space and phone equipment.  A separate department will not be required staff should be free and available to answer phones.

Detailed System Creation:

Formulas & Calculations:

Firstly these following formulas will be used; this table explains in brief

1. The formula (required formula fields).
2. What the formula does.
3. How I will incorporate the formula into my project.

Key:

? = A Cell Reference – E.g. A2

?>? = A Range of Cells - E.g. A2:B3 (this will include cells A2, B2, A3 and B3)

Calculations will be used to calculate such things as ticket totals and price totals.

*If formulas will be used in conjunction with validation (described below) to prevent misuse of the system. For example the maximum amount of tickets ever available in the front block is 600. Validation does not include a sum of over two cells (child and adult), so instead each cell is maxed to 600 and under, but therefore 1200 tickets will theoretically be available to purchase. So an, if formula will notify you if over 600 (or the current availability) amount is surpassed. And if then is still processed a void receipt and an error log will also be created.

Design:

The design of this project will have to be simple and easy to use, the customer may not have any computing knowledge and will therefore instructions must be given to the user to reduce errors. I will do this by firstly using shaded boxes to show where the customer is required to enter the required data. Secondly I will use drop down boxes on dates and events this will reduce many errors if the customer was to enter the required event as misspelling often occurs.  

Secondly macros will be used in the form of buttons; this will tell the customer what the macro does and how to proceed to the next stage of the order.  

Detailed System Creation - Continued:

Design:

The interface will also need to be very presentable as it is a well known company, I intend to use graphics of famous celebrity’s or artists to make the system more user friendly.

The company’s logo will be recreated using vector graphic software such as Jasc’s PaintShop Pro 7.  The staff and administrator sheets will also be well presented for the ease of the employees.

Macros, Security & Error Reduction:

A macro is set of instructions pre-recorded/written by the user and once ran, the macro will carry out the instructions stored. For Microsoft Excel™ the macros I use will be written in Visual Basic script. However if the option is unavailable in Microsoft Excel™ and error will occur to these prevent errors, I will make macros visible only when there valid (the show all logs button on advanced filters only when all results are not currently shown). I will achieve this by either shifting the button 1000’s of pixels left or making the show logs button at the bottom of the list, so it’s not visible on screen. Macros will also be used to transfer information across files and to switch sheets.

Security measures will need to be used so customers or people accessing the system will not be able to view pending orders in which they can get access to other people’s credit card numbers and address information. To do this I will log this vital information into another password protected sheet however the password may be able to be viewed if some one can access the macro script so the sheet using the macro will also be locked to ensure full security.

Validation, Sheet Locking & Verification:

Validation is where the customer is restricted to what he or she may enter. This will be used in a variation of cells; firstly the most seats available in each seating section (also mentioned above under “Formulas and Calculations”) will be restricted to what the user can enter. For example a maximum availability of 600 is used for the front seating block and therefore the user will not be bale to enter 601. It will also be used to ensure credit card details are 16 digits long and in conjunction with an, if formula to check weather the card’s expiry dates hasn’t already passed.

Verification will be checked with if formulas in situations such as the one described above.

To make sure un-wanted changes are not made to the sheets, sheets will be locked using Microsoft Excel™. Various sheets such as staff sheets will be required to enter a password to unlock the sheet for updates or changes can be made.

Drawbacks or Changes:

        The equipment for this system is all new so there are not any drawbacks or limitations to the hardware. Microsoft Excel™ will be able to do all the required tasks efficiently. However there are some limitations and problems that may arise. Firstly the system may not be 100% secure, however this is true with any system and maybe be vulnerable to fraud.  Secondly the Ticket Box would benefit from merchant accounts for instantly accepting payments; however you can not link Microsoft Excel™ to a secure online server. Staff and Administrators may need to run a 20/45 minute basic training course.

What needs to be inputted (Customer):

        The data which needs to be inputted will be directly from the customers. The data that is required to be inputted will be:

1. The event the customer wishes to see.
2. The date the customer intends to visit the arena.
3. The required tickets for each seating block stating each child and adult.
4. The customer’s address must be entered as this is where the tickets will arrive.
5. The customer’s credit card number, type and expiry date must all be entered.

All this data is essential for logging as is will then go on to produce various charts, graphs and help track fraudulent transactions.

        Data will be input every time a new customer uses this system. With customers it is expected that regular data input into the system will occur.

What will be outputted (Customer):

The data outputted to the customer, corresponding to the above list will be:

1. The prices for the event the customer wishes to visit.
2. The ticket availability in the arena, additional changes to price per ticket depending on date of visit.
3. Overall prices after such things as special offers and bulk/child discounts have been deducted.
4. Confirmation of logging of address.
5. A final printable receipt which will then be kept by the customer before the system returns to the first page ready for the next user.

Outputs that the user will not be able to see will include:

1. The logging of all there details, and event information (used for processing).
2. The update in event availability once there seats are booked.
3. The clearing of all previously entered data once the order is complete.

System Flow: