BioPharma Inc.
Regional Sales and Plant Capacities
Plant Capacity
Highcal Relax
Latin
America
7.0 7.0
Brazil 18.0
Europe 15.0 12.0 Germany 45.0
Asia w/o
Japan
5.0 3.0
India 18.0
Japan 7.0 8.0 Japan 10.0
Mexico 3.0 3.0 Mexico 30.0
U.S. 18.0 17.0 U.S. 22.0
Calculation of Fixed and Variable Costs for Specified Year
Exchange Rates Year 2013
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
2013
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
Brazil
20.0 5.0 5.0 3.6 5.1 4.6 6.6
Brazilian
Real
2.15 Brazil
20.0 5.0 5.0 3.6 5.1 4.6 6.6
Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0 Euro 0.75 Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0
India
14.0 3.0 3.0 3.6 4.5 4.5 6.0
Indian
Rupee
58.44 India
14.0 3.0 3.0 3.6 4.5 4.5 6.0
Japan
13.0 4.0 4.0 3.9 6.0 5.1 7.0
Japanese
Yen
97.58 Japan
13.0 4.0 4.0 3.9 6.0 5.1 7.0
Mexico
30.0 6.0 6.0 3.6 5.0 4.6 6.5
Mexican
Peso
12.75 Mexico
30.0 6.0 6.0 3.6 5.0 4.6 6.5
U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
U.S.
Dollar
1.00 U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
Transportation Costs ($ / kg) Variable Material, Production and Transportation Cost for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0.20 0.45 0.50 0.50 0.40 0.45 Brazil 8.90 9.15 9.20 9.20 9.10 9.15
Germany 0.45 0.20 0.35 0.40 0.30 0.30 Germany 10.35 10.10 10.25 10.30 10.20 10.20
India 0.50 0.35 0.20 0.30 0.50 0.45 India 8.60 8.45 8.30 8.40 8.60 8.55
Japan 0.50 0.40 0.30 0.10 0.45 0.45 Japan 10.40 10.30 10.20 10.00 10.35 10.35
Mexico 0.40 0.30 0.50 0.45 0.20 0.25 Mexico 9.00 8.90 9.10 9.05 8.80 8.85
U.S. 0.45 0.30 0.45 0.45 0.25 0.20 U.S. 9.05 8.90 9.05 9.05 8.85 8.80
2013 Exchange Rates Variable Material, Production and Transportation Cost for Relax
2013
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Real
Brazilian
11.40 11.65 11.70 11.70 11.60 11.65
Euro 0.75 Germany 12.45 12.20 12.35 12.40 12.30 12.30
Indian
Rupee
11.00 10.85 10.70 10.80 11.00 10.95
Japanese
Yen
12.60 12.50 12.40 12.20 12.55 12.55
Mexican
Peso
11.50 11.40 11.60 11.55 11.30 11.35
U.S.
Dollar
11.45 11.30 11.45 11.45 11.25 11.20
Import Duties (%)
Latin
America
Asia w/o
Japan
Market
Fixed and Variable Production Costs by Plant at 2013 Exchange Rates
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
Fixed and Variable Costs at Current Exchange Rates
Year 2013
Plant
Plant Fixed
Cost
(Million $)
Highcal
Fixed
Cost
(Million $)
Relax
Fixed
Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
Brazil 20.0 5.0 5.0 3.6 5.1 4.6 6.6
Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0
India 14.0 3.0 3.0 3.6 4.5 4.5 6.0
Japan 13.0 4.0 4.0 3.9 6.0 5.1 7.0
Mexico 30.0 6.0 6.0 3.6 5.0 4.6 6.5
U.S. 23.0 5.0 5.0 3.6 5.0 4.5 6.5
Variable Material, Production and Transportation Cost for Highcal Total Variable Cost Including Duties for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S. From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 8.9 9.2 9.2 9.2 9.1 9.2 Brazil 8.9 9.4 11.7 9.8 12.3 9.5
Germany 10.4 10.1 10.3 10.3 10.2 10.2 Germany 13.5 10.1 13.0 10.9 13.8 10.6
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S. From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 11.4 11.7 11.7 11.7 11.6 11.7 Brazil 11.4 12.0 14.9 12.4 15.7 12.1
Germany 12.5 12.2 12.4 12.4 12.3 12.3 Germany 16.2 12.2 15.7 13.1 16.6 12.8
India 11.0 10.9 10.7 10.8 11.0 11.0 India 14.3 11.2 10.7 11.4 14.9 11.4
Plants Open / Shut, Lines On / Off Highcal Quantity Shipped From / To
Plant
Plant Open
(1) / Shut
(0)
Highcal
On (1) /
Off (0)
Relax On
(1) / Off
(0)
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Total Cost = 1,267.18$ Relax Quantity Shipped From / To
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 7.0 0.0 0.0 0.0 0.0 0.0
Germany 0.0 12.0 0.0 5.0 0.0 0.0
India 0.0 0.0 3.0 3.0 0.0 0.0
Japan 0.0 0.0 0.0 0.0 0.0 0.0
Plant
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Germany 28 -7.1E-15 28 Relax 0.0 0.0 0.0 0.0 0.0 0.0
India 0 6 12
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
BioPharma Inc.
Regional Sales and Plant Capacities
Plant Capacity
Highcal Relax
Latin
America
7.0 7.0
Brazil 18.0
Europe 15.0 12.0 Germany 45.0
Asia w/o
Japan
5.0 3.0
India 18.0
Japan 7.0 8.0 Japan 10.0
Mexico 3.0 3.0 Mexico 30.0
U.S. 18.0 17.0 U.S. 22.0
Calculation of Fixed and Variable Costs for Specified Year
Exchange Rates Year 2012
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
2012
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
Brazil
20.0 5.0 5.0 3.6 5.1 4.6 6.6
Brazilian
Real
1.95 Brazil
22.1 5.5 5.5 4.0 5.6 5.1 7.3
Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0 Euro 0.78 Germany 43.3 12.5 12.5 3.8 5.8 4.8 6.7
India
14.0 3.0 3.0 3.6 4.5 4.5 6.0
Indian
Rupee
53.46 India
15.3 3.3 3.3 3.9 4.9 4.9 6.6
Japan
13.0 4.0 4.0 3.9 6.0 5.1 7.0
Japanese
Yen
79.79 Japan
15.9 4.9 4.9 4.8 7.3 6.2 8.6
Mexico
30.0 6.0 6.0 3.6 5.0 4.6 6.5
Mexican
Peso
13.15 Mexico
29.1 5.8 5.8 3.5 4.8 4.5 6.3
U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
U.S.
Dollar
1.00 U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
Transportation Costs ($ / kg) Variable Material, Production and Transportation Cost for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0.20 0.45 0.50 0.50 0.40 0.45 Brazil 9.79 10.04 10.09 10.09 9.99 10.04
Germany 0.45 0.20 0.35 0.40 0.30 0.30 Germany 9.97 9.72 9.87 9.92 9.82 9.82
India 0.50 0.35 0.20 0.30 0.50 0.45 India 9.35 9.20 9.05 9.15 9.35 9.30
Japan 0.50 0.40 0.30 0.10 0.45 0.45 Japan 12.61 12.51 12.41 12.21 12.56 12.56
Mexico 0.40 0.30 0.50 0.45 0.20 0.25 Mexico 8.74 8.64 8.84 8.79 8.54 8.59
U.S. 0.45 0.30 0.45 0.45 0.25 0.20 U.S. 9.05 8.90 9.05 9.05 8.85 8.80
Exchange Rate History Variable Material, Production and Transportation Cost for Relax
2013
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Real
Brazilian
12.55 12.80 12.85 12.85 12.75 12.80
Euro 0.75 Germany 11.99 11.74 11.89 11.94 11.84 11.84
Indian
Rupee
11.98 11.83 11.68 11.78 11.98 11.93
Japanese
Yen
15.30 15.20 15.10 14.90 15.25 15.25
Mexican
Peso
11.16 11.06 11.26 11.21 10.96 11.01
U.S.
Dollar
11.45 11.30 11.45 11.45 11.25 11.20
Import Duties (%)
Latin
America
Asia w/o
Japan
Market
Fixed and Variable Production Costs by Plant at 2013 Exchange Rates
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
Fixed and Variable Costs at Current Exchange Rates
Year 2012
Plant
Plant Fixed
Cost
(Million $)
Highcal
Fixed
Cost
(Million $)
Relax
Fixed
Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
Brazil 22.1 5.5 5.5 4.0 5.6 5.1 7.3
Germany 43.3 12.5 12.5 3.8 5.8 4.8 6.7
India 15.3 3.3 3.3 3.9 4.9 4.9 6.6
Japan 15.9 4.9 4.9 4.8 7.3 6.2 8.6
Mexico 29.1 5.8 5.8 3.5 4.8 4.5 6.3
U.S. 23.0 5.0 5.0 3.6 5.0 4.5 6.5
Variable Material, Production and Transportation Cost for Highcal Total Variable Cost Including Duties for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S. From / To Latin America Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 9.8 10.0 10.1 10.1 10.0 10.0 Brazil 9.8 10.3 12.8 10.7 13.5 10.4
Germany 10.0 9.7 9.9 9.9 9.8 9.8 Germany 13.0 9.7 12.5 10.5 13.3 10.2
India 9.4 9.2 9.1 9.2 9.4 9.3 India 12.2 9.5 9.1 9.7 12.6 9.7
Japan 12.6 12.5 12.4 12.2 12.6 12.6 Japan 16.4 12.9 15.8 12.2 17.0 13.1
Mexico 8.7 8.6 8.8 8.8 8.5 8.6 Mexico 11.4 8.9 11.2 9.3 8.5 8.9
U.S. 9.1 8.9 9.1 9.1 8.9 8.8 U.S. 11.8 9.2 11.5 9.6 11.9 8.8
Variable Material, Production and Transportation Cost for Relax Total Variable Cost Including Duties for Relax
From / To
Latin
America
Asia w/o
Japan
Asia w/o
Japan
U.S. 11.5 11.3 11.5 11.5 11.3 11.2 U.S. 14.9 11.6 14.5 12.1 15.2 11.2
Decision Variables
Plant
(1) / Shut
(0)
On (1) /
Off (0)
(1) / Off
(0)
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
India 1.0 1.0 1.0 India 0.0 3.0 5.0 7.0 0.0 0.0
Japan 0.0 0.0 0.0 Japan 0.0 0.0 0.0 0.0 0.0 0.0
Mexico 1.0 1.0 1.0 Mexico 0.0 8.0 0.0 0.0 3.0 0.0
U.S. 1.0 1.0 0.0 U.S. 0.0 4.0 0.0 0.0 0.0 18.0
Total Cost = 1,283.54$ Relax Quantity Shipped From / To
America
Japan
Latin
Asia w/o
Japan 0.0 0.0 0.0 0.0 0.0 0.0
Mexico 0.0 0.0 0.0 0.0 3.0 16.0
U.S. 0.0 0.0 0.0 0.0 0.0 0.0
Constraints
Capacity Demand
Plant
Total Capacity
Highcal Relax
Demand
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
BioPharma Inc.
Regional Sales and Plant Capacities
Plant Capacity
Highcal Relax
Latin
America
7.0 7.0
Brazil 18.0
Europe 15.0 12.0 Germany 45.0
Asia w/o
Japan
5.0 3.0
India 18.0
Japan 7.0 8.0 Japan 10.0
Mexico 3.0 3.0 Mexico 30.0
U.S. 18.0 17.0 U.S. 22.0
Calculation of Fixed and Variable Costs for Specified Year
Exchange Rates Year 2011
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
2011
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Productio
n Cost
($/kg)
Brazil
20.0 5.0 5.0 3.6 5.1 4.6 6.6
Brazilian
Real
1.67 Brazil
25.7 6.4 6.4 4.6 6.6 5.9 8.5
Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0 Euro 0.72 Germany 46.9 13.5 13.5 4.1 6.3 5.2 7.3
India
14.0 3.0 3.0 3.6 4.5 4.5 6.0
Indian
Rupee
46.85 India
17.5 3.7 3.7 4.5 5.6 5.6 7.5
Japan
13.0 4.0 4.0 3.9 6.0 5.1 7.0
Japanese
Yen
79.70 Japan
15.9 4.9 4.9 4.8 7.3 6.2 8.6
Mexico
30.0 6.0 6.0 3.6 5.0 4.6 6.5
Mexican
Peso
12.42 Mexico
30.8 6.2 6.2 3.7 5.1 4.7 6.7
U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
U.S.
Dollar
1.00 U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
Transportation Costs ($ / kg) Variable Material, Production and Transportation Cost for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0.20 0.45 0.50 0.50 0.40 0.45 Brazil 11.40 11.65 11.70 11.70 11.60 11.65
Germany 0.45 0.20 0.35 0.40 0.30 0.30 Germany 10.76 10.51 10.66 10.71 10.61 10.61
India 0.50 0.35 0.20 0.30 0.50 0.45 India 10.60 10.45 10.30 10.40 10.60 10.55
Japan 0.50 0.40 0.30 0.10 0.45 0.45 Japan 12.62 12.52 12.42 12.22 12.57 12.57
Mexico 0.40 0.30 0.50 0.45 0.20 0.25 Mexico 9.23 9.13 9.33 9.28 9.03 9.08
U.S. 0.45 0.30 0.45 0.45 0.25 0.20 U.S. 9.05 8.90 9.05 9.05 8.85 8.80
Exchange Rate History Variable Material, Production and Transportation Cost for Relax
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazilian
Real
Indian
Rupee
Japanese
Yen
Mexican
Peso
U.S.
Dollar
Import Duties (%)
Latin
America
Asia w/o
Japan
Market
Fixed and Variable Production Costs by Plant at 2013 Exchange Rates
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
Fixed and Variable Costs at Current Exchange Rates
Year 2011
Plant
Plant Fixed
Cost
(Million $)
Highcal
Fixed
Cost
(Million $)
Relax
Fixed
Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
Brazil 25.7 6.4 6.4 4.6 6.6 5.9 8.5
Germany 46.9 13.5 13.5 4.1 6.3 5.2 7.3
India 17.5 3.7 3.7 4.5 5.6 5.6 7.5
Japan 15.9 4.9 4.9 4.8 7.3 6.2 8.6
Mexico 30.8 6.2 6.2 3.7 5.1 4.7 6.7
U.S. 23.0 5.0 5.0 3.6 5.0 4.5 6.5
Variable Material, Production and Transportation Cost for Highcal Total Variable Cost Including Duties for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S. From / To
Latin America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 11.4 11.7 11.7 11.7 11.6 11.7 Brazil 11.4 12.0 14.9 12.4 15.7 12.1
Germany 10.8 10.5 10.7 10.7 10.6 10.6 Germany 14.0 10.5 13.5 11.4 14.3 11.0
India 10.6 10.5 10.3 10.4 10.6 10.6 India 13.8 10.8 10.3 11.0 14.3 11.0
Japan 12.6 12.5 12.4 12.2 12.6 12.6 Japan 16.4 12.9 15.8 12.2 17.0 13.1
Mexico 9.2 9.1 9.3 9.3 9.0 9.1 Mexico 12.0 9.4 11.8 9.8 9.0 9.4
Germany 13.0 12.7 12.9 12.9 12.8 12.8 Germany 16.8 12.7 16.3 13.7 17.3 13.3
India 13.6 13.4 13.3 13.4 13.6 13.5 India 17.7 13.9 13.3 14.2 18.4 14.1
Japan 15.3 15.2 15.1 14.9 15.3 15.3 Japan 19.9 15.7 19.2 14.9 20.6 15.9
Mexico 11.8 11.7 11.9 11.8 11.6 11.6 Mexico 15.3 12.0 15.1 12.6 11.6 12.1
U.S. 11.5 11.3 11.5 11.5 11.3 11.2 U.S. 14.9 11.6 14.5 12.1 15.2 11.2
Plant Open
Highcal
Relax On
Plant
(1) / Shut
(0)
On (1) /
Off (0)
(1) / Off
(0)
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0.0 0.0 0.0 Brazil 0.0 0.0 0.0 0.0 0.0 0.0
Germany 1.0 0.0 1.0 Germany 0.0 0.0 0.0 0.0 0.0 0.0
India 1.0 1.0 1.0 India 0.0 1.0 5.0 7.0 0.0 0.0
Latin
Asia w/o
From / To
America
Europe
Japan
Japan Mexico U.S.
Brazil 0.0 0.0 0.0 0.0 0.0 0.0
Germany 0.0 12.0 0.0 8.0 0.0 17.0
India 0.0 0.0 3.0 0.0 0.0 0.0
Japan 0.0 0.0 0.0 0.0 0.0 0.0
Mexico 7.0 0.0 0.0 0.0 3.0 0.0
U.S. 0.0 0.0 0.0 0.0 0.0 0.0
Plant
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Mexico 0 10 20
U.S. 0 0 0
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
BioPharma Inc.
Regional Sales and Plant Capacities
Plant Capacity
Highcal Relax
Latin
America
7.0 7.0
Brazil 18.0
Europe 15.0 12.0 Germany 45.0
Asia w/o
Japan
5.0 3.0
India 18.0
Japan 7.0 8.0 Japan 10.0
Mexico 3.0 3.0 Mexico 30.0
U.S. 18.0 17.0 U.S. 22.0
Calculation of Fixed and Variable Costs for Specified Year
Exchange Rates Year 2010
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
2010
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Productio
n Cost
($/kg)
Brazil
20.0 5.0 5.0 3.6 5.1 4.6 6.6
Brazilian
Real
1.75 Brazil
24.6 6.1 6.1 4.4 6.3 5.7 8.1
Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0 Euro 0.75 Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0
India
14.0 3.0 3.0 3.6 4.5 4.5 6.0
Indian
Rupee
45.72 India
17.9 3.8 3.8 4.6 5.8 5.8 7.7
Japan
13.0 4.0 4.0 3.9 6.0 5.1 7.0
Japanese
Yen
87.78 Japan
14.5 4.4 4.4 4.3 6.7 5.7 7.8
Mexico
30.0 6.0 6.0 3.6 5.0 4.6 6.5
Mexican
Peso
12.63 Mexico
30.3 6.1 6.1 3.6 5.0 4.6 6.6
U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
U.S.
Dollar
1.00 U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
Transportation Costs ($ / kg) Variable Material, Production and Transportation Cost for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0.20 0.45 0.50 0.50 0.40 0.45 Brazil 10.89 11.14 11.19 11.19 11.09 11.14
Germany 0.45 0.20 0.35 0.40 0.30 0.30 Germany 10.35 10.10 10.25 10.30 10.20 10.20
India 0.50 0.35 0.20 0.30 0.50 0.45 India 10.85 10.70 10.55 10.65 10.85 10.80
Japan 0.50 0.40 0.30 0.10 0.45 0.45 Japan 11.51 11.41 11.31 11.11 11.46 11.46
Mexico 0.40 0.30 0.50 0.45 0.20 0.25 Mexico 9.08 8.98 9.18 9.13 8.88 8.93
U.S. 0.45 0.30 0.45 0.45 0.25 0.20 U.S. 9.05 8.90 9.05 9.05 8.85 8.80
Exchange Rate History Variable Material, Production and Transportation Cost for Relax
2013
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Real
Brazilian
13.96 14.21 14.26 14.26 14.16 14.21
Euro 0.75 Germany 12.45 12.20 12.35 12.40 12.30 12.30
Indian
Rupee
13.92 13.77 13.62 13.72 13.92 13.87
Japanese
Yen
13.95 13.85 13.75 13.55 13.90 13.90
Mexican
Peso
11.61 11.51 11.71 11.66 11.41 11.46
U.S.
Dollar
11.45 11.30 11.45 11.45 11.25 11.20
Import Duties (%)
Latin
America
Asia w/o
Japan
Market
Fixed and Variable Production Costs by Plant at 2013 Exchange Rates
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
Fixed and Variable Costs at Current Exchange Rates
Year 2010
Plant
Plant Fixed
Cost
(Million $)
Highcal
Fixed
Cost
(Million $)
Relax
Fixed
Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
Brazil 24.6 6.1 6.1 4.4 6.3 5.7 8.1
Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0
India 17.9 3.8 3.8 4.6 5.8 5.8 7.7
Japan 14.5 4.4 4.4 4.3 6.7 5.7 7.8
Mexico 30.3 6.1 6.1 3.6 5.0 4.6 6.6
U.S. 23.0 5.0 5.0 3.6 5.0 4.5 6.5
Variable Material, Production and Transportation Cost for Highcal Total Variable Cost Including Duties for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S. From / To Latin America Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 10.9 11.1 11.2 11.2 11.1 11.1 Brazil 10.9 11.5 14.2 11.9 15.0 11.6
Germany 10.4 10.1 10.3 10.3 10.2 10.2 Germany 13.5 10.1 13.0 10.9 13.8 10.6
India 10.9 10.7 10.6 10.7 10.9 10.8 India 14.1 11.0 10.6 11.3 14.7 11.2
Japan 11.5 11.4 11.3 11.1 11.5 11.5 Japan 15.0 11.7 14.4 11.1 15.5 11.9
Mexico 9.1 9.0 9.2 9.1 8.9 8.9 Mexico 11.8 9.3 11.7 9.7 8.9 9.3
U.S. 9.1 8.9 9.1 9.1 8.9 8.8 U.S. 11.8 9.2 11.5 9.6 11.9 8.8
Variable Material, Production and Transportation Cost for Relax Total Variable Cost Including Duties for Relax
From / To
America
Japan
Japan
Latin
Asia w/o
Asia w/o
U.S. 11.5 11.3 11.5 11.5 11.3 11.2 U.S. 14.9 11.6 14.5 12.1 15.2 11.2
Decision Variables
Plants Open / Shut, Lines On / Off Highcal Quantity Shipped From / To
Plant
(1) / Shut
(0)
On (1) /
Off (0)
(1) / Off
(0)
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
India 0.0 0.0 0.0 India 0.0 0.0 0.0 0.0 0.0 0.0
Japan 0.0 0.0 0.0 Japan 0.0 0.0 0.0 0.0 0.0 0.0
Mexico 1.0 1.0 1.0 Mexico 0.0 15.0 1.0 7.0 3.0 0.0
U.S. 1.0 1.0 0.0 U.S. 0.0 0.0 4.0 0.0 0.0 18.0
Total Cost = 1,351.19$ Relax Quantity Shipped From / To
Latin
America
Asia w/o
Japan
Japan 0.0 0.0 0.0 0.0 0.0 0.0
Mexico 0.0 0.0 0.0 0.0 3.0 1.0
U.S. 0.0 0.0 0.0 0.0 0.0 0.0
Constraints
Capacity Demand
Plant
Total
Capacity
Highcal Relax
Demand
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 4 11 11 Highcal 0.0 0.0 0.0 0.0 0.0 0.0
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
BioPharma Inc.
Regional Sales and Plant Capacities
Plant Capacity
Highcal Relax
Latin
America
7.0 7.0
Brazil 18.0
Europe 15.0 12.0 Germany 45.0
Asia w/o
Japan
5.0 3.0
India 18.0
Japan 7.0 8.0 Japan 10.0
Mexico 3.0 3.0 Mexico 30.0
U.S. 18.0 17.0 U.S. 22.0
Calculation of Fixed and Variable Costs for Specified Year
Exchange Rates Year 2009
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
2009
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Productio
n Cost
($/kg)
Brazil
20.0 5.0 5.0 3.6 5.1 4.6 6.6
Brazilian
Real
1.99 Brazil
21.6 5.4 5.4 3.9 5.5 5.0 7.1
Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0 Euro 0.72 Germany 46.9 13.5 13.5 4.1 6.3 5.2 7.3
India
14.0 3.0 3.0 3.6 4.5 4.5 6.0
Indian
Rupee
48.42 India
16.9 3.6 3.6 4.3 5.4 5.4 7.2
Japan
13.0 4.0 4.0 3.9 6.0 5.1 7.0
Japanese
Yen
93.58 Japan
13.6 4.2 4.2 4.1 6.3 5.3 7.3
Mexico
30.0 6.0 6.0 3.6 5.0 4.6 6.5
Mexican
Peso
13.48 Mexico
28.4 5.7 5.7 3.4 4.7 4.4 6.1
U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
U.S.
Dollar
1.00 U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
Transportation Costs ($ / kg) Variable Material, Production and Transportation Cost for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0.20 0.45 0.50 0.50 0.40 0.45 Brazil 9.60 9.85 9.90 9.90 9.80 9.85
Germany 0.45 0.20 0.35 0.40 0.30 0.30 Germany 10.76 10.51 10.66 10.71 10.61 10.61
India 0.50 0.35 0.20 0.30 0.50 0.45 India 10.28 10.13 9.98 10.08 10.28 10.23
Japan 0.50 0.40 0.30 0.10 0.45 0.45 Japan 10.82 10.72 10.62 10.42 10.77 10.77
Mexico 0.40 0.30 0.50 0.45 0.20 0.25 Mexico 8.53 8.43 8.63 8.58 8.33 8.38
U.S. 0.45 0.30 0.45 0.45 0.25 0.20 U.S. 9.05 8.90 9.05 9.05 8.85 8.80
Exchange Rate History Variable Material, Production and Transportation Cost for Relax
2013
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Real
Brazilian
12.30 12.55 12.60 12.60 12.50 12.55
Indian
Rupee
13.17 13.02 12.87 12.97 13.17 13.12
Japanese
Yen
13.12 13.02 12.92 12.72 13.07 13.07
Mexican
Peso
10.90 10.80 11.00 10.95 10.70 10.75
U.S.
Dollar
11.45 11.30 11.45 11.45 11.25 11.20
Import Duties (%)
Latin
America
Asia w/o
Japan
Market
Fixed and Variable Production Costs by Plant at 2013 Exchange Rates
Enter the year you want costs calculated for in cell E1
and click on the “Calculate Costs” button
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
Fixed and Variable Costs at Current Exchange Rates
Year 2009
Plant
Plant Fixed
Cost
(Million $)
Highcal
Fixed
Cost
(Million $)
Relax
Fixed
Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
Brazil 21.6 5.4 5.4 3.9 5.5 5.0 7.1
Germany 46.9 13.5 13.5 4.1 6.3 5.2 7.3
India 16.9 3.6 3.6 4.3 5.4 5.4 7.2
Japan 13.6 4.2 4.2 4.1 6.3 5.3 7.3
Mexico 28.4 5.7 5.7 3.4 4.7 4.4 6.1
U.S. 23.0 5.0 5.0 3.6 5.0 4.5 6.5
Variable Material, Production and Transportation Cost for Highcal Total Variable Cost Including Duties for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S. From / To Latin America Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 9.6 9.8 9.9 9.9 9.8 9.8 Brazil 9.6 10.1 12.6 10.5 13.2 10.2
Germany 10.8 10.5 10.7 10.7 10.6 10.6 Germany 14.0 10.5 13.5 11.4 14.3 11.0
India 10.3 10.1 10.0 10.1 10.3 10.2 India 13.4 10.4 10.0 10.7 13.9 10.6
Japan 10.8 10.7 10.6 10.4 10.8 10.8 Japan 14.1 11.0 13.5 10.4 14.5 11.2
Mexico 8.5 8.4 8.6 8.6 8.3 8.4 Mexico 11.1 8.7 11.0 9.1 8.3 8.7
U.S. 9.1 8.9 9.1 9.1 8.9 8.8 U.S. 11.8 9.2 11.5 9.6 11.9 8.8
Variable Material, Production and Transportation Cost for Relax Total Variable Cost Including Duties for Relax
From / To
Latin
America
Asia w/o
Japan
Asia w/o
Japan
U.S. 11.5 11.3 11.5 11.5 11.3 11.2 U.S. 14.9 11.6 14.5 12.1 15.2 11.2
Decision Variables
Plants Open / Shut, Lines On / Off Highcal Quantity Shipped From / To
Plant
(1) / Shut
(0)
On (1) /
Off (0)
(1) / Off
(0)
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
India 1.0 1.0 1.0 India 0.0 3.0 5.0 7.0 0.0 0.0
Japan 0.0 0.0 0.0 Japan 0.0 0.0 0.0 0.0 0.0 0.0
Mexico 1.0 1.0 1.0 Mexico 0.0 4.0 0.0 0.0 3.0 0.0
U.S. 1.0 1.0 0.0 U.S. 0.0 4.0 0.0 0.0 0.0 18.0
Total Cost = 1,313.76$ Relax Quantity Shipped From / To
Latin
America
Asia w/o
Japan
Japan 0.0 0.0 0.0 0.0 0.0 0.0
Mexico 0.0 0.0 0.0 3.0 3.0 17.0
U.S. 0.0 0.0 0.0 0.0 0.0 0.0
Constraints
Capacity Demand
Plant
Total Capacity
Highcal Relax
Demand
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0 7 11 Highcal 0.0 0.0 0.0 0.0 0.0 0.0
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
BioPharma Inc.
Regional Sales and Plant Capacities
Plant Capacity
Highcal Relax
Latin
America
7.0 7.0
Brazil 18.0
Europe 15.0 12.0 Germany 45.0
Asia w/o
Japan
5.0 3.0
India 18.0
Japan 7.0 8.0 Japan 10.0
Mexico 3.0 3.0 Mexico 30.0
U.S. 18.0 17.0 U.S. 22.0
Calculation of Fixed and Variable Costs for Specified Year
Exchange Rates Year 2008
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
2008
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Productio
n Cost
($/kg)
Brazil
20.0 5.0 5.0 3.6 5.1 4.6 6.6
Brazilian
Real
1.83 Brazil
23.5 5.9 5.9 4.2 6.0 5.4 7.8
Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0 Euro 0.68 Germany 49.6 14.3 14.3 4.3 6.6 5.5 7.7
India
14.0 3.0 3.0 3.6 4.5 4.5 6.0
Indian
Rupee
43.62 India
18.8 4.0 4.0 4.8 6.0 6.0 8.0
Japan
13.0 4.0 4.0 3.9 6.0 5.1 7.0
Japanese
Yen
103.42 Japan
12.3 3.8 3.8 3.7 5.7 4.8 6.6
Mexico
30.0 6.0 6.0 3.6 5.0 4.6 6.5
Mexican
Peso
11.13 Mexico
34.4 6.9 6.9 4.1 5.7 5.3 7.4
U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
U.S.
Dollar
1.00 U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
Transportation Costs ($ / kg) Variable Material, Production and Transportation Cost for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0.20 0.45 0.50 0.50 0.40 0.45 Brazil 10.42 10.67 10.72 10.72 10.62 10.67
Germany 0.45 0.20 0.35 0.40 0.30 0.30 Germany 11.37 11.12 11.27 11.32 11.22 11.22
India 0.50 0.35 0.20 0.30 0.50 0.45 India 11.35 11.20 11.05 11.15 11.35 11.30
Japan 0.50 0.40 0.30 0.10 0.45 0.45 Japan 9.84 9.74 9.64 9.44 9.79 9.79
Mexico 0.40 0.30 0.50 0.45 0.20 0.25 Mexico 10.25 10.15 10.35 10.30 10.05 10.10
U.S. 0.45 0.30 0.45 0.45 0.25 0.20 U.S. 9.05 8.90 9.05 9.05 8.85 8.80
Exchange Rate History Variable Material, Production and Transportation Cost for Relax
2013
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Real
Brazilian
13.36 13.61 13.66 13.66 13.56 13.61
Indian
Rupee
14.57 14.42 14.27 14.37 14.57 14.52
Japanese
Yen
11.92 11.82 11.72 11.52 11.87 11.87
Mexican
Peso
13.12 13.02 13.22 13.17 12.92 12.97
U.S.
Dollar
11.45 11.30 11.45 11.45 11.25 11.20
Import Duties (%)
Latin
America
Asia w/o
Japan
Market
Fixed and Variable Production Costs by Plant at 2013 Exchange Rates
Enter the year you want costs calculated for in cell E1
and click on the “Calculate Costs” button
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
Fixed and Variable Costs at Current Exchange Rates
Year 2008
Plant
Plant Fixed
Cost
(Million $)
Highcal
Fixed
Cost
(Million $)
Relax
Fixed
Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
Brazil 23.5 5.9 5.9 4.2 6.0 5.4 7.8
Germany 49.6 14.3 14.3 4.3 6.6 5.5 7.7
India 18.8 4.0 4.0 4.8 6.0 6.0 8.0
Japan 12.3 3.8 3.8 3.7 5.7 4.8 6.6
Mexico 34.4 6.9 6.9 4.1 5.7 5.3 7.4
U.S. 23.0 5.0 5.0 3.6 5.0 4.5 6.5
Variable Material, Production and Transportation Cost for Highcal Total Variable Cost Including Duties for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S. From / To Latin America Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 10.4 10.7 10.7 10.7 10.6 10.7 Brazil 10.4 11.0 13.6 11.4 14.3 11.1
India 11.4 11.2 11.1 11.2 11.4 11.3 India 14.8 11.5 11.1 11.8 15.3 11.8
Japan 9.8 9.7 9.6 9.4 9.8 9.8 Japan 12.8 10.0 12.2 9.4 13.2 10.2
Mexico 10.3 10.2 10.4 10.3 10.1 10.1 Mexico 13.3 10.5 13.1 10.9 10.1 10.5
U.S. 9.1 8.9 9.1 9.1 8.9 8.8 U.S. 11.8 9.2 11.5 9.6 11.9 8.8
Variable Material, Production and Transportation Cost for Relax Total Variable Cost Including Duties for Relax
From / To
Latin
America
Asia w/o
Japan
Asia w/o
Japan
U.S. 11.5 11.3 11.5 11.5 11.3 11.2 U.S. 14.9 11.6 14.5 12.1 15.2 11.2
Decision Variables
Plants Open / Shut, Lines On / Off Highcal Quantity Shipped From / To
Plant
(1) / Shut
(0)
On (1) /
Off (0)
(1) / Off
(0)
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
India 1.0 1.0 1.0 India 0.0 0.0 5.0 5.0 0.0 0.0
Japan 1.0 0.0 1.0 Japan 0.0 0.0 0.0 0.0 0.0 0.0
Mexico 1.0 1.0 1.0 Mexico 0.0 13.0 0.0 0.0 3.0 11.0
U.S. 1.0 1.0 1.0 U.S. 0.0 0.0 0.0 0.0 0.0 7.0
Total Cost = 1,423.16$ Relax Quantity Shipped From / To
Latin
America
Asia w/o
Japan
Japan 0.0 0.0 0.0 8.0 0.0 2.0
Mexico 0.0 0.0 0.0 0.0 3.0 0.0
U.S. 0.0 0.0 0.0 0.0 0.0 15.0
Constraints
Capacity Demand
Plant
Total Capacity
Highcal Relax
Demand
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
BioPharma Inc.
Regional Sales and Plant Capacities
Plant Capacity
Highcal Relax
Latin
America
7.0 7.0
Brazil 18.0
Europe 15.0 12.0 Germany 45.0
Asia w/o
Japan
5.0 3.0
India 18.0
Japan 7.0 8.0 Japan 10.0
Mexico 3.0 3.0 Mexico 30.0
U.S. 18.0 17.0 U.S. 22.0
Calculation of Fixed and Variable Costs for Specified Year
Exchange Rates Year 2007
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
2007
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Productio
n Cost
($/kg)
Brazil
20.0 5.0 5.0 3.6 5.1 4.6 6.6
Brazilian
Real
1.94 Brazil
22.2 5.5 5.5 4.0 5.7 5.1 7.3
Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0 Euro 0.73 Germany 46.2 13.4 13.4 4.0 6.2 5.1 7.2
India
14.0 3.0 3.0 3.6 4.5 4.5 6.0
Indian
Rupee
41.34 India
19.8 4.2 4.2 5.1 6.4 6.4 8.5
Japan
13.0 4.0 4.0 3.9 6.0 5.1 7.0
Japanese
Yen
117.77 Japan
10.8 3.3 3.3 3.2 5.0 4.2 5.8
Mexico
30.0 6.0 6.0 3.6 5.0 4.6 6.5
Mexican
Peso
10.92 Mexico
35.0 7.0 7.0 4.2 5.8 5.4 7.6
U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
U.S.
Dollar
1.00 U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
Transportation Costs ($ / kg) Variable Material, Production and Transportation Cost for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0.20 0.45 0.50 0.50 0.40 0.45 Brazil 9.84 10.09 10.14 10.14 10.04 10.09
Germany 0.45 0.20 0.35 0.40 0.30 0.30 Germany 10.62 10.37 10.52 10.57 10.47 10.47
India 0.50 0.35 0.20 0.30 0.50 0.45 India 11.95 11.80 11.65 11.75 11.95 11.90
Japan 0.50 0.40 0.30 0.10 0.45 0.45 Japan 8.70 8.60 8.50 8.30 8.65 8.65
Mexico 0.40 0.30 0.50 0.45 0.20 0.25 Mexico 10.44 10.34 10.54 10.49 10.24 10.29
U.S. 0.45 0.30 0.45 0.45 0.25 0.20 U.S. 9.05 8.90 9.05 9.05 8.85 8.80
Exchange Rate History Variable Material, Production and Transportation Cost for Relax
2013
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazilian
Real
Indian
Rupee
Japanese
Yen
Mexican
Peso
U.S.
Dollar
Import Duties (%)
Latin
America
Asia w/o
Japan
Market
Fixed and Variable Production Costs by Plant at 2013 Exchange Rates
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
Fixed and Variable Costs at Current Exchange Rates
Year 2007
Plant
Plant Fixed
Cost
(Million $)
Highcal
Fixed
Cost
(Million $)
Relax
Fixed
Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
Brazil 22.2 5.5 5.5 4.0 5.7 5.1 7.3
Germany 46.2 13.4 13.4 4.0 6.2 5.1 7.2
India 19.8 4.2 4.2 5.1 6.4 6.4 8.5
Japan 10.8 3.3 3.3 3.2 5.0 4.2 5.8
Mexico 35.0 7.0 7.0 4.2 5.8 5.4 7.6
U.S. 23.0 5.0 5.0 3.6 5.0 4.5 6.5
Variable Material, Production and Transportation Cost for Highcal Total Variable Cost Including Duties for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S. From / To Latin America Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 9.8 10.1 10.1 10.1 10.0 10.1 Brazil 9.8 10.4 12.9 10.8 13.6 10.5
Germany 10.6 10.4 10.5 10.6 10.5 10.5 Germany 13.8 10.4 13.4 11.2 14.1 10.9
India 12.0 11.8 11.7 11.8 12.0 11.9 India 15.5 12.2 11.7 12.5 16.1 12.4
Japan 8.7 8.6 8.5 8.3 8.7 8.7 Japan 11.3 8.9 10.8 8.3 11.7 9.0
Mexico 10.4 10.3 10.5 10.5 10.2 10.3 Mexico 13.6 10.7 13.4 11.1 10.2 10.7
U.S. 9.1 8.9 9.1 9.1 8.9 8.8 U.S. 11.8 9.2 11.5 9.6 11.9 8.8
Variable Material, Production and Transportation Cost for Relax Total Variable Cost Including Duties for Relax
From / To
Latin
America
Asia w/o
Japan
Asia w/o
Japan
U.S. 11.5 11.3 11.5 11.5 11.3 11.2 U.S. 14.9 11.6 14.5 12.1 15.2 11.2
Decision Variables
Plants Open / Shut, Lines On / Off Highcal Quantity Shipped From / To
Plant
(1) / Shut
(0)
On (1) /
Off (0)
(1) / Off
(0)
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
India 0.0 0.0 0.0 India 0.0 0.0 0.0 0.0 0.0 0.0
Japan 1.0 0.0 1.0 Japan 0.0 0.0 0.0 0.0 0.0 0.0
Mexico 1.0 1.0 1.0 Mexico 0.0 15.0 0.0 4.0 3.0 0.0
U.S. 1.0 1.0 0.0 U.S. 0.0 0.0 4.0 0.0 0.0 18.0
Latin
America
Asia w/o
Japan
Japan 0.0 0.0 2.0 8.0 0.0 0.0
Mexico 0.0 0.0 0.0 0.0 3.0 0.0
U.S. 0.0 0.0 0.0 0.0 0.0 0.0
Constraints
Capacity Demand
Plant
Total
Capacity
Highcal Relax
Demand
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
For each year the inputs are on the corresponding input sheet and the solution
is obtained using Solver on the corresponding solution sheet.
BioPharma Inc. Year (2004–2009) 2009
Regional Sales and Plant Capacities
Plant Capacity
Highcal Relax
Latin
America
7.0 7.0
Brazil 18.0
Europe 15.0 12.0 Germany 45.0
Asia w/o
Japan
5.0 3.0
India 18.0
Japan 7.0 8.0 Japan 10.0
Mexico 3.0 3.0 Mexico 30.0
U.S. 18.0 17.0 U.S. 22.0
Calculation of Fixed and Variable Costs for Specified Year
Exchange Rates Year 2006
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
2006
Plant
Plant
Fixed Cost
(Million $)
Highcal
Fixed Cost
(Million $)
Relax
Fixed Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Productio
n Cost
($/kg)
Brazil
20.0 5.0 5.0 3.6 5.1 4.6 6.6
Brazilian
Real
2.17 Brazil
19.8 5.0 5.0 3.6 5.1 4.6 6.5
Germany 45.0 13.0 13.0 3.9 6.0 5.0 7.0 Euro 0.80 Germany 42.2 12.2 12.2 3.7 5.6 4.7 6.6
India
14.0 3.0 3.0 3.6 4.5 4.5 6.0
Indian
Rupee
45.18 India
18.1 3.9 3.9 4.7 5.8 5.8 7.8
Japan
13.0 4.0 4.0 3.9 6.0 5.1 7.0
Japanese
Yen
116.29 Japan
10.9 3.4 3.4 3.3 5.0 4.3 5.9
Mexico
30.0 6.0 6.0 3.6 5.0 4.6 6.5
Mexican
Peso
10.89 Mexico
35.1 7.0 7.0 4.2 5.9 5.4 7.6
U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
U.S.
Dollar
1.00 U.S.
23.0 5.0 5.0 3.6 5.0 4.5 6.5
Transportation Costs ($ / kg) Variable Material, Production and Transportation Cost for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0.20 0.45 0.50 0.50 0.40 0.45 Brazil 8.82 9.07 9.12 9.12 9.02 9.07
Germany 0.45 0.20 0.35 0.40 0.30 0.30 Germany 9.73 9.48 9.63 9.68 9.58 9.58
India 0.50 0.35 0.20 0.30 0.50 0.45 India 10.98 10.83 10.68 10.78 10.98 10.93
Japan 0.50 0.40 0.30 0.10 0.45 0.45 Japan 8.81 8.71 8.61 8.41 8.76 8.76
Mexico 0.40 0.30 0.50 0.45 0.20 0.25 Mexico 10.47 10.37 10.57 10.52 10.27 10.32
U.S. 0.45 0.30 0.45 0.45 0.25 0.20 U.S. 9.05 8.90 9.05 9.05 8.85 8.80
Exchange Rate History Variable Material, Production and Transportation Cost for Relax
2013
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Real
Brazilian
11.30 11.55 11.60 11.60 11.50 11.55
Indian
Rupee
14.08 13.93 13.78 13.88 14.08 14.03
Japanese
Yen
10.65 10.55 10.45 10.25 10.60 10.60
Mexican
Peso
13.40 13.30 13.50 13.45 13.20 13.25
U.S.
Dollar
11.45 11.30 11.45 11.45 11.25 11.20
Import Duties (%)
Latin
America
Asia w/o
Japan
Market
Fixed and Variable Production Costs by Plant at 2013 Exchange Rates
Enter the year you want costs calculated for in cell E1
and click on the “Calculate Costs” button
Fixed and Variable Costs at Current Exchange Rates
Year 2006
Plant
Plant Fixed
Cost
(Million $)
Highcal
Fixed
Cost
(Million $)
Relax
Fixed
Cost
(Million $)
Highcal
Raw
Material
Cost
($/kg)
Highcal
Production
Cost ($/kg)
Relax
Raw
Material
Cost
($/kg)
Relax
Production
Cost ($/kg)
Brazil 19.8 5.0 5.0 3.6 5.1 4.6 6.5
Germany 42.2 12.2 12.2 3.7 5.6 4.7 6.6
India 18.1 3.9 3.9 4.7 5.8 5.8 7.8
Japan 10.9 3.4 3.4 3.3 5.0 4.3 5.9
Mexico 35.1 7.0 7.0 4.2 5.9 5.4 7.6
U.S. 23.0 5.0 5.0 3.6 5.0 4.5 6.5
Variable Material, Production and Transportation Cost for Highcal Total Variable Cost Including Duties for Highcal
From / To
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S. From / To Latin America Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 8.8 9.1 9.1 9.1 9.0 9.1 Brazil 8.8 9.3 11.6 9.7 12.2 9.4
Germany 9.7 9.5 9.6 9.7 9.6 9.6 Germany 12.7 9.5 12.2 10.3 12.9 10.0
India 11.0 10.8 10.7 10.8 11.0 10.9 India 14.3 11.2 10.7 11.4 14.8 11.4
Japan 8.8 8.7 8.6 8.4 8.8 8.8 Japan 11.4 9.0 10.9 8.4 11.8 9.1
Mexico 10.5 10.4 10.6 10.5 10.3 10.3 Mexico 13.6 10.7 13.4 11.2 10.3 10.7
U.S. 9.1 8.9 9.1 9.1 8.9 8.8 U.S. 11.8 9.2 11.5 9.6 11.9 8.8
Variable Material, Production and Transportation Cost for Relax Total Variable Cost Including Duties for Relax
From / To
Latin
America
Asia w/o
Japan
Asia w/o
Japan
U.S. 11.5 11.3 11.5 11.5 11.3 11.2 U.S. 14.9 11.6 14.5 12.1 15.2 11.2
Decision Variables
Plant
(1) / Shut
(0)
On (1) /
Off (0)
(1) / Off
(0)
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
India 1.0 1.0 1.0 India 0.0 0.0 5.0 2.0 0.0 0.0
Japan 1.0 0.0 1.0 Japan 0.0 0.0 0.0 0.0 0.0 0.0
Mexico 0.0 0.0 0.0 Mexico 0.0 0.0 0.0 0.0 0.0 0.0
U.S. 1.0 1.0 0.0 U.S. 0.0 0.0 0.0 1.0 3.0 18.0
Total cost = 1,286.90$ Relax Quantity Shipped From / To
America
Japan
Latin
Asia w/o
Japan 0.0 0.0 0.0 8.0 2.0 0.0
Mexico 0.0 0.0 0.0 0.0 0.0 0.0
U.S. 0.0 0.0 0.0 0.0 0.0 0.0
Constraints
Capacity Demand
Plant
Total Capacity
Highcal Relax
Demand
Latin
America
Europe
Asia w/o
Japan
Japan Mexico U.S.
Brazil 0 7 11 Highcal 0.0 0.0 0.0 0.0 0.0 0.0
Germany 0 30 15 Relax 0.0 0.0 0.0 0.0 0.0 0.0