EMPIRE STATE COLLEGE
MBA /PM Certificate Program
Assignment #6 Colorado Trip
Several years ago, Connor and Siobhan Dempsey moved to Boulder, Colorado, drawn by their love of the mountains and their interest in outdoor activities of all kinds. This interest led them to form the Mountain River Adventures center. The center began as a whitewater rafting tour provider, but quickly grew to encompass other activities, such as canoeing, hiking, camping, fishing, and rock climbing.
From the beginning, Connor and Siobhan have used computers to help them manage all aspects of their business. They are exploring using Excel to store information about clients, equipment, and types of guided tours they provide.
Help this company to develop a Colorado Trip management system.
1. Download Assignment 6 Colorado Trip workbook (Excel file) and save it as your name A6 Colorado Trip Lookup.
2. Fill in Cover Sheet.
3. On Trip Data Sheet,
Insert a new column after the Region column and label it as New Region. Create Input Data Dropdown List for Region (East, West, South and North) and fill in the data from the dropdown list.
4. Using vLookup to fill in Commission Rate (Set up a commission table first as shown below).
5. Calculate total commission amount.
6. Using hLookup to fill in Salary (Set up a Salary table first as shown below).
7. Calculate total payment (Commission and Salary).
8. Determine the minimum, average, and maximum payment (Lowest, Average, and Highest), respectively.
Make sure to save your work
9. On Trip Fee Sheet: Using hLookup to set up only one Lookup table. After you finish the following processes, when you enter a trip ID, the rest of information filled in the table automatically:
a. Using Trip ID to lookup each trip information.
b. Calculate field Trip Fee that times Fee and Person.
c. Use the If function to reduce TripFee by 10% if the people is greater than or equal to ten (10).
d. Calculate Total trip fee.
