Question

Suppose that a particular computer company has 12 sales representatives based in New York City and 18 sales representatives based in San Francisco. To staff an upcoming trade show in Dallas and another trade show in Chicago, this company wants to send at least 15 sales representatives to Dallas and at least 10 sales representatives to Chicago. Also, at least 5 of the sales representatives in San Francisco must be sent to Dallas. According to Travelocity, for October 2017, the lowest round-trip airfares (including taxes and fees) for a one-week stay, with tickets purchased two weeks in advance, are as follows: San Francisco to Dallas: $440; San Francisco to Chicago: $280; New York (LaGuardia) to Dallas: $380; New York (LaGuardia) to Chicago: $280. Using Excel Solver, determine how many sales representatives this company should send from New York City to each trade show and how many should be sent from San Francisco to each trade show in order to satisfy the staffing requirements while minimizing the total airfare required to send the representatives. Be sure to select keep the Solver solution and generate answer report. Solver Your task is to use Excel Solver and solve the optimization problem. (a) Create a workbook named Flight.xlsx. Insert a Documentation worksheet and add appropriate title for the workbook in cell Al. In cell B3 enter your name. In cell B4 enter your student number. In cell B5 enter the completion date of your assignment and in cell B6 enter a sentence to describe the purpose of the workbook. (b) Using a new worksheet, formulate and enter the flight problem as shown in class. You can also find several examples on this type of problems on Blackboard under Lecture 4 folder. (c) Enter Solver Parameters (Set Objective, To, By Changing Variable Cells and Subject to the Constraints) (d) Solve the problem. Be sure to select Keep the Solver Solution and generate Answer report. Note: If you get an internal memory error message, remove Solver as an add-in, close the workbook, open the workbook, add Solver in again, and finish using Solver. (e) Using the same worksheet, answer the following questions. i. How many sales representative this company should send from New York and San Francisco to each trade show? ii. What is the minimum total airfare required to send these representatives? (rounded your answer to the nearest cent) (t) Format your workbook as desired (Headings, Font, Fill color, Number format...)

Please use excel to solve and please show how you did it thanks.

Answer 1

Solution

i.

No of representatives	Dallas	Chicago	Total
New York	10	-	10
San Francisco	5	10	15
Total	15	10	25

ii. $8,800

Explanation:

I hereby attach images of the excel worksheet in which the solution has been done. I could not attach the workbook here in the solution tab so please refer the below images where the format and formulas have been displayed.

File Home Insert Page Layout Formulas Data Help Power Pivot ASAP Utilities Search Share Comments Calibri === On Review View Developer a Wrap Text General Merge & Center - $ -% ¢ , 12 E A 2 1 I Insert Delete Format AutoSum - 11-AA - - A- 47 0 5 a Paste BIU - , 40.00 68 2 , Conditional Format as Cell Formatting Table Styles Styles Fill Clear Ideas Sort & Filter Find & Select Clipboard Font Alignment Number Cells Editing Ideas N2 : xv for A B C D E F G H I J K L M N O P QA No of representatives New York San Francisco Total Dallas Chicago Total | 10| - | 10 15 15 10 25 10 Cost per representative Dallas New York $ 380 San Francisco $ 440 Chicago $ 280 $ 280 12 15 16 17 18 Total cost New York San Francisco Total Dallas $ 3,800 $ 2,200 $6,000 Chicago Total $ - $ 3,800 $ 2,800 $5,000 $2,800 $ 8,800 Sheet1 + Ready @ -- +_120%

File Home Insert Page Layout Formulas Data Review View Search Share Comments ASAP Utilities - M 2 1 . Developer Help Power Pivot TA Y Clear = Reapply Sort Filter Text to Advanced Columns Sort & Filter Data Queries & Connections Refresh B Properties All Edit Links Queries & Connections Data Analysis 2 Colu Solver Parameters Get Stocks Geography Data- Get & Transform Data Data Types Set Objective: SE$17 E17 : * fic To: O Max Min O Value of: B C D E F G By Changing Variable Cells: SC$3:$D$4 Dallas Chicago No of representatives New York San Francisco Total Total =SUM(C3:03) =SUM(C4:04) =SUM(C5:D5) Add =SUM(C3:04) =SUM(D3:04) Subject to the Constraints: SCS3:SDS4 = integer SCS4 >= 5 SCS5 >= 15 SD$5 >= 10 SES3 <= 12 SE$4 <= 18 Change Delete Chicago Cost per representative Dallas New York 380 San Francisco 440 280 280 Reset All Load/Save ✓ Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options Total cost New York San Francisco Total Dallas Chicago Total =C9*C3 =D9*D3 =SUM(C15:D15) =C10*C4 =D10*D4 =SUM(C16:016) =SUM(C15:C16) |-SUM(D15:016) =SUM(C17:017) 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 Sheet1 Point 2 1 - - + 100%

Notes:

The objective cell $E$17 is the sum of costs of sending representatives to both Dallas and Chicago subject to the given constraints.. This has been set to Min value to arrive at least cost.

The constraints:

$C$4 >= 5 At least 5 of the sales representatives to be sent from San Francisco to Dallas

$C$5 >= 15 At least 15 of the sales representatives to be to Dallas

$D$5 >= 10 At least 10 of the sales representatives to be sent to Chicago

$E$3 <= 12 Maximum 12 sales representatives from New York

$E$4 <= 18 Maximum 18 sales representatives from San Francisco