Project: Eden Hotels & Resorts Group: Sales Incentive Plan (Java), Revenue Management (Alteryx & Excel), Database Design and Analysis (SQL)

Category:

• Hotel

• Resort

• Revenue

• Hospitality

• Travel

1/ Eden Hotels & Resorts Group's Revenue Management:

A. Tools: Alteryx

BG10

1. Overview

2. Creating Days Before Arrival

3. Creating Days Before Arrival Groups

4. Summarizing by DBA Groups

5. Calculating ROH, % ROH, RN OTB, % RN OTB

6. Calculating Total ROH, Total RN OTB

7. Union

8. Final Touch

BG11

1. Overview

2. Creating DBA

3. Creating DBA Groups and DBA Groups Order

4. Group by DBA Groups

5. Multi Row Formula for RN ROH PU and RN OTB PU

6. Final result

B. Tools: Excel

Table of Contents:

1. Calculating KPIs by Revenues

2. Calculating KPIs by Segments

3. Calculating Monthly and Year-to-Date KPIs by Segments

4. Aggregate data from other sheets to have a Pivot table

5. Calculate Inventory, Room Night, Occupancy, Average Daily Rate, Room Revenue, Room Revenue Per Available Room

6. Create reports from Pivot tables

7. Draw charts

8. Conditional Formatting

9. Daily Pickup Report

10. Booking Window (Lead Time) Analysis & Visualization

11. Booking Pace and Booking Curve Analysis & Visualization

12. Final Reports of 3 Eden Hotels

2/ Database Design and Analysis

Language: SQL

Introduction:

Eden Hotel Group is a new player in the luxury hotel industry worldwide, based in Toronto, Canada. It has different brands in North America providing luxury 5-star accommodation, restaraunt's food and berage service and banquet catering. This new hotel group has been expanding further in Canada (reached 20 properties), the US (15), Mexico (8) and recently in West Europe, starting with City Hotels in Paris, France and Lausanne, Switzerland. Due to its growing brands, properties, employees, guests, customers and transactions, it requires a well-developed database to manage its business.

Tables of Database:

Hotel_Brand:

ID	CITY_HOTEL_ID	EMPLOYEE_ID

City_Hotel:

CITY_HOTEL_ID	CITY_HOTEL_NAME	CITY_HOTEL_BRAND	CITY_HOTE_ADDRESS

Employee:

EMPLOYEE_ID	EMPLOYEE_NAME	DATE_OF_BIRTH	CITY_HOTEL_ID	TITLE

Services:

SERVICE_ID	SERVICE_NAME	PRICE	SERVICE_TYPE

Customer:

CUSTOMER_ID	CUSTOMER_NAME	CUSTOMER_DOB	CUSTOMER_COMPANY	CUSTOMER_CREDIT_CARD	CUSTOMER_ADDRESS	TRANSACTION_ID	CUSTOMER_BOOKING_ID

Guest:

GUEST_ID	GUEST_NAME	GUEST_DOB	GUEST_COMPANY	GUEST_CREDIT_CARD	GUEST_ADDRESS	TRANSACTION_ID	CUSTOMER_BOOKING_ID

Supplier:

SUPPLIER_ID	SUPPLIER_NAME	PURCHASE_ORDER	PURCHASE_TYPE

Orders:

ORDER_ID	SUPPLIER_ID	CUSTOMER_ID	PRODUCT_ID	PRIMARY KEY

Tables of Content:

• Creating tables

CREATE DATABASE Eden_Hotel_Group_Database;
use Eden_Hotel_Group_Database;
CREATE TABLE Hotel_Brand
 ( ID NUMERIC CONSTRAINT ID_NN NOT NULL,
CITY_HOTEL_ID NUMERIC (6,0),
EMPLOYEE_ID NUMERIC (6,0),
PRIMARY KEY (ID),
 );

CREATE TABLE City_Hotel
( CITY_HOTEL_ID NUMERIC (6,0) CONSTRAINT CITY_HOTEL_ID_NN NOT NULL ,
 CITY_HOTEL_NAME VARCHAR (30),
 CITY_HOTEL_BRAND VARCHAR (30),
 CITY_HOTEL_ADDRESS VARCHAR (40),
 PRIMARY KEY (CITY_HOTEL_ID),
 );

