Exp19_Excel_Ch11_HOEAssessment_Accountants

Exp19 Excel Ch11 HOEAssessment Accountants

Excel Chapter 11 Hands-On Exercise Assessment – Accountants

Project Description:

You work for a tax accounting firm that has offices in San Diego, Dallas, Miami, and Albany. Previously, you downloaded data from the employee database. Now you will use text, database, and lookup functions to obtain the results you want.

Start Excel. Download and open   the file named Exp19_Excel_Ch11_HOEAssessment_Accountants.xlsx.   Grader has automatically added your last name to the beginning of the   filename.

Your first step is to create a   unique ID for each accountant using the year hired, date hired, and number.
 

  In cell D2, enter 2007-1018-10 and use Flash Fill to complete the pattern to   create the remaining IDs in the range D3:D26.

The accountants’ full names are   located in column E. You want to separate the first and last names.
 

  Select the range E2:E26 and convert text to columns using the space as the   delimiter. Change cell E1 to First Name.

Next, you want to display the   accountants’ names with the last name and first name, separated by a comma.
 

  In cell G2, insert the TEXTJOIN function to join the last name and first name   for the first accountant, using a comma and space as the delimiter. The name   should display as Adams, Camille.   Copy the function to the range G3:G26.

Column J contains the phone   numbers. You want to extract the area codes.
 

  In cell K2, use the MID function to extract the area code for the phone   number for the first employee. The Start_num argument should be the position   of the first digit in the area code to avoid including the opening   parenthesis. Make sure the function extracts the three-digit area code. Copy   the function to the range K3:K26.

The Location column displays the   city names in all capital letters. Because this is hard to read, you will use   a text function to display the cities in upper and lowercase.
 

  In cell M2, use the PROPER function to display the first city name in upper   and lowercase. Copy the function to the range M3:M26.

You want to create a criteria   range to perform an advanced filter. The criteria are (1) Tax Accountants in   Miami and (2) Tax Accountants in San Diego.
 

  Copy the range A1:M1 and paste it in the range A28:M28. Enter Tax Accountant and Miami in the respective cells on row 29. Enter Tax   Accountant and San Diego in the respective cells on row   30.

Now you are ready to perform the   advanced filter by copying the data below the criteria range.
 

  Click within the dataset. Select A1:M26 as the list, the criteria range you   defined in the previous step, and A32:M32.

Next you want to calculate the   total salaries for all Tax Accountants in Miami and San Diego.
 

  In cell P2, insert the DSUM function using the dataset, column heading Salary, and the defined criteria   range.

Next, you will calculate the   average salary for Tax Accountants in Miami and San Diego.
 

  In cell P3, insert the DAVERAGE function to calculate the average salary   using the column heading Salary.

You want to identify the highest   salary for Tax Accountants in Miami and San Diego.
 

  In cell P4, insert the DMAX function.

Next, you want to identify the   lowest salary of Tax Accountants in Miami and San Diego.
 

  In cell P5, insert the DMIN function.

Finally, you want to count the   number of Tax Accountants in Miami and San Diego.
 

  In cell P6, insert the DCOUNT function.

The range O8:P9 contains a new   set of criteria to identify the one Senior Accountant in San Diego. You want   to obtain that person’s salary.
 

  In cell P11, insert the DGET function.

The range O13:P16 is designed to   look up a person’s name to return the position number and salary for that   person. First, you will look up the person’s location within the dataset.
 

  In cell P15, insert the MATCH function to look up the name in cell P14 and   return that person’s position within the Last Name column. Use only the range   containing the actual last names in the argument.

Now that you have the position   number for the accountant, you are ready to identify that person’s salary.
 

  In cell P16, insert the INDEX function using the range F2:M26 as the range   and the position number identified by the MATCH function.

In cell O19, insert the   FORMULATEXT function to display the formula that is stored in cell P15. Copy   the function to cell O20.

Create a footer with your name   on the left side, the sheet tab code in the center, and the file name code on   the right side of the worksheet.

Save and close Exp19_Excel_Ch11_HOEAssessment_Accountants.xlsx.   Exit Excel. Submit the file as directed.

1) Project goals.
2) Database description: A general description of the database, what data is stored in the database.
How the database will benefit the users.
3) Data model & design:
o Completed ER/EER diagram, 5 tables minimum (8 tables in case the group has 2
members)
 MUST use https://www.draw.io to draw the ER diagram, and then take a
snapshot.
 Include a snapshot of the diagram in the report.
o Business rules.
o Data dictionary: For each one of the tables, list columns, data types, column restrictions,
column descriptions.

