Hello I am having trouble with AG Excel Linear Programming Problem using Solver Solution. I have uploaded an image of the question on "A Transportation Problem: Tropicsun" along with notes on Bob and Dora's investment and the Blue Ridge Hot Tubs that can help explain the problem. I cannot seem to find the steps to answer the question. The formula is also in cell G2 as well to get the =sumproduct.

Real question>

1. A Transportation Problem: Tropicsun Processing Groves Distances (in miles) Plants Supply Capacity Mt. Dora 21 Ocala 27 5,000 200,000 50 40 35 Eustis 30 Orlando 400,000 600,000 2 5 22 55 Clermont 20 Leesburg 300,000 225,000 3 25 6 Minimize the total number of bushel-miles. Formulate and solve the model for this problem in excel. Let Xij = # of bushels shipped from node i to node j Specifically, the nine decision variables are: X14 = # of bushels shipped from Mt. Dora (node 1) to Ocala (node 4) X15 = # of bushels shipped from Mt. Dora (node 1) to Orlando (node 5) X16 = # of bushels shipped from Mt. Dora (node 1) to Leesburg (node 6) X24 = # of bushels shipped from Eustis (node 2) to Ocala (node 4) X25 = # of bushels shipped from Eustis (node 2) to Orlando (node 5) X26 =# of bushels shipped from Eustis (node 2) to Leesburg (node 6) X34 =# of bushels shipped from Clermont (node 3) to Ocala (node 4) X35 = # of bushels shipped from Clermont (node 3) to Orlando (node 5) X36 =# of bushels shipped from Clermont (node 3) to Leesburg (node 6)6'01? A/V 00M '5 WWII/14M] Bob and Dora Sweet wish to start investing $1,000 each month. The Sweets are looking at five investment plans and wish to maximize their expected return each month. Assume interest rates remain fixed and once their investment plan is selected they do not change their mind. The investment plans offered are: Investment Plan Annual Return Fidelity 9.1% return per year Optima 16.1% return per year CaseWay 7.3% return per year Safeway 5.6% return per year National 12.3% return per year CONSTRAINTS Since Optima and National are riskier, the Sweets want a limit of 30% per month of their total investments placed in these two investments. Since Safeway and Fidelity are low risk, they want at least 40% of their investment total placed in these investments. DEFINING THE DECISION VARIABLES X1 = Investment amount for Fidelity X2 = Investment amount for Optima X3 = Investment amount for Case Way X4 = Investment amount for Safeway X5 = Investment amount for NationalDEFINING THE OBJECTIVE FUNCTION Maximize Return MAX: 0.091X1 + 0.161X2 + 0.073X3 + 0.056X4 + 0.123X5DEFINING THE OBJECTIVE FUNCTION Maximize Return MAX: 0.091X1 + 0.161X2 + 0.073X3 + 0.056X4 + 0.123X5File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share Calibri v 11 % Conditional Formatting B IUAA Format as Table Cells Editing Analyze Paste Alignment Number MVA Cell Styles Data Clipboard Font Styles Analysis Jndo G2 vi X fx =SUMPRODUCT(B2:F2,$B$3:$F$3) A B G H K Fidelity Optima Caseway Safeway National Max Returns N - Returns 9.10% 16.10% 7.30% 5.60% 12.30% $95.80 Decision Variables $200.00 $200.00 $300.00 $200.00 $100.00 A W 5 Constraints Usage Relation Limit 6 Total Investments $1,000.00 = $1,000 Risky Investment 30% 30% 8 Low Investment 40% >= 40% 9 Non-negativity X1 $200.00 > = 0 OO OHH OH OOHOOH OH OOOH OF OOHOO OO - 10 Non-negativity X2 $200.00 = = 0 12 Non-negativity X4 $200.00 > = 0 13 Non-negativity X5 $100.00 > = - O 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Sheet1 +GENERAL FORM OF AN OPTIMIZATION PROBLEM MAX (or MIN): fo(X 1, X2, . .., Xn) Subject to: fi(X 1 , X2, ..., Xn)= bk fm(X 1, X2, ..., Xn)=bm Note: If all the functions in an optimization are linear, the problem is a Linear Programming (LP) problemAN EXAMPLE LP PROBLEM Blue Ridge Hot Tubs produces two types of hot tubs: Aqua-Spas & Hydro-Luxes. Aqua-Spa Hydro-Lux Pumps - Labor 9 hours 6 hours Tubing 12 feet 16 feet Unit Profit $350 $300 There are 200 pumps, 1566 hours of labor, and 2880 feet of tubing available.5 STEPS IN FORMULATING LP MODELS: 1. Understand the problem. 2. Identify the decision variables. X, =number of Aqua-Spas to produce X,=number of Hydro-Luxes to produce 3. State the objective function as a linear combination of the decision variables. MAX: 350X, + 300X,5 STEPS IN FORMULATING LP MODELS (CONTINUED) 4. State the constraints as linear combinations of the decision variables. 1X, + 1X2 = 0 X, >= 0\fSOLVING LP PROBLEMS: AN INTUITIVE APPROACH Idea: Each Aqua-Spa (X, ) generates the highest unit profit ($350), so let's make as many of them as possible! How many would that be? " Let X2 = 0 . 1 st constraint: 1X,