I need help with formulating the pictured problem in Excel.

Pictured below is what I have in Excel so far and Problem 3.7

I also need help with knowing what formula to input to get the desired output and constraints in Excel Solver for the problem.

* Problem 3.7 (Best-of-nine series): A \"best-of-nine\" playoff is a duel between two teams, where one team needs to secure five victories to clinch the series. Let's assume that Team A's likelithood of beating Team B in any given game remains constant, denoted as p, and that the games are statistically independent. In this scenario, we can approach the problem by creating a dynamic Assignment #3 T Young H. Chun ISDS 7103 A3-4 programming model with a recursive equation as we did for the \"best-of-seven playoff in the lecture notes. (4 points) (a) If p=0.55, what is the average number of games in the best-of-nine series? %O (b) If p=0.55, find the probability that Team A will win the best-of-nine series. L@ N Tn B W e S S S S ERERERBEEEESELREEREE B 5 D E F Problem 3.7 (Best-of-nine series) - e = o= 1. Recursive equation (a) Expected number of games Team A ~lo kM w e Team B K L M N o P Q R 1. You need to use the "backward recursion" method for this dynamic programming problem. 2. Formulate a DP model as follows: * State: The game score is (i, f) for Team A and Team B * Expected number of games at state (i, j) is f{i, f) * Optimality equation: fli ) =p * F(+1, )+ (1-p) * fl7, j+1) + 1 where the terminal condition is f{i, 3) = f(, j) =0 3. Add the terminal conditions, 0, in the last row D9:H9 and the last column 110:114. 4. The starting point in the backward iteration is the state (4, 4) in cell H19. 5. Type the optimality equation in cell H19. 6. Then, highlight all the cells D10:H14. 7. Use "Fill left" and "Fill down" to calculate all the probabilities! A B C D E F G H I J K L M N O P Q R S T U V Problem 3.7 (Best-of-nine series) p= 0.55 q= 0.45 1. Recursive equation 1. Formulate a DP model as follows: (b) Probability of winning the series for Team A * State: The game score is (i, j) for Team A and Team B * Probability that Team A will win the series: f(i, j) * Optimality equation: Team A f(i, j) = p * f (i+1, j) + (1-p) * f(i, j+1) OHNWAU where the terminal condition is f(i, 5) = 0 and f(5, j) = 1 2. Add the terminal conditions, 1 or 0, in the last row D9:H9 and the last column 110:114. i/j 0 1 2 3 4 5 16 TeamB 3. As you did in Part (a), type the optimality equation in cell H10. 4. Use "Fill left" and "Fill down" to calculate all the probabilities