Assignment

Name:- Jai Batra

Course:- [Link](Hons.) C.S.
Paper:- Mathematical Modelling with Excel
Roll no.:- 24/BS(H)CS/25748
 Index
[Link] Topic Remarks
1 LiNEAR MODEL
2 POWER MODEL
3 EXPONENTIAL MODEL
4 LOG MODEL
5 RADOACTIVE DECAY
6 Population growth
7 ANTBIOTIC MODEL
8 SAVING ACCOUNT
9 Linear Predator Prey Model
10 Non-Linear Predator Prey
Model
11 Diameter vs Volume
12 SIR Model
13 SIS MODEL
14 Free fall body problem
15 Fruit Basket
16 Diet Problem
17 Discrete Logistic Equation
 18 HoME Improvement Problem
19 Montey Hall Problem
20 Buffet Needle Problem
21 Birthday Problem
22 Flipping a coin

Experiment Number-1
Topic:- Linear Function
Ques. The table below contains the total length and weight of
20 black bears (data collected by Brett Troyer, 2011). Graph
weight vs. length, fit different linearizable models to the data, and
select the one that best fits the data. Briefly explain your
reasoning.
Length 139 138 139 120.5 149 141 150 166 180 129.5
Weight 110 60 90 60 85 95 85 155 220 105
Length 150 142 162 148 140 134 137 149 102 151.5
Weight 110 115 255 105 90 75 75 115 35 140
Ans.
Conclusion: The power model effectively describes the relationship
with LENGTH. The high 𝑅2 value further supports the model's reliability,
between LENGTH and WEIGHT, showing that WEIGHT increases exponentially

although some variability remains unexplained .

Experiment Number-2
Topic:- Power Model
Ques. The table below contains the total length and weight of
20 black bears (data collected by Brett Troyer, 2011). Graph
weight vs. length, fit different linearizable models to the data, and
select the one that best fits the data. Briefly explain your
reasoning.
Length 139 138 139 120.5 149 141 150 166 180 129.5
Weight 110 60 90 60 85 95 85 155 220 105
Length 150 142 162 148 140 134 137 149 102 151.5
Weight 110 115 255 105 90 75 75 115 35 140
Ans.

Conclusion:- The R2 value is 0.76, which indicates a good fit. This

means that 76% of the variation in WEIGHT can be explained by the LENGTH
using this power model. There is still 24% of variability due to other factors
not accounted for in this model

Experiment Number-3

Topic:- Exponential Model

Ques. The table below contains the total length and weight of
20 black bears (data collected by Brett Troyer, 2011). Graph
weight vs. length, fit different linearizable models to the data, and
select the one that best fits the data. Briefly explain your
reasoning.
Length 139 138 139 120.5 149 141 150 166 180 129.5
Weight 110 60 90 60 85 95 85 155 220 105
Length 150 142 162 148 140 134 137 149 102 151.5
Weight 110 115 255 105 90 75 75 115 35 140

Ans.

Conclusion:- The exponential model effectively captures the relationship

LENGTH increases. The 𝑅2 value of 0.768 supports the model's reliability, although
between LENGTH and WEIGHT, showing a rapid, nonlinear growth in WEIGHT as

there is still some degree of unexplained variability.

 Experiment Number-4
Topic:- Log Model
Ques. . The table below contains the total length and weight of
20 black bears (data collected by Brett Troyer, 2011). Graph
weight vs. length, fit different linearizable models to the data, and
select the one that best fits the data. Briefly explain your
reasoning.
Length 139 138 139 120.5 149 141 150 166 180 129.5
Weight 110 60 90 60 85 95 85 155 220 105
Length 150 142 162 148 140 134 137 149 102 151.5
Weight 110 115 255 105 90 75 75 115 35 140

Ans.
Conclusion:- The logarithmic model suggests that WEIGHT increases with
LENGTH, but at a decreasing rate (logarithmic growth).With an R² value of 0.601,
the model provides a moderate fit, meaning other factors may influence WEIGHT
beyond [Link] model could be useful for predicting weight based on length,
but it is not perfectly accurate. Further refinement or additional data may improve
the model.

Experiment Number-5
Topic:- Radioactive Decay
 Number of
radioactive
Time, t
nuclei remaining,
N

