Assignment - LP problems
The data for all the problems in this HW are included in the LP_problems_xlsx spreadsheet
Problem 1: Cash Planning
A startup investment project needs money to cover its cash flow needs.
At the end of the 5-month period all the cash that is left will be turned over to investors.
The company financing options are given in the table below.
|
|
Short-Term Debt
|
Long-Term Debt
|
|
Maximum amount ($000s)
|
300
|
400
|
|
Terms
|
Can be taken out at the beginning
of any month.
Must be repaid with interest at
the beginning of the following
month
|
Must be taken out Jan. 1.
Repaid Apr. 1.
No early repayment.
Monthly interest payments due on
Feb. 1, Mar 1, Apr 1.
|
|
Interest rate per month
|
1.2%
|
1%
|
Assume: all transactions happen at the beginning of the month.
Money market: if there is money left after transactions, we put it in the money market and get it back at the beginning of the next month plus interest. Money-market interest rate is 0.7% per month.
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.
3. How is this problem similar to the Iceberg for Kuwait model?
Problem 2: 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. 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.
Problem 3: 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.
Attachment:- Assignment Files.rar