OMGT 6613 Management Science

Exercise #3-Summer

Linear and Integer Programming

Download the Excel file titled Exercise 3 for this assignment. The file contains several tabs with the data required for the assignment.

Problem 1 (10 points)

An agriculture firm owns 2,000 acres of farmland. They are trying to determine the amount of land to allocate to 4 separate crops with the following characteristics so as to maximize profit.

The company has a total of 4,000 labor hours per month to allocate to the farms.

• Formulate this problem as a Linear Program.
• Solve the problem to find the optimal solution.
• Record the optimal decision and the optimal objective value in the template.
• Generate an Answer Report and paste it into the template.
• Generate a Sensitivity Report and past it into the template.
• Assume up to 500 additional acres can be leased for the season at a cost of $100 per acre. How many acres should the firm lease?

Problem 2 (10 points)

A money manager for a university endowment has received a $10 million donation to invest. The intent is to establish a cash flow stream for the next 25 years. The goal is to have an annual payout that begins at $50,000 and grows by 3% per year. The manager has identified a list of 50 different bonds to invest in. The manager’s goal is to determine which bonds to purchase now so as to maximize the total discounted return while guaranteeing the bond’s pay out the required annual funds. The manger has specified a limit of 2,500 units of any individual bond.

The data on tab 3 lists the 50 bonds available for purchase annual payout for each of the next 25 years. The data includes the payout each year based on the bond’s coupon and maturity date. The investment column contains a place to identify the number of bonds purchased of each type.

First, complete the model.

• Calculate the cash flow in each year as the (sum) product of the Investment column and the appropriate bond cash flow column.
• Calculate the cash flow required based on the Initial Cash Required and the Annual Increase
• Calculate the total Disc Cash Flow as the product of the Cash Flow and Discount Factor rows.
• Specify the Max Per Bond setting as the maximum number of units that can be invested in a single bond.

Now specify the optimization model using Analytic Solver.

• Define the Investment column as the decision variables.
• Define the Disc Cash Flow as the Objective. (max-normal)
• Establish the constraint that the Cash Flow must exceed the Cash Required field.
• Establish a constraint that limits the Investment to be less than the Available Funds.
• Constrain the Investments to be non-negative integers and less than the Max per Bond
• We wish to solve the model using the Simplex method to guaranteed optimality.

Now solve the model.

• Create a graph of the Cash Flow per year and paste it into your template.
• What is the total Disc Cash Flow?
• How many bonds were invested in?
• Filter your data to show the bonds that were selected, including the number purchased and the Bond characteristics. Paste this data into your template.

Problem 3 (5 points extra credit)

In this problem we will further investigate the Bond Investment problem from problem 3 by performing a parameter analysis.

First, we will look at how decreasing risk through diversification impacts the total return.

We will do this by evaluating different settings on the Max per Bond setting.

• Convert the Max per Bond cell into a parameter that varies from 1,500 to 5,000
• Generate a Parameter report with 8 steps that tracks the Discounted Cash Flow as an output.
• Paste the report into your template.

Now we will look at how smoothing out the cash flow effects the total return.

• Keep the Max per Bond setting as a parameter.
• Convert the Initial Cash Required setting to a parameter that varies from 50,000 to 75,000.
• Generate a parameter report that includes 8 major axis points, and 11 minor axis points, and tracks the Disc Cash flow. Select Vary Two Selected Parameters Independently. Paste the report into your template.
• Generate a parameter graph for the same settings and paste the graph into the template.

Consider this analysis and

• Comment on how you think the time to solve would change if we increased the number of bonds to choose from? How could the analyst address this issue?
• Comment on what observations you can make from this sensitivity analysis.