You are the systems manager for Blue City Movies Rentals and you have been asked to create a report on historical sales data. To complete your task you will combine and edit data from multiple sources using Excel’s Power add-ins, XML, and text functions. Instructions: For the purpose of grading the project you are required to perform the following tasks: StepInstructionsPoints Possible1 Open e10c2MovieRentals.xlsx and save the workbook with the name e10c2MovieRentals_LastFirst .0 2 Import the movie data from the delimited file e10c2Movies.txt and rename the new worksheet Inventory . Hint: On the Data tab, in the Get External Data group, click From Text.9 3 Copy the data in the range B2:B26 and paste it in the range G2:G26. Then delete the values in the range B2:B26.0 4 Enter a function in cell B2 that references the copied value in cell G2 and formats the data with the first letter capitalized. Use the Fill handle to copy the function down to complete the column. Hint: Use the PROPER function to display the text with a capital letter at the beginning of each word. 0 5 Copy the range B2:B26 and paste the values back into the range B2:B26 to remove the underlying functions. Then delete the values in Column G. Hint: Select the range B2:B26 and click Copy in the Clipboard group. Right-click cell B2, and select Paste Special, Values. 10 6 Create a new worksheet named Customers . Hint: To create a new worksheet, click the New Sheet button located at the end of the worksheet tabs in the workbook.5 7 Create a connection to the e10c2Contacts.xml file. Hint: Click the Data tab. Click Get External Data, then click From Other Sources, and select From XML Data Import.8 8 Open e10c2Contacts.xml in Notepad. Locate the spelling error in the first account type siver . Edit the text to say Silver and save the file. Refresh the connection. Hint: Click the Data tab and click Refresh all in the Connections group.3 9 Create a new worksheet named Transactions. Hint: To create a new worksheet, click the New Sheet button located at the end of the worksheet tabs in the workbook.5 10 Use Power Pivot to import the file e10Transactions.accdb into a PivotTable on the Transactions worksheet. Hint: Click the PowerPivot tab and click Manage. Click Get External Data, click From Database, and select Access.12 11 Add the Date field from Transactions to the FILTERS area, Account from Table1 to the ROWS area, Transaction # to the ROWS area, Last from Table1 to the ROWS area, and Total to the VALUES area.5 12 Create a relationship between the Account fields in Table1 (Related) and the Transactions (Table) table. Hint: On the PivotTable Tools Analyze tab, click Relationships in the Calculations group. 6 13 Open e10c2Rates.xlsx using Power Query, add Currency Number Format to column B, and load the data into a new worksheet named Rates . Hint: Click the Data tab. Click New Query, click From File, and select From Workbook.10 14 Add the Insert a Power View Report button to the Quick Access Toolbar, if necessary. Create a new Power View report. Hint: Click Insert a Power View Report on the Quick Access Toolbar. 6 15 Create a visualization in the right half of the canvas that displays Date and total earnings (Total) by account type (Type) in a Matrix format. Set the aggregation settings to Sum. Hint: To add a visualization to a Power View, drag the desired field onto the canvas. Next drag each additional field into the newly created visualization. On the DESIGN tab, in the Switch Visualization group, click Table, and then click Matrix. Aggregation settings can be edited in the Power View Fields pane. 3 16 Create a visualization pie chart of number of accounts by account type (both from Table1) in the upper left corner. Hint: To create a pie chart from tabular data, click the tabular visualization you want to edit and select the desired chart option from the Switch Visualization group on the DESIGN tab. 6 17 Create a table visualization in the lower left corn…

Looking for solution of this Assignment?

WHY CHOOSE US?

We deliver quality original papers

Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected  

Free revisions

We offer our clients multiple free revisions just to ensure you get what you want.

Discounted prices

All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount

100% originality

We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI

On-time delivery

We will deliver your paper on time even on short notice or  short deadline, overnight essay or even an urgent essay