The largest and most powerful automobile offered by the Fjord Motor Company in North America is the Coronet Elizabeth. Like Fjord's other models, the Coronet Elizabeth is sold through two different channels. Most cars are sold to the public through Fjord's nationwide network of dealers. However, the Coronet Elizabeth is also a popular model for fleet purchases, particularly by police departments and corporate fleets and Fjord maintains a separate direct channel for fleet sales.

Fjord defines fleet sales as those that include 10 or more vehicles. Most fleet sales are negotiated by Fjord's regional sales network. (A handful of very large deals involving hundreds of cars and mixed fleets are handled by a national sales team.) For the purposes of this problem, you can assume that each fleet purchase is strictly for Coronet Elizabeth's and that each bid proceeds by the buyer specifying the number of vehicles that he wishes to purchase, followed by Fjord submitting a sealed a bid for the order specifying Fjord's price for satisfying the bid. Bids are competitive but typically Fjord does not know who they are bidding against nor the size of the bids submitted by the other competitors. After evaluating all bids, a buyer will tell Fjord whether or not Fjord has won the deal1.

All fleet sales are for a "plain vanilla" (e.g. standard) version of the Coronet Elizabeth. This version costs Fjord $15,000 per vehicle to make and the Manufacturer's Suggested Retail Price (MSRP) is $25,000. However, all fleet bids are somewhere between the cost (at which Fjord makes no margin) and the MSRP (at which the buyer receives no discount). Fjord makes about 4,000 fleet bids per quarter and wins almost 70% of them. According to Fjord management about half of the bids are to police departments and half are to corporate fleet purchasing departments.

Fjord management has recently become concerned that its fleet sales staff have been "leaving money on the table" through inconsistent and capricious bidding and has hired you as a consultant to help Fjord institute a more rational pricing process for fleet sales. As part of this effort, they have given you the data on the spreadsheet. This data is the information available for all 4,000 bids made by the regional sales group during the last quarter of 2007. Unfortunately, due to difficulties reconciling the pricing database with the CRM database, this data does not include any information that would allow you to differentiate between bids to police departments and bids to corporations.

1. On the basis of the data in the spreadsheet, find a two-parameter logistic model that best estimates the probability of winning each bid as a function of the discount from list price assuming that a single price per unit will be offered for each bid. The model you are fitting is (p) = 1/(1+exp(a +bp)), where is the probability of winning the bid and a and b are the parameters to be estimated. p is the price that Fjord bid on a deal expressed as a fraction of the MSRP of $25,000per unit - that is if Fjord bid $20,000 per unit, p would be equal to 20,000/25,000 or .8. What are the values of a and b that maximize the sum of log likelihoods? What is the optimum price that Fjord should offer assuming that it is going to bid at a single price for each bid? What would the expected total contribution have been for the 4,000 bids? How does this compare to the contribution that Fjord actually received?

2. Miraculously, Fjord discovers that bids 1 through 2,000 were to police departments and bids 2,001 through 4,000 were to corporate buyers. Taking advantage of this discovery, estimate separate two-parameter logistic response functions for police departments and corporate buyers. The model you are fitting is the same as above, but there will be a different value of a and a different value of b for the police and for corporate buyers. What are the corresponding values of a and b? What are the optimum prices that Fjord should offer to the police versus corporate buyers? What would the expected contribution have been if Fjord had used the prices in the 4,000 bids in the database? What is the change from the contribution actually received and the best that Fjord could do when it could not differentiate between the police and corporate buyers? (Hint: Solver will also work better if you use the values of a and b that you derived as answers to Part 1 as the starting values for solving this part.)

3. As you continue your analysis, a senior sales manager tells you that he believes that the size of the order is an important factor in determining price-sensitivity. Specifically, he believes that customers who are making larger orders are more sensitive to the price-per-unit. Add a single parameter c to your analysis to incorporate this potential effect. Your new model is = 1/(1+exp(a+bp+cs)) where s is the number of vehicles in an order and c is the corresponding parameter which must be estimated. As before, calculate different values of a and b for police and corporate sales, but calculate only a single value of c for both police and corporate sales. What is the improvement in total log likelihood? How does this compare with the improvement from differentiating police and corporate sales? What are the optimal prices that Fjord should charge for orders of 20 and 40 cars to police departments and corporate purchasers? Extra credit: Calculate optimal prices for all order sizes from 10 through 60 vehicles for both police and corporate sales and use these prices to determine the total contribution margin that Fjord would have received if it had used these prices in the 4,000 historic bids.

4. You discover rather late in the process that the Coronet Elizabeth model sold to police departments is somewhat different than that sold to corporate fleets. Specifically, the police model includes a slightly souped-up engine, bullet-proof glass in all windows, a metal grill separating the front and back seats, and plastic doughnut holders for both front seats. As a result, the police version of the Coronet Elizabeth costs $16,000 to manufacture versus $15,000 for the corporate version. How would this change the optimal prices for police purchase of 20 and 40 vehicles? (Note, you do not need to run any regressions to answer this question.)

5. What ways can you think of that might improve this model? Are there other customer or product dimensions that you would recommend that Fjord collect in the future that might provide opportunities for further segmentation? Can you think of any business rules or other considerations that might restrict Fjord from pricing the way it might wish to price in order to maximize expected contribution?

Looking for help with part 5, including prior components for context

Here is the case: https://www.coursehero.com/file/170202454/Fjord-Motorpdf/

Here is the data: https://www.coursehero.com/file/180588115/FjordMotorxls/