We Helped With This Excel Programming Homework: Have A Similar One?

In this assignment you will simulate the process of population decay by using random numbers. We can make random numbers simulate an event which can occur with any probability that we decide. In this assignment we will use random numbers to simulate the population decay process.

The population decay process is founds in many fields of Science and Engineering and can be stated as follows. Given an initial population with a certain probability of decay per unit time of a member of that population, then that population will decline in an exponential fashion.  The decay constants are dependent on the probabilty of decay.  Thus

                                                                   N=N_oe^-at  
    
Where No is the initial number and N is the number remaining at time t. 'a  is called the decay constant.

Some common examples of this process are as follows:

In radioactive decay, Uranium atoms spontaneously decay with a certain probability per unit time. The amount of Uranium present decays exponentially. Because the probability of the fission of a particular uranium atom in a given time is small, the decay constant is small.

In Biology given an initial population of rabbits whose chance of getting eaten by a predator is say one in five per week, then the population will decline exponentially. Here no account is taken of production of new rabbits.

In manufacturing of light bulbs (or any other components), if the probability of failure of the lightbulbs are say 1 
in four per year, then the population of light bulbs (with no replacements) will decline exponentially.

The assignment consists of simulating the decay process in the following way:

1)Use the =RAND( ) function to generate an event with a 75% probablity of  survival and a 25% probability of  failure. Since Random numbers are generated in the range 0-1, this can be done by choosing "survival"  if the random number falls between 0- 0.75 and  "failure"   if the random number falls between  0.75 and 1.0.

2) To model the process, for each sample, generate random numbers until say a "failure"  is achieved which indicates decay of that sample. So if it takes 10  random numbers to get a "failure" the individual has lived 10 units of time. This is repeated over and over again generating a "history" for a  reasonable sample size of individuals.

3) The simulation consists of many repetitions  to build up statistics of the lifetimes of the many individual samples. You build up statistics of how many individuals lived for 1 unit of time, how many for 2 units of time how many for 3 units etc. At the end of the simulations you have the history of a large number of individuals and one can construct a graph of number left vs time. This will look like an exponential decay. The equation is N=N₀e^-at, where N₀ is the initial number at time 0 and "a"  is the decay constant. Note that the half life of the decay is the time when the number has dropped to one half of N₀ and is related to the constant 'a' by the formula Half Life =  ln(2)/a.

4)To get the constant you do a semi log plot ln(N(t))   vs  t  which should yield points roughly in a straight line. Use the trend line tool in Excel to get the best" straight line fit and thus  get the constant.

On a single graph show the ln(N(t)) vs time and the straight line produced by using the constants provided by the  trend line tool.

ASSIGNMENT DETAILS

Note: that the file will take about a Megabyte of storage because we need many examples.

THE FOLLOWING COLUMN HEADINGS MAY BE USED: 
 

• Column 0: The "toss"  number, numbered one to 500 or one to 1000. Use more a few thousand.
• Column 1: The  "toss" . Use the =RAND( ) function to fill about 2000 cells with either 0  which represents "survival" or 1  which represents "failure".  There should be a 0 if the random number is between 0 and 0.75 and a 1 otherwise.  This represents a 1 in 4 probability of failure in a time period.
• You should have a cell to represent the number of samples.
• Column 2  and 3: Using a running count, generate the lifetime of the sample.(This will be illustrated in class). When "failure" has occurred the lifetime should be reset to 1.  In column 3 isolate just the individual lifetimes  and use this to calculate the average lifetime.
• Use the countif function or the frequency Excel function to find how many individuals "failed" after  1 period, 2 periods,........10 periods.
• Use the results to generate a  summary table as follows.

THE SUMMARY TABLE.

Your summary table should tabulate:

• time periods from 0 through 10
• The number of samples that have failed after  that amount of time.
• The number of samples remaining  N(t).
• The ln ( N(t)) to get a semi log plot
• The constant "a"  generated by the regression analysis of the semi-log plot.
• The average lifetime

Summary of Graphs

Generate a plot of  N(t)  vs t    (This should look like an exponential decay curve).

Generate a second graph  of  experimental ln (N(t)) and calculated ln( N(t))  vs t  Show the points only for the "experimental values"  and show the line only for the calculated values which you have plotted from the regression data.

Clearly show values for the decay constant, a;   the half life (calculated from "a") ; the average life.

Further explanation will be provided in class 
 

5 points for  column1. 
10 points for column 2 
5 points for column 3. 
5 points for  each for the 4 calculated items in the summary tables. 
10 points for the graphs, decay constant, half life. 
10 points for appearance and organization

Total 60 points