Question

1 Approved Answer

Posted on Aug 17, 2024

12 13 14 15 16 17 as netutu to it t Connect to the Southgate Resort database and import all the tables into the data

12 13 14 15 16 17 as netutu to it t Connect to the Southgate Resort database and import all the tables into the data model in the workbook. From the Data tab, and in the Get & Transform group, connect to the Southgate Resort Access Database.accdb downloaded file. Select the CustomerList. PaymentFees, and RoomRates tables in the database. Import the data being sure to creating a connection and to load the data to the data model. On the Reservation List sheet, format cells A4:F34 as a table. Choose any table style. Name the table Reservation List. Add the Reservation List table to the Data Model. From the Data tab, and in the Get & Transform group use connect from Table/Range. Transform the data to ensure the Transaction Date only includes the date (no time). Use "Load to" to make a connection. Be sure to create a connection and load the data to the data model. In PowerPivot Create relationships between the four tables as follows: 1 - There is one CustomerID on the CustomerList table for Many CustomerID on the ReservationList table 2- There is one PaymentType on the PaymentFees, table for many PaymentType on the Reservation List table 3- There is one RoomCode on the Room Rates, table for many RoomCode on the ReservationList In PowerPivot, create calculated columns for Gross Price, CC Fee, and Net Profit in the ReservationList table. For the Gross Price and CC Fee use the DAX function RELATED to retrieve the appropriate amounts from other tables. The new columns are calculated as follows: Gross Price Number of Nights (from ReservationList table) x Room Rate (from RoomRates Table) CC Fee Gross Price x Fee (from PaymentFees Table) Net Profit Gross Price - CC Fee In PowerPivot, on the ReservationList table, format the Gross Price, CC Fee, and Net Profit fields as Currency with 2 decimal places. Format the Transaction Date field as Date (with no time 7/23/2023). = - =

