create a database in which to store  its records.

An independent real estate firm that  specializes in home sales needs to create a database in which to store  its records. In the following project, you will open a database  containing information about the properties the real estate firm has  listed. You will work with a table in which to store agent information,  and then sort the table. You create a relationship between two tables  and enforce referential integrity. You also apply filters, sort a table,  and save the results.

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

Open the Agents table in   Datasheet view, and add the following records letting Access assign the   AgentID number:

FirstName   LastName Title
Guillaume Picard Broker
   Keith Martin Agent
   Usa-chan Yang Agent
   Steven Dougherty Agent   in Training
   Rajesh Khanna Agent in Training
   Juan Rosario President

Sort the Age nts table by the LastName field in   ascending order.

Apply a selection filter so that   everyone with a title other than Agent   in Training displays. Save the changes to the table design and close the   Agents table.

Open the Properties table in   Design View. Change the data type for the specified fields as follows:

Field Name    Data   Type
DateListed   Date/Time
ListPrice   Currency
SqFeet Number
Beds Number
Baths Number
AgentID Number

Save the changes to the design   of the table. Access will alert you  that some of the data in the table may be   lost due to changing the  data type of the fields listed above. Click Yes then view the table in Datasheet   View.

Sort the records in the   Properties table by the ListPrice field from largest to smallest.

Use Filter by Form to create a   filter that will identify all properties with a list price less than $300,000 and with 2 beds. Apply the filter and   preview the filtered table. Close the table and save the changes.

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

  • attachment

    Exp19_Access_Ch01_ML1-HomeLoans1.0.png

Solved Carpenter Family Camp

Solved Carpenter Family Camp

 

Shelly Cashman Access 2016 | Modules 1–3: SAM Capstone Project 1a

Carpenter Family Camp

CREATING TABLES, QUERIES, FORMS, AND REPORTS IN A DATABASE

GETTING STARTED

• Open the file SC_AC16_CS1-3a_FirstLastName_1.accdb, available for download from the SAM website.

• Save the file as SC_AC16_CS1-3a_FirstLastName_2.accdb by changing the

“1” to a “2”.

o If you do not see the .accdb 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 Project, you will also need to download and save the following support file from the SAM website:

o Support_SC_AC16_CS1-3a_Activity.xlsx 

PROJECT STEPS

1. Carpenter Family Camp operates summer camps for youth as well as families on a lake in Maine situated near the coast. The camp includes activities for families as well as separate activities for adults and children. Optional adventures to nearby attractions are also available.

Create a new table in Datasheet View with the following options:

a. Rename the default primary key ID field to AdventureID, and then change its data type to Short Text. (Hint: The AdventureID field should remain the primary key.)

b. Change the field size of the AdventureID field to 4.

c. Add a new field with the name AdventureName and the Short Text data type.

d. Add another field to the table with the name AdultPrice and the Currency data type.

e. Add a fourth field to the table with the name ChildPrice and the Currency data type.

f. Save the table using Adventure as the name.

2. With the Adventure table still open in Datasheet View, change the font in the table to Arial Narrow and the font size to 12 pt.

3. With the Adventure table still open in Datasheet View, add the records shown in Table 1 below. If necessary, resize the AdventureName field so that all field values are completely visible. Save and close the Adventure table.

Table 1: Adventure Table Records

AdventureID

AdventureName

AdultPrice

ChildPrice

BB01

Bike & Beach

$28.00

$20.00

HR01

Horseback Ride

$30.00

$25.00

WJ01

Windjammer Cruise

$45.00

$35.00

WW01

Whale Watching

$32.00

$26.00

4. Open the Reservation table in Design View and make the following changes:

a. Change the data type for the LodgingFee field to Currency.

b. Specify that the SessionID field is a Required field.

c. Save the changes to the Reservation table, and then close it. (Hint: Because you made changes to data types and field sizes, the “Some data may be lost” warning message appears. The data fits within the valid ranges, so ignore this message and continue saving the table.)

5. Open the Relationships window, and then add the Camper table to it. Create a one-to-many relationship between the ParentID field in the Parent table and the ParentID field in the Camper table. Make the relationship enforce referential integrity and cascade update related fields. Do not make the relationship cascade delete related records. Save the relationships, and then close the window.

6. Open the Counselor table in Design View and make the following changes:

a. Add a new field following the Specialty field. Assign the name CPR Certification to the field.

b. Set the data type for the field to Yes/No.

c. Set the Caption property, using CPR as the value.

d. Enter CPR certification current? as the description for the field.

e. Save the changes to the Counselor table, and then close it.

7. Open the Reservation table in Datasheet View, and then find or navigate to the record with the ReservationID field value 1700012. Change the LodgingFee field value to $140.00.

8. With the Reservation table still open in Datasheet View, apply a filter by selection to locate all records where the SessionID field value equals 1. Change the AdultPrice field value for ParentID 101 to $245.00. Change the ChildPrice field value for the same record to $215.00. Clear all filters. Save and close the Reservation table.

9. Open the Counselor table in Design View. Use the Lookup Wizard to change the Specialty field to a Lookup field. Type in the following four values (in the order shown) as the list of possible values for the field: Arts & Crafts, Land Sports, Nature Studies, and Water Sports. Limit the field values to only the items in the list, and do not allow multiple values for the field.

