Linear programming : excel solution

BC Canners is a medium-sized company which cans and distributes a variety of tomato products under private brands. They purchase raw tomatoes and convert it to various tomato products which are sold to retailers. The raw tomato crop, which had been purchased at planting, was beginning to arrive at the cannery. They were to receive 3 million pounds of raw tomatoes.
They make and sell three finished products out of the raw tomatoes: canned whole tomatoes (WT), tomato juice (TJ) and tomato paste (TP), with profit margins per pound of $0.09, $0.07, and $0.08, respectively. The maximum market potential for the three products is: 1.44 million pounds of WT, 1 million pounds of TJ and 2 million pounds of TP.

BC Canners used a numerical scale to record the quality of both raw produce and finished products. This scale ran from zero to ten, the higher number representing better quality. According to this scale, "A" tomatoes averaged nine points per pound and "B" tomatoes averaged five points per pound. As for the finished products, WT product must average at least 8 points, while TJ must average at least 6 points. There is no minimum point quality requirement for TP. They estimated that 20% of the incoming 3 million pounds of raw tomatoes are Grade A, with the remainder Grade B. (Assume that there is no other raw material or ingredient added and there is no loss of weight during processing. For instance, 5 pounds of Grade A tomato and 2 pounds of Grade B tomato when mixed will result in 7 pounds of a finished product.)

BC Canners has to decide how much of the finished products to produce so as to maximize total net profits from the three products.

1. Define the decision variables and formulate a linear programming model to maximize net profit. Please state your variables, objective and constraints clearly in this sheet. (Hint: Your decision is to determine to use the two inputs, Grade A and B raw tomatoes, to produce each of the three finished products and your variables should be defined accordingly.)

2. Use "Excel Solver" to set up and solve the model and answer the following questions. (Remember to check the "Assume Non-negative" and "Assume Linear Model" under Options before solving the model.)

a. How many pounds of the three products should the firm produce?
b. What is the total profit in dollars?
c. Is the maximum demand for the three products fully satisfied? If some of the product demands are not satisfied fully, can you intuitively guess why this is the case?