0 280

1 252

2 225

3 203

4 187

5 163

6 147

7 128

8 115

9 110

10 96
Conclusion:- The graph demonstrates a
11 89
clear exponential decay pattern, where
the values decrease rapidly at first and 12 75

then level off over time. The data points 13 70

closely follow the exponential decay 14 61

curve, confirming this trend. 15 57

16 49

17 45

18 39

19 35

20 29

21 26

22 24

23 20

24 16

25 15

26 14

27 12

28 11

29 11

30 11

31 9

32 9
 Experiment Number-6
Topic:-Population Growth
Ques:- In many situations involving populations, the larger the population,
the faster it grows. This suggests a proportionality relationship between the
population and the rate of growth. Table 2.3 shows the population (in
thousands) of bacteria in a Petri dish at different points in time. The third
column contains the change in population between time periods.

Day Actual Population Change in Population

(n) pn pn=pn+1-pn
0 10.3 6.9

1 17.2 9.8

2 27 18.3

3 45.3 34.9

4 80.2 45.1

5 125.3 50.9

6 176.2 79.4

7 255.6
x1 y1

10.3 6.9 0.478 478

Δpn= pn+1 − pn = 0.5pn ⇒ pn+1 = 1.5pn

255.6 122.1768
This model predicts that the population grows by about 50% each time period,

which means the population will grow without bound.

Conclusion:- The graph demonstrates a positive linear relationship between

population size and population growth, indicating a consistent increase in population over time.
The constant value in column D might suggest a fixed growth rate or a factor influencing this
trend.
 Experiment Number-7
Topic:- Antibiotic
Ques. Suppose the amount of a drug in a patient’s blood stream decreases at
the rate of 50% per hour and that an injection is given at the end of each hour
which increases the amount of drug in the blood stream by 0.2 units. a. Formulate
a model of the amount of drug in the blood stream at the end of each hour.

Ans. T=0 (Initial Time)

A0:-Principal=500
An:- Amount of drug @ time nth hour
An+1:- Amount of drug @ time (n+1)th hour
An+1=an/2+0.2

Conclusion:- The graph demonstrates that the amount of

antibiotic in the body increases rapidly at first and then
levels off over time. This suggests that the initial dose is
quickly absorbed, and the body gradually eliminates the
drug.

Experiment Number -8

Topic: Saving Account

Ques. Suppose you open a savings account that pays 5% interest
compounded yearly with a $500 initial deposit and make a $200 deposit at
the end of each year. a. Construct a model of the amount of money in the
account at the end of each year and define the variables.

Ans. r= 5% Annual Interest Rate

t=0 Initial Time
a0 - Principal=500
an - Amount @ time nth year
an+1 - Amount @ time (n+1)th year
an+1= an(1+(r/100)) +200
Total n= 30
a30= 14522.6102

Conclusion:- The table and graph illustrate the growth of an investment

over 30 years with an initial principal of 500, an annual interest rate of 5%,
and an additional annual deposit of [Link] investment grows significantly
over time, demonstrating the power of compound interest and consistent
contributions.
 Experiment Number-9
Topic:- A Linear Predator–Prey Model
Fn+1=(1-a)*Fn + b*Rn
Rn+1=-c*Fn +(1+d)*Rn
1-a= death rate of foxes
B= birth date of foxes
-c= death rate of rabbits
1+d= birth rate of rabbits
Death Birth
Foxes 0.5 0.4
Rabbits -0.17 1.1
Conclusion:- The graphs illustrate a predator-prey relationship
between foxes and rabbits. The fox population initially increases
as the rabbit population provides ample food, but then declines as
the rabbit population decreases due to predation. This leads to a
recovery in the rabbit population, which in turn allows the fox
population to grow again. The cycle repeats, resulting in
oscillations in both populations.

Experiment Number-10
Topic:- A Non-Linear Predator–Prey Model

Ques. Consider a forest that contains Foxes and Wolves which

