Data analysis for Business Operations 401
swearing123
ExcelExercisesVLOOKUP.xlsxEx1
| Course | Course Title | SType | COL | Dept | Delivery | Enrollment | |
| ACCT616 | CPA Exam Review/Financial | LAB | BU | ACFN | |||
| ACCT617 | CPA Exam Overview | LAB | BU | ACFN | |||
| ACCT633 | CPA Exam Prep | LAB | BU | ACFN | |||
| BIOL492 | Departmental Seminar | IND | AS | BIOL | |||
| EDCO191 | Praxis Core Prep | IND | ED | EDCO | |||
| ENGL670 | Grad Research in English | IND | AS | ENGL | |||
| ENGL777 | Thesis Research | IND | AC | ACAF | |||
| CSCI101B | Using Microsoft Excel | LAB | BU | CSQM | |||
| CSCI101C | Using Microsoft Access | LAB | BU | CSQM | |||
| CSCI101D | Advanced Microsoft Excel | LAB | BU | CSQM | |||
| BADM340A | Elective Internship in Bus Adm | IND | BU | MGMK | |||
| BIOL223 | Principles Ecology Evolution L | LAB | AS | BIOL | |||
| BIOL624 | Advanced Ecology Evolution L | LAB | AS | BIOL | |||
| BIOL461 | Academic Internships in Biolog | INT | AS | BIOL | |||
| ENVS461 | Intnshp in Environmental Studi | INT | AS | INDS | |||
| EXSC385 | Exercise Physiology Lab | LAB | ED | PESH | |||
| EXSC481 | Indiv Prog Design & Appl | LAB | ED | PESH | |||
| MCOM461 | Mass Communication Internship | INT | AS | MCOM | |||
| HTXR227 | Anatomy and Terminology | LEC | AS | HNTR | |||
| NUTR601 | Graduate Dietetic Research | IND | AS | HNTR | |||
| PSYC340A | Field Placement in PSYC | INT | AS | PSYC | |||
| SCWK612E | Field Instruction I | FLD | AS | SCWK | |||
| SOCL340A | CCE Internship Program | INT | AS | SOCL | |||
| PSYC555 | Field PSYC | INT | AS | PSYC | |||
| SPMA494 | Sport Management Project | FLD | ED | PESH | |||
| ACCT280 | Intro to Financial Accounting | LEC | BU | ACFN | |||
| ACCT281 | Intro to Managerial Accounting | LEC | BU | ACFN | |||
| ACCT491 | Accounting Internship | INT | BU | ACFN | |||
| ANTH201 | Introd to Cultural Anthropolog | LEC | AS | SOCL | |||
| ARTA695 | Research in Arts Admin I | IND | VP | VPAS | |||
| ARTA696 | Research in Arts Admin II | IND | VP | VPAS | |||
| ARTH444 | Arts Nouveau | LEC | VP | FINE | |||
| BADM491 | Internship in General Business | INT | BU | MGMK | |||
| BADM492 | Internship in Intl Business | INT | BU | MGMK | |||
| College | Enrollment | ||||||
| AS | |||||||
| BU | |||||||
| ED | |||||||
| VP | |||||||
| University | |||||||
Exercise: VLOOKUP and SUMIF 1. Using the VLOOKUP function, find the Delivery information for each course (e.g., WEB, CLASS, HYB25, HYB50, or HYB75) based on the data found in the Registration tab. If the course is not found, the cell should automatically list "Not Found". Hint: Must also use the IFNA function. 2. Using the VLOOKUP function, find the Enrollment figure for each course, based on the data found in the Registration tab. If the course is not found, the cell should automatically list "0". Hint: Must also use the IFNA function. 3. Using the function SUMIF, calculate total enrollment for AS, BU, ED, and VP colleges. Calculate total enrollment for the University. 4. Briefly discuss the results obtained for each of the above activity in the VL Discussions Tab. *Keep answers in this tab. Total Points: 100
VL Discusions
Discussions / Reflections 1. What did the team find useful or challenging about the VLOOKUP function of Excel and why? 2. Discuss in at least 4 sentences how each member of the team plan to use the VLOOKUP function at work or in professional setting.
Registration
| Course | Delivery | Enrollment |
| SCWK632D | WEB | 11 |
| SCWK622E | WEB | 10 |
| SCWK632C | WEB | 9 |
| SPMA392 | CLASS | 14 |
| SPMA494 | CLASS | 8 |
| SCWK622C | WEB | 16 |
| SCWK612E | WEB | 21 |
| SCWK632 | WEB | 12 |
| OUTL351 | HYB75 | 6 |
| INSA599 | HYB75 | 9 |
| MCNR300 | HYB75 | 10 |
| GSTC600 | HYB75 | 11 |
| GSTC603 | CLASS | 22 |
| ENGL670 | CLASS | 10 |
| BADM340A | CLASS | 14 |
| NUTR601 | CLASS | 7 |
| ARTA695 | WEB | 13 |
| BIOL492 | CLASS | 15 |
| CSDV614 | WEB | 12 |
| ARTA696 | WEB | 18 |
| EDCO191 | WEB | 20 |
| ENVS461 | WEB | 8 |
| SPMA496 | CLASS | 11 |
| CSCI491 | CLASS | 18 |
| EXSC496 | CLASS | 16 |
| HDFS594 | WEB | 16 |
| BIOL461 | CLASS | 15 |
| BIOL463 | CLASS | 16 |
| HIST601 | CLASS | 4 |
| HCMT491 | CLASS | 9 |
| PSYC340A | CLASS | 8 |
| PSYC340B | HYB75 | 10 |
| PSYC340C | HYB75 | 4 |
| BADM491 | HYB75 | 10 |
| BADM492 | HYB75 | 19 |
| BADM694 | HYB50 | 18 |
| MGMT491 | HYB25 | 14 |
| MKTG491 | HYB25 | 5 |
| ENTR491 | HYB25 | 14 |
| CSDV612 | WEB | 18 |
| MCOM461 | CLASS | 11 |
| ACCT491 | CLASS | 6 |
| FINC491 | WEB | 16 |
| SOCL340A | WEB | 17 |
| EXSC481 | WEB | 16 |
| EXSC385 | CLASS | 13 |
| CSCI101B | WEB | 11 |
| CSCI101C | WEB | 19 |
| CSCI101D | WEB | 14 |
| ACCT616 | HYB75 | 16 |
| ACCT617 | HYB75 | 12 |
| ACCT618 | HYB75 | 21 |
| BIOL223 | HYB50 | 15 |
| MATH111 | HYB50 | 4 |
| SCWK623 | HYB50 | 15 |
| PSYC510 | CLASS | 6 |
| PSYC618 | CLASS | 21 |
| TESL670 | WEB | 14 |
| SPED561 | WEB | 5 |
| READ290 | WEB | 13 |
| BIOL150 | HYB50 | 19 |
| SCWK604 | HYB50 | 13 |
| SCWK640 | HYB50 | 10 |
| HLTH300 | HYB50 | 8 |
| ANTH201 | HYB50 | 7 |
| WPDC595 | HYB50 | 8 |
| EXSC465 | HYB50 | 17 |
| SPFA685 | HYB50 | 16 |
| MGMT680 | HYB50 | 8 |
| MGMT365 | HYB25 | 7 |
| ARTH351 | HYB25 | 5 |
| EDCO600 | HYB25 | 14 |
| HDFS101 | HYB25 | 20 |
| HDFS350 | HYB25 | 10 |
| MGMT675 | WEB | 6 |
| HMXP102 | WEB | 9 |
| EDCI594 | WEB | 10 |
| EDCI595 | WEB | 7 |
| FINC512 | WEB | 9 |
| SPMA325 | WEB | 14 |
| SPMA240 | WEB | 13 |
| SPMA101 | WEB | 9 |
| NUTR227 | HYB75 | 7 |
| ACCT280 | HYB75 | 6 |
| ACCT281 | HYB25 | 15 |
| READ415 | HYB25 | 13 |
| HIST111 | HYB25 | 8 |
| PHYS250 | HYB25 | 19 |
| Total | 1073 | |
| Category | Count | Enrollment |
| CLASS | 20 | 244 |
| WEB | 30 | 386 |
| HYB50 | 13 | 158 |
| HYB25 | 12 | 144 |
| HYB75 | 13 | 141 |
| Total | 88 | 1073 |
Ex7
| Group Name | Group Segment | Revenue | Expenses | Net Income/Loss | Data | ||||||||
| Postcards Store | Big | 8249.3276698127 | 594.878242008 | 7654.4494278048 | Group Segment | Sum of Expenses | Sum of Revenue | ||||||
| Randles Roadshack | Small | 6557.8729832219 | 83.3393859721 | 6474.5335972499 | Big | 41586390.9696846 | 48945091.5423439 | ||||||
| Examineers Inc | Medium | 6180.0357471981 | 551.2667803915 | 5628.7689668066 | Medium | 2310.7997296187 | 22222.6176752647 | ||||||
| Buttersworth Co | Small | 9915.4543112251 | 336.8576784372 | 9578.5966327879 | Small | 2186.2988659312 | 30444.5522903428 | ||||||
| FASST Trax | Small | 4047.6691780881 | 481.0083008923 | 3566.6608771959 | Grand Total | 41590888.0682801 | 48997758.7123095 | ||||||
| Hula Hoopers | Big | 4797.0875045761 | 962.3550943443 | 3834.7324102318 | |||||||||
| Hehehe | Medium | 6537.8001010167 | 931.9697247837 | 5605.830376233 | |||||||||
| Marleyville Tea | Small | 3151.4227753704 | 848.2135170178 | 2303.2092583526 | |||||||||
| Creepy Lada | Medium | 9504.7818270498 | 827.5632244434 | 8677.2186026064 | |||||||||
| Jokes R Us | Big | 9378.1404699585 | 566.7976536033 | 8811.3428163552 | |||||||||
| Petie's Palace | Small | 6772.1330424373 | 436.8799836118 | 6335.2530588255 | |||||||||
| Ramone's Castle | Big | 48922666.9867 | 41584266.9386946 | 7338400.04800493 |
Exercise 7 Because Ramone's Castle is a high outlier, take it out of the pivot table that is already done. Show Revenue and Expenses by Group Segment (excluding Ramone's Castle). Have "Sum of Revenue" be the left column (you may need to switch the two columns). Keep pivot table in this tab.
