Question: Using this information in excel how do I calculate the optimal supply chain for the given budget and constraints This is all the given information.

Using this information in excel how do I calculate the optimal supply chain for the given budget and constraints

This is all the given information. The objective is to design the optimised supply chain given the constraints.

chain for the given budget and constraints This is all the given Something like this. My problem is I'm unsure of how to list in the constraints for minimum order size for each type of transport.

Weight (T) Product Info Product LCD 42" LCD 32" Demand (Units) 0.022 920,000 530,000 0.0165 Budget () 3,000,000,000 Rail Air Express 64,400 115,920 103,040 64,400 4,250.40 5,796 5,796 4,250.40 Product - ODM LCD 42" ODM 1 LCD 42" ODM 2 LCD 42" ODM 3 LCD 42" ODM 4 LCD 42" ODM 5 LCD 42" ODM 6 LCD 42" ODM 7 LCD 32" ODM 1 LCD 32" ODM 1 Shipping Cost Per Metric Ton () Road Road LTL Road Network 70,840 6,182.40 5,216.40 4,830.0 127,512 7,084 5,796 5,667.20 113,344 7,084 5,796 5,677.20 70,840 6,182.40 5,280.80 5,216.40 9,660 9,016 8,694 148,120 112,700 7,084 5,538.40 70,840 6,182.40 5,216.40 4,830 127,512 7,084 5,796 5,667.20 Water 3,091.20 2,704.80 3,284.40 3,091.20 Distance to DC (Kms) TV Unit Production Cost () Regular Air 2508 1,983.40 1553 2,254.00 1380 2,582.00 2150 1,976.10 30 2,711.30 690 2,704.80 686 2,125.20 2508 1,818.00 1553 1,996.40 5,796 135,240 103,040 64,400 115,920 5,860.40 4,250.40 5,796 3,413.20 2,769.20 3,091.20 2,704.80 Constraints Minimum Order Number at Any ODM Maximum Oder Number at any ODM Minimum number of units to be shipped by air LCD 42" (LCD 32") Minimum number of units to be shipped by road LCD 42" (LCD 32") Minimum number of units to be shipped by rail LCD 42" (LCD 32") 200,000 600,000 46,000 92,000 138,000 53,000 79,500 79,500 Decision Varaibles Regular Air Objective Function Cost (%) 2,999,995,614.45 Air Express 39279 O open = 1 319998 0 Water open=1 Total 47436 48146 o O 0 127 599237 0 ODM 1 ODM 2 ODM 3 ODM 4 ODM 5 ODM 6 ODM 7 Total Production Cost () 2783888433 Supply From Plants Road Road LTL Road Network Rail 43258 47198 47317 47364 0 0 O 0 o 0 o O 127 127 127 127 0 0 O O 0 O O 0 O 1 100 100 100 100 O 0 127 599999 0 0 O 0 O 0 0 Total Shipping Cost () 216107181.8 0 1 3 920000 1 100 530000 100 0 0 529400 O 0 530000 ODM 1 32" ODM 2 32" Min 42" Min 32" Constraints Shipping weights ODM 1 ODM 2 Regular Air Air Express 864.138 0 0 ODM 3 ODM 4 ODM 5 2.794 Road Road LTL Road Network Rail Water total 951.676 1038.356 1040.974 1042.008 1043.592 1059.212 0 o o ol 0 0 0 0 2.794 2.794 2.794 2.794 2.794 13183.214 o 0 0 0 0 0 0 0 0 0 0 0.022 0.022 0.022 1.65 1.65 1.65 1.65 1.65 8735.1 0 0 ODM 6 ODM 7 ODM 1 32" 0 1.65 LODILI Sheet1 Sheet3 Weight (T) Product Info Product LCD 42" LCD 32" Demand (Units) 0.022 920,000 530,000 0.0165 Budget () 3,000,000,000 Rail Air Express 64,400 115,920 103,040 64,400 4,250.40 5,796 5,796 4,250.40 Product - ODM LCD 42" ODM 1 LCD 42" ODM 2 LCD 42" ODM 3 LCD 42" ODM 4 LCD 42" ODM 5 LCD 42" ODM 6 LCD 42" ODM 7 LCD 32" ODM 1 LCD 32" ODM 1 Shipping Cost Per Metric Ton () Road Road LTL Road Network 70,840 6,182.40 5,216.40 4,830.0 127,512 7,084 5,796 5,667.20 113,344 7,084 5,796 5,677.20 70,840 6,182.40 5,280.80 5,216.40 9,660 9,016 8,694 148,120 112,700 7,084 5,538.40 70,840 6,182.40 5,216.40 4,830 127,512 7,084 5,796 5,667.20 Water 3,091.20 2,704.80 3,284.40 3,091.20 Distance to DC (Kms) TV Unit Production Cost () Regular Air 2508 1,983.40 1553 2,254.00 1380 2,582.00 2150 1,976.10 30 2,711.30 690 2,704.80 686 2,125.20 2508 1,818.00 1553 1,996.40 5,796 135,240 103,040 64,400 115,920 5,860.40 4,250.40 5,796 3,413.20 2,769.20 3,091.20 2,704.80 Constraints Minimum Order Number at Any ODM Maximum Oder Number at any ODM Minimum number of units to be shipped by air LCD 42" (LCD 32") Minimum number of units to be shipped by road LCD 42" (LCD 32") Minimum number of units to be shipped by rail LCD 42" (LCD 32") 200,000 600,000 46,000 92,000 138,000 53,000 79,500 79,500 Decision Varaibles Regular Air Objective Function Cost (%) 2,999,995,614.45 Air Express 39279 O open = 1 319998 0 Water open=1 Total 47436 48146 o O 0 127 599237 0 ODM 1 ODM 2 ODM 3 ODM 4 ODM 5 ODM 6 ODM 7 Total Production Cost () 2783888433 Supply From Plants Road Road LTL Road Network Rail 43258 47198 47317 47364 0 0 O 0 o 0 o O 127 127 127 127 0 0 O O 0 O O 0 O 1 100 100 100 100 O 0 127 599999 0 0 O 0 O 0 0 Total Shipping Cost () 216107181.8 0 1 3 920000 1 100 530000 100 0 0 529400 O 0 530000 ODM 1 32" ODM 2 32" Min 42" Min 32" Constraints Shipping weights ODM 1 ODM 2 Regular Air Air Express 864.138 0 0 ODM 3 ODM 4 ODM 5 2.794 Road Road LTL Road Network Rail Water total 951.676 1038.356 1040.974 1042.008 1043.592 1059.212 0 o o ol 0 0 0 0 2.794 2.794 2.794 2.794 2.794 13183.214 o 0 0 0 0 0 0 0 0 0 0 0.022 0.022 0.022 1.65 1.65 1.65 1.65 1.65 8735.1 0 0 ODM 6 ODM 7 ODM 1 32" 0 1.65 LODILI Sheet1 Sheet3

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

ACADEMiCiNTEGRITY: This IS a group assignment. You are on your honor. You cannot discuss any aspect of this case or anything related to it with any other teams. You cannot use any other outside...

Pecision 1 : Marketing - Advertising Placement PUT INTO EXCEL SOLVER!!!!!!!!!!!!!!!!!! This can be solved using Linear Programming. Here's how to approach it: Define Decision Variables: , Let W =...

Journal of Information Systems Education, Vol. 22(4) Teaching Case The Challenge of Implementing an ERP System in a Small and Medium Enterprise - A Teaching Case of ERP Project Management Hongjiang...

BIZ3189 Management Information Systems Take-home Exam 1 The tasks in this exam aim at developing a basic level of understanding on Excel and how you can use it to develop Decision Support Systems....

SHOW CALCULATIONS IN EXCEL!!!!!! Student Name You work as a supply chain manager for Primo Caf Inc. Primo Caf is a small-sized manufacturer of stylish coffee makers. The company has three distinct...

Background - Federal Express FedEx Corporation is a $20 billion market leader in transportation, information, and logistics solutions, providing strategic direction to the five main operating...

1. There are several categories of needs; identify each and provide an example (summarize each category and give examples for each category) CATEGORIES OF NEEDS Organizational needs can be classified...

Hard Rock Caf cas study THERE ISN'T MUCH OF A WAIT at the Orlando Hard Rock Caf, even though it's right outside of the evercrowded Universal Studios theme park. The music is loud as you await your...

refer to attachment and answer the following...refer to page 35...can be like a page and a half Strategy : Identify accounting choices which allow managers to strategically communicate economic...

Melton Corporation is preparing the comparative financial statements for the annual report to its shareholders for the fiscal years ended May 31, 2011, and May 31, 2012. The income from operations...

A nominal 1,000 MWe PWR plant operated for 1 year at a capacity factor of 0.75. The plant operates at an overall efficiency of 33%. The fuel mass of uranium is 26,977 kg. (a) What is the specific...

13.33 A price promotion study. How does the frequency that a supermarket product is promoted at a discount affect the price that customers expect to pay for the product? Does the percent reduction...

Skills necessary for success in marketing include Multiple select question. analytical thinking advertising industry experience infallibility ability to work with others

4.103 Bright Star Financial Advisers receives a mean of 19.5 applications per week for a personal financial review. Each review requires one day of an analysts time to prepare a review. Assume that...

4.99 Your computer is in serious need of repair. You have estimated that the breakdowns occur on average 3.5 times per week. If you are right and the breakdown variable is a Poisson distribution,...

4.101 Consider a country that imports steel and exports automobiles. The value per unit of cars exported is measured in units of thousands of dollars per car by the random variable X. The value per...