10. With the Counselor table still open in Design View, delete the HomePhone field. (Hint: If a message appears concerning deleting an index, continue with the deletion.) Save the Counselor table.

11. Switch to viewing the Counselor table in Datasheet View, and then change the SpecialCertification field value for Susan Kiley (who has a CounselorID field value of KS01) to Wilderness First Aid. Close the table.

12. Import the data from the file Support_SC_AC16_CS1-3a_Activity.xlsx, available for download from the SAM website. Append the records to the Activity table. Do not create a new table, and do not save the import steps.

13. Carpenter Family Camp requires all campers to submit a signed liability waiver from their parents. Create an update query to change the Waiver field value to No for all records currently in the Parent table. Run the query, and then save it using Waiver Update Query as the name. (Hint: 21 records will be updated by this query.) Close the query.

14. Use the Simple Query Wizard to create a query based on the Counselor table with the following options:

a. Include the CounselorID, FirstName, LastName, and CellPhone fields (in that order).

b. Save the query with the name Counselor Contact Query, and then close the query.

15. Create a new query in Design View based on the Counselor and Counselor_Session tables with the following options:

a. Include the SessionID field from the Counselor_Session table.

b. Include the LastName, FirstName, and CellPhone fields (in that order) from the Counselor table.

c. Sort the records in ascending order based on the SessionID field and then by the LastName field.

d. Save the query using Session Contact Query as the name.

e. Run the query, and then close it.

16. Use the Crosstab Query Wizard to create a crosstab based on the Reservation table with the following options:

a. Use only data from the Reservation table in the query.

b. Use SessionID as the row heading.

c. Use Children as the column heading.

d. Use a sum of the ChildPrice field as the calculated value for each row and column intersection in the crosstab query.

e. Save the crosstab query using Session-Child Crosstab as the name.

f. View the query, and then close it.

17. Create a new query in Design View based on the Reservation table and the Session 1 Payments table with the following options:

a. Select the ParentID field from the Reservation table.

b. Select the ReservationID, SessionID, AdultTotal, ChildTotal, and Lodging fields (in that order) from the Session 1 Payments table.

c. Move the ParentID field to the right of the SessionID field.

d. Add a calculated field after the Lodging field with the alias TotalFees that calculates the sum of the AdultTotal, ChildTotal, and Lodging fields.

e. Save the query using Session 1 TotalFees Query as the name.

f. View the query, confirm that it matches Figure 1 below, and then close it.

Figure 1: Session 1 TotalFees Query

Open the States Query in Design View, and then add the criteria to select only those records with a State field value of NY or NJ. Save and run the query, and then close it. (Hint: This query should return records that meet one or more of the query conditions.)

19. Open the Younger Males Query in Design View, and then add the criteria to select only those records with a Gender field value of M and an Age field value of less than 10. Hide the Gender field. Save and run the query, and then close it. (Hint: This query should only return records that meet both of the query conditions.)

20. Open the Sessions Total Query in Design View, and then modify it by adding Totals to the query. For the SessionID field, set the Total row to Group By. For the ReservationID field, set the Total row to Count. Save and run the query, and then close it.

21. Create a Split Form based on the Parent table. Save the form as Parent Update Form, and then close the form.

22. Open the Parent Update Form, and then add a new record to the Parent table, using the values shown in Figure 2 below. Close the form.

Figure 2: New Record for the Parent Update Form Figure 3: Adventure Report 

24. Use the Report Wizard to create a new report based on the Parent table with the following options:

a. Include the ParentID, FirstName, LastName, HomePhone, and CellPhone fields (in that order) from the Parent table.

b. Use no additional grouping in the report.

c. Sort the report in ascending order by the ParentID field.

d. Use the Tabular layout and Portrait orientation for the report.

e. Assign the name Parent Contact Report to the report.

f. Preview the report to ensure that it matches Figure 4 below, and then save and close the report.

Figure 4: Parent Contact Report 

a. Remove the City and State columns from the report.

b. Add a Totals row that calculates the sum of the values in the Adults column and the sum of the values in the Children column. If necessary, expand the size of the total control so that it appears completely.

c. Change the title of the report using Session 1 Camper Report as the name.

d. View the Session 1 Report in Report View, confirm that it matches Figure 5 below, and then save and close the report.

Figure 5: Session 1 Report

Shelly Cashman Access 2016 | Modules 1–3: SAM Capstone Project 1a

Carpenter Family Camp

CREATING TABLES, QUERIES, FORMS, AND REPORTS IN A DATABASE

GETTING STARTED

• Open the file SC_AC16_CS1-3a_FirstLastName_1.accdb, available for download from the SAM website.

• Save the file as SC_AC16_CS1-3a_FirstLastName_2.accdb by changing the

“1” to a “2”.

o If you do not see the .accdb 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 Project, you will also need to download and save the following support file from the SAM website:

o Support_SC_AC16_CS1-3a_Activity.xlsx 

PROJECT STEPS

1. Carpenter Family Camp operates summer camps for youth as well as families on a lake in Maine situated near the coast. The camp includes activities for families as well as separate activities for adults and children. Optional adventures to nearby attractions are also available.

Create a new table in Datasheet View with the following options:

a. Rename the default primary key ID field to AdventureID, and then change its data type to Short Text. (Hint: The AdventureID field should remain the primary key.)

b. Change the field size of the AdventureID field to 4.

