A motorcycle shop sells two models of motorcycles: Type A and Type B. The shop is limited by a quota of 100 of Type A and 150 of Type B. Dealer preparation requires 2 hours for a motorcycle of Type A and 3 hours for a motorcycle of Type B. Next quarter, the shop has 400 labor-hours available for this work and wants to know how many of each type should be ordered so that profit is maximized. The cost for Type A is $8000 per motorcycle and $12,000 for Type B. The sales of a motorcycle will depend on the selling price, such that:
PA = 0.009A2 – 8A+9000
PB = -7.5B+13200
Where
A = quarterly sales of Type A
PA = selling price of Type A
B = quarterly sales of Type B
PB = selling price of Type B
c) Formulate a mathematical model to maximize profit. Clearly define your decision variables, constraints and objective
d) Implement your model in Excel and utilize Solver to answer the following questions (include organized snapshots of your Excel answers in the report):
How many of each motorcycle type should the shop optimally order? · ·
What prices should the shop set for the two types?
What will be the optimal profit contribution?
Which constraints are binding?
How does the percentage of Type B motorcycles vary compared to the total as the number of labor hours available varies from 100 to 400 in steps of 50.