Yo19_Excel_Bu03_Ps1_Movie_Analysis

Grader - Instructions Excel 2019 Project YO19_Excel_BU03_PS1_Movie_Analysis Project Description: Bob Dobalina runs a movie production consulting business and has worked with some of the world’s most renown directors. He has requested your help in analyzing a small sample of the movies on which he has provided consultation. Steps to Perform: Step Instructions Points Possible 1 Start Excel. Download and open the file named Excel_BU03_PS1_MovieAnalysis.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. 0 2 Creating Named Ranges will make it easier to refer to tables of data when using LOOKUP functions to retrieve information. On the MovieTables worksheet, assign a MovieTitles named range to the range A2:B37 and assign a MovieRatings named range to the range D2:E37. 4 3 Tables are an easy way to format a data set and explore using the built in filters. On the Movies worksheet, create a table with headers using the range A5:K32 and apply the Light Blue, Table Style Medium 23. 5 4 Use the Create from Selection named range tool to create a named range for each column, using the Top row of the table for the names. 4 5 In cell J6, enter a VLOOKUP function that will lookup the MovieID value in the leftmost column of the MovieTitles named range and return the movie name from the second column. Incorporate an IFERROR function so that if there is no MovieID, the function returns a blank. 10 6 In cell K6, enter a VLOOKUP function that will lookup the MovieID value in the leftmost column of the MovieRatings named range and return the movie rating from the second column. Incorporate an IFERROR function so that if there is no MovieID, the function returns a blank. Copy the formula down through cell K35. 10 7 In cell N5, enter an INDEX function that will retrieve the name of the movie with the highest gross sales. Write the function using the INDIRECT function as the array argument and then copy the formula across to P5. 12 8 Insert Year and Genre slicers to further explore the data in the Movies table. Format the slicers as follows. • Position the Year slicer so that its top-left corner is in cell M7. Change the layout so that it is 2 columns and then adjust the height of the slicer so that the extra white space is no longer visible. • Position the Genre slicer so that is top-left corner is in cell M24. Change the layout so that it is 2 columns and then adjust the height of the slicer so that the extra white space is no longer visible. • Apply the Light Blue, Slicer Style Dark 1 to both slicers. 5 9 Use the slicers to select only the movies from the Animation, Adventure, or Action genres that were released in 2011, 2012, or 2014. Copy the filtered data, including the column headers, and paste the results on the SelectedMovies worksheet, starting in cell A1. 5 10 On the Movies worksheet, in cell B1, enter a function that will calculate the total gross sales for records visible in the table. Format as Accounting. In cell B2, enter a function that will calculate the average gross sales for records visible in the table. Format as Accounting. In cell B3, enter a function that will calculate the number of movies visible in the table, using the MovieID field. 13 11 On the Directors worksheet, in cell D2, enter an IFS function that will determine the status of each director, based on the following conditions. • A director is considered Elite if they have directed at least 15 movies with an average score that is greater than 6.5 or has an average score of greater than 6.8 regardless of the number of movies. • A director is considered Premiere if they have directed at least 5 movies with an average rating of at least 6. • All other directors should have a status of Standard • Copy the formula down through cell D28. 12 12 Using the table on the Movies worksheet, create a PivotTable on a new worksheet and configure it according to the following. • Name the worksheet that the PivotTable is on, PivotAnalysis • The PivotTable should the Average Gross sales for each Genre (rows), with the ability to filter on Director. • Format the Average of Gross as Accounting with 0 decimal places and with a custom name of Average Gross Sales • Edit the Row Labels label to read Genres • Apply the Dark Blue, Pivot Style Dark 2 style to the PivotTable. 10 13 Using the PivotTable on the PivotAnalysis worksheet create a 2-D Clustered Column Chart and configure the chart according to the following. • Apply the Style 10 Chart Style. • Apply the Layout 5 Quick Layout. • Apply the Monochromatic Palette 5 colors. • Edit the Y axis title to read Average Gross Sales • Edit the Chart title to read Average Gross Sales by Genre • Move the PivotChart to a new worksheet, naming the worksheet AverageGrossPivotChart • Use the Director filter to select only the following directors: Kevin Smith, Kunihiko Yuyama, Oliver Stone, and Tim Burton. 10 14 Save and close Excel_BU03_PS1_MovieAnalysis.xlsx. Exit Excel. Submit the file as directed. 0 Total Points 100 Created On: 01/22/2021 1 yo19_excel_bu03_ps1 Movies Total Gross Sales (filtered) Average Gross Sales (filtered) Total Movies (filtered) MovieName Company Genre Budget Director Company Country Genre Gross StarActor Year MovieID MovieName MovieRating Highest Grossing Movie $ 130,000,000.00 Chris Miller DreamWorks Animation USA Animation $ 149,260,504.00 Antonio Banderas 2011 Mov-5962 $ 6,000,000.00 Jay Russell Alcon Entertainment USA Comedy $ 34,099,640.00 Frankie Muniz 2000 Mov-6396 $ 30,000,000.00 Mark Waters Angry Films USA Action $ 7,791,146.00 Zoey Deutch 2014 Mov-7522 $ 13,500,000.00 Kasi Lemmons Universal Pictures USA Crime $ 687,194.00 Samuel L. Jackson 2001 Mov-3548 $ 10,000,000.00 Joe Nussbaum Metro-Goldwyn-Mayer (MGM) USA Comedy $ 9,436,390.00 Alexa PenaVega 2004 Mov-7957 $ 3,500,000.00 John Harrison Paramount Pictures USA Comedy $ 16,324,573.00 Debbie Harry 1990 Mov-7354 $ 5,000,000.00 Michael Lehmann Cinemarque Entertainment USA Comedy $ 485,772.00 Ed Begley Jr. 1990 Mov-8908 $ 10,600,000.00 Jaume Balagueró Castelao Producciones USA Horror $ 22,163,442.00 Anna Paquin 2002 Mov-7969 $ 7,000,000.00 Robert Klane Artimm USA Adventure $ 12,741,891.00 Andrew McCarthy 1993 Mov-3778 $ 30,000,000.00 Kunihiko Yuyama Oriental Light and Magic (OLM) Japan Animation $ 85,744,662.00 Veronica Taylor 1998 Mov-7718 $ 13,000,000.00 Charles Grosvenor Hanna-Barbera Productions UK Animation $ 6,574,896.00 Michael Crawford 1993 Mov-7744 $ 70,000,000.00 Oliver Stone Twentieth Century Fox Film Corporation USA Drama $ 52,474,616.00 Shia LaBeouf 2010 Mov-4371 $ 30,000,000.00 John McTiernan Paramount Pictures USA Action $ 122,012,643.00 Sean Connery 1990 Mov-4246 $ 95,000,000.00 Steve Martino Blue Sky Studios USA Animation $ 161,321,843.00 Ray Romano 2012 Mov-4914 $ 30,000,000.00 Shekhar Kapur PolyGram Filmed Entertainment UK Biography $ 30,082,699.00 Cate Blanchett 1998 Mov-6036 $ 50,000,000.00 Adam McKay Paramount Pictures USA Comedy $ 127,352,707.00 Will Ferrell 2013 Mov-4834 $ 80,000,000.00 Dennis Dugan Columbia Pictures USA Comedy $ 162,001,186.00 Adam Sandler 2010 Mov-6653 $ 10,000,000.00 Julien Temple De Laurentiis Entertainment Group (DEG) UK Comedy $ 3,916,303.00 Geena Davis 1988 Mov-5175 $ 32,000,000.00 Nanette Burstein New Line Cinema USA Comedy $ 17,804,299.00 Drew Barrymore 2010 Mov-4507 $ 37,000,000.00 William Malone Dark Castle Entertainment USA Horror $ 40,846,082.00 Geoffrey Rush 1999 Mov-7395 $ 1,500,000.00 Fred Olen Ray Cinetel Films USA Action $ 1,368,912.00 David Carradine 1986 Mov-7316 $ 16,600,000.00 Joachim Rønning Recorded Picture Company (RPC) UK Adventure $ 1,517,410.00 Pål Sverre Hagen 2012 Mov-8761 $ 40,000,000.00 Jake Kasdan Columbia Pictures USA Comedy $ 38,543,473.00 Jason Segel 2014 Mov-3334 $ 230,000.00 Kevin Smith View Askew Productions USA Comedy $ 3,151,130.00 Brian O'Halloran 1994 Mov-8822 $ 80,000,000.00 Stephen Sommers Universal Pictures USA Action $ 155,247,825.00 Brendan Fraser 1999 Mov-8142 $ 55,000,000.00 Steve Antin Screen Gems USA Drama $ 39,440,655.00 Cher 2010 Mov-7375 $ 10,000,000.00 Tim Burton The Weinstein Company USA Biography $ 14,479,776.00 Amy Adams 2014 Mov-8159 Directors Director NumOfMovies AvgScore Status Chris Miller 2 6.4 Jay Russell 4 6.68 Mark Waters 10 6.14 Kasi Lemmons 3 6.9 Joe Nussbaum 3 5.67 John Harrison 1 6.2 Michael Lehmann 8 5.88 Jaume Balagueró 2 6 Robert Klane 1 4.6 Kunihiko Yuyama 2 5.85 Charles Grosvenor 1 6.5 Oliver Stone 17 6.93 John McTiernan 11 6.54 Steve Martino 2 6.85 Shekhar Kapur 3 6.97 Adam McKay 6 6.92 Dennis Dugan 14 5.79 Julien Temple 2 5.4 Nanette Burstein 1 6.3 William Malone 2 4.45 Fred Olen Ray 2 4.65 Joachim Rønning 1 7.2 Jake Kasdan 5 6.14 Kevin Smith 11 6.73 Stephen Sommers 7 6.19 Steve Antin 1 6.4 Tim Burton 17 7.07 MovieTables ID MovieTitle ID Rating Mov-3334 Sex Tape Mov-3334 R Mov-3548 The Caveman's Valentine Mov-3548 R Mov-3778 Weekend at Bernie's II Mov-3778 PG Mov-4246 The Hunt for Red October Mov-4246 PG Mov-4371 Wall Street: Money Never Sleeps Mov-4371 PG-13 Mov-4507 Going the Distance Mov-4507 R Mov-4834 Anchorman 2: The Legend Continues Mov-4834 PG-13 Mov-4914 Ice Age: Continental Drift Mov-4914 PG Mov-5175 Earth Girls Are Easy Mov-5175 PG Mov-5705 La isla maldita Mov-5705 R Mov-5962 Puss in Boots Mov-5962 PG Mov-6036 Elizabeth Mov-6036 R Mov-6396 My Dog Skip Mov-6396 PG Mov-6653 Grown Ups Mov-6653 PG-13 Mov-7316 Armed Response Mov-7316 R Mov-7354 Tales from the Darkside: The Movie Mov-7354 R Mov-7375 Burlesque Mov-7375 PG-13 Mov-7395 House on Haunted Hill Mov-7395 R Mov-7522 Vampire Academy Mov-7522 PG-13 Mov-7589 Twilight Mov-7589 R Mov-7718 Pokémon: The First Movie - Mewtwo Strikes Back Mov-7718 G Mov-7744 Once Upon a Forest Mov-7744 G Mov-7957 Sleepover Mov-7957 PG Mov-7969 Darkness Mov-7969 PG-13 Mov-8142 The Mummy Mov-8142 PG-13 Mov-8159 Big Eyes Mov-8159 PG-13 Mov-8475 Music of the Heart Mov-8475 PG Mov-8761 Kon-Tiki Mov-8761 PG-13 Mov-8822 Clerks Mov-8822 R Mov-8908 Meet the Applegates Mov-8908 R Mov-9611 Crocodile Dundee Mov-9611 PG-13 Mov-9765 The Fly Mov-9765 R Mov-9840 Big Trouble in Little China Mov-9840 PG-13 Mov-9863 Pretty in Pink Mov-9863 PG-13 Mov-9917 Highlander Mov-9917 R Mov-9938 Lucas Mov-9938 PG-13 SelectedMovies Documentation Create Date By Whom Description Workbook Name 5/18/22 Bob Dobalina e03MovieAnalysis.xlsx Mod. Date By Whom Mod. Description Last Version Backup Name : VERSION BACKUP NAME : Before modifying any worksheet, save the original workbook with the following name format: Original name_yyyymmdd Create Date Sheet Name Creator Purpose 5/18/22 Movies Bob Dobalina 5/18/22 Directors Bob Dobalina 5/18/22 MovieTables Bob Dobalina 5/18/22 SelectedMovies Bob Dobalina