FOR 240 – Homework Assignment 2 Mathematical Programming in Forest Management with Excel Solver Tool Introduction to Computing in Natural Resources In this exercise, we will use Microsoft Excel to solve the following linear programming problem. WVUTIMBER produces both sawlogs and pulpwood from its forest. Its takes 600 acres of forest to produce 100 truckloads of sawlogs and 400 acres to produce 100 truckloads of pulpwood. Let’s assume that 100 truckloads make one standard delivery. The company has 2400 acres of forest lands. It takes about 100 labor hours to harvest one truckload of sawlogs and 200 hours to produce same amount of pulpwood. The total number of labor hours with the company is 600 for a given harvesting season. It is assumed that the demand of pulpwood at processing industry is not more than 100 truck loads than that of sawlogs. Also, maximum demand of pulpwood is 200 truck loads per season. Let’s say that profit from one standard delivery (100 truck loads) of sawlog is $ 50000 and profit from pulpwood is $ 40000. WVUTIMBER wants to determine the best combination of sawlogs and pulpwood harvesting from its forest. This problem can be formulated as LP. Let’s define the decision variables first. X1 = # of 100 Truckloads of sawlogs per season X2 = # of 100 Truckloads of pulpwood per season The objective is to maximize the profit (z) of this timber sale including pulpwood and sawlogs. Maximize z = 50,000X1 + 40,000X2 Next, we need to define our constraints. Acres
Document Preview:
: Total available acres = 2,400 100 truckloads of sawlogs require 600 acres. 100 truckloads of pulpwood require 400 acres. Hence, 600X1 + 400X2 = 0. This problem can be written in the following format: Max: Z = 50000X1 + 40000X2 …………………….. (OBJECTIVE FUNCTION) 600X1 + 400X2 = 0 ……………………………….…….(NON NEGATIVE CONSTRAINT) X2 >= 0……………………………….……. (NON NEGATIVE CONSTRAINT) Create an Excel workbook and save it as LP.xlsx, which must include the above model. Once you have this workbook, you need to accomplish the followings: a. Solve the model using excel solver. b. Report optimal value of Z. c. Describe how much sawlogs and pulpwoods should be harvested to obtain the reported value of z. Please provide me a typed summary report including the output from solver as well as reported values by the beginning of the class next week.
Attachments:
HW2.pdf
