A company is planning its cash flow for the next 6 months and expects to have a cash shortage in some months. The table
Posted: Sun May 29, 2022 7:20 pm
A company is planning its cash flow for the next 6 months and
expects to have a cash shortage in some months. The table on the
worksheet for Problem 1 shows the expected cash receipts and
payments due over the planning period. The company has two options
to meet its cash shortages:
1. Borrow money for one month at a time at an interest rate of
0.75% per month. Any money borrowed is available to meet cash
requirements in the month it is borrowed but must be paid back with
interest in the next month.
2. Borrow a single lump sum at the beginning of the first month
and repay the entire amount at the end of month 6.
The annual interest rate is 6%, or 0.5% per month.
In this case, interest must be paid each month (but no part of
the principal is paid until the end of month 6).
The company currently has $50,000 on hand and would like to
maintain an ending cash balance each month of at least $25,000.
Create an LP model in Excel to track monthly cash flow and
determine the borrowing and repayment plan that minimizes the total
cost of financing the loans. Upload the Excel file showing your
model and solution.
expects to have a cash shortage in some months. The table on the
worksheet for Problem 1 shows the expected cash receipts and
payments due over the planning period. The company has two options
to meet its cash shortages:
1. Borrow money for one month at a time at an interest rate of
0.75% per month. Any money borrowed is available to meet cash
requirements in the month it is borrowed but must be paid back with
interest in the next month.
2. Borrow a single lump sum at the beginning of the first month
and repay the entire amount at the end of month 6.
The annual interest rate is 6%, or 0.5% per month.
In this case, interest must be paid each month (but no part of
the principal is paid until the end of month 6).
The company currently has $50,000 on hand and would like to
maintain an ending cash balance each month of at least $25,000.
Create an LP model in Excel to track monthly cash flow and
determine the borrowing and repayment plan that minimizes the total
cost of financing the loans. Upload the Excel file showing your
model and solution.