math

profilehelp55
CSCExcelAnalysisLinearFormulation.xlsx
Home>Mathematics homework help>math

Data - Exhibit 1

Student Commercial Industrial
Product completion cost $100,000 $200,000 $500,000
Variable cost/unit $15 $25 $35
Market Segment Size Segment Dev. Cost WTP/unit
Large, multidivisional Corporations 5,000 $150,000 $150 $1,200 $2,500
Corporate R&D and university labs 2,000 $100,000 $100 $1,000 $2,000
Consultants and professional Co 20,000 $200,000 $200 $300 $600
Small businesses 15,000 $200,000 $175 $225 $300
Students 500,000 $300,000 $50 $60 $100

Student

Solver Optimization Formulation
Student
Product completion cost $100,000
Variable cost/unit $15
Market Segment Size Segment Dev. Cost WTP/unit ak
Large, multidivisional Corporations 5,000 $150,000 $150 100%
Corporate R&D and university labs 2,000 $100,000 $100 100%
Consultants and professional Co 20,000 $200,000 $200 100%
Small businesses 15,000 $200,000 $175 100%
Students 500,000 $300,000 $50 60%
Large Constant: H= 5000
Decision Variables
Price: P = 0
Product Development: Y= 0
Xk Pk
Large, multidivisional Corporations
Corporate R&D and university labs
Consultants and professional Co
Small businesses
Students
Constraints
Participation Constraints
Wk*Xk-Pk >=0 P-Pk >=0 Pk-P+H*(1-Xk) >=0
Large, multidivisional Corporations 0 0 5000
Corporate R&D and university labs 0 0 5000
Consultants and professional Co 0 0 5000
Small businesses 0 0 5000
Students 0 0 5000
Objective Function
Profits
Large, multidivisional Corporations $0
Corporate R&D and university labs $0
Consultants and professional Co $0
Small businesses $0
Students $0
Product Completion Cost = $0
Net profits = $0

Commercial

Solver Optimization Formulation
Commercial
Product completion cost $200,000
Variable cost/unit $25
Market Segment Size Segment Dev. Cost WTP/unit ak
Large, multidivisional Corporations 5,000 $150,000 $1,200 100%
Corporate R&D and university labs 2,000 $100,000 $1,000 100%
Consultants and professional Co 20,000 $200,000 $300 100%
Small businesses 15,000 $200,000 $225 100%
Students 500,000 $300,000 $60 60%
Large Constant: H= 5000
Decision Variables
Price: P =
Product Development: Y=
Xk Pk
Large, multidivisional Corporations
Corporate R&D and university labs
Consultants and professional Co
Small businesses
Students
Constraints
Participation Constraints
Wk*Xk-Pk >=0 P-Pk >=0 Pk-P+H*(1-Xk) >=0
Large, multidivisional Corporations 0 0 5000
Corporate R&D and university labs 0 0 5000
Consultants and professional Co 0 0 5000
Small businesses 0 0 5000
Students 0 0 5000
Objective Function
Profits
Large, multidivisional Corporations $0
Corporate R&D and university labs $0
Consultants and professional Co $0
Small businesses $0
Students $0
Product Completion Cost = $0
Net profits = $0

Industrial

Solver Optimization Formulation
Industrial
Product completion cost $500,000
Variable cost/unit $35
Market Segment Size Segment Dev. Cost WTP/unit ak
Large, multidivisional Corporations 5,000 $150,000 $2,500 100%
Corporate R&D and university labs 2,000 $100,000 $2,000 100%
Consultants and professional Co 20,000 $200,000 $600 100%
Small businesses 15,000 $200,000 $300 100%
Students 500,000 $300,000 $100 60%
Large Constant: H= 50000
Decision Variables
Price: P =
Product Development: Y=
Xk Pk
Large, multidivisional Corporations
Corporate R&D and university labs
Consultants and professional Co
Small businesses
Students
Constraints
Participation Constraints
Wk*Xk-Pk >=0 P-Pk >=0 Pk-P+H*(1-Xk) >=0
Large, multidivisional Corporations 0 0 50000
Corporate R&D and university labs 0 0 50000
Consultants and professional Co 0 0 50000
Small businesses 0 0 50000
Students 0 0 50000
Objective Function
Profits
Large, multidivisional Corporations $0
Corporate R&D and university labs $0
Consultants and professional Co $0
Small businesses $0
Students $0
Product Completion Cost = $0
Net profits = $0

Multi-Product Solver

