This is an Excel assignment. The directions are attached.
MIS 140 Arakawa Computer Project: Excel Due Date: 11/10/20 at 10:00pm CST Requirements You will need Excel 365, Excel 2016 or higher to complete this assignment. Please visit this link for more information : https://offices.depaul.edu/information-services/services/Software/Pages/Software-for-Personal-Computers.aspx You should have completed all the prior video tutorials from LinkedIn Learning. Each of the two computer project assignments are worth 10% of your course grade for a total of 20%. The data file is available via D2L in the current Week module (Excel_assignment_aut20.xlsx). Download this file to start the project. After you download the file, name it LName_FName_Excel1.xlsx. When you are finished, submit your file to D2L Submissions to the proper folder by the due date/time. EntertainmentTech Research Analyst – 60% You have been hired to work as a research analyst at EntertainmentTech. EntertainmentTech is an emerging entertainment consulting firm that provides information to industry executives to help them make better decisions. You are working in the Movies division. Next week, you (and the other 3 analysts) are attending a meeting with your supervisor, Jaimie Dwan. Jaimie is trying to narrow down the plethora of film titles to research to something more manageable. Your specific area of responsibility for this meeting is film profitability and popularity. EntertainmentTech has compiled data on the films released over a five-year period. You will find this data in the “Movie Data” worksheet in the “Excel_assignment_aut20.xlsx” on D2L. “Movie Data” Worksheet Profitability First, we are going to rename some cells to give them more meaning when we write other formulas. It will also make the reference absolute. Click on cell M2, use the namebox to name this cell “Profitability” (this is the same as $M$2) Click on cell M3, use the namebox to name this cell “WorldWideGross” (No spaces) Click on cell M4, use the namebox to name this cell “Popularity” Next, we want to identify the movies that are financially successful. Jaime has yet to define exactly what this means to you. She has said that she wants to be able to change the values to which we will compare (measure) Profitability (you will enter that value in cell M2). A profitability score of 1 means that the film recovered its initial investment. A profitability score of 2 means that the film doubled its initial investment. She wants to be able to adjust this threshold as the meeting progresses. Also, she has said that she wants to be able to adjust the values to which we will compare WorldWideGross (you will enter that value in cell M3). WorldWideGross is the total box office, DVD and streaming sales. In each cell from I2 to I73, we want to enter in a function that tests if a film has a certain level of profitability and reached a certain dollar amount of WorldWideGross. We will be using the AND function. So, enter the following AND function in I2 where you enter in the logical tests for Argument 1 and Argument 2: Logical test 1 will test if the value for Profitability in E2 is greater than the value we enter in M2 (remember we renamed it Profitability). Also, keep in mind the named cells or ranges are absolute references. This is what we need here as we copy this formula down to I73. Logical test 2 will test if the value for worldwide gross sales in G2 is greater than the value we enter in M3 (remember we renamed it WorldWideGross). If both logical tests return TRUE, then the AND function will return TRUE. If either logical test is FALSE, then the AND function returns FALSE. After you have carefully entered the above function in I2, use the fill handle to copy it down the entire range to I73. Double check a couple rows to verify your function. Popularity In each cell from J2 to J73, we want to enter in a function that tests if a film has a certain level of Popularity with either Audience % OR Rotten Tomatoes %. We will be using the OR function. This function is like the AND function. You will enter this one in yourself. Review the video on LinkedIn Learning for some tips. Unlike the AND function, if either logical test return TRUE, the OR function will return TRUE. If both logical tests are FALSE, then the OR function returns FALSE. In other words, we will rate this film as popular if either the Audience OR Rotten Tomatoes percentage is greater than the value, we entered in cell M4. After you have carefully entered the above function in J2, use the fill handle to copy it down the entire range to J73. Double check a couple rows to verify your function. Total movies to research Lastly, we need a running total of the number of movies that are both profitable and popular. This will be the movies that have values of TRUE for profitability (column I) and TRUE for popularity (column J). In column K we want to indicate which movies fit into this category. Use the AND function for this. This will be written like the first AND we wrote above. The difference we will be testing if each of the two cells are equal to TRUE. The value TRUE is a Boolean value. So, do not put TRUE in quotes. This is NOT the entire function. This is just one of the logical tests in the AND function. Write your AND function in cell K2. After you have carefully entered your function in K2, use the fill handle to copy it down the entire range. Double check a couple rows to check your function. We are going keep a count of how many TRUE values we have in column K. This is the number of movies on which the staff will do more extensive research. Keep that running total in cell M6. Use the COUNTIF function. This number will change as the values are adjusted for Profitability, Gross sales, and Popularity. So, enter the following in M6: The COUNTIF function will check the range starting with the first cell in the Cell Range to see if it is equal to the value ???. If it is, it adds 1 to the running count. This will continue for each cell in the range until we get the total number of rows (movies) with the value of ???. Lastly, Jaimie wants a total of no more than 10 movies to research. To make our job a little easier during the meeting, we are going to include a warning in cell M7. Let’s use an IF function to display “OK” if the number of movies is 10 or fewer and “Too Many” if the number is greater than 10. So, we enter the following in M7: The Logical Test (Argument 1) will test if the value of cell M6 is greater than 10. If that is TRUE, it will display “Too Many”, otherwise, if FALSE, “OK” will be displayed. I have formatted the cell M7 to Merge and center across two columns with a green fill. I also used conditional formatting to change the fill color to red, if the cell contains the value “Too Many”. Make the following changes to see this function in action: Profitability: 2: Worldwide Gross: 20 and Popularity: 75. Gross sales by studio and year Next, Jamie wants to have the Worldwide gross sales (we will call this “sales” from now on) numbers for certain Studios but only for a certain year. For example, she might ask “What were the sales for Disney for all films in 2009?” Or “Give me sales for all 20th Century Fox films in 2011?” I have set up a drop-down list in cell P2. If you click in cell P2, you can select the year from a list. This is called a validation list and is used to ensure that correct values are entered in cells. For example, some people might enter in “07” instead of “2007.” Or they might enter in something like, 1823 (which is outside the valid range of years). I have also created a second drop-down list for the studio names in cell P3. Try it out. These are the two parameters for the function we will write in cell P6. Since we want the “Total Worldwide Gross Based on Year and Studio” to appear in cell P6, this is where we will enter in the SUMIFS function. So, we enter the following in P6: Here is how this function runs: first, it tests does H2=P2. If TRUE, it tests does A2=P3. If that is also TRUE, then it adds G2 to the running total (which starts at 0). If either test is FALSE, then it moves to the next row down and repeats. It tests does H3=P2. If TRUE, it tests does A3=P3. If that is also TRUE, then it adds G3 to the running total. If either test is FALSE, then it moves to the next row down and repeats… You are now ready for the meeting! Total Grind Coffee: Assistant Manager - 30% Part of the management training program at Total Grind Coffee requires you to work at the different levels of store operations. After 8 long months, you have just been promoted from Trainee to Assistant Manager. (WARNING! Bad joke ahead!) You might say, “It has been a Total Grind.” The new area manager, Steve Jones, asked you to update his spreadsheet. Steve was recently hired from a competitor, Coffee Café. Go back and watch the video training on VLOOKUPs if you start having trouble. On the “Coffee Sales” worksheet, in column F (start in cell F9), Steve would like you to insert the store manager name for that row. He is planning on making some personnel changes, so he wants you to use a VLOOKUP to insert the correct name from the” Store Manager Directory” on the “LookUp Info” worksheet. That way, he can update the Store Manager Directory one time and not have to change each row on the Coffee Sales worksheet. You have to make the reference to this table array in the VLOOKUP absolute ($A$12:$E$35). If you do not, you will get the “#NA” error in the cells under cell F9 when you copy down this function to cell F88. On the “LookUp Info” worksheet, change the Name of the store manager (StoreMGR) for store 101 (cell B12) to your name. Steve is also giving ratings to each store manager: “A” “B” “C” and “F” for each coffee sales based on the following criteria: “A” (sales above $500) “B” (sales above $400) “C” (sales above $300) “F” (sales equal to or under $300). On the “Coffee Sales” worksheet, use VLOOKUP (approximate match) that will generate the proper grade (A,B,C,F) based on each coffee