Assignment Title: "Island Breeze" Resort Management System
Scenario:
You are the Assistant Manager of "Island Breeze," a boutique resort. You are required to use a
spreadsheet to track guest bookings, calculate revenue, and analyze occupancy trends for the month
of July.
Task 1: Data Entry and Formatting (4 Marks)
1. Worksheet Setup: Open a spreadsheet application and create a new workbook titled
Island_Breeze_SBA_YourName.
2. Data Entry: On "Sheet 1" (rename to Bookings), enter the following data for at least 10 guests:
o Guest Name, Room Type (Standard, Deluxe, Suite), Arrival Date, Departure Date, and
Number of Nights.
3. Formatting:
o Merge and center the title "Island Breeze July Bookings" across all columns.
o Apply Bold and a background fill color to the column headers.
o Format all currency columns (Rate, Total Cost, Deposit) to Currency with 2 decimal
places.
o Set the Arrival Date and Departure Date columns to a consistent Date format (e.g.,
DD/MM/YYYY).
Task 2: Calculations and Functions (6 Marks)
1. VLOOKUP: Create a second sheet named Rates. In this sheet, create a small table showing
the nightly rate for each Room Type. Use a VLOOKUP function in the Bookings sheet to
automatically retrieve the nightly rate for each guest based on their "Room Type".
2. Arithmetic Formulae: Create a formula to calculate the Total Cost (Number of Nights *
Nightly Rate).
3. Absolute Referencing: The resort charges a 10% Government Tax on all bookings. Place the
tax rate (0.10) in a single cell (e.g., cell J1). Use an Absolute Cell Reference (e.g., $J$1) in
your formula to calculate the tax for each guest.
4. IF Function: The resort offers a "Loyalty Discount." Create a column for "Discount." Use an IF
function to award a 15% discount if the guest is staying for more than 5 nights; otherwise, the
discount is 0.
5. Summary Functions: Use the SUM, AVERAGE, MAX, and MIN functions at the bottom of
your data to show the Total Revenue, Average Nights Stayed, Maximum Bill, and Minimum Bill
respectively.
Task 3: Data Management and Filtering (6 Marks)
1. Sorting: Perform a Two-Level Sort on your data. First, sort the list by Room Type in
ascending order, and then by Guest Name in ascending order.
2. Advanced Filtering/Extraction:
o Define a Criteria Range on your sheet to extract all guests who stayed in a "Suite"
AND paid a Total Cost of more than $1000.
o Extract the results to a separate area on the same worksheet, ensuring the heading
"High Value Suite Guests" is clearly visible.
3. Counting: Use the COUNTIF function to determine the total number of "Deluxe" rooms
booked.
Task 4: Visualization and Analysis (4 Marks)
1. Chart Creation: Create a Column Chart (Bar Chart) that compares the Total Revenue
generated by each Room Type.
2. Chart Formatting: Ensure the chart includes:
o A descriptive Title (e.g., "Revenue Analysis by Room Category").
o Labelled Axes (X-axis: Room Type, Y-axis: Revenue in $).
o A Legend if more than one data series is used.
3. "What-If" Analysis: Copy your original data to a new sheet named Scenario. Change the
Government Tax rate from 10% to 15%. Note the impact on the Total Revenue and describe
the result in a brief text box below the data.
Deliverables (Submission Requirements)
To receive full marks, you must submit:
1. The Excel/Spreadsheet File containing all worksheets.
2. Formula Sheets: A printout or PDF of the worksheet in Formula View (showing all functions
and formulas used, ensuring columns are wide enough to read the full formula).
3. Charts: The revenue chart placed on its own sheet or clearly visible alongside the data.