Linear Formulation
Data
Industrial Commercial Student
Product completion cost: CCi $500,000 $200,000 $100,000
Variable cost/unit: ci $35 $25 $15
Segment Size: Mk Segmnt Dev. Cost: Dk WTP/unit: Wki ak
Large, multidivisional Corporations 5,000 $150,000 $2,500 $1,200 $150 100%
Corporate R&D and university labs 2,000 $100,000 $2,000 $1,000 $100 100%
Consultants and professional Co 20,000 $200,000 $600 $300 $200 100%
Small businesses 15,000 $200,000 $300 $225 $175 100%
Students 500,000 $300,000 $100 $60 $50 60%
Max Number of Products (Nmax): 3
Decision Variables
Industrial Commercial Student Objective: Max Profits $0
Price: Pi 0 0 0 Sum_i Yi
Development: Yi 0 0 0 0 Constraints
Single Price Schedule: Pki >= Pi-H*(1-Xki)
Assignment: Xki Sum_i Xki Large, multidivisional Corporations 5000 5000 5000
Large, multidivisional Corporations 0 0 0 0 Corporate R&D and university labs 5000 5000 5000
Corporate R&D and university labs 0 0 0 0 Consultants and professional Co 5000 5000 5000
Consultants and professional Co 0 0 0 0 Small businesses 5000 5000 5000
Small businesses 0 0 0 0 Students 5000 5000 5000
Students 0 0 0 0
(Sum_k Xki)/5 0 0 0 Single Price Schedule :Pi >= Pki
Large, multidivisional Corporations 0 0 0
Auxiliary Variables Auxiliary Surplus: Ski Corporate R&D and university labs 0 0 0
Surplus: Sk Industrial Commercial Student Sum_i Ski Consultants and professional Co 0 0 0
Large, multidivisional Corporations 0 0.0 0.0 0.0 0 Small businesses 0 0 0
Corporate R&D and university labs 0 0.0 0.0 0.0 0 Students 0 0 0
Consultants and professional Co 0 0.0 0.0 0.0 0
Small businesses 0 0.0 0.0 0.0 0 Industrial Commercial Student
Students 0 0.0 0.0 0.0 0 Consumer Surplus: Sk >= Wki* (Sum_i Xki)-Pi
Large, multidivisional Corporations 0 0 0
Auxiliary Price: Pki Corporate R&D and university labs 0 0 0
Industrial Commercial Student Consultants and professional Co 0 0 0
Large, multidivisional Corporations 0.0 0.0 0.0 Small businesses 0 0 0
Corporate R&D and university labs 0.0 0.0 0.0 Students 0 0 0
Consultants and professional Co 0.0 0.0 0.0
Small businesses 0.0 0.0 0.0 Single Purchase: Ski = Wki*Xki - Pki
Students 0.0 0.0 0.0 Large, multidivisional Corporations 0 0 0
Corporate R&D and university labs 0 0 0
Parameter: Consultants and professional Co 0 0 0
Big H 5000 Small businesses 0 0 0
Students 0 0 0
Marginal Surplus: Pki*ak - ci*Xki
Industrial Commercial Student Sum_i Zki Single Purchase: Sk= sum_i Ski
Large, multidivisional Corporations 0 0 0 0 Large, multidivisional Corporations 0
Corporate R&D and university labs 0 0 0 0 Corporate R&D and university labs 0
Consultants and professional Co 0 0 0 0 Consultants and professional Co 0
Small businesses 0 0 0 0 Small businesses 0
Students 0 0 0 0 Students 0

Objective: Max [M k ⋅(P

ki ⋅α

k - c

i ⋅X

ki )

k=1

5

∑ i=1

3

∑ −Dk ⋅Xki] − CCi ⋅Yi i=1

3

∑ Subject to −) Segment Dev.: X

ki ≤ Y

i ∀k, i

−) Single price: P i - H⋅(1-X

ki ) ≤ P

ki ≤ P

i ∀k , i

−) Single purchase: X ki

i=1

3

∑ ≤ 1 ∀k −) Consumer surplus: S

ki = W

ki ⋅X

ki - P

ki ∀k , i

S k = S

ki i=1

3

∑ ∀k

S k ≥ W

ki ⋅ X

ki i=1

3

∑ ⎛

⎝⎜ ⎞

⎠⎟ - P

i ∀k , i

-) Max # Product Develop: Y i ≤N

Max i=1

3

∑ −) Non-Negativities and Binary: P

i , P

ki , S

ki , S

k ≥0 and X

ij , Y

j ~ Binary(0,1)

Objective: Max [M

k

×(P

ki

×a

k

- c

i

×X

ki

)

k=1

5

å

i=1

3

å

-D

k

×X

ki

] - CC

i

×Y

i

i=1

3

å

Subject to

-) Segment Dev.: X

ki

£Y

i

"k, i

-) Single price: P

i

- H×(1-X

ki

) ≤ P

ki

≤ P

i

"k , i

-) Single purchase: X

ki

i=1

3

å

£ 1 "k

-) Consumer surplus: S

ki

= W

ki

×X

ki

- P

ki

"k , i

S

k

=S

ki

i=1

3

å

"k

S

k

³ W

ki

×X

ki

i=1

3

å

æ

è

ç

ö

ø

÷

- P

i

"k , i

-) Max # Product Develop: Y

i

≤N

Max

i=1

3

å

-) Non-Negativities and Binary:

P

i

, P

ki

, S

ki

, S

k

≥0 and X

ij

, Y

j

~ Binary(0,1)