Assigned: 04/14/03
Due: 04/23/03 at the start of class (time will only be available in class on 04/14 – a good idea to complete before Excel test)
Assignment:
Imagine
that you have been asked to create the spreadsheet, XY Scatter graph with line
fit, and scenario summary report 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, graph, and scenario report.
Turn In:
1) Print out of spreadsheet, the graph, and the scenario summary report. Make sure you enter your name on the all pages - the main data spreadsheet, the graph, and the scenario summary report.
2) Disk, with your name on it.
3) Answer to the following question – obtained using Goal Seek
“What does the probability of reproduction need to be in order for the final population to be 15000?”
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 left 4 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. Number of Organisms at Time 0 should be typed in as ‘2’. Probability of Reproducing should be typed in as the number ‘0.5’ (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. 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 Total column must be calculated using formulas. It is the total of the two preceding columns.
5) The Number of Organisms at times beyond Time 0 must be calculated using formulas. It is merely the previous time’s “New Total.”
6) Type your actual name where the example says “Your Name”.
7) 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.
8) Make sure that the whole spreadsheet prints on one page. Adjust under Page Setup if necessary.
9) Create the XY scatter graph from the data as shown.
10) Make X axis, Y-axis, and Graph Title as shown – including YOUR ACTUAL NAME where the example has YOUR NAME HERE.
11) Adjust the X-axis and Y-axis as shown – so that X-axis only goes up to time period 20, and so minor ticks are shown inside the box.
12) Make sure that the background color on the graph is white!!!
13) Fit a curve to the data plotted, and include the equation and the R-squared value on the chart. Increase the size on them and make them bold so they show up clearly.
14) 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.
15) Create 7 scenarios for the spreadsheet using Excel’s capabilities – “Initial”, “Sixty Pct”, and “Seventy Pct”, etc. Use values for scenarios as shown in the printed scenario summary report. The result cell is the final population – the number of organisms at Time 20.
16) Add “Created Automatically by Your Name” to the bottom of the report (except use your actual name instead of “Your Name”). Replace row headings with the more clear: “Probability of reproducing” and “Final Population”. These are is the only things that you should type on this page! This report MUST be generated using Scenarios!!
17) Change the colors on the column header row for better readability.
18)
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.
19) Print the spreadsheet, graph and the report. Make sure you turn in the answer to the question too!!