Shelly Cashman Excel 2016 | Module 6: SAM Project 1a Fish Chips Electronics CREATING, SORTING, AND QUERYING A TABLE GETTING STARTED • Open the file SC_EX16_6a_FirstLastName_1.xlsx, available for download from the SAM website. • Save the file as SC_EX16_6a_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 SC_EX16_6a_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 STEP 1. Cyril Netreba works at Fish Chips Electronics, which sells inexpensive computers (called “Fish Chip systems”) to schools to help teach computer science and electrical engineering. Cyril wants to summarize some information on Fish Chips Electronics’ product data into tables to help him train a new sales associate. Switch to the Systems worksheet. Select the range A1:H9 and format the range as a table (with headers), using the Table Style Medium 14. (Hint: Depending on your version of Office, the Table style may appear as Green, Table Style Medium 14.) Use SystemsTable as the name of the table. 2. Fish Chips Electronics approved a new third-party sensor for use with the Fish Chip Systems. Switch to the Sensors worksheet. Enter a new record into the end of the SensorsTable table (in row 15) as shown in Table 1 below: Table 1: New SensorsTable Record Sensor ID Sensor Name Fish Chip Classic Compatible Fish Chip 2 Compatible Fish Chip 3 Compatible Cost Shelly Cashman Excel 2016 | Module 6: SAM Project 1a S487 Adi Humidity Sensor Yes Yes No $5.50 3. Sort the SensorsTable table from smallest to largest by the Cost field values. 4. On the Sensors worksheet, Cyril wants to display only sensors that are compatible with the Fish Chip 3 systems below the SensorsTable data. Use an advanced filter to copy all the records for sensors compatible with the Fish Chip 3 systems from the SensorTable into a new range as described below: a. Enter Yes into cell E19 to set up the value to filter on in the criteria range. b. In the Advanced Filter, use the range A1:F15 as the List range, the range A18:F19 as the Criteria range, and copy the records to the range A21:F21. (Hint: 10 records will be copied from the SensorsTable data.) 5. Switch to the Displays worksheet. Apply Wrap Text formatting to the range E1:G1. 6. Remove the duplicate record in the DisplaysTable table. 7. LED displays are the most popular displays sold by Fish Chips Electronics and Cyril wants to highlight these displays in the table. Filter the table to display only those records with a Type column value of LED. 8. At the beginning of the year, Fish Chips Electronics makes a bulk order of cases for each system they sell. Cyril wishes to show his sales associate a snap-shot of the number of cases that were ordered, sold, and are available at the warehouse for each Fish Chip system. Cyril already sorted the table containing this data, he just needs to insert subtotals into the table. Switch to the Cases Subtotal worksheet. Convert the CasesSubtotal table (in the range A1:I24) into a range. 9. 10. After converting the table to a range, insert subtotals into the range A1:I24 using the following options: a. Subtotals should be inserted at each change in the System Compatibility value. b. The subtotals should use the Sum function. c. Subtotals should be added to the 2018 Inventory, Cases Sold, and Cases Available values only. d. The subtotals should replace current subtotals and include a summary below the data. Cyril wishes to highlight some additional data on the cases sold by Fish Chips Electronics. He first wants to identify how many options are available for each type of case. Switch to the Cases worksheet and complete the following steps to the identify how many case options are available for each case type. Shelly Cashman Excel 2016 | Module 6: SAM Project 1a 11. 12. a. In cell C2, enter a formula using the COUNTIF function that counts the number of cases with the Unibody case type. Use Unibody as the criteria argument, and CasesTable[Body Type] as the range argument in your formula. b. In cell C3, enter a formula using the COUNTIF function that counts the number of cases with the 2-Piece case type. Use 2-Piece as the criteria argument, and CasesTable[Body Type] as the range argument in your formula. c. In cell C4, enter a formula using the COUNTIF function that counts the number of cases with the Compact case type. Use Compact as the criteria argument, and CasesTable[Body Type] as the range argument in your formula. Complete the following steps to determine the total number of cases were sold for each body type. a. In cell E2, enter a formula using the SUMIF function that totals the value in the Cases Sold column for each case with a Unibody case type. Use Unibody as the criteria argument, CasesTable[Body Type] as the range argument, and CasesTable[Cases Sold] as the sum_range argument in your formula. b. In cell E3, enter a formula using the SUMIF function that totals the value in the Cases Sold column for each case with a 2-Piece case type. Use 2Piece as the criteria argument, CasesTable[Body Type] as the range argument, and CasesTable[Cases Sold] as the sum_range argument in your formula. c. In cell E4, enter a formula using the SUMIF function that totals the value in the Cases Sold column for each case with a Compact case type. Use Compact as the criteria argument, CasesTable[Body Type] as the range argument, and CasesTable[Cases Sold] as the sum_range argument in your formula. In the range H8:H30 (the Cases Sold column), create a new Icon Set conditional formatting rule as described below: a. Use the 3 Signs icon set in your conditional formatting rule. b. Display the green, circular icon in all cells with a Number type value greater than or equal to 60. c. Display the yellow, triangular icon in all cells with a Number type value less than 60 and greater than or equal to 25. d. Display the red, diamond icon in all cells with a Number type value less than 25. 13. Apply a custom sort on multiple fields to the CasesTable, so that the table is first sorted by the System Compatibility field in descending (Z to A) order, then by the Body Material field in ascending (A to Z) order, and finally by the Body Type field in ascending order. 14. Add a Total Row to the CasesTable. (Hint: The total row should appear in row 31 with a total for Cases Sold automatically appearing in cell H31.) Using the total row, display the SUM of the 2018 Inventory in cell G31. Shelly Cashman Excel 2016 | Module 6: SAM Project 1a 15. Upon review of the CasesTable, Cyril notices the Cases Available column needs to be added to the table. Add a calculated column to the CasesTable (in the range A7:H30) as described below: a. In cell I7, enter the text Cases Available as the calculated column’s heading. b. In cell I8, enter a formula without a function using structured references that subtracts the value in the Cases Sold column from the value in the 2018 Inventory column. Use [2018 Inventory] and [Cases Sold] as the structured references in your formula. The formula should autofill into the range I8:I30. If it does not, copy the formula into the range. c. 16. Using the table’s total row (in row 31), SUM the values in the Cases Available column. Fish Chips Electronics also sells packaged sets that combine popular Fish Chip systems with an LED display, a case, and a simple project guide. The PackageTable on the Packages worksheet identifies the system and display included in each package by ID number rather than by name. Cyril wants to set up a simple lookup table to make it easier for his new sales associate to identify what is included in each package. Switch to the Packages worksheet. In cell H3, create a formula using the VLOOKUP function to determine the type of system included with the package using the following parameters: 17. 18. a. The formula should use cell J3 (the System ID) as the lookup_value, the range G7:H12 as the table_array, and 2 as the col_index_num. b. Leave the optional range_lookup parameter blank. In cell H5, create a formula using the VLOOKUP function to display the name of the package using the following parameters: a. The formula should use cell H2 (the Package ID) as the lookup_value, the table named PackagesTable as the table_array, and 4 as the col_index_num. b. Leave the optional range_lookup parameter blank. Cyril also wants to highlight the number of packages associated with the latest iteration of the Fish Chip System – the Fish Chip 3 Mod C+. In cell H16, create a formula using the DCOUNT function to count the number of packages associated with the Fish Chip 3 Mod C+ systems, using the range A1:D22 as the database, “System ID” as the field, and the range H14:H15 as the criteria. 19. In cell H17, create a formula using the DAVERAGE function to determine the average cost of a Fish Chip 3 Mod C+ package, using using the range A1:D22 as the database, “Cost” as the field, and the range H14:H16 as the criteria. Your workbook should look like the Final Figures on the following pages. Save your changes, close the document, and then exit Excel. Follow the directions on the SAM website to submit your completed project. Shelly Cashman Excel 2016 | Module 6: SAM Project 1a Final Figure 1: Systems Worksheet Final Figure 2: Sensors Worksheet Shelly Cashman Excel 2016 | Module 6: SAM Project 1a Final Figure 3: Displays Worksheet Final Figure 4: Cases Subtotal Worksheet Shelly Cashman Excel 2016 | Module 6: SAM Project 1a Final Figure 5: Cases Worksheet Final Figure 6: Packages Worksheet 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 t SAM website. Shelly Cashman Excel 2016 | Module 6: SAM Project 1a Fish Chips Electronics CREATING, SORTING, AND QUERYING A TABLE Latrell Veney edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. System ID 1000 2013 3013 3014 4034 4035 4036 4037 System Name Fish Chip Classic Fish Chip 2 Fish Chip 2+ Fish Chip 2 Vinegar Fish Chip 3 Mod A Fish Chip 3 Mod B Fish Chip 3 Mod C Fish Chip 3 Mod C+ Processor Speed 300 MHz 600 MHz 600 MHz 900 MHz 900 MHz 1.2 MHz 1.2 MHz 1.5 MHz RAM 500 Mb 1 GB 2 GB 2 GB 1 GB 2 GB 2 GB 2 GB Power Source 1.0 Amp 1.5 Amp 1.5 Amp 1.5 Amp 2.0 Amp 2.0 Amp 2.5 Amp 2.5 Amp USB Ports 2 3 3 3 3 4 4 4 Bluetooth No No Yes Yes No Yes Yes Yes Wi-Fi Ready No No No Yes Yes Yes Yes Yes Sensor ID S103 S203 S304 S345 S378 S397 S399 S400 S401 S402 S414 S321 S357 Sensor Name Roger Temperature Sensor Humidity Sensor (wired) Temperature-Humidity Sensor Triple-Axis Accelerometer Light Sensor (Photo Transistor) Microphone (Omnidirectional) UV Index/IR/Visible Light Sensor (Digital) Venti Muscle Sensor Barcode Scanner Marcos Ultrasonic Rangefinder Boraks Ultrasonic Rangefinder Luminosity Sensor Double-Axis Accelerometer Sensor ID Sensor Name Sensor ID Sensor Name Fish Chip Classic Compatible Yes Yes No No No No No No No Yes No No Yes Fish Chip 3 Compatible Sensors Fish Chip Classic Compatible Fish Chip Classic Compatible Fish Chip 2 Compatible Yes Yes No Yes Yes Yes No Yes Yes Yes Yes Yes Yes Fish Chip 2 Compatible Fish Chip 2 Compatible Fish Chip 3 Compatible Yes No Yes Yes Yes Yes Yes Yes Yes No Yes No Yes Cost $5.50 $8.00 $14.95 $10.50 $1.25 $2.00 $9.95 $40.50 $65.95 $20.95 $50.95 $6.00 $8.50 Fish Chip 3 Compatible Cost Fish Chip 3 Compatible Cost Display ID D4431 D1116 D7500 D4132 D9124 D9651 D9770 D2305 D7554 D1809 D1519 D9651 Display Name 1.3" OLED - Monochrome 1.3" LED - Green on Black 1.3" Touchscreen 1.4" LED - Square - Backlit 1.4" LED - Blue on White 1.8" Touchscreen 1.4" LED - Backlit 1.4" LED - Square - Green on Black 10.1" HDMI (No Touchscreen) 7" HDMI (No Touchscreen) 7" HDMI + Touchscreen 1.8" Touchscreen Size 1.3" 0.96" 3.5" 1.4" 1.4" 1.8" 1.4" 1.4" 10.1" 7" 7" 1.8" Type OLED LED Touchscreen LED LED Touchscreen LED LED HDMI HDMI HDMI - Touchscreen Touchscreen Fish Chip Classic Compatible Fish Chip 2 Compatible Fish Chip 3 Compatible Cost Yes Yes Yes $ 24.50 Yes Yes Yes $ 5.50 No Yes Yes $ 40.95 Yes Yes Yes $ 10.95 Yes Yes Yes $ 12.95 No Yes Yes $ 20.95 Yes Yes Yes $ 10.95 Yes Yes Yes $ 8.95 No No Yes $ 150.00 No No Yes $ 45.50 No No Yes $ 175.00 No Yes Yes $ 20.95 Case ID C9257 C2496 C6235 C7656 C3411 C6617 C9894 C4329 C9517 C6557 C6247 C4587 C9956 C4241 C6882 C2914 C9494 C7697 C3765 C8726 C1289 C3933 C3418 System Compatibility Fish Chip Classic Fish Chip Classic Fish Chip Classic Fish Chip Classic Fish Chip 2 Fish Chip 2 Fish Chip 2 Fish Chip 2 Fish Chip 2 Fish Chip 2 Fish Chip 2 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Body Material Aluminum Plastic Plastic Plastic Aluminum Plastic Plastic Plastic Plastic Plastic Plastic Aluminum Aluminum Aluminum Aluminum Plastic Plastic Plastic Plastic Plastic Plastic Plastic Plastic Body Type Unibody Unibody 2-Piece 2-Piece Unibody 2-Piece Unibody Unibody 2-Piece Unibody 2-Piece Unibody 2-Piece Unibody 2-Piece 2-Piece 2-Piece Compact Unibody 2-Piece 2-Piece Unibody Compact Color Standard Clear Blue Translucent Green Standard Smoke Grey Blue Green Translucent Smoke Grey Red Translucent Orange Standard Standard Standard Standard Rainbow Translucent Green Clear Pink White Translucent Black Translucent Black Black Mounting Plate No No No No No Yes Yes Yes Yes Yes No Yes No No Yes Yes Yes Yes Yes Yes Yes Yes Yes 2018 Inventory 50 25 25 25 75 100 50 100 50 100 50 100 50 150 150 50 50 150 50 100 75 50 200 Cases Sold 28 17 13 10 37 36 28 33 28 53 15 37 31 58 94 37 35 106 32 37 56 36 81 Cases Available 22 8 12 15 38 64 22 67 22 47 35 63 19 92 56 13 15 44 18 63 19 14 119 Case Types Unibody 2-Piece Compact Case ID C2496 C9257 C6617 C3411 C6235 C7656 C9894 C4329 C9517 C2914 C4587 C9494 C7697 C3765 C9956 C4241 C6882 C8726 C1289 C3933 C3418 C6557 C6247 System Compatibility Fish Chip Classic Fish Chip Classic Fish Chip 2 Fish Chip 2 Fish Chip Classic Fish Chip Classic Fish Chip 2 Fish Chip 2 Fish Chip 2 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 3 Fish Chip 2 Fish Chip 2 Body Material Plastic Aluminum Plastic Aluminum Plastic Plastic Plastic Plastic Plastic Plastic Aluminum Plastic Plastic Plastic Aluminum Aluminum Aluminum Plastic Plastic Plastic Plastic Plastic Plastic 2018 Inventory 750 725 350 Body Type Unibody Unibody 2-Piece Unibody 2-Piece 2-Piece Unibody Unibody 2-Piece 2-Piece Unibody 2-Piece Compact Unibody 2-Piece Unibody 2-Piece 2-Piece 2-Piece Unibody Compact Unibody 2-Piece Cases Sold Color Clear Standard Smoke Grey Standard Blue Translucent Green Blue Green Translucent Smoke Grey Rainbow Standard Translucent Green Clear Pink Standard Standard Standard White Translucent Black Translucent Black Black Red Translucent Orange Cases Available 750 725 350 Mounting Plate No No Yes No No No Yes Yes Yes Yes Yes Yes Yes Yes No No Yes Yes Yes Yes Yes Yes No 2018 Inventory Cases Sold 25 50 100 75 25 25 50 100 50 50 100 50 150 50 50 150 150 100 75 50 200 100 50 17 28 36 37 13 10 28 33 28 37 37 35 106 32 31 58 94 37 56 36 81 53 15 Package ID System ID Display ID Cost P12018 4037 D2305 $ 72.90 P12084 4037 D1116 $ 69.45 P12085 4037 D9770 $ 74.90 P12113 4035 D2305 $ 66.45 P12167 4034 D4132 $ 65.90 P12292 4035 D4132 $ 68.45 P12367 4036 D2305 $ 69.45 P12381 4034 D2305 $ 63.90 P12382 4034 D9770 $ 65.90 P12414 3014 D4132 $ 63.95 P12574 4036 D9770 $ 71.45 P12614 4037 D9124 $ 76.90 P12617 4035 D9124 $ 70.45 P12618 4036 D4132 $ 71.45 P12687 4036 D1116 $ 66.00 P12777 4035 D9770 $ 68.45 P12823 4037 D4132 $ 74.90 P12879 4036 D9124 $ 73.45 P12913 4034 D1116 $ 60.45 P12959 4034 D9124 $ 67.90 P12970 4035 D9770 $ 68.45 Package Details Package ID P12687 System Name System ID Display Name 1.3" LED - Green on Black 4036 Display ID D1116 Cost System ID System Name Display ID 3014 Fish Chip 2 Vinegar D9770 4034 Fish Chip 3 Mod A D2305 4035 Fish Chip 3 Mod B D4132 4036 Fish Chip 3 Mod C D9124 4037 Fish Chip 3 Mod C+ D1116 System Name Fish Chip 3 Mod C+ Number of Fish Chip 3 Mod C+ Packages Average Cost of Fish Chip 3 Mod C+ Packages System ID 4037 Display Name 1.4" LED - Backlit 1.4" LED - Square - Green on Black 1.4" LED - Square - Backlit 1.4" LED - Blue on White 1.3" LED - Green on Black Shelly Cashman Excel 2016 | Module 7: SAM Project 1a Antioch Sports Center IMPORTING DATA AND WORKING WITH SMARTART, IMAGES, AND SCREENSHOTS GETTING STARTED • Open the file SC_EX16_7a_FirstLastName_1.xlsx, available for download from the SAM website. • Save the file as SC_EX16_7a_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. To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer: o Support_SC_EX16_7a_Baseball.png o Support_SC_EX16_7a_Basketball.png o Support_SC_EX16_7a_Prime.docx o Support_SC_EX16_7a_Soccer.png o Support_SC_EX16_7a_Stillman.accdb o Support_SC_EX16_7a_Volleyball.png o Support_SC_EX16_7a_TonyBrown.txt With the file SC_EX16_7a_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. Lindsey Bates works part-time at the Antioch Sports Center, which is evaluating whether to merge with another sports center in the area. One of Lindsey’s assignments is to combine the data from each sports center into a single workbook, format the data, and create graphics to make the data easier to analyze. Go to the Candidates worksheet. Insert a SmartArt graphic using the Continuous Picture List layout (in the Picture section of the SmartArt Graphic dialog box) to include an eye-catching graphic that identifies the merger candidates. Shelly Cashman Excel 2016 | Module 7: SAM Project 1a 2. 3. 4. 5. 6. 7. 8. 9. Using the support files available for download from the SAM website, insert pictures into the SmartArt graphic’s picture placeholders as follows: a. Insert Support_SC_EX16_7a_Baseball.png into the first shape. b. Insert Support_SC_EX16_7a_Basketball.png into the second shape. c. Insert Support_SC_EX16_7a_Soccer.png into the third shape. Add text to the SmartArt graphic to identify the merger candidates as follows: a. Type Prime Sports into the first text placeholder. b. Type Tony Brown into the second text placeholder. c. Type Northside into the third text placeholder. Change the format of the SmartArt graphic to coordinate with the Antioch Sports Center logo as follows: a. Change the colors to Colorful Range - Accent Colors 5 to 6 (5th option in the Colorful section of the Change Colors gallery). b. Apply the White Outline SmartArt style. Update the SmartArt graphic as follows to include a fourth merger candidate: a. Add a new shape to the right side of the SmartArt graphic. b. In the new shape, insert the file Support_SC_EX16_7a_Volleyball.png. c. Type Stillman Sports in the new shape below the volleyball picture. Improve the appearance of the Candidates worksheet as follows: a. Move the SmartArt graphic so its upper-left corner is within cell B3. b. Resize the SmartArt graphic so its lower-right corner is within cell J15. c. Remove the worksheet gridlines from view. Go to the Prime Sports worksheet, and then arrange the data as follows: a. Copy the range I3:N8 to the Office Clipboard. b. Paste the data from the range I3:N8 into cell B3, transposing the data when you paste. c. Delete the contents of the range I3:N8. The Prime Sports Center has provided sales data for the last five years in a Word table. Insert the data into the Prime Sports worksheet as follows: a. Open the Support_SC_EX16_7a_Prime.docx Word document. b. Select the first six columns of the Financial Information table, copy the data to the Office Clipboard, paste the Word data into cell B11 of the Prime Sports worksheet, matching the destination formatting, and then close the Word file. Use the Format Painter to copy the formatting of the range B11:G16 to the range B3:G8 in the Prime Sports worksheet. Shelly Cashman Excel 2016 | Module 7: SAM Project 1a 10. 11. 12. The Tony Brown Sports Center has provided sales data for the last five years in a text file. Insert the data into the Tony Brown worksheet as follows: a. Go to the Tony Brown worksheet, and then import data from the Support_SC_EX16_7a_TonyBrown.txt text file. b. Specify that the data in the text file has headers. c. Also specify that the data is delimited only with a comma. d. Import the data into cell B3. Organize the historical data in the Tony Brown worksheet as follows: a. Use the Convert Text to Columns Wizard to convert the text in the range B12:B15 to columns. b. Use only a space as the column delimiter. (Hint: Click OK to replace the data if the warning message comes up.) c. Change the width of columns B:G to 12.0 characters. Add totals to the historical data as follows: In cell C16, create a formula using the SUM function that total the values in the range C12:C15. Copy the formula in cell C16 to the range D16:G16. 13. To make the Northside worksheet consistent with the other worksheets, go to the Northside worksheet and replace all instances of the word Quarter with the letter Q (Hint: you should make four replacements). 14. To help analyze Northside’s historical data, select the range C12:G15 and apply a Data Bars conditional formatting rule using Solid Fill Blue Data Bars. 15. Make the bar chart in the Northside Revenue Chart worksheet more attractive as follows: a. Change the chart style to Style 3. b. Format the data series to vary colors by point. (Hint: In the Format Data Series task pane, display the Fill options.) 16. The Stillman Sports Center provided sales data in an Access database. In cell B11 of the Stillman worksheet, import data from the Sales table in the Support_SC_EX16_7a_Stillman.accdb Access database file. 17. To make the data in the Stillman worksheet consistent with the data for the other sports centers, format the data and the worksheet as follows: 18. a. Convert the imported Access table to a range. b. Change the width of columns B:G to 12.0 characters. c. Apply the Accounting number format with zero decimal places and $ as the symbol to the range C4:G8. (Hint: Depending on how you perform this step, the number format may be displayed as Custom.) d. Use the Format Painter to copy the formatting of the range B2:G8 to the range B10:G16. To help analyze the Stillman Sports Center’s sales in 2020, create a chart as follows: Shelly Cashman Excel 2016 | Module 7: SAM Project 1a 19. a. For the range B3:F7, insert a Clustered Bar chart with each quarter as a data series. b. Move the clustered bar chart to a new chart sheet named 2020 Stillman Chart. (Hint: Don’t type the period.) c. Move the 2020 Stillman Chart worksheet after the Stillman worksheet. To coordinate the new chart with the Northside revenue chart, make the following changes to the 2020 Stillman chart: a. Change the chart style to Style 3. b. Add a Primary Horizontal Axis Title using Sales as the title. c. Use Stillman Sports Center as the chart title. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. Final Figure 1: Candidates Worksheet Shelly Cashman Excel 2016 | Module 7: SAM Project 1a Final Figure 2: Prime Sports Worksheet Final Figure 3: Tony Brown Worksheet Shelly Cashman Excel 2016 | Module 7: SAM Project 1a Final Figure 4: Northside Worksheet Final Figure 5: Northside Revenue Chart Shelly Cashman Excel 2016 | Module 7: SAM Project 1a Final Figure 6: Stillman Worksheet Final Figure 7: 2020 Stillman Chart Shelly Cashman Excel 2016 | Module 7: SAM Project 1a Antioch Sports Center IMPORTING DATA AND WORKING WITH SMARTART, IMAGES, AND SCREENSHOTS Author: Latrell Veney 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. ew copy of the Merger Candidates Prime Sports - 2020 Sales Prime Sports - Historical Sales Sales Events Leagues Memberships Programs Total Q1 $ 6,500 $ 7,000 $ 36,000 $ 42,000 $ 91,500 Q2 $ 7,750 $ 7,000 $ 39,000 $ 41,500 $ 95,250 $ $ $ $ $ Q3 7,450 8,550 45,550 49,800 111,350 Q4 $ 8,000 $ 9,750 $ 47,500 $ 50,750 $ 116,000 Total $ 29,700 $ 32,300 ######## ######## ######## Tony Brown - 2020 Sales Tony Brown - Historical Sales Sales Q1 18550 Q2 25455 Q3 30650 Q4 44500 Total 2016 2017 $ $ $ $ 2018 43,760 44,750 50,500 52,900 $ $ $ $ 55,000 51,800 53,900 55,500 2019 $ $ $ $ 2020 56,500 55,000 59,300 62,750 $ $ $ $ 61,900 65,750 67,300 70,750 Northside Sports - 2020 Sales Sales Q1 Q2 Q3 Q4 Total $ $ $ $ $ Events 7,150 8,085 6,116 7,425 28,776 $ $ $ $ $ Leagues Memberships Programs 3,250 $ 11,700 $ 10,400 3,675 $ 13,230 $ 11,760 2,780 $ 10,008 $ 8,896 3,375 $ 12,150 $ 10,800 13,080 $ 47,088 $ 41,856 Total $ 32,500 $ 36,750 $ 27,800 $ 33,750 $ 130,800 Northside Sports - Historical Sales Sales Quarter1 Quarter2 Quarter3 Quarter4 Totals $ $ $ $ $ 2016 26,500 22,700 21,600 27,700 98,500 2017 $ 25,900 $ 30,000 $ 25,000 $ 26,500 $ 107,400 2018 $ 27,000 $ 28,500 $ 23,575 $ 27,000 $ 106,075 2019 $ 29,750 $ 32,550 $ 28,750 $ 31,500 $ 122,550 2020 $ 32,500 $ 36,750 $ 27,800 $ 33,750 $ 130,800 Northside Sports Center 2020 2019 2018 2017 2016 $- $20,000 $40,000 $60,000 $80,000 Sales $100,000 $120,000 $140,000 Stillman Center - 2020 Sales Sales Q1 Q2 Q3 Q4 Total Events Leagues Memberships Programs 11594 5270 18972 16864 14091 6405 23058 20496 12490.5 5677.5 20439 18168 13211 6005 21618 19216 51386.5 23357.5 84087 74744 Total 52700 64050 56775 60050 233575 Stillman Center - Historical Sales Total 0 0 0 0 0
Purchase answer to see full attachment