IS2022 Business Programming with Spreadsheet - Assignment: Golf Club
IS2022 Business Programming with Spreadsheet
Individual Assignment (8%)
Develop a professional error-free application using MS Excel and VBA to calculate fees and charges for a golf club.
The ISmart Golf Course (IGC) is a public golf course that requires players to take the Golf Course Ferry from the designated pier. The round-trip ferry price are as follow: $85 for regular users, $40 for junior (under 12 years of age), and $40 for senior (60 years of age and over).
IGC offers three exceptional public golf courses in Asia: the East Course, North Course, and South Course. Players must reserve a course at least one week in advance. The individual course fees are provided in Table 1. The golf course also offers a variety of golf tuition programs for beginners to advanced golfers. The tuition fees include the golf course charges, and the details can be found in Table 2. Optional equipment hire services are also available.
You may copy the fee tables to MS Excel in order to calculate fees and charges accurately. Create a button on the worksheet to initiate your application. Develop a Sub procedure in a Module with appropriate variables, or constants, and data types to calculate the total amount a golf player needs to pay, considering tuition, fees or charges. Use Input box functions (traditional input boxes, NOT Input box methods; NO userform is required) to prompt the user for mandatory information, such as reservation date, player’s full name, gender, and player status (regular junior/senior, HKID card holder or non-HKID card holder). The user should also indicate whether they want to reserve a golf course or register a lesson.
If the user wants to reserve a golf course, they will enter the corresponding code for the desired course, as listed in Table 1. If the user is booking a lesson, there is no need to reserve a golf course. Instead, they will enter appropriate tuition code from Table 2. The User may also reserve equipment by entering the corresponding hire code(s) from Table 3.
The application and input boxes should incorporate appropriate data validation and error handling mechanisms to avoid abrupt termination, and input errors or execution errors. The application should calculate the fees and display the results in a message box. The message box should display the player’s full name, a breakdown of charges and fees, and the total fees formatted with appropriate currency format, icon, title and current date.
If the user chooses to retry after viewing the result message box, the application should display all the input boxes again and repeat the process. If the user chooses to cancel, the application should terminate.
Your task is to develop a comprehensive and user-friendly application that accurately calculates fees and charges for the golf club, incorporate proper validation, error handling, and user interaction features.
Assessment criteria of the individual assignment will be based on your ability to perform the criteria above in terms of the followings:
o Write macros (sub procedures) using VBA in Microsoft Excel.
o Use appropriate data types to declare variables and/or constants.
o Useappropriateinputboxfunctionstocaptureuserinputwithdatavalidationrulesand error handling mechanisms.
IS2022 Business Programming with Spreadsheet
o Use repetition, decision and/or selection structures in VBA code.
o Use message box with appropriate prompt to display results correctly.
Save your file as IndAssign_<your Student ID number>.xlsm
Submission deadline: 14 April 2024, (Week 12 Sunday) 11:59 pm
Please check and confirm the file you submit on Canvas is correct and complete before the submission deadline.
Incomplete or late submission will result in 0 mark.
Fee Tables:
Table 1: Green Fee | ||
Regular | ||
G01 | East Course (18 holes) | $1,200 |
G02 | South/North Course (18 holes) | $980 |
G03 | South Course (9 holes) | $550 |
Senior (60 years of age and over) | ||
G04 | East Course (18 holes) | $850 |
G05 | South/North Course (18 holes) | $750 |
G06 | South Course (9 holes) | $380 |
Junior (Under 21 years of age) | ||
G07 | East Course (18 holes) | $475 |
G08 | South/North Course (18 holes) | $450 |
G09 | South Course (9 holes) | $250 |
Regular (non-HKID Card Holder) | ||
G10 | East Course (18 holes) | $1,600 |
G11 | South/North Course (18 holes) | $1,300 |
G12 | South Course (9 holes) | $800 |
Junior non-HKID Card Holder (Under 21 years of age) | ||
G13 | East Course (18 holes) | $675 |
G14 | South/North Course (18 holes) | $650 |
G15 | South Course (9 holes) | $350 |
Table 2: Golf Tuition | ||
Regular | Per Person | |
T01 | Introduction (8 hours) | $2,200 |
T02 | On Course Lesson (4 hours) | $1,200 |
T03 | Golf Pass (8 hours) | $2,500 |
T04 | Private Tuition Fee with Head Golf Professional | $4,000 |
Junior (Under 21 years of age) | Per Person | |
T05 | Junior Group Lesson | $600 |
T06 | Junior Private Tuition Fee (3 hours) | $1,000 |
Table 3: Hire Service | ||
H01 | Set of Golf Club (Premium) | $500 |
H02 | Hire Center Golf Bag Storage | $40 |
H03 | Private Locker – Annual rate* | $4,000* |
H04 | Golf Bag Carrier | $340 |
