Reference no: EM133274723

Case: You are the General Manager of Northern Virginia Motors that specializes in the sales of station wagons and light trucks. Your company has a strong reputation in the market but customer demand remains somewhat sensitive to price. Based on historical data analysis, your marketing team has come up with the demand curves for trucks and for wagons as follows:

Truck Demand (in unit) = 500 - 18 x (Truck Price in thousands)

Wagon Demand (in unit) = 400 - 11 x (Wagon Price in thousands)

Your purchasing prices from automakers are respectively $20,000 per truck and $25,000 per wagon. Before you sell your vehicles in the market, you need to prepare them. It takes 3 hours to prepare truck and 2 hours for a wagon. You currently have a staff with a total of 250 hours labor hours that you can deploy for the prep work. Also, you have a line of loans of $2,000,000 from the bank available for you to finance your vehicle purchases.

(1) In the below space, write out the mathematical formulation of the optimization problem that can help you decide the optimal price for each type of vehicle. Clearly state your decision variables, objective function and all necessary constraints if any.

(2) Implement the above optimization model in Excel. Label your workbook/worksheet that contains your implementation of the model as "Problem 3".

(3) What are the optimal prices that maximize profit?

(4) Under the optimal pricing, how many vehicles will you be able to sell? (Note: fraction is acceptable)

(5) If your labor cost is $30 per hour, should you expand your staff to 260 hours?

(6) If the bank loan has an interest rate of 10%, should you borrow $200,000 more to finance your vehicle purchase to sell more and make more money?