Star Soybeans buys and sells soybeans from its office in Decatur, Georgia. It pr

Question

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 model to 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.

Explanation / Answer

Given that purchase in a month may be upto 1000 tons and sales has maximum quantity of 2000tons per month. Inventory carrying cost $10/ton/month on average of beginning and ending inventory for the month.

Excel sheet is created as follows:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Beginning Inventory 470 1470 2470 3470 1980 2980 1490 0 0 0 1000 2000 Price expected 110 125 140 160 165 180 190 175 155 135 145 160 Purchases 1000 1000 1000 510 1000 510 0 0 0 1000 1000 0 Sales 0 0 0 2000 0 2000 1490 0 0 0 0 2000 Max.Purchase 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 LimitSales 1470 2470 3470 3980 2980 3490 1490 0 0 1000 2000 2000 Max.Sales 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 Ending Inventory 1470 2470 3470 1980 2980 1490 0 0 0 1000 2000 0 Const.End.Inv.>= 0 0 0 0 0 0 0 0 0 0 0 0 Max.Inventory 4000 4000 4000 4000 4000 4000 4000 4000 4000 4000 4000 4000 Average Inventory 970 1970 2970 2725 2480 2235 745 0 0 500 1500 1000 Sales revenue 0 0 0 320000 0 360000 283100 0 0 0 0 320000 1283100 Purchase cost 110000 125000 140000 81600 165000 91800 0 0 0 135000 145000 0 993400 Inventory cost 9700 19700 29700 27250 24800 22350 7450 0 0 5000 15000 10000 170950 Profit 118750

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.