Use INDEX and MATCH functions to fill in a table that extracts the amounts shipped between...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Use INDEX and MATCH functions to fill in a table that extracts the amounts shipped between each pair of cities in the accompanying data, which shows the solution to an optimization model that finds the minimum cost and amounts shipped from two plants to four distribution centers. Your table should be set up as follows, and the formulas for the Amount Shipped column should reference the names in the From and To columns. Click the icon to view the data and model. H 1 From 2 Marietta Cleveland 3 Marietta Baltimore 4 Marietta Chicago 5 Marietta Phoenix 6 Minneapolis Cleveland 7 Minneapolis Baltimore 8 Minneapolis Chicago 9 Minneapolis Phoenix 1 To Let cell range B11 E12 be named "Model," let cell range A11 A12 be named "Plant," and let cell range B10:E10 be named "Center." Select the appropriate INDEX and MATCH functions to complete the spreadsheet below. Amount Shipped From J To Amount Shipped Marietta Marietta Marietta Marietta Cleveland Baltimore Chicago Phoenix Minneapolis Cleveland Minneapolis Baltimore Minneapolis Chicago Phoenix Minneapolis General Appliance Corporation 1 Data 2 3 4 5 6 7 8 Model 9 10 11 12 13 14 A 15 16 Total Plant Cleveland Marietta Minneapolis Demand Marietta Minneapolis Demand B Plant Cleveland Cost $13.24 $9.71 200 $27,509 C 0 200 200 Distribution Center Baltimore Chicago $14.66 $16.38 250 D Distribution Center Baltimore Chicago 250 0 250 $11.63 $8.08 500 E Phoenix 0 500 500 $17.84 $17.95 1000 F Capacity Phoenix Total Shipped 1050 900 800 200 1000 1050 900 Let cell range B11:E12 be named "Model," let cell range A11:A12 be named "Plant," and let cell range B10: E10 be named "Center." Select the appropriate INDEX and MATCH functions to complete the spreadsheet below. H I To 1 From 2 Marietta 3 Marietta Cleveland Baltimore 4 Marietta Chicago 5 Marietta Phoenix 6 Minneapolis Cleveland 7 Minneapolis Baltimore 8 Minneapolis Chicago 9 Minneapolis Phoenix =MATCH(Plant INDEX(H9, Center, 0), INDEX(19, Model,0)) =INDEX(Model, MATCH(H9, Plant,0), MATCH(19, Center, 0)) =MATCH(Model, INDEX(H9, Plant,0), INDEX(19,Center, 0)) =INDEX(Center, MATCH(H9, Plant,0), MATCH(19, Model,0)) Use INDEX and MATCH functions to fill in a table that extracts the amounts shipped between each pair of cities in the accompanying data, which shows the solution to an optimization model that finds the minimum cost and amounts shipped from two plants to four distribution centers. Your table should be set up as follows, and the formulas for the Amount Shipped column should reference the names in the From and To columns. Click the icon to view the data and model. H 1 From 2 Marietta Cleveland 3 Marietta Baltimore 4 Marietta Chicago 5 Marietta Phoenix 6 Minneapolis Cleveland 7 Minneapolis Baltimore 8 Minneapolis Chicago 9 Minneapolis Phoenix 1 To Let cell range B11 E12 be named "Model," let cell range A11 A12 be named "Plant," and let cell range B10:E10 be named "Center." Select the appropriate INDEX and MATCH functions to complete the spreadsheet below. Amount Shipped From J To Amount Shipped Marietta Marietta Marietta Marietta Cleveland Baltimore Chicago Phoenix Minneapolis Cleveland Minneapolis Baltimore Minneapolis Chicago Phoenix Minneapolis General Appliance Corporation 1 Data 2 3 4 5 6 7 8 Model 9 10 11 12 13 14 A 15 16 Total Plant Cleveland Marietta Minneapolis Demand Marietta Minneapolis Demand B Plant Cleveland Cost $13.24 $9.71 200 $27,509 C 0 200 200 Distribution Center Baltimore Chicago $14.66 $16.38 250 D Distribution Center Baltimore Chicago 250 0 250 $11.63 $8.08 500 E Phoenix 0 500 500 $17.84 $17.95 1000 F Capacity Phoenix Total Shipped 1050 900 800 200 1000 1050 900 Let cell range B11:E12 be named "Model," let cell range A11:A12 be named "Plant," and let cell range B10: E10 be named "Center." Select the appropriate INDEX and MATCH functions to complete the spreadsheet below. H I To 1 From 2 Marietta 3 Marietta Cleveland Baltimore 4 Marietta Chicago 5 Marietta Phoenix 6 Minneapolis Cleveland 7 Minneapolis Baltimore 8 Minneapolis Chicago 9 Minneapolis Phoenix =MATCH(Plant INDEX(H9, Center, 0), INDEX(19, Model,0)) =INDEX(Model, MATCH(H9, Plant,0), MATCH(19, Center, 0)) =MATCH(Model, INDEX(H9, Plant,0), INDEX(19,Center, 0)) =INDEX(Center, MATCH(H9, Plant,0), MATCH(19, Model,0))
Expert Answer:
Answer rating: 100% (QA)
Based on the named ranges Model for B11E12 Plant for A11A12 and Center for B10E10 the correct formul... View the full answer
Related Book For
Business Analytics Methods Models and Decisions
ISBN: 978-0321997821
2nd edition
Authors: James R. Evans
Posted Date:
Students also viewed these general management questions
-
Use INDEX and MATCH functions to fill in a table that extracts the amounts shipped between each pair of cities in the Excel file General Appliance Corporation. Your table should display as follows,...
-
Case Study: Quick Fix Dental Practice Technology requirements Application must be built using Visual Studio 2019 or Visual Studio 2017, professional or enterprise. The community edition is not...
-
Mention 4 characteristics of the enzyme systems that are responsible for the biotransformation of xenobiotics.
-
Perform computational experiments to verify that the average path length in a ring graph on \(V\) vertices is \(\sim 1 / 4 V\). Then, repeat these experiments, but add one random edge to the ring...
-
Go to www.pearsonhighered.com/ sullivanstats to obtain the data file SullivanStatsSurveyII using the file format of your choice for the version of the text you are using. One question asked in this...
-
An 11-lb force P is applied to a shift lever. The moment of P about B is clockwise and has a magnitude of 250 lb in. Determine the value of a. B P 8 in A 22 in.
-
Describe and calculate employer payroll taxes.
-
Clapton Company manufactures custom guitars in a wide variety of styles. The following incomplete ledger accounts refer to transactions that are summarized for May: In addition, the following...
-
Facts For the year ended December 31, YR01 Target Inc. had net income of $10,000. At this date Target had 1,000 common shares issued and outstanding (par value =$1 per share). The number of common...
-
A tennis ball is mounted on a spring that is suspended from a ceiling in John's dorm room. For entertainment, stress relief, boredom or just for a pure conversation piece, the tennis ball can be...
-
Year Existing Holst 1 2 3 5 6 Calculation of Operating Cash Inflows Net Profits before Taxes Taxes Profits before Depreciation and Taxes Depreciation Year Holst A 1 N st AD Calculation on Incremental...
-
Following is the current asset section from the Ralph Lauren Corporation balance sheet: At March 29, 30 ($ millions) Cash and cash equivalents Short-term investments. Accounts receivable, net of...
-
What is the equivalent resistance of the circuit in Figure P31.59? Use the values \(R_{1}=2.0 \Omega, R_{2}=1.5 \Omega\), \(R_{3}=2.0 \Omega, R_{4}=1.5 \Omega, R_{5}=2.0 \Omega\), and \(R_{6}=1.5...
-
Find an article that discusses ethics and the use of social media by paralegals.
-
Search for a California case that lists the essential elements of a cause of action for a particular contract case. If your library has Witkins Summary of the Law, a multivolume treatise that...
-
Locate a sample memorandum of points and authorities at www.courts.ca.gov. What information did the memorandum contain? What was the motion about?
-
What costs are included when calculating the cost of acquiring a new customer? Question 14 options: All future marketing costs for the customer Variable and fixed marketing costs associated with the...
-
Determine the optimal use of Applichem's plant capacity using the Solver in Excel.
-
The Excel file Cereal Data provides a variety of nutritional information about 67 cereals and their shelf location in a supermarket. Use regression analysis to find the best model that explains the...
-
A bank developed a model for predicting the aver-age checking and savings account balance as balance = - 17,732 + 367 * age + 1,300 * years education + 0.116 * household wealth. a. Explain how to...
-
Suppose that the probabilities of the three scenarios are estimated to be 0.15, 0.40, and 0.45, respectively. Find the best expected value decision. The DoorCo Corporation is a leading manufacturer...
-
Refer to the data and information given in Problem 18-79 for Keefe Corporation. Assume that the tax rate for 2020 through 2022 is known to be \(25 \%\), but that a new law is passed in 2020 that will...
-
Whirlpools Corporation provided the following information: taxable income based on its 2020 tax return, \(\$ 47,600\); income tax rate, \(25 \%\). There were two temporary differences. - December 31,...
-
Terms frequently used in accounting for income tax along with descriptions of the terms are included in the following two lists. Terms - 1. Deferred tax asset _ 2. Taxable temporary difference - 3....
Study smarter with the SolutionInn App