**Note: Please explain with steps and methods. If possible please show the formulas used and explain the steps***

Environmental Planning

You are the operations manager of a chemical company which produces five products in a common production facility. The following table gives potential sales for each product, along with variables costs and revenues. Production in any year cannot exceed the sales potential.

ProductSales potentialVariable costsRevenues

(tons/year)($/ton)($/ton)

A20007001000

B1600600800

C150010001500

D100016001800

E60013001700

Your production facility rotates through the product line because it can produce only one product at a time. The production rates differ for the various products. It takes 0.3 hours to make 1 ton of A, 0.5 hour for a ton of B, and 1 hour each for a ton of C, D, or E. The facility can be operated up to 4000 hours per year.

The file EnviroPlanning.xlsx contains all the information given above in a format designed for optimization. Use this template to answer the following questions.

a. Determine the maximum profit the company can achieve from its product line in the coming year.

$____________________

b. What is the shadow price on the sales potential for product C?

$____________________

Parts (c), (d) and (e) are independent: changes described in one part do not apply to the other parts. For example, part (c) involves changing the sales potential of product C from 1000 to 2500 tons. For parts (d) and (e), the sales potential should be set back to the original 1500 tons.

c. Describe in words how the optimal production plan and profit change as the sales potential on Product C changes from 1000 to 2500 tons.

d. An engineering firm is offering to improve the efficiency of the process for manufacturing products C, D, and E. This process improvement will reduce the time required for all three products by up to 10%. How big an improvement to all three products is necessary in order to offset the cost of $30,000? Your answer should be a percentage between 0% and 10% (answer to the nearest integer percentage). Include a table of results to justify your answer.

Required improvement: ___________

TABLE:

e. The EPA is proposing to limit particulate emissions from your company over the next five years to the amounts specified below. The sales potential for each of the five products is expected to remain stable over this period.

YearTotal Emissions

(tons)

1100

280

360

440

520

The emissions produced by each product are given below.

ProductParticulate Emissions

(tons/ton produced)

A0.0010

B0.0025

C0.0300

D0.0400

E0.0250

The company plans to satisfy these requirements by modifying the product mix each year. What would the optimal profits be in each of the next five years?

YearProfit

1$______________

2$______________

3$______________

4$______________

5$______________

A B C D E F G H 1 Planning template H K L 2 A B C D E Decisions 100 100 100 100 100 Revenue 1000 800 1500 1800 1700 Variable Cost 700 600 1000 1600 1300 10 Margin 11 12 Sales Potential 2000 1600 1500 1000 600 13 Unused Sales Potential 1900 1500 1400 900 500 14 15 Used 16 Available Hours Consumed 0.3 0.5 17 4000 18 19 20 21 22 Sheet1 +