Problem: You are working for an organic farming collective. You have been asked to create two PivotTables and corresponding PivotCharts from the annual sales worksheet for the collective. One PivotTable and PivotChart summarize the sales by farm. The other PivotTable and PivotChart summarize the dairy sales by month for the top dairy producer.

Instructions: Perform the following tasks:

1. Start Excel. Open the Excel Chapter 8 – Lab Test A – Data file from the Data Files provided in the location specified by your instructor. Save the wokbook using the file name, Lab Test A – Excel Chapter 8.

2. Create the PivotTable and associated PivotChart shown in Figure E8A – 1 in a separate sheet in the workbook. Name the worksheet Sales by Farm.

a. Change cell A4 to Farms and cell B3 to Stores. Apply the Pivot Style Light 18 style to the PivotTable. Format the values as Currency values with a dollar sign and no decimal places. Apply the Style 6 to the PivotChart.

3. Create a second PivotTable and associated PivotChart as shown in Figure E8A–2 in a separate sheet in the workbook. Name the worksheet Dairy Sales by Month.

4. Change cell A4 to Month and cell B3 to Farm. Apply the Pivot Style Light 18 style to the PivotTable. Format the values as Currency values with a dollar sign and no decimal places. Turn off the legend for the chart. Apply the Style 5 to the PivotChart.

a. Filter the product type by Dairy. Filter the farm to Red Earth.

b. Change the chart type to Line and then add a linear trendline that forecasts the trend for two more months.

c. Change the document properties as specified by your instructor. Change the Dairy Sales by Month worksheet header to include your name, course number, and other information as specified by your instructor. Print the worksheet.

5. Change the Sales by Farm worksheet header to include your name, course number, and other information as specified by your instructor. Print the worksheet, and then save the workbook.

6. Submit the assignment as requested by your instructor.

Figure E8A – 1

Figure E8A – 2

Page 2

Problem: You are working for a home furnishings store. They want you to create a workbook that is created from a company template. You are then to import data provided in various files, consolidate the data, and insert and format a picture SmartArt displaying a list of the stores main departments.

Instructions: Perform the following tasks:

1. Start Excel. Open the Excel Chapter 7 - Lab Test A - Template file from the Data Files provided in the location specified by your instructor. Save the template as a workbook using the file name, Lab Test A - Excel Chapter 7.

2. Add three additional worksheets after Sheet1, and then paste the Sheet1 contents to the three empty sheets.

3. From left to right, rename the sheet tabs North, South, Central, and Consolidated. Change the subtitle on all four tabs from branch to the name of the corresponding tab. Change the title style for each title area in the first three sheets to Blue, Accent 1 for North, Light Green for South, and Purple for Central. Color all four tabs to be similar to their title areas.

4. On the North worksheet, select cell B4. Import the comma-delimited text file, Excel Chapter 7 - Lab Test A - Data 1, from the Data Files. In the Text Import Wizard - Step 2 of 3 dialog box, click the Comma check box to select it and then click Tab to clear the check box; otherwise accept the default settings. In the Import Data dialog box, click the Properties button. In the External Data Range Properties dialog box, click ‘Adjust the column width’ to clear the check box. Make sure ‘Overwrite existing cells with new data, clear unused cells is selected’. Import the text data to cell B4 of the existing worksheet.

5. On the South worksheet, select cell B4. Import the Access database file, Excel Chapter 7 - Lab Test A - Data 2, from the Data Files. Choose to view the data as a table, and insert the data starting in cell B4 in the existing workbook. Accept all of the default settings to import the data. Right-click any cell in the table, point to Table, and then click Convert to Range. Click the OK button to permanently remove the connection to the query. Copy and paste the values from cell range B9:E10 to B4:E5. Delete rows 8-10. If necessary, adjust column widths to display all the data.