CREATE TABLE Employee
 ( EMPLOYEE_ID NUMERIC (6,0) CONSTRAINT EMPLOYEE_ID_NN NOT NULL,
EMPLOYEE_NAME VARCHAR (30),
DATE_OF_BIRTH VARCHAR (35),
CITY_HOTEL_ID NUMERIC (6,0),
TITLE VARCHAR(50),
PRIMARY KEY (EMPLOYEE_ID),
 );

CREATE TABLE Services
( SERVICE_ID NUMERIC (10,0) CONSTRAINT SERVICE_ID_NN NOT NULL,
 SERVICES_NAME VARCHAR (30),
 SERVICES_PRICE NUMERIC (6,0),
 SERVICES_TYPE VARCHAR (15),
 PRIMARY KEY (SERVICE_ID),
 );

CREATE TABLE Customer
( CUSTOMER_ID NUMERIC (6,0) CONSTRAINT CUSTOMER_ID_NN NOT NULL,
 CUSTOMER_NAME VARCHAR (30),
 CUSTOMER_DOB VARCHAR (30),
 CUSTOMER_COMPANY VARCHAR (30),
 CUSTOMER_CREDIT_CARD NUMERIC (20),
 CUSTOMER_ADDRESS VARCHAR (40),
 TRANSACTION_ID NUMERIC (6,0),
 CUSTOMER_BOOKING_ID NUMERIC (10),
PRIMARY KEY (CUSTOMER_ID)
);

CREATE TABLE Guest
( GUEST_ID NUMERIC (6,0) CONSTRAINT GUEST_ID_NN NOT NULL,
 GUEST_NAME VARCHAR (30),
 GUEST_DOB VARCHAR (30),
 GUEST_COMPANY VARCHAR (30),
 GUEST_CREDIT_CARD NUMERIC (20),
 GUEST_ADDRESS VARCHAR (40),
 TRANSACTION_ID NUMERIC (6,0),
 CUSTOMER_BOOKING_ID NUMERIC (10), 
PRIMARY KEY (GUEST_ID)
);

CREATE TABLE Supplier
( SUPPLIER_ID NUMERIC (6,0) CONSTRAINT SUPPLIER_ID_NN NOT NULL,
 SUPPLIER_NAME VARCHAR (20),
PURCHASE_ORDER NUMERIC (10,0),
PURCHASE_TYPE VARCHAR (20),
PRIMARY KEY (SUPPLIER_ID),
);

CREATE TABLE Orders
( ORDER_ID NUMERIC (6,0) CONSTRAINT ORDER_ID_NN NOT NULL,
 SUPPLIER_ID NUMERIC (6,0),
CITY_HOTEL_ID NUMERIC (6,0),
PRODUCT_ID NUMERIC(10,0),
PRIMARY KEY (ORDER_ID),
FOREIGN KEY (SUPPLIER_ID) REFERENCES Supplier(SUPPLIER_ID),
FOREIGN KEY (CITY_HOTEL_ID) REFERENCES City_Hotel(CITY_HOTEL_ID),
FOREIGN KEY (SERVICE_ID) REFERENCES Services(SERVICE_ID),
);

ALTER TABLE Hotel_Brand
ADD FOREIGN KEY (EMPLOYEE_ID) REFERENCES Employee(EMPLOYEE_ID);

ALTER TABLE City_Hotel
ADD FOREIGN KEY (PRODUCT_ID) REFERENCES Product(PRODUCT_ID);

ALTER TABLE Employee
ADD FOREIGN KEY (CITY_HOTEL_ID) REFERENCES City_Hotel(CITY_HOTEL_ID);

• Inserting data into database

USE Eden_Hotel_Group_Database
---Insert values to Customer---
INSERT INTO Customer (CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_DOB, CUSTOMER_COMPANY, CUSTOMER_CREDIT_CARD, CUSTOMER_ADDRESS, TRANSACTION_ID, CUSTOMER_BOOKING_ID)
VALUES (007,'Liam', 01/01/1990, 'NASA', 1234567812345678, 'Washington DC', 121212, 232345);

-------------------------------
---Insert values to Service---
INSERT INTO Services (SERVICE_ID, SERVICES_NAME, PRICE, SERVICES_TYPE) 
VALUES (001, 'Eden Restaurant', 400, 'Dining');

---Insert values to Supplier---
INSERT INTO Supplier (SUPPLIER_ID, SUPPLIER_NAME, PURCHASE_ORDER, PURCHASE_TYPE) 
VALUES (909, 'Douglas', 005, 'Rooms');

