Problem 4. Trading strategy for Commo-Trading (6 points, Excel Analytic Solver is needed) You own a commodity trading firm Commo-Trade. You buy and sell corn. You have a warehouse that can store 10,000 bushels of corn. You currently have 2,000 bushels in the warehouse. When you buy (in the middle of the month), corn will be delivered to the warehouse during the first week of the next month. When you sell (in the middle of the month), corn is shipped out one week later. All transactions are on a cash basis. It is now the end of December, you have $20,000 cash at hand for making transactions. It is also assumed that you only make buy/sell transactions (how much to buy and how much to sell) in the middle of the month. Your team has made the following predictions on the corn price ($ per bushel) for the next three months which you will reply on to make buy and sell decisions to maximize profit for Commo-Trade: Buy price Sell price Jan 15, 2021 $5.45 $6.05 Feb 15, 2021 Mar 15, 2021 $5.75 $6.00 $6.25 $6.30 a. Draw a network flow chart to describe the situation (with nodes and arcs in a graph showing when to buy, sell and hold in inventory) (1 point) b. Formulate the problem of maximizing total profit for Commo-Trade as an optimization problem. Indicate what are the variables, what are the constraints and what is the objective function. To simplify, no need to account for the time-value of money. That is, no need to do NPV calculations, simply adding profit across the three months is sufficient (2 points). c. Use Analytic Solver to determine the buy-and-sell plan (that is, when to buy / sell how much corn) that will produce the maximum total profit for Commo-Trade. What is the optimal profit? (1 point) d. Construct a table to show how the optimal profit vary with the cash available at hand for making purchases. (1 point) Maximum Profit Cash 20,000 25,000 30,000 35,000 40,000 e. What is the maximum financial interest charge that the firm is willing to pay to increase its cash amount from the current $20,000 to $22,000? (1 point) Problem 4. Trading strategy for Commo-Trading (6 points, Excel Analytic Solver is needed) You own a commodity trading firm Commo-Trade. You buy and sell corn. You have a warehouse that can store 10,000 bushels of corn. You currently have 2,000 bushels in the warehouse. When you buy (in the middle of the month), corn will be delivered to the warehouse during the first week of the next month. When you sell (in the middle of the month), corn is shipped out one week later. All transactions are on a cash basis. It is now the end of December, you have $20,000 cash at hand for making transactions. It is also assumed that you only make buy/sell transactions (how much to buy and how much to sell) in the middle of the month. Your team has made the following predictions on the corn price ($ per bushel) for the next three months which you will reply on to make buy and sell decisions to maximize profit for Commo-Trade: Buy price Sell price Jan 15, 2021 $5.45 $6.05 Feb 15, 2021 Mar 15, 2021 $5.75 $6.00 $6.25 $6.30 a. Draw a network flow chart to describe the situation (with nodes and arcs in a graph showing when to buy, sell and hold in inventory) (1 point) b. Formulate the problem of maximizing total profit for Commo-Trade as an optimization problem. Indicate what are the variables, what are the constraints and what is the objective function. To simplify, no need to account for the time-value of money. That is, no need to do NPV calculations, simply adding profit across the three months is sufficient (2 points). c. Use Analytic Solver to determine the buy-and-sell plan (that is, when to buy / sell how much corn) that will produce the maximum total profit for Commo-Trade. What is the optimal profit? (1 point) d. Construct a table to show how the optimal profit vary with the cash available at hand for making purchases. (1 point) Maximum Profit Cash 20,000 25,000 30,000 35,000 40,000 e. What is the maximum financial interest charge that the firm is willing to pay to increase its cash amount from the current $20,000 to $22,000? (1 point)
