Question

In: Operations Management

Please submit your solutions as ONE single workbook with the sheets clearly labelled to indicate the...

Please submit your solutions as ONE single workbook with the sheets clearly labelled to indicate the corresponding problem numbers as indicated on this test.

• For each problem, you are required to use a textbox to show the definition of your decision variables. You may include this textbox only with part a) of the problem if the following parts do not require additional variables to reformulate.

• If a problem requires sensitivity analysis, re-order the worksheets so that they appear in the order of the questions.

• Answers to the questions should be clearly described in a separate textbox of the corresponding labelled worksheets. Since you will only be credited with problems that have correct answers, please check carefully your report textbox.

• For each problem formulation, please highlight the decision variables in Yellow, the constriants (Left Hand Side) in Blue, and the Objective function in Red.

3) ​The A&B Supermarkets have 3 warehouses A, B, and C, that ships supplies to their 3 stores. Their costs of shipping per unit are given below:

1

2

3

A

14

12

9

B

11

8

12

C

13

10

12

​The capacities of A, B, and C are 240, 360, and 200 units, respectively, while the demands of stores 1, 2, and 3 are 400, 250, and 150, respectively. [Notice that the supplies = demands!]

a) Formulate the transportation problem to minimize total cost for shipping. Please provide the actual shipping schedule derived from Solver.

b) If the shipping routes can be changed so that the supplies can be transshipped from warehouse to warehouse, market to market, and even market to warehouse, would you recommend a new set of shipping routes given the following per unit cost shipping data:

A

B

C

1

2

3

A

0

1

5

14

12

9

B

1

0

2

11

8

12

C

5

2

0

13

10

12

1

14

11

13

0

4

3

2

12

8

10

4

0

2

3

9

12

12

3

2

0

​​

c)​What would be the new shipping routes and quantities? How are these different from those of a)?

Solutions

Expert Solution

a)

Create spreadsheet model as follows:

EXCEL FORMULAS:

Optimal shipping plan
From / To 1 2 3 Sent
A 90 0 150 =SUM(B10:D10)
B 110 250 0 =SUM(B11:D11)
C 200 0 0 =SUM(B12:D12)
Received =SUM(B10:B12) =SUM(C10:C12) =SUM(D10:D12)
Total Cost =SUMPRODUCT(B10:D12,B3:D5)

[there are no formulas in the cost of Shipping table. In that table, all the numbers are manually entered]

Enter Solver Parameters as follows:

Click Solve to generate the solution:

b)

Create spreadsheet model as follows:

EXCEL FORMULAS:

Optimal shipping plan
From / To A B C 1 2 3 Row Total Net Supply
A =SUM(B13:G13) =H13-B19
B =SUM(B14:G14) =H14-C19
C =SUM(B15:G15) =H15-D19
1 =SUM(B16:G16)
2 =SUM(B17:G17)
3 =SUM(B18:G18)
Column Total =SUM(B13:B18) =SUM(C13:C18) =SUM(D13:D18) =SUM(E13:E18) =SUM(F13:F18) =SUM(G13:G18)
Net Received =E19-H16 =F19-H17 =G19-H18
Total Cost =SUMPRODUCT(B13:G18,B3:G8)

[there are no formulas in the cost of Shipping table. In that table, all the numbers are manually entered]

Enter Solver Parameters as follows:

Click Solve to generate the solution:

The new shipping routes and quantities are shown in optimal shipping plan

This new shipping plan is different from (a) in the sense that in this plan, 90 units are shipped from A to B . This increased the total capacity of B from 360 to 450

This new plan resulted in a cost reduction of = 8420 - 8240

= 180


Related Solutions