--Insert values to City Hotel
INSERT INTO Store (CITY_HOTEL_ID, CITY_HOTEL_NAME, CITY_HOTEL_BRAND, CITY_HOTEL_ADDRESS) 
VALUES (002,'Eden Garden Hotel by the Pacific Bay', 'Eden Garden', 'Vancouver', 002);

--Insert values to Employee
INSERT INTO Employee (EMPLOYEE_ID, EMPLOYEE_NAME, DATE_OF_BIRTH, CITY_HOTEL_ID, TITLE)
values (007, 'Simon', 1997/07/07, 001, 'Director of Sales and Marketing');

--Insert values to Hotel_Brand
INSERT INTO Hotel_Brand (ID, CITY_HOTEL_ID, EMPLOYEE_ID) 
VALUES (001, 001, 007);

--Insert values to Orders
INSERT INTO Orders (ORDER_ID, SUPPLIER_ID, CITY_HOTEL_ID, SERVICE_ID) 
VALUES (090, 003, 003, 202);

3/ Reporting the Eden Hotels & Resorts Group' Sales Incentive Plan for the Sales & Marketing department

Language: Java

Introduction:

My mission is to create a Java program for the Director of Sales and Marketing of a Hotels & Resorts Group. The system must store information of each Salesperson and assign them to incentive scheme based on their gained revenue per month. The system must prompt the user to input each Salesperson’s name, revenues of the sales categories they completed each month. Then, the system must calculate their Monthly Sales Revenue Ratio and use to assign them to respective rewarded sales incentives.

System Requirement:

These requirements are created by me and inspired by my recent Java project at Humber College, Toronto, ON, Canada and my previous work experience in the luxury hotel industry.

/*
 * Name: Phuong Dai Ngo (Liam Ngo)
 * Date: May 03, 2021
 * This program helps user input and check password, number of Salespersons, Sales Revenues by Categories, calculate Average Sales Revenue, print reports
 */
import java.util.Scanner;
public class JavaProjectEdenHotelsResorts {
    /* I: String
     * P: Invoking other specific methods, calculate Sales Revenue for each Salesperson and inserting each into matrix
     * O: void (not returning anything) so there is no output
     */
    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        System.out.println("----------Welcome in Eden Hotels & Resorts----------\n");
        System.out.print("\nEnter the Target Revenue of this month: ");
        int monthlyTargetRevenue = input.nextInt();

        int attempt = 0; // Checking password:
        while (attempt < 3) {
            System.out.print("\nEnter your password: ");
            String password = input.next();
            if (checkPassword(password) == true) {
                System.out.println("\nValid Password\n");
                break;
            } else {
                System.out.println("\nInvalid Password\n");
                attempt++;
            }
        }
        if (attempt == 3) {
            System.out.println("\nMaximum number of attempts exceeded\n");
            System.exit(0); // end the program if exceeding 3 attempts
        }
        // Create two 1D-array to store Salesperson name, number of Salesperson per Incentive Scheme level, Salespersons' total revenue and revenue ratio
        String[] listSalesPerson = new String [enterNumSalesPersons(input)]; // Name List should be in a separate List // check Number of Salesperson // set string length by numSalesperson --> this list contains String values
        double [][] matrixRevenue = new double[listSalesPerson.length][7]; // Enter Revenue and Calculate Total Revenue, Revenue Ratio // create matrix containing Salesperson 's revenues including 6 columns for each categorical revenue, 7th column for total sales revenue // not use enterNumSalesperson() to avoid invoke that method and enter multiple times
        int [] listIncentiveScheme = new int[4]; // Select School

        for (int i = 0; i < matrixRevenue.length; i++) {
            System.out.print("\nEnter Salesperson's name: ");
            listSalesPerson[i] = input.next();
            matrixRevenue = enterRevenue(i, matrixRevenue, "Banquet", 0, input); // added the 5th parameter (Scanner input) to this enterRevenue to generate inputting without having another line of Scanner
            matrixRevenue = enterRevenue(i, matrixRevenue, "Meeting", 1, input);
            matrixRevenue = enterRevenue(i, matrixRevenue, "MICE", 2, input);
            matrixRevenue = enterRevenue(i, matrixRevenue, "Room", 3, input);
            matrixRevenue = enterRevenue(i, matrixRevenue, "F&B", 4, input);
            matrixRevenue = enterRevenue(i, matrixRevenue, "Outside Catering", 5, input);
            matrixRevenue[i][6] = matrixRevenue[i][6] / monthlyTargetRevenue * 100; // calculate Monthly Revenue Ratio per Salesperson
        }