c. Add a new field with the name AdventureName and the Short Text data type.

d. Add another field to the table with the name AdultPrice and the Currency data type.

e. Add a fourth field to the table with the name ChildPrice and the Currency data type.

f. Save the table using Adventure as the name.

2. With the Adventure table still open in Datasheet View, change the font in the table to Arial Narrow and the font size to 12 pt.

3. With the Adventure table still open in Datasheet View, add the records shown in Table 1 below. If necessary, resize the AdventureName field so that all field values are completely visible. Save and close the Adventure table.

Table 1: Adventure Table Records

AdventureID

AdventureName

AdultPrice

ChildPrice

BB01

Bike & Beach

$28.00

$20.00

HR01

Horseback Ride

$30.00

$25.00

WJ01

Windjammer Cruise

$45.00

$35.00

WW01

Whale Watching

$32.00

$26.00

4. Open the Reservation table in Design View and make the following changes:

a. Change the data type for the LodgingFee field to Currency.

b. Specify that the SessionID field is a Required field.

c. Save the changes to the Reservation table, and then close it. (Hint: Because you made changes to data types and field sizes, the “Some data may be lost” warning message appears. The data fits within the valid ranges, so ignore this message and continue saving the table.)

5. Open the Relationships window, and then add the Camper table to it. Create a one-to-many relationship between the ParentID field in the Parent table and the ParentID field in the Camper table. Make the relationship enforce referential integrity and cascade update related fields. Do not make the relationship cascade delete related records. Save the relationships, and then close the window.

6. Open the Counselor table in Design View and make the following changes:

a. Add a new field following the Specialty field. Assign the name CPR Certification to the field.

b. Set the data type for the field to Yes/No.

c. Set the Caption property, using CPR as the value.

d. Enter CPR certification current? as the description for the field.

e. Save the changes to the Counselor table, and then close it.

7. Open the Reservation table in Datasheet View, and then find or navigate to the record with the ReservationID field value 1700012. Change the LodgingFee field value to $140.00.

8. With the Reservation table still open in Datasheet View, apply a filter by selection to locate all records where the SessionID field value equals 1. Change the AdultPrice field value for ParentID 101 to $245.00. Change the ChildPrice field value for the same record to $215.00. Clear all filters. Save and close the Reservation table.

9. Open the Counselor table in Design View. Use the Lookup Wizard to change the Specialty field to a Lookup field. Type in the following four values (in the order shown) as the list of possible values for the field: Arts & Crafts, Land Sports, Nature Studies, and Water Sports. Limit the field values to only the items in the list, and do not allow multiple values for the field.

10. With the Counselor table still open in Design View, delete the HomePhone field. (Hint: If a message appears concerning deleting an index, continue with the deletion.) Save the Counselor table.

11. Switch to viewing the Counselor table in Datasheet View, and then change the SpecialCertification field value for Susan Kiley (who has a CounselorID field value of KS01) to Wilderness First Aid. Close the table.

12. Import the data from the file Support_SC_AC16_CS1-3a_Activity.xlsx, available for download from the SAM website. Append the records to the Activity table. Do not create a new table, and do not save the import steps.

13. Carpenter Family Camp requires all campers to submit a signed liability waiver from their parents. Create an update query to change the Waiver field value to No for all records currently in the Parent table. Run the query, and then save it using Waiver Update Query as the name. (Hint: 21 records will be updated by this query.) Close the query.

14. Use the Simple Query Wizard to create a query based on the Counselor table with the following options:

a. Include the CounselorID, FirstName, LastName, and CellPhone fields (in that order).

b. Save the query with the name Counselor Contact Query, and then close the query.

15. Create a new query in Design View based on the Counselor and Counselor_Session tables with the following options:

a. Include the SessionID field from the Counselor_Session table.

b. Include the LastName, FirstName, and CellPhone fields (in that order) from the Counselor table.

c. Sort the records in ascending order based on the SessionID field and then by the LastName field.

d. Save the query using Session Contact Query as the name.

e. Run the query, and then close it.

16. Use the Crosstab Query Wizard to create a crosstab based on the Reservation table with the following options:

a. Use only data from the Reservation table in the query.

b. Use SessionID as the row heading.

c. Use Children as the column heading.

d. Use a sum of the ChildPrice field as the calculated value for each row and column intersection in the crosstab query.

e. Save the crosstab query using Session-Child Crosstab as the name.

f. View the query, and then close it.

17. Create a new query in Design View based on the Reservation table and the Session 1 Payments table with the following options:

a. Select the ParentID field from the Reservation table.

b. Select the ReservationID, SessionID, AdultTotal, ChildTotal, and Lodging fields (in that order) from the Session 1 Payments table.

c. Move the ParentID field to the right of the SessionID field.

d. Add a calculated field after the Lodging field with the alias TotalFees that calculates the sum of the AdultTotal, ChildTotal, and Lodging fields.

e. Save the query using Session 1 TotalFees Query as the name.

f. View the query, confirm that it matches Figure 1 below, and then close it.

Figure 1: Session 1 TotalFees Query

Open the States Query in Design View, and then add the criteria to select only those records with a State field value of NY or NJ. Save and run the query, and then close it. (Hint: This query should return records that meet one or more of the query conditions.)

