I NEED IT ON AN EXCEL FILE AND SOLVE WITH MONTE CARLO SIMULATION

Value of a Customer. As the manager of credit card services at Bank of Hanover (BOH), youre aware that the average profitability of a credit card customer grows with the number of years they have used the credit card. Two probabilistic factors affect actual profitability. The mean profitability function is given in the table below, which has been gathered from data on BOH customers. The actual profit in a given year follows a normal distribution, with a standard deviation equal to 25 percent of the mean profit. In addition, there is a probability less than one that a customer will continue to use the card during year t. This probability is sometimes called the retention rate. For instance, an 80 percent retention rate means that, during any year, there is a 20 percent chance the customer will cancel their credit card. Assume that if a customer cancels during year t, then the cancellation occurs at the end of the year, and BOH still gets profits from year t. The current retention rate has been estimated at 80 percent.

BOH uses a discount rate of 10 percent for calculating net present values. Year Mean Profit 1 40 2 66 3 72 4 79 5 87 6 92 7 96 8 99 9 103 10 106 Year Mean Profit 11 111 12 116 13 120 14 124 15 130 16 137 17 142 18 148 19 155 20 161 a. When the retention rate is 80 percent, what is the average NPV from a customer? b. When the retention rate is 80 percent, what is the probability that the NPV for a given customer will exceed $100? c. Determine the average NPV from a customer when the retention rate is 85 percent, 90 percent and 95 percent. Sketch a graph that describes how the average NPV varies with the retention rate, for retention rates above 75 percent; then, interpret the sketch.