Question

Toys is a large discount toy store in Valley Wood Mall. The store typically has slow sales in the summer months that increase dramatically and rise to a peak at Christmas. During the summer and fall, the store must build up its inventory to have enough stock for the Christmas season. To purchase and build up its stock during the months when its revenues are low, the store borrows money. Following is the store’s projected revenue and liabilities schedule for July through December (where revenues are received and bills are paid at the first of each month): Month Revenue Liabilities July 20,000.00 60,000.00 August 30,000.00 60,000.00 September 40,000.00 80,000.00 October 50,000.00 30,000.00 November December 80,000.00 100,000.00 30,000.00 20,000.00 At the beginning of July, the store can take out a 6-month loan that carries an 11% interest rate and must be paid back at the end of December. The store cannot reduce its interest payment by paying back the loan early. The store can also borrow money monthly at a rate of 5% interest per month. Money borrowed on a monthly basis must be paid back at the beginning of the next month. The store wants to borrow enough money to meet its cash flow needs while minimizing its cost of borrowing. Formulate a linear programming model for this problem.

Solve this model on EXCEL SPREADSHEET

please provide formulation also

Thanks!

Answer 1

solution:

Linear Programming model is as follows

Decision variables: X = Six-month loan amount taken at the beginning of July.

Yi = Monthly borrowing at the beginning of each month, i=1,2,3,4,5,6 for Jul, Aug, Sep, Oct, Nov, Dec.

Objective: Min 0.11X + 0.05*(Y1+Y2+Y3+Y4+Y5+Y6)

s.t.

20000 + X + Y1 - B1 = 60000, where B1 is the cash balance at the beginning of August.

B1 + 30000 + Y2 - 1.05Y1 - B2 = 60000

B2 + 40000 + Y3 - 1.05Y2 - B3 = 80000

B3 + 50000 + Y4 - 1.05Y3 - B4 = 30000

B4 + 80000 + Y5 - 1.05Y4 - B5 = 30000

B5 + 100000 + Y6 - 1.05Y5 - B6 = 20000

B6 >= 1.05Y6 + 1.11X

X, YI >= 0

Solution using Excel spreadsheet model is as follows

Optimal solution:

6-month loan at the beginning of loan = $ 70,000

Monthly loan at the beginning of September = $ 40,000

Monthly loan at the beginning of October= $ 22,000

Loans in all other months = 0

Total interest cost = $ 10,800

please upvote ,comment incase any doubt , feel free to comment ,thank you