19. Open the Younger Males Query in Design View, and then add the criteria to select only those records with a Gender field value of M and an Age field value of less than 10. Hide the Gender field. Save and run the query, and then close it. (Hint: This query should only return records that meet both of the query conditions.)

20. Open the Sessions Total Query in Design View, and then modify it by adding Totals to the query. For the SessionID field, set the Total row to Group By. For the ReservationID field, set the Total row to Count. Save and run the query, and then close it.

21. Create a Split Form based on the Parent table. Save the form as Parent Update Form, and then close the form.

22. Open the Parent Update Form, and then add a new record to the Parent table, using the values shown in Figure 2 below. Close the form.

Figure 2: New Record for the Parent Update Form Figure 3: Adventure Report 

24. Use the Report Wizard to create a new report based on the Parent table with the following options:

a. Include the ParentID, FirstName, LastName, HomePhone, and CellPhone fields (in that order) from the Parent table.

b. Use no additional grouping in the report.

c. Sort the report in ascending order by the ParentID field.

d. Use the Tabular layout and Portrait orientation for the report.

e. Assign the name Parent Contact Report to the report.

f. Preview the report to ensure that it matches Figure 4 below, and then save and close the report.

Figure 4: Parent Contact Report 

a. Remove the City and State columns from the report.

b. Add a Totals row that calculates the sum of the values in the Adults column and the sum of the values in the Children column. If necessary, expand the size of the total control so that it appears completely.

c. Change the title of the report using Session 1 Camper Report as the name.

d. View the Session 1 Report in Report View, confirm that it matches Figure 5 below, and then save and close the report.

Figure 5: Session 1 Report

Analyzing LDAP directories

Describe some ways that an administrator can harden a system on a network.

Wk 7 Discussion

Research the variety of enumeration tools available. Select one tool and explain what it does, how it works and what type of information it extracts (example:  Softerra LDAP Browser is the industry-leading software for browsing and analyzing LDAP directories. It provides a wide variety of features for handy viewing of directory contents, getting information about directory infrastructure and objects.)

 

Week 7 Discussion

Describe some ways that an administrator can harden a system on a network.

Post should be between 250-300 words

Complete the Post-Mortem template based on your experiences with completing the course project over the last five weeks.

Project rollout begins after a project has been implemented, tested, and judged stable and functional enough to be moved into production. The project rollout process comprises specific steps that must be scheduled carefully to enable the project to hit production deadlines and meet business expectations.

For this assignment, you will create an implementation project plan, which outlines the steps involved in implementing your project. This document is sometimes called a work-back schedule because you begin with the date that your project needs to go live, and then work back from there to determine what needs to be done when.

You will also conduct a post-mortem on the work you have submitted to date. Post-mortem (from the Latin for “after death,” but referring to it in the context of business) is the analysis of a project’s process and results conducted by key project stakeholders after the project has been moved into production, which means it’s functionally complete. The goal of a post-mortem is to identify what went right and what went wrong post-implementation so that project managers can apply these lessons learned to future projects.

For this three-part assignment, you will:

  • Construct a work-back schedule that presents the steps involved in implementing your project.
  • Construct a post-mortem using the template on the work you have submitted to date in this course.
  • Create updated project documents based on the results of your post-mortem.

Review the Project Implementation Plan Example.

Create a project implementation plan for your project based on the example document. You may use Microsoft Word or other software to create your project implementation plan.

Complete the Post-Mortem template based on your experiences with completing the course project over the last five weeks.

Update the project documents you have submitted thus far in the course based on your completed post-mortem.

Submit your project implementation plan, post-mortem, and updated project documents.

  • attachment

    BSA425_v1.3_wk5_post_mortem.docx
  • attachment

    BSA425_v1.3_wk5_project_implementation_plan_example.docx
  • attachment

    BSA425_v1.3_wk2_project_plan_draftpowedeyingi.docx
  • attachment

    BSA425_v1.3_wk2_project_draft2powedeyingi.docx
  • attachment

    BSA425_v1.3_wk1_project_proposal_templatepowedeyingi.docx
  • attachment

    powedeyingiXYZBankSoftwareTestPlan.docx
  • attachment

    XYZBankSoftwareTestPlan.docx

Create   a new query in Design view

Exp19_Access_Ch02_ML1 – Game Collection 1.2

 

Project Description:

You have cataloged your vintage video games in an Access database. In  this project, you add two tables to a database you have started? One to  identify the game system that runs your game and the other to identify  the category or genre of the game. Then you will join each table in a  relationship so that you can query the database.

Start Access. Open the downloaded   Access file named Exp19_Access_Ch02_ML1_Games.  Grader has automatically added   your last name to the beginning of the  filename. Save the file to the   location where you are storing your  files.

Open   the Games table and review the fields containing the game information. Close   the table.
Create a new table in Design view. Add the field name SystemID and select AutoNumber as the Data Type. Add the caption System ID. Set the SystemID field as the primary   key for the table.

Add   a second field named SystemName   with the Short Text Data Type.   Change the SystemName field size property to 15.   Add the caption System Name.   Save the table as System.

Switch   to Datasheet view and enter the following system names, letting Access assign   the System ID:
XBOX 360
PS3
Wii
NES
PC Game
Nintendo 3DS
Double-click the border to the   right of the System Name column to set the column width to Best Fit. Save and   close the table.

