Training & Evading ML Based IDS (Python) homework help

CS6262-O01 Network Security – Project 5 Training & Evading ML based IDS

 

Introduction/Assignment Goal

The goal of this project is to introduce students to machine learning techniques and methodologies, that help to differentiate between malicious and legitimate network traffic. In summary, the students are introduced to:

· Use a machine learning based approach to create a model that learns normal network traffic.

· Learn how to blend attack traffic, so that it resembles normal network traffic, and by-pass the learned model.

NOTE: To work on this project, we recommend you to use Linux OS. However, in the past, students faced no difficulty while working on this project even on Windows or Macintosh OS.

 

Readings & Resources

This assignment relies on the following readings:

( • )“Anomalous Payload-based Worm Detection and Signature Generation”, Ke Wang, Gabriela Cretu, Salvatore J. Stolfo, RAID2004.

( • )“Polymorphic Blending Attacks”, Prahlad Fogla, Monirul Sharif, Roberto Perdisci, Oleg Kolesnikov, Wenke Lee, Usenix Security 2006.

· “True positive (true detections) and False positive (false alarms)”

 

Task A

( • )Preliminary reading. Please refer to the above readings to learn about how the PAYL model works: a) how to extract byte frequency from the data, b) how to train the model, and c) the definition of the parameters; threshold and smoothing factor. Note: Without this background it will be very hard to follow through the tasks.

( • )Code and data provided. Please look at the PAYL directory, where we provide the PAYL code and data to train the model.

· Install packages needed. Please read the file SETUP to install packages that are needed for the code to run.

· PAYL Code workflow. Here is the workflow of the provided PAYL code:

· It operates in two modes: a) training mode: It reads in pcap files provided in the ‘data’ directory, and it tests parameters and reports True Positive rates, and b) testing mode: It trains a model using specific parameters and using data in the directory, it will use a specific packet to test and then will decide if the packet fits the model.

· Training mode: It reads in the normal data and separates it into training and testing. 75% of the provided normal data is for training and 25% of the normal data is for testing. It sorts the payload strings by length and generates a model for each length. Each model per length is based on [ mean frequency of each ascii, standard deviation of frequencies for each ascii]

· To run PAYL on training mode: python wrapper.py. You will have to modify the port numbers in the

read pcap.py (commented in the sourcecode) according to the protocol you select.

· Testing mode: It reads in normal data from directory, it trains a model using specific parameters, and it tests the specific packet (fed from command line) against the trained model. 1. It computes the mahalanobis distance between each test payload and the model (of the same length), and 2. It labels the payload: If the mahalanobis distance is below the threshold, then it accept the payload as normal traffic. Otherwise, it reject the packet as attack traffic.

· To run PAYL on testing mode: python wrapper.py [FILE.pcap]

 

 

( #Sample Output: $ python wrapper.py Attack data not provided, training and testing model based on pcap files in data/ folder alone. )1

2

3

 

( 1 )

 

( To provide attack data, run the code as: python wrapper.py <attack-data-file-name> ——————————————— Training Testing Total Number of testing samples: 7616 Percentage of True positives: XX.XX Exiting now )4

5

6

7

8

9

10

 

Tasks:

( • )You are provided a single traffic trace (artificial-payload) to train a PAYL model.

( • )After reading the reference papers above, it should make sense that you cannot train the PAYL model on the entire traffic because it contains several protocols. Select a protocol: a) HTTP or b) IRC to train PAYL.

( • )Modify the IP addresses/port numbers (also commented in the python files) in the source code according to the traffic you choose (HTTP/IRC).

( • )Use the artificial traffic corresponding to the protocol that you have chosen and proceed to train PAYL. Use the provided code in the training mode and make sure that you are going to use the normal traffic(artificial payload) that is fed to your code while training. Provide a range of the two parameters (threshold and smoothing factor). For each pair of parameters you will observe a True Positive Rate. Select a pair of parameters that gives 95% or more True Positive; more than 99% true positive rate is possible. You may find multiple pairs of parameters that can achieve that.

 

Task B

( • )Download your unique attack payload: To download your unique attack payload, visit the following url: https://www.cc.gatech.edu/˜rgiri8/6262_P5/einstein7.pcap and replace “einstein7” with your GTID.

( • )Use PAYL in testing mode. Feed the same training data that you selected from Task A, use the same pair of parameters that you found from Task A and provide the attack trace.

· Verify that your attack trace gets rejected – in other words that it doesn’t doesn’t fit the model.

· You should run as follows and observe the following output:

