Finite Math - Problem with Excel Spreadsheet Attachment

  

At the start of the year, a company wants to invest excess cash in
one-month, three-month and six-month Certificates of Deposit (CD’s). 
(Purchase price and yields for the different CD’s appear in the table
below). The company is somewhat conservative, however, and wants to make
sure that it has a safety margin of cash-on-hand each month; i.e.,
cash-on-hand left over from the previous month / available at the
outset, plus principal and interest from CDs that have become due, minus
investments made at the start of the month, must be no less than the
month's safety margin.

The size of the monthly safety margins are as follows:

Jan      $175,000

Feb       $90,000

Mar      $80,000

Apr      $180,000

May     $150,000

Jun       $85,000

How should the company maximize total earned interest?

Given that:

·         Three-month CDs can only be bought at the start of
January and April; six-month CDs can only be bought in January.

·         Initial cash available is $400,000. 

                                    
Yield    Term   Price                Purchase at
start of:  

1-mo CDs:      1.0%     1 mo.  $2,000            
January thru June    

3-mo CDs:      4.0%    3          $3,000
           January and April     

6-mo CDs:      9.0%    6          $5,000
           January         

           

What are the decision variables and the objective function?

 

Define cash-flow constraints for each month.

 

a. Solve using Lindo or Excel. (IMHO, this problem lends itself to a
spreadsheet formulation, and the latter is definitely the way to go) (25
points)

 

b. Modify  your formulation to take into account the added requirement
that one may not purchase fractional amounts of  CD’s (5 points).

 

Please submit two separate spreadsheets / Lindo formulations.

 

 

Some suggestions:

 

a. variable definitions

 

You might want to define:

 

·                 variables* for each possible
investment, each month

 

·                 variables for the total invested each
month

 

·                 a variable for total interest earned
(useful for the objective function)

 

·                 for each month, a variable that
represents the amount of cash on hand during the month, after making the
investments at the start of the month, but before realizing the income
from investments that come due at the end of the month. (This must
exceed the required safety margin.)

 

·                 a variable for each month’s
required safety margin

 

·                 for each month, a variable that
represents the added income that’s available to us at the start of
each month from investments that have come due at the end of the
previous month (= principle + interest)

 

·                 for each month, a variable that
represents the cash on hand at the start of the month

 

*   for ‘variables’ in Lindo, substitute ‘cell’ or column of
cell entries’ in Excel

 

b. constraint definitions

 

You’ll need to define a series of constraints (and / or inter-cell
relationships in Excel) that relate:

 

·                 The cash on hand available at the
start of a given month to the amount available during the previous month

 

·                 The cash on hand during the month
with the cash on hand at the start of the month

 

·                 The cash on hand during the month and
the month’s safety margin

 

 c.  Since the problem involves deciding how much money to invest for
one-month, three-month, and six-month periods, you CANNOT consider it as
involving six separate monthly decisions.

 

Hope this helps!

 

 

 



Page PAGE 1