compete for the same food resources. If Fn and Wn represent the
populations of foxes and wolves, respectively, at the end of
month n, a model for their populations is Fn+1 = 1.2 Fn − 0.001
FnWn Wn+1 = 1.3 Wn − 0.002 FnWn
Draw Graph Plot and draw Trajectory.
Factors
Death Birth
Foxes 0.88 0.0001
Rabbits -0.0003 1.039
Conclusion:- The graphs illustrate a complex predator-prey
relationship with oscillations in both fox and rabbit populations.
However, the oscillations in this scenario are less pronounced and
more chaotic compared to the previous example. This suggests a
more delicate balance between the two populations, where small
changes in factors like birth and death rates can lead to
significant fluctuations.
 Experiment Number-11
Topic:-Diameter vs Volume
Ques. The data below contain the diameter of the trunk at chest
height and volume of wood in several pine trees. Use the
trendline function in Excel to model Volume in terms of Diameter
with several different linearizable models and select the best one.
Briefly explain how you decided which one is best.
Diameter 32 29 24 45 20 30 26 40 24 18
Volume 185 109 95 300 30 125 55 246 60 15

Ans. SLOPE=(Y2-Y1)/(X2-X1)
SLOPE= 10.8940704
Y-INTERCEPT= -191.74923
Conclusions:- The graph shows a strong positive linear
relationship between the diameter of a sphere and its volume.
This means that as the diameter increases, the volume also
increases [Link] regression equation y = 10.884x -
191.75 and the high R-squared value of 0.9585 confirm this
strong linear relationship

Experiment Number-12
Topic:- SIR MODEL
Ques.

S:- Suspectible
I:- Infected
R:-Recovered
Infected= 3
Initial Population= 1000
Transmission rate= 0.00167
Recovery rate= 0.5

Epidermic Model

Conclusion:- The graph demonstrates a classic SIR (Susceptible-

Infected-Recovered) model of disease [Link], the number
of infected individuals increases rapidly, peaking around week 11.
This rapid spread is likely due to the high number of susceptible
individuals and the transmission [Link] the peak, the number
of infected individuals starts to decline as more individuals
recover from the disease and become immune. The epidemic
eventually subsides as the number of susceptible individuals
decreases.

Experiment Number-13
Topic:- SIS Model
Ques.
Population:- 1000
Transmission rate:- 0.00167
Recovery Rate:- 0.5
Initially Infected:-4

Conclusion:- The SIS model shows that the infection spreads

rapidly at first and then stabilizes at an equilibrium where the
infection persists. Higher infection rates increase the infected
population, while higher recovery rates reduce it. Since there’s no
immunity, the disease never disappears completely.

Experiment Number-14
Topic:- Free Fall Body Problem

Using Formula:- h= 1000-1/2*g*t^2

For t=0 to 30
Assuming Height =1000
Conclusion: The graph demonstrates the relationship between
time and height for a free-falling object. The height decreases
rapidly over time, as expected for an object falling under the
influence of gravity. The quadratic regression equation y =
5.8164x²- 33.541x+255.87 and the high R² value of 0.9866
indicate a strong fit to the data, confirming the parabolic nature
of the free fall motion.

Experiment Number:-15
Topic:- Fruit Basket Problem

Ques. The manager of a produce department at a neighborhood

supermarket is making fruit baskets for the busy holiday season.
He sells two sizes of baskets: small and large. He has only 200
apples and 100 oranges remaining and is trying to decide how
many of each size of basket he should make. Each small basket
returns a profit of $3 and requires 3 apples and 1 orange while
each large basket returns a profit of $4 and requires 2 apples and
2 oranges. Assuming he will sell all that he makes, how many of
each size should he make to maximize his profit?

SMALL LARGE
NUMBER 50 25 AMT USED AMT
AVA. APPLES 3 2 200 200
ORANGES 1 2 100 100
Total Profit
PROFIT 3 4 250

Conclusion:- The table presents a simple decision-making problem

involving the allocation of resources (small and large boxes) to
two products (apples and oranges).The optimal solution
maximizes the total profit by allocating 3 small boxes and 2 large
boxes to apples, and 1 small box and 2 large boxes to oranges,
resulting in a total profit of 250.

Experiment Number-16
Topic:- Diet Problem

Ques. John has decided to go on a diet to lose weight and has

limited himself to two types of food: protein shakes and pasta
(plus vitamin supplements). He is concerned with getting enough
protein and carbohydrates, but not too much fat. Table 7.2 lists
the amounts of these nutrients provided by each type of food
along with his daily requirements and cost information. How many
servings of each food should he eat each day to meet the
requirements and minimize the cost?

