Develop a spreadsheet implementation for this model
The Miller–Orr model in finance addresses the problem of managing its cash position by purchasing or selling securities at a transaction cost in order to lower or raise its cash position. That is, the firm needs to have enough cash on hand to meet its obligations, but does not want to maintain too high a cash balance because it loses the opportunity for earning higher interest by investing in other securities. The Miller–Orr model assumes that the firm will maintain a minimum cash balance, m , a maximum cash balance, M , and an ideal level, R , called the return point. Cash is managed using a decision rule that states that whenever the cash balance falls to m, R – m securities are sold to bring the balance up to the return point. When the cash balance rises to M, M – R securities are purchased to reduce the cash balance back to the return point. Using some advanced mathematics, the return point and maximum cash balance levels are shown to be: R = m + Z M = R + 2Z Where For example, if the premium is 4,, r = 0.04>365. To apply the model, note that we do not need to know the actual demand for cash, only the daily variance. Essentially, the Miller–Orr model determines the decision rule that minimizes the expected costs of making the cash-security transactions and the expected opportunity costs of maintaining the cash balance based on the variance of the cash requirements. Suppose that the daily requirements are normally distributed with a mean of 0 and variance of $60,000. Assume a transaction cost equal to $35, with an interest rate premium of 4%, and a required minimum balance of $7,500. Develop a spreadsheet implementation for this model. Apply Monte Carlo simulation to simulate the cash balance over the next year (365 days). Your simulation should apply the decision rule that if the cash balance for the current day is less than or equal to the minimum level, sell securities to bring the balance up to the return point. Otherwise, if the cash balance exceeds the upper limit, buy enough securities (i.e., subtract an amount of cash) to bring the balance back down to the return point. If neither of these conditions hold, there is no transaction and the balance for the next day is simply the current value plus the net requirement. Show the cash balance results on a line chart.