Question

what are the formulas for each column so i can understand where the numbers are coming from. refer to Question: Historical data indicate that a student's income for any month...part of the answer has been posted on Chegg but the formulas are needed.Thanks!

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.

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:

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

Answer 1

First we need to find the cumulative probabilities and the random number intervals for monthly income

			Random Number interval
Income	Probability	Cumulative probability	From	To
$750	0.2	0.2	0	0.2
$950	0.36	0.56	0.2	0.56
$1,150	0.3	0.86	0.56	0.86
$1,350	0.14	1	0.86	1

To simulate the income for any given month we use the following steps

• generate a random number from uniform distribution in the interval (0,1) using =RAND()
• Check the interval in which the random number lies and take the corresponding income
  • for example if the random number generated is 0.45, it lies in the interval 0.2 to 0.56 and hence income for the month is $950

Do the same for the expenses

			Random Number interval
Expense	Probability	Cumulative probability	From	To
$900	0.4	0.4	0	0.4
$1,000	0.25	0.65	0.4	0.65
$1,100	0.2	0.85	0.65	0.85
$1,200	0.15	1	0.85	1

Do this for 12 months and get the final balance

prepare the following sheet

get this

copy the income and expenses as values to avoid frequent changes.

In this simulation we can see that the position of the student at the end of the year is $1,100

This is 1 trial of 12 months. We would need to do this many times and take an average, if we need the expected value of the final position.