BIDA 650
see attachment
DR. MAJED AL-GHANDOUR 1
BIDA 650 Business Analytics
Homework 2 (Individual)
Question 1 (25%)
More than 46,000 farms spread across 8.4 million acres of land in NC State, with average farm size ringing
in at 182 acres. A farmer in Hendersonville owns 50 acres of land. He is going to plant each acre with
Apples or Pumpkins. Each acre planted with Apples yields $400 profit; each with Pumpkins yields $200
profit. The labor and fertilizer used for each acre are given in the table below. Resources available include
150 workers and 200 tons of fertilizer. Using EXCEL.
Apples Pumpkins
Labor (Workers) 5 3
Fertilizer (Tons) 6 2
(A) Formulate a linear programming model that will enable the farmer to determine the number of acres
that should be planted Apples and/or Pumpkins in order to maximize his profit.
(B) Find an optimal solution to the model in (A) and determine the maximum profit.
(C) Implement the model in (A) in Excel Solver and obtain an answer report. Which constraints are
binding on the optimal solution?
(D) Obtain a sensitivity report for the model in (A). How much should the farmer be willing to pay for an
(E) Suppose the farmer hires 10 additional workers. Can you use the sensitivity analysis obtained for (D) to
determine his expected profit? Would his planting plan change? Explain your answer.
(F) Using SAS Code to run an optimization problem and print output decision variables, objective function
Z, and constraints solutions. You need to provide your Code on a separate file called it SAS_Problem1.sas
Question 2 (50%)
Assume that you have decided to enter the Chocolate Truffle business. You are considering producing
two types of Truffle: A and B, both of which consist solely of sugar, nuts, and chocolate. At present
you have 12,000 ounces of sugar, 3000 ounces of nuts, and 3000 ounces of chocolate in stock. The
mixture used to make Truffle A must contain at least 10% nuts 80% sugar, and 10% chocolate. The
mixture used to make Truffle B must contain at least 10% chocolate, 70% sugar, and at least 20% nuts.
Each ounce of Truffle A can be sold for $0.40 and each ounce of Truffle for B $0.50. Determine how
you can maximize your revenues from Truffle sales using linear programming approach. Provide a
linear programming formulation, notation and solution using ONLY EXCEL Solver.
DR. MAJED AL-GHANDOUR 2
Question 3 (10%)
Using ONLY SAS Code to run an optimization problem and print output decision variables, objective
function Z, and constraints solutions. Code on a separate file called it SAS_Problem3.sas
A boutique chocolatier – Lafayette Village Raleigh has two most sellable products:
Its flagship assortment of triangular chocolates, called Pyramide, and the more decadent and deluxe
Pyramide Nut and Caramel.
How much of each should it produce to maximize profits?
Every box of Pyramide has a profit of $1.
Every box of Nut and Caramel has a profit of $6.
The daily demand is limited to at most 200 boxes of Pyramide and 300 boxes of Nut and Caramel.
The current workforce can produce a total of at most 400 boxes of chocolate per day.
Let x1 be # of boxes of Pyramide, x2 be # of boxes of Nut and Caramel
Question 4 (15%)
Costco company is a vertically integrated company (both producing and selling the goods in its own
retail outlets). After production, the goods are stores in two warehouses until needed by retail outlets.
Trucks are used to transport the goods from two plants to two warehouses, and then from the warehouses
to the three retail outlets. Using full truckloads, the following table shows each plant’s monthly output,
its shipping cost per truckload sent to each warehouse and the maximum amount it can ship per month
to each warehouse.
From Unit Shipping Cost Shipping Capacity Output
To Warehouse 1 Warehouse 2 Warehouse 1 Warehouse 2
Plant 1 $425 $560 125 150 200
Plant 2 $510 $600 175 200 300
For each retail outlet (RO), the next table shows its monthly demand, its shipping cost from each
warehouse, and the maximum amount that can be shipped per month from each warehouse. The
management wants to determine a distribution plan that will minimize the total shipping cost.
From Unit Shipping Cost Shipping Capacity
To RO1 RO2 RO3 RO1 RO2 RO3
Warehouse 1 $470 $500 $450 100 150 100
Warehouse 2 $390 $400 $410 125 150 75
Demand 150 200 150 150 200 150
a. Draw a graph that shows the company’s distribution network. Identify the supply nodes,
transshipment nodes and demand nodes. Write down the arc costs and capacities.
b. Formulate this problem as a linear programming formulation with notation.
c. Use Only Excel solver to find the optimal solution and report your solution.