Minimize C = 0.75s + 1.25p

Subject to 1s + 35p ≥ 80
7s + 6p ≥ 75
5s + 1p ≤ 50
s, p ≥ 0

Conclusion:- The table shows a dietary analysis for a meal plan. It

calculates the optimal quantities of "Shake" and "Pasta" to meet
specific nutrient requirements while minimizing the total cost. he
has to consume 8.974895 units of shake and 2.029289 units of
pasta for meeting his diet requirements while minimizing the
cost . additionally, the minimized cost for the same is
$9.2677825.

Experiment Number-17
Topic:- Discrete Logistic Equation
Ques.

Soln. an+1= an+b(c-an)an

b and c are constants. This type of equation is often used to
model population growth where an is the population at time n.
The constant b is called the intrinsic growth rate and c is called
the carrying capacity.

an- Starting Population 10

b- growth rate 0.001
c- carrying capacity 1000
Conclusion:- The graph demonstrates a classic logistic growth
pattern, where the population initially grows exponentially but
then levels off as it approaches the carrying capacity. This is a
common pattern observed in populations with limited resources.

Experiment Number-18
Topic:- Home Improvement Decisions
Ques. Nathan and Laura are trying to sell their house which has
two bedrooms and two bathrooms. To increase the house’s value,
they want to remodel one or more rooms. They have estimated
the costs of remodeling each room and their real estate agent has
estimated the increase in the house’s value if each room was
remodeled as shown in Table 7.10 (where costs and increases in
values are given in thousands of dollars). They have only $10,000
to spend remodeling, and they have decided that they can’t do
both bathroom 2 and bedroom 2. They will only do bathroom 2 if
they also do bathroom 1. Also, they will only do bedroom 2 if they
also do bedroom 1. Which rooms should they remodel to
maximize the total increase in their house’s value?

Maximize Z = 9x1 + 5x2 + 6x3 + 4x4

Subject to 6x1 + 3x2 + 5x3 + 2x4 ≤ 10
x3 + x4 ≤ 1
−x1 + x3 ≤ 0
−x2 + x4 ≤ 0
x1, x2, x3, x4 are binary
Conclusion:- This Table Shows that how should they remodel to
maximize the total increase in their house’s value. n. It calculates
the optimal Constraint of "Bedroom and Bathroom" to maximize
Total Increase in house.
 Experiment Number-19
Topic: Montey Hall Problem
Ques. In the famous game show Let’s Make a Deal, hosted by
Monty Hall, one of the games required a contestant to choose one
of three doors. Behind one of the doors was a prize (like money or
a car), and behind the other two doors were dummy prizes (like a
donkey). Once the choice was made, Monty Hall would open up
one of the un-chosen doors revealing one of the dummy prizes.
The contestant was then given a choice to either switch to the
remaining unopened door or keep the door that was already
chosen. The contestant would get whatever “prize” was behind
the door. To illustrate this game, consider the scenario in Figure
6.17 where the real prize is behind door 2 (unbeknownst to the
contestant) and the contestant chooses door 1. Monty would then
open door 3 revealing a dummy prize. The contestant then had to
decide whether to switch to door 2 (and consequently win the real
prize) or don’t switch (and consequently not win the real prize).
The dilemma facing the contestant is the decision of whether to
switch or not. To help make this decision, we will use a simulation
to estimate the following two probabilities: 1. Probability of
winning if switching and 2. Probability of winning if not switching.
The key to estimating these probabilities is to realize that if not
switching, the only way to win is to initially choose the door with
the real prize. If not switching, the prize is won if the door with the
real prize is not initially chosen.

Ans. Total Trials= 1500

