Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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 Star 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 S/ton in the next twelve months. Table 1. Estimated market price for sale or purchase in 5 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. Create rows for beginning inventory, purchases, sales, ending inventory and average inventory. 2. 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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started