Please solve 8-17. You can use excel, and please explain/show all work. 30 35 40
ID: 3320953 • Letter: P
Question
Please solve 8-17. You can use excel, and please explain/show all work.
30 35 40 45 50 0.21 0.18 0.21 0.06 Chelsea h her suppl (a) Simu calcu cate t age y Shawn Bishop, a neuroscience PhD student at Clarksville University, has been having problems balancing his checkbook. His monthly income is derived from a graduate research assistant- ship; however, he also makes extra money in most months by tutoring undergraduates in their intro- ductory neurobiology course. His chances of vari- ous income levels are shown here (assume that this income is received at the beginning of each month): 8-17 abilit zine qua The Pari its famo store ha the dist 8-19 MONTHLY INCOME $850 S 900 $950 $1,000 PROBABILITY 0.35 0.25 0.25 0.15 DAIL Bishop has expenditures that vary from month to month, and he estimates that they will follow this distribution MONTHLY EXPENSES 800 $900 $1,000 $1,100 PROBABILITY 0.05 0.20 0.40 0.35 Each 1 sold f batch (a) S ci (b) T Bishop begins his final year with $1,500 in his checking account. Simulate the cash flow for 12 months and replicate your model N times to identify Bishop's (a) ending balance at the end of the year and (b) probability that he will have a neg- ative balance in any month. Chelsea Truman sells celebrity magazines on 8-20 Lion ay use Each 8-18Explanation / Answer
SIMULATION OF MONTHLY INCOME Random Number Range Table for Income Income level Probability Cumulative Range for the month Probability $850 0.35 0.35 00-34 $900 0.25 0.60 35-59 $950 0.25 0.85 60-84 $1,000 0.15 1.00 85-99 SIMULATED VALUES FOR TWELVE MONTHS Random numbers are generated by using RAND function in excel REPLICATED SECOND TIME REPLICATED THIRD TIME Month Random Month Random Month Random Number Income Number Income Number Income 1 74 $950 1 95 $1,000 1 89 $1,000 2 45 $900 2 86 $1,000 2 85 $1,000 3 72 $950 3 55 $850 3 25 $850 4 48 $900 4 13 $850 4 3 $850 5 45 $900 5 29 $850 5 74 $950 6 18 $850 6 18 $850 6 41 $900 7 51 $900 7 66 $950 7 90 $1,000 8 67 $950 8 52 $900 8 78 $950 9 92 $1,000 9 6 $850 9 33 $850 10 53 $900 10 16 $850 10 88 $1,000 11 82 $950 11 2 $850 11 2 $850 12 6 $850 12 65 $950 12 62 $950 SIMULATION OF MONTHLY EXPENSE Random Number Range Table for Expense Income level Probability Cumulative Range for the month Probability $800 0.05 0.05 00-04 $900 0.20 0.25 05-24 $1,000 0.40 0.65 25-64 $1,100 0.35 1.00 65-99 SIMULATED VALUES FOR TWELVE MONTHS Random numbers are generated by using RAND function in excel REPLICATED SECOND TIME REPLICATED THIRD TIME Month Random Monthly Month Random Monthly Month Random Monthly Number Expense Number Expense Number Expense 1 67 $1,100 1 65 $1,100 1 91 $1,100 2 70 $1,100 2 47 $1,000 2 20 $900 3 17 $900 3 63 $1,000 3 68 $1,100 4 76 $1,000 4 11 $900 4 17 $900 5 44 $1,000 5 84 $1,100 5 57 $1,000 6 54 $1,000 6 51 $1,000 6 75 $1,100 7 48 $1,000 7 25 $1,000 7 49 $1,000 8 94 $1,100 8 29 $1,000 8 60 $1,000 9 40 $1,000 9 64 $1,000 9 41 $1,000 10 64 $1,000 10 60 $1,000 10 6 $900 11 5 $800 11 3 $800 11 97 $1,100 12 38 $1,000 12 5 $900 12 74 $1,100 CASH FLOW BASED ON FIRST SIMULATION OF INCOME AND EXPENSES A B C=A+B D E=C-D Opening Income Total Cash Expense Month end Month Cash balance Received Available in the month Cash balance 1 1,500 $1,000 2,500 $1,100 1,400 2 1,400 $1,000 2,400 $1,100 1,300 3 1,300 $850 2,150 $900 1,250 4 1,250 $850 2,100 $1,000 1,100 5 1,100 $950 2,050 $1,000 1,050 6 1,050 $900 1,950 $1,000 950 7 950 $1,000 1,950 $1,000 950 8 950 $950 1,900 $1,100 800 9 800 $850 1,650 $1,000 650 10 650 $1,000 1,650 $1,000 650 11 650 $850 1,500 $800 700 12 700 $950 1,650 $1,000 650 REPLICATED FOR SECOND TIME A B C=A+B D E=C-D Opening Income Total Cash Expense Month end Month Cash balance Received Available in the month Cash balance 1 $1,500 $1,000 $2,500 $1,100 $1,400 2 $1,400 $1,000 $2,400 $1,000 $1,400 3 $1,400 $850 $2,250 $1,000 $1,250 4 $1,250 $850 $2,100 $900 $1,200 5 $1,200 $850 $2,050 $1,100 $950 6 $950 $850 $1,800 $1,000 $800 7 $800 $950 $1,750 $1,000 $750 8 $750 $900 $1,650 $1,000 $650 9 $650 $850 $1,500 $1,000 $500 10 $500 $850 $1,350 $1,000 $350 11 $350 $850 $1,200 $800 $400 12 $400 $950 $1,350 $900 $450 REPLICATED FOR THIRD TIME A B C=A+B D E=C-D Opening Income Total Cash Expense Month end Month Cash balance Received Available in the month Cash balance 1 $1,500 $1,000 $2,500 $1,100 $1,400 2 $1,400 $1,000 $2,400 $900 $1,500 3 $1,500 $850 $2,350 $1,100 $1,250 4 $1,250 $850 $2,100 $900 $1,200 5 $1,200 $850 $2,050 $1,000 $1,050 6 $1,050 $850 $1,900 $1,100 $800 7 $800 $950 $1,750 $1,000 $750 8 $750 $900 $1,650 $1,000 $650 9 $650 $850 $1,500 $1,000 $500 10 $500 $850 $1,350 $900 $450 11 $450 $850 $1,300 $1,100 $200 12 $200 $950 $1,150 $1,100 $50 ENDING Balance at the end of year: First simulation= $650 First Replication $450 Second Replication= $50 : AVERAGE= $ 383 (b) Out of 36 simulation, there was no instance of negative balance in any month Hence, we can conclude that the probability of cash out is negligible (Please remember, the answer will vary depending on the random numbers Since it is statistical problem of simulation, the answer Will not be exactly the same at every simulation. However, variation will be limited