(Solved): WPC 300 Assignment - Assignment 3 (UPDATED)...

WPC 300 (Hybrid Course)

Assignment-3

Maximum points: 25

Student Name:  _[Mary Alfansha ]___________________ Class Day &Time:__[Monday & 9.15 AM]_

The Excel file Graduates’ Inn Guest provides a list of customers, rooms they occupied, arrival and departure dates, number of occupants, and daily rate for a small bed-and-breakfast inn during one month. Room rates are the same for one or two guests; however, additional guests must pay an additional amount (based on the room type as shown in the table below) per person per day for meals and other miscellaneous expenses. Guests staying for seven days or more receive a 10% discount (applied to revenue per party transaction).

Deliverable: Modified excel file with your name and student ID on it.

Modify the spreadsheet (add one column for each question from 1 to 3) to calculate:

1. The number of days that each party stayed at the inn [5 points]
2. Daily rates considering the number of guest(s), Hint: Use VLookUp function. You will need to create an additional cost table using the data available to you. [5 points]
3. Revenue collected from each party for their total stay at the guest house [2.5 points] and total revenue generated for the month from all guests [2.5 points]

Add one additional sheet to the spreadsheet to answer the following questions.

4. Use the SUMIF() function to calculate the revenue collected from each room type for the whole month. [5 pts]
5. Show a pie chart to display the revenue breakdown from Q(4) [2.5 points].  Which room type generated highest revenue and how much revenue? [2.5 points]