Assigned: 04/07/06
Due: 04/12/06 at the start of class (time will only be available in class on 4/07)
Remember: late penalties are heavy
Assignment:
Imagine that you have been asked to create the spreadsheet and graph shown on the attached pages. Note that formulas are required in all cells except for the basic info that cannot be calculated. Print the spreadsheet and the graph. Also, answer the question below by doing simple “what-if” analysis (answers can be handwritten on the print out of your spreadsheet or on the assignment sheet).
Turn In:
1) Prints Out of Spreadsheet and graph
2) Disk
3) Answer to the following question – obtained using manual what if analysis
“If the probability of reproduction increases to 0.6, what is the final population?”
Details:
1) The spreadsheet should be formatted roughly as shown, including title, headings, totals, etc. Much of this can be accomplished using Simple AutoFormat, but some adjustments will be necessary. The title should be centered across the main left columns using merge and center and be Bold. Column headings are bold text,, and each is in exactly one cell!! Border lines should be included as shown.
2) Time should be typed in as shown. It can be done quickly using Auto Fill using the fill handle (like coping formulas down) Number of Organisms at Time 0 should be typed in as ‘2’. Assumptions should be typed in: Probability of Reproducing should be typed in as the number ‘0.5’. Probability of Dying should be 0.1. (format with solid boundary and light blue background). All other values that you see should be calculated using formulas!!
3) Number of Offspring for a given period of time must be calculated using formulas. It needs to be a whole number – the smallest number equal to or below the number of organisms times the probability of reproducing. Use a two step process as seen in the spreadsheet. First calculate the number of offspring as if there could be fractional offspring, then in the next column drop down to the next smallest whole number. You will need to look up a function to help with this. Hints: mathematicians refer to whole numbers as integers. Computer scientists refer to rounding down (always, as opposed to rounding) as truncating. There is more than one function that will help with this task. Your formula must use absolute references for any references to Probability of Reproducing.
4) The New Subtotal column must be calculated using formulas. It is the total of the number of organisms and the number of offspring.
5) Number of Deaths for a given period of time must be calculated using formulas. It needs to be a whole number – the smallest number equal to or below the number of organisms (after births have been added to keep this simple!) times the probability of dying. Use a two step process as seen in the spreadsheet. First calculate the number of deaths as if there could be fractional deaths, then in the next column drop down to the next smallest whole number. You will use the same function as in step 3 to help with this. Your formula must use absolute references for any references to Probability of Dying.
6) The New Total column must be calculated using formulas. It is the result of deaths being removed from the subtotal..
7) The Number of Organisms at times beyond Time 0 must be calculated using formulas. It is merely the previous time’s “New Total.” (e.g. Time 2’s “Number of Organisms” is equal to Time 1’s “New Total”). You can put a formula with the equals sign and (just) the address of the other cell)
8) Use Conditional Formatting so that any numbers greater than 500 under “New Total” are red. DO NOT DO THIS BY HAND! For credit it must be done using conditional formatting.
9) Type your actual name where the example says “Your Name”.
10) Change the tab name to something appropriate (instead of sheet1)
11) Put the current date and time, file name and sheet name, page in the header and footer as shown – using fields so they automatically update.
12) Make sure that the whole spreadsheet prints on one page. Adjust under Page Setup if necessary.
13) Create the line graph from the data as shown. You may have to be careful with series so that the time periods are on the X-axis and are not themselves given a line.
14) Make X axis, Y-axis, and Graph Title as shown – including YOUR ACTUAL NAME in the title of the graph
15) Adjust the X-axis and Y-axis as shown – so that X-axis only goes up to time period 21, and so the Y –axis has minor ticks every 50, marked outside the box.
16) Make sure that the background color on the graph is white!!!
17) Print the spreadsheet and graph and the report. Make sure you turn in the answer to the question too!! (along with the file and print outs)