ISYS $2103$: Data Analytics Case Study

Scenario: You are the regional manager of the QC Inn, a nationwide chain of affordable motels for vacationing families & business travelers. You will be preparing year end reports for $24$ QC Inn franchises located in Nebraska$/$South Dakota region.

Open QC Inn file & save as QCInn$\_$yourfirstnamelastname: Example: QCInn$\_$JohnDoe. Fill out Documentation Sheet with Your Name & Date. Review the data on the Motels & Terms & Definitions Sheets. On the Motels Sheet: Complete the Occupancy Rates, ADR & Rev Par$($Columns K$,$ M & N$).$

Create PivotTables to answer the following questions:

NOTE: Format ALL Value fields accordingly$-$accounting or currency, $\%,$ commas & NO decimals: For an example: $$134,000,5\%,56,000$

When complete: Submit in Blackboard: HW Assignment # $2($PivotTables$)$

$1.$ Total number of Rooms rented each Year

$2.$ Total Revenue by Motel

$3.$ Total Rooms rented by Region & State

$4.$ Total Motel Rooms & Rooms Rented per City

$5.$ Total Revenue by Year

a$.$ Create a Clustered Column Chart

b$.$ Chart Title: QC Inn: Total Revenue by Year

c$.$ Remove the Legend

d$.$ Format: Your choice

$6.$ Total Revenue by Month per City

$7.$ Total Revenue by Motel & $\%$ of the Total Revenue by Motel

a$.$ Sort $\%$ Highest to Lowest.

b$.$ Highlight in YELLOW the Best Performing Motel

$8.$ Total Rooms Rented by Motel

a$.$ Filter by Weekday

b$.$ Show ALL Rooms Rented on Saturday

$9.$ Total Revenue by State & Region

a$.$ Insert a Timeline

b$.$ Show Revenue for March $2020$

c$.$ Change Timeline Caption to: Click on Month

$10.$ Total Revenue & Total Rooms Rented by Month

a$.$ Insert a Slicer for Motel Name

b$.$ Show data for Lexington Inn & Suites

c$.$ Change Sliver Caption to: Click on Motel Name

$11.$ Total Revenue by City

a$.$ Sort Smallest to Largest

b$.$ Highlight the City with the Lowest Revenue