Business Spreadsheet

How can I get the answer?

One branch of a bank must keep enough money on hand to satisfy customers' cash cemands. Suppose the daily demand for cash at a branch of the bank is independent and follows a normal distribution with means and standard deviation summarized as follows {in $1.0005]: Monday Tuesday Wednesday Thursday Friday Saturday Sunday Mean $1?5 $120 $90 $60 $120 $140 $65 St d d 3'7 ii $26 $13 $13 $12 $13 $21 $10 deviation An amoured truck delivers cash to this bank once a week. The manager of the bank can order any amount of cash he desires for1is delivery. Naturally. running out of cash in any week is very undesirable as customers of the bank expect to be able to withdlaw their deposits on demand. Keeping excessive cash reserves would guard against this situation. but implies opportunity loss at the same time. Assume there is no replenishment of cash during the week. [a] Suppose the bank manager follows the practice of keeping a beginning balance of $300000 to start each week. Create a simulation model on spreadsheet to track the daily cash balance throughout the week. [bl In Ea), count the number ofdays in the week the bank will run out of money and express it as a percentage out of seven days. [c1 Repeat the above simulation for 100 weeks. In each replication {week}, dis play two outputs: The number of days the bank will run out of money and express it as a percentage {as in {b]]. Find the average of each output. [Hint: You can repeat the simulation for 100 weeks by using data table tool in Excel] (dl From (cl. create a frequency table for the number of days in the week the bank will run out of money. Then convert it to relative frequency [in percentage]. Ee] What is the minimum amount of money that should be kept at the start at each week to ensure there is at most an average chance of 0.5% of running out of money over 100 weeks? Try in steps of $50 (in $1.0Ms} from the current value oi $300000 to $1,000,000. [Hint: You can complete it using the Scenario Manager tool in Excel] [ Use a scatter diagram {with smooth lines and markers] to plot the average chance [96] of running out of cash vs the beginning cash kept at the start of each week. Na me the diagram with the tide "ATM Cash Problem\