Establish a connection to the data in the CSV file e$05$NewProducts using Get & Transform. Transform the data before importing it to Excel.

On the Transform tab, and in the Text Column group, use Format to capitalize each word in the Product column.

Ensure that the Price column data type is Currency.

On the Add Column tab, and in the General group, use Index Column to add a new column starting from $1.$ Move the column so that it is the first column of data. Rename the Column ProductID

Close and Load the product information into cell A$1$ of the NewProducts worksheet.

g$.$Click the NewSuppliers worksheet. The addresses in column B are arranged in the following manner:

Street Address$$City$,$ State Zip

In cell C$2,$ use Flash Fill to return the Street Address from column B$.$

h$.$In cell D$3,$ use Flash Fill to return the City from the addresses in column B$.$

i$.$In cell E$3,$ use Flash Fill to return the State abbreviations from the addresses in column B$.$

j$.$In cell F$3,$ use Flash Fill to return the Zip from the addresses in column B$.$

k$.$Click the GiftShop worksheet. In cell C$15,$ calculate the Extended Price for Souvenir Mugs by multiplying the Number to Order by the Selling Price.

l$.$In cell D$15,$ calculate the Purchase Cost for Souvenir Mugs by multiplying the Number to Order by the Cost.

m$.$In cell E$15,$ calculate the Profit by subtracting the Purchase Cost from the Extended Price. Copy cells C$15$:E$15$ down to row $20.$

n$.$In cell B$21,$ calculate the total number of items to order.

o$.$In cell B$27,$ calculate the total amount spent purchasing items.

p$.$In cell B$21,$ calculate the total profit to be made from selling items.

q$.$From the Data tab in the ribbon and in the Analyze group, launch Solver. Complete the Solver Parameters window as follows:

Maximize the Total Profit calculated in the model by changing the number of items to order.

Create a constraint that requires at least $15$ items of each product to be ordered.

Create a constraint that limits the Total Purchasing Cost to be less than or equal to the value in cell B$24.$

Create a constraint that limits the Total Items Ordered to be less than or equal to the value in cell B$25.$

Use the Simplex LP solving method and run Solver.