A full statistical methodology is used to determine the bid price of cars in an upcoming auction to help a dealership maximize their profits and stay within their budget. Data was first cleaned and prepared for regression analysis, then the car auction was simulated to determine the bid percentage which maximizes the chances of winning the auction for a handful of cars, and lastly optimization was used to determine which cars to bid on according to the budget and available floor space of the dealership.
The pdf file goes into detail about the problem, our solution and methodology as well as strengths and weaknesses of the model. There are two excel files, one for the regression analysis and the second one for the simulation and optimization models.
Here is a quick summary of the project:
Beemer Barry runs a used BMW dealership and has recently had trouble turning a profit. In the used car business, predicting what customers value is essential in determining which cars to keep on the lot and how to price them. Therefore, Barry is hoping to use past auction and sales data to inform his future decisions for his business. Moreover, Barry is going to attend the Beaumont Auction in two days. There are five vehicle candidates with detailed specifications to be revealed in the auction and Barry strives to set a bidding strategy to maximize his expected profits. With a budget of $50,000 and a maximum capacity of four cars, Barry wants to know what the optimal bid percentage level for him would be when considering the probability of winning the auction items.
After conducting regression, simulation, and optimization analyses, Barry can decide on what the projected resale value of each car would be, what the possible percentages of winning for each bid percentage, and which auction items he should bid on to maximize expected profits. From our analysis, it was determined that Barry should bid on all four of the 3 Series cars at a 90% bid level to maximize his profits.