Create   a new table in Design view. Add the field name CategoryID   and select AutoNumber as the Data   Type. Add the caption Category ID.   Set the CategoryID field as the   Primary Key for the table.

Add   a second field named CategoryDescription   and accept Short Text as the Data   Type. Change the field size property to 25.   Add the caption Category Description.   Save the table as Category.

Switch   to Datasheet view, and enter the following Category Description, letting   Access assign the Category ID:
Action
Adventure
Arcade
Racing
Rhythm
Role-playing
Simulation
Sports
Close the table.

Establish   relationships in the database by adding the Category, Games and System  tables to the Relationships   window. Close the Show Table dialog box.  Create a one-to-many relationship   between the SystemID field in the  System table and the SystemID field in the   Games table, enforcing  Referential Integrity. Select the option to cascade   update the related  fields.

Create   a one-to-many relationship between the CategoryID field in  the Category table   and the CategoryID field in the Games table,  enforcing Referential Integrity.   Select the option to cascade update  the related fields. Save and close the   Relationships window.

Create   a simple query using the Query Wizard. From the Games table, add the GameName and Rating fields (in that order). Save the query using the title Ratings. Switch to Design view. Sort the   Rating field in ascending order.

Click   the gray column selector at the   top of the Rating field, then click and drag the column to move it to the first position of the query design grid.   Add a Total row, click in the Totalrow   of the GameName column, click the arrow,   and then select Count.  The records   will be grouped by rating, and the number of games will  be counted for each   group. Run, save, and close the query.

Create   a new query in Design view. Add the Category,   Games, and System tables to the query design window. Add the following   fields to the query (in this order).
GameName

CategoryDescription
   Rating
   SystemName
   DateAcquired

Sort   the query in ascending order by GameName and run the query. Save the query as   Game List and close the query.

Copy   the Game List query in the   Navigation pane and paste it with the name PS3 Games.   Modify the query in Design view by using PS3    as the criteria for SystemName. Remove the sort by GameName and sort  in   ascending order by CategoryDescription. Save and run the query.  Close the query.

Create   a new query in Design view. Add the Category,   Games, and System  tables to   the query design window. Add fields (in this order) to  display the name of   the game, its rating, the category description of  the game, and the system   name. You only want to display Wii games with  a rating of Everyone or Teen.    Because the system name is the same for the games you plan to pack and  share,   hide this value from the results. Save the query as Thanksgiving Games and run the query. Close the query.

  • attachment

    Exp19_Access_Ch02_ML1-GameCollection1.2.png

Complete your analysis by adding   formulas in the range G9:H9

Exp19_Excel_Ch08_Cap_Golden_State_5K

 

Project Description:

You are a volunteer for the Golden State 5k, an annual 5k held across several cities in California to raise money for at risk youth. As part of your duties, you track donations, volunteer information, and race results. This year you have decided to use Excel to calculate frequency distribution by age and time, calculate various descriptive statistics, and forecast participation rate as well as donation rate for 2025.

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

Ensure the RaceResults worksheet   is active, then use the FREQUENCY function to calculate the frequency   distribution of the race results in column D. Place your results in the range   G4:G9.

Enter a function in cell F22 to   calculate the correlation between age (Columns C) and race time (Column D).

Enter a function in cell G22 to   calculate the covariance between age and race time.

Enter a function in cell H22 to   calculate the variance of the ages in the data set. Note this is a sample of   data not a population.

Enter a function in cell I22 to   calculate the standard deviation of the ages in the data set.

Ensure the Data Analysis ToolPak   add-in is active. Use the Data Analysis ToolPak to create a histogram with   chart output starting in cell H12 based on the ages of the runners surveyed.   Use the range F13:F18 as the Bin Range. Ensure that Chart output and   Cumulative percentage is included in the results. Place the upper left hand   corner of the chart in cell L13.

Ensure the VolunteerInfo   worksheet is active. Use the Data Analysis ToolPak to perform a single factor   ANOVA on the range C5:E21 (Including column lables). Place the results   starting in cell G5.

Create a Forecast Sheet that   depicts year over year growth in participation for the city of Los Angeles.   Set the Forecast end year as 2025 and place the results on a new worksheet named 2025Forecast.

Ensure the Participants   worksheet is active then create a scatter plot chart that places the   Participant observations on the X axis and the Donation dollars on the Y axis   (do not include column headings). Add the chart title Participant   Forecast and a   linear trendline to the chart that also shows the Equation and the R-square.

Enter a function in cell F6 to   calculate the intercept of the linear trendline created in the prior step.

Enter a function in cell G6 to   calculate the Slope of the linear trendline.

Enter a function in cell H6 to   calculate the R-square of the linear trendline.

Enter a function in cell I6 to   calculate the Standard Error.

Use the FORECAST.LINEAR function   in cell F9 to forecast potential donations once the goal of 20,000   participants is reached. Format the results as Currency.

Complete your analysis by adding   formulas in the range G9:H9 to calculate the high and low thresholds of the   forecast.

conduct an employee satisfaction survey.

Exp19_Excel_Ch08_CapAssessment_Employee_Satisfaction

 

Project Description:

You work as a data analyst for Camino Marketing, a U.S.-based web marketing company. You have been asked to conduct an employee satisfaction survey. As part of the survey, you will collect sample data to help provide insight into employee salaries, productivity within the three national offices, and to predict the growth of the New York office’s client base by 2025.