Please submit your solutions as ONE single workbook with the sheets clearly labelled to indicate the...
Please submit your solutions as ONE single workbook with the sheets clearly labelled to indicate the corresponding problem numbers as indicated on this test. • For each problem, you are required to use a textbox to show the definition of your decision variables. You may include this textbox only with part a) of the problem if the following parts do not require additional variables to reformulate. • If a problem requires sensitivity analysis, re-order the worksheets so that they appear...
QUESTION ONE (a) With the aid of a clearly labelled diagram, critically analyse the implications of...
QUESTION ONE (a) With the aid of a clearly labelled diagram, critically analyse the implications of a small nation, Mazyopa Republic, imposing a 100 percent ad valorem tariff on the commodity of its comparative disadvantage. [30 Marks] (b) Briefly describe the Leontief Paradox. What explanations were drawn from the paradox [20 Marks] [TOTAL: 50 MARKS]
Please provide solutions to the following problems. Please use Excel to solve the problems and submit...
Please provide solutions to the following problems. Please use Excel to solve the problems and submit the Excel spreadsheet. A fair coin is tossed 15 times, calculate the probability of getting 0 heads or 15 heads A biased coin with probability of head being .6 is tossed 12 times. What is the probability that number of head would more than 4 but less than or equal to 10. You have a biased dice (with six faces numbered 1,2,3,4,5 and 6)...
Please provide solutions to the following problems. Please use Excel to solve the problems and submit...
Please provide solutions to the following problems. Please use Excel to solve the problems and submit the Excel spreadsheet. You started a new restaurant. Based on invoices for the first 30 days, you estimated your average grocery bill to be $20,000 with a standard deviation of $2000. You want to start another restaurant in a similar neighborhood and you are planning to prepare a brochure for investors and to work out a deal with a whole sale food distributor. Prepare...
Please show all your work in Excel, please send me the spreadsheet / workbook. What is...
Please show all your work in Excel, please send me the spreadsheet / workbook. What is the market value of the following bond? Coupon 8% Maturity date 2038 Interest paid semiannually Par Value $1000 Market interest rate 10% What is the market value of the following bond? Coupon 9% Maturity date 2028 Interest paid semiannually Par Value $1000 Market interest rate 8% What is the yield to maturity of the following bond? Coupon 9% Maturity date   2027 Interest paid semiannually...
Your essay must be written concisely and not exceed 700 words. Indicate your choice clearly Discuss...
Your essay must be written concisely and not exceed 700 words. Indicate your choice clearly Discuss the possible impact of budget deficits on interest rates and private investment in small island economies in the Pacific.
*Please clearly indicate which answer is for what part.* Tim suffered greatly this year. In January...
*Please clearly indicate which answer is for what part.* Tim suffered greatly this year. In January a freak storm damaged his sailboat and in July Tim's motorcycle was stolen from his vacation home. Tim originally paid $30,600 for the boat, but he was able to repair the damage for $7,050. Tim paid $18,050 for the motorcycle, but it was worth $19,600 before it was stolen. Insurance reimbursed $1,540 for the boat repairs and the cycle was uninsured. a. Calculate Tim's...
Use Python to Complete the following on a single text file and submit your code and...
Use Python to Complete the following on a single text file and submit your code and your output as separate documents. For each problem create the necessary list objects and write code to perform the following examples: Sum all the items in a list. Multiply all the items in a list. Get the largest number from a list. Get the smallest number from a list. Remove duplicates from a list. Check a list is empty or not. Clone or copy...
Please submit as a single Excel file. To receive credit, appropriate detail needs to be provided....
Please submit as a single Excel file. To receive credit, appropriate detail needs to be provided. In addition, a professional presentation of findings is important. #1       Determine the total cost for this plan given the following forecast: Month 1 2       3       4       5       6 Forecast 380   400   420   440   460   480 Use steady regular output of 400 units per month, use overtime as needed for up to 40 units per month, and use subcontracting to make up any needed...
Complete the “July Journal Entries” tab in your workbook using the Step One data in the...
Complete the “July Journal Entries” tab in your workbook using the Step One data in the appendix. The following events occur in July, 2018: July 1: You take $10,000 from your personal savings account and buy common stock in Peyton Approved. July 1: Purchase $6,500 in baking supplies from vendor, on account. July 3: Your parents lend the company $10,000 cash in exchange for a two-year, 6% note payable. Interest and the principal are repayable at maturity. July 7: Enter...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT