A large toy discount store has slow sales in the summer months that increase drastically and rise to a peak in 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 inventory during the months when revenue is 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	Revenues (in thousands)	Liabilities (in thousands)
July	$20	$60
August	$30	$60
September	$40	$80
October	$50	$30
November	$80	$30
December	$100	$20

At the beginning of July, the store can take out a 6-month loan that carries an 11% interest rate (for the entire period) and that must be paid on January 1. The loan cannot be paid early and interests are not paid monthly, instead they are all paid on January 2. The store can also borrow money monthly at a rate of 5% per month. Money borrowed on a monthly basis must be paid back at the beginning of the next month, along with the interests. For example, if $100 are borrowed on July 1 then $105 must be paid on August 1. Money borrowed on December 1 must be paid on January 1. The store wants to borrow money to meet its cash flow obligations while minimizing its cost of borrowing. Note that minimizing the cost of borrowing is the same as maximizing the store’s cash position on January 1. Also, note that positive cash balances from one month should be carried to the next month. No negative cash balances are allowed.

1. What type of optimization problem is this? Options: Inventory, Cash flow, Transshipment, Assignment, Binary
2. Consider a borrowing plan of monthly loans (in thousands of dollars) taken on the first day of each of the following months: $40 in July, $72 in August, $116 in September, $102 in October, and $57 in November. Is this plan feasible? Yes or No
3. Consider a borrowing plan of monthly loans (in thousands of dollars) taken on the first day of each of the following months: $40 in July, $72 in August, $116 in September, $102 in October, and $57 in November. What is the cash position on January 1 for this plan?
4. Create a model to find a plan that maximizes the store’s cash position on January 1. What is the optimal borrowing plan?

• Six-month loan:
• July loan:
• August loan:
• September loan:
• October loan:
• November loan:
• December loan:

1. What is the cash position on January 1 for the optimal borrowing plan?

SHOW EXCEL ANSWER ONLY - show steps and equations used in solver optimization. Use a template format provided

 

A B 1 Evergalde Cash Flow Management Problem 2 E F 3 LT rate 4. ST rate 11% 5% 5 6 Start balance 1 7 Minimum balance 0.5 (all amounts in millions) 8 9 Cash LT ST LT 10 Month flow loan loans interest ST interest LT ST Ending payment payment balance 11 July -8 4.65 2.85 0.50 12 August 5.28 -0.51 -0.14 -2.85 2.28 13 September 14 October 15 November 16 December 4363 -4 9.88 -0.51 -0.26 -5.28 2.10 7.81 -0.51 -0.49 -9.88 2.02 2.59 -0.51 -0.39 -7.81 1.90 0.00 -0.51 -0.13 -2.59 1.67 17 -0.51 #REF! -4.65 #REF! #REF! 18 10