HW #2: LP problems
Problem 1: Classwork
While watching the videos, follow the instructions and submit your classwork. This includes your spreadsheet solution to the following assignments:
1. Feasibility check of the Go-Green! Problem.
2. Solution to the Go-Green! Problem, using Risk Solver.
3. Sensitivity analysis report to the Go-Green! Problem.
4. Solution to the Toy Story problem.
Problem 2: Lincoln Lock Distribution Problem
Lincoln Lock Company manufactures a commercial security lock.
• Company plants are in 4 locations
• The locks are sold through wholesale distributors in 7 locations around the country.
Goal: Determine the least costly way of shipping products from plants to distributors. Data available in the Excel file for this problem:
• Costs of production at each plant
• Shipping cost for each plant-distributor combination
• Plant capacities
• Forecasted demand from each distributor for the coming year
1. Formulize the problem. Identify the objective, decisions and constraints. Should the objective be maximized or minimized?
2. Solve the problem using Solver: Build a spreadsheet model to analyze this problem. The parameters are given in the LP_problems.xlsx spreadsheet.
Hint: The final solution should result in total cost of $2,720,200.
Problem 3: Natural Gas Trading
A natural gas trading company makes profit from daily gas trading. Every morning the company extract gas from its storage, and sales it for the bid (selling) price (per cubic feet). With the available money funds the company buys gas in the afternoon for the ask (buying) price, and inject it back into its storage. Maximum storage, daily extraction and injection are given in the table below, as well as the bid and ask price for the next 10 days. Note that every day the company pays 5% daily storage fee that is calculated as the bid price value of average daily amount stored. The storage fee is due in full at the end of the 10-days period (it is calculated daily, but payed at once after 10 days). Assume that the company has no available funds to use on day 1, and no financing options.
1. Formulize the problem. Identify the objective, decisions and constraints. Should the objective be maximized or minimized?
2. Solve the problem using Solver: Build a spreadsheet model to analyze this problem. The parameters are given in the LP_problems.xlsx spreadsheet. Make sure that your spreadsheet includes calculations of how much money is available to buy natural gas on a given day, and how much inventory is available for extraction.
Hint: The final solution should result in a profit of $1,102.64.