Cashback Offer from 15th to 20th April 2021. Get Flat 20% Cashback credited to your account for a minimum transaction of $80. Post Your Question Today!

Question DetailsNormal
$ 25.00

Office 2016 MyITLab MS-Excel Grader EX16_XL_CH10_GRADER_CAP_AS – Movie Rental

Question posted by
Online Tutor Profile
request

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 Possible

1Open e10c2MovieRentals.xlsx and save the workbook with the name e10c2MovieRentals_LastFirst.0

2Import 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

3Copy 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

4Enter 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

5Copy 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

6Create 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

7Create 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

8Open 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

9Create 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

10Use 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

11Add 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

12Create 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

13Open 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

14Add 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

15Create 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

16Create 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

17Create 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

18Create 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

19Save 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

Available Answer
$ 25.00

[Solved] Office 2016 MyITLab MS-Excel Grader EX16_XL_CH10_GRADER_CAP_AS – Movie Rental

  • This solution is not purchased yet.
  • Submitted On 30 Mar, 2021 05:59:26
Answer posted by
Online Tutor Profile
solution
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 u...
Buy now to view the complete solution
Other Similar Questions
User Profile
Exper...

Office 2013 Textbook Publishers | Complete Solution

This Tutorial is rated A+ previously,if you have any questions regarding this tutorial then you can contact me....
User Profile
Acade...

Office 2016 MyITLab MS-Excel Grader EX16_XL_CH10_GRADER_CAP_AS – Movie Rental

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 ad...
User Profile
Acade...

Office 2019 MS-Access Grader YO19_Access_Ch12_PS1 – Automating Tasks in the Putts for Paws Database

Putts for Paws is an annual charity event that takes place at the Painted Paradise Golf Resort & Spa and raises money for a local animal shelter. You have been asked to enhance their current database by creating macros that w...
User Profile
Acade...

Office 2016 MyITLab MS-Excel Grader EX16_XL_CH10_GRADER_CAP_AS – Movie Rentals 1.0

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 ad...
User Profile
Acade...

Office 2016 MyITLab MS-Excel Grader EX16_XL_CH08_GRADER_CAP_HW – Customer Satisfaction 1.4

You have been hired to analyze the effectiveness of a local restaurant’s drive-through service. As part of your analysis, you will evaluate customer satisfaction. You hypothesize that customer satisfaction decreases as wait...

The benefits of buying study notes from CourseMerit

homeworkhelptime
Assurance Of Timely Delivery
We value your patience, and to ensure you always receive your homework help within the promised time, our dedicated team of tutors begins their work as soon as the request arrives.
tutoring
Best Price In The Market
All the services that are available on our page cost only a nominal amount of money. In fact, the prices are lower than the industry standards. You can always expect value for money from us.
tutorsupport
Uninterrupted 24/7 Support
Our customer support wing remains online 24x7 to provide you seamless assistance. Also, when you post a query or a request here, you can expect an immediate response from our side.
closebutton

$ 629.35