Connect to the Southgate Resort database and import all the tables into the data model in the workbook. From the Data tab, and in the Get \& Transform group, connect to the Southgate Resort Access Database. accdb downloaded file. Select the CustomerList, PaymentFees, and ReomRates tables in the database. Import the data being sure to creating a connection and to load the data to the data model. On the Reservation.List sheet, format cells A4:F34 as a table. Choose any table style. Name the table ReseryationList. Add the ReservationList table to the Data Model. From the Data tab, and in the Get \& Transform group use connect from Table/Range. Transform the data to ensure the Transaction Date only includes the date (no time). Use "Load to" to make a connection. Be sure to create a connection and load the data to the data model. In PowerPivot Create relationships between the four tables as follows: 1 - There is one CustomerID on the CustomerList table for Many CustomerID on the ReservationList table 2 - There is one PaymentType on the PaymentFees table for many PaymentType on the Reservation List table 3 - There is one ReomCode on the Reom Rates table for many RoomCode on the ReservationList. In PowerPivot, create calculated columns for Gross Price, CC Fee, and Net Profit in the ReservationList table. For the Gross. Price and CC.Fee use the DAX function RELATED to retrieve the appropriate amounts from other tables. The new columns are calculated as follows: - Gross Price = Number of Nights (from ReseryationList table) x RoomRate (from ReomRates Table) - CC.Fee = Gross.Price Fee (from PaymentFees Table) - Net Profit = Gross Price - CC.Fee In PowerPivot, on the ReseryatienList table, format the Gress. Price, CC.Fee, and Net. Profit fields as Currency with 2 decimal places. Format the Transaction. Date field as Date (with no time 7/23/2023). Create four explicit calculated fields in Power Pivot and on the ReserxationList table. Use the CALCULATE and SUM functions as necessary in your calculations. Calculate all Cash Net.Price in an explicit field named Cash.Prefit in the PaymentType field column*. Using the AutoSum function on the Home tab and in the Calculations group, sum the Gross Price, CC. Fee, and Net.Profit calculated columns. Accept the default names of the explicit fields. Format all of the explicit fields as Currency (using $ English). *Hint: The filer should be ReservationList[PaymentType]="Cash" Create a dashboard consisting of one PivotChart and one PivotTable based on data from the Data Model. From Power Pivot on the Home tab, click or press PivotTable and select Chart and Table (Vertical). Insert the charts on the PixotChartandTable worksheet, beginning in cell A1. Select Chart 1 and add State from the CustomerList table to the Filters area. On the chart, use the State filter to display NY and MA customers. Add the transaction date from the ReservationsList to the Axis (Categories) field. Remove the grouping for TransactionDate(year), TransactionDate(Quarter), TransactionDate(Month), and TransactionDate(Date) [leaving just TransactionDate]. Add Cash. Prefit and Sum of CC. Fee from the ReservationsList Table to the Values area. Change the Chart Type to Combo and change the Cash Profit series to be a clustered column and the Sum of CC. Fee to be a line. Change the Chart Name to CashProfitySCCFee. Improve on the design of the dashboard by changing the chart titles and modifying other chart elements. Select the CashProfitYSCCFee chart, add a chart title that reads Cash Profit and Credit Card Fees. Change the Chart Style to Style 6. Hide all Field Buttons from the chart. Select PivotTable1 and add State from the Customers table to Rows area. Add Sum of Net Profit from the ReserxationList table to the Values. In the Replace Row Labels in cell A15 with "State". Change the PivotTable Name to Sales-by-State. Click the Documentation worksheet and update the Change Log. Briefly describe at least one major modification that was made to the Reservation.Analysis, Activities, ReservationsbyCategory, Break-Even Analysis, Amortization, and TourBusEyal worksheets. Each modification should be described on a separate row of the Change Log. Enter the modification date and your name along with a description of each modification. If necessary, wrap the text in the cell containing the description. You do not need to enter a Last Connect to the Southgate Resort database and import all the tables into the data model in the workbook. From the Data tab, and in the Get \& Transform group, connect to the 12 Southgate Resort Access Database. accdb downloaded file. Select the CustomerList, PaymentFees, and ReomRates tables in the database. Import the data being sure to creating a connection and to load the data to the data model. Connect to the Southgate Resort database and import all the tables into the data model in the workbook. From the Data tab, and in the Get \& Transform group, connect to the Southgate Resort Access Database. accdb downloaded file. Select the CustomerList, PaymentFees, and ReomRates tables in the database. Import the data being sure to creating a connection and to load the data to the data model. On the Reservation.List sheet, format cells A4:F34 as a table. Choose any table style. Name the table ReseryationList. Add the ReservationList table to the Data Model. From the Data tab, and in the Get \& Transform group use connect from Table/Range. Transform the data to ensure the Transaction Date only includes the date (no time). Use "Load to" to make a connection. Be sure to create a connection and load the data to the data model. In PowerPivot Create relationships between the four tables as follows: 1 - There is one CustomerID on the CustomerList table for Many CustomerID on the ReservationList table 2 - There is one PaymentType on the PaymentFees table for many PaymentType on the Reservation List table 3 - There is one ReomCode on the Reom Rates table for many RoomCode on the ReservationList. In PowerPivot, create calculated columns for Gross Price, CC Fee, and Net Profit in the ReservationList table. For the Gross. Price and CC.Fee use the DAX function RELATED to retrieve the appropriate amounts from other tables. The new columns are calculated as follows: - Gross Price = Number of Nights (from ReseryationList table) x RoomRate (from ReomRates Table) - CC.Fee = Gross.Price Fee (from PaymentFees Table) - Net Profit = Gross Price - CC.Fee In PowerPivot, on the ReseryatienList table, format the Gress. Price, CC.Fee, and Net. Profit fields as Currency with 2 decimal places. Format the Transaction. Date field as Date (with no time 7/23/2023). Create four explicit calculated fields in Power Pivot and on the ReserxationList table. Use the CALCULATE and SUM functions as necessary in your calculations. Calculate all Cash Net.Price in an explicit field named Cash.Prefit in the PaymentType field column*. Using the AutoSum function on the Home tab and in the Calculations group, sum the Gross Price, CC. Fee, and Net.Profit calculated columns. Accept the default names of the explicit fields. Format all of the explicit fields as Currency (using $ English). *Hint: The filer should be ReservationList[PaymentType]="Cash" Create a dashboard consisting of one PivotChart and one PivotTable based on data from the Data Model. From Power Pivot on the Home tab, click or press PivotTable and select Chart and Table (Vertical). Insert the charts on the PixotChartandTable worksheet, beginning in cell A1. Select Chart 1 and add State from the CustomerList table to the Filters area. On the chart, use the State filter to display NY and MA customers. Add the transaction date from the ReservationsList to the Axis (Categories) field. Remove the grouping for TransactionDate(year), TransactionDate(Quarter), TransactionDate(Month), and TransactionDate(Date) [leaving just TransactionDate]. Add Cash. Prefit and Sum of CC. Fee from the ReservationsList Table to the Values area. Change the Chart Type to Combo and change the Cash Profit series to be a clustered column and the Sum of CC. Fee to be a line. Change the Chart Name to CashProfitySCCFee. Improve on the design of the dashboard by changing the chart titles and modifying other chart elements. Select the CashProfitYSCCFee chart, add a chart title that reads Cash Profit and Credit Card Fees. Change the Chart Style to Style 6. Hide all Field Buttons from the chart. Select PivotTable1 and add State from the Customers table to Rows area. Add Sum of Net Profit from the ReserxationList table to the Values. In the Replace Row Labels in cell A15 with "State". Change the PivotTable Name to Sales-by-State. Click the Documentation worksheet and update the Change Log. Briefly describe at least one major modification that was made to the Reservation.Analysis, Activities, ReservationsbyCategory, Break-Even Analysis, Amortization, and TourBusEyal worksheets. Each modification should be described on a separate row of the Change Log. Enter the modification date and your name along with a description of each modification. If necessary, wrap the text in the cell containing the description. You do not need to enter a Last Connect to the Southgate Resort database and import all the tables into the data model in the workbook. From the Data tab, and in the Get \& Transform group, connect to the 12 Southgate Resort Access Database. accdb downloaded file. Select the CustomerList, PaymentFees, and ReomRates tables in the database. Import the data being sure to creating a connection and to load the data to the data model