MIS homework

mark39

MIS3003Chapter10HomeworkInstruction.pptx

Home >Information Systems homework help >MIS homework

MIS 3003 Chapter 10

Homework Instructions

Be careful! All instructions here are not for your homework questions, but just some very similar questions!!!

Question

Develop a spreadsheet model of the cost of a virus attack at an organization that has three types of computers: employee workstations, data servers, and Web servers. Assume that the number of computers affected by the virus depends on the severity of the virus. For the purposes of your model, assume that there are three levels of virus severity: Low-severity incidents affect fewer than 30% of the user workstations, and none of the data or web servers. Medium-severity incidents affect up to 70% of the user workstations, up to 50% of the data servers, and none of the web servers. High-severity incidents can affect all organizational computers.

Question

Assume 40% of the incidents are low severity, 35% are medium severity, and 25% are high severity.

Assume employees can remove viruses from workstations themselves, but the specially trained technicians are required to repair the servers. The time to eliminate a virus from an infected computer depends on the computer type. Let the time to remove the virus from each type be an input into your model. Assume that when use eliminate the virus themselves, they are unproductive for twice the time required for the removal. Let the average employee hourly labor cost be an input to your model. Let the average cost of a technician also be an input into your model. Finally, let the total number of user computers, data servers, and web servers be inputs into your model.

Question

Given the example inputs:

Run your simulation 10 times. Use the same inputs for each run, but draw a random number to determine the percentage of computers of each type affected, using the constrains detailed earlier. For example, if the attack is of medium severity, draw a random number between 0 and 70% to indicate the percentage of infected user workstations and a random number between 0 and 50% to indicate the percentage of infected web servers.

Question

This question is very realistic and very difficult. We will learn RANDOM function in excel and study how to make a simulation by using many environment variables.

Draw a draft of your simulation model. Because 40% attacks are low severity, 35% are med severity, and 25% are high severity. We could make the assumption of 100 attacks. (If we don’t use 100, it’s possible to have half attack, or 0.3 attack, which is not appropriate).

For each attack, we want to have the cost for each attack. The cost will be the total for workstations, data servers and web servers.

To calculate the cost of workstations, low severity attack, we will randomly choose a percentage between 0% to 30% (from the question), we will use function RANDBETWEEN function, RANDBETWEEN function have two input, bottom and top of your range.

Be careful, the input should be integer, means you cannot input 0% and 30%, instead, we should input 0, 30, and after we get a number between 0 and 30, we will divide it by 100.

Further information could check in: https://support.office.com/en-us/article/RANDBETWEEN-function-4cc7f0d1-87dc-4eb7-987f-a469ab381685

Question

The cost for workstation, will be based on the employee hourly cost multiply by time to fix and by 2 (They need double of the time to recover to work, from question).

So, to make your report clear, it’s nice to have three sub-column for each type of computer: number of affected, time to fix, costs.

For low-severity, workstations:

Number of Affect: =ROUND(RANDBETWEEN(0, 30) / 100 * “# Workstations”,0) (use absolute reference of # workstations, don’t directly use 200, because you may want to change environment variable later for other simulations, replace “# Workstations“ to the reference cell).

Time to fix: =“Number of Affect” * “Low-severity of workstations time to fix” * 2 (again, don’t use 1 for low-severity of workstation time to fix, because you may change it later)

Costs: =“Time to fix” * “Emp. Hourly cost” (use absolute reference again)

Question

For Data Servers, we will have number of affected, time to fix, costs too, but the time to fix will not need to multiply by 2, and also the cost will be by Tech. Hourly cost.

Number of Affect: =ROUND(RANDBETWEEN(0, 0) / 100 * “# Data servers”,0) (use absolute reference of # data servers, don’t directly use 200, because you may want to change environment variable later for other simulations, replace “# servers“ to the reference cell).

Time to fix: =“Number of Affect” * “Low-severity of Data server time to fix” (again, don’t use 1.5 for low-severity of data server time to fix, because you may change it later)

Costs: =“Time to fix” * “Tech. Hourly cost” (use absolute reference again)

The same for Web Servers.

Do the same for med-severity attacks and high-severity attacks.

Calculate the total cost for attacks, and then for the entire simulations.

Copy and paste the spreadsheet 10 times for 10 round simulations, because you use random functions, the numbers will change for every copy&paste.

Implementation

Create the Environment Variable spreadsheet, put all environment variables in.

Implementation

Create a new empty spreadsheet for simulation1

Implementation

For the first low-severity attack and Workstations:

# of affected: =ROUND(RANDBETWEEN(0,'Environment Variables'!$C$3)*'Environment Variables'!$B$10/100,0)

'Environment Variables'!$C$3 is refer to Percent of attack for low severity and workstations.

'Environment Variables'!$B$10 is refer to # of Workstations.

Time to fix: =B3*'Environment Variables'!$B$3*2

B3 is refer to # of affected for the first attack.

'Environment Variables'!$B$3 is refer to time to fix for low severity and workstations.

Cost: =C3*'Environment Variables'!$B$7

C3 is refer to time to fix for the first attack.

'Environment Variables'!$B$7 is refer to emp. Hourly cost for low severity and workstations.

Implementation

For the first low-severity attack and Data Server:

# of affected: =ROUND(RANDBETWEEN(0,'Environment Variables’!$E$3)*'Environment Variables'!$B$11/100,0)

'Environment Variables’!$E$3 is refer to Percent of attack for low severity and data server.

'Environment Variables'!$B$11 is refer to # of data server.

Time to fix: =E3*'Environment Variables'!$D$3

E3 is refer to # of affected for the first attack.

'Environment Variables’!$D$3 is refer to time to fix for low severity and data server.

Cost: =F3*'Environment Variables'!$B$8

F3 is refer to time to fix for the first attack.

'Environment Variables'!$B$8 is refer to emp. Hourly cost for low severity and data server.

Implementation

For the first low-severity attack and Web Server:

# of affected: =ROUND(RANDBETWEEN(0,'Environment Variables'!$G$3)*'Environment Variables’!$B$12/100,0)

'Environment Variables’!$G$3 is refer to Percent of attack for low severity and web server.

'Environment Variables'!$B$12 is refer to # of web server.

Time to fix: =H3*'Environment Variables’!$F$3

H3 is refer to # of affected for the first attack.

'Environment Variables’!$F$3 is refer to time to fix for low severity and web server.

Cost: =I3*'Environment Variables'!$B$8

I3 is refer to time to fix for the first attack.

'Environment Variables'!$B$8 is refer to emp. Hourly cost for low severity and web server.

Total Cost: =D3+G3+J3

Implementation

Use fill handler to copy your formulas for 40 low severity attacks. (from row 3 to row 42)

Implementation

Create the first attack for Med-severity attack, remember to change the percent of attack from C3 to C4, E3 to E4, G3 to G4.

Use fill handler to copy your formulas for 35 med severity attacks. (from row 43 to row 77)

Create the first attack for High-severity attack, remember to change the percent of attack from C4 to C5, E4 to E5, G4 to G5.

Use fill handler to copy your formulas for 25 med severity attacks. (from row 78 to row 102)

Calculate the SUM of total column to find out the total loss in this simulation

Implementation

Copy and paste your simulation1 10 times, to create 10 round simulation.

Create a report spreadsheet, average your total cost for each simulations.

Average Simulation Cost: =AVERAGE('Simulation 1:Simulation 10'!K103)

Average hours could be similar.

Implementation

Be creative for any formatting in this homework :-p

Upload to d2l

I will need you to upload only the Excel spreadsheet to dropbox.