1

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

2

Ensure the Employee Satisfaction   worksheet is active, then use the FREQUENCY function to calculate the   frequency distribution of job satisfaction in column D. Place your results in   the range G5:G9.

3

Enter a function in cell G12 to   calculate the standard deviation of column c.

4

Enter a function in cell G13 to   calculate the variance between salaries.

5

Enter a function in cell G14 to   calculate the Covariance of the salaries and job satisfaction in the data   set. Note this is a sample of data not a population.

6

Enter a function in cell F17 to   calculate the correlation coefficient between employee salary and job   satisfaction.

7

Ensure the Data Analysis ToolPak   add-in is active. Use the Data Analysis ToolPak to create a histogram based   on the salaries in column C. Use the range I4:I9 as the Bin Range. Ensure   that Cumulative percentage, column labels, and chart output are included in   the results. Place the results in cell K4. Edit the result text in cell K4 to   display   Salary, if   necessary.

8

Ensure the Office_Data worksheet   is active. Use the Data Analysis ToolPak to perform a single factor ANOVA on   the range C3:E35. Place the results starting in cell G3. Be sure to include   column labels. Resize columns G:M as needed.

9

Create a Forecast Sheet that   depicts year over year growth in customer growth. Set the Forecast end year   as 2025 and place the results on a new worksheet named 2025Forecast.

10

Ensure the Customers worksheet   is active then create a scatter plot chart that places the Years on the X   axis and the Clients on the Y axis. Add a linear trendline to the chart that   also shows the Equation and the R-square. Add the title Customer   Growth.

11

Enter a function in cell E4 to   calculate the intercept of the linear trendline created in the prior step.

12

Enter a function in cell F4 to   calculate the Slope of the linear trendline.

13

Enter a function in cell G4 to   calculate the R-square of the linear trendline.

14

Enter a function in cell H4 to   calculate the Standard Error.

15

Use the FORECAST.LINEAR function   in cell E7 to the total number of customers by the end of 2021.

16

Complete your analysis by added   formulas in the range F7:G7 to calculate the high and low thresholds of the   forecast.

17

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

create two separate policies on use of EMAIL and a WIFI/INTERNET USE within the company.

create two separate policies on use of EMAIL and a WIFI/INTERNET USE within the company.

You have just been hired as the Security Manager of a medium-sized Financial Services company employing 250 people in New Hampshire, and have been asked to write two new security policies for this company. The first one is an e-mail policy for employees concentrating on personal use of company resources. The second policy is that of WIFI and Internet use within the company.

There are many resources available on the web so researching these topics and policies should be easy. The most difficult part of this exercise will be determining how strict or how lenient you want to make these policies for this particular company.

Project Plan

You are asked to create two separate policies on use of EMAIL and a WIFI/INTERNET USE within the company.

Be specific in your terms and conditions of use. Consider these items to be included in your policies (as applicable).

1. Overview

2. Purpose

3. Scope

4. Policy

5. Policy Compliance

6. Related Standards, Policies and Processes

7. Definitions and Terms

Some useful links and resources for your research:

https://resources.workable.com/email-usage-policy-template

https://www.sans.org/security-resources/policies/general/pdf/email-policy

https://resources.infosecinstitute.com/acceptable-use-policy-template-public-wifi-networks/#gref

https://www.techrepublic.com/article/download-our-wireless-policy-template/

complete an amortization table with financial functions

Exp19_Excel_AppCapstone_CompAssessment_Manufacturing

 

You have recently become the CFO for Beta Manufacturing, a small cap company that produces auto parts. As you step into your new position, you have decided to compile a report that details all aspects of the business, including: employee tax withholding, facility management, sales data, and product inventory. To complete the task, you will duplicate existing formatting, utilize various conditional logic functions, complete an amortization table with financial functions, visualize data with PivotTables, and lastly import data from another source.

1

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

2

Group all the worksheets in the   workbook and fill the range A1:F1 from the Insurance worksheet across all   worksheets including the formatting. Ungroup the worksheets after the fill is   complete and ensure the Insurance worksheet is active.

3

Click cell I5, and enter a   function that determines the number of full-time employees, (FT).

4

Enter a database function in   cell I6 that determines the average salary of all full-time employees with at   least one dependent. Format the results in Accounting Number Format.

5

Enter a lookup function in cell   E5 that returns the tax deduction amount for the number of dependents listed   in the cell C5. Use   the table in range H13:I17 to complete the function. The maximum deduction is   $500.00; therefore, employees with more than four dependents will receive no   additional deductions.

6

Use Auto Fill to copy the   function down, completing column E. Be sure to use the appropriate cell   referencing. Format the data in column E with the Accounting Number Format.

7

Enter a logical function in cell   F5 that calculates employee FICA withholding. If the employee is full-time   and has at least one dependent, then he or she pays 7% of the annual salary   minus any deductions. All other employees pay 5% of the annual salary minus   any deductions. Copy the function down through column F. Format the data in   column F with Accounting Number Format.

8

Apply conditional formatting to   the range C5:C34   that highlights any dependents that are greater than 3 with Light Red Fill and Dark Red Text.

9

Click cell H10, and enter an   AVERAGEIFS function to determine the average salary of full-time employees   with at least one dependent. Format the results in Accounting Number Format.

10

