Operations management/Excel simulation

Herts Car Rental Company, based in Florida, has one major competitor whom they compete with. Recently, Market Intelligence (MI) discovered their competitor adopted a masked pricing strategy to follow Herts’ last price within a 20% (more or less) range. Luckily MI provides a weekly demand forecast for the upcoming 4 weeks, based on historical data. Both Hertz and competitors’ pricing strategies determine both the market size Nt(pt), as well as the market share. The competitor would follow Herts’ market share (HMS) starting with 1-[HMS] (described in the parameters). Herts must decide a weekly price (P) and monthly fleet size (Q) measured with total profit (V), the sum of profits of four weeks, as well as the monthly fill rate (f), using the defined ratio (in parameters) between monthly sales and monthly demand. Through the use of the data and questions provided, as well as outsourced research, we will determine the best decision for Herts to hopefully beat out their competitors.
Parameters
Q=1500     M= $13 per sale     I= $298 per car     K= $344978
C0=0.0875       c1=-0.0220       c2=0.0170    and      b0=105644.5    b1= -377.5    b2= -676.25
Underlying Assumptions
According to the case study, the demand follows a binomial distribution Dt(pt) ∼(Nt, πt) where both the market size, the market share (trials), and the market share (the number of success) are determined by the price strategies. Our competitor price is in plus or minus 20% than our rental price. To compute a simulation of 1000 months, we will follow a normal distribution according to our market size and market share Mu and sigma. It is given that our fleet size is 1500 and our price will be determined every week to give us the optimal profit.  
Objective
Our objective is to maximize the weekly price P and our monthly fleet size Q that together will maximize our net monthly profit for the Tampa branch, taking into account the behavior of both customers and competition. We will measure these performances by two criteria, the monthly total profit (V) and monthly fill rate (f) defined by the ratio between the monthly sales and the monthly demand. Following are the questions we intend to utilize to ensure that we optimize our total profit.
a)  Fleet size Q = 1500 and the price P = $40 (p1=p2=p3=p4). Simulate the operation of the Tampa branch for n = 10^3 months (sample-path with 4 observations). Compute the expected fill rate E[f ], total expected monthly profit VQ(p 1 ), and its 95% confidence interval. To simulate 1000 months (sample size N) we will use the following metrics: where the fleet size Q= 1500 and the p=$40, maintenance cost M=$13 per sale, monthly inventory cost I=$298 per car, and fixed costs K=$344978, we will be able to estimate the expected fill rate and the expected monthly profit. Then, we will be able to measure the 95% probability of these expectations.
b)  Assume Q = 1500. Repeat question 1 for p 1 ∈ { 45, 50, . . . , 70 }. Graph VQ(p 1 ) against p 1 and find the optimal price p 1∗ that maximizes the total profit V ∗ Q = maxp 1 VQ(p 1 ). Using the same metrics above but using multiple rental prices to find out the optimal price that optimizes total profit, then use these finding to plot the total profit in relation to the price P1*.
c)  Now change Q = 2000, repeat questions 1 and 2. Find the optimal price p1∗ and total profit V ∗ Q for fleet size Q = 2000. Our new Q = 2000 while other metrics are still the same. Using the new Q and simulating a new sample size we will be able to generate a new expected fill rate, optimal price, and a new expected monthly profit.
d)   Repeat questions 1 and 2, for Q = { 2500, 3000, . . . , 4000 }. For each Q, find the optimal associated price p 1∗ and the optimal total profit V ∗ Q. Graph V ∗ Q against Q. Find the optimal fleet size Q∗. Our simulation in this part will be containing different fleet sizes. We will use the same parameters to apply the different costs on each Q and find the optimal price P for each Q. We can then use our findings to graph the total profit in relation to the fleet size.
e)   Now consider a dynamic pricing strategy. Suppose you may set the price p 1 t for each week differently, where p 1 t ∈ { 40, 45, . . . , 70 }. By the similar approach in questions 1-3, find the optimal price path (p 1∗ 1, p 1∗ 2, p 1∗ 3, p 1∗ 4 ) for each fleet size Q ∈ { 1500, 2000, . . . , 4000 } and its associated optimal profit V ∗ Q. Graph V ∗ Q against Q. Find the optimal Q∗. Compare the total profit under the dynamic pricing strategy with that under the static pricing strategy. Which one performs better? By how much? Why? In this part, we will consider different prices and different fleet sizes. Do the simulation for 1000 months and calculate the optimal price and profit. Using these findings we will be able to plot our optimal profit in relation to the optimal fleet size. 
f)   Assume fleet size Q=2000 and static pricing strategy: p1=p2=p3=p4=40 , simulate the operation for n= 1000 months. what is the expected market size and the expected market share for the Tampa branch? Doing this simulation using the following metrics: the market size Nt(Pt) and the market share πt(pt) where b0 = 105644.5, b1 = –377.5, and b2 = –676.25 and  c0 = 0.0875, c1 = –0.0220, and c2 = 0.0170 we will be able to visualize our expected market size and our expected market share.   

Market share = πt(pt) = 1/1+exp(-c0-c1*p1-c2*p2)
Market size = Nt(pt) = b0 + b1p1+b2*p2