Question

Scenario: A bicycle manufacturer produces a racing bicycle and a commuting bicycle. Long-term projections indicate a minimum expected demand of at least 100 racing and 80 commuting bicycles each day. However, they can produce no more than 200 racing and 170 commuting bicycles each day. To satisfy a shipping contract, they must produce a total of at least 200 bicycles each day. If each racing bicycle sold results in a $20 loss, but each commuting bicycle produces a $50 proft, how many of each type should they make daily to massimize net profits? 1. Make sure Solver is activated in Excel 2. Analyse in Excel using the Solver Add-in and 3. Upload your Excel spreadsheet with the solution Need help? Review the mini-lectures and your notes Come to office hours.

Answer 1

No of racing bicycles = 100

No of commuting bicycles = 170

WORKING

	Racing bicycle	Commuting Bicycle	Objective function
			Maximize
Units produced	100	170
Profit per unit	-20	50
Total profit	-2000	8500	6500
Constraints
Number of racing bicycles	100<=	100	<=200
Number of commuting bicycles	80<=	170	<=170
Total		270	>=200

Book Solver Parameters X File Home Insert Page Layout Formulas Data Review View are Set Objective: $D$5 1 Show Queries Connections Å To: O Max O Min O Value Of: 0 Get External Data - From Table New Query [ Recent Sources Get & Transform Refresh All- Properties Edit Links A Sort By Changing Variable Cells: Connections $B$3:$C$3 4 D5 Add 1 Subject to the Constraints: $B$3 <= 200 $B$3 >= 100 $C$10 >= 200 $C$3 <= 170 $C$3 >= 80 Change Delete Reset All 100<= Load/Save A В. C D Racing bicycle Commuting Bicycle Objective function 2 Maximize 3 Units produced 100 170 4. Profit per unit -20 50 5 Total profit =B3*B4 =C3*C4 i=B5+C5 6 7 Constraints 8 Number of racing bicycles =B3 <=200 9 Number of commuting bicycles 80<= =C3 <=170 10 Total =B3+C3 >=200 11 12 13 14 15 16 17 18 19 20 21 Sheet1 ✓ Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close Point 100%