        // Print out maxtrix matrixRevenue:
        for (int i = 0; i < matrixRevenue.length; i++) {
            for (int j = 0; j < matrixRevenue[i].length; j++) {
                System.out.print(matrixRevenue[i][j] + " ");
            }
            System.out.println();
        }
        //Invoke salesReport method
        salesReport(listSalesPerson, matrixRevenue, listIncentiveScheme);
        input.close();
    }
    /*
     * I: String parameter (password)
     * P: Check password if meeting requirements
     * O: Return boolean
     */
    public static boolean checkPassword(String password) { /// specific method
        String specialCharactersString = "!@#$%&*()'+,-./:;<=>?[]^_`{|}";
        if (password.length() < 12) {   // check password length
            return false;
        } else {
            char c;
            int numUpperCaseLetter = 0, numDigit = 0, numSpecialChar = 0;
            for (int i = 0; i < password.length(); i++) {
                c = password.charAt(i);
                if (Character.isUpperCase(c))  numUpperCaseLetter++; // check Uppercase
                else if (Character.isDigit(c)) numDigit++;  // check Digit
                else if (specialCharactersString.contains(Character.toString(c))) numSpecialChar++; // check special char
            }
            if (numUpperCaseLetter < 3) {
                System.out.println("\nPassword must contain at least three upper case letters.\n");
                return false;
            }
            if (numDigit != 4 && numDigit != 3)   {
                System.out.println("\nPassword must contain three or four numbers.\n");
                return false;
            }
            if (numSpecialChar != 2) {
                System.out.println("\nPassword must contain two special characters.\n");
                return false;
            }
        }
        return true; // *** must return true to get the true condition
    }

    /*
     * I: Scanner object
     * P: Check number of Salespersons if within range from 1-15
     * O: Integer parameter (numSalesPersons)
     */
    public static int enterNumSalesPersons(Scanner input) { // Check Number of Salespersons // added Scanner input as a parameter for this method (actually no need to close Scanner running within a specific method)
        int attemptNumSalesPersons = 0, numSalesPersons = 0;
        //Scanner input = new Scanner(System.in);
        while (numSalesPersons <1 || numSalesPersons > 15) {
            System.out.print("Enter the number of Salespersons: "); // must enter new number (first time or again)
            numSalesPersons = input.nextInt();
            if (numSalesPersons < 1 || numSalesPersons > 15) {
                System.out.println("\nNumber of Salespersons must be between 1-15.\n");
                attemptNumSalesPersons++;
            }
            if (numSalesPersons >= 1 && numSalesPersons <= 15) {
                System.out.println("\nNumber of Salespersons: " + numSalesPersons); // --> check length of list Salespersons
                //  input.close();
                return numSalesPersons; // stronger than break to exit // after return, the rest will not be proceeded
            }
            if (attemptNumSalesPersons == 3) {
                System.out.println("\nMaximum number of attempts exceeded.\n");
                System.exit(0);
            }
        }
        return numSalesPersons;
    }
    /*
     * Input: Integer, String, Matrix of Integers (parameter), Scanner object
     * Process: Enter each Sales category's revenue of each Salesperson into a matrix cell, calculate total revenue of each Salesperson by summing up
     * Output: Matrix of Integers (matrix)
     */
    public static double [][] enterRevenue(int salesPersonRowIndex, double [][] matrix, String categoryName, int categoryColumnIndex, Scanner sc) { // added the 5th parameter (Scanner input) to this enterRevenue to generate inputting without having another line of Scanner //public static int calculateRevenue(int revenue) ---> return total revenue/monthly target revenue in the main method;
        System.out.print("\nInput your revenue in " + categoryName + ": ");
        matrix[salesPersonRowIndex][categoryColumnIndex] = sc.nextDouble();
        matrix[salesPersonRowIndex][6] += matrix[salesPersonRowIndex][categoryColumnIndex]; // cumulative sum of Sales Revenue per Salesperson
        return matrix;
    }

    /*
     * I: Array of String (listSalesPerson), Matrix of Integers (matrixSalesRevenue), Array of Integers (Incentive Scheme Levels)
     * P: Print results and reports only
     * O: void
     */
    public static void salesReport(String [] salesPersonName, double [][] matrixRevenue, int [] incentiveSchemeLevel) { // school: number of students accepted in a school // markList: score matrix
        for (int i = 0; i < salesPersonName.length; i++) {
            System.out.print("Salesperson's Name: " + salesPersonName[i]);
            if (matrixRevenue[i][6] >= 125) {
                incentiveSchemeLevel[0]++; // Incentive Scheme Level III
                System.out.println(" is rewarded with Incentive Scheme Level III");
            }
            else if (matrixRevenue[i][6] >=115 && matrixRevenue[i][6] < 125) {
                incentiveSchemeLevel[1]++; // Incentive Scheme Level II
                System.out.println(" is rewarded with Incentive Scheme Level II");
            }
            else if (matrixRevenue[i][6] >= 100 && matrixRevenue[i][6] < 115) {
                incentiveSchemeLevel[2]++; //Incentive Scheme Level I
                System.out.println(" is rewarded with Incentive Scheme Level I");
            }
            else {
                incentiveSchemeLevel[3]++; // Non-rewarded
                System.out.println(" is not rewarded this month");
            }
        }
        System.out.println("Number of rewarded Salespersons with incentives: " + (incentiveSchemeLevel[0] + incentiveSchemeLevel[1] + incentiveSchemeLevel[2])); //
        System.out.println("Number of rewarded Salespersons with Incentive Scheme Level III: " + incentiveSchemeLevel[0]);
        System.out.println("Number of rewarded Salespersons with Incentive Scheme Level II: " + incentiveSchemeLevel[1]);
        System.out.println("Number of rewarded Salespersons with Incentive Scheme Level I: " + incentiveSchemeLevel[2]);
        System.out.println("Number of non-rewarded Salespersons this month: " + incentiveSchemeLevel[3]);
        System.out.println("Ratio of rewarded Salespersons in S&M department: " + ((double)(incentiveSchemeLevel[0] + incentiveSchemeLevel[1] + incentiveSchemeLevel[2]) / matrixRevenue.length)*100 + " %");
    }
}

