As explained in Exercise 1, parts of recurrence calculations require explicit cell references because using named ranges would lead to circular references. In exercise 2, the implementation of:
Balance = previous balance + deposit – withdrawal
will use explicit cell references for the previous balance part, but can still use named ranges for deposit and withdrawal. Building this formula is done in much the same way as in Exercise 1.
This exercise is essentially a Compound Interest problem, but with a wrinkle to create the illusion that it adjusts itself automatically to show only enough rows to match the number of Periods. You fill down a sufficient number of rows to cover the worst case, i.e. the largest number of Periods you expect to ever encounter. The formulas produce null strings, i.e. text with no characters, in the Data View, but the cells are NOT blank. They contain formulas.
The formula for the Period column is outlined in the middle of page 5-5. It is fairly straight forward to translate it into an Excel formula. "increment the Period value by 1" in the true_part of the IF is the recurrence calculation, just add 1 to the cell immediately above. The Capital column also uses an IF. It can be outlined as follows:
=IF(ISNUMBER(cell to left), calculate interest and add to capital, "")
Lastly, be careful how you calculate the length of the Term. It is
=YEAR(maturity date) – YEAR(investment date)
and then format it as a number.
Warning: If you enter dates in the form xx/xx/xxxx, be very careful. Canada and the United States differ in the order of month and day. Remember that Excel is a product of MicroSoft, an American company.
This exercise looks horribly complicated, but it isn't really. It's just a matter of getting organized and giving the various cells in the Parameters worksheet and columns in the PopulationGrowth worksheets suitable named ranges. Once you've done that the formulas for the calculations in columns D to K will look exactly like those given on page 5-7 and 5-8. Columns A and B hold the recurrence calculations using explicit cell references and column C is also a named range calculation.
Named ranges for the values in the Parameters worksheet should be a combination of the column and row headings. For example, cell B5 should be named Light_Reproduction_Rate, and so on. For the PopulationGrowth worksheet, give the columns names like: Light_Population (column A), Total (column C), Light_Survivor (column D), etc. By a column we mean a range starting in row 3 to, say, row 502, if 500 rows are desired. With these named ranges the formula you enter into cell D3 will look like:
=Light_Population − Light_Population * Light_Predition_Rate
The recurrence formula you enter in cell A4 looks like:
=D3 + E3 − F3 + J3 − G3
which is how the "Next population" formula for light moths on page 5-7 is translated into Excel.
Formatting the column headings and generating the graph are done as in earlier labs.
Warning:The Parameters worksheet (with graph) that you submit should contain the parameter values you've chosen to reproduce the graph in figure 5.7. Finding these values is very difficult. We will accept any reasonable effort to obtain a graph that is somewhere between those shown in figures 5.6 and 5.7. Appreciate that doing a "what-if" analysis can be difficult.
This exercise is practically a rerun of the Pepper Moth simulation. A couple of things are new: In Exercise 5a you import some experimental data into Excel. This becomes part of Exercise 5b.
There are some new wrinkles in creating the graph. The six "series" (curves) you plot come from two different worksheets. In step 2 of the Chart Wizard (Excel 2003), under the "Series" tab you'll find boxes labeled "X Values" and "Y Values". The "Y Values" are the curve and the "X Values" are the x- or time-axis. These "X Values" come from column A of the same worksheet as the "Y Values". Notice these are different on the Simulation worksheet and the ThyroxinData worksheet. Use values from the same worksheet for the X and Y Values, otherwise the curves won't line up properly.
After you are done creating the chart, you will have six curves of markers. Now you turn the three that came from the Simulation worksheet into lines. Double-click on any marker from the desired curve. A dialog box comes up that let's you change it to a line.
Similar issues arise in Excel 2007, but there the chart is built piece-by-piece, so drawing data from two separate worksheets is more straightforward.
Next comes the fun part: changing the three rates on the parameters worksheet so that the lines (the simulation) match the related markers (the corresponding experimental data). As with the Pepper Moth Simulation, do your best to reproduce figure 5.14, but you'll find it a similarly frustrating experience.