Win if switch= if(_<>_,1,0)
Win if don't switch= if(_=_,1,0)
P(switch)= 0.67133333
P(don't switch)= 0.32866667
P(switch)/(don't switch)= countif(_:_,"1")/total trial

Conclusion:- The table and calculations demonstrate the Monty

Hall problem, a classic probability puzzle. The results show that
switching doors leads to a higher probability of winning the prize
(approximately 68.4%) compared to not switching (approximately
31.6%).This counterintuitive result highlights the importance of
understanding conditional probability and the impact of changing
information on the likelihood of different outcomes.
 Experiment Number-20
Topic:- Buffon’s Needle Problem
Ques. A version of this problem was first solved by the French
naturalist and mathematician, the Comte de Buffon (1707-1788).
Suppose we randomly drop a needle of length L ≤ 1 on a wood
floor in which the joints between the planks are 1 unit apart. Find
the probability that the needle “hits,” or intersects, one of the
joints. Let x denote the distance of the midpoint of the needle to
the nearest joint between the planks and θ denote the angle as
illustrated in Figure 6.23. Note that 0 ≤ x ≤ 0.5 and 0 ≤ θ ≤ π/2.

To determine whether the needle intersects a joint, observe that

this can only occur if the hypotenuse of the right triangle in Figure
6.23 is less than L/2 (if the needle does not intersect a joint, then
the needle must be “extended” to form the triangle, making the

hypotenuse, then using trigonometry we have cos θ = x h ⇒ h =

hypotenuse more than L/2). If h denotes the length of the

x cos θ . Thus the needle intersects a joint if x/ cos θ < L/2.

L<=1
x is a mid pt of needle (L)
cos theta= x/h
h= x/cos theta
PROB. OF SUCCESS= countif(_:_,'1')/ total trials
TOTAL TRIALS= 1500
PROB. OF SUCCESS= 0.65
Conclusion:- The table and calculations demonstrate a simulation
of Buffon's needle experiment, a classic probability problem. The
experiment involves dropping a needle of length L onto a grid of
parallel lines with spacing H. The probability of the needle
intersecting a line is given by the formula (2L)/(πH). By
performing a large number of trials and counting the number of
hits, the simulation approximates the value of π. In this case, the
estimated value of π is 0.65466667, which is close to the actual
value of π (approximately 3.14159).
 Experiment Number-21
Topic:- The Birthday Problem
Ques. In a class of n students, what’s the probability that at least
two students will share a birthday (month and day)? This famous
problem is known as the birthday problem. We assume that
birthdays are uniformly distributed throughout the year (i.e. no
day is more or less likely to be a birthday than any other day) and
we ignore leap years.

Algorithm:- a. Randomly generate an integer between 1 and 365

for each student in the class to represent birthdays (1 = January
1, 2 = January 2, etc.). b. For each day of the year, count the
number of students in the class that have that day as their
birthday.
c. Determine if some birthday is shared by at least two students.
This is considered a success.
d. Determine the number of successes.
e. Calculate P (at least two people sharing a birthday) ≈ number
of successes number of trials .

Total Stud.= 1500

Total sum= 1483
Probability= 0.98866667
Total sum= sumif(_:_,">=2")
Conclusions:- The table shows the distribution of students'
birthdates. The probability of a student having a birthday on a
day with at least 2 other students is 0.98866667. This indicates a
high likelihood of shared birthdays within this group of students.

Experiment Number-22
Topic:- Flipping a Coin
Ques. Here we approximate the probability of getting at least 7
tails when a coin is flipped 10 times. In the terminology of
elementary probability theory, flipping a coin 10 times is a
random experiment, and getting at least 7 tails is an event. One
way of estimating the probability of an event is to perform the
random experiment many times, each time is called a trial, and
count the number of times the event occurred, called the number
of successes. We then calculate P(event) ≈ Number of successes
Total number of trials. We apply this approach to estimate P(at
least 7 tails).
Ans. Algorithm:-
a. Simulate 10 random flips of a coin and determine the number
of tails obtained. This is one trial.
b. Repeat for 1500 trials.
c. Calculate the number of trials in which at least 7 tails were
obtained. This is the number of successes.
d. Calculate P(least 7 tails) ≈ Number of successes 1500

0= tails 1= head
PROBABILITY= favourable/total
Favourable= countif(_:_,">=7")
Favourable:- 263
Trials:- 1500
PROBABILITY= 0.175333333

Conclusion:- The probability of getting atleast 7 tails in a single

trial is 0.17533333, calculated by dividing the number of
favorable outcomes (263) by the total number of trials (1500).The
simulation provides a practical way to estimate probabilities,
especially in scenarios where theoretical calculations might be
complex. By running a large number of trials, we can obtain a
reliable approximation of the true probability