6. Start Microsoft Word, and then open the Word file, Excel Chapter 7 - Lab Test A - Data 3, from the Data Files. Copy all of the data in the table except for the first row. Switch to Excel. Select the Central worksheet. Select cell A13, and then using the Paste Special command, paste the data as text into the Central worksheet. Close Word. Copy cell range B13:D16. Select cell B4, and then using the Paste Special command, paste the transpose of the data. Delete rows 13-16. Adjust the column widths as necessary to display all of the data.

7. On the Consolidated worksheet, use the SUM function, 3-D references, and copy and paste capabilities of Excel to total the corresponding cells on the three branch sheets. First, compute the sum in cell B4 and then compute the sum in cell B5. Copy the range B4:B5 to the range C4:E5. The Consolidated sheet should resemble Figure E7A – 1.

8. On the Consolidated worksheet, insert a SmartArt graphic using the Picture type, and select the Picture Frame layout type (column 1, row 3) in the layout area in the Choose a SmartArt Graphics dialog box. In the first strip, insert a furniture image from Office.com Clip Art. For the text, enter Furniture. In the second strip, insert an electronics picture from Office.com Clip Art, and enter the text Electronics. In the third strip, insert a kitchen picture from Office.com Clip Art, and enter the text Kitchen. Add a fourth strip. In the fourth strip, insert a Bath picture from Office.com Clip Art, and enter the text Bath.

9. Apply the Polished SmartArt Style to the SmartArt. Position the SmartArt so that it is centered below the data on the worksheet. It should resemble Figure E7A – 1.

10. Change the document properties as specified by your instructor. Change the worksheet header with your name, course number, and other information as specified by your instructor.

11. Save the changes to the workbook.

12. Submit the results according to instructions provided by your instructor.

Figure E7A – 1

Page 1

By

Professor Henry A. McKelvey

In Chapter 3 We will discuss the following objective:

1) How we handle data and information and why it is problematic

2) Be able to provide examples of threats

3) Determine the difference between nation and non-nation state threats

4) Know the difference between AHM and Penetration Testers

5) Describe the AHM components

6) Explain the hacker's thought process

7) List and describe the APT hacking core steps

8) Describe and explain the APT hacker attack phases

How we handle data and information and why it is problematic

Today we are handling much more data than we did in the past few years this has caused a very problematic situation that involves our lack of knowledge about how to handle these large amounts of data. The facts are there are limits to the data handling resources, these limits lead to compromises of the systems used to store the data. Given that there are compromises the sheer amount of data causes compromises to go undetected and undiscovered.

When compromises are discovered there is a tendency to sit on the information until instructions are given to inform the public about the compromises, this leads to the discoveries not being reported in a timely fashion. This is a chain reaction that leads to the following:

1) Not all the facts of any specific compromise are always uncovered

2) Some facts that are released might be misleading or even incorrect

3) Data and information are not disclosed in an open manner

These three factors are what eventually lead to distrust of the system to detect and warn of current and impending breaches.

The lack of trust can be seen as a vulnerability that leads to threats against the system. Threats are defined as any person, situation, thing, or event that can exploit a weakness in a system.

When it comes threats systems are actually at the mercy of such threats because of many factors take for instance these few:

· Techno-Criminals:

· Skimmer Evolution

· Skimmers are used by individuals who may not have technical ability but can gain access to machines.

· See page 32-33 in the book

· Hacking Power Systems

· Smart-meter tampering

· Power Jacking USB supplied Power Systems

· Defeating physical controls

· Unsophisticated Threat:

· Hollywood Hacker

· Unskilled but use complete immersion of technology against targets

· Social Engineering tactics

· Neighbor from Hell

· Wi-Fi Attacks, e-mail spoofing to others

· Using attack methods to cast blame on others

· See page 35-37 the Barry Ardolf Story

· Smart Persistent Threats

· Kevin Mitnick

· Gaining Access to Computer Systems

· Social Engineering

· Using knowledge of the interaction of people and system

