Question

Historical data indicate that a student’s income for any month of school from work, parents, scholarships, and loans is consistent with the following probability distribution:

Income Probability
$750 0.20
$950 0.36
$1150 0.30
$1350 0.14
Expenses for the same student are believed to be consistent with the following probability distribution:
Expense Probability
$900 0.40
$1000 0.25
$1100 0.20
$1200 0.15
Assuming the student begins the school year with a balance of $1200, use Excel to simulate 12 months of activity and to predict the position of the student at the end of the year.

Answer 1

The position of the student at the end of 12 months is $1900

Explanation:

From the given information:

The objective of this question is to use Excel to simulate 12 months of activity and to predict the position of the student at the end of the year.

The table below shows the data computed into the excel worksheet and the result gotten.

Initial Balance 1200

Month U Income Expense Balance at month end

1 0.738100256 1150 1100 1250

2 0.219674065 950 900 1300

3 0.622637417 1150 1000 1450

4 0.721004276 1150 1100 1500

5 0.543855233 950 1000 1450

6 0.209865042 950 900 1500

7 0.758422397 1150 1100 1550

8 0.932588776 1350 1200 1700

9 0.361888069 950 900 1750

10 0.343300893 950 900 1800

11 0.946833427 1350 1200 1950

12 0.427857569 950 1000 1900

The position of the student at the end of 12 months is $1900

At U column, a random number is generated from U(0,1)

To generate from the income distribution, we have:

750 if U<0.2

950 if 0.2<U<0.56

1150 if 0.56<U<0.86

1350 if 0.86<U<1

To generate from the expense distribution, we have:

900 if U<0.4

1000 if 0.4<U<0.65

1100 if 0.64<U<0.85

1200 if 0.85<U<1.00