Question 2 Jane needs to get the juices from three different suppliers, namely (Fruit Smart, J&N and Lemon Bar) and they are located in different geographical locations. Fruit Smart will supply apple juice and the distance from Fruit Smart to Janes shop is 1600 km and the fuel cost is $0.30 per km. Lemon Bar specialises in orange juice and it is 1400 km away from Janes shop and the fuel cost is $0.40 per km and finally, J&N sells melon juice and it is 3000 km away from Janes shop and the fuel cost is $0.25 per km. Assume the daily demand for apple, orange and melon as 400 boxes, 300 boxes and 250 boxes respectively and there are 22 days in a month.

The juices are transported by road from the supplier to Janes shop and each containers trip cost is $1500. On top of it, the transportation charges vary according to the travelling distance. The juices are packed in boxes and 1 box of juice takes up 0.01 m3 of space. Each twenty foot container has a capacity of 20 m3 and the number of containers needs to be a whole number (i.e. no decimal place such as 0.5 containers is allowed). The cost for transport is $0.50 per km and the car can only travel 400 km per day. (a) Construct a lookup table and design a spreadsheet model which allows Jane to enter the type of Juice (Apple, Orange or Melon) and the total quantity required as input, and the model should look up a table and get the respective supplier name, the distance to travel, the number of boxes required, the number of days required for shipping and the total transportation cost. (b) What is the total transportation cost per month based on the daily demand, which is inclusive of all three types of juice? (c) If Jane wants to achieve the goal of only using $20000 as the transportation cost per month, she has to see how much she should negotiate with the supplier to change the cost of per container trip from $1500 to a lower value. Suggest the maximum amount Jane should pay for per container trip to achieve the goal and justify how you get the solution using the model that you have built above.