The above are examples of threats to a system which are as real as the recent NASA breach through the use of a Raspberry Pi which is a small embedded processor computer, which was setup to channel data from the NASA network to some location in China. This attack would thus come under the heading of Techno-Criminal and Smart Persistent Threat. The threats can be layered to provide levels of sophistications and complexity. When defining who uses hackers to carry out attacks, usually this occurs in countries that are called nation states; These countries usually hire hackers to carry out attacks on other countries to disavow any responsibility for the attacks, to avoid direct contact, and to be able to claim that the attack was not carried out by them. These Nation-States are defined by the following:

1) A nation state is a geographical area that can be identified as deriving its political legitimacy from serving as a sovereign nation. A state is a political and geopolitical entity, while a nation is a cultural and ethnic one.

Taking into mind that this is a political definition, which is used to differentiate a Nation-State from a Non-Nation State which is:

1) A nation in which there is a cultural diversity, and from this cultural diversity no one ethic group holds complete national autonomy.

The differences between the two are an implication that there is often unrest in Nation-States caused by the attempt to maintain homogeny while trying to keep up with the modern world which tends to favor diversity as a means of functioning. An example of this is to be found in Islamic countries in which there is a clash between the old world and the modern world.

There are various attacks that have been carried out by Nation States and these are: RSA Attack, MITM Attack, and Carrier IQ Attack

In addition there are attacks carried out against Nation States: Stuxnet, Duqu, Flame

The point here is that both sides use hackers to carry out attacks against each other.

There are often misconceptions about Penetration Testers and Advanced Persistent Threat Hackers. This misconception has often led to the belief that ATP Hackers can be compared to Pen Testers. There is a fundamental flaw to this belief. The flaw is the failure to notice that APT Hackers and Pen-Testers have different goals and thus deploy a different methodology to hacking . Thus the following can be ascertained.

· AHM = APT Hacker Methodology

· A skill set that allows for big picture understanding of attacks and attack methods

· A methodology that avoids segmentation of attack methods

· PTM = Penetration Tester Methodology

· A skill set that allows for convergent and directed understanding of attacks and attack methods

· A methodology that’s seek to segment attack methods

Understanding that there are differences between APT Hackers and Pen-Testers show that using one to define the other is a mistake. The following chart shows the fundamental differences in method:

Understanding the differences between Pen Testers and APT Hackers helps in understanding the underlined issues that involve APT Hackers. However what exists is the knowledge that they both use the same core steps to obtain their goals. These core steps are:

· Reconnaissance

· Enumeration

· Exploitation

· Maintaining Access

· Clean up

· Progression

· Exfiltration

Enumeration uses tools like ping and traceroute to determine if the element is present on the network

Reconnaissance use tools like Nmap to test network elements for open ports

Exploitation is used to launch attacks against open ports based of vulnerabilities of the programs on those open ports

Maintaining Access is what hackers do when the setup access points and back doors in systems

Clean up involves the removing of the evidence that the intrusion occurred, this involves the removing or altering of log files, and possibly altering the files of IDS and IPS software

Progression involves the attacking of other systems from the other system to avoid detection and to set up the ubiquitous gathering of data.

Finally exfiltration is the retrieving and dissemination of gathered data, along with the eventual removal of the hacker from the system which may take months or even years.

The APT Hacker will use the following Attack phases to gain entry to systems:

Reconnaissance

Gather all information and data on a system

Spear Social Engineering

Manipulate persons who can be used for access

Remote and Wireless

Target remote users and wireless users to exploit wireless weaknesses

Hardware Spear-phishing

Use custom built devices to infiltrate buildings and locations

Physical infiltration

Target any place that the main target will or might locate to (hotel rooms, third party locations, etc…)

The goals of the APT Hacker is to get into a system and remain there until all information of that system has been exfiltrated and used by the APT Hacker for personal, monetary, or political gain.