Purpose In this project, you will use data analytics software and a sample dataset to gain an understanding of visual analytics and then use that knowledge to answer some important business questions from an accounting perspective. You will create dashboards (e.g., the balanced scorecard) to assess performance in the hospitality sector. Although the data has been altered slightly to maintain anonymity for the hotel chain, this case represents real challenges which senior management faced. At the end of this module, you will be able to: . MO 9.1 Generalize accounting information through interactive dashboards . MO 9.2 Integrate multiple sources of data and enumerate that in your analysis. . MO 9.3 Identify trend information through critique of the analysis. Case Study-Background Background Pueblo Hospitality, Inc. (PHI) operates a chain of 12 hotels. Founded in 1968 by Mark Bayer of Denver, Colorado, his first hotel was in the Denver suburb of Lakewood. Two years later, Mark opened his second hotel in Colorado Springs, a city about 75 miles south of his Lakewood hotel. By 1974, Mark expanded beyond his home state of Colorado, opening hotels in Oklahoma and Texas. One year later PHI opened in New Mexico. Exhibit 1 is a table of the number of hotels by state. PHI's hotels are in the economy lodging segment. Their brand competes with the likes of Super 8, Days Inn, Motel 6, Econo Lodge, Microtel and Red Roof Inn. With 1 14 hotels, PHI ranks as the 18th-largest operator of economy lodging hotels. A typical economy lodging hotel has an average of 84 rooms, although PHI's hotels average 117 rooms. Properties are staffed by a general manager, front desk staff of 6 persons, a head housekeeper, 7 housekeepers, and a maintenance worker. With the exception of the general manager, employees are paid hourly and their assigned hours vary based on demand Now in his 80s, Mark is no longer involved in the day-to-day running of PHI Stephanie Putnam, Mark's daughter, is PHI's President and CEO. Reporting to Stephanie are Senior Vice Presidents Adelaide St. Olive and Laura Mccarthy. The organization chart is shown in Exhibit 2. Present Situation It is December 30th. Stephanie has asked you, the Chief Financial Officer, to provide data and provide recommendations to the managers who will make presentations at the Annual Operations Review. The review is held at the company's Denver headquarters each year on the last business day in January. In the past, district managers have compared actual results to the budgeted income statement. While this practice should continue, you've come to the realization that there is too little attention to non-financial measures. Having heard about dashboards and balanced scorecards, you decided to gather a variety of financial and non-financial data to forward on to the general managers, district managers, and area managers who will attend the meeting with Stephanie. The data you have collected (in eight Excel files) is described in Exhibit 3. You will need to combine data by locations and aggregate the data with annual amounts Performance Targets Since its founding, PHI has used a limited number of performance benchmarks to evaluate properties and its managers. Specifically, they have used profits per available room (ProfitPAR, a common industry benchmark) and customer satisfaction. The target for ProfitPAR is a 2% increase from the prior-year results and, for customer satisfaction, the target is a score of 7.5. Assume your cost of capital is 9%. Further, you've been thinking about productivity and decide that housekeeper productivity can be measured. After interviewing a number of general managers, you determine the expected time to clean a room should be 30 minutes (0.5 hour) Lastly, PHI has considered the mini be 7 0 Stephanie wants you to develop a dashboard, proposing additional performance measures See Exhibits 1-3 for State, Organizational Chart, and Data Description Industry Background Read the Case Study, reference reading on the hotel industry, and review the presentation on Balanced Scorecards. For more information on the hotel industry, see the following links: https://lodgingmagazine com/more-supply-and-demand-a-look-at-the-economy-hotel-landscape/ https://www.hotelbusiness.com/fundamentals-within-the-economy-segment-remain-sound/ http//www.hotelnewsnow.com/Articles/6217/Hotel-Industry-Terms-to-Know https //guidingmetrics. com/content/hotel-industrys-14-most-critical-metrics/ Task 1.) Assume you are the CFO, and you will use Tableau to analyze Pueblo Hospitality. Prepare a balanced scorecard in Tableau and then summarize firm performance including both financial and non-financial information that can help inform decision making a. Load the data into Tableau (see separate instructions below) Use the data to prepare a presentation on the Performance Targets and include one of more of the following one of the firm's two areas one district within the chosen area iii. one hotel within the chosen district iv. the company as a whole. HINT: consider how you incorporate filters into your worksheets and data viz. c. Create a worksheet for each component of the balanced scorecard (financial, customer, internal, and earning) Label your worksheets at Financial, Customer, Internal, and Learning. Should you have more than one for a component, add a number (e.g. Financial-1, Financial-2) but note that will be grading the -1 version. ii. You must have at least two different chart layout types in your four sheets. ili. You must include at least one trend line on one of your graphs/charts Hint: Refer to Performance Targets in the background. of KAK iV. You must include filters on each of your worksheets. V . You must create an interactive dashboard using the 4 sheets references in 2ci above. vi. Label sheets as Financial, Customer, Internal, Learning, and Dashboard d. Performance Targets (see Case Study section of these instructions) will require you to make a number of calculated fields. You are required to have at least one of calculated field in each of your four worksheets (financial, customer, internal, and learning). HINT: Check out KPIs for hotel industry: https://guidingmetrics com/content/hotel-industrys-14- most-critical-metrics/ 2.) Using your Tableau worksheets for each element of the Balanced Scorecard, create a Dashboard. Your dashboard must be interactive meaning that selecting any filter in the dashboard will update all the data viz's used within the dashboard. 3.) Prepare a one-page (single spaced) narrative that concisely summarizes your findings. Focus on one area, one district within the chosen area, one hotel within the chosen district, and the company as a whole. 4.) Prepare a formulas sheet where you type out every calculated field that you created. Be sure to explain the logic of whichever KPI's you choose to incorporate. 5.) Deliver a video presentation summarizing your findings. Your video presentation should include outputs from Tableau. Your delivery can be done on different software. See instructions for creating a video in Canvas. Please note, your video presentation should be about 5 minutes. Materials and Technology Needed Tableau was selected by Pueblo Hospitality Inc. for its ease of use for non-technical users See Module 0 if you have not downloaded Tableau already . In the Module 9, there are links to training videos that will show you have to get started in Tableau as well as how to begin making a dashboard Criteria Tableau is a powerful tool that enables you to create a wide range of visualization with numerous data sources and because of this, you really need to spend time in the software before you truly master these skills. Please note that these instructions are vague as you have an amazing tool at your disposal to visualize this information. As such, the worksheets and the information that you display are all subjective to the story that you are telling in your project. My expectation is that you will have well-developed worksheets that will include things like trend-lines or averages, tool tip. and dashboards that have floating containers and a legend. If you choose to add extra sheets to your workbook, be sure to provide clarity as to which sheets you have the required components for grading. This task is worth 150 points and will be evaluated as met or not met based on your successful completion of the assignment requirements