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:
| Step | Instructions | Points Possible |
| 1 | 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 corner that displays the rental cost by account type (from Rates). Add an appropriate title to the Power View.Hint: Drag the rental cost field to the lower left corner of the canvas then drag account number over the new visualization to add it to the table. Double-click the title area to edit the field. | 6 |
| 18 | Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet.Hint: On the Page Layout tab, in the Page Setup group, click the Page Setup Dialog Box Launcher. | 6 |
| 19 | Save and close the file. Based on your instructor’s directions, submit e10c2MovieRentals_LastFirst.xlsx. Hint: Click the Save icon on the Quick Access Toolbar to save the workbook. |
0 |