4) Implementation:
o Use MySQL Server and MySQL Workbench to create the database/tables.
 Include a snapshot of the SQL code in your report
o Populate the each table with a minimum of 20 rows of sample data. Make sure the
entered data is descriptive (not just random numbers and letters.
 Include a snapshot of the SQL code in your report.
o Use MySQL Server and MySQL Workbench to write/run the SQL queries below. For
each query, provide the following in Report 2:
  Snapshot of the code and the output in the report.  
 In about 2 lines, explain what the query returns.
Queries:
 1 trivial query. Simple select with ordering.
 2 medium difficulty queries. Queries that use composite condition for selection,
computations, aggregate function and grouping.  
 1 query that uses subquery.
 2 queries that uses join (1 inner join, 1 left or right outer join).
 1 view (query must use join – hint: you can use one of the queries from the
previous question).
 1 query that uses union.
 1 custom stored function.
 1 custom stored procedure. 

 ENTITIES WITH ATTRIBUTES

 CHILDREN: Id, First Name, Last Name ,Age, Gender, Instructor Id,  Guardian Number PROFESSOR : Id, First Name,  Last Name,  Home Address, Department,  Office Hours, Office Phone

 ACCOUNTING: Date,  Rental fee,  Electricity,Servicing , Tax paid ,Earning , Worker’s salary , Contribution

 HEALTH SECTOR: Children Id,  Sickness,  Disabled,  Hypersensitive,  Vaccination,  Health Records,  Prescription 

CATALOG: Books, Cardboard, Ink-pen, Highlighter,  pencil

  List of the 10 questions 

1. Display all the columns and rows of the children’s table whose first name starts with the letter B for every class. 

2. Display all the columns from the professor table whose office phone starts with 6.

 3. Display how many children have each professor has. 

4. Display worker’s salary and electricity bill of 2020. 

5. Display Guardian Number of the children with prescription. 

6. Display the name of the children with disabilities whose names start with c, d, and f.

 7. Display the name of the children who are healthy and have no medical history. 

8. Display the name of the children who have hypersensitive. 

9. Display the name of the children who are using cardboard and have medical prescriptions. 

10. Display tax paid and contribution.  

Can someone do these problems please?

This discussion topic is to be reflective of the course material.  Therefore, use your own words to respond to the following questions:

1. What other topics would you have liked to have covered in this course?

2. What reading did you find most interesting and why?

3. How has this course changed your perspective or outlook moving forward?

4. Any other topic of interest that you would like to add.

Link to video to react to: https://www.nfb.ca/film/ryan/

Consider and explain how animation creates meaning in this piece. The use of 3-D animation expresses a specific aspect of this narrative that isn’t told through words. Also consider color and texture in this context

Most of the animation takes place in a mirrored space (you will notice this if you pay attention to the backgrounds). Why do you think this is? How does this formal choice create meaning?

3 pages please double spaced

Need you to adequately follow the RUBRIC guidelines in responding to this assignment. Thanks 

Part 3: Analyzing Malicious Windows Programs (Lab 7.1 from PMA)

Complete all the steps mentioned in the below attached document for part 3 of this project:

Project part 3_ Analyzing Malicious Windows Programs.pdf

Part 4: Analyzing Code Constructs in Malware (Lab 6.1 from PMA)

Part 4 of this project is based on Lab 6-1 in “Practical Malware Analysis” textbook chapter 6.

Based on the knowledge gained in all previous lab assignments, you will have to complete lab 6-1

individually (with minimal or no supervision) by following the instructions given in Lab 6-1 in the

textbook. There are more detailed solutions in the back of the book.

1. Open and analyze the malware found in the file Lab06-01.exe using IDA Pro.

2. Answer all the questions (Q1 to Q3) found in Lab 6-1 in your own words.

3. List all the steps you followed in setting up the software environment and the screenshots captured

while analyzing the malware in IDA Pro (Hint: The steps that you list for Lab 6-1 should be something

similar to the steps that were given to you in all previous lab assignment instructions).

Submission Requirements for all four parts of the project:

Format: Microsoft Word

Font: Arial, 12-Point, Double-Space

Citation Style: APA

Length: Each part should have a minimum of 3 pages. So overall report size should be a minimum

of 6 pages (excluding title page and bibliography).

Exp19_Access_Ch01_CapAssessment – Latte Delights Inventory 1.0

Exp19 Access Ch01 CapAssessment Latte Delights Inventory 1.0

EX19_AC_CH01_GRADER_CAP_AS

Project Description:

In this project, you will open a database containing the inventory of a coffee shop, create a new table in which to store sales representatives (reps) information, and then add the sales rep records. You add a new product using a form and print a report. You apply a filter by selection and sort a query, and then you apply a filter by form to a table. You save both filters.

Steps to Perform:

Step

Instructions

Points    Possible

1

Start   Access. Open the downloaded Access file named Exp19_Access_Ch01_CapAssessment_Latte_Delights_Inventory.accdb.   Grader has automatically added your last name to the beginning of the   filename.

0

2

Add the following records to the Sales Reps table,   letting Access assign the SalesRepID:

LastName

FirstName

Address

City

State

ZIP

Eissler

Karean

13 Oak Lane

Montgomery

PA

17752

Rai

Namrata

1000 Ogden Blvd

New Castle

DE

19720

Pellum

Katryn

286 Bergen Rd

Salford

PA

18957

  Close the table.

16

3

Open   the Customers form in Form view. Use Search to locate the record for Baker Auto   Supply. Modify   the record so that the Address1 field displays 4508 N.W. 7 Avenue. Save and close the form.

12

4

Open the Products form in Form view. Add a new   product (Access will automatically add the Product ID): Coffee-Arabica   Premium. The   Description is 24/Case, Pre-Ground 1.75 Oz Bags, the Cost is $13 (just type 13, no $), the   Markup Percent is 100%, the Brand is Premium, and the Year Introduced is 2017. Save the new record and close the form.

12

5

Open   the Products report and check that the report contains the new product P0026,   Coffee-Arabica Premium. View the layout of the report in Print Preview and   then change the page layout to Landscape. Save the report and close Print   Preview.

12

6

Open the Customers table. Sort the table by   Customer Name in ascending order. Save the changes and close the table.

12

7

Open   the Revenue Stats query and sort the City field in ascending order. Apply   Filter by Selection so that only stats for the last name Pellum display. Save and close the filtered query.

16

8

Open the Products table. Use Filter by Form to   create a filter that will identify all premium brands in the Brand field that   were introduced after 2017. Apply the filter and preview the filtered table. Close the   table and save the changes.

20

9

Close   all database objects. Close the database and then exit Access. Submit the   database as directed.

0

Total   Points

100

4 parts of the assignment:

Part 1: Check out the lecture notes and read chapter 6 of the text book before attempting these problems (see attachment)

1) Write a program that prompts the user for a number, tests to see whether that number is even or odd and prints out “Number is Even” if it is even, or “Number is Odd” if it is odd.

