General Instructions

Each model model must be presented in a clear manner with the variables designated through meaningful titles $($e$.$g$.,$ supply, demand, cost, revenue, etc.$)$ along with all relevant equations. When applicable, please make sure to embed the generated simulation results such as $$Forecast Charts$$ within your spreadsheet model. Please submit $3$ individual Excel files to the dropbox. Also, please clarify any assumptions that you utilize in creating your models.

Important:

The final exam is intended to be completed individually by each student without any collaboration or consultation. All students are expected to adhere to the Academic Code of Honesty that is reflected in your course syllabus as plagiarism will not be tolerated.

Good luck.

Question #$1$

A bank must keep enough money on hand in its ATM machine to satisfy customers' cash demands $.$ Suppose that the daily demand for cash follows a normal distribution with a mean of $$20,000$ and a standard deviation of $$1000.$

Create a spreadsheet that tracks the daily cash balance of the ATM machine throughout each day of the week $($i$.$e$.,$ Monday through Sunday$)$ assuming that the bank manager stocks the ATM machine with $$140,000$ at the beginning of each week $($i$.$e$.,$ Monday$).$

a$.$ What is the average ending cash amount at the end of the week on Sunday. Use $5000$ simulation runs as a default.

b$.$ What is the probability that the ending cash value on Sunday will be less than or equal to zero. Use $5000$ simulation trials as a default.

Make sure that you copy your forecast chart along with the relevant statistics $($i$.$e$.,$ split view$)$ into your spreadsheet. The probability should be displayed on the same Forecast Chart.

Problem #$2$

Assume that the current opening stock price for a company is $$9\mathrm{.}75$ per share. The daily historic changes in stock price over the last $500$ trading days have been analyzed resulting in the following frequency distribution:

Price Change Frequency

$-$$$5\mathrm{.}001$

$-$$$1\mathrm{.}0020$

$-$$$0\mathrm{.}5055$

$-$$$0\mathrm{.}3895$

$$0\mathrm{.}00140$

$$0\mathrm{.}38100$

$$0\mathrm{.}5073$

$$1\mathrm{.}0010$

$$2\mathrm{.}006$

SUM: $500$

Develop a spreadsheet to simulate the daily stock price performance over the next $30$ trading days. Use Crystal Ball to find the average ending$/$closing stock price $($i$.$e$.,$ at the end of day $30)$ using $5000$ runs. Make sure that you copy your forecast chart along with the relevant statistics into your spreadsheet using "Split View".