Capstone exercise excel 2016 chapter 1

Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. CMPTR Excel 2016 | Chapter 15: SAM Project 1a Silver Screen Cinemas CREATING AN ADVANCED WORKBOOK Deborah Adu not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Silver Screen Cinemas Standard Tick Event ID PU-0805-1,2 PR-0806-3,4 Date 08/05/16 08/06/16 08/07/16 08/08/16 08/09/16 08/10/16 08/11/16 08/12/16 08/13/16 Screens 1,2 3,4 3,4,5 2,5 6,7 6,7 5,8 5,8 7,8,9 Movie Spacewalk 4 Cross the Range The Great Gamble A Touch of Gold Dry Heat The Perfect Home Minutes in Mississippi Before the Visit Impossible Task 2 Total Event Type Tickets Public 60 Private 90 Special 120 Private 60 Public 90 Public 90 Private 60 Private 60 Special 120 Profit per Tic Ticket Type Standard Tickets VIP Tickets Standard Tickets VIP Tickets Tickets Remaining Sold Tickets 48 60 72 46 32 65 20 31 45 12 30 48 14 58 25 40 29 75 Ticket Price $ $ $ $ $ $ $ $ $ 10 15 25 15 10 10 15 15 25 Total Tickets Remaining Tickets Sold Tickets 15 25 30 15 25 25 15 15 30 6 18 15 11 16 0 9 7 13 9 7 15 4 9 25 6 8 17 Ticket Price $ $ $ $ $ $ $ $ $ 15 25 40 25 15 15 25 25 40 Tickets Sold 54 78 87 57 48 65 29 38 58 Total Revenue Profit per Ticket Type Ticket Type Standard Tickets VIP Tickets Center Revenue Percentage 80% 65% House Staff Required Extra Setup? Silver Screen Cinemas 42 Silver Screen Drive Austin, TX 78718 Info@sscine.example.com 877-530-3643 Contact Information Name Estela Degen Address 1 1883 Corral Park Road Address 2 City Austin State TX Postal Code 78709 Phone Number 543-625-9668 Email Address e.k.degan@example.com Silver Subscriber Yes Event ID Movie 0 The Great Gamble 0 Minutes in Mississippi Date Ticket Type Cost 8/7/2016 VIP $ 40,00 8/11/2016 Standard $ 15,00 Order # Order Date Delivery Option 1853 7/24/2016 Digital Delivery Charges Pick-up $ Digital $ Mail $ Quanity 1,50 2,50 Total 8 $ 320,00 53 $ 795,00 Subtotal Subscriber Discount Delivery Total $ 1.115,00 $ 1.115,00 CMPTR Excel 2016 | Chapter 15: SAM Project 1a Silver Screen Cinemas CREATING AN ADVANCED WORKBOOK GETTING STARTED • Open the file CMPTR_EX16_15a_FirstLastName_1.xlsx, available for download from the SAM website. • Save the file as CMPTR_EX16_15a_FirstLastName_2.xlsx by changing the “1” to a “2”. o • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the file CMPTR_EX16_15a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. You are employed at Silver Screen Cinemas, a luxury movie theater that pairs a full restaurant experience with the big screen. The theater features nine screens and three types of events, as well as a full-sized kitchen and wait staff. Your manager has asked you to make a spreadsheet to keep track of ticket sales and revenue from each film. Switch to the August Movies worksheet. In cell A11, type SP-0807-3,4,5. Select the range A9:A17, and then use Flash Fill to fill the values into the range A12:A17. 2. Clear the conditional formatting rule from the range E9:E17. 3. Because of the limited amount of seating for each screen, it would be helpful to have a visual cue to quickly see when the number of remaining tickets gets low. In the range H9:H17, use conditional formatting to apply Highlight Cells Rules to cells with a value less than 15 with Light Red Fill with Dark Red Text. 4. In the range L9:L17, use conditional formatting to apply Highlight Cells Rules to cells with a value less than 10 with Light Red Fill with Dark Red Text.