Hint: An even number is one that is dividable by 2 and has no remainder! Look up the % operator.

Part 2: 2) Write a program that stores your name in a variable. Then ask the user to guess your name. If heshe guesses right, display the message “Well done, good guess work!”, otherwise display the message “Try again next time!” and just exit the program at that point (we’ll see how to loop, i.e. keep on asking the user for names till heshe gets the right one, next week!).

Part 3: Modify the question above so that you keep on asking the user for names till heshe gets it right! So heshe has to guess your name right BEFORE you exit the program. Use a while loop.

Part 4:Write a program that asks the user for 5 numbers, using a for loop and one input function call inside that for loop. Sum the numbers as you are getting them from the user, then display the sum and average to the user at the end.

1. (5 points) In Clojure, implement Quicksort. Do so in a function called quicksort with one argument, a vector of numbers. The function returns a sequence of sorted numbers. For example (quicksort [2 6 1 9 1]) returns (1 1 2 6 9). 

2. (5 points) In Clojure, implement Mergesort. Do so in a function called mergesort with one argument, a vector of numbers. The function returns a sequence of sorted numbers. For example (mergesort [2 6 1 9 1]) returns (1 1 2 6 9). 

3. (5 points) Test the two functions to ensure they sort correctly. 

4. (5 points) Implement Quicksort and Mergesort in any other language and make sure they sort correctly. 5. (5 points) In both languages, time your quicksort and merge sort functions on a sorted list of 1,000,000 numbers. Time them on a reverse sorted list of 1,000,000 numbers. If 1,000,000 numbers are not enough to see a difference between the two algorithms, increase the input size. 

6. (15 points) Evaluate your results. Which program was faster on the sorted input? Was it also faster on the input that was in reverse sorted order? Is the result the same in both languages? Explain your results. Were the results as expected? Can you say anything about the languages used for sorting in terms of performance and ease of implementation? 

7. (Extra Credit 5 points) Compare the performance of your implementations in both languages with the performance of the built-in sort function in each language. Explain the result.