Use Advanced Filtering to   restrict the data to only display full-time employees with at least one   dependent. Place the results in cell A37. Use the criteria in the range   H24:M25 to complete the function.

11

Ensure that the Facilities   worksheet is active. Use Goal Seek to reduce the monthly payment in cell B6   to the optimal value of $6000. Complete this task by changing the Loan amount in cell E6.

12

Create the following three   scenarios using Scenario Manager. The scenarios should change the cells B7, B8,   and E6.
Good   
B7 = .0325
B8 = 5
E6 = 275000
Most   Likely
B7 = .057
B8 = 5
E6 = 312227.32
Bad
B7 = .0700
B8 = 3
E6 = 350000
Create a Scenario Summary Report based on the value in cell B6. Format the   new report appropriately.

13

Ensure that the Facilities   worksheet is active. Enter a reference to the beginning loan balance in cell   B12 and enter a   reference to the payment amount in cell C12. 

14

Enter a function in cell D12,   based on the payment and loan details, that calculates the amount of interest   paid on the first payment. Be sure to use the appropriate absolute, relative,   or mixed cell references.

15

Enter a function in cell E12,   based on the payment and loan details, that calculates the amount of   principal paid on the first payment. Be sure to use the appropriate absolute,   relative, or mixed cell references.

16

Enter a formula in cell F12 to   calculate the remaining balance after the current payment. The remaining   balance is calculated by subtracting the principal payment from the balance   in column B.

17

Enter a function in cell G12, based on the payment   and loan details, that calculates the amount of cumulative interest paid on   the first payment. Be sure to use the appropriate absolute, relative, or   mixed cell references.

18

Enter a function in cell H12,   based on the payment and loan details, that calculates the amount of   cumulative principal paid on the first payment. Be sure to use the   appropriate absolute, relative, or mixed cell references.

19

Enter a reference to the   remaining balance of payment 1 in cell B13. Use the fill handle to copy the functions created in   the prior steps down to complete the amortization table.

20

Ensure the Sales worksheet is   active. Enter a function in cell B8 to create a custom transaction number.   The transaction number should be comprised of the item number listed in cell   C8 combined with the quantity in cell D8 and the first initial of the payment type in cell E8. Use   Auto Fill to copy the function down, completing the data in column B.

21

Enter a nested function in cell G8 that displays the word Flag if the Payment Type is Credit and the Amount is greater than   or equal to $4000. Otherwise, the function will display a blank cell. Use Auto   Fill to copy the function down, completing the data in column G.

22

Create a data validation list in   cell D5 that displays Quantity, Payment Type, and Amount (in that order).

23

Type the Trans# 30038C in cell B5, and select Quantity from the validation list in   cell D5.

24

Enter a nested lookup function   in cell F5 that evaluates the Trans # in cell B5 as well as the Category in   cell D5, and returns the results based on the data in the range A8:F32.

25

Create a PivotTable based on the   range A7:G32. Place the PivotTable in cell I17 on the current worksheet.   Place Payment Type in the Rows box and Amount in the Values box. Format the   Amount with Accounting Number Format.

26

Insert a PivotChart using the   Pie chart type based on the data. Place the upper-left corner of the chart   inside cell I22. Format the Legend of the chart to appear at the bottom of   the chart area. Format the Data Labels to appear on the Outside end of the   chart.

27

Insert a Slicer based on Date.   Place the upper-left corner of the Slicer inside cell L8.

28

Use PowerQuery to connect to the   Access database Exp19_Excel_AppCapstone_Comprehensive_Inventory.accdb.   Load the Inventory table into a new worksheet named Inventory.
Note, Mac users, download and import the delimited Inventory.txt file into a new worksheet named Inventory starting in cell A1.

29

Create a footer with your name   on the left, the sheet code in the center, and the file name on the right for   each worksheet.

30

Save the file Exp19_Excel_AppCapstone_ComprehensiveAssessment-   Manufacturing.xlsx. Exit Excel. Submit the file as directed.

develop 10 investigative questions that you would ask during the course of this investigation.

Incident Response Report #2

This assignment is similar to the last incident response report. You will take the framework you

developed for your report, with corrections that I suggested as feedback, then develop a new

report for this incident. The grading rubrics will be the same, but the grading will be stricter

being this is your 2nd time developing an incident response report.

The incident scenario this time is that an employee was installing a new computer. They were

in the process of installing software when they noticed abnormal mouse activity on the

computer. At that point, they walked away from the computer and called you for help. Treat

this like an incident at a corporation, although the computer is not joined to a domain.

I am expecting that at a minimum you are able to tell the story of what happened during the

incident. What was the initial infection vector, what happened next? Are there any persistence

mechanisms in play? Are there any IP addresses associated with the attacker? And if so, what

are they?

Instead of a 22 page document with evidence, you will be working with evidence that has been

extracted from the computer in its raw form just like an incident responder would see if they

were working on the workstation. The evidence totals about 7 gigs with the memory image.

Some of the evidence collected may overlap evidence that was collected in another method.

Some of the included evidence is from:

• Netstat

• Complete directory / file structure listing for the entire C drive

• Services

• Several Kape collections – These will provide a rich set of evidence but take a little bit to

get used to the way Kape provides the collected evidence

• Browser information

• Prefetch

• Scheduled Tasks

• Users

• Windows Event Logs

• Forensic RAM image

