“STAR SOYBEANS

Star Soybeans buys and sells soybeans from its office in Decatur, Georgia. It provides storage for its soybeans in a
warehouse leased. Star estimates that
the market would allow purchases of up to 1000 tons a month and sales of up to
2000 tons a month at maximum. Currently
it estimates that it will have 470 tons of in storage at the beginning of
January. The terms of the lease are the
inventory costs will be $10 per ton of average monthly inventory (average of
beginning and ending inventory) and Star is entitled to have up to 4000 tons in
storage at the end of each month.

Table 1
shows the estimates Star has made for the market price in $/ton in the next
twelve months.

Table
1. Estimated market price for sale or purchase
in $ per ton

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

$110

$125

$140

$160

$165

$180

$190

$175

$155

$135

$145

$160

Create a Solver-based spreadsheet modelto determine how many tons of soybeans should
be purchased or sold each month in order to maximize profits and solve the
model.

Comments and
Hints:

1.
Create cells
holding the specified values for inventory cost in $/ton, initial January
inventory, maximum monthly purchases, maximum monthly sales and maximum
inventory level. Use these cells in your
formulas, not constants.
2.
Create rows
for beginning inventory, purchases, sales, ending inventory and average
inventory.
3.
Create cells
for sales revenue, purchase cost, inventory cost and profit.
4.
The Solver
option, Make Unconstrained Variables Non-Negative, applies only to changing
cells. In this problem you must restrict
the ending inventory cells to non-negative values with a range constraint.STAR SOYBEANSStar
Soybeans buys and sells soybeans from its office in Decatur, Georgia. It provides storage for its soybeans in a
warehouse leased. Star estimates that
the market would allow purchases of up to 1000 tons a month and sales of up to
2000 tons a month at maximum. Currently
it estimates that it will have 470 tons of in storage at the beginning of
January. The terms of the lease are the
inventory costs will be $10 per ton of average monthly inventory (average of
beginning and ending inventory) and Star is entitled to have up to 4000 tons in
storage at the end of each month.Table 1
shows the estimates Star has made for the market price in $/ton in the next
twelve months.Table
1. Estimated market price for sale or purchase
in $ per tonJanFebMarAprMayJunJulAugSepOctNovDec$110 $125 $140 $160 $165 $180 $190 $175 $155 $135 $145 $160 Create a Solver-based spreadsheet modelto determine how many tons of soybeans should
be purchased or sold each month in order to maximize profits and solve the
model.Comments and
Hints:1.
Create cells
holding the specified values for inventory cost in $/ton, initial January
inventory, maximum monthly purchases, maximum monthly sales and maximum
inventory level. Use these cells in your
formulas, not constants.2.
Create rows
for beginning inventory, purchases, sales, ending inventory and average
inventory.3.
Create cells
for sales revenue, purchase cost, inventory cost and profit.4.
The Solver
option, Make Unconstrained Variables Non-Negative, applies only to changing
cells. In this problem you must restrict
the ending inventory cells to non-negative values with a range constraint.”