Establish a connection to the text file Excel$\_$BU$05\_$PS$2\_$ReservationData.txt using Get & Transform.

Split the data into individual columns using the Tab character as a delimiter and use the first row of data as headings.

Change the data types of the CheckInDate and CheckOutDate fields to Date.

Clean any non$-$printable characters from the GuestName field. These may not be visible in the

Power Query Editor, but will be visible in Excel if not removed.

Extract the first name, middle name, and last name from GuestName into three new columns

named FirstName MiddleName and LastName in that order.

Close and Load the data into Cell A$1$ of the ReservationData worksheet.

On the RatesScenario worksheet, use the Scenario Manager to add a scenario named Most$-$

likely scenario. Use cells C$4$:C$7$ as the Changing cells. The current values on the worksheet

will be your Most Likely scenario values.

Add a new scenario named Best$-$case scenario using the same changing cells. In the

Scenario Values dialog box, in row $1,$ type $410$ and then in row $2,$ type $220.$ Click row $3,$ type

$265$ and then in row $4,$ type $130.$

Add a new scenario named Worst$-$case scenario using the same changing cells, In the

Scenario Values dialog box, in row $1,$ type $160$ and then in row $2,$ type $105.$ Click row $3,$ type

$160$ and then in row $4,$ type $65.$