Question

Zippy Motorcycle manufacturing produces two popular pocket bikes (miniature motorcycles with 49 cc engines): the Razor and the Zoomer. In the coming week, the manufacturer wants to produce up to 700 bikes and wants to ensure the number of Razors produced does not exceed the number of Zoomers by more than 300. Each Razor produced and sold results in a profit of $70 while each Zoomer results in a profit of $40. The bikes are identical mechanically and only differ in the appearance of the polymer-based trim around the fuel tank and seat. Each Razor’s trim requires 2 pounds of polymer and 3 hours of production time. On the other hand, each Zoomer requires 1 pound of polymer and 4 hours of production time. Assume that 900 pounds of polymer and 2,400 labor hours are available for production of these items in the coming week. Find the best production strategy for Zippy.  Can you solve this problem using linear programming and excel solver and formulas

Answer 1

Let the number of Razor bikes be R and Zoomer bikes be Z

Total Profit = 70R + 40Z

We have to maximize this profit

Subject to Constraints:

R <= Z + 300.............Constraint for max. Razor bikes not exceeding Zoomer by 300

2R + 1Z <= 900.........Constraint for amount of polymer available

3R + 4Z <= 2400.......Constraint for no. of labor hour available

R + Z <= 700.............Max. no. of bikes to be produced

R, Z >= 0...................Non-negative constraint as no. of bikes produced cannot be negative

We solve the above LPP in Excel using Excel Solver as shown below:

The above solution in the form of formulas along with Excel Solver extract is shown below for better understanding and reference:

As seen from above,

No. of Razors = 240

No. of Zoomers = 420

Total Profit = $33600

-----------------------------------------------------------------------------------------------------------------------

In case of any doubt, please ask through the comment section before Upvote/downvote.

If you liked the answer, please give an upvote. This will be quite encouraging for me, thank-you!!!