#2. The Sunnyvale Sawmill produces prime cherry lumber (boards) to furniture manufacturers all over the Southeast US. To keep up with demand, the sawmill buys cherry logs from regional loggers and saws them into rough sawn cherry lumber. Both logs (the input) and lumber (the output) are measured in board feet (BF)1.

The quality of the lumber produced is related to the quality of the logs obtained. Sunnyvale buys logs that are graded as High Quality (F1), Medium Quality (F2) or Low Quality (F3). The rough sawn lumber produced from these logs are of four different qualities: Select (highest quality), followed by C1, followed by C2, and finally C3 (lowest quality).

The yields for converting logs into lumber, as well as the current costs for cherry logs (measured in $/BF) are given in the table below.

Lumber Quality/Grade Low GradeSelectC1C2C3Log Cost ($/BF)F10.550.180.160.111F20.260.350.20.19.55F30.140.430.220.21.35

Using the first row of the table for interpretation, one BF of F1 log produces .55 BF of Select lumber, .18 BF of C1 lumber, .16 BF of C2 lumber and .11 BF of C3 lumber. The cost of one BF of F1 cherry log is $1.

This month, the saw mill has no cherry lumber in inventory. To meet existing orders, it needs to produce at least 4800 BF of Select, 3600 BF of C1, 2700 BF of C2, and 1200 BF of C3. After contacting local loggers, supply of F2 and F3 logs is virtually unlimited, but only about 4000 BF of F1 quality logs can be obtained. Assume a higher grade of lumber cannot be substituted for a lower grade of lumber.

A. Clearly define your decision variables and then formulate a LP to help Sunnyvale meet or exceed production targets at minimum cost.

B. Solve your formulation in Excel. Indicate the optimal purchase quantities of each type of log and the optimal cost.