The system must do the following:

A- A warm welcoming message will show “Welcome in Eden Hotels & Resorts” when the program begins and ask the user to input the monthly target revenue for each Sales person in the S&M department

B- The password must be asked to input satisfy the following rules:

• Not be less than 12 characters.

• Contain at least 3 upper-case letter.

• Contain only 3 or 4 numbers.

• Contain only 2 special characters.

The system must ask the user to enter new password if the password is incorrect and allow the user only 3 attempts.

The system will continue if the password is correct.

C- After checking the password, the system must require the user to enter the number of Salesperson per property, the number must be between 1-15. If the number is incorrect, the system must ask the user to re-enter (a number between 1-15). The system must provide the user only five attempts. If exceeded, the program will stop.

D- After entering a valid number of Salespersons, the system must inform the user to enter the names of Salespersons.

E- Then, the system must announce the user to enter the revenues of the sales categories of each Salesperson as follows:

a- “Input your revenue in Banquet” = 2

b- “Input your revenue in Meeting” = 2

c- “Input your revenue in MICE” = 3

d- “Input your revenue in Room” = 3

e- “Input your mark in F&B” = 2

f- “Input your mark in Outside Catering” = 2

F- The system must calculate the Monthly Sales Revenue Ratio of each Salesperson based on the revenues that were entered in the previous step according to the following function:

MSRR = (∑ (Revenue)/monthly target revenue)*100

G- The system must assign Salespersons to Incentive Scheme Levels based on the following matrix:

Incentive Scheme Level III: MSRR >= 125 (%)

Incentive Scheme Level II: 125> MSRR <=115 (%)

Incentive Scheme Level I: 115 > MSRR <=100 (%)

No Incentive: MSRR <100 (%)

H- The system must be able to print the following:

a- Report 1: Salesperson Name, Incentive Scheme Level

b- Report 2: Number of Salespersons in Sales & Marketing department showing Salesperson distribution per each Incentive Scheme Level.

c- Report 3: Number of Salespersons that not accepted to receive incentive.

d- Report 4: Ratio of Salespersons qualified to receive incentive among the S&M department.