Linear Programming

BUAD 831 - ASSIGNMENT 3 – FALL 2017

THE FLORIDA FRUIT COMPANY

On Monday, October 30, 2017, Raj Patel, President of the Florida Fruit Company, asked the comptroller, the marketing

manager, and the operations manager to meet with him to discuss the amount of orange juice products to pack that

season. The orange crop, which had been purchased at planting, was beginning to arrive at the cannery, and packing

operations would have to begin by the following Monday. The Florida Fruit Company was a medium-size company which

canned and distributed a variety of fruit and vegetable juices under private brands in the southeastern states.

Jill Short, the comptroller, and Barb Berg, the marketing manager, were the first to arrive in Patel's office. Dan Chen, the

operations manager, came in a few minutes later and said that he had picked up the latest estimate of the quality of the

incoming oranges. About 20% of the 3-million-pound orange crop was Grade "A" quality and the remaining portion was

Grade B.

Patel asked Berg about the demand for orange products for the coming year. The marketing manager then passed

around the latest demand forecasts, which are shown in Exhibit 1. She reminded the group that the selling prices had

been set in light of the long-term marketing strategy of the Company, and potential sales had been forecasted at these

prices. Production could not exceed these demand forecasts because no warehouse space was available to inventory

any unsold products.

Exhibit 1: Demand Forecasts (Cases)

Product Selling Price ($ per case) Demand Forecast (cases) _________________________________________________________________________ canned sliced oranges 40 80,000 orange juice 45 50,000 orange frozen concentrate 38 80,000 __________________________________________________________________________

In May, after Florida Fruit had signed contracts agreeing to purchase the grower's production at an average delivered

price of 60 cents per pound, Jill Short had computed the orange products’ profit contributions (see Exhibit 2).

The pounds per case for each product are given parenthetically as follows: Canned sliced oranges (18), orange juice (20),

and frozen orange concentrate (25).

Dan Chen brought to Berg's attention that, although there was ample production capacity, it was impossible to produce

all canned orange slices as too small a portion of the orange crop was "A" quality. Florida Fruit used a numerical scale to

record the quality of both raw produce and prepared products. This scale ran from zero to ten, the higher number

representing better quality. Rating oranges according to this scale, "A" oranges averaged nine points per pound and "B"

oranges averaged five points per pound. Dan Chen noted that the minimum average input quality for canned sliced

oranges was eight and for orange juice it was six points per pound. Orange frozen concentrate could be made entirely

from "B" grade oranges, meaning production of canned orange slices was limited to 800,000 pounds.

Patel stated that this was not a real limitation. He had been recently solicited to purchase 80,000 pounds of Grade "A"

oranges at 81.5 cents per pound and at that time had turned down the offer. He felt, however, that the oranges were

still available if needed.

Exhibit 2: Product Item Profitability ($ per case) ___________________________________________________________________________ Product Canned Oranges Orange Juice Frozen Concentrate --------------------------------------------------------------------------------------------------------------------------- Selling Price 40.00 45.00 38.00 -------------------------------------------------------------------------------------------------------------------------- Variable Costs: Direct Labor 11.80 13.20 5.40 Variable Overhead 1 2.40 3.60 2.60 Variable Selling/Marketing 4.00 8.50 3.80 Packaging Material 7.00 6.50 7.70 Total Variable Costs 25.20 31.80 19.50 ------------------------------------------------------------------------------------------------------------------------- Contribution to profit 14.80 13.20 18.50 ------------------------------------------------------------------------------------------------------------------------ 1 Variable overhead costs are those costs that vary with production that are not included in all other categories of variable c osts.

Examples are gas and maintenance costs on machines that produce a product, or, payroll taxes for hourly workers who work only

enough hours to meet production levels.

_________________________________________________________________________________________________

PART 1.

Use the Excel template (posted in Canvas) to determine, to the best of your ability, a FEASIBLE production plan. Assume

they do not purchase the additional 80,000 pounds of grade “A” oranges. For Part 1, you will use the first worksheet

labeled ‘Part 1’.

USE THE PROVIDED EXCEL TEMPLATE. IF YOU DEVELOP YOUR OWN SPREADSHEET, YOUR ASSIGNMENT WILL NOT BE

ACCEPTED. Examining 55 different spreadsheets to determine if solutions are feasible would greatly delay grading. And,

you need not develop one; it’s already made up for you.

PART 2.

a) Use the 2nd worksheet labeled “Part 2” to develop a linear programming model in SOLVER to determine the best

production plan. (If you are having difficulty modeling the quality constraints, see the worksheet ‘hint’.)

b) Calculate the percentage increase in profit as a result of implementing the optimal plan vs your plan in Part 1.

c) Using ONLY the output from Excel Solver, determine if Florida Fruit should purchase the additional 80,000

pounds of grade “A” oranges? If yes, what is the net benefit to the company?

What to Submit: The ONE Excel file you generated from the provided template. This file must contain the ‘Solution

Report’ worksheet that is automatically generated from running Solver.

Suggestion: Examine the grading rubric for this assignment. It should provide insight on some constraints.