( \$ python wrapper.py attack-trace-test.pcap Attack data provided, as command line argument attack-trace.pcap ——————————————— Training Testing Total Number of testing samples: 7616 Percentage of True positives: XX.XX ————————————– Analysing attack data, of length1 No, calculated distance of ZZZZ is greater than the threshold of XXXX. IT DOESN’T FIT THE MODEL. Total number of True Negatives: 100.0 Total number of False Positives: 0.0 \texttt{Number of samples with same length as attack payload: 1 )1

2

3

4

5

6

7

8

9

10

11

 

12

13

14

15

 

( • )Finally, use the artificial payload of the protocol you have selected. Test the artificial payload against your model (use testing mode as explained above). This packet should be accepted by your model. You should get an output that says “It fits the model”.

 

Task C

1. Preliminary reading. Please refer to the “Polymorphic Blending Attacks” paper. In particular, section 4.2 that describes how to evade 1-gram and the model implementation. More specifically we are focusing on the case where m <and the substitution is one-to-many.

2. We assume that the attacker has a specific payload (attack payload) that he would like to blend in with the normal

traffic. Also, we assume that the attacker has access to one packet (artificial profile payload) that is normal and is accepted as normal by the PAYL model.

 

3. The attackers goal is to transform the byte frequency of the attack traffic so that is matches the byte frequency of the normal traffic, and thus by-pass the PAYL model.

( • )Code provided: Please look at the Polymorphic blend directory. All files (including attack payload) for this task should be under this directory.

( • )How to run the code: Run task1.py. You will have to modify the port numbers according to the protocol you select in substitution.py (also commented in the sourcecode).

( • )Main function: task1.py contains all the functions that are called.

( • )Output: The code should generate a new payload that can successfully by-pass the PAYL model that you have found above (using your selected parameters). The new payload (output) is shellcode.bin + encrypted attack body + XOR table + padding. Please refer to the paper for full descriptions and definitions of Shellcode, attack body, XOR table and padding. The Shellcode is provided.

( • )Substitution table: We provide the skeleton for the code needed to generate a substitution table, based on the byte frequency of attack payload and artificial profile payload. According to the paper the substitution table has to be an array of length 256. For the purpose of implementation, the substitution table can be a python dictionary data structure. Since we are going to verify your substitution table, for the purpose of consistency, we ask you to use a dictionary data structure only(refer to appendix). You can use a list of values for a key in your substitution table. Your task is to complete the code for the substitution function. Also you are asked to implement the mapping as one-to-many.

( • )Padding: Similarly we have provided a skeleton for the padding function and we are asking you to complete the rest.

( • ) ( • )Main tasks: Please complete the code for the substitution.py and padding.py, to generate the new payload. Deliverables: Please deliver your code of the substitution, padding and the substitution table output (use print command to get it) along with the output of your code. Please see the deliverable section.

4. Test your output.

Test your output (below noted as output) against the PAYL model and verify that it is accepted. FP should be 100% indicating that the payload got accepted as legit, even though is malicious. You should run as follows and observe the following output:

 

( $ python wrapper.py Output Attack data provided, as command line argument Output ——————————————— Training Testing Total Number of testing samples: 7616 Percentage of True positives: XX.XX ————————————– Analysing attack data, of length1 Yes, calculated distance of YYYY is lesser than the threshold of XXXX. IT FITS THE MODEL. Total number of True Negatives: 0.0 Total number of False Positives: 100.0 )1

2

Deliverables & Rubric

( • )Task A: 35 points Please report the protocol that you used and the parameters that you found in a file named

parameters. Please report a decimal with 2 digit accuracy for each parameter.

Format:

( | | ) ( | | ) ( | | ) ( | | | | )Protocol:HTTP or Protocol:IRC Threshold:1.23 SmoothingFactor:1.24 TruePositiveRate:80.95

( • )Task B: 5 points Please append a new line in parameters with the score of the attack payload after completing Task B.

Format:

|Distance:2000|

· Task C: 60 points

–Code: 40 points. Please submit the code for substitution.py, substitution table.txt and

padding.py.

–Output: 20 points. Please submit your output of Task C generated as a new file after running task1.py.

 

A How to verify your task C:

If you only have 64-bit compiler, you need to run following:

( # Or whatever your current gcc version is sudo apt-get install lib32gcc-4.9-dev sudo apt-get install gcc-multilib )1

2

 

Next, then create a Makefile with following:

( a.out: shellcode.o payload.o gcc -g3 -m32 shellcode.o payload.o -o a.out shellcode.o: shellcode.S gcc -g3 -c shellcode.S -m32 -o shellcode.o payload.o: payload.bin objcopy -I binary -O elf32-i386 -B i386 payload.bin payload.o )1

2

3

4

5

6

 

Now, modify the hardcoded attack payload length at line no. 10 of shellcode.S with the length of your malicious at- tack payload. It should be an integer value equal to or the next multiple of 4 of your attack payload length. You can also get this number from task1.py and seeing what len(adjusted attack body) is. Without this the code wont point to the correct xor table location.

 

Next, you need to generate your payload. So, somewhere near the end of task1.py add the following to create your payload.bin:

( with open(“payload.bin”, “wb”) as payload_file: payload_file.write(’’.join(adjusted_attack_body + xor_table)) )1

2

 

Now, run task1.py to generate payload.bin and once it’s generated, run the makefile with make and then run a.out:

( make ./a.out )1

2

 

If all is well you should see your original packet contents. If not and you get a bunch of funny letters.. it didn’t work. Note: It was only tested on Linux, you might need to make a few modifications according to your system configuration.

 

A Sample substitution table.txt:

Below is the one-line output generated using “print substitution table” in python. Your substitution table.txt should look like this:

( {’t’: [(’Z’, 0.69), (’4’, 0.54), (’.’, 0.11), (’2’, 0.09), (’!’, 0.09), (’-’, 0.09), (’u ’, 0.07), (’x’, 0.07), (’9’, 0.05), (’v’, 0.05), (’,’, 0.04), (’k’, 0.04), (’)’, 0.009), (’(’, 0.008), (’5’, 0.008), (’F’, 0.007), (’&’, 0.0065), (’G’, 0.005), (’%’, 0.001), (’6’, 0.0001), (’B’, 0.0001), (’I’, 0.001), (’K’, 0.001), (’S’, 0.001), (’g’, 0.001), (’W’, 0.001)], ’.’: [(’s’, 0.041)], ’5’: [(’=’, 0.0225)], ’0’: [(’v’, 0.036) ], ’3’: [(’h’, 0.028)], ’1’: [(’\n’, 0.009)], ’9’: [(’”’, 0.025)], ’:’: [(“’”, 0.009) ], ’<’: [(’\’, 0.054)], ’F’: [(’m’, 0.029)], ’q’: [(’5’, 0.009)], ’b’: [(’c’, 0.04)], ’s’: [(’0’, 0.012)], ’u’: [(’b’, 0.0123)], ’o’: [(’>’, 0.035)], ’x’: [(’d’, 0.02)]} )1

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Computer Science: Application Multiple Choice

Computer Science: Application Multiple Choice

1. Which of these is a valid Java Karel command?

move;
move();
move()
move(5);

 

2. If Karel starts at Street 1 and Avenue 3 facing East, what row and column will Karel be on after this code runs?

move();

move();

move();

turnLeft();

move();

Street 1 and Avenue 3
Street 4 and Avenue 4
Street 2 and Avenue 6
Street 6 and Avenue 2

 

3. If Karel is facing North and the code

turnLeft();

turnLeft();

runs; which direction is Karel facing now?

North
South
East
West

 

4. What is the run method in a Java program with Karel?

A method that always makes Karel move in a square.
The method that is called just before the program ends.
The method that is called to start a Karel program.
The method that is called in order to move Karel one space forward.

 

5. What does a Karel run method look like?

A

public run()

{

//code

}

 

B

public run(){

//code

}

 

C

public void run

{

//code

}

 

D

public void run()

{

//code

}

A
B
C
D

 

6. What can be used to teach Karel to turn right?

Variables
Methods
Dog treats
Karel can already turn right

 

7. Why do we use methods in a Java program for Karel?

To stop the Java program
To display the current row that Karel is in
We do not use methods in Java
To teach Karel new commands

 

8. Which method will teach Karel how to spin in a circle one time?

A

private void spin()

{

turnRight();

}

 

B

private void spin()

{

turnLeft();

turnLeft();

turnLeft();

turnLeft();

}

 

C

private void spin()

{

turnLeft();

turnLeft();

}

 

D

private void spin()

{

move();

move();

move();

move();

}

A
B
C
D

 

9. Why do we use methods in Java programming?

Break down our program into smaller parts
Avoid repeating code
Make our program more readable
All of the above

 

10. What is top down design?

Top down design is a way of designing your program by starting with the biggest problem and breaking it down into smaller and smaller pieces that are easier to solve.
Top down design is a way that you can create designs on a computer to put on a web page
Top down design is a way of designing your programs starting with the individual commands first
Top down design is a way to use loops and classes to decompose the problem

 

11. What is a code comment?

A way to teach Karel a new word
A way to give notes to the reader to explain what your code is doing
A message to your teacher in code
A place to write whatever you want in your code

 

12. How do you write a SuperKarel class?

A

public class FunProgram extends Karel

 

B

private class FunProgram extends Karel

 

C

class FunProgram extends SuperKarel

 

D

public class FunProgram extends SuperKarel

A
B
C
D

 

13. What commands does SuperKarel know that regular Karel does not?

turnLeft() and jump()
turnRight() and jump()
turnLeft() and move()
turnAround() and turnRight()

 

14. Why do we use for loops in Java?

To break out of some block of code
To do something if a condition is true
To do something while a condition is true
To repeat something for a fixed number of times

 

15. Which general for loop definition is written correctly?

A

for(true)

{

// code

}

 

B

if(i<5)

{

//code

}

 

C

for(int i = 0; i < count; i++)

{

//code

}

 

D

while(condition)

{

//code

}

A
B
C
D

 

16. Why do we use while loops in Java?

To break out of some block of code
To do something if a condition is true
To repeat some code while a condition is true
To repeat something for a fixed number of times

 

17. Which general while loop definition is written correctly?

A

while(x is true)

{

// code

}

 

B

if(i<5)

{

//code

}

 

C

while(int i = 0; i < count; i++)

{

//code

}

 

D

while(condition)

{

//code

}

A
B
C
D

 

18. Why do we use if statements in Java?

To break out of some block of code
To do something only if a condition is true
To do something while a condition is true
To repeat something for a fixed number of times

 

19. Which general if statement definition is written correctly?

A

for(condition)

{

// code

}

 

B

if(condition)

{

//code

}

 

C

if(int i = 0; i < count; i++)

{

//code

}

 

D

if(false)

{

//code

}

A
B
C
D

 

20. Why do we use if/else statements in Java?

To repeat something for a fixed number of times
To either do something if a condition is true or do something else
To break out of some block of code
To repeat something while a condition is true

 

21. What does an if/else statement look like in Java?

A

if(condition)

{

//code

}

 

B

for(int i = 0; i < count; i++)

{

//code

}

 

C

if(condition)

{

//code

}

if(condition)

{

//code

}

 

D

if(condition)

{

//code

}

else

{

//code

}

A
B
C
D

 

22. What do we use control structures for in Java?

Control the flow of the program; how the commands execute.
Start our Java program
Store information for later
Teach Karel new commands

 

23. Which of the below are examples of control structures?

(I) if

(II) if/else

(III) while

(IV) for

I only
I and II only
III and I only
I, II, III, and IV

 

24. Why should a programmer indent their code?

Helps show the structure of the code
Easier for other people to understand
Indenting is a key part of good programming style
All of the above

 

25. To maintain good style, where should brackets go in Java programming?

Before defining the run method
Always on their own line
Right after defining a method
Two brackets per line

 

26. Which of these is a valid Karel command in Java?

move();
MOVE
move;
move()

 

27. Which of these is a valid Karel command in Java?

turnLeft();
turnleft();
turnLeft()
TurnLeft

28.

public class FunKarel extends Karel

{

public void run()

{

move();

putBall();

move();

}

}

What is the name of this class?

Karel
FunKarel
Run
SuperKarel

 

29. What is the name of the method that gets called to start a Java Karel program?

go()
start()
run()
move()

 

30. Which is the correct class signature for a Karel program named CleanupKarel?

public class CleanupKarel extends Karel
public class CleanupKarel < Karel
public CleanupKarel
CleanupKarel extends Karel

 

31. Which of these methods will create a method called turnRight that will have Karel turn left three times?

 

A

private void turnRight

{

turnLeft();

turnLeft();

turnLeft();

}

 

B

private void turnRight()

{

turnLeft

turnLeft

turnLeft

}

 

C

private void turnRight()

{

turnLeft();

turnLeft();

turnLeft();

}

 

D

private turnRight()

{

turnLeft();

turnLeft();

turnLeft();

}

A
B
C
D

 

32. What is a method in Karel?

A method is the name for an entire Java program
A method is something that lets you repeat a command a fixed number of times
A method is a command that Karel can do. It has a name and a set of instructions.
A method is the way that you leave a note for someone else who reads the code.

 

33. What is top down design?

Top down design is a way of designing your program by starting with the biggest problem and breaking it down into smaller and smaller pieces that are easier to solve.
Top down design is a way that you can create designs on a computer to put on a web page
Top down design is a way of designing your programs starting with the individual commands first
Top down design is a way to use loops and classes to decompose the problem

 

34. Which of these show the proper format for a Java comment?

 

A

/*

*

* My java comment

*/

 

B

/**

*

* My java comment

*/

 

C

// My java comment

 

D

All of the above

A
B
C
D

 

35. Which of these is a valid way to write a single line comment in Java?

 

A

// This is a comment

 

B

++ This is a comment

 

C

This is a comment //

 

D

/* This is a comment

A
B
C
D

 

36. What is the correct class signature in Java for a SuperKarel program?

 

A

public class FunKarel << SuperKarel

 

B

public class FunKarel extends Karel

 

C

public class FunKarel extends SuperKarel

 

D

public FunKarel extends SuperKarel

A
B
C
D

 

37. Which of these loops has Karel move 7 times?

 

A

for(int i = 1; i < 7; i++)

{

move();

}

 

B

for(int i = 0; i < 7; i++)

{

move();

}

 

C

for(int i = 0; i <= 7; i++)

{

move();

}

 

D

for(i = 0; i < 7; i++)

{

move();

}

A
B
C
D

 

38. Which of these loops will run correctly 10 times?

 

A

for(int i = 0, i < 10, i++)

{

turnLeft();

}

 

B

for(int i = 0; i < 10; i + 1)

{

turnLeft();

}

 

C

for(int i = 0; i < 10; i++)

{

turnLeft();

}

 

D

for(int i = 0; i > 10; i++)

{

turnLeft();

}

A
B
C
D

 

39. In the code snippet below, how many times will the putBall command run?

putBall();

 

for(int i = 0; i < 100; i++)

{

putBall();

}

 

for(int i = 0; i < 6; i++)

{

putBall();

}

100
1
3
106
107

 

40. Which of these code snippets would be the best implementation of a method called moveToWall() which moves Karel to the end of a row in the current direction?

 

A

while(frontIsClear())

{

move();

}

 

B

while(frontIsBlocked())

{

move();

}

 

C

while(frontIsClear())

{

turnLeft();

move();

}

 

D

for(int i = 0; i < 10; i++)

{

move();

}

A
B
C
D

 

41. If Karel is not on a tennis ball, and all directions around are clear:  https://s3.amazonaws.com/f.cl.ly/items/41372Z3y2b1j2N0U1m3e/Screen%20Shot%202015-11-11%20at%209.55.24%20PM.png

What will happen when running this code?

while(noBallsPresent())

{

turnLeft();

}

Karel will not do anything
Karel will go into an infinite loop
Karel will put down a tennis ball
Karel will turn left once

 

42. What is the correct syntax for an if statement?

 

A

if frontIsClear()

{

move();

}

 

B

if(frontIsClear())

{

move();

}

 

C

if(frontIsClear())

[

move();

]

 

D

if(frontIsClear())

(

move();

)

A
B
C
D

 

43. If Karel is directly in front of a wall, which condition would properly check if a wall is in front and turn left, and otherwise would move?

if(<CONDITION>)

{

turnLeft();

}

else

{

move();

}

leftIsClear()
frontIsClear()
facingNorth()
frontIsBlocked()

 

44. Say you want to write a program to have Karel put down 300 tennis balls. Which control structure would you use?

An if statement
A for loop
A while loop
A nested while loop

 

45. Say you want to write a program to have Karel to pick up all of the tennis balls in a location, but you don’t know how many tennis balls there are. Which control structure would you use?

An if statement
A for loop
A while loop
An if/else statement

 

46. What is a condition in Karel programming?

The place in code where the program stops running.
A method that returns a true or false answer. Typically used within a control structure.
The number of times a program loops.
What karel does during the program.

 

47. What is wrong with the style of this method declaration?

private void spinKarel() {

turnLeft();

turnLeft();

turnLeft();

turnLeft();

}

(I) Indenting is wrong

(II) No comment

(III) Not using camelCasing for name

(IV) Brackets are not on their own line

I only
I and II
IV only
I, II, and IV
II and III

 

48. What is wrong with this method declaration?

public karelDance()

[

move();

turnLeft();

move();

turnLeft();

move();

]

(I) Not using curly brackets

(II) Missing void

(III) Using public instead of private

(IV) Illegal characters in the method name

I only
I and II
I, II, and III
I, II, III, IV

 

49. How can we teach Karel new commands?

A for loop
A while loop
Define a new method
The run method

 

50. Why does a programmer indent their code?

Helps show the structure of the code.
Easier for other people to understand.
A key part of good programming style!
All of the above
 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Machine Learning- Perception,Linear Regression, Booting

Machine Learning- Perception,Linear Regression, Booting

Perceptron, SGD, Boosting

1. Consider running the Perceptron algorithm on a training set S arranged in a certain order. Now suppose we run it with the same initial weights and on the same training set but in a different order, S′. Does Perceptron make the same number of mistakes? Does it end up with the same final weights? If so, prove it. If not, give a counterexample, i.e. an S and S′ where order matters.

2. We have mainly focused on squared loss, but there are other interesting losses in machine learning. Consider the following loss function which we denote by φ(z) = max(0,−z). Let S be a training set (x1,y1), . . . , (xm,ym) where each xi ∈ Rn and yi ∈{−1, 1}. Consider running stochastic gradient descent (SGD) to find a weight vector w that minimizes 1

m

∑m i=1 φ(y

i · wTxi). Explain the explicit relationship between this algorithm and the Perceptron algorithm. Recall that for SGD, the update rule when the ith example is picked at random is

wnew = wold −η∇φ ( yiwTxi

) .

3. Here we will give an illustrative example of a weak learner for a simple concept class. Let the

domain be the real line, R, and let C refer to the concept class of “3-piece classifiers”, which are functions of the following form: for θ1 < θ2 and b ∈ {−1, 1}, hθ1,θ2,b(x) is b if x ∈ [θ1,θ2] and −b otherwise. In other words, they take a certain Boolean value inside a certain interval and the opposite value everywhere else. For example, h10,20,1(x) would be +1 on [10, 20], and −1 everywhere else. Let H refer to the simpler class of “decision stumps”, i.e. functions hθ,b such that h(x) is b for all x ≤ θ and −b otherwise.

(a) Show formally that for any distribution on R (assume finite support, for simplicity; i.e., assume the distribution is bounded within [−B, B] for some large B) and any unknown labeling function c ∈ C that is a 3-piece classifier, there exists a decision stump h ∈ H that has error at most 1/3, i.e. P[h(x) 6= c(x)] ≤ 1/3.

(b) Describe a simple, efficient procedure for finding a decision stump that minimizes error

with respect to a finite training set of size m. Such a procedure is called an empirical risk minimizer (ERM).

(c) Give a short intuitive explanation for why we should expect that we can easily pick m

sufficiently large that the training error is a good approximation of the true error, i.e. why we can ensure generalization. (Your answer should relate to what we have gained in going from requiring a learner for C to requiring a learner for H.) This lets us conclude that we can weakly learn C using H.

1

 

 

4. Consider an iteration of the AdaBoost algorithm (using notation from the video lecture on Boosting) where we have obtained classifer ht. Show that with respect to the distribution Dt+1 generated for the next iteration, ht has accuracy exactly 1/2.

2

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Exp22_Excel_Ch01_Cumulative_Medical

Exp22_Excel_Ch01_Cumulative_Medical

Exp22_Excel_Ch01_Cumulative_Medical

Exp22 Excel Ch01 Cumulative Medical

Excel Chapter 1 Cumulative – Medical Expenses

 

Project Description:

You track your medical expenses each month. You developed a worksheet that contains dates, descriptions, amount billed, and applicable copayments. Your health insurance provider details the amount not covered and how much was covered by insurance. You want to create formulas, format the worksheet to improve readability, and copy the worksheet to use as a template for the next month.

 

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

 

You notice that the 3/21/2024   Vision expense on Row 10 is a duplicate of the expense on Row 8. You will   delete the duplicate row.
Select and delete row 10 that contains the duplicate 3/21/2024 Vision data.

 

You want to move the 3/2/2024   Pharmacy expense to be listed in chronological order.
Select and cut row 11 that contains the 3/2/2024 Pharmacy expense and insert   cut cells on row 5.

 

You decide to insert a column   for additional data.
Insert a new column A and type Expense # in cell A3.

 

In the new column, you want to   create a coding system consisting of the year, month, and sequential   numbering for the expenses.
Type 2024-03-001 in cell A4 and use Auto Fill to   complete the expense numbers to the range A5:A11.

 

After filling in the expense   numbers, you will increase the column width to display the data.
Change the width of column A to 12.

 

The worksheet should have a   title above the data.
Type March   2024 Medical Expenses   in cell A1 and merge and center the title over the range A1:J1.

 

The title would look more   professional with some formatting.
Apply bold, Green font color, and 14-pt font size to the title in cell A1.

 

You decide to replace Lab with   Laboratory to provide a more descriptive expense name.
Find all occurrences of Lab and replace them with Laboratory.

 

The worksheet contains spelling   errors that need to be identified and corrected.
Check the spelling in the worksheet and correct all spelling errors.

 

You are ready to enter the first   formula to calculate the adjusted rate.
Calculate the Adjusted Bill in cell F4 by subtracting the Amt Not Covered   from the Amount Billed. Copy the formula to the range F5:F11.

 

Next, you will calculate the   amount owed.
Calculate the Amount Owed in cell I4 by subtracting the Insurance Paid and   Copay from the Adjusted Bill. Copy the formula to the range I5:I11.

 

Finally, you will calculate the   percentage paid of the adjusted bill.
Calculate the % Pd of Adj Bill in cell J4 by adding the Copay and Amount Owed   and then dividing that amount by the Adjusted Bill. Copy the formula to the   range J5:J11.

 

The first row of monetary values   should be formatted with dollar signs.
Apply Accounting Number Format to the range D4:I4.

 

For the remaining rows of   monetary values, you decide to apply Comma Style.
Apply Comma Style to the range D5:I11.

 

The percentage paid column needs   to be formatted with percent signs.
Apply Percent Style with one decimal place to the range J4:J11.

 

Formatting the column headings   on the third row will provide a professional appearance.
Wrap text, bold, and horizontally center the labels in the range A3:J3.

 

You want to continue formatting   the column headings.
Apply Green fill and apply Thick Bottom Border to the range A3:J3.

 

You will apply a cell style for   the last three columns that represent your costs.
Apply the Good cell style to the range H4:J11.

 

For the last column, you want to   format the percentages below the column heading.
Apply Align Right and indent once the data in the range J4:J11.

 

Changing the page orientation   will enable the data to better fit on a printout.
Select landscape orientation.

 

Next, you are ready to set the   top margin and center data on the page.
Set a 1-inch top margin and center the worksheet horizontally between the   left and right margins.

 

To make the data easier to read,   you will increase the scaling.
Set 110% scaling.

 

Insert a footer with the text Exploring   Series on the   left side, the sheet name code in the center, and the file name code on the   right side of the worksheet.

 

You will rename the sheet tab   and copy the worksheet to start creating a template for the next month.
Rename Sheet1 as March. Copy the worksheet, place the duplicate to the right, and then   rename it as April.

 

You will make some changes on   the April worksheet so that it will be ready for data entry.
Change the title to April 2024 Medical Expenses in the April worksheet. Delete   data in ranges A4:E11 and G4:H11. The formula results in column J display   #DIV/0! because you deleted the data, which generates a divide by zero error.   Type 1 in cell D4 and copy the value   to the range D5:D11 as placeholder values to avoid displaying the error   results

 

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

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Vending Machine JAVA Program Assignment Help

Vending Machine JAVA Program Assignment Help

Please do the following to complete this assignment.

Purpose:

The purpose of this project is to provide non-trivial practice in the use of Java object-oriented programming features to implement an object-oriented design and have a bit of fun doing it.

Resources Needed:

You will need a computer system with Java 7 or greater SE edition run-time and Java Development Kit (JDK). You may optionally use a Java IDE for example NetBeans, Eclipse, etc. However application builders are not allowed.

Submitted Files:

Design and Analysis:

This is an informal essay-style single-spaced word-processed document. The file formats accepted will be announced at project assignment. The length of the document should be between 1 and 1.5 pages. The following subjects should be discussed in this order:

1. General program design. How is the program organized? What major data structures were used? How did you divide the functionality among your classes? How are commands processed? Etc.

2. What alternative approaches were considered and why were they rejected?

3. What did you learn from doing this project and what would you do differently?

Source files:

Each public class must be contained in a separate Java source file. Only one source file will have a main() method and this source will be named VendingMachineSimulator.java. Other source/class names are up to you following the guidelines specified so far in the course.

The format of the Java source must meet the general Java coding style guidelines discussed so far during the course. Pay special attention to naming guidelines, use of appropriate variable names and types, variable scope (public, private, protected, etc.), indentation, and comments. Classes and methods should be commented with JavaDoc-style comments (see below). Please use course office hours or contact the instructor directly if there are any coding style questions.

JavaDocs:

Sources should be commented using JavaDoc-style comments for classes and methods. Each class should have a short comment on what it represents and use the @author annotation. Methods should have a short (usually 1 short sentence) description of what the results are of calling it. Parameters and returns should be documented with the @param and @return annotations respectively with a short comment on each.

JavaDocs must be generated against every project Java source file. They should be generated with a –private option (to document all protection-level classes) and a d [dir] option to place the resulting files in a javadocs directory/folder at the same level as your source files. See the JavaDocs demonstration for more details.

Submit file:

The submit file is to be a Zip file containing your design and analysis document, your Java sources, and your javadocs directory/folder. Any appropriate file name for this Zip file is acceptable.

If you know how to create a standard Java JAR file, this is also acceptable for your source code. However, make sure you include the source code in your JAR file.

Program Specification:

1. Create a new multi-class Java program which implements a vending machine simulator which contains the following functionality:

A) At program startup, the vending machine is loaded with a variety of products in a variety of packaging for example soda/tonic/Coke in bottles, peanuts in bags, juice in cartons, etc. Also included is the cost of each item. The program should be designed to easily load a different set of products easily (for example, from a file).

Also at program startup, money should be loaded into the vending machine. Money should consist of different monetary objects for the specified currency for example $1 bills, $5 bills, quarters, dimes, etc. Your program should be designed to use different national currencies easily (for example the Euro) without changing source code. Money should be maintained as paper bills and coins, not just amounts.

B) A menu of commands must be provided. At a minimum the menu should consists of the following commands:

1. Display the list of commands

2. Display the vending machine inventory. For each item, this command should result in displaying a description and current quantity.

3. Display the money currently held in the vending machine.

4. Purchase an item. The result of this selection should be the following actions:

1. Prompt the user to indicate what item to purchase

2. Prompt the user to specify what monetary items are being used for payment (the actual items for example quarters, dimes, etc.), not a money amount

3. If the user specified enough money to purchase the selected item, the item is purchased (deducted from inventory), supplied money is added to the vending machine, and any change is returned in the form of monetary items (quarters, dimes, etc.).

4. If the user did not specify enough money for the selected item, the transaction is aborted with the supplied money not added to the machine (not accepted) and the product not purchased (i.e. the state of the vending machine is unchanged).

5. Exit – exits the program displaying a departing message.

2. Additional points to consider:

A) You can use the Java Standard Edition (SE) API library as supplied by Oracle (AKA Sun) except the collection classes other than String and standard arrays (i.e. not ArrayList, Map, Vector, etc.). These other collections will be covered later in the course.

B) When developing complex classes, consider creating a main() method to test them out. Once tested successfully, delete the main() method.

C) You should generate error messages when appropriate, for example on invalid input values or not enough money supplied for the selected item to purchase. Exceptions will be covered later in the course so for this program displaying appropriate messages on the console is fine.

Other Activates:

1. Observe the presentation on JavaDocs.

2. Observe the Vending Machine Simulator demonstration for an example of one implementation.

3. Create a compressed zipped folder containing your Design and Analysis document, your Java source code files, and your javadocs folder.

4. Submit your compressed zipped folder.

Assignment Rubric:

Part 70% 80% 90% 100% % of
          Grade
Design and All but one subject All assigned All assigned subjects All assigned 15%
Analysis addressed with subjects address address with subjects address  
Document relevant, with mostly accurate and with accurate,  
  information. Few relevant relevant. Nicely relevant, and  
  minor information. formatted document. insightful  
  typographical Nicely formatted Document is within information. Very  
  issues. Document document. assigned length nicely formatted.  
  is close to assigned Document is   Document is  
  length close to assigned   within assigned  
    length   length  
Functionality Majority of Most required Nearly all required All required 55%
  required function function parts function parts work function parts  
  parts work as work as indicted as indicted in the work as indicted  
  indicted in the in the assignment text in the assignment  
  assignment text. assignment text above and submitted text above and  
  One major or 3 above and documentation. One submitted  
  minor defects. All submitted to two minor documentation.  
  major functionality documentation. defects.    
  at least partially One major or 3      

image1.jpg

image2.jpg working (example minor defects.      
  change provided All major      
  but not correct). functionality at      
  Design document least partially      
  does not fully working      
  reflect ((example      
  functionality. change provided      
    but not correct).      
Code Majority of the Most of the code Almost all code All code 25%
  code conforms to conforms to conforms to coding conforms to  
  coding standards as coding standards standards as coding standards  
  explained and as explained and explained and as explained and  
  demonstrated so far demonstrated so demonstrated so far demonstrated so  
  in the course (ex. far in the course in the course (ex. far in the course  
  method design, (ex. method method design, (ex. method  
  naming, design, naming, naming, formatting, design, naming,  
  formatting, etc.). formatting, etc.). etc.). One to two formatting, etc.).  
  Five to six minor Three to four minor coding Appropriate level  
  coding standard minor coding standard violations. of useful  
  violations. Some standard Appropriate level of comments.  
  useful comments. violations. useful comments. Complete  
  Some JavaDocs Mostly useful Public class JavaDocs as  
  commenting. comments. JavaDocs complete. specified. Code  
  Code compiles Public class Code compiles. compiles with no  
  with multiple JavaDocs Code compiles with errors or  
  warnings or fails to complete. Code no errors or warnings.  
  compile with compiles with warnings.    
  difficult to one to two      
  diagnose error. warnings.      
Submit More than one file All but one file All file submitted in All file submitted 5%
package submitted in submitted in correct format but in correct file  
  incorrect format. correct format. not in the specified formats and  
  Files not enclosed Files not compressed file. compressed as  
  in the specified enclosed in the   specified.  
  compressed file. specified      
    compressed file.  
 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Data Visualization For Business With Tableau Assignment Help

Data Visualization For Business With Tableau Assignment Help

ANL201 ECA Guidance

1. The overall intent of this assignment is to come up with data-rich visual evidence to help your target audience in their decision making process of whether they should show up in Singapore (either to work or to start a business presence here) So, your mission should be something similar to this.

2. Your four strategic objectives (one from each balanced scorecard perspective) are then designed to move you closer to achieving your mission.

3. Each of the strategic objectives should be positioned and phrased as something Singapore does well at, that is also relevant and an important consideration for your target audience in their decision-making process.

4. Your associated measures are then used as quantitative indicators to determine how well you are progressing on your strategic objectives

5. Your measures should be tightly coupled with your strategic objectives i.e. if a measure shows improvement/decline it should automatically mean that you are doing better/poorer on the associated strategic objective. Example: LTA has a strategic thrust of “An Inclusive Land Transport System” They have an indicator for the proportion of buses that are wheelchair friendly. If we see this proportion increase, it automatically means we are doing better on the strategic thrust of having an inclusive land transport system.

6. There should be interlinked relationship amongst the objective, measure and available data. If there is no data available for the measure for an objective, then you will have to consider modifying your objective or measure to something you have data for. So, you will have to be willing to change your objectives or measure given the data that is available to you.

7. You can use data from other reputable sources. Just remember not to have these non-data.gov.sg based measures overwhelm your charts.

8. Avoid zooming into any specific company for this assignment

9. A dashboard is analogous to an “elevator pitch”. Think about how you should design a one-page, stand-alone, self-explanatory, data-rich visual that conveys what decision makers should know about the current status and future of your project/organisation. As much as possible, design the dashboard to control the narrative to encourage the viewer to conclude you want.

10. In the question paper, Q1(e ) asks to create a single dashboard using the charts created in Q1 (d). However, Q1(g) allows for the possibility of submitting multiple dashboards. So, if you have more than one dashboard in your submission, please identify which is for Q1(e ).

11. The storyboard is your opportunity to lay out your visual evidence and explain (in a sequence of story points) to your target audience why they should consider Singapore.

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Data Mining Assignment 12

Data Mining Assignment 12

Intro to Data Mining

 

Dept. of Information Technology &

School of Computer and Information Sciences

 

Chapter 9 Assignment:

 

Data Mining Cluster Analysis: Advanced Concepts and Algorithms

 

 

Chapter 9 : Data Mining Cluster Analysis: Advanced Concepts and Algorithms – Check Point

 

Answer the following questions. Please ensure to use the Author, YYYY APA citations with any content brought into the assignment.

 

1. For sparse data, discuss why considering only the presence of non-zero values might give a more accurate view of the objects than considering the actual magnitudes of values. When would such an approach not be desirable?

 

2. Describe the change in the time complexity of K-means as the number of clusters to be found increases.

 

3. Discuss the advantages and disadvantages of treating clustering as an optimization problem. Among other factors, consider efficiency, non-determinism, and whether an optimization-based approach captures all types of clusterings that are of interest.

 

4. What is the time and space complexity of fuzzy c-means? Of SOM? How do these complexities compare to those of K-means?

 

5. Explain the difference between likelihood and probability.

 

6. Give an example of a set of clusters in which merging based on the closeness of clusters leads to a more natural set of clusters than merging based on the strength of connection (interconnectedness) of clusters.

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Microsoft Access assignment help

Microsoft Access assignment help

~ DrtvetrtcketDetails ~————————-<

USING MICROSOFT ACCESS 2016 Independent Project 7-5

Step 1: Download start file

Independent Project 7-5 The New York Department of Motor Vehicles wants to extend the functionality of its database. You use Design view to finish building a main form, add a calculated control to concatenate the first and last name, and add header and footer sections. You also add a table as a subform and customize the form to add sections, modify properties, enhance the look of the form, and add a calculated control. This project has been modified for use in SIMnet®.

Skills Covered in This Project • Edit a main form in Design view. • Add Form Header and Form Footer sections to a form. • Edit a control to add an expression to

concatenate fields. • Change the tab order of a control. • Change the border style property of a form.

• Remove the record selector. • Add and edit a control in a form. • Use a table as the subform in the SubForm Wizard. • Use an aggregate function on a subform. • Add a control on a main form that refers to a

control on a subform.

1. Open the NewYorkDMV-07 database start file.

2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.

3. Enable content in the security warning.

4. Complete the main form. a. Open the DriverTicketDetails form in Design view. b. Set the following property for the Form: Enter 6″ in the Width property. c. Set the following property for the Detail section: Enter 4″ in the Height property. d. Edit the Control Source property of the Unbound text box to contain =[FirstName] & “ ” &

[LastName], and enter 1.5″ in the Width property and DriverName in the Name property. e. Add Form Header and Form Footer sections to the form. f. Set the following properties for the Form Header section: Enter .6″ in the Height property and

select Green Accent 6, Lighter 80% (the tenth column, second row, in the Theme Colors area) in the Back Color property.

g. Set the following properties for the Form Footer section: Enter .6″ in the Height property and select Green Accent 6, Lighter 80% (the tenth column, second row, in the Theme Colors area) in the Back Color property.

h. Add a label control into the Form Header section and enter Ticket Details by Driver into the label.

i. Set the following properties for the label: Change the Width property to 2, the Height Property to .25″, the Top property to .2″, the Left property to 2.5″, the Font Size property to 14, and the Font Weight property to Bold.

j. Change the tab order of the controls to the following: LicenseNumber, Gender, DriverName, BirthDate, Address, City, State, and ZIP.

k. Remove the record selector from the form. l. Save your changes to the form. Your form

should look similar to Figure 7-115. 7-115 Design view of main form

Access 2016 Chapter 7 Creating Advanced Forms Last Updated: 1/11/18 Page 1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

T,d.,tDat” T City

ltmBI 2/1/2016 New York 4235894352 2/14/2016 Albany

4235895548 5/11/ZOlo Buffalo

*

PrimarvFactor

unsafe Speed

License umber

Name

Birth Date

Gender EE

Address 1274 w 145th street I City INewYork I State ~ ZIP 110039 I

TicketsSubform TlcketNumt • TlcketDate • City

4 235~3852 2/1/2016 New York

4235~352 2/14/2016 Albany

4 235~5648 5 / 11/2016 Buffa lo

*

RHord: ,~ 7 01 3 · • .i • l;.. No F 1t-2r searm

Fine FirstName LastName

$45.00 Amanda Freed

unsa es Parnng/

unsafe s

Passing/lan e Violations s120.00 Javier Torre s

unsafe speed $180.00 Alex Rodnguez

USING MICROSOFT ACCESS 2016 Independent Project 7-5

5. Add a table as a subform in a main form. a. Ensure that Use Control Wizards is turned on. b. Use the Subform/Subreport button to add a subform onto the main form. Position the subform

near the left border at the 2″ high mark. The SubForm Wizard launches. c. Choose Use existing Tables and Queries, add the TicketNumber, TicketDate, City,

PrimaryFactor, and Fine fields from the Tickets table into the Selected Fields area in the SubForm Wizard. Also add the FirstName and LastName field from the Officers table into the Selected Fields area.

d. Select the Show Tickets for each record in Drivers using LicenseNumber link statement. e. Enter the name TicketsSubform instead of the default name and click Finish. f. Save the main form. g. Switch to Layout view. The Detail section of the

form should be similar to Figure 7-116. h. Click the Next record arrow in the Navigation

bar of the main form to advance to the next record. Verify that the subform updates to display no tickets for Hossein Badkoobehi.

i. Switch to Design view. j. Increase the width of the main form to 9″ to

provide space to see and modify the subform. k. Set the following properties for the

TicketSubform: Enter 8.6″ in the Width property and .1″ in the Left property.

l. Save and close the form to ensure that all updates are correctly processed. If prompted, save your changes.

6. Customize the subform to adjust field widths and remove the border, Navigation bar, and label. a. Open the DriverTicketDetails form in Layout view. b. Click to select the TicketNumber column in the subform. c. Move the pointer to the right border until it changes to the resize arrow, and then click, hold,

and drag the resize arrow to increase the width of the column until you can see the entire label of TicketNumber. Recall that the column widths of a subform can only be changed in Layout view.

d. Adjust the width of the additional columns in the subform so they look similar to Figure 7-117. You may need to temporarily change the width of a few columns to smaller than shown to be able to adjust the LastName column.

7-116 Form view of the Detail section of the main form

7-117 Adjusted column widths in the subform

Access 2016 Chapter 7 Creating Advanced Forms Last Updated: 1/11/18 Page 2

e. Save your changes. f. Switch to Design view. If prompted, save your changes. g. Click to select the TicketsSubform and enter 1.2″ in the Height property, and select

Transparent in the Border Style property.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Ticket Details by Driver

UcenseNumber 10001532

Name

Birth Date

City New York

~ OrtYertkketDebls ~—————————— Ticket Details by Driver

LioemeNum ber ~ Gender ~

Name ~1mothy smnh

Birth Dale~

Addres5 (214 w 145th Street

Ctty !New York : State ~ ZIP 110039 I TlcketNumber • Tld:etDate City Prlma~Factor Fine FlrstName LastName 4235893852 2/1/2016 New York Unsafe Speed S4S.OO Amanda Freed 4235894352 2/14/2016 AIOOny Pnssmg/lone Violations Sl.20.00 Jevier To rres 4235895648 5/11/2016 BIJffalo Unsafe Speed 5180.00 AleK Rodriguez

*

Total a>’il o f fin- $345.00

USING MICROSOFT ACCESS 2016 Independent Project 7-5

h. Click the Select All box in the subform to select the subform. The Select All box in the subform updates to display a black square.

i. Select No in the Navigation Buttons property. j. Delete the TicketsSubform label. k. Save the form. l. Switch to Form view. Verify that the border, Navigation bar, and label have been removed.

7. Add a calculated control onto the subform and enter an aggregate function. a. Switch to Design view. b. Click, hold, and drag the vertical scroll bar on the subform to move down to the Form Footer

section. c. Click the Form Footer section bar and set the Height property to .5″. d. Add a text box control to the Form Footer section of the subform. e. Enter =Sum([Fine]) into the Control Source property and SFTotalFine in the Name property. f. Delete the label that was added with the text box. g. Save the form.

8. Add a text box to the main form and reference a control from the subform. a. Add a text box control below the subform and make the following changes to these

properties: Enter =[TicketsSubform].[Form]![SFTotalFine] into the Control Source property, .8″ in the Width property, 3.5″ in the Top property, and 5.4″ in the Left property. Select Currency in the Format property, Transparent in the Border Style property, Bold in the Font Weight, and No in the Tab Stop property.

b. Click the label control of that text box and make the following changes to these properties: Enter Total cost of fines in the Caption property, 1.2″ in the Width property, 3.5″ in the Top property, and 4.1″ in the Left property. Select Bold in the Font Weight.

c. Save the form.

9. Enhance the look of the form. a. Switch to Layout view. b. Select all of the controls shown in Figure 7-118. c. Press the right arrow key 20 times to move the

selected controls to the right. d. Set the following property for the Form: Change the

Scroll Bars property to Neither. e. Save the form.

10. Switch to Form view to view the completed form. The form should be similar to Figure 7-119. Depending on the default font size, the width of the fields in the subform, and the specific record you are viewing, scroll bars may display in the subform.

11. Close the database.

12. Upload and save your project file.

13. Submit project for grading.

7-118 Selected controls to move

7-119 Form view of completed main form with a subform

Step 2 Upload & Save

Step 3 Grade my Project

Access 2016 Chapter 7 Creating Advanced Forms Last Updated: 1/11/18 Page 3

 

  • Independent Project 7-5
    • Skills Covered in This Project
 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Exp19_Excel_App_Cap_Comp_Tech_Store

Exp19_Excel_App_Cap_Comp_Tech_Store

Grader – Instructions Excel 2019 Project

Exp19_Excel_App_Cap_Comp_Tech_Store
Project Description:
After graduating from college, you and three of your peers founded the software company TechStore Unlimited (TSU). TSU provides an online market place that fosters business to business (B2B), business to consumer (B2C), and consumer to consumer sales (C2C). As one of the company’s principal owners, you have decided to compile a report that details all aspects of the business, including: employee payroll, facility management, sales data, and product inventory. To complete the task you will duplicate existing formatting, import data from an Access database, utilize various conditional logic functions, complete an amortization table, visualize data with PivotTables and Power Maps, connect and transform several external data sources, and lastly you will inspect the workbook for issues.

Steps to Perform:
Step

Instructions

Points Possible

1

Start Excel. Open Exp19_Excel_AppCapstone_Comp.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

Fill the range A1:E1 from the Employee_Info worksheet across all worksheets, maintaining the formatting.

2

3

Make the New_Construction worksheet active and create Range Names based on the data in the range A6:B9.

2

4

Ungroup the worksheets and ensure the Employee_Info worksheet is active. Click cell G6 and enter a nested logical function that calculates employee 401K eligibility. If the employee is full time (FT) and was hired before the 401k cutoff date 1/1/19, then he or she is eligible and Y should be displayed, non-eligible employees should be indicated with a N. Be sure to utilize the date located in cell H3 as a reference in the formula. Use the fill handle to copy the function down completing the range G6:G25.

3

5

Apply conditional formatting to the range G6:G25 that highlights eligible employees with Green Fill with Dark Green text. Eligible employees are denoted with a Y in column G.

2

6

Create a Data Validation list in cell J7 based on the employee IDs located in the range A6:A25. Add the Input Message Select Employee ID and use the Stop Style Error Alert.

2

7

Enter a nested INDEX and MATCH function in cell K7 that examines the range B6:H25 and returns the corresponding employee information based on the match values in cell J7 and cell K6. Note K6 contains a validation list that can be used to select various lookup categories. Use the Data Validation list in cell J7 to select Employee_ID 31461 and select Salary in cell K6 to test the function.

2

8

Enter a conditional statistical function in cell K14 that calculates the total number of PT employees. Use the range E6:E25 to complete the function.

2

9

Enter a conditional statistical function in cell K15 that calculates the total value of PT employee salaries. Use the range E6:E25 to complete the function.

2

10

Enter a conditional statistical function in cell K16 that calculates the average value of PT employee salaries. Use the range E6:E25 to complete the function.

2

11

Enter a conditional statistical function in cell K17 that calculates the highest PT employee salary. Use the range E6:E25 to complete the function.

1.6

12

Apply Currency Number Format to the range K15:K17.

2

13

Click cell K11 and type FT. Click cell A28 and type Full Time Employees.

2

14

Use the Format Painter to apply the formatting from the cell A3 to the range A28:B28.

2

15

Use Advanced Filtering to restrict the data to only display FT employees based on the criteria in the range K10:K11. Place the results in cell A29.

3

16

Enter a database function in cell K18 to determine the total number of FT employees. To complete the function use the range A5:H25 as the database argument, cell E5 for the field, and the range K10:K11 for the criteria.

2

17

Enter a database function in cell K19 to determine the total value of FT employee salaries. To complete the function use the range A5:H25 as the database argument, cell H5 for the field, and the range K10:K11 for the criteria.

2

18

Enter a database function in cell K20 to determine the average FT employee salary. To complete the function use the range A5:H25 as the database argument, cell H5 for the field, and the range K10:K11 for the criteria.

3

19

Enter a database function in cell K21 to determine the highest FT salary. To complete the function use the range A5:H25 as the database argument, cell H5 for the field, and the range K10:K11 for the criteria.

3

20

Format the range K19:K21 with Currency Number Format.

2

21

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

3

22

Create the following three scenarios using Scenario Manager. The scenarios should change the cells B7, B8, and E6. Good B7 = .0312 B8 = 5 Most Likely B7 = .0575 B8 = 5 Bad B7 = .0625 B8 = 3 Create a Scenario Summary Report based on the value in cell B6. Format the new report appropriately and reorder the worksheets so the Scenario Summary worksheet appears as the last worksheet in the workbook.

7.4

23

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

4

24

Use the IPMT function in cell D12 to calculate the interest paid for the first payment of the loan. Use the information in the loan details section (E6:E9) of the worksheet to locate the required inputs for the function. Be sure to use the appropriate absolute, relative, or mixed cell references. All results should be formatted as positive numbers.

4

25

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

4

26

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.

4

27

Use the CUMIPMT function in cell G12 to calculate the cumulative interest paid on the first payment. Use the loan details information (E6:E9) as needed for inputs. Be sure to use the appropriate absolute, relative, or mixed cell references. All results should be formatted as positive values.

4

28

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. All results should be formatted as positive numbers.

4

29

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. Expand the width of columns D:H as needed.

4

30

Use PowerQuery to connect and open the Orders table in the eApp_Cap_Orders.accdb database. Use the Query editor to format column A with Date number format and load the table. Rename the worksheet Orders.

4

31

Adapt the previous step to connect and load the Warehouse table.

2

32

Connect to, but don’t load the Inventory table from the eApp_Cap_Orders.accdb database.

0

33

Create the following relationships. Relationship 1 Table Name Inventory Column (Foreign) Warehouse Table Warehouse Column (Primary) Warehouse Relationship 2 Table Orders Column (Foreign) Item_Number Table Inventory Column (Primary) Item_Number

3

34

Use PowerPivot to create a blank PivotTable on a new worksheet. Add the following fields to the PivotTable. Rows Warehouse: Location Warehouse: Warehouse Inventory: Item_Number Values Inventory: Current_Inventory Inventory: Total_Value

4

35

Insert a Slicer based on Warehouse. Place the upper left corner of the Slicer inside the borders of cell F3.

2

36

Create a 3D PowerMap that displays the location of all warehouses based on the City geographic type. Rename the worksheet Inventory.

1

37

Make the Orders worksheet active. Use the Data Analysis ToolPak to output Summary statistics starting in cell G3. The statistics should be based on the quantity of orders located in the range E1:E50. Be sure to include column headings in the output.

0

38

Record a macro using the Macro Recorder named Sort. When activated, the macro should sort the Orders table in ascending order by date. Open the newly created module in the Visual Basic Editor and copy the code in Module1. Paste the code starting in cell A1 on the Code worksheet.

1

39

On the Orders worksheet, insert a Form Control button labeled Sort in the range G21:I24 and assign the Sort macro.

2

40

Use the Accessibility Checker to inspect for issues. Once located, make the following changes to alleviate the issues. Warehouse worksheet Change Table Style to none. Orders worksheet Change Table Style to none. Employee_Info worksheet Change Font Color to Black, Text 1 New_Construction worksheet Change Font Color to Black, Text 1 Save the file Exp19_Excel_AppCapstone_Comp.xlsx. Exit Excel. Submit the file as directed.

1

Total Points

100

Created On: 12/09/2019 1 Exp19_Excel_App_Cap_Comp – Tech Store 1.3

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Solar Oven Final Report

Solar Oven Final Report

University  of  Arizona   Tucson,  AZ,  85716

S.A.C.R.A.W Solar Oven Prepared for: Dr. Stanley Pau

 

 

 

Jack Speelman Rebecca Nelson Paola “Andy” Lopez Lorin Greenwood Stephanie Gilboy October 21st 2012

Figure 1: Shows the team members of Team S.A.C.R.A.W standing alongside the final solar oven on Solar Oven Testing Day.

 

 

 

Team S.A.C.R.A.W Solar Oven 2

 

Table of Contents

 

 

Cover 1 Table of Contents 2 Executive Summary 3 Introduction – Motivation/Background/Key Terms 4-5 – Criteria and Constraints 5-6 Main Body – Functional and Design Requirements 6-7 – Design Theory and System Model 7-10 – Design Description – Conceptual Design 11-13 – Design Description – Final Design 14-17 – Design Justification 17-18 – Evaluation of Results 18-19 – Test Procedure 19-20 Design Critique and Summary 20-22 Appendix – First Oven Spreadsheet Data and Design/Drawing 22-24 – Final Oven Spreadsheet Data and Design/Drawing 25-27 – References 28

 

 

 

Team S.A.C.R.A.W Solar Oven 3

Executive Summary

The objective of the solar oven project was to design, build, and test a productive solar oven that

could reach an interior temperature of 100˚C. This was obtained through converting solar energy,

also known as electromagnetic energy, into thermal energy. The first law of thermodynamics

was utilized by understanding that energy cannot be created or destroyed but could be

transformed and used to heat the interior of the oven.

 

Two solar ovens were constructed in order to fully maximize the temperature inside the oven

chamber. The first oven was used as a prototype and research tool in order to build an oven that

could reach the optimum temperature calculated. The initial oven was predicted at 170.26˚C but

only reached an interior temperature of 85.6˚C. This produced a preforming index number of

1.02 and a cost index of 6.05˚C/dollar. The improved oven had a predicted temperature of

176.58˚C using the ambient air temperature and solar density provided. The oven reached an

interior temperature of 99.6˚C. The performing index for the second oven was calculated to be

1.28 and a cost index of 4.34˚C/dollar.

 

 

 

Team S.A.C.R.A.W Solar Oven 4

Introduction

Motivation:

• Learn the concept of team work and how to work together with other people to

achieve a common goal

• Gain proficiency in Excel, Solid Works, and basic solar oven knowledge.

• Acquire knowledge of the transformation of solar energy to heat.

• Learn the basics of the design and construction process

Background:

The main goal of the solar oven project was to find out the best way to change solar

energy into thermal energy. To do this, teams needed to know the first law of thermodynamics.

The first law states that energy cannot be created or destroyed, but can be changed from one

form to another. In the Solar Oven Theory, the energy that is put into the oven should equal the

energy that comes out (Ein=Eout). Therefore, the solar energy in joules should equal the thermal

energy in joules. The oven does this by taking the energy from the sunlight and transferring it

into heat. Since the energy in equals the energy out it allows the equation stated above to be

true. Knowing that the power is equal allows Team S.A.C.R.A.W. to find the temperature of the

oven chamber.

Mathematics for the Solar Oven with Key Terms:

Predicted Temperature:

 

 

 

Team S.A.C.R.A.W Solar Oven 5

Tio = Tambient + IoAw ⋅ G⋅ τ

n ⋅ a Usb ⋅ Asb +Uw ⋅ Aw( )

 

Variables:

Tio =the temperature inside the cooking chamber

Tambient= the outdoor temperature on the day tested

G= the gain from the reflectors

Uw= the heat transfer coefficient of the window

Aw= the area of the window

Usb= the heat transfer coefficient of the sides and bottom of the cooking chamber

Asb= the total area of the sides and bottom of the cooking chamber

Constants:

a= absorption coefficient of the cavity walls

τ= the optical transmission coefficient of the cavity walls

Io= the incident solar power density

Performing Index:

 

Variables:

Tio =the temperature inside the cooking chamber

Tambient= the outdoor temperature on the day tested

Tpredicted= the predicted temperature of the oven

Cost= the amount of money and labor for the solar oven in dollars

(Source: “Solar Oven Basics” Engineering 102)

 

 

 

Team S.A.C.R.A.W Solar Oven 6

Criteria and Constraints:

• Cooking oven must be equal to 1000 cm3

o Length (L) and height (h) need to be a minimum of 5cm

• The cooking oven window needs to be a square (W=L)

• The oven must have access for a digital thermometer and have a rack to support a biscuit

• There must be two calculated Performing Index’s calculated PISTOD and PIcost

• The maximum M/L ratio is 3

• The minimum final oven temperature is 100ᵒ C

• Optimal final temperature is over 200ᵒ C

• Focusing lenses and parabolic designs are not allowed

(Source: Solar Oven Design Project and Report Guidelines)

Main Body

Functional and Design Requirements The overall purpose of constructing the solar oven is to convert the absorbed solar energy into

heat. Different requirements of the oven were given in order to fulfill the purpose of constructing

and designing the solar oven. Functional requirements of the oven are needed in order for the

oven to actual work. Functional Requirements for the project include:

• The oven chamber must reach a minimum temperature of at least 100 degrees Celsius.

This is the minimum temperature that is needed to cook the biscuit inside the solar oven

chamber

 

 

 

Team S.A.C.R.A.W Solar Oven 7

• Using exactly four reflectors, sunlight needs to deflect into the Mylar windows at a

certain angle in order to achieve maximum temperature.

• Insulation is needed to surround the chamber to avoid loosing any form of heat.

• The four reflectors given need to be placed at a certain angle to not let any of the sunlight

be steered away from the Mylar window.

• An object or stand supporting the oven may be used in order to have the window be

exactly perpendicular to the sunlight. This way, all the sunlight can directly enter the

window.

Design requirements are given to avoid any advantage towards any oven. These requirements are

given in order for every oven to be at the same advantage. Design Requirements for the project

include:

• The oven chamber dimension should equal exactly 1000 cm3.

• Access to the inside of the solar oven should be fairly simple. Different methods to access

the oven include: lifting the top, door-opening mechanism, the sliding tray mechanism,

and many other methods.

• A hole or small opening for the thermometer to have access to the oven chamber

• The ratio of the reflectors and the width of the widow (M/L) should have a ratio of three

or less.

• The reflectors should be flat and straight; not parabolic. Having parabolic curves could

result in an explosion in the solar oven.

Design Theory and System Model

To conquer the solar oven, Team S.A.C.R.A.W. had to learn more about the theory

behind the solar oven. The knowledge learned can be implemented into constructing the most

 

 

 

Team S.A.C.R.A.W Solar Oven 8

efficient solar oven. The different dimensions of the oven are variables that affect how much

solar energy is converted heat inside the solar oven chamber. By knowing how these variables

affect the temperature allowed Team S.A.C.R.A.W. to reach optimum temperature inside the

oven. The equation predicting the temperature inside the chamber is:

Figure 2: Shows the equation to predict the temperature inside the solar oven chamber

 

 

In the equation above, the variables that have the most effect on the predicted temperature (Tio)

are the variables that are related to the dimensions of the solar oven chamber (Asb and Aw). Asb

represents the total surface areas of the side and bottoms of the solar oven chamber. Aw

represents the surface area of the window that heat is transferred through. Since a given

constraint of the oven is that the volume has to equal 1000 cm3, increasing the Aw would result in

decreasing the Asb. Since Aw appears in both the denominator and numerator in the equation,

increasing the value would have no affect in the Tio , but decreasing the Asb would result in a

higher Tio since the variable only appears in the denominator.

In the equation, G represents the gain of heat that the solar oven reflectors acquire. The

following equation is used to calculate the value of G:

Figure 3: Shows the equation to solve for G, and the variables that affect the value

 

As shown in Figure 3, the number of reflectors (represented by r) affects the overall gain.

Without any reflectors, the gain would equal just one. A higher M/L ratio for the oven would

also increase the gain value. The maximum value of M/L that is allowed is three, and as shown

Tio = Tambient + IoAw⋅ G⋅ τ

n⋅ a Usb⋅ Asb +Uw⋅ Aw( )

 

 

 

Team S.A.C.R.A.W Solar Oven 9

in Figure 3, the highest M/L would be most ideal. However, as shown in the table below, having

a higher M/L ratio results in a smaller alpha value.

Table 1: Shows the M/L ratio and the alpha and omega angle associated with each ratio

M/L α Ω 3 16.31° 106.31° 2 21.47° 111.47° 1 30° 120°

 

The alpha angle in the higher M/L ratio is smaller than the smallest M/L ratio, which is one.

However, when multiplying the M/L ratio by the sin (α), the highest value still comes out for the

M/L value that equals three. The highest M/L value is preferred for constructing the oven

because it will allow for the largest reflector gain for the oven.

Two values in the solar oven predicted temperature equation depend on the weather:

temperature (Tambient) and the solar irradiance of the sun (Io) at that time of day. If the

temperature outside is low, the Tambient will also be low, which would result in a lower Tio. If the

sky is cloudy and hardly any sunlight is penetrating through, the solar density will be low, which

would also result in a lower Tio. The weather is a major component in a higher Tio. Because

Arizona relatively tends to have high temperatures and large amounts of sunlight, both values

should be high.

Two other given constraints in the solar oven predicted temperature equation are τ

(optical transmission coefficient of window) and a (absorption coefficient of the cavity walls).

Team S.A.C.R.A.W wanted as much power to be absorbed into the solar oven chamber as

possible, which would result in a higher Tio To achieve such results, both τ and a have to be as

close to one as possible. The optical coefficient of the window depends on the type of window

material used. An ideal window material used for higher τ would have to be highly transparent

 

 

 

Team S.A.C.R.A.W Solar Oven 10

across the visible and near-infrared spectrum. The absorption coefficient of the cavity walls (a)

depends on the color used. The color black absorbs the highest amount of sunlight in comparison

with others colors, and thus has a higher absorption coefficient.

In the solar oven chamber predicted temperature equation, having a low U (overall heat

transfer coefficient) would result in a higher predicted temperature. The value of U is inversely

related to the thermal resistance to the flow of heat energy (R). The equation below shows the

relationship between both values:

Figures 4: Shows the relationship between the heat transfer coefficient and the thermal resistance

 

The equation shows that having multiple materials with higher thickness (x) and higher thermal

conductivity (k) values result in a higher R-value, which results in a lower U value. Having more

insulation materials (such as cardboard, foam, newspaper) decreases the overall heat transfer and

increases the Tio.

One of the main goals of Team S.A.C.R.A.W for constructing the solar oven chamber is

to have the actual temperature acquired equal to the predicted temperature calculated. However,

the task is nearly impossible. Different sources of error can prohibit Team S.A.C.R.A.W from

achieving the desired goal. One of the main errors is not having the dimensions of the solar oven

match up with the ones calculated. For example, if Team S.A.C.R.A.W calculated the area of the

window to be 0.02 m2 and instead constructed a window with an area of 0.018 m2, the result

would be an actual value of Tio that differs from the calculated Tio. Another source of error

includes the deterioration of the materials due to the heat. The materials to construct the oven

must be careful considered. If the materials used are not able to withstand the heat and melt, the

solar oven’s durability will also decrease.

 

 

 

Team S.A.C.R.A.W Solar Oven 11

Design Description – Conceptual Design

Figure 5: Shows the outer view of the first oven Figure 6: Shows the inside view (the insulation) of the first solar oven constructed

 

constructed.

 

 

 

 

The first goal for Team S.A.C.R.A.W was to generate a solar oven that met the minimum requirements given by the instructor. Since every solar oven group had two official

trials to test out the ovens, Team S.A.C.R.A.W.s’ main focus was to achieve the optimum

temperature possible with the given constraints. The variables in our oven focused around the

dimensions of the oven chamber. The following table lists the variables that were adjusted for the

first solar oven.

Table 2: Shows the design variables and measurements for the first solar oven constructed

Design Variables Measurements

Aw 0.01 m2

0.92

a 0.9

r 0.7

40

Usb 0.642192854

Asb 0.05 m2

 

 

 

Team S.A.C.R.A.W Solar Oven 12

Io 691 W/m2

40 degrees

Tambient 29.4 degrees Celsius

G 5.205335351

 

The materials that were given by the instructor to construct the oven include:

cardboard, Mylar sheets, black paper, scissors, and a thermometer. The cardboard given was

used to construct the four reflectors, the solar oven chamber, and the outer box where the solar

oven chamber and insulation was kept. Black Duct tape was used to connect the solar oven

chamber to the lid of the outer box. To get to the oven chamber, the reflectors and Mylar sheets

where lifted from a hole centered in the top lid. Inside the chamber, there was a rack that was

intended to hold to biscuit in place. To position the angle of the oven in order for the oven

chamber to absorb optimum heat, backpacks and notebooks were used.

Two Mylar sheets were used and were on top of one another. They were placed

directly above the solar oven chamber and were connected to the reflectors. Having more Mylar

sheets minimized heat losses through the window, while still allowing solar energy to be

transmitted into the oven chamber.

Four reflectors were used and were covered with aluminum foil. Aluminum foil was

used because the material is low-cost and effective. Despite trying to have a smooth aluminum

foil covering the reflectors, Team S.A.C.R.A.W had to take into account the wrinkled sheets,

which affects the overall temperature inside the oven. The shape of the four reflectors was a

trapezoid, and to keep things simple, the height of each reflector was 30 cm. Having a trapezoid

shape for the reflectors allowed no gaps between the reflectors, a problem that would arise if

rectangular reflectors were used. The base of the reflectors was 10 cm wide, conforming to the

 

 

 

Team S.A.C.R.A.W Solar Oven 13

dimensions of the width of the mylar windows. In order to maintain a M/L ratio of 3 or less, the

area of the window had to be 10 cm by 10 cm. To keep with the constraint of having a solar oven

chamber with a volume of 1000 cm3, the solar oven ended up being a cube.

To maximize insulation, Team S.A.C.R.A.W built a huge outer oven to store all of

the insulation. The dimensions of the outer box were 0.62×0.62×0.1 all measured in meters.

However, the insulation material used – newspaper and printer paper – was all wrinkled up and

was not arranged in an organized matter (as shown in Figure 4). However, having high

insulation, allowed for a higher Uxb value, which increases the Tio value overall. Also, to not let

any heat escape the sides of the oven, the thermometer was placed inside the oven. Having that

particular setup prevents obstruction of window and loss of heat through mylar window.

To obtain the dimensions for the oven, Team S.A.C.R.A.W worked from the inside

out, starting with the dimension of the solar oven chamber. To keep things simple, the solar oven

chamber was made into a cube, and from those dimensions, the height of the reflectors was

determined. Having a maximum volume of 1000 cm3 and a M/L ratio of less than three put a

constraint on the oven since it prohibited from further increasing the height of the reflector.

 

 

 

 

Team S.A.C.R.A.W Solar Oven 14

Design Description – Final Design

Figure 7: Shows the construction of the new reflectors Figure 8: Shows the side view of the for the final oven final solar oven on Solar Oven Testing Day

 

 

 

 

 

 

The first solar oven testing allowed Team S.A.C.R.A.W to see what adjustments were

needed for the second oven. Needless to say, many adjustments were done. A new goal was

created – to reach the minimum temperature required, which was 100 degrees Celsius. Also, the

group strived to try to achieve a higher Performance index by reducing the total materials used,

thus reducing the total performing index. To decrease the cost without falling under the

minimum temperature required (100 degrees Celsius), Team S.A.C.R.A.W. took the following

steps: create a smaller insulation box, create smaller and more efficient reflectors, and improve

the design of cooking chamber in order to maximize the total volume. The following variables in

the table below show the values used to calculate the predicted temperature

Table 3: Shows the list of variables used to predict the temperature inside the oven chamber.

Design Variables Measurements

Aw 0.013225 m2

0.92

a 0.9

 

 

 

Team S.A.C.R.A.W Solar Oven 15

40

Usb 0.642192854

Asb 0.0219206522 m2

Io 619 W/m2

40 degrees

Tambient 29.4 degrees Celsius

G 5.205335351

 

Somehow, the first solar oven had dimensions that did not match up with the dimensions

that Team S.A.C.R.A.W. calculated. The group that dissected the oven noticed that the alpha

angle did not match up with the value that Team S.A.C.R.A.W had calculated. The angle value

that Team S.A.C.R.A.W that used based off of the M/L value, which was equal to three. The

group that dissected Team S.A.C.R.A.W.s’ oven measured out the angle to be 15.687 degrees.

The angle that Team S.A.C.R.A.W. calculated based off the M/L ratio was 16.31. Also, the M/L

ratio (that the group who dissected Team S.A.C.R.A.Ws’ oven measured out) was higher than

three. Knowing that these dimensions were inaccurate from the proposed dimensions given by

Team S.A.C.R.A.W, Team S.A.C.R.A.W focused more on accurately measuring out the

dimensions of the oven and making sure that all of the dimensions calculated for the oven

matched up to the actual oven.

Instead of constructing a cubic oven chamber, a rectangular-shaped oven was generated.

Team S.A.C.R.A.W decreased the height of the oven chamber to increase the area of the top and

bottom sides of the oven chamber. Since the width and length of the Mylar sheets are correlated

with the dimensions of the top lid of the chamber oven, the area of the window also increased by

making the oven chamber rectangular. The length of the square Mylar sheet for the new oven

 

 

 

Team S.A.C.R.A.W Solar Oven 16

was determined to be 11.5 cm, which also increased the height of the trapezoidal reflectors to

34.5 cm (to keep in with the M/L ratio of 3).

During the trial for the first solar oven, Team S.A.C.R.A.W noted that tape used did not

properly hold the oven in its place. A new brand of tape was bought, Gorilla duct-tape, which

withstood the heat and did not melt or peel off when exposed to the sun. Although it increased

the Performance Index cost since it was more expensive than the previous adhesive, this new

duct-tape improved structural rigidity and high-temperature performance. The duct-tape was

applied along the sides and corners of any joint component of the oven, which resulted in a

stronger structure and prohibited any air from escaping.

Instead of using wrinkled up newspaper and printer paper for insulation, Polyurethane

insulation foam was used. This foam did a better job of trapping heat and hardly left any room

for letting heat escape. The foam also had greater thickness than the newspaper and higher

thermal conductivity, thus increasing the resistance to escaping heat. However, it was discovered

afterwards that the foam expanded during testing. This increased the dimensions of the outer box

and may have possibly decreased the volume of the solar oven chamber.

The last improvement done on the final solar oven would be the dimensions of the

reflectors. Since the alpha angle was off in the first solar oven, a protractor was used to insure

that the angle matches to the one calculated by Team S.A.C.R.A.W. These new reflectors were

longer, were angled correctly to match the alpha angle calculated (16.31 degrees Celsius), and

had a higher reflectivity (aluminum foil was much smoother). Also to avoid loosing more heat,

the chambers’ sides were still connected with the reflectors when they were cut out. This way,

instead of loosing heat between the gaps of the reflectors and chamber, hardly any heat would be

lost. To have access to the insides of the chamber, the chamber and reflectors were lifted (since

 

 

 

Team S.A.C.R.A.W Solar Oven 17

they were connected). The Mylar sheets were placed from the top and were put at the bottom of

the reflectors. The cut out of the Mylar sheets had a slightly larger area than the window

dimensions, allowing the extra surface area on the Mylar sheets to connect the mylar window to

the top of the solar chamber (area where the solar chamber and reflectors meet).

Much more care and accuracy was placed into constructing the final oven in comparison

with the first oven. Also, to absorb more heat, the inside solar chamber was painted black. Black

is known to absorb the most amount of heat in comparison with other colors. Team

S.A.C.R.A.W also made the outer chamber more aesthetically pleasing by decorating it with red

paint.

Design Justification

To keep the first solar oven simple, Team S.A.C.R.A.W. made the solar oven chamber,

the basis for the construction of the oven, a cube. Team S.A.C.R.A.W.s’ main focus was on

simplicity and the performance of the oven, thus generating an oven with large reflectors and a

cubic solar oven chamber. With those factors in mind, Team S.A.C.R.A.W designed the oven

and constructed the oven based on those parameters. An example of a performance-orientated

component of the oven was the insulation. Team S.A.C.R.A.W decided to use as much insulation

as possible to achieve optimum temperature and a greater performance overall. Unfortunately,

having more insulation in the outer box chamber resulted in more material usage and more open

space for heat to escape (a factor that was not thought of until the day of solar oven testing). In

addition, the size of the reflectors was maximized in relations with the width of the window

(M/L = 3). Both actions were done without a thought about the performance index cost.

 

 

 

Team S.A.C.R.A.W Solar Oven 18

Once the oven was tested and dissected by another team, Team S.A.C.R.A.W noticed that

keeping the oven simple was not a method to achieve maximum performance. The overuse of

materials and lack of accuracy of the dimensions made the performance index relatively low. As

a result, the oven had the lowest temperature acquired out of all the other ovens in the class. For

the second oven, the team decided to replace many materials with more durable options and

made sure that the oven matched the dimensions that were calculated. Less cardboard was used

on the solar chamber and outer chamber, and also, different insulation was used. However, to

obtain a higher temperature, the size of the reflectors was increased. Increasing reflector size

resulted in a higher area of window and a lower surface area of the sides and bottom of the

chamber. The outer chamber box was decreased to reduce the insulation and the new insulation,

foam, did a better job of not letting heat escape than the newspaper and printer paper. The

performance index cost reduced with the usage of less material for the first oven. Also, the

overall performing index of the new solar oven was higher than the first solar oven.

 

Evaluation of Results

Due to the cloudy day and lack of sunlight, the final test did not yield results consistent

with the teams’ expectations and did not verify the final design process. The solar power density

given before the Solar Oven Throw down did not match up with the actual solar density value of

the weather. The result caused the predicted temperature calculated beforehand to be much

higher than the one actually calculated on that day (calculated to be around 109 degrees). The

temperature of the final oven was 99.6 degrees Celsius with a performing index of 1.28. The

predicted temperature of the oven on that day was calculated to be 176.58 degrees Celsius. The

large difference in predicted and actual temperature resulted in a lower performing index.

 

 

 

Team S.A.C.R.A.W Solar Oven 19

However, the main focus for the second design of the solar oven was to improve on the

performing index and durability of the oven. The first oven had a performing index of 1.02, a

predicted temperature of 170.26 degrees Celsius, and an actual temperature of 85.8 degrees

Celsius. No issues regarding the weather occurred on the first solar oven trial day – the day was

filled with sunlight and heat. Although the final oven did not reach the desired 100 degree

Celsius temperature, the final oven had a greater performance and better durability than the first

oven, which was the overall goal for Team S.A.C.R.A.W.

 

Test Procedure

Table 4: Shows the values measured in both ovens.

Value Measured First Oven Second Oven

Predicted Temperature (Tio) 170. 26 degrees Celsius 176.58 degrees Celsius

Actual Temperature Acquired 85.8 degrees Celsius 99.6 degrees Celsius

Performing Index 1.02 1,23

Performing Cost 6.05 degrees Celsius/dollar 4.34 degrees Celsius/dollar

 

As shown in Table 4, the measured values are much smaller than the predicted values. For the

first oven, Team S.A.C.R.A.W. identified that the result of disagreement between the predicted

and actual temperature value was because of the lack of accuracy done on the dimensions of the

oven. The angles of the reflectors were not measured accurately – the angles were just assumed

to be correct based off of the dimensions of the reflectors. Also, the insulation was not tightly

packed together; instead there were many openings for heat escape through. For the second oven,

more accuracy was placed on the dimensions of the oven. Also, a different material was used for

 

 

 

Team S.A.C.R.A.W Solar Oven 20

the insulation. However, the reason for the vast difference in degree of the predicted and actual

temperature was because of the weather. The weather on solar oven testing day was much cooler

than predicted. Also, not that much sunlight was penetrating through the clouds, resulting in a

lower solar irradiance value (I0)). The change in weather is out of Team S.A.C.R.A.Ws’ control,

thus no conclusion can be made on what improvements could have been done for the final oven

aside from testing it on a different day that includes more sunlight and less clouds.

 

Design Critique and Summary

Team S.A.C.R.A.W.’s objective was to build a solar oven that met the constraints and

performed at optimum temperature. The team needed to build the oven at a low cost but still able

to withstand the high temperatures made on the day of testing. Teamwork, collaboration, and

attention to detail were implemented to produce an oven that had the potential to reach 176.58°

C. During construction of the first and second oven, the team realized many areas of

improvement that would raise the temperature of their oven and produce a more efficient

product. For example, in the first oven constructed, a flimsy duct tape was used to hold together

the corners of the exterior of the oven. When the first oven was tested, the tape could not

withstand the high temperatures and the adhesives began to fall apart. Because of the error, gaps

began to form around the exterior oven, which allowed heat to escape. To prevent this from

happening again, the second oven was constructed with a heavy-duty black duct tape that was

able to withstand much higher temperatures than what could be reached with the solar oven. If

 

 

 

Team S.A.C.R.A.W Solar Oven 21

another oven was to be built, it should be constructed with the heat resistant tape to minimize the

heat loss. Another area that needed improvement from the first oven to the second was the angle of

the reflectors relative to the oven container. Precise measurements needed to be done in order to

have the optimal angle that would allow the most solar light to be reflected into the cooking

chamber. To fix this problem, S.A.C.R.A.W. used a more accurate protractor and ruler when

measuring the reflectors for the second oven. This way the angle was accurate and allowed the

most light to be reflected into the cooking chamber. If this oven was to be reconstructed it is

recommended that an emphasis is put on the measurement of the angles because this has a direct

impact on the amount of light reflected into the cooking chamber. The more light reflected into

the cooking chamber, the higher the temperature will reach.

The insulating material is a large component of how much heat will be retained and how

much will be lost through the walls of the oven. In the first oven, the team used crumpled up

newspaper and printer paper to insulate the cooking chamber. While the crumpled up paper took

up a lot of room, there was a lot of room for the air to move around between the pieces of paper.

The conductive heat loss was maximized because the molecules had a lot of air to move around

in and therefore they did not hold very much heat. For the second oven constructed, insulating

foam was used inside the oven. The foam sealed all of the edges of the oven to heat was not lost

through those and it also reduced the movement of air inside the chamber. The heat stayed

localized to the chamber instead of freely moving about the oven and escaping. This was

essential for the oven built by S.A.C.R.A.W. because on the day of testing it was very cloudy out

and the ambient air temperature and the incident solar density were not very high. This meant

that the oven only increased temperature when there was direct sunlight. The data oscillated

 

 

 

Team S.A.C.R.A.W Solar Oven 22

because every time the sun would go behind the clouds, the temperature would drop slowly and

when the sun would come back out, the temperature would shoot up. Because of the foam

insulation and the minimal amount of heat escaping the oven, S.A.C.R.A.W.’s oven was able to

maintain a temperature higher than the ambient air temperature when the sun was behind the

clouds so when it did come out and provide direct sunlight, the temperature was able to start at a

higher initial temperature and rise from there. If there had been direct sunlight on the day of

testing, the oven would be considered a viable cooking unit that could be used if desired to cook

food for consumption. Appendix

First Oven Spreadsheet Data and Drawing

Figure 9: Shows the drawing and dimensions for the first solar oven.

 

 

 

 

 

Team S.A.C.R.A.W Solar Oven 23

Table 5: Shows the value of each variable and their value. It also states the variables’ units and description

Variable Value Units Description Io 619 watts/m2 (solar power density)

τ 0.92 (transmissivity for single layer of mylar)

a 0.9 (absorptivity of oven chamber and contents)

r 0.7 (reflectivity of Al foil) Tambient 29.4 C ambient temperature L 0.1 m length of oven window

h 0.1 m height of oven chamber n 2 # of layers of mylar M 0.3 m length of reflectors Aw 0.01 m2 area of the window

Asb 0.05 m2 area of the sides and bottom

Vchamber 0.00099981 m3 volume of the oven chamber

M/L 3 ratio of reflector length to oven window length

Usb 0.642192854 heat transfer coefficient of the chamber

α 0.284602961 angle of the reflectors with respect to the Sun’s rays

G 5.205335351 gain from reflectors Table 7: Shows the dimensions of the oven

Table 6: Shows the materials used for insulation

Wall Element Thickness (m)

Thermal Conductivity (watts/m-C)

Inner cardboard wall x1 0.003 k1 0.064 Insulation (wadded newspaper) x2 0.18 k2 0.123 Outer cardboard wall x3 0.003 k3 0.064

 

Dimension Length (m) Length of window 0.1 Width of window 0.1 Length of oven 0.6096 Width of oven 0.6096 Height of oven 0.1524 Length of chamber 0.1 Width of chamber 0.1 Height of chamber 0.1 Reflector length 0.3

 

 

 

Team S.A.C.R.A.W Solar Oven 24

 

 

 

 

 

 

Tio Uw(single) Uw(double) Table Combo 1

10.1 4.9 66 225.319429 13.9 6.7 93 179.723146 18.7 9 121 145.571651 24.3 11.7 149 121.231541 31.6 15.2 177 101.530964 40.1 19.4 204 87.1142525

Value Measured First Oven Predicted Temperature (Tio)

170. 26 degrees Celsius

Actual Temperature Acquired

85.8 degrees Celsius

Performing Index 1.02 Cost Index 6.05 degrees

Celsius/dollar

Component Amount Cost ($)

Total Cost for Component ($)

Grand Cost ($)

Reflectors $0.31 $1.25 $1.25 Interior Chamber

$0.40 $0.40 $1.59

Exterior Chamber

$1.94 $1.94 $3.73

Mylar Sheets $0.25 $0.50 With Reflectors

Duct Tape $0.50 $0.50 $8.73 Paper $0.03 $0.03 With interior

chamber Newspaper $0.00 $0.00 $8.73 Grand Total $8.73

Table 8: Shows the heat transfer values for the windows

Table 9: Final Results for the First Oven

Table 10: Shows the breakdown of costs for the first oven

Figure 10: Shows the graph and equations of line used to predict the temperature of the fist oven

 

 

 

Team S.A.C.R.A.W Solar Oven 25

Second Oven Spreadsheet Data and Drawing

Figure 11: shows the drawing and dimensions for the final oven

 

 

 

 

Team S.A.C.R.A.W Solar Oven 26

Table 11: Shows the values for the variables used to predict the Tio for the second oven Variable Combo 1 Units Description Io 619 watts/m2 (solar power density)

τ 0.92 (transmissivity for single layer of mylar)

a 0.9 (absorptivity of oven chamber and contents)

r 0.7 (reflectivity of Al foil) Tambient 29.4 C ambient temperature L 0.115 M length of oven window

h 0.115 M height of oven chamber n 2 # of layers of mylar M 0.345 M length of reflectors Aw 0.013225 m2 area of the window

Asb 0.048001 m2 area of the sides and bottom

Vchamber 0.00099981 m3 volume of the oven chamber

M/L 3 ratio of reflector length to oven window length

Usb 0.642192854 heat transfer coefficient of the chamber

α 0.284602961 angle of the reflectors with respect to the Sun’s rays

G 5.205335351 gain from reflectors Table 12: Shows the values for the insulation used. Table 13: Shows the dimensions of the oven

 

Wall Element Thickness (m)

Thermal Conductivity (watts/m-C)

Inner cardboard wall x1 0.003 k1 0.064 Insulation foam x2 0.0925 k2 0.21 Outer cardboard x3 0.003 k3 0.064

Dimension Length (m) Length of Window 0.115 Width of Window 0.115 Length of oven 0.3 Width of oven 0.3 Height of oven 0.197 Length of Chamber

0.115

Width of Chamber 0.115 Height of Chamber 0.0756 Reflector Length 0.345

 

 

 

Team S.A.C.R.A.W Solar Oven 27

 

 

 

Table #16: Shows the breakdown of costs for the final oven

 

 

Tio Uw(single) Uw(double) Table Combo 1

10.1 4.9 66 358.947717 13.9 6.7 93 285.790364 18.7 9 121 229.865253 24.3 11.7 149 189.399928 31.6 15.2 177 156.270352 40.1 19.4 204 131.809152

Value Measured Second Oven Predicted Temperature (Tio)

176.58 degrees Celsius

Actual Temperature Acquired

99.6 degrees Celsius

Performing Index 1,23 Cost Index 4.34 degrees

Celsius/dollar

Component Amount Cost $

Total Cost for Component $

Grand Cost $

Reflectors $0.43 $1.72 $1.72 Interior Chamber

$0.05 $0.05 $1.77

Exterior Chamber

$0.71 $0.71 $2.48

Mylar Sheets $0.25 $0.50 With Reflectors Duct Tape $5.00 $5.00 $7.48 Foam $5.00 $7.50 $14.98 Grand Total $14.98

Figure 12: Shows the graph and equation of lines used to determine the predicted temperature for the final oven

Table 14: Shows the heat transfer values for the windows   Table 15: Shows the final results for the final oven.

 

 

 

Team S.A.C.R.A.W Solar Oven 28

References References from Solar Oven Handout

“Solar Oven Basics” University of Arizona. Engineering 102. 2012

Shawyer, Michael and Avilio F. Medina Pizzali. “FAO Fisheries Technical Paper 436: The use

of ice on small fishing vessels.” FAO Corporate Document Repository. Food and

Agriculture Organization of the United Nations. Rome. 2003. Web. 13 Oct. 2012.

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!