Build a spreadsheet model to analyze this problem

Assignment Help Engineering Mathematics

Reference no: EM132133916

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

Reference no: EM132133916

Questions Cloud

Alternatives to job-based pay structure and actual pay : The traditional and most widely used approach to developing a pay structure focuses on setting pay for jobs or groups of jobs.

How can the expectations between the parties be designed : One of the most common problems a Project Manager will face with the client is with the project's scope.

What challenges will there be in researching market domain : Describe your research approach and your sources of information. What challenges will there be in researching this market domain?

Firm save annually in ordering and carrying costs : How much could the firm save annually in ordering and carrying costs by using the EOQ?

Build a spreadsheet model to analyze this problem : Lincoln Lock Distribution Problem - Solve the problem using Solver: Build a spreadsheet model to analyze this problem

Identify potential risks-including risks related to problems : Identify sixthree potential risks, including risks related to the problems described in the preceding paragraph.

Identify three leadership traits from the trait theory : Identify three leadership traits from the Trait Theory you feel you do or do not possess.

Asking job incumbent to write these statements : How should task statements be written, and what sorts of problems might you encounter in asking a job incumbent to write these statements?

Veterinary clinics found that having diagnostic equipment : Veterinary clinics found that having diagnostic equipment in-house led to a lower volume of testing.

User Account

All Pages