The evidence can be downloaded from a Google Drive link I’ll post on D2L.

Not every piece of evidence collected will yield results. It is your job to comb through the

evidence to put together the pieces of what happened to develop your report. You can

successfully respond to the incident without doing the memory forensics, but it may provide

some easier insights if you want to give it a shot. The memory image is around 5.4 gigs in size.

I split the evidence into different folders to make it easier to download and work with if you don’t

have the full 7 gigs of free space on your drive.

Start by forming your investigative questions, then going searching for evidence that proves

your questions. Your IOCs this time will be generated based on actual evidence you find that

can be used to search other computers to determine if they are in scope of the incident as well.

I will be providing the VM as an optional way to investigate the incident. The VM is provided as

is without any additional support from me. It is a VMWare image. Some of the evidence will still

be in the VM, but it will be provided post attack after that attacker is already gone.

To produce your report, analyze the evidence, then summarize and present it using the

guidelines from the chapter 16 lecture, chapter 16 from the textbook, and guidance from the

“ICS-487 incident report what to include” in the sticky section of D2L.

The report should be at least 5 not counting any pictures utilized and the mandatory coverpage.

There will be a point penalty if the report is less than 5 pages. Do not turn in a 50-page report

for this assignment. The objective is to be concise as you tell the who what when where and

how story.

As previously stated, the raw evidence for this report has already been collected for you. You

will need to comb through it to find useful artifacts to fill in the pieces for the report. This

means making up a company that was hacked, the name of your company that is responding,

etc. Basic remediation steps will also need to be described as partof this assignment. For the

lessons learned section, describe in a paragraph or two items that you learned about incident

response that you will apply to future investigations based on theevidence that was

provided to you this time.

You should list 5 Indicators of Compromise that you gleaned from the evidence that are worth

searching for enterprise wide to make sure no other systems are infected. These will be best

displayed in a table for the IOC section. These IOCs will be developed when you find

artifacts related to the investigation. A timeline should also be put into the report like last

time.

You will also need to develop 10 investigative questions that you would ask during the course of

this investigation.

Keep in mind that for this project this evidence is manually collected from a single computer.

This is a normal amount of evidence one would collect in the course of Incident Response. This

is why we are teaching the method of developing investigative leads, turning those leads into

IOCs, then searching for those IOCs. This type of manual collection does not scale across more

than one or two systems in an incident. The next Incident Response report will involve you

collecting the evidence, and then reporting on that evidence you collect.

Make sure you start this assignment early. This is a 400-level class and will require some critical

thought to complete this assignment along with utilizing the skills that have been taught up to

this point. A quality report will take time to produce especially when it comes to selecting

which pieces of evidence to use that will tell the story but not provide unnecessary detail.

Also keep in mind the responses I provided to your previous report.

A common mistake is making the executive summary and findings section too

technical. This should be written in language that an 80 year old grandma who doesn’t

have an IT background can understand.

Because this is an academic environment, anytime you use someone else’s ideas you must cite

it in IEEE format. It is assumed that most of your content will be coming from the provided

evidence document. You don’t have to cite general information you take from that document.

Make sure that you are re-telling the story. It is ok to use a sentence or two once in a while.

Do not just copy an entire paragraph from the evidence document and turn that in as your

work.

Grading Rubric

Requirement Possible

Score

Coversheet meets specifications including affected organization, incident number

or name, date published, name of the organization that performed the

investigation, and “Privileged and Confidential”

2

Table of contents 2

Report is at least 5 pages long not including cover sheet and any pictures used as

part of the report 3

Good grammar, style, and use of IEEE citations if applicable 10

Formatted according to specifications and style suggestions in Chapter 16 and

reporting lecture 10

10 quality investigative questions based on evidence provided 10

5 Indicators of Compromise based on evidence provided 5

1 – 2 paragraphs of lessons learned describing items that you learned from this

investigation that you will apply to future investigations 7

Remediation describes exact steps taken to expel malicious actors from the

network 5

Who what where when why is told to the best of the author’s ability with the

evidence provided throughout the documents without making the reader piece

any parts of the puzzle together

8

Executive summary containing how the incident was discovered, the type of

incident this is what the response was, the goal of the investigation, duration of

the work, start and stop date, and who sponsored the work

10

Findings section addresses the goals of the investigation by summarizing the

information found in the investigative questions, systems involved, IOC, evidence

collected and remediation sections in a manner that is written with nontechnical

executives in mind. No more than a page long.

5

Systems involved are described and information that is known based on the

limited evidence provided is recorded.

3

Evidence collected section contains detailed account of the evidence that was

utilized for the report, how it was collected, what facts it is providing to back up

the rest of the report. Any procedures have to be documented in such a way that

a third party could replicate the results of the analysis. You may have to do a little

googling here to come up with any missing parts of the analysis and collection.

15

Recommendations section describes fixing the holes that caused this incident and

any larger suggestions for the company as a whole to improve their overall

security posture

5

This report has a total of 25 grading points available. Each individual item will be scored as a

percentage adding to a total of 100% for the assignment. Your total percentage will be what

percent of the 25 points you get on the assignment. For example, I produce a report that

scores a total of 85 of the 100 available percentage points. 85% of 25 is 21.25 so I will get 21.25

points on the assignment.

  • attachment